大綱
1.磁碟資料頁的儲存結構
2.沒有索引資料庫如何搜尋資料
3.在表中插入資料時如何進行頁分裂
4.如何設計主鍵索引及如何根據主鍵索引查詢
5.索引的物理儲存結構
6.更新資料時自動維護的聚簇索引是什麼
7.針對主鍵之外的欄位建立的二級索引
8.插入資料時如何維護不同索引的B+樹
9.完整的MySQL的B+樹索引原理總結
10.聯合索引使用規則
11.在SQL裡進行排序時如何能使用索引
12.在SQL裡進行分組的時候如何才能使用索引
13.回表查詢對效能的損害以及什麼是索引覆蓋
14.設計索引的原則
15.設計索引的具體案例
1.磁碟資料頁的儲存結構
資料庫所有的資料都會存放到磁碟上的檔案，資料在檔案裡存放的物理格式就是資料頁，大量的資料頁會按順序一頁一頁存放的，兩兩相鄰的資料頁之間會採用雙向連結串列的格式互相引用。
資料頁在磁碟檔案裡就是一段資料，可能是二進位制或某特殊格式的資料。資料頁裡包含兩個指標：一個指標指向自己上一個資料頁的實體地址，一個指標指向自己下一個資料頁的實體地址。
在資料頁內部儲存的一行一行資料，也就是往表裡插入的一行一行資料。資料頁裡的每一行資料都會按照主鍵大小進行排序儲存，同時每一行資料都有指標指向下一行資料的位置，組成單向連結串列。
LRU連結串列、flush連結串列和free連結串列都是雙向連結串列，資料頁之間組成雙向連結串列，資料行之間組成單向連結串列。
2.沒有索引資料庫如何搜尋資料
(1)資料頁在磁碟檔案中的物理儲存結構
(2)查詢資料的情形分析
(1)資料頁在磁碟檔案中的物理儲存結構
資料頁之間組成雙向連結串列，資料頁內部的資料行組成單向連結串列。資料行會根據主鍵從小到大排序進行儲存，每個資料頁都會有一個頁目錄，頁目錄中存放的是每一個資料行的主鍵和所在槽位的對映關係。資料行會被分散儲存到不同的槽位裡，一個槽位會有多條資料行。
(2)查詢資料的情形分析
情形一：
假設要根據主鍵查詢某個表的一條資料，而且此時該表並沒有幾條資料，該表總共就一個資料頁。那麼查詢時就會首先到資料頁的頁目錄根據主鍵進行二分查詢，然後透過二分查詢在目錄裡迅速定位到主鍵對應的資料是在哪個槽位。接著到對應的槽位裡，遍歷槽位裡的每一行資料，這樣就能快速找到那個主鍵對應的資料。
情形二：
假設要根據非主鍵的其他欄位查詢某個表的一條資料：這時就沒有辦法透過頁目錄的主鍵來進行二分查詢了，只能進入到資料頁裡，依次遍歷單向連結串列裡的每一個資料行來查詢，這樣效能就很差。
情形三：
上述只是假設只有一個資料頁的情況，如果有很多資料頁又該如何查詢？當有大量資料頁的情況下，而且又沒有建立任何索引，那麼無論是根據主鍵查詢還是根據其他欄位進查詢，都只能全表掃描。
所謂全表掃描就是：首先將第一個資料頁從磁碟上讀取到記憶體Buffer Pool的快取頁裡。然後在該快取頁裡，如果是根據主鍵查詢，就在其頁目錄進行二分查詢；如果是根據其他欄位查詢，則只能遍歷快取頁中資料行的單向連結串列來查詢；如果第一個資料頁沒找到，只能根據資料頁的雙向連結串列去找下一個資料頁。然後讀取到Buffer Pool的快取頁裡，按照同樣方法繼續。依此類推，直到找到為止。
在沒有任何索引時，不管如何查詢資料，都是一個全表掃描的過程。首先根據雙向連結串列依次把磁碟上的資料頁載入到Buffer Pool的快取頁中，然後在Buffer Pool的快取頁內部來查詢資料，或是二分查詢頁目錄，或是遍歷資料頁中資料行的單向連結串列，來找出需要的那條資料。
3.在表中插入資料時如何進行頁分裂
(1)資料頁的儲存和查詢
(2)資料頁的內部構成
(3)頁分裂與主鍵值
(4)頁分裂、主鍵值與資料移動示例
(1)資料頁的儲存和查詢
資料頁之間會組成雙向連結串列，資料頁內部的資料行會組成單向連結串列，每個資料頁會根據資料行的主鍵和槽位構建一個頁目錄。
在沒有索引的情況下，所有的資料查詢在物理層面都是全表掃描。也就是首先依次遍歷每個資料頁，然後掃描其內部的每個資料行。
(2)資料頁的內部構成
正常情況下往一個表插入一些資料後，都會進入到一個資料頁裡。在資料頁內部，這些資料會組成一個單向連結串列，如下圖示：
可以看到，資料頁裡面就是一行一行的資料。剛開始第一行是行型別為2的起始行，代表最小的一行。然後起始行有一個指標指向了下一行資料，每一行資料都有自己每個欄位的值，每一行會透過一個指標不停的指向下一行資料。其中普通資料行的行型別為0，最後一行是行型別為3的結束行，代表最大的一行。這就是一個典型的資料頁內部的情況。
(3)頁分裂與主鍵值
一.什麼時候會出現頁分裂
在InnoDB的一個表裡不停的插入資料時，會涉及到一個頁分裂的過程。正是由於頁分裂，才產生了一個個資料頁。
假設不停往表裡插入資料，那麼剛開始會不停往一個資料頁插入資料。插入的資料越多，這個資料頁就越大，這時就需要一個新的資料頁。
但是此時會遇到一個問題，就是索引運作的一個核心基礎，即要求後一個資料頁的主鍵值都大於前面一個資料頁的主鍵值。如果資料的主鍵是自增的，那這一點是可以保證的，因為新插入後一個資料頁的主鍵值一定都大於前一個資料頁的主鍵值。
但有時主鍵並不是自增長的，所以可能會出現如下的情況：後一個資料頁的資料行裡，有的主鍵是小於前一個資料頁的主鍵值。比如第一個資料頁裡有一條資料的主鍵是10，第二個資料頁裡出現一條資料的主鍵值是8，這時就有問題。所以此時就會出現一個過程，叫做頁分裂。
二.什麼是頁分裂
就是萬一主鍵不是自增而是自定義的，那麼在增加一個新的資料頁時，會把前一個資料頁裡主鍵值較大的資料行，移動到新的資料頁裡。然後把新插入的主鍵值較小的資料行，移動到上一個資料頁裡。從而保證新資料頁裡的主鍵值一定比上一個資料頁裡的主鍵值都大。
(4)頁分裂、主鍵值與資料移動示例
假設新資料頁裡，有兩條資料的主鍵值明顯小於上一個資料頁的主鍵值：第一個資料頁有1、5、6三條資料，第二個資料頁有2、3、4三條資料。如下圖示：
由於第二個資料頁裡的主鍵值比第一個資料頁裡的兩個主鍵5和6小，於是這時就會出現頁分裂行為。也就是會把第二個資料頁裡的兩條資料2和3移動到第一個資料頁中，同時把第一個資料頁裡的兩條資料5和6移動到第二個資料頁中。
4.如何設計主鍵索引及如何根據主鍵索引查詢
(1)資料頁分裂的過程
(2)根據主鍵索引查詢的過程
(3)基於主鍵的索引是如何設計的
(4)如何根據主鍵索引查詢
(1)資料頁分裂的過程
在不停往表裡插入資料時，會建立一個一個的資料頁。如果資料主鍵不是自增，就可能會出現資料行跨頁移動。以此保證下一個資料頁主鍵值都大於上一個資料頁主鍵值。
(2)根據主鍵索引查詢的過程
如果要查詢主鍵id=4的資料，由於不知道在哪個資料頁，只能全表掃描。從第一個資料頁開始，遍歷每個資料頁都進入到其頁目錄裡查詢主鍵。最壞的情況下，所有資料頁都得遍歷一遍。
(3)基於主鍵的索引是如何設計的
此時需針對主鍵設計一個索引，針對主鍵的索引實際上就是主鍵目錄。主鍵目錄會把每個資料頁的頁號，還有資料頁裡最小的主鍵值放在一起，從而形成一個主鍵的目錄，如下圖示：
(4)如何根據主鍵索引查詢
有了主鍵目錄，查詢時就可以直接到主鍵目錄去查詢了。透過和最小主鍵值進行對比，就可以快速定位資料在哪個資料頁中，之後便可以到對應的資料頁中，根據主鍵值找出要找的資料。類似這種主鍵目錄，就可以認為是索引目錄。
5.索引的物理儲存結構
既然索引目錄裡會包含每個資料頁和它的最小索引值。那麼查詢主鍵值時，透過二分查詢索引目錄就能快速定位對應的資料頁。接著進入對應的資料頁的頁目錄，繼續二分查詢就能快速定位所查資料。
但是現在問題來了，表裡的資料可能很多，比如有幾百萬甚至上千萬。此時有大量資料頁，於是索引目錄就要儲存大量的資料頁和最小索引值。那麼應該如何儲存索引目錄？
MySQL將索引目錄儲存在專門的資料頁上，這些資料頁稱為索引頁；
如果有很多資料頁，此時就需要很多索引頁。為了快速查詢索引頁的資料，可從索引頁的基礎上多加一個層級出來。在更高的索引層級裡，儲存每個索引頁和索引頁裡的最小主鍵值。
現在假設要查詢id=46：首先可以到最頂層的索引頁35裡去找，直接透過二分查詢定位到下一步應該到索引頁20裡去找。接著到索引頁20裡透過二分查詢也能很快定位到資料應該在資料頁8裡。再進入資料頁8裡，就可以根據頁目錄+二分查詢定位id=46的那行資料。
假如最頂層的那個索引頁35存放的下層索引頁的頁號太多了，怎麼辦？此時可以再次進行頁分裂，增加一層索引頁：
其實這就是一棵B+樹，所以才說MySQL的索引是用B+樹來組成的。當資料庫為一個表的主鍵建立索引後，這個主鍵的索引就是一棵B+樹。當資料庫需要根據主鍵來查詢資料時，會從B+樹的頂層開始二分查詢。一層一層往下定位，最終一直定位到一個資料頁裡，然後在資料頁內部的目錄裡繼續二分查詢，最後找到那條資料。
以上就是索引的物理儲存結構：
一.採用和資料頁一樣的索引頁來儲存索引資料；
二.一個索引就是很多索引頁組成的一棵B+樹；
6.更新資料時自動維護的聚簇索引是什麼
(1)基於索引資料結構去查詢主鍵的過程
(2)聚簇索引是什麼
(1)基於索引資料結構去查詢主鍵的過程
假設要查詢一個主鍵id對應的行，則資料庫會先去查詢頂層的索引頁88。然後透過二分查詢的方式，很容易定位到應該去下層哪個索引頁裡去繼續找。
假設定位到了下層的索引頁35，在索引頁35裡也有一些索引條目。這些索引條目分別是下層索引頁(20,28)和它們的最小主鍵值。
此時進入索引頁35裡二分查詢，可定位到應該到下層的哪個索引頁查詢。假設從索引頁35接著找，就找到應該到下層的索引頁28裡去繼續查詢。
此時索引頁28裡肯定也是有索引條目的，裡面存放了部分資料頁頁號。假設索引頁28存放了資料頁2和資料頁8以及每個資料頁最小的主鍵值。
於是在索引頁28繼續二分查詢，就可定位到應該到哪個資料頁裡去找。假設需要進入資料頁2，而資料頁2裡就會有一個頁目錄。在資料頁2的頁目錄裡存放了各行資料的主鍵值和行的實際物理位置。
於是繼續在資料頁2的頁目錄裡二分查詢，就可以快速定位到要查詢的主鍵值對應的資料行的物理位置，最後便可以在資料頁2裡找到對應的資料。這就是基於索引資料結構去查詢主鍵的過程。
(2)聚簇索引是什麼
其實最下層的索引頁，都會有指標引用資料頁的，所以實際上索引頁之間跟資料頁之間會透過指標連線起來。在索引頁內部，同一個層級的索引頁互相之間會基於指標組成雙向連結串列，就和資料頁之間可以組成雙向連結串列一樣。
假設把索引頁和資料頁綜合起來看，它們都是連線在一起的，看起來就如同一棵完整的大的B+樹一樣。從根索引頁88開始，一直到所有的資料頁，會組成一棵巨大的B+樹。在這棵樹裡最底層的一層就是資料頁，而資料頁就是B+樹裡的葉子節點。
所以如果一棵大的B+樹索引資料結構裡，葉子節點就是資料頁自己本身。那麼此時我們就可以稱這棵B+樹索引為聚簇索引。因此上圖中所有的索引頁+資料頁組成的B+樹，就是聚簇索引。
一般來說，即便是億級的大表，所建的索引的層級也就三四層而已。這個聚簇索引預設是按主鍵來組織的，所以資料庫在增刪改資料的時候，會更新資料頁，同時自動維護B+樹結構的聚簇索引，新增和更新索引頁。
7.針對主鍵之外的欄位建立的二級索引
(1)聚簇索引和主鍵搜尋
(2)對主鍵外的其他欄位建立索引的原理
(1)聚簇索引和主鍵搜尋
聚簇索引就是Innodb儲存引擎預設建立的一個基於主鍵的索引結構，而且表裡的資料預設就是直接放在聚簇索引裡的。聚簇索引的葉子節點就是資料頁自己本身。
對主鍵資料的搜尋，其實就是從聚簇索引的根節點開始進行二分查詢。一直找到對應的資料頁裡，最後基於頁目錄來二分查詢定位到資料行。
(2)對主鍵外的其他欄位建立索引的原理
一.該索引也維護B+樹來存放索引和主鍵欄位
二.該索引也按索引值排序及構建多層級索引頁
三.按該索引查詢完整資料要回表查詢主鍵索引
四.聯合索引的排序規則和查詢
一.該索引也維護B+樹來存放索引和主鍵欄位
假設需要對其他欄位建立索引，比如name欄位。那麼資料庫就需要維護一個聚簇索引和一個name欄位的索引，即要維護聚簇索引的B+樹，也要維護name欄位索引的B+樹。
所以插入資料時，會先把完整資料插入到聚簇索引的葉子節點的資料頁。再把部分資料(主鍵 + name)插入到name欄位索引的葉子節點的資料頁。
二.該索引也按索引值排序及構建多層級索引頁
需要注意：name欄位索引是獨立於聚簇索引之外的一棵索引B+樹。
name欄位的索引B+樹裡的葉子節點的資料頁只放主鍵和name欄位的值。name的排序規則和主鍵的排序規則一樣，也按name值的大小進行排序，也就是下一個資料頁的name欄位值要大於上一個資料頁的name欄位值。
然後name欄位的索引B+樹也會構建多層級的索引頁，這個索引頁裡存放的就是下一層的頁號和最小name欄位值。
三.按該索引查詢完整資料要回表查詢主鍵索引
如果要根據name欄位來搜尋資料，也會從name欄位的索引B+樹裡的根節點開始查詢，一層一層往下找，一直找到葉子節點的資料頁為止。
但找到葉子節點的資料頁，也僅僅是找到對應的主鍵值，還不能找到這行資料完整的所有欄位，所以這時候還需要進行回表查詢。
這個回表，指的是還需要根據主鍵值，到聚簇索引裡從根節點開始查詢。一直找到葉子節點的資料頁，才能定位到主鍵對應的完整資料行。
由於根據name欄位索引B+樹找到主鍵後，還要根據主鍵去聚簇索引找，所以一般把name欄位這種普通欄位的索引稱為二級索引。相對應的，一級索引就是聚簇索引。
四.聯合索引的排序規則和查詢
此外也可以把多個欄位聯合起來，建立聯合索引，比如name + age。此時聯合索引的執行原理也是一樣的，只不過是新建一棵獨立的B+樹。這棵新的B+樹的葉子節點的資料頁裡會存放id + name + age。
聯合索引name + age的排序規則是預設按name排序，name一樣就按age排序。不同資料頁之間的name + age值的排序也是如此。
在這個name + age的聯合索引的B+樹的索引頁裡，放的就是下層節點的頁號和最小的name + age的值。所以根據name + age查詢時，就會使用name + age聯合索引這棵B+樹。搜尋到主鍵之後再根據主鍵到聚簇索引裡搜尋。
以上就是Innodb儲存引擎的索引完整實現原理。
8.插入資料時如何維護不同索引的B+樹
(1)MySQL插入資料時聚簇索引的維護過程
(2)MySQL插入資料時二級索引的維護過程
(1)MySQL插入資料時聚簇索引的維護過程
一.建立表時只有一個資料頁
二.只有一個資料頁時的查詢
三.根頁分裂 -> 根頁成為索引頁
四.資料頁分裂 -> 索引頁分裂
一.建立表時只有一個資料頁
首先一個表完成建立後，它就是一個資料頁。這個資料頁屬於聚簇索引的一部分，而且當前還是空的。此時MySQL插入資料，會直接往這個資料頁裡插入，不用新增索引頁。
二.只有一個資料頁時的查詢
這時這個初始的資料頁其實就是一個根頁。由於每個資料頁內預設有一個基於主鍵的頁目錄，所以可根據主鍵來查。查詢時直接在這唯一的資料頁中，透過頁目錄來查詢即可。
三.根頁分裂 -> 根頁成為索引頁
隨後MySQL會往表插入越來越多的資料，此時資料頁滿了，就會新增兩個資料頁，把根頁裡的資料都複製到新的兩個資料頁上，並且會按照主鍵值大小進行複製，讓兩個新的資料頁能根據主鍵值排序，確保第二個資料頁的主鍵值都大於第一個資料頁的主鍵值。
複製完成後根頁會升級為索引頁，這個索引頁裡放的是兩個新增資料頁的頁號和它們裡面最小的主鍵值。如下圖示：唯一一個資料頁(根頁)滿了之後，根頁會成為索引頁並引用兩個資料頁。
四.資料頁分裂 -> 索引頁分裂
之後MySQL繼續往表裡插入資料，然後資料頁會不停的進行頁分裂，分裂出越來越多的資料頁。此時唯一的一個索引頁(根頁)裡面的索引條目也越來越多，當這個索引頁也放不下時，這個索引頁也會分裂成兩個索引頁。原來唯一的根頁會往上提一個層級，然後引用這個兩個索引頁。
五.以此類推
當插入的資料繼續增多，資料頁越來越多，根頁指向的索引頁也會不停的分裂，索引頁也越來越多。當根頁下面的索引頁數量太多時，一個根頁已經放不下所有的索引頁了。這時會導致根頁也分裂成多個索引頁，根頁再次往上提一個層級。
以上就是插入資料時，聚簇索引的維護過程。
(2)MySQL插入資料時二級索引的維護過程
假設name欄位有索引，那麼剛開始插入資料時：首先會在聚簇索引的唯一資料頁裡插入資料，然後會在name欄位索引B+樹的唯一資料頁裡插入資料。隨後插入的資料越來越多，name欄位索引B+樹的唯一資料頁也會分裂，分裂過程和聚簇索引一樣。
所以MySQL插入資料時，本身就會自動維護各個索引的B+樹。在name欄位索引B+樹的索引頁中，除存放頁號和最小name欄位值外，每個索引頁還會存放最小name欄位值對應的主鍵值。
9.MySQL的B+樹索引原理總結
(1)聚簇索引的葉子節點才是資料頁
(2)索引頁或資料頁之間組成雙向連結串列 + 頁內記錄組成單向連結串列
(3)高效查詢原因
(4)二級索引的維護
(5)基於二級索引的查詢與回表
(6)建立索引的好處和壞處
B+樹索引的資料結構、排序規則、插入時索引頁的形成過程、基於B+樹的查詢原理、不同欄位的索引有獨立B+樹、回表的過程。
(1)聚簇索引的葉子節點才是資料頁
預設情況下MySQL建立的聚簇索引都是基於主鍵值來組織的。聚簇索引的葉子節點都是資料頁，裡面放的就是插入的完整資料。
(2)索引頁或資料頁之間組成雙向連結串列 + 頁內記錄組成單向連結串列
資料頁或索引頁裡的記錄會組成一個單向連結串列，按資料大小有序排列。資料頁或索引頁之間會組成一個雙向連結串列，按資料大小有序排列。
(3)高效查詢原因
正是因為這個有序的B+樹索引結構，才能讓MySQL查詢資料時，從B+樹的根節點開始，按資料值大小透過二分查詢一層一層往下高效查。
(4)二級索引的維護
如果針對主鍵外的欄位建立索引，那麼本質上就是為那個欄位的值重新建立另外一棵B+樹索引。這棵索引B+樹的葉子節點也是資料頁，存放的只有欄位的值和主鍵值。而每層索引頁存放的都是下層索引頁或資料頁的引用。
(5)基於二級索引的查詢與回表
假設要根據非主鍵欄位的索引來查詢一行完整的資料。首先會基於非主鍵欄位的索引B+樹，快速查詢到那個值所對應的主鍵值。然後再根據這個主鍵值，去主鍵的聚簇索引B+樹裡，進行回表查詢，即重新從根節點開始查詢那個主鍵值。最後找到主鍵值對應的完整資料。
(6)建立索引的好處和壞處
好處是不需要全表搜尋，可以將查詢效能提升得很高。因為可以直接根據某個欄位的索引B+樹來進行高效查詢資料。
壞處有兩點，分空間上和時間上兩個方面。空間上要給很多欄位建立索引，建立很多棵索引B+樹，很耗費磁碟空間。時間上在增刪改時，每次都需要維護各個索引的資料有序性。因為每個索引B+樹都要求頁內按照索引值大小排序，索引頁之間有序。而不停的增刪改會導致資料頁不停分裂、不停增加索引頁，很耗費時間。
所以一般不建議一個表裡設定太多索引。
10.聯合索引使用規則
(1)規則一：等值匹配規則
(2)規則二：最左側列匹配規則
(3)規則三：最左字首匹配規則
(4)規則四：範圍查詢規則
(5)規則五：等值匹配 + 範圍匹配的規則
(6)如何判斷聯合索引的欄位是否還在生效
聯合索引中的資料頁或索引頁是按照聯合索引的欄位順序進行排序的。先根據第一個欄位排序，再根據第二個欄位排序，依此來建立順序關係。
(1)規則一：等值匹配規則
在where語句中的欄位名稱和聯合索引的欄位完全一樣，而且都是基於等號的等值匹配，那麼肯定會用上聯合索引。
即便where語句裡的欄位順序和聯合索引裡的欄位順序不一致，MySQL也會自動最佳化按聯合索引的欄位順序去找。
(2)規則二：最左側列匹配規則
假設聯合索引是key(class_name, student_name, subject_name)，那麼不一定要在where語句里根據這三個欄位來查。其實只要根據最左側的部分欄位，也是可以利用索引進行查詢的。在聯合索引的B+樹裡，必須先按class_name查，再按student_name查，不能跳過前面兩個欄位，直接按最後一個subject_name來查。由如下例子可知，聯合索引最好是區分度大的排前面。
//可以利用上聯合索引來查
mysql> select * from student_score where class_name = '' and student_name = '';
//不能利用聯合索引來查
mysql> select * from student_score where subject_name = '';
//只有class_name的值可以在聯合索引裡搜尋，剩下的subject_name沒法利用聯合索引搜尋
mysql> select * from student_score where class_name = '' and subject_name = '';(3)規則三：最左字首匹配規則
一.假如使用的是 like '1%' 查詢
//查詢所有1開頭的班級的分數，那麼也是可以用到聯合索引的
mysql> select * from student_score where class_name like '1%';那麼這也是可以用到聯合索引的，因為在聯合索引的B+樹裡，都是按class_name排序的。所以要是給出class_name的確定的最左字首就是1，然後後面給的一個模糊匹配符號，那也可以基於索引來查詢。
二.假如使用的是 like '%班' 查詢
在左側用一個模糊匹配符，那麼就沒法用聯合索引。
(4)規則四：範圍查詢規則
使用如下範圍查詢，也可以利用上聯合索引：
//可以用上聯合索引
mysql> select * from student_score where class_name > '1班' and class_name < '5班';因為索引的最下層的資料頁都是按順序組成雙成雙向連結串列的，所以完全可以先找到'1班'對應的資料頁，再找到'5班'對應的資料頁。兩個資料頁中間的那些資料頁，就全都是在所查範圍內的資料。但是如下的語句只有class_name是可以基於聯合索引來查，student_name的範圍是沒法使用到聯合索引的：
//只有class_name是可以使用聯合索引，student_name的範圍是沒法使用到聯合索引
mysql> select * from student_score where class_name > '1班' and class_name < '5班' and student_name > '';所以如果使用的where語句裡有範圍查詢，那麼只有對聯合索引最左側的列進行範圍查詢才能用到索引。
(5)規則五：等值匹配 + 範圍匹配的規則
mysql> select * from student_score where class_name = '1班' and student_name > '' and subject_name < '';該語句可以使用class_name在聯合索引裡精準定位到一堆資料。然後這堆資料裡的student_name都是按照順序排列的，所以student_name > ''也基於索引來查詢，但接下來的student_name < ''是不能用索引的。
(6)如何判斷聯合索引的欄位是否還在生效
可以根據，前面的欄位按照索引篩選後，得出的結果是否還能按後面的欄位順序排列，來判斷後面的欄位的索引是否有效。
11.在SQL裡進行排序時如何能使用索引
當SQL語句裡使用where語句進行資料過濾和篩選時，從聯合索引最左側的欄位開始去使用，保證前一個欄位的篩選結果能按當前欄位順序排列，這樣當前欄位就能用上索引樹。
當SQL語句裡使用order by語句進行排序的時候：儘量按照聯合索引的欄位順序去進行order by排序，從而利用上聯合索引B+樹裡的資料有序性。
但要注意，按多個欄位排序時，這多個欄位要麼都是升序要麼都是降序。不能出現一個欄位升序一個欄位降序，否則只能先基於where語句篩選出資料，然後放到記憶體或臨時磁碟檔案，接著再透過排序演算法按照某個欄位來進行排序。
基於臨時磁碟檔案來排序，MySQL裡叫做filesort。
12.在SQL裡進行分組的時候如何才能使用索引
假設要執行一個類似如下的語句：
mysql> select count(*) from table group by xxx;似乎需要把所有的資料都放到一個臨時磁碟檔案加上部分記憶體，按照指定欄位分成一組一組，然後對每一組都執行一個聚合函式，這樣會涉及大量的磁碟互動，效能很差。
所以通常而言，對於group by後的欄位，最好也是按照聯合索引裡的最左側的欄位開始，按順序排列開來。這樣就可以完美的利用索引來直接提取一組一組的資料，然後針對每一組的資料執行聚合函式即可。
其實group by和order by用上索引的原理和條件都是一樣的。如果在group by後的欄位順序和聯合索引中最左側開始的欄位順序一致，那麼就可以充分利用索引樹裡已經完成排序的特性。
所以進行表設計時，通常設計兩三個常用的索引，覆蓋常見的where篩選、order by排序和group by分組的需求，保證常見SQL都能用上索引，系統跑起來不會有太大的查詢效能問題。
13.回表查詢對效能的損害以及什麼是索引覆蓋
(1)按索引欄位查詢非索引欄位的值需要回表
(2)回表操作可能不使用聯合索引而用全表掃描
(3)有限制條數的回表操作還是會使用聯合索引
(4)索引覆蓋會在索引樹上獲取資料無需回表
(1)按索引欄位查詢非索引欄位的值需要回表
一般我們自己建的索引不管是單列索引還是聯合索引：一個索引就對應著一棵獨立的索引B+樹，其節點僅包含索引裡的欄位的值和主鍵值。
所以即使根據索引樹按照條件找到了需要的資料，這些資料也僅僅是索引裡的幾個欄位的值和主鍵值。萬一查詢的是其他非索引裡的欄位，那還需要進行回表操作，也就是根據主鍵到聚簇索引裡把所需欄位提取出來。
(2)回表操作可能不使用聯合索引而用全表掃描
比如，類似如下的查詢語句：
mysql> select * from table order by xx1,xx2,xx3;可能先從聯合索引的索引樹裡按照順序取出所有資料，然後每條資料都根據主鍵去聚簇索引查詢，這樣效能也不高。甚至有時MySQL的執行引擎可能會認為：這種語句相當於把聯合索引和聚簇索引都掃描一遍，還不如不去使用聯合索引，而直接全表掃描。
(3)有限制條數的回表操作還是會使用聯合索引
但是類似如下這樣的語句：
mysql> select * from table order by xx1,xx2,xx3 limit 10;MySQL的執行引擎就會知道，需要掃描聯合索引的索引樹拿到10條資料。然後對10條資料在聚簇索引裡查詢10次，那麼還是會使用聯合索引的。
(4)索引覆蓋會在索引樹上獲取資料無需回表
索引覆蓋不是一種索引，而是基於索引查詢的一種方式。需要的欄位直接在索引樹裡就能提取出來，不需要回表到聚簇索引，這樣的查詢方式就是索引覆蓋。
(5)總結
儘量在SQL裡指定只需要的幾個欄位，不要將非索引裡的欄位也查出來。這樣就能利用起索引覆蓋的方式，不用回表查聚簇索引。即便需要回表查聚簇索引，儘量使用limit來限定回表聚簇索引的次數。
14.設計索引的原則
(1)設計的索引儘量包含上where、order by、group by裡的欄位
(2)根據離散度來選擇索引欄位，儘量使用基數較的、值多的欄位
(3)一個表的索引不要太多，最好兩三個聯合索引能覆蓋掉全部查詢
(4)查詢列舉欄位時比如性別可透過in的方式改造成使用聯合索引
(5)經常使用範圍查詢的欄位最好可以放在聯合索引最後面
(6)針對低基數字段的查詢可以使用id設計輔助索引來處理
(1)設計的索引儘量包含上where、order by、group by裡的欄位
設計兩三個聯合索引，要儘量包含where、order by、group by裡的欄位。儘量讓where、order by、group by後的欄位順序，都能命中聯合索引。
(2)根據離散度來選擇索引欄位，儘量使用基數較的、值多的欄位
這樣才能發揮出B+樹快速二分查詢的優勢。同時，儘量對欄位型別較小的欄位建立索引，如tinyint、varchar(255)。如果要建立索引的欄位的值太大，可以取欄位值的前20個字元建立索引。
比如：欄位name是varchar(255)型別，但需要基於欄位name建立索引。那麼可以建立這樣的索引key my_index(name(20),age,course)，不過這種索引只能用在where條件下，不能用在order by和group by條件。
但即便欄位基數較低，卻頻繁查詢的欄位，也可放到聯合索引的最左側。比如性別欄位、省市欄位等。
(3)一個表的索引不要太多，最好兩三個聯合索引能覆蓋掉全部查詢
索引太多必然導致對資料進行增刪改時效能很差。此外主鍵也最好自增，不要使用UUID之類的主鍵。
自增的主鍵可以保證聚簇索引不會頻繁的產生頁分裂。UUID的主鍵會導致聚簇索引頻繁的頁分裂，增刪效能查。
(4)查詢列舉欄位時比如性別可透過in的方式改造成使用聯合索引
某些查詢語句即便只需要聯合索引的部分欄位作為條件去查詢，也可以透過in語句的方式，把查詢語句改造成可以使用聯合索引。
比如key(city,sex,age)，雖然只需查where city = 'xxx' and age > xxx，也可以改造成where city = 'xxx' and sex in (0,1) and age > xxx。
也就是說，對於那些列舉欄位，查詢的時候也可以加進來作為條件。從而利用上聯合索引，不然原來的語句是利用不上索引的。
(5)經常使用範圍查詢的欄位最好可以放在聯合索引最後面
從而保證範圍查詢欄位前面的條件欄位也可以用到索引，比如上面的age就是範圍查詢使用得多。
(6)針對低基數字段的查詢可以使用id設計輔助索引來處理
比如：
mysql> select * from users where sex = 'man' limit x,y;可以增加輔助欄位score，然後建立索引key(sex, score)，查詢時使用：
mysql> select * from users where sex = 'man' order by score limit x,y;也可以使用id建立輔助索引：key_sex_id(sex,id)，查詢時使用：
mysql> select * from users where sex = 'man' order by id limit x,y;總之，儘量利用一兩個複雜的多欄位聯合索引，抗下80%以上的查詢。然後用一兩個輔助索引抗下剩餘20%的非典型查詢。
15.設計索引的具體案例
(1)在大部分情況下where篩選和order by排序是沒法都用到索引的
(2)where和order by索引衝突，通常讓where使用索引來快速篩選資料
(3)可以把基數太低的欄位(每次查詢幾乎用到)放到聯合索引最左側
(4)透過in語句的方式把查詢語句改造成可以使用聯合索引的查詢語句
(5)必須把經常用做範圍查詢的欄位放在聯合索引的最後一個
(6)可以把一些複雜的查詢條件轉換為列舉欄位
(7)透過輔助索引解決where條件裡都是基數低的欄位且還要排序後分頁
(1)在大部分情況下where篩選和order by排序是沒法都用到索引的
對於如下的SQL，假如就一個聯合索引key(age, score)，那麼where可以用上索引，但是排序是基於score欄位，此時不能用索引；假如針對age和score分別設計了兩個索引，SQL裡如果基於age索引進行篩選就不能利用score索引進行排序。
mysql> select xxx from user_info where age between 20 and 25 order by score limit 1,10;(2)where和order by索引衝突，通常讓where使用索引來快速篩選資料
當出現索引衝突時，是針對where設計索引還是針對order by設計索引？
這個問題的本質就是：是讓where語句先基於聯合索引去篩選出一部分使用者指定的資料，接著再把資料載入到記憶體或基於臨時磁碟檔案進行指定條件的排序，最後用limit語句拿到一頁資料；還是讓order by語句按照索引順序去找，找的過程中再基於where的條件篩選出指定資料，然後再根據limit語句拿出一頁資料。
其實一般都是讓where條件使用索引來快速篩選出一部分指定資料，接著再進行排序，最後再從排序後的資料中拿出一頁資料。因為基於索引進行where篩選能以最快速度篩選出需要的少部分資料，如果篩選出的資料量不是太大，那麼後續排序和分頁的成本就不會太大。
(3)可以把基數太低的欄位(每次查詢幾乎用到)放到聯合索引最左側
前面說過，基數太低的欄位最好別放到索引裡。假如某個SQL的where條件需要用到這幾個欄位：省份、城市和性別，由於其基數太小而不包含到聯合索引裡。那麼每次執行該SQL查詢時，只能先把這幾個欄位放在where條件最後，然後用聯合索引查出一部分資料，接著將這些資料載入到記憶體，再根據where條件的省份、城市和性別這幾個欄位進行過濾篩選。這樣每次查詢都得多這麼一個步驟了。
與其如此，還不如就把省份、城市和性別欄位，放在聯合索引的最左側。這樣跟其他欄位組合聯合索引後，大部分查詢都可以透過索引樹把where條件指定的資料篩選出來。
可以把基數較低但是頻繁查詢(幾乎每次查詢都會指定)的幾個欄位，放到聯合索引的最左側，這樣讓每次查詢時直接從索引樹裡進行篩選。
(4)透過in語句的方式把查詢語句改造成可以使用聯合索引的查詢語句
假設查詢時的where條件如下，而索引設計成(province, city, sex, age)；
where province=xx and city=xx and age between xx and xx此時因為age不在索引裡，所以就根本沒法透過age在索引裡進行篩選。但是如果把上述語句改寫成如下，那也是沒法讓age用上索引去篩選的。因為city和age中間差了一個sex，此時不符合最左側連續多個欄位原則。
where province=xx and city=xx and age>=xx and age<=xx針對這個問題，可以把where語句寫成如下所示：這樣就能讓整個where語句裡的條件全部都在索引樹裡進行篩選和搜尋。
where province=xx and city=xx and sex in ('female', 'male') and age >=xx and age<=xx假設在查詢語句裡還有一些頻繁使用的條件，比如興趣愛好和性格特點，而這個興趣愛好和性格特點，往往都是有固定的一些列舉值。那麼針對這些頻繁使用的列舉值欄位，也完全可以加入到聯合索引裡。從而設計成(province, city, sex, hobby, character, age)這樣的聯合索引。
此時假設出現了這樣一個查詢，按照省份、城市、性格和年齡進行搜尋，那麼SQL可以按如下in方式寫：
where province=xx and city=xx and sex in(xx, xx) and hobby in (xx, xx, xx, xx) and character=xx and age>=xx and age<=xx也就是說，即使不需按性別和愛好篩選，但在SQL裡可以對這兩個欄位用in語句，把所有列舉值都放進去。這樣就能讓province，city，character和age四個要篩選的欄位用上索引。
(5)必須把經常用做範圍查詢的欄位放在聯合索引的最後一個
為什麼一直強調age欄位必須要放在聯合索引的最後一個呢？因為索引規則中，假設where語句裡有等值匹配還有範圍匹配，此時必須先讓聯合索引最左側開始的多個欄位使用等值匹配，接著最後一個欄位是範圍匹配。
比如下面的語句，完全就是按照聯合索引最左側開始的。province、city、sex、hobby、character是聯合索引最左側的多個欄位，它們都是等值匹配，而最後一個age欄位使用的是範圍匹配，這種就可以完全用上索引。
where province=xx and city=xx and sex in(xx, xx) and hobby in (xx, xx, xx, xx) and character=xx and age>=xx and age<=xx但假設在聯合索引裡把age放在中間位置，設計一個類似(province, city, sex, age, hobby, character)的聯合索引。接著SQL寫成如下所示：
where province=xx and city=xx and sex in(xx, xx) and age>=xx and age<=xx and hobby in (xx, xx, xx, xx) and character=xx那麼只有province, city, sex, age幾個欄位可以用上索引。因為在SQL裡，一旦一個欄位做範圍查詢用到了索引，那麼這個欄位接下來的條件就都不能用索引了。所以必須把經常用做範圍查詢的欄位放在聯合索引的最後一個，這樣才能保證SQL裡每個欄位都能基於索引去查詢。
(6)可以把一些複雜的查詢條件轉換為列舉欄位
把索引設計成(province, city, sex, hobby, character, age)的原因總結：首先讓最頻繁查詢的一些條件都放到索引裡去。然後查詢時如果有些欄位是不使用的，可用in(所有列舉值)的方式寫SQL。同時對範圍查詢的age欄位必須放在最後一個，保證範圍查詢也用上索引。
接下來假設在查詢時還有一個條件：要根據使用者最近登入時間篩選最近7天登入過APP的使用者，而使用者表裡有一個叫latest_login_time的欄位。
要是在where條件裡直接加入一個latest_login_time <= 7天內的語句，那麼肯定是沒法用上索引，因為這裡會用一些計算或者函式來對比時間。而且假設where條件查詢裡還有age進行範圍查詢，那麼根據前面提到：範圍查詢時也就只有第一個範圍查詢是可以用上索引，第一個範圍查詢後的其他範圍查詢是用不上索引的。
也就是說，即使索引設計成這樣：
(province, city, sex, hobby, character, age, latest_login_time)然後where語句寫成如下這樣：
where xx xxx and age>=xx and age<=xxx and latest_login_time>=xx；雖然age和latest_login_time都在聯合索引裡，但按照規則，只有age範圍查詢可以用到索引，latest_login_time始終是用不到索引的。
所以此時有一個技巧，就是在設計表時，就必須考慮到這個問題。此時完全可以設計一個欄位為does_login_in_latest_7_days，也就是該使用者是否在最近7天內登入過APP。假設在7天內登入了這個APP，那麼這個欄位就是1，否則超過7天沒登入，這個欄位就是0。這樣就把一個篩選時間欄位的查詢轉換為了一個列舉值的欄位。接下來的解決方案就簡單化了，可以設計一個聯合索引為：
(province, city, sex, hobby, character, does_login_in_latest_7_days, age)然後查詢時就在where條件裡帶上一個does_login_in_latest_7_days=1，最後再跟上age範圍查詢，就可以讓where條件裡的欄位都用索引了。
(7)透過輔助索引解決where條件裡都是基數低的欄位且還要排序後分頁
萬一僅僅用聯合索引裡一些基數特別小的欄位來篩選，如基於性別篩選。那麼這樣一下子就會篩選出所有女性，可能有上百萬使用者資料。接著還要磁碟檔案進行排序再分頁，那這個效能可能就會極差了。
因此可針對那種基數很低的欄位 + 排序欄位單獨額外設計一個輔助索引，專門用於解決where條件裡都是基數低的欄位，且要進行排序後分頁。
比如可以設計一個聯合索引為(sex, score)，其中的score也可以換成id。那麼對於如下SQL，使用之前設計的那個聯合索引。那絕對是基本沒法用上索引的，而且效能極差。
mysql> select xx from user_info where sex='female' order by score limit xx,xx;但如果使用了上述設計的輔助索引(sex, score)後：因為where條件裡的欄位是等值匹配，而且還是等於某個常量值。所以雖然order by後跟的score欄位是(sex, score)索引裡的第二個欄位，order by沒有從索引最左側欄位開始排列，但可以使用索引來排序，所以這條SQL語句整體執行的效率是非常高的。