一)什么是AQS？

1)AQS也被稱之為是抽象同步隊列，它是JUC包底下的多個組件的底層實現(xiàn)，Lock，CountDownLatch和Semphore底層都使用到了AQS

AQS的核心思想就是給予一個等待隊列和同步狀態(tài)來實現(xiàn)的，它的內(nèi)部使用一個先進先出的隊列管理來獲取同步資源的線程，每一個線程在競爭同步資源的時候會先嘗試獲取同步資源，如果獲取不到，那么會被封裝成一個節(jié)點加入到阻塞隊列中

2)在底層的AQS提供了兩種鎖機制，分別是共享鎖和排他鎖

排它鎖，就是存在多線程競爭同一共享資源時，同一時刻只允許一個線程訪問該共享資源，也就是多個線程中只能有一個線程獲得鎖資源，比如 Lock 中的 ReentrantLock 可重入鎖實現(xiàn)就是用到了 AQS 中的排它鎖功能；

共享鎖也稱為讀鎖，就是在同一時刻允許多個線程同時獲得鎖資源，比如 CountDownLatch 和Semaphore都是用到了AQS中的共享鎖功能；

AQS是抽象同步隊列，就是一個抽象類，Reentranlock和信號量和CountDownLatch在底層都是基于AQS來實現(xiàn)的，就是實現(xiàn)這些產(chǎn)品的一個公共的方法，無論是鎖，是需要競爭那一把鎖的，信號量也是需要得到停車位的，計數(shù)器-1，計時器也是有多個線程來競爭同一把鎖，計數(shù)器-1，多個產(chǎn)品是具有一個公共的功能的，于是就把這個公共的功能封裝起來實現(xiàn)了一個抽象類?

3)設(shè)計整個AQS體系需要解決的三個問題就是:

3.1)互斥變量的設(shè)計以及多線程同時更新互斥變量時候的安全性

a)AQS采用了int類型的互斥變量來記錄競鎖的一個狀態(tài)，0表示沒有任何線程獲取到鎖資源，大于等于1表示已經(jīng)有線程獲取到了鎖資源并持有鎖

b)如果是無鎖狀態(tài)，競爭鎖的線程則把這個state更新成1，表示占用到鎖，此時如果多個線程進行同樣的操作，會造成線程安全問題，AQS采用了CAS機制來保證互斥變量state的原子性

3.2)為競爭到的鎖的線程等待以及競爭到的鎖資源的線程釋放鎖之后的喚醒

c)未獲取到鎖資源的線程通過Unsafe類中的park方法對線程進行阻塞，把阻塞的線程按照先進先出的原則加入到一個雙向鏈表的結(jié)構(gòu)中，當獲得鎖資源的線程釋放鎖之后，會從雙向鏈表的頭部去喚醒下一個等待的線程再去競爭鎖；

3.3)鎖競爭的公平性和非公平性

另外關(guān)于公平性和非公平性問題，AQS的處理方式是，在競爭鎖資源的時候

公平鎖需要判斷雙向鏈表中是否有阻塞的線程，如果有，則需要去排隊等待；

非公平鎖不管雙向鏈表中是否存在等待鎖的線程，都會直接嘗試更改互斥變量state去競爭鎖

二)AQS的工作流程:

1)線程請求同步資源:當一個線程請求某一個同步資源也就是嘗試進行加鎖的時候，AQS會嘗試使用CAS來操作修改同步狀態(tài)，如果成功獲取到了鎖，該線程可以繼續(xù)執(zhí)行

2)獲取同步狀態(tài)失敗:如果說當前同步狀態(tài)已經(jīng)被其他線程占用了，鎖被其他線程獲取了，那么當前線程就需要將等待，AQS就會將該線程封裝成一個節(jié)點，加入到雙向鏈表中

3)自旋和阻塞:在等待隊列中的線程會不斷地進行自旋嘗試獲取到鎖，如果自旋一定次數(shù)還是獲取不到鎖，那么就進入到阻塞狀態(tài)，等待被喚醒

4)線程釋放鎖:當線程完成了對資源的操作需要釋放鎖的時候，這個線程就會調(diào)用AQS方法中的release方法，這個線程會使用CAS來修改同步狀態(tài)，并喚醒等待隊列中的一個線程或者是多個線程

5)等待喚醒線程:AQS在釋放資源以后，會從隊列中選擇一個或者是多個線程并將其喚醒，被喚醒的線程會嘗試再次去獲取同步狀態(tài)，如果獲取成功，那么繼續(xù)執(zhí)行，如果獲取失敗，那么繼續(xù)進入自旋或者是阻塞狀態(tài)

Thread.sleep()方法可以讓線程進入到阻塞狀態(tài)，讓出CPU的執(zhí)行權(quán)，這個方法可以傳入一定的參數(shù)讓線程休眠指定的時間，讓CPU的執(zhí)行權(quán)給到其他線程或者是進程，操作系統(tǒng)底層會設(shè)置一個定時器，當定時器的時間到了以后，操作系統(tǒng)會再次喚醒這個線程，Thread.sleep(0)雖然沒有傳遞睡眠時長，但是還是會觸發(fā)線程調(diào)度的切換，當前線程會從運行狀態(tài)切換到就緒狀態(tài)，然后操作系統(tǒng)會根據(jù)優(yōu)先級選擇一個線程來執(zhí)行，如果有優(yōu)先級更高的線程來等待時間片，那么這個線程就會得到執(zhí)行，如果沒有就會可能立即選擇剛剛進入到就緒狀態(tài)的這個線程來執(zhí)行，具體的調(diào)度策略，取決于操作系統(tǒng)底層的調(diào)度算法

CAS保證多線程環(huán)境下共享變量操作的一個原子性

三)獲取到AQS的同步狀態(tài)，就相當于是獲取到了鎖嗎？

在大多數(shù)情況下，AQS中獲取到同步狀態(tài)確實是表示獲取到了鎖資源，但是某些情況下獲取到同步狀態(tài)表示獲取到了某一些條件，而不是鎖資源；

1)當使用Reentranlock的時候，AQS的子類會確保在獲取到同步狀態(tài)的時候，該線程獲取到了鎖，并且可以繼續(xù)執(zhí)行臨界區(qū)的代碼，這種情況下，獲取到了同步狀態(tài)確實是獲取到了鎖資源

2)但是對于AQS來說，他還可以實現(xiàn)一些其它類型的同步器，比如說信號量和CountDownLatch，在這些場景下，獲取到同步狀態(tài)并不是代表著獲取到了鎖資源，而是獲取到了特定類型的同步器所提供的信號或者是等待條件

索引是存儲引擎中用于快速找到數(shù)據(jù)記錄的一種數(shù)據(jù)結(jié)構(gòu)，就是類似于教科書中的目錄部分，可以通過目錄快速的找到文章所對應的頁碼，MYSQL也是一樣的道理，在進行數(shù)據(jù)查找的時候，首先進行判斷這個條件是否命中索引，如果是就通過索引查找相關(guān)數(shù)據(jù)，如果沒有那么就需要進行全表掃描，一條一條地將數(shù)據(jù)加載到磁盤中進行比對

