引言

通過本篇文章，我們可以收獲：

1、熟悉MySQL索引的基礎知識：

• 索引是什么
• 常見索引模型
• InnoDB索引模型
• 索引種類有哪些
• 索引的應用場景

2、如何提高開發、DBA和QA 在項目過程中關于 Mysql 索引相關操作的技術分析能力。

一、背景

分享這篇文章的目的：提升開發、DBA、QA在項目過程中關于提測 sql 和 sql 變更中關于添加、修改、刪除索引合理性的分析能力。

二、MySQL索引

1、概念說明

簡單來說，索引的出現其實就是為了提高數據查詢的效率，在表數據量較大時，索引的重要性尤為突出，可以理解為索引就像書的目錄一樣。

例如：一本1000頁的書，如果你想快速找到其中某個知識點，如果不按照目錄來查找，直接一頁頁翻開查找，無疑效率是十分低下的。

類比于數據庫的表而言，索引其實就是它的“目錄”。

2、常見索引模型

哈希表

哈希表是一種以鍵-值(Key-Value)格式存儲數據的結構，通過輸入待查找的 Key值，就可以找到該 Key 對應的 Value。

哈希的思想比較簡單，將值放在數組里，再使用哈希函數將輸入的 Key 值換算成一個確定位置的值，最后把 Value 放在數組的這個確定的位置。

因為多個輸入的 Key 值在使用哈希函數進行換算時，會出現多個 Key 換算出來是同一個值的情況，如下圖中的 id1 和 idn 換算的結果都為：x，這種情況下哈希表給出的處理方案是拉出一個鏈表。

例如，現有一張用戶表信息，需要根據用戶 id 來查找用戶 name，對應的哈希索引示意圖如下：

這時當你要查 id1 對應的名字是什么，處理步驟是：

首先，將 id1 通過哈希函數算出 x。

然后，按照順序遍歷，找到 User1，即可查詢到對應的 name 名稱。

注意：

圖中的 id 的值并不是有序遞增的，這樣做的好處是增加新的 User 時速度比較快，只需要往后追加。

但缺點也很明顯，因為不是有序的，所以哈希索引做區間查詢的速度是很慢的。因為需要進行全表掃描一遍。

小結：

哈希表這種結構適用于只有等值查詢的場景，比如一些NoSQL(非關系型數據庫)引擎。

有序數組

有序數組在等值查詢和范圍查詢場景中的性能是十分優秀的。

還是上面的根據用戶 id 來查找用戶 name 的例子，如果使用有序數組來實現的話，對應的示意圖如下：

假設這里的 id 沒有重復，數組就是按照 id 遞增的順序進行保存的，這時如果你要查 id2 對應的名字，用二分法就可以快速得到，這個時間復雜度是O(log(N))。這種索引結構能很好的支持范圍查詢 。

例如你想要查詢 [idm, idn] 區間的 User 的 name 信息，可以先用二分法找到 idm，如果不存在 idm，就去尋找大于 idm 的第一個 User，然后依次向右遍歷，直至查詢到第一個大于 idn 的 id 號，退出循環。

注意：

單從查詢效率來看，有序數組就是最好的數據結構了。思考一個問題，當這種數據結構在遇到更新數據(插入或刪除)時，會怎樣?

比如你刪除或插入一條記錄，就會非常麻煩，因為插入數據需要將后半部分的數據往后挪動一個位置，刪除數據需要將后半部分的數據往前挪動一個位置，成本太高了。

小結：

有序數組索引只適用于靜態存儲引擎，適合存儲不會再修改的數據。

二叉搜索樹

如果還是用上面使用 id 來查詢 name 的例子，來看下使用二叉搜索樹的數據結構來實現，對應的示意圖如下：

二叉搜索樹的特點：

父節點左子樹所有結點的值小于父節點的值，右子樹所有結點的值大于父節點的值。

如果你要查id2的信息話，按照圖中的搜索順序就是按照UserA—>UserC—>UserF—>User2這個路徑得到，這個時間復雜度是O(log(N))。

樹有二叉，也可以有多叉，多叉樹就是每個節點有多個兒子，兒子之間的大小保證從左到右是遞增的。

二叉樹是搜索效率最高的，但是實際上大多數的數據庫存儲卻并不使用二叉樹。原因是索引不僅存在內存中，也要寫到磁盤上。

3、InnoDB索引模型

