接觸MySQL數據庫的小伙伴一定避不開索引，索引的出現是為了提高數據查詢的效率，就像書的目錄一樣。

某一個SQL查詢比較慢，你第一時間想到的就是“給某個字段加個索引吧”，那么索引是什么？是如何工作的呢？一起靜下心來，耐心看完這篇文章吧，干貨不啰嗦，相信你一定會有所收獲。

一、索引模型

模型也就是數據結構，常見的三種模型分別是哈希表、有序數組和搜索樹。

了解MySQL的朋友已經知道，現在MySQL默認使用的是InnoDB存儲引擎，使用的是B+樹索引數據結構。所以這個話題我們簡單介紹，不作過多篇幅解釋，只需了解為什么InnoDB選擇B+樹作為索引的數據結構。

1.1 哈希表

key-value鍵值對存儲結構，哈希思路非常easy，給定key，通過哈希函數命中value。了解HashMap的都知道，哈希表不可避免的會發生同值沖突，所以需要通過鏈表跟在數組后面。

哈希表的特點是插入速度很快，只需要通過hash找到對應數組，發生沖突就在鏈表往后追加。但缺點也正是因為無序，導致查詢速度很慢，幾乎全部掃描一遍。

1.2 有序數組

有序數組非常簡單，遞增順序保存。查詢時可以使用二分法，時間復雜度O(log(N))，但是更新數據就很麻煩，往中間插入一個數據就必須挪動后面所有的記錄，成本很高。

1.3 B+樹

B+樹衍生于二叉搜索樹，沒錯，大學數據結構中的二叉搜索樹，課堂的熟悉感是不是都回來了～

二叉搜索樹的特點是：每個節點的左兒子小于父節點，右兒子大于父節點。所以查詢搜索的時間復雜度是O(log(N))，為了維持O(log(N))查詢復雜度，需要保證這棵樹是平衡二叉樹，所以更新的時間復雜度也是O(log(N))。

但是平衡二叉樹的缺點在于隨著數據的增加，整棵樹的層級越來越高，考慮葉子節點的數據總是在內存中，那么樹層級越多意味著需要多次的磁盤尋址。一棵100萬數據量節點的平衡二叉樹，樹高H=log2(N+1) - 1=log2(1000000+1)-1=20，一次查詢可能需要訪問20個數據節點，磁盤隨機讀一個數據塊大約10ms，總計需要20個10ms時間找到一行數據，難以接受！

為了減少盡量少的讀磁盤，二叉樹就演變為了N叉樹，N是多少，取決于數據塊的大小。如果MySQL數據表中你創建了一個整數類型的索引，這個N差不多是1200，樹高是4時，整棵樹可以存儲1200的3次方，也就是17億數據，查找一個數據只需要訪問3次磁盤，很nice~

二、索引維護

每個索引在InnoDB中都對應一棵B+樹。

根據葉子節點的內容，索引類型分為主鍵索引和非主鍵索引。主鍵索引又稱為聚簇索引，葉子節點中存儲的是整行數據。

非主鍵索引的葉子節點中存儲的是主鍵的值。所以，如果你的SQL語句查詢時用到的索引不是主鍵，那么就會有一次普通索引找到葉子節點中的主鍵ID，再進行回表獲取數據。因此，我們在查詢時應盡量使用主鍵查詢。

B+樹為了維護索引有序性，是有代價的，如果新插入的數據在數據頁中有空位置且后面沒有數據，可以直接插入；如果在500和700中間插入600，則需要挪動700及后面的數據，空出位置；更糟糕的是，如果數據頁已經滿了，則需要新申請一個新的數據頁，然后挪動數據，這就是頁分裂。有分裂就有合并，對應的就是刪除數據場景。性能很受影響！

從性能角度看，如果采用主鍵自增剛好符合索引有序的特點，每次插入數據都是追加，不會挪動數據也不會觸發頁分裂。

從存儲空間角度看，主鍵長度越小，索引的葉子節點就越小，占用空間越小。如整型只要4個字節，長整型（bigint）就是8字節。這也是為什么不建議大家用隨機數作為主鍵的原因。

三、索引利用

我們以一條SQL語句執行流程來看索引是怎么提高查詢效率的。

selcect * from T where k between 1 and 3;