1)如上圖所示，數(shù)據(jù)庫在沒有索引的情況下，數(shù)據(jù)本身又分散在硬盤上面的不同位置，在進行讀取數(shù)據(jù)的時候，擺臂需要前后擺動來進行查詢數(shù)據(jù)，這樣在磁盤上查找數(shù)據(jù)非常消耗時間，即使數(shù)據(jù)是按照順序進行存放的，那么也是需要按順序從磁盤上從1位置到6位置進行讀取，還要將數(shù)據(jù)加載到磁盤上面6次，這樣的IO操作仍然是非常浪費時間

2)如果不借助任何數(shù)據(jù)結(jié)構(gòu)來來幫助快速定位數(shù)據(jù)的話，查找操作就是逐行查找比較，從Col 2 = 34 開始，進行比較，發(fā)現(xiàn)不是，繼續(xù)下一行，當前的表只有不到10行數(shù)據(jù)，但如果表很大的話，有上千萬條數(shù)據(jù)，就意味著要做很多很多次硬盤I/0才能找到；

3)CPU必須先去磁盤中去查找這條記錄，找到之后加載到內(nèi)存中，再來針對于數(shù)據(jù)進行處理，這個過程最好費時間的就是磁盤IO，涉及到磁盤的旋轉(zhuǎn)時間和磁頭的尋道時間；

如果沒有索引:就需要加數(shù)據(jù)加載到內(nèi)存中，一個一個加載到磁盤里面進行比對，遍歷所有的數(shù)據(jù)，將所有的數(shù)據(jù)加載到磁盤中，我們先把第一條數(shù)據(jù)從磁盤加載到內(nèi)存中，依次進行數(shù)據(jù)比對，相當于是順序查找

有了索引之后減少IO次數(shù)，B+樹里面每一個節(jié)點都是一個數(shù)據(jù)頁，要找77，先加載根節(jié)點34到內(nèi)存中，發(fā)現(xiàn)77比34大，那么直接向右走，直接砍掉了一半的數(shù)據(jù)，然后再加載89到內(nèi)存中，然后直接向左走，最多3次IO，從磁盤將數(shù)據(jù)加載在內(nèi)存中是很浪費時間的

四)說說索引的優(yōu)缺點:

優(yōu)點:合理的增加索引，可以提高數(shù)據(jù)的查詢效率，減少數(shù)據(jù)的查詢時間

缺點:

1)創(chuàng)建索引和維護索引需要消耗一定的時間

2)索引需要一定的物理空間

3)對創(chuàng)建索引的表進行新增刪除和修改的時候，也需要同步動態(tài)維護索引會造成性能的影響

五)MYSQL不適合創(chuàng)建索引:

1)數(shù)據(jù)量太小:即使不創(chuàng)建索引，查詢的速率也是比較快的，這個是或創(chuàng)建索引反而會增加維護的成本和查詢時間，查詢的時間可能都比不上遍歷索引的時間

2)數(shù)據(jù)區(qū)分度不高，有大量重復數(shù)據(jù)的列不要建立索引:

比如說年齡，性別這樣的列構(gòu)建索引反而會降低檢索效率

3)查詢條件包含函數(shù)操作，索引會失效

4)頻繁變更的表:經(jīng)常要進行更新，刪除和插入操作

a)頻繁進行更新的字段不需要創(chuàng)建索引，因為更新數(shù)據(jù)的時候也是需要更新索引的，如果索引太多，那么更新索引的時候也會造成負擔從而影響速率

b)如果對表創(chuàng)建的索引過多，雖然提高了查詢速度，也會降低更新表的速度

六)InnoDB和MYSIM有什么區(qū)別？

存儲引擎是定義數(shù)據(jù)的存儲方式和實現(xiàn)數(shù)據(jù)讀取的實現(xiàn)邏輯，

不同的存儲引擎就決定了底層的文件系統(tǒng)中文件的物理結(jié)構(gòu)，存儲引擎表示表的類型

每一張表都會對應著一個存儲引擎，每一個存儲引擎來負責表結(jié)構(gòu)在底層存數(shù)據(jù)到底是一個什么樣子的結(jié)構(gòu)

show vaiables like "%storage_engines%"；查看默認存儲引擎

show create table+表名字，顯示出表的存儲引擎

create table user(id int) engine=innodb，造表的時候指定存儲引擎

show create user;

alter table 表名字 engine='MYSIM"

MYSIM存儲引擎數(shù)據(jù)和索引是分離的，.MYD文件，D代表Data是MYSIM的數(shù)據(jù)文件，存放的是數(shù)據(jù)記錄，.MYI文件，I代表index是MYSIM的索引文件，MYSIM索引和數(shù)據(jù)是分離的

InnoDB只有一個IBD文件索引和數(shù)據(jù)是結(jié)合在一起的

?

1)數(shù)據(jù)存儲的方式不同:MYSIM是將索引和數(shù)據(jù)存儲到連兩個不同的文件里面，而InnoDB索引即數(shù)據(jù)，數(shù)據(jù)即索引

2)InnoDB支持外鍵，Innodb可以在表和表之間建立關(guān)聯(lián)關(guān)系，來保證數(shù)據(jù)的完整性，但是MYSIM不支持外鍵約束；

3)支持行級鎖，提高了程序的并發(fā)訪問性能多個事務可以訪問不同的行，避免了鎖定整張表的情況，鎖的粒度更細，但是MYSIM只是支持表鎖，當一個事務對表進行修改操作的時候，其他食物無法對表進行操作，會出現(xiàn)性能瓶頸；

4)InnoDB支持崩潰修復和自增列，可以在數(shù)據(jù)庫崩潰后進行數(shù)據(jù)恢復，保證數(shù)據(jù)的一致性

InnoDB支持崩潰修復和自增列的，崩潰修復本身是依靠日志來實現(xiàn)的，底層是依靠日志，Redolog重寫日志，重寫日志就可以實現(xiàn)崩潰修復，就是數(shù)據(jù)信息還沒有刷盤到MYSQL的磁盤里面，MYSQL崩潰了，此時MYSQL就可以使用Redlog來實現(xiàn)崩潰修復

InnoDB依靠readdolog，重寫日志，數(shù)據(jù)還沒有進行落盤，還沒有放入磁盤里面，突然掉電了，突然發(fā)生意外事故了，此時有redolog，就可以崩潰修復了，但是MSIM崩潰之后就需要人工手動恢復操作可能會導致數(shù)據(jù)的丟失和數(shù)據(jù)完整性的不一致問題

5)Innodb支持事務:innodb有ACID四大特性，MYSIM針對于數(shù)據(jù)統(tǒng)計有額外的常數(shù)存儲，因此count(*)的查詢效率比較高

Memory存儲引擎:不支持事務，不支持外鍵，它是一種內(nèi)存性的存儲引擎，所有的數(shù)據(jù)都存儲在內(nèi)存中，不支持事務，不支持外鍵，本身支持hash索引和B樹索引

七)說一說數(shù)據(jù)庫的三范式:

第一范式:第一范式規(guī)定數(shù)據(jù)表中的每一個列是不可分割的最小單元

存儲地址盡量分割成幾個字段去填，淘寶的京東在進行填寫貨物的地址的時候，先讓你進行填寫省，接下來讓你選擇市，區(qū)，接下來是詳細地址，為什么不把用戶的地址分割成一個字段呢？將地址分割幾個成幾個字段，每一個字段都被拆分成不可分割的最小單元，假設(shè)有一天某一個行政單位發(fā)生改變，有一天，口琴村變成XX村了，此時如果都寫到一塊，此時字段就不好修改，替換的時候還會影響其他的，如果表中的字段都是不可分割的最小單元，那么此時就很方便的進行替換了還不會影響其他的字段；