在 Mysql 中，索引是在存儲引擎層實現的，所以并沒有統一的索引標準，即使用不同的存儲引擎，其對應索引的工作方式并不一樣。

InnoDB存儲引擎在Mysql數據庫中使用最為普遍，下面來看下InnoDB的索引模型。

在InnoDB中，表都是根據主鍵順序以索引的形式存放的，這種存儲方式的表稱為索引組織表，且數據都是存儲在B+樹中的。

為什么使用的是B+樹，而不是其他的數據索引模型呢?

(1) 減少磁盤IO次數

B+樹的數據結構模型將所有數據都放到葉子節點，且葉子節點形成一個列表(可以做范圍查詢)，非葉子節點只放鍵值，這樣每個數據葉中可存放的有效數據就多了，可以有效減少磁盤IO次數。

(2) 每次查詢的時間復雜度是固定的

在B+樹中，由于分支節點只是葉子節點的索引，所以對于任意關鍵字的查找都必須從根節點走到分支節點，所有關鍵字查詢路徑長度相同，每次查詢的時間復雜度是固定的。但是在B樹中，其分支節點上也保存有數據，對于每一個數據的查詢所走的路徑長度是不一樣的，所以查詢效率也不一樣。

(3) 遍歷效率更高

由于B+樹的數據都存儲在葉子節點上，分支節點均為索引，方便掃庫，只需掃一遍葉子即可。但是B樹在分支節點上都保存著數據，要找到具體的順序數據，需要執行一次中序遍歷來查找。所以B+樹更加適合范圍查詢的情況，在解決磁盤IO性能的同時解決了B樹元素遍歷效率低下的問題。

索引分類

聚簇索引

主鍵索引

在Innodb中，Mysql中的數據是按照主鍵的順序來存放的。那么聚簇索引就是按照每張表的主鍵來構造一顆B+樹，葉子節點存放的就是整張表的行數據。由于表里的數據只能按照一顆B+樹排序，因此一張表只能有一個聚簇索引。

在Innodb中，聚簇索引默認就是主鍵索引。

假如表沒有設定主鍵，則按照下列規則來創建聚簇索引。

• 沒有主鍵時，會用一個唯一且不為空的索引列做為主鍵，成為此表的聚簇索引。
• 如果沒有這樣的索引，InnoDB會隱式定義一個主鍵來作為聚簇索引。

例如現有一個主鍵列為id的user表，表中有字段 t 和 name，并且在 t 上有索引。

建表語句如下：

create table user( id int primary key, t int not null, name varchar(16), index (t))engine=InnoDB; 

非聚簇索引

聯合索引

使用多個列字段建立的索引，稱為聯合索引，也叫組合索引。

聯合索引為：(t,name)。

其建表語句如下：

create table user( id int primary key, t int not null, name varchar(16), index(t), index(t,name) )engine=innodb; 

說到聯合索引，一定要談談最左匹配原則。

所謂最左匹配原則指的就是如果 SQL 語句中用到了聯合索引中的最左邊的索引，那么這條 SQL 語句就可以利用這個聯合索引去進行匹配，值得注意的是，當遇到范圍查詢(>、<、between、like)就會停止匹配。

設定表T已建立聯合索引(id, name)。

where條件為：

• id = 1 或者
• id = 1 and name = 'tom'

滿足聯合索引的最左匹配原則，是可以匹配索引來執行sql的。

where條件為：

• name = 'tom' and id = 1

也滿足聯合索引的最左匹配原則，因為Mysql優化器會自動調整id，name的順序與索引順序一致，這樣就能用到聯合索引了。

where條件為：

• name = 'tom'

不滿足聯合索引的最左匹配原則，也就無法使用(id, name)的聯合索引了。

設定表T已建立聯合索引(a, b, c, d)。

where條件為：

• a = 10 and b = 20 and c >100 and d = 5

這個where條件，只有a, b, c能使用到聯合索引，d無法使用索引，因為c>100屬于范圍查詢，將后面d的索引匹配給中斷了。

前綴索引

當索引列的字符比較多時，索引很大且速度很慢，此時可以優化索引列，只索引列開始的部分字符串，以此節約索引空間，提高索引效率。

前綴索引的使用原則是：降低重復的索引值。

例如有以下一張學生表，st_num為學號：

從上表可以發現 st_num 字段前7位都是重復的，都是以0102021開頭的。

如果使用前1-7位字符來做前綴索引就會出現大量索引值重復的情況。