假定主鍵索引和k索引樹分別是：

這條SQL查詢的執行流程：

1、在k索引樹上找到k=1的記錄，取得主鍵ID=1

2、到主鍵索引樹上查到ID=1對應的V1

3、在k索引樹繼續取下一個值k=3，取得主鍵ID=4

4、到主鍵索引樹上查到ID=4對應的V4

5、在k索引樹上取下一個值k=5，不滿足查詢條件，循環結束。

可以看到，這條SQL查詢了k索引樹3次，回表2次。這里你一定會想，能不能優化索引，避免回表呢？

如果這條SQL改為selcect id from T where k between 1 and 3;那么就不需要回表了，這時k索引也稱為覆蓋索引，也就是查詢的字段已經在k索引中，無需回表了。

你可能會立即反問，業務中怎么可能剛剛好只查主鍵呢，那就需要根據實際情況考慮建立聯合索引。

3.1 最左前綴原則

每一種查詢都新加一個索引，無疑是對索引的浪費。

我們來看（name，age）這個聯合索引：

當你查詢姓名為“李五”時，可以快速定位到id3，然后向后遍歷得到所要的結果。

如果你查詢的姓名第一個字是“李”時，可以定位到id2，然后向后遍歷得到所要的結果。

所以最左前綴，不僅是聯合索引的最左N個字段，也可以是字符串的最左N個字符。

這時你需要根據具體的業務場景，考慮聯合索引的順序問題，當有(name,age)這個聯合索引，就無需再單獨建立（name）索引了，但是如果查詢條件里面只有age，是無法使用(name,age)索引了。

3.2 索引下推

在MySQL5.6之后，引入了索引下推優化，可以在索引遍歷過程中，對索引中包含的字段先做判斷，直接過濾不滿足條件的記錄，減少回表次數。

SQL語句：

select * from T where name like "李%" and age = 25;

無索引下推機制時，根據最左前綴匹配原則，找到“李%”的name之后，就回表查詢主鍵索引，然后過濾age字段。索引下推機制下，在尋找“李%”name時，會直接判斷age=25符合條件的數據，然后回表查詢主鍵索引，減少了2次回表次數。

3.3 唯一索引

唯一索引的屬性列不能出現重復的數據，但是允許數據為 NULL，一張表允許創建多個唯一索引。建立唯一索引的目的大部分時候都是為了該屬性列的數據的唯一性，而不是為了查詢效率。

知道了唯一索引的概念之后，你也許已經猜到唯一索引的性能可能比不上普通索引，一起來看背后的原因是什么？

我們從查詢和更新2個角度來分析，唯一索引和普通索引的區別。

查詢過程

SQL語句：

select id from T where k = 3;

普通索引：查找到滿足第一個k=3記錄后，向后繼續尋找，直到第一個不滿足k=3的記錄。

唯一索引：由于索引數據的唯一性，查找到第一個滿足k=3條件的記錄后，停止檢索。

對于內存操作來說，兩者的性能差別幾乎可以忽略。

更新過程

當更新一個數據頁時，如果數據頁在內存中就直接更新，否則InnoDB會將這些更新操作緩存在change buffer中，這樣就不需要從磁盤中讀入這個數據頁了。在下次查詢訪問這個數據頁時，會觸發change buffer的merge操作，持久化到磁盤中。除了訪問數據頁觸發merge時，系統有后臺線程也會定期merge，在數據庫正常關閉時，也會執行merge操作。將更新操作先記錄在change buffer，就是為了減少讀磁盤，提升SQL語句執行速度。

唯一索引更新不能使用change buffer，而普通索引可以使用。

此時，再執行一條更新語句時，第一種情況時更新的數據頁在內存中，這時：

唯一索引：找到需要插入的位置，判斷有沒有沖突，執行插入，結束；

普通索引：找到需要插入的位置，插入這個值，結束。

可以看出，數據頁在內存中的兩者操作幾乎沒有性能差別。

重點來了，如果更新的數據頁不在內存中，這時：

唯一索引：將數據頁讀入內存，判斷有沒有沖突，執行插入，結束；

普通索引：將更新記錄在change buffer，結束。

將數據從磁盤讀入內存涉及隨機IO的訪問，是數據庫里面成本最高的操作之一。change buffer因為減少了隨機磁盤訪問，所以對更新性能的提升是會很明顯的。