第二范式:存在非主屬性對于主鍵的部分函數(shù)依賴

一個表當存在聯(lián)合主鍵，有兩個主鍵字段充當整張表的聯(lián)合主鍵(一個主鍵)，不能說有一個非主鍵字段只依賴于聯(lián)合主鍵中的一個而不依賴另一個聯(lián)合主鍵，不能存在非主鍵字段對于部分主鍵的依賴，一定要對聯(lián)合主鍵都依賴；

第三范式:消除非主屬性對于逐漸的傳遞函數(shù)依賴

表中的列不存在對非主鍵列的傳遞函數(shù)依賴，一個非主鍵列3推出非主鍵列2，非主鍵列2推導出主鍵列1，從而非主鍵列3推出主鍵列1

八)內(nèi)連接和外連接有什么區(qū)別？

內(nèi)連接和外連接是關(guān)系型數(shù)據(jù)庫常見的連接操作:

內(nèi)連接:兩個表中都存在的字段最終才會包含在結(jié)果集中

左外連接:左外連接直接返回左表中的所有記錄，以右表中滿足連接條件的匹配記錄，如果右表中沒有匹配的記錄，那么右表的記錄值就為null；

右外連接:直接返回右表中的所有記錄，以及左表中滿足要求的所有記錄，如果左表中沒有符合要求的紀錄，，那么左表的記錄值就是null；

九)MYSQL中索引的分類:

索引的分類:

一)按照字段特性進行分類:

1)主鍵索引:數(shù)據(jù)列不允許重復，不允許為null，在一張表只能有一個主鍵；

2)唯一索引:數(shù)據(jù)列不允許重復況且允許為null值，在一張表中允許多個列創(chuàng)建唯一索引；

3)普通索引:基本的索引類型，沒有唯一性約束，也允許為null值

4)全文索引:對文本的內(nèi)容進行分詞，搜索

二)按照物理存儲進行分類:聚簇索引和非聚簇索引

三)按照索引數(shù)量進行分類:

1)單列索引:針對表中的某一列創(chuàng)建的索引，可以根據(jù)該列的值快速定位到所對應的記錄，單列索引適用于針對于單個列進行頻繁的查詢排序和過濾的場景，比如說可以針對于用戶ID列創(chuàng)建索引，以便根據(jù)用戶ID快速的進行查詢

2)聯(lián)合索引:針對于表中的多個字段進行建立索引，也被稱之為是復合索引或者是組合索引

聚簇索引并不是一種單獨的索引類型，而是一種數(shù)據(jù)存儲的方式，所有的用戶記錄都存儲在了葉子節(jié)點上面，數(shù)據(jù)行和相鄰鍵值是存儲在一起的，B+樹分成聚簇索引和非聚簇索引

數(shù)據(jù)自動添加的時候，底層的B+樹就已經(jīng)自動創(chuàng)建了一個聚簇索引

1)每一個頁中的記錄按照主鍵值的大小順序組成了一個單向鏈表

2)各個存放用戶記錄的頁也是根據(jù)記錄的主鍵順序大小組成了一個雙向鏈表

3)放目錄項記錄的頁分為不同的層次，在同一層次中的頁也是根據(jù)頁中目錄項記錄的主鍵大小順序排成一個雙向鏈表，所有的用戶記錄都會存放在聚簇索引的葉子節(jié)點處;

4)這種聚簇索引并不需要我們在MySQL語句中顯式的使用INDEX 語句去創(chuàng)建，?InnDB?存儲引擎會?自動?的為我們創(chuàng)建聚簇索引，由于對于MYSQL數(shù)據(jù)庫來說數(shù)據(jù)物理存儲排序方式只能有一種，所以每一個MYSQL的表中只能有一個聚簇索引，一般情況下就是該表的主鍵

5)InnoDB的主鍵盡量選擇有序的順序ID，而不建議使用無序的ID，比如說UUID，MD5，字符串作為主鍵無法保證索引的順序增長

聚簇索引的缺點:

1)聚簇索引的插入速度嚴重依賴于插入順序，按照主鍵的插入順序是最快的插入順序，否則會出現(xiàn)頁分裂，會嚴重影響到性能，所以對于Innodb存儲引擎來說，一般選擇自增的ID作為主鍵

2)對于聚簇索引來說，主鍵的更新代價很大，因為可能會導致被更新的行進行移動，對于Innodb表來說，一般定義為表不可更新

上面建立的聚簇索引都是只能是主鍵作為搜索條件的時候才可以發(fā)揮作用，因為B+樹中的數(shù)據(jù)都是按照主鍵進行排序的，那么如果想要以別的列作為搜索條件怎么辦呢？肯定是不能從頭到尾按照鏈表全部遍歷一遍，答案是可以創(chuàng)建多個B+樹不同的B+樹采用不同的排序規(guī)則

這個時候c2列在葉子節(jié)點是按照升序來進行排列的，c2列下面還會存放主鍵ID

如果每一個二級索引都存放完整數(shù)據(jù)，那么就太浪費空間了

B+樹作為索引的注意事項:

1)根節(jié)點位置萬年不動:

1.1)一開始現(xiàn)在只有一個頁page1就是來存放一條一條的記錄的，假設(shè)一個頁能夠存放三條記錄，現(xiàn)在這個第一個頁中已經(jīng)存放三條記錄了，這個時候再去像這個頁中添加數(shù)據(jù)，不是新創(chuàng)建一個頁，來存放第四條數(shù)據(jù)

1.2)此時會創(chuàng)建一個新的目錄頁page2，將原來第一個頁中的三條數(shù)據(jù)放到這個新的page2中，因為此時又新增了一條數(shù)據(jù)，但是此時page2也放不下，此時又會開辟一個新的頁page3來存放新的記錄，假設(shè)這條記錄的主鍵值比page2的主鍵值都大，如果不是，那么就在進行調(diào)整，此時會將page2和page3中的最小值存放到page1里面，此時page1就變成了目錄頁

1.3)當為某一張表創(chuàng)建B+樹索引的時候，聚簇索引不是人為創(chuàng)建的，默認就有，都會為這個索引創(chuàng)建一個根節(jié)點頁面，最開始表中沒有數(shù)據(jù)的時候，B+樹的索引對應的根節(jié)點既沒有用戶記錄，也沒有目錄項記錄，隨后向表中插入數(shù)據(jù)的時候，數(shù)據(jù)會記錄在根節(jié)點中

1.4)當根的可用空間用完之后向這個表中插入記錄，此時會將根結(jié)點的所有記錄復制到一個新開辟的頁中，比如說頁a，然后針對于這個頁進行頁分裂的操作，得到一個新的頁，頁B，此時新插入的記錄根據(jù)主鍵值的大小就會被分配到頁a或者是頁b中，而根節(jié)點是作為存儲目錄項記錄中的頁

這個過程需要特別注意的是，一個B+樹的索引的根節(jié)點自誕生開始就不會再進行移動，只要針對于某一張表建立了索引，那么它的根節(jié)點的頁號就會被移動到某一個地方，從今以后凡是InnoDB引擎在使用到這張表的時候，都會從固定的地方取出根節(jié)點的頁號從而來訪問索引

2)目錄項記錄的唯一性

3)一個頁中至少存放兩條記錄?