此時索引值重復性高，查詢效率低下，不符合前綴索引的原則，因此可以依據具體需求來決定使用前8-10位字符來做前綴索引。

前綴索引創建方式如下：

create table `student` ( `st_num` varchar(255) not null, `sex` int(10) not null, `name` varchar(255) not null, index (st_num(8)) )engine=InnoDB; 

普通索引

如下user建表語句中的 t 就是一個普通索引，普通索引與主鍵索引的區別在于，普通索引的葉子節點存放的不是行數據，而是主鍵值。(在索引原理中會詳細說明)。

例如現有一個主鍵列為id的user表，表中有字段 t 和 name，并且在 t 上有索引。

建表語句如下：

create table user( id int primary key, t int not null, name varchar(16), index (t))engine=InnoDB; 

例如：

select * from user where t=100; 

這個查詢sql會通過 t 這個普通索引在自身的 B+ 樹上找到對應主鍵：1，然后再使用1在主鍵索引所在的B+樹上查詢出真實表的行數據后返回結果，這個操作被稱為回表。

唯一索引

與普通索引類似，不同點在于唯一索引的索引列的值必須唯一，但允許有空值，這點與主鍵不同(主鍵索引列的值唯一，但不能為空)。

如果是多個字段組成的聯合索引，則列值的組合必須唯一，創建方法與普通索引類似。

全文索引

5.6版本之后InnoDB存儲引擎開始支持全文索引，Mysql允許在char、varchar、text類型上建立全文索引。

Mysql支持三種模式的全文檢索模式：

• 自然語言模式：通過match against 傳遞某個特定的字符串進行檢索。
• 布爾模式：可以為檢查的字符串增加操作符。

布爾操作符可以通過以下sql語句查看：

• 查詢擴展模式：當查詢的關鍵字太短，用戶需要隱含知識時進行。

例如，對于單詞operating system的查詢，用戶可能希望查詢的結果除了包含operating system的文檔，還應該包含linux，windows，unix的單詞。

這種查詢會分2次執行檢索，第1次是使用給定的operating system的短語進行檢索，第2次結合第一次相關性比較高的進行檢索。

索引原理

聚簇索引

以下面一張學生表student為例，其中s_id為主鍵。

對應的聚簇索引結構圖如下：

從圖中可以看下結構圖共分為上下部分，上部分是：由主鍵s_id形成聚簇索引(B+樹)，下部分是：student表存儲在磁盤上的真實數據。

當我們使用主鍵s_id作為查詢條件時，來看下以下sql的執行過程。

select * from student where s_id='25'; 

如上圖所示，從根開始，經過3次查找，就可以找到s_id=25對應的真實數據。如果不使用索引，那就要在磁盤上，進行逐行掃描，直到找到數據位置。

顯然，使用索引速度會快。但是在寫入數據的時候，需要維護這顆B+樹的結構，因此寫入性能會下降!

聚簇索引(主鍵索引)的葉子節點存儲的是整行數據。

非聚簇索引

還是以上述的學生表 student 為例，給該表添加普通索引 name 后，結構圖中新增一棵 name 字段的非聚簇索引的 B+ 樹。

如下圖所示：

因此， 我們每加一個索引，就會增加表的體積，占用磁盤存儲空間。

請注意看name字段的非聚簇索引B+樹上的葉子節點，非聚簇索引的葉子節點并不是真實數據，它的葉子節點依然是索引節點，存放的是該索引字段的值以及對應的主鍵(s_id)索引(聚簇索引)。

此時執行下列查詢語句：

select * from student where name='Candy'; 

通過上圖紅線可以看出，查詢路徑是先從非聚簇索引樹開始查找，然后找到聚簇索引后根據聚簇索引，在s_id的聚簇索引的B+樹上，找到完整的數據!這個過程稱為回表。

也就是說，基于非主鍵索引的查詢需要多掃描一棵索引樹。因此，我們在應用中應該盡量使用主鍵查詢。

索引維護

因為B+樹為了維護索引有序性，在插入新值或刪除數據的時候需要做必要的維護。

以上圖為示例，如果需要插入新的s_id值為50，則需要在s_id=44的記錄后面插入一行新記錄。但如果插入的s_id的值為：28，則需要將s_id=31的數據往后挪動。