舉個例子，大家感受更深，如果你的業務庫有大量插入數據的操作，內存命中率下降明顯，系統就會經常處于阻塞狀態，更新語句都堵住了。有可能就是因為普通索引改為了唯一索引。

那么怎么判斷是不適合用唯一索引呢？

對于寫多讀少的業務來說，寫完之后立即讀的概率比較小，此時change buffer效果很好，推薦使用普通索引。

對于一個業務寫完之后立即就要訪問，將更新操作記錄在change buffer之后，由于馬上訪問這個數據頁，會立即出觸發merge操作，增加隨機訪問IO次數，此時，change buffer反而起到了反作用，增加了維護代價，可以使用唯一索引。

四、索引實踐

4.1 order by語句

給定一個SQL語句：

select city,name,age from T where city = "杭州" order by name limit 1000;

此時你已經很清楚，為了避免全表掃描，需要在city字段上加索引，我們用explain命令看一下這條SQL執行情況：

Extra中的“Using filesort”表示就是需要排序，MySQL會分配一塊內存用于排序，稱為sort_buffer。這條SQL的執行流程是：

1、初始化sort_buffer，放入name、city、age三個字段

2、從索引city找到第一個滿足city="杭州"的主鍵ID

3、從主鍵索引中取出行數據，選取select條件中的city、name、age三個字段，放入sort_buffer中

4、從索引city中取下一個記錄的主鍵id

5、重復3、4直到不滿足city的查詢條件

6、對sort_buffer中的數據按照name進行快速排序

7、按照排序結果取前1000行返回給用戶

通過一張圖來看流程：

但是sort_buffer不是無限大的，如果排序數據量太大，內存就會放不下，就會用到磁盤臨時文件進行排序。

合并多個臨時文件一般使用歸并排序算法。MySQL的設計思想是：如果內存夠用，盡量多利用內存，減少磁盤訪問。

4.2 索引選擇

其實對于4.1中的SQL語句是可以優化的，不知道你猜到了沒有，SQL中過濾條件是city和name，我們可以創建一個(city，name)的聯合索引，這樣能夠保證從city索引中取出來的數據，天然就是按照name遞增排序的，此時的流程變為了：

是不是效果好多了，而且查詢過程不需要臨時表，也不需要排序，我們用explain來驗證下：

可以看到，Extra中沒有Using filesort了，也就是不需要排序了。而且(city,name)聯合索引本身有序，掃描行數由之前的4000行減少到1000行。

你是不是覺得到此為止了？還可以再優化下哦，如果創建一個(city,name,age)的聯合索引，那么這個聯合索引對于這個SQL來說就成了覆蓋索引，流程簡化成了這樣：

來看一下explain效果：

可以看到，Extra中多了“Using index”，表示使用了覆蓋索引，性能上快了很多。

以上是索引選擇的一個例子，在大家日常業務開發中會有很多遇到索引的情況，一般可考慮：

?被頻繁查詢的字段：我們創建索引的字段應該是查詢操作非常頻繁的字段。

?被作為條件查詢的字段：被作為 WHERE 條件查詢的字段，應該被考慮建立索引。

?頻繁需要排序的字段：索引已經排序，這樣查詢可以利用索引的排序，加快排序查詢時間。

?被經常頻繁用于連接的字段：經常用于連接的字段可能是一些外鍵列，對于外鍵列并不一定要建立外鍵，只是說該列涉及到表與表的關系。對于頻繁被連接查詢的字段，可以考慮建立索引，提高多表連接查詢的效率。

?如果一個字段不被經常查詢，反而被經常修改，那么就更不應該在這種字段上建立索引了。

?盡可能的考慮建立聯合索引而不是單列索引。每個索引都需要維護一棵B+樹，索引占用的空間也是很多的，且修改索引時，耗費的時間也是較多的。如果是聯合索引，多個字段在一個索引上，那么將會節約很大磁盤空間，且修改數據的操作效率也會提升。

4.3 索引失效

索引失效也是慢查詢的主要原因之一，常見的導致索引失效的情況有下面這些：

?使用 SELECT * 進行查詢，優化器可能會認為全表掃描比索引更高效。

?創建了聯合索引，但查詢條件未遵守最左匹配原則;