?十)聚簇索引和非聚簇索引有什么區(qū)別??

1)葉子節(jié)點上存儲的東西不同:聚簇索引的葉子節(jié)點上存放的是完整的數(shù)據(jù)，而非聚簇索引葉子節(jié)點上存放的是主鍵的ID

2)查詢效率不同:聚簇索引的查詢效率要比非聚簇索引效率要高

3)數(shù)量限制不同:聚簇索引通常是主鍵索引，而主鍵一張表只能有一個，但是非聚簇索引表中是可以有多個的

回表查詢:非聚簇索引的葉子節(jié)點存放的并不是真實的數(shù)據(jù)，而是聚簇索引的主鍵ID，，所以當時使用到普通索引查詢的時候需要先查詢到主鍵索引，然后再從主鍵索引中查詢到真實的數(shù)據(jù)，這個過程就是回表查詢

1)所以說在InnoDB存儲引擎中，不建議使用過長的字段來作為主鍵，因為所有的二級索引的葉子節(jié)點都是主鍵值，過長的主鍵索引會使二級索引變得很大

2)使用費單調(diào)性的字段作為索引不是一個好主意，因為InnoDB的數(shù)據(jù)文件本身就是一顆B+樹，而非單調(diào)性的主鍵在進行插入新記錄的時候，數(shù)據(jù)文件會維持B+樹的特性而進行分裂調(diào)整，十分低效，所以使用自增字段作為主鍵是一個不錯的選擇；

十一)MYSQL索引選擇順序結(jié)構(gòu)的合理性:

全表掃描:將所有的索引依次加載到內(nèi)存中，加載一個數(shù)據(jù)需要一次磁盤IO，數(shù)據(jù)庫的查詢本身就是查詢索引的操作，當數(shù)據(jù)量比較大的時候，索引都是存放在磁盤上面的，當使用索引進行查詢的時候，不可能將整個索引全部加載到內(nèi)存里面，而是用到誰加載誰

1)Hash索引:只有Memory存儲引擎支持哈希索引，下面是哈希索引的缺點:

1.1)哈希索引只能支持等于，不等于還有in查詢，如果進行范圍查詢，哈希索引的時間復雜度會退化成O(N)

1.2)哈希索引本身并沒有順序，無法支持order by

1.3)針對于聯(lián)合索引無能為力

1.4)一般來說針對于等值查詢來說，哈希索引的效率要更高，不過就是有一種情況索引的重復值如果很多，那么哈希索引的效率就會降低，這是因為當遇到哈希沖突的時候，一般使用鏈式法來解決哈希沖突，鏈表的查詢效率過低，所以說不建議hash索引建在重復列比較多的字段，比如說姓名，年齡，性別

但是InnoDB支持自適應哈希索引，什么情況下才會使用到自適應哈希索引呢？就是當某一個數(shù)據(jù)經(jīng)常訪問，當滿足一定條件的時候，就會將這個數(shù)據(jù)頁的地址存放到哈希表中，這樣子下一次進行查詢的時候，就可以直接找到這個頁面的所在位置

innodb默認使用常駐哈希索引是不需要進行指定的

使用自適應哈希索引目的是為了方便根據(jù)SQL的查詢條件很快的定位到葉子節(jié)點，尤其是當B+樹比較深的時候，使用哈希索引可以快速的定位到葉子節(jié)點，可以加快數(shù)據(jù)檢索效率

mysql> show variables like '%adaptive_hash_index';

2)二叉搜索樹:

1)二叉搜索樹可能退化成單分支的樹，退化成鏈表

2)二叉搜索樹一個節(jié)點只能存儲一個值，進行一次磁盤IO只能比較一次

3)樹的高度比較高，就算不退化成鏈表磁盤IO也很高

3)AVL樹:和二叉搜索樹類似，樹的高度太高了，每訪問一個節(jié)點就需要進行一次磁盤IO操作，雖然AVL樹相比于二叉搜索樹來說維持了自平衡的特性，相比于B+樹一點優(yōu)勢都沒有

4)紅黑樹:原因和不使用B樹差不多，甚至還不如B樹

1)紅黑樹不如B+樹更矮胖，紅黑樹高度更高，要進行多次磁盤IO

2)紅黑樹一個節(jié)點只能存放一條數(shù)據(jù)，一次IO只能進行一次比較，而B+樹一次磁盤IO可以進行多次比較

3)紅黑樹插入節(jié)點不僅要改變頻繁節(jié)點顏色，有時候左旋，右旋，插入性能非常低老保證紅黑樹的特征，插入刪除不如B+樹，B+樹有冗余節(jié)點，插入和刪除效率更高

4)處理范圍查詢不方便，紅黑樹也要進行深度優(yōu)先遍歷才能得到范圍內(nèi)的數(shù)據(jù)

5)紅黑樹和B樹非葉子節(jié)點即要存數(shù)據(jù)又要存放ID，沒有冗余節(jié)點，沒有冗余字段，所以需要的頁非常多，每一個頁中存放的ID是非常少的，只有B+樹葉子節(jié)點全部是完整的數(shù)據(jù)

紅黑樹本質(zhì)上是二叉樹，插入很麻煩，頻繁左旋右旋，一個節(jié)點值只能存放一個數(shù)據(jù)，是可以要滿足平衡二叉樹的性質(zhì)，需要大量的旋轉(zhuǎn)和更改顏色來維持紅黑樹的特性，增刪效率都很低；

B樹:

1)N個關(guān)鍵字有N+1個分支

2)數(shù)據(jù)分布在整個B樹中，B樹的葉子節(jié)點和非葉子節(jié)點都存放數(shù)據(jù)，整個數(shù)據(jù)的搜索可能在非葉子節(jié)點結(jié)束，它的搜索相當于是做一次二分查找，只能通過中序遍歷來排序；

B+樹和B樹的區(qū)別:

1)B+樹K個節(jié)點就有K個關(guān)鍵字，但是B樹K個節(jié)點有K+1個關(guān)鍵字

2)B+樹非葉子節(jié)點的關(guān)鍵字也會出現(xiàn)在葉子節(jié)點中，并且是作為子節(jié)點中的最大值或者是最小值

3)B+樹的非葉子節(jié)點僅僅只是用做索引而不保存最終的完整的數(shù)據(jù)記錄，但是B樹中非葉子節(jié)點及保存索引也保存數(shù)據(jù)的完整記錄

4)B+樹的所有關(guān)鍵字都在葉子節(jié)點出現(xiàn)，葉子節(jié)點構(gòu)成一個有序鏈表，并且葉子節(jié)點也是按照從小到大的順序來進行排序的

十二)為什么索引選擇B+樹而不選擇B樹？?

1)B+樹的查詢效率更穩(wěn)定:

所有的數(shù)據(jù)都存儲在葉子節(jié)點上，但是B樹可能查詢終止在葉子節(jié)點上

2)B+樹處理范圍查詢更方便:B樹只能通過中序遍歷來處理范圍查詢，而B+樹可以直接通過截取鏈表中的一部分進行處理范圍查詢

3)B+樹插入和刪除性能更好:

B+樹有大量的冗余節(jié)點，所有的非葉子節(jié)點都是冗余索引，這些冗余索引使得B+樹在進行插入刪除結(jié)點相比于B樹的效率更高

4)B+樹的IO次數(shù)更少:

B+樹的非葉子節(jié)點不存放具體的實際的記錄數(shù)據(jù)而是存放索引，B樹的所有結(jié)點既存放用戶的數(shù)據(jù)又存放索引，因此當數(shù)據(jù)量相同的情況下，B+樹的非葉子節(jié)點可以存放更多的索引，因此在查詢的時候IO查詢次數(shù)更少，效率更高，16K的頁只存ID，是可以存儲很多ID的，但是如果這個16K頁既存儲數(shù)據(jù)又存儲ID，在同一個頁中，存儲的ID就會比B+樹同等的葉子節(jié)點少很多，B+樹的非葉子節(jié)點一次IO可以得到更多的ID，就可以進行更多的比較，那么在同等查詢下，遍歷B+樹的非葉子節(jié)點的個數(shù)就要B樹少，比B樹遍歷IO次數(shù)就會很少，效率會很高，MYSQL的根節(jié)點是常駐內(nèi)存的，B樹的一個頁存儲的目錄項要比B+樹的存儲的目錄項少，所以B+樹更矮胖；

十三)B+樹的存儲能力如何，為什么說只需要一次磁盤IO或者是三次磁盤IO？

1)InnoDB存儲引擎中的頁的大小是16KB，假設(shè)BIGINT占用8個字節(jié)，指針類型占用8個字節(jié)，也就是說一個頁中大概存儲16KB/(8B+8B)=1000個鍵值，也就是說深度是3的B+樹可以存放10^3*10^3*10^3=10億條記錄，這里面是假定一個數(shù)據(jù)頁可以存儲10^3條行記錄數(shù)據(jù)了

2)但是實際情況中可能每一個節(jié)點可能不能完全填充滿，因此在數(shù)據(jù)庫中B+樹的高度一般都是在2-4層，MYSQL的存儲引擎在設(shè)計的時候是將根節(jié)點常駐內(nèi)存的，也就是說再進行查找某一個鍵值的行記錄最多只是需要1-3次磁盤IO操作的

十四)一條SQL語句的執(zhí)行流程:

簡單來說 MySQL主要分為 Server 層和存儲引擎層，Server層主要包括連接器、查詢緩存、分析器、優(yōu)化器、執(zhí)行器等，還有一個通用binlog日志模塊(用于整個數(shù)據(jù)庫操作記錄，主從復制的關(guān)鍵)，存儲引擎層主要負責數(shù)據(jù)的存儲和讀取

1)連接器

客戶端想要對數(shù)據(jù)庫進行操作時，連接器就是用來負責跟客戶端建立連接、獲取權(quán)限、維持和管理連接的，連接器支持短連接也支持長連接，同時為了避免頻繁創(chuàng)建和銷毀連接造成性能損失，可選擇利用連接池進程管理

2)查詢緩存:

2.1)查詢緩存主要用來緩存我們所執(zhí)行的 select語句以及該語句的結(jié)果集，如果開啟了查詢緩存，執(zhí)行查詢語句的時候，會先查詢緩存，如果緩存 key 被命中，就會直接返回給客戶端，在數(shù)據(jù)變換頻繁的表中，是不推薦使用的，當一張表的數(shù)據(jù)發(fā)生變化，其所有緩存都將清空

2.2)一般MYSQL的緩存盡量在靜態(tài)表中進行使用，就是很少更新的表，MYSQL服務器如果在查詢緩存中發(fā)現(xiàn)了這一條SQL語句，(緩存的SQL-->key，結(jié)果----value)

那么就會直接將結(jié)果返回給客戶端，如果沒有，就直接進入到解析器，由于MYSQL命中緩存的概率非常低，所以MYSQL8.0就將這個功能給刪除了

2.3)SQL語句作為key，查詢結(jié)果是String，也是value

1.1)SQL有空格

1.2)函數(shù)調(diào)用，NOW()函數(shù)，雖然兩個SQL相同，但是NOW()函數(shù)的結(jié)果不一樣

1.3)緩存失效，比如說緩存成功的緩存了一條SQL語句，但是userID的記錄我在原表中進行刪除了，再次查詢就查不到了，數(shù)據(jù)進行修改的時候，再次調(diào)用結(jié)果就不正確了，但是緩存只是適用于不經(jīng)常修改的表中

3)分析器:語法分析，語義分析，知道SQL要做什么

3.1)詞法分析:因為輸入的命令是是由多個字符串和空格組成的一條SQL語句，MYSQL需要進行識別里面的字符串都是代表的什么含義，就比如MYSQL把你輸入的這個select這個關(guān)鍵字識別出來，這是一個查詢語句，也要把字符串user識別成表名，把userID識別成userID；

3.2)語法分析:把提取出的關(guān)鍵詞轉(zhuǎn)換為抽象語法樹后進行檢驗。主要就是判斷你輸入的 sql 是否正確，是否符合 MySQL 的語法，并會提示you have an error in your syntax

4)優(yōu)化器:

一條查詢可能有很多種執(zhí)行方式，最后都返回相同的結(jié)果，優(yōu)化器就是在其中找到最好的執(zhí)行計劃，優(yōu)化器會根據(jù)IO和CPU成本，選出代價最小的索引進行執(zhí)行

4.1)邏輯查詢優(yōu)化:索引優(yōu)化，在優(yōu)化器中就會確定出SQL的執(zhí)行路徑比如說是全表檢索還是索引檢索，還有可能說就是這個字段有多個索引，那么優(yōu)化器會最終判斷到底要走哪一條索引，最終生成執(zhí)行執(zhí)行計劃

4.2)語法查詢優(yōu)化:比如說子查詢改成多表連接，就是通過SQL的等價變換來提升查詢效率，直白一點說就是換一種寫法可能查詢效率更高，改變各個表的連接順序

select * from user where username="張三" and gender="男"

五)執(zhí)行器:

執(zhí)行器會判斷你是否具有相關(guān)操作的權(quán)限，沒有權(quán)限就會發(fā)生報錯，如果具備權(quán)限，就執(zhí)行SQL進行返回，如果設(shè)置了查詢緩存那么就會將查詢結(jié)果進行緩存，最終會調(diào)用存儲引擎的API也就是一些接口，調(diào)用存儲引擎之后再去調(diào)用文件系統(tǒng)，實現(xiàn)對數(shù)據(jù)的查詢

執(zhí)行引擎根據(jù)執(zhí)行計劃查詢數(shù)據(jù)，并把結(jié)果集返回客戶端

六)BufferPool

BufferPool起到一個緩存的作用,MySQL 的數(shù)據(jù)最終是存儲在磁盤中的，如果沒有 Buffer Pool，那么每次的數(shù)據(jù)庫請求都會磁盤中查找，這樣必然會存在 IO 操作，但是有了 Buffer Pool，只有第一次在查詢的時候會將查詢的結(jié)果存到 Buffer Pool 中，這樣后面再有請求的時候就會先從緩沖池中去查詢，如果沒有再去磁盤中查找，然后在放到 Buffer Pool 中

如果沒有索引查詢，那么MYSQL會從第一個數(shù)據(jù)頁開始從磁盤加載到執(zhí)行引擎的緩存池中

https://blog.csdn.net/qq_43618881/article/details/118657040

innodb數(shù)據(jù)引擎的更新操作:

1)innodb存儲引擎首先先去BufferPool中查找id=1的記錄，沒找到就會從磁盤中進行查找，如果查找到就會把這一條記錄加載到緩沖池bufferPool中，由于是更新操作innodb會診對于這一條記錄加鎖