假如s_id=44所在的數據頁滿了，根據B+樹的算法，此時需要申請一個新的數據頁，然后將部分數據挪動到新的數據頁上，這個過程稱為頁分裂。這種情況下，性能自然會受到影響。

頁分裂帶來的不僅是性能的影響，還會影響數據頁的利用率。原本放在一個頁的數據，現在分到2個數據頁上，整體空間利用率大幅下降。

當相鄰兩個頁由于刪除了數據，利用率很低之后，會將數據頁做合并。合并的過程，可以認為是分裂過程的逆過程。

基于上述對索引維護過程的說明，下面來討論一個具體案例：

• 哪些場景下應該使用自增主鍵?
• 哪些場景下又不應該使用自增主鍵?

我們知道自增主鍵是指自增列上定義的主鍵，在建表語句中一般是使用關鍵字：NOT NULL PRIMARY KEY AUTO_INCREMENT來定義的。

這樣在插入新的記錄時，是不需要指定自增主鍵列 id 值的，因為系統會獲取當前 id 最大值后+1作為下一條記錄的自增主鍵列 id 的值。

這種插入數據的模式都是追加操作，不涉及到挪動其他記錄的操作，也就不會觸發葉子節點的分裂了。

從性能角度看：

如果使用業務邏輯的字段做主鍵，則相比自增主鍵id，不太容易保證有序插入，這樣寫數據成本相對較高。

從存儲空間角度看：

假設user表中有一個字符串類型的身份證號字段，且是唯一不重復的，此時是用身份證號做主鍵，還是使用自增字段做主鍵比較好呢?

前面講索引原理中講到非聚簇索引的葉子節點上都是主鍵的值，如果使用身份證號做主鍵，那么每個非主鍵索引的葉子節點占用約20個字節，而如果使用整型做主鍵，則只需要4個字節，如果是長整型(bigint)則是8個字節。

由此可知，主鍵長度越小，普通索引的葉子節點就越小，普通索引整體占用的空間也就越小。

因此從性能和存儲空間兩方面來考慮，使用自增主鍵作為索引是更優的選擇。

單個索引的值字符長度不能過大，因為B+樹索引并不能直接找到行，只是找到行所在的頁，通過從磁盤把整頁讀入內存，再在內存中查找。

其中每頁的大小是有規定的，頁是InnoDB管理存儲空間的基本單位：1頁=16kb，原則是盡量在一個頁內存放多個索引。

覆蓋索引

還是以上述例子來講解，現將下列查詢語句：

select * from student where name='Candy'; 

修改為：

select s_id from student where name='Candy'; 

這時只需要查 s_id 的值，而 s_id 的值已經在普通索引 name上了，因此可以從非聚簇索引B+樹上直接返回查詢結果，不需要回表操作。

也就是說，在這個查詢里面，索引name已經覆蓋了我們的查詢需求，因此稱為覆蓋索引。

由于覆蓋索引可以減少樹的搜索次數，顯著提升查詢性能，所以使用覆蓋索引是一個常用的性能優化手段。

應用場景

1. 當只有一個索引，且該索引一定是唯一索引。這種場景適合用業務字段直接做主鍵。業務使用時盡量使用主鍵查詢，避免回表。
2. 當表是經常需要更新的不適合做索引，頻繁更新會導致索引也會頻繁更新，降低寫的效率。
3. 使用索引進行模糊查詢時，切記 like 后的關鍵字的前面不能使用%(例如：name like "%三")，只能在關鍵字的后面加上%，因為索引是從左至右匹配的，如果在前面加上%就無法找到索引。
4. 數據表過大時，當索引字段的字符長度過長則不適合作為索引。因為查詢大量數據時，索引即使有效，但是速度依然慢。
5. 表數據量大且字段值有較多相同值的時候適合選擇使用普通索引。
6. 當字段多且字段值沒有重復的時候用唯一索引。
7. 當where條件后查詢字段較多，適合建立聯合索引。
8. 不會出現在where條件后的查詢字段，不要建立索引。

三、總結

1. 項目代碼在 code diff 時會發現常用的業務查詢 sql，根據 where 條件來判斷頻繁查詢字段，再根據本文分享的索引知識，來判斷在sql審核中關于索引相關的操作是否合理且有效。
2. 測試過程中通過設置 slow sql 查詢參數，找出對應的 sql 查詢語句，分析 slow sql 產生的原因，并給出自己的解決方案，如添加必要的字段索引。

原文地址:https://www.toutiao.com/a7070111710790615586/