?在索引列上進行計算、函數、類型轉換等操作;

?以 % 開頭的 LIKE 查詢比如 like '%abc';

?查詢條件中使用 or，且 or 的前后條件中有一個列沒有索引，涉及的索引都不會被使用到;

這里可以展開說明函數和類型轉換為什么會導致索引失效。

案例一：函數操作

SQL語句：

select count(*) from T where month(modified) = 6

這條SQL想要檢索modified修改時間在6月份的數據量有多少。

即使modified字段上有索引，你仍會發現SQL語句執行了很久。因為MySQL規定：對字段做了函數計算，就不能使用索引。為什么條件是where modified='2024-06-18’的時候可以用上索引，而改成where month(modified)=6的時候就不行了？

其實也很簡單，modified索引樹的每個節點存儲的是“2024-06-18”這樣的數據，month(modified)之后的結果是7，無法在索引樹中進行檢索。最終MySQL選擇了全表掃描。

案例二：類型轉換

給定SQL語句：

select * from T where age = 20;

設定age這個字段上有索引，創建表語句中age是varchar(10)，通過explain可以發現，這條SQL走了全表掃描，因為輸入的age參數是整數，需要做類型轉換。

對于MySQL優化器來說，這條SQL等同于：

select * from T where CAST(a ge AS signed int) = 20;

這樣就又回到了對索引字段做函數操作，優化器會放棄走樹搜索功能。

案例三：編碼轉換

如果你需要做兩張表的聯表查詢，但是一張表的編碼是utf8，另一張表的編碼格式是utf8mb4，那么在通過join字段連接時用不上關聯字段的索引。utf8中的一個英文字符占用一個字節的存儲空間，一個中文占用三個字節的存儲空間，不支持4個字節的存儲，而utf8mb4支持4個字節的存儲，可以更好的支持emoji表情。

所以在連表查詢時，MySQL會先將utf8字符串轉換為utf8mb4字符集，再做比較。SQL語句變成了：

select * from T1 where CONVERT(T1.age USING utf8mb4)=T2.age.value;

這樣又回到了對索引字段做函數操作，走不到索引。

五、QA

（1）B樹與B+樹的區別

數據結構上：B樹所有節點都可包含記錄，而B+樹只有葉子節點存儲數據，非葉子節點只用于索引，不存儲實際數據。

更新操作上：B+樹執行更新需要維護索引的變化以保持有序。

查詢性能上：B樹通過二分查找，而且是跨層查找，理論上，需要命中的數據離根節點越近性能越高（最好的性能是O(1)），否則需要多次磁盤訪問，性能較低。B+樹是數據存儲在葉子節點，通過鏈表定位數據的時間復雜度是O(log(N))。

使用場景上：B樹適合隨機訪問的場景，比如文件系統索引；B+樹適合范圍查詢和順序查詢，比如數據庫索引。

（2）explain語句

explain語句也稱為獲取MySQL執行計劃信息，展示一條SQL的執行方案。

explain語句執行結構一共有12列，每一列什么含義和如何分析，百度很多，這里不展開解釋了。大家也不需要刻意記每個字段什么含義，需要explain時百度下，次數多了自然也就熟悉了。

（3）為什么要限制每張表上的索引數量？

索引可以提高查詢效率，是不是一張表上索引越多越好呢，其實不然。索引可以提高效率也可以降低效率，因為MySQL優化器在選擇如何優化SQL查詢時，會對每一個索引進行評估，以生成一個最好的執行計劃，如果同時有很多索引都可以使用，就會增加MySQL優化器生成執行計劃的時間，同樣會降低查詢性能。所以一般建議單表索引不超過5個，根據實際頻繁查詢的字段設置索引。

（4）索引失效的進一步擴展

我們已經知道MySQL遇到函數轉換會使索引失效，那么假定主鍵id是int類型，如果執行下面SQL語句，會導致全表掃描嗎？

select * from T where id = "65535";

你可以去數據庫嘗試下這條SQL，然后通過explain驗證下。其實結論很簡單，這里進行的是查詢條件的value值函數轉換（將varchar轉換為int），并不是在索引字段id上，自然是命中索引的。

大家在日常中還有遇到什么坑或者經驗，歡迎評論區分享

審核編輯 黃宇