2)SQL語句執(zhí)行前默認是開啟事務的，考慮到更新失敗后的數(shù)據(jù)回滾，把更新前的數(shù)據(jù)寫入undolog中

3)更新BufferPool中的數(shù)據(jù)

4)此時內(nèi)存中的數(shù)據(jù)已經(jīng)更改，此時磁盤上面的數(shù)據(jù)還沒有修改，但是為了防止數(shù)據(jù)的丟失，需要先將更新后的值寫入到redo log buffer中；

5)此時就可以進行事務的提交了，事務提交的同時會按照一定的策略將redo log buffer中的數(shù)據(jù)刷入到磁盤中，從而避免了數(shù)據(jù)的丟失，然后更新binlog；

十五)如何創(chuàng)建索引刪除索引？?

一)創(chuàng)建索引:

1)在創(chuàng)建表的時候構(gòu)建索引:

1.1)使用create table創(chuàng)建表時，除了可以定義列的數(shù)據(jù)類型外，定義主鍵約束、外鍵約束或者唯一性約束，而不論創(chuàng)建哪種約束，在定義約束的同時相當于在指定列上創(chuàng)建了一個索引,沒有給索引起名字，那么就直接使用字段名當索引

1.2)可以在創(chuàng)建表的時候構(gòu)建索引:

2)創(chuàng)建表以后創(chuàng)建索引:

--創(chuàng)建索引,具體格式是 index 索引名字(表中的字段名字)
create table user(
userID int,
username varchar(30),
index idx_username(username)
);
--1.命令查看索引
show create table user;
--2.查看索引
show index from user;
--3.創(chuàng)建唯一索引,下面的username就不能有相同的值
create table user(
userID int,
username varchar(30),
unique index idx_username(username)
);
--4.刪除主鍵索引,不能有auto_increment
alter table user drop primary key;
--5.創(chuàng)建聯(lián)合索引
create table user(
userID int,
username varchar(30),
index idx_username(userID,username)
);
--6.創(chuàng)建全文索引,只會拿前20個字符作為索引
create table user(
userID int,
username varchar(30),
fulltext index text_username(username(20))
);

十六)哪些情況適合創(chuàng)建索引呢？

1)針對字段有唯一性的限制:索引本身是可以起到約束的作用的，例如唯一索引和主鍵索引都是可以起到約束作用的，因此在數(shù)據(jù)表中如果某一個字段是唯一的，就可以創(chuàng)建唯一性索引或者是主鍵索引，這樣就可以快速地根據(jù)索引確定該條記錄，例如學生表中學號是唯一的字段，針對該字段建立唯一索引就可以快速地確定學生的信息

2)頻繁做Where查詢的字段:某一個字段經(jīng)常在Select語句中經(jīng)常被使用到，那么就需要給這個字段建立索引了，尤其是數(shù)據(jù)量比較大的情況下，創(chuàng)建普通索引就可以大幅度提升數(shù)據(jù)查詢的效率，假設(shè)現(xiàn)在學生表中有1000條數(shù)據(jù)，查詢name="張三"的信息，就可以建立索引

針對于多表的join的連接字段要創(chuàng)建索引，where的條件要創(chuàng)建索引，不能連接過多的表

3)經(jīng)常group by和order by的列:然后針對于order by來說如果存在索引那么這個索引已經(jīng)排好序了，于是就節(jié)省了排序的時間，同理group by是相同的字段組合成一組，同理索引已經(jīng)是默認排好序的，那么排好序之后相同的字段挨在一起了;

先考慮student_id在考慮create_time，創(chuàng)建聯(lián)合索引效率更高

單獨使用group by你就針對該字段建立一個索引，order by也是同理，如果既要進行g(shù)roup by又要進行order by，那么首先將group by放在前面

4)經(jīng)常要update和delete的條件列:

對數(shù)據(jù)按照某個條件進行查詢后再進行 UPDATE 或 DELETE 的操作，如果對 WHERE 字段創(chuàng)建了索引，就能大幅提升效率,原理是因為我們需要先根據(jù) WHERE 條件列檢索出來這條記錄，然后再對它進行更新或刪除,如果進行更新的時候，更新的字段是非索引字段，提升的效率會更明顯，這是因為非索引字段更新不需要對索引進行維護

5)針對于distinct的字段需要建立索引:select distinct(studentid) from user

因為在對去重的字段建立索引的時候去重的字段本身就挨著，對于緊挨著的字段進行去重本身就簡單很多，因為索引本身就是排好序的

6)針對于列的類型小的字段建立索引:

假設(shè)說現(xiàn)在有一個字段叫做ID，那么這個ID類型上可以在考慮滿足實際要求的情況下，盡量選擇類型小的，創(chuàng)建表以后添加這個表中數(shù)據(jù)的時候占用表空間比較少一些，另一種情況就是當我們給這個字段添加索引的情況下，假設(shè)如果針對id建立索引，id占用的空間比較大，那么在非聚簇索引中一個頁中存放的數(shù)據(jù)項就會相對于來說比較少，如果id本身占據(jù)的數(shù)據(jù)相比較小，那么一個頁中能夠存放的數(shù)據(jù)項就比較多，那么此時一個頁中放的數(shù)據(jù)更多，那么這顆B+樹也就會變得越扁平，此時IO次數(shù)就會更少

假設(shè)極端情況下這個ID是一個主鍵，此時ID所占的大小，此時影響的就不光是一個聚簇索引了，而是會影響所有的非聚簇索引

7)針對于字符串的前綴創(chuàng)建索引:

當字段類型是字符串類型的時候(varchar，text等)，有的時候需要索引長度很長的字符串，這就會使得索引占用的空間很大，查詢的時候浪費大量的磁盤IO，影響查詢效率，此時就可以之將字符串的一部分前綴作為索引，這樣可以大大的節(jié)省空間，從而提升索引效率

create index 索引名字 on 表名(字符串的列(前幾個字符作為索引))

如何選擇截取的字符的大小呢？

如果截取字符截取的過多，那么會達不到節(jié)省空間的目的，如果截取字符截取的太少，那么字段的離散度和選擇度就會變得很低，那么如何進行計算不同長度的選擇性呢

1)首先先觀察一下字段在全部數(shù)據(jù)中的選擇度:

select count(distinct(address))/count(*) from user

2)通過不同長度去計算和全表的選擇性進行對比:count(distinct(left(address，10)))/count(*)

這個值越接近1越好，說明越有區(qū)分度

elect count(distinct left(address,10)) / count(*) as sub10, -- 截取前10個字符的選擇度
count(distinct left(address,15)) / count(*) as sub11, -- 截取前15個字符的選擇度
count(distinct left(address,20)) / count(*) as sub12, -- 截取前20個字符的選擇度
count(distinct left(address,25)) / count(*) as sub13 -- 截取前25個字符的選擇度
from shop;

count(distinct(left(address，10))/count(*)==1，說明截取前10個字符也是不重復的

假設(shè)現(xiàn)在使用到了前綴索引，只是把address列的前12個字符放到了二級索引中，下面的這個查詢就有點尷尬了:

select * from user order by address limit 12；

但是這個二級索引不包含完整的address列信息，所以無法對前12個字符相同，后面字符不同的記錄進行排序，所以使用前綴索引不支持索引排序，因為唯一索引的選擇性是1，這是最好的索引選擇性

假設(shè)一張表中有50條記錄，現(xiàn)在針對adress列的前12個字符建立了前綴索引，那么此時就會出現(xiàn)問題，假設(shè)50條記錄中前49條中前12個字符都是都是相同的，那么此時針對于order by進行排序或者是group by進行排序麻煩，索引使用前綴索引無法支持索引排序

8)選擇區(qū)分度高的字段作為索引

select count(要計算區(qū)分度的字段)/count(*) from 表名

假設(shè)現(xiàn)在有100萬條數(shù)據(jù)，假設(shè)根據(jù)性別建立索引，男生50W，女生50W，那么此時基數(shù)就是2，所謂的區(qū)分度非常差勁，這個時候針對gender創(chuàng)建索引就不太好的

9)使用最頻繁的列放在聯(lián)合索引的左側(cè):

十六)如何排查慢SQL?

在MYSQL中排查慢SQL通過開啟慢查詢?nèi)罩緛黹_啟排查慢SQL，然后分析和解決慢SQL

慢查詢是MYSQL提供的一種慢查詢?nèi)罩?#xff0c;具體指運行時間超過long_query_time的SQL，那么會被記錄到慢查詢?nèi)罩局?#xff0c;這個參數(shù)默認是10，意思就是說運行時間超過10s以上的語句

默認情況下MYSQL是不會開啟慢查詢?nèi)罩?#xff0c;況且需要我們手動來設(shè)置這個參數(shù)，如果不是條有需要的話，一般是不建議開啟這個參數(shù)的，因為開啟慢查詢?nèi)罩緯oMYSQL服務器帶來一定的性能影響，慢查詢?nèi)罩局С謱⑷罩居涗泴懭氲轿募?#xff0c;也支持將日志記錄寫入到數(shù)據(jù)庫表

通過下面這個命令就可以來進行查詢慢日志是否開啟:

如果要開啟慢查詢?nèi)罩?但是下面這個命令只會對當前數(shù)據(jù)庫生效，如果MYSQL重啟也會消失，如果要想永久生效，就必須修改MYSQL的配置文件my.cnf

修改閾值:

查詢慢查詢?nèi)罩緱l數(shù):

SHOW GLOBAL STATUS LIKE '%Slow_queries%';

找到慢SQL以后再執(zhí)行Explain執(zhí)行計劃來查詢慢SQL，然后分析慢SQL的原因

數(shù)據(jù)量過大，加緩存來減少數(shù)據(jù)查詢的次數(shù)或者是分庫分表，要進行垂直分割和水平分割

沒有加索引，直接加索引就好了

SQL語句寫法問題導致沒有觸發(fā)索引，從而來調(diào)整查詢SQL

十七)explain執(zhí)行計劃:

1)table:查詢出來的每一條記錄都對應著一張單表，因為結(jié)果集可能出現(xiàn)多條記錄

2)id:最終的結(jié)果最上面的記錄稱之為驅(qū)動表，下面的記錄稱之為是被驅(qū)動表，有時候優(yōu)化器會針對驅(qū)動表和被驅(qū)動表做一個替換，表示在一個大的查詢語句中每一個select關(guān)鍵字都對應著一個唯一的id，查詢語句中有兩個表那么就一共有兩條記錄，但是select關(guān)鍵字一共只有一個，那么id也就只有一個，一層嵌套一個select，那么就代表有一個id是外查詢，有一個id是內(nèi)查詢，內(nèi)查詢的id比外查詢的大

1)查詢優(yōu)化器可能對涉及到子查詢的查詢語句進行重寫，轉(zhuǎn)化成多表查詢的操作

2)union去重，內(nèi)部需要創(chuàng)建臨時表來去重，unionall不去重

3)select_type:可以確定select關(guān)鍵字對應的查詢的類型，確定小查詢在大查詢中扮演了一個什么樣子，普通查詢和連接查詢都是simple類型(不包含union，不包含子查詢)

1)查詢語句中不包含UNION或者子查詢的查詢都算作是SIMPLE類型

2)union前面是primary 后面是union

3)對于子查詢來說，最左邊就是primary，該子查詢的第一個SELECT關(guān)鍵字代表的那個查詢的select_type就是SUBQUERY,該子查詢沒有轉(zhuǎn)化成多表連接

4)最終轉(zhuǎn)化成了多表連接:

4)type:

1)system:當表中只有一條記錄況且存儲引擎是MYSIM的時候因為統(tǒng)計記錄時精確的，查詢就是sysyem，越在前面效果越好，MYSIM存儲引擎內(nèi)部維護一個變量來記錄表中字段的個數(shù)，但是在innodb中，它統(tǒng)計數(shù)據(jù)就不是精確的了，沒有維護內(nèi)部變量，此時即使是數(shù)據(jù)中只一條記錄，也是走的是全表掃描；

2)const:當根據(jù)主鍵或者是唯一二級索引與常數(shù)進行等值匹配的時候，對于單表的訪問方法就是const，假設(shè)現(xiàn)在User表中的userID是主鍵，username是唯一索引，此時針對于這兩個字段進行等值匹配的時候，type的類型是const

 create table user(userID int,username varchar(30),primary key(userID),unique index username_index(username));

3)eq_ref:再進行連接查詢的時候，如果被驅(qū)動表是通過主鍵或者是唯一二級索引等值匹配的方式來進行訪問的，如果該主鍵或者是唯一二級索引是聯(lián)合索引的話，所有的索引列都必須進行等值比較，此是被驅(qū)動表的訪問方法就是eq_ref?

select * from user where username=123，此時這個字符串沒有加雙引號，此時就會使用到函數(shù)，此時就會索引失效

select * from s1 inner join s2 on s1.id=s2.id

在s2表中找到值和s1.id進行相同的記錄，此時的查詢過程是在s1中取出每一個id值然后去s2表中去查找有沒有s2的id和s1相同的，所以針對s1是全表掃描針對于s2是使用到了索引

4)ref:當通過普通的二級索引和常量進行等值匹配的時候來查詢這張表，那么此時的查詢結(jié)果就是ref

5)ref_or_null:當對普通二級索引進行等值匹配的時候，該索引列的值也可以是null的時候，那么對該表的訪問方法就是ref_or_null

6)index merage:當時用到or的時候

7)unique_subquery:驅(qū)動表是全表掃描

8)range:如果使用索引來獲取某些范圍區(qū)間內(nèi)的記錄，那么就有可能使用到range訪問方法

9)index:可以使用到覆蓋索引，但是需要掃描全部的索引記錄的時候就會使用到index

ref使用非唯一索引掃描或唯一索引前綴掃描，返回單條記錄，常出現(xiàn)在關(guān)聯(lián)查詢中
eq_ref?類似ref，區(qū)別在于使用的是唯一索引，使用主鍵的關(guān)聯(lián)查詢

5)key:真實使用到的索引；

6)possible keys:可能是用到的索引

7)key_len:實際使用到的索引長度，主要針對于聯(lián)合索引有參考意義

MYSQL，int占用四個字節(jié)，如果說int類型的變量可以是null，那么實際使用到的索引長度會多一個字節(jié)，針對于字符串類型，MYSQL中utf-8編碼字符類型占用三個字節(jié)，null的情況1個字節(jié)，還要使用2個字節(jié)來記錄實際長度，因為varchar長度是不確定的，針對于聯(lián)合索引來說，聯(lián)合索引使用到的索引長度越長，那么查詢效果越好，如果沒有使用到索引，那么key_len長度是0；

8)ref:當時用到索引或者是等值查詢的時候，與索引列進行等值查詢的匹配的對象信息，比如說只是一個常數(shù)或者是某一個列

rows和fiter可以聯(lián)合在一起進行查看:

rows:預估的需要讀取的記錄數(shù)，值越少越好，值越少，頁越少

fiter:rows中能夠查詢到記錄的概率的百分比，百分比越高越好

1)這個SQL語句表示的是382條數(shù)據(jù)滿足key1>'z"其中只有百分之10的記錄滿足common_field="a"

2)下面中的這個SQL語句代表的是，一共有9895條記錄中只有10%的數(shù)據(jù)滿足s1.common_field='a'，然后MYSQL再拿著10%的驅(qū)動表的記錄去匹配被驅(qū)動表進行連表查詢

10)extra:

1)no tables used:沒有任何表被使用

2)imposble where

?3)using where:沒有使用到任何的索引，此時針對于s1沒有任何的索引，就是當使用全表掃描的時候完成針對于某一張表的查詢，并且該where與具有搜索條件但是還沒有索引

4)using index:使用覆蓋索引，建議使用覆蓋索引，聯(lián)合索引使用覆蓋索引比較好

key1本身有索引，這里就是用到了覆蓋索引

5)using condition:有些搜索條件雖然使用到了索引列，但是卻不能夠使用索引，索引下推是再進行索引遍歷過程中對索引中的字段先做判斷，直接過濾掉不滿足要求的紀錄，從而減少回表的次數(shù)

1)先找key1>"z"的所有記錄，然后進行回表查詢，此時再從主鍵索引中篩選key1 like "a%"的記錄，此時回表次數(shù)比較多；

2)下面這種情況，針對于key1>'z'使用到了索引，索引遍歷過程中，然后再根據(jù)key1進行過濾掉不滿足要求的紀錄，甚至此時經(jīng)過過濾完成之后，符合兩個條件的記錄一個主鍵ID值都沒有，此時就不用再進行回表查詢了，此時回表次數(shù)相比于第一次來說比較少很多

6)再進行連接查詢的過程中，當被驅(qū)動表不能有效地利用索引來加快訪問速度，MYSQL會為其分配一塊名字是join buffer的內(nèi)存塊來加快查詢速度

下面common field沒有索引

7)not exists:此時的id字段是一個主鍵，主鍵是不可能是null的

8)?zero limt:

9)using intersect

10)using filesort:不能使用已經(jīng)排好序的B+樹了，顯然性能很低下，當我們出現(xiàn)這個字段的時候是很有必要給這個字段加上一個索引，所以說如果某一次查詢需要使用到文件排序是在的的方式來進行查詢那么此時就會在執(zhí)行計劃中的Extra列顯示using filesort

下面的字段針對于common_field字段是用排序會使用到文件排序

11)using tempory:比如說要進行去重操作的時候如果沒有使用到索引，group by distinct

是使用到臨時表，有時候是用到臨時表進行去重，但是索引本身已經(jīng)是有序的的

十八)SQL優(yōu)化:?

一)關(guān)聯(lián)查詢優(yōu)化:

左外連接:

在explain執(zhí)行計劃中，上面是驅(qū)動表，下面是被驅(qū)動表

1)外層循環(huán)是驅(qū)動表，內(nèi)層循環(huán)是被驅(qū)動表，是從外層循環(huán)的驅(qū)動表中取一條數(shù)據(jù)，然后根據(jù)連接條件，然后去被驅(qū)動表中去查找匹配關(guān)系，正常來說是從驅(qū)動表中取一條數(shù)據(jù)，在被驅(qū)動表中去進行遍歷，根據(jù)指定的條件進行篩選，然后再從驅(qū)動表中再去一條數(shù)據(jù)，假設(shè)驅(qū)動表中有20條記錄，被驅(qū)動表中有30條記錄，那么一共要鏈接的次數(shù)就是20*30

假設(shè)A表中的個數(shù)是A個，B表中的個數(shù)是B個，從下面的公式來看，A越小越好，所以選擇小表驅(qū)動大表

2)此時兩張表中的字段都沒有都沒有索引，從這里面可以看出使用joinbuffer將被驅(qū)動表的數(shù)據(jù)存儲起來

?

3)此時嘗試給被驅(qū)動表添加索引:可以提升查詢速度

?create index classID_index on class(classID);

內(nèi)連接:優(yōu)化其實有權(quán)利決定誰是驅(qū)動表誰是被驅(qū)動表，優(yōu)先選擇有索引的作為被驅(qū)動表，對于內(nèi)連接來說，如果表的連接條件只能有一個字段作為索引，那么有索引的字段所在的表會被作為被驅(qū)動表，對于內(nèi)連接來說查詢優(yōu)化器是可以決定誰作為驅(qū)動表，誰作為被驅(qū)動表出現(xiàn)的，給被驅(qū)動表加索引，如果表的連接條件中只能有一個字段有索引，那么有索引的這個字段將會作為被驅(qū)動表出現(xiàn)

對于內(nèi)連接來說，在兩個表的連接條件都存在索引的情況下，會選擇小表(數(shù)據(jù)量小的表)作為驅(qū)動表出現(xiàn)，小表驅(qū)動大表，多表關(guān)聯(lián)查詢時，小表在前，大表在后，還要針對于大表建立索引

join的底層實現(xiàn)原理:

簡單循環(huán)嵌套算法執(zhí)行效率太慢，所以要給被驅(qū)動表添加索引，當每一次取出驅(qū)動表的的一條記錄的時候就不需要再去循環(huán)掃描被驅(qū)動表了，而是直接根據(jù)索引和查詢條件來確定被驅(qū)動表中的正確的數(shù)據(jù)

但是這個時候，對于簡單循環(huán)嵌套算法來說，join原理，每一次取出驅(qū)動表中的數(shù)據(jù)的時候，都需要將被驅(qū)動表中的數(shù)據(jù)記載到內(nèi)存中，假設(shè)A表也就是驅(qū)動表中的數(shù)據(jù)有A條，那么就需要加載被驅(qū)動表A次，此時磁盤IO吃滿

1)盡量將驅(qū)動表和被驅(qū)動表都加載進來，盡可能多加載A，并加載B的所有記錄

2)如果可以一次性的將A全部加載到joinbuffer中，那最好了，B只需要加載一次就可以了

3)此時假設(shè)將A加載到joinbuffer中的次數(shù)是K次，直接將B加載到內(nèi)存K次也可以

4)在這里面緩存的不僅僅是查詢條件，還有select后面要展示的字段，所以說盡量不要使用select *，無用的列會占用join buffer的空間，join buffer中存放驅(qū)動表的條目數(shù)變少

5)有N個join關(guān)聯(lián)的sql會被分配N-1個join buffer，所以查詢的時候盡量減少不必要的字段，可以讓joinbuffer存放更多的列

used_column_size是A表中每一條的大小，小表驅(qū)動大表是為了減少外層循環(huán)的趟數(shù)

十八)索引覆蓋:?

覆蓋索引是指在某一次查詢里面，某個索引的值已經(jīng)包含了所有的查詢需求，此時不需要再次進行回表查詢了，假設(shè)下面的查詢針對于age建立了索引