引言

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

1、熟悉MySQL索引的基礎(chǔ)知識：

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

2、如何提高開發(fā)、DBA和QA 在項(xiàng)目過程中關(guān)于 Mysql 索引相關(guān)操作的技術(shù)分析能力。

一、背景

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

二、MySQL索引

1、概念說明

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

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

類比于數(shù)據(jù)庫的表而言，索引其實(shí)就是它的“目錄”。

2、常見索引模型

哈希表

哈希表是一種以鍵-值(Key-Value)格式存儲數(shù)據(jù)的結(jié)構(gòu)，通過輸入待查找的 Key值，就可以找到該 Key 對應(yīng)的 Value。

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

因?yàn)槎鄠€輸入的 Key 值在使用哈希函數(shù)進(jìn)行換算時，會出現(xiàn)多個 Key 換算出來是同一個值的情況，如下圖中的 id1 和 idn 換算的結(jié)果都為：x，這種情況下哈希表給出的處理方案是拉出一個鏈表。

例如，現(xiàn)有一張用戶表信息，需要根據(jù)用戶 id 來查找用戶 name，對應(yīng)的哈希索引示意圖如下：

這時當(dāng)你要查 id1 對應(yīng)的名字是什么，處理步驟是：

首先，將 id1 通過哈希函數(shù)算出 x。

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

注意：

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

但缺點(diǎn)也很明顯，因?yàn)椴皇怯行虻模怨Ｋ饕鰠^(qū)間查詢的速度是很慢的。因?yàn)樾枰M(jìn)行全表掃描一遍。

小結(jié)：

哈希表這種結(jié)構(gòu)適用于只有等值查詢的場景，比如一些NoSQL(非關(guān)系型數(shù)據(jù)庫)引擎。

有序數(shù)組

有序數(shù)組在等值查詢和范圍查詢場景中的性能是十分優(yōu)秀的。

還是上面的根據(jù)用戶 id 來查找用戶 name 的例子，如果使用有序數(shù)組來實(shí)現(xiàn)的話，對應(yīng)的示意圖如下：

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

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

注意：

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

比如你刪除或插入一條記錄，就會非常麻煩，因?yàn)椴迦霐?shù)據(jù)需要將后半部分的數(shù)據(jù)往后挪動一個位置，刪除數(shù)據(jù)需要將后半部分的數(shù)據(jù)往前挪動一個位置，成本太高了。

小結(jié)：

有序數(shù)組索引只適用于靜態(tài)存儲引擎，適合存儲不會再修改的數(shù)據(jù)。

二叉搜索樹

如果還是用上面使用 id 來查詢 name 的例子，來看下使用二叉搜索樹的數(shù)據(jù)結(jié)構(gòu)來實(shí)現(xiàn)，對應(yīng)的示意圖如下：

二叉搜索樹的特點(diǎn)：

父節(jié)點(diǎn)左子樹所有結(jié)點(diǎn)的值小于父節(jié)點(diǎn)的值，右子樹所有結(jié)點(diǎn)的值大于父節(jié)點(diǎn)的值。

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

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

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

3、InnoDB索引模型

在 Mysql 中，索引是在存儲引擎層實(shí)現(xiàn)的，所以并沒有統(tǒng)一的索引標(biāo)準(zhǔn)，即使用不同的存儲引擎，其對應(yīng)索引的工作方式并不一樣。

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

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

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

(1) 減少磁盤IO次數(shù)

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

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

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

(3) 遍歷效率更高

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

索引分類

聚簇索引

主鍵索引

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

在Innodb中，聚簇索引默認(rèn)就是主鍵索引。

假如表沒有設(shè)定主鍵，則按照下列規(guī)則來創(chuàng)建聚簇索引。

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

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

建表語句如下：

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

非聚簇索引

聯(lián)合索引

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

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

其建表語句如下：

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

說到聯(lián)合索引，一定要談?wù)勛钭笃ヅ湓瓌t。

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

設(shè)定表T已建立聯(lián)合索引(id, name)。

where條件為：

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

滿足聯(lián)合索引的最左匹配原則，是可以匹配索引來執(zhí)行sql的。

where條件為：

• name = 'tom' and id = 1

也滿足聯(lián)合索引的最左匹配原則，因?yàn)镸ysql優(yōu)化器會自動調(diào)整id，name的順序與索引順序一致，這樣就能用到聯(lián)合索引了。

where條件為：

• name = 'tom'

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

設(shè)定表T已建立聯(lián)合索引(a, b, c, d)。

where條件為：

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

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

前綴索引

當(dāng)索引列的字符比較多時，索引很大且速度很慢，此時可以優(yōu)化索引列，只索引列開始的部分字符串，以此節(jié)約索引空間，提高索引效率。

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

例如有以下一張學(xué)生表，st_num為學(xué)號：

從上表可以發(fā)現(xiàn) st_num 字段前7位都是重復(fù)的，都是以0102021開頭的。

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

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

前綴索引創(chuàng)建方式如下：

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 就是一個普通索引，普通索引與主鍵索引的區(qū)別在于，普通索引的葉子節(jié)點(diǎn)存放的不是行數(shù)據(jù)，而是主鍵值。(在索引原理中會詳細(xì)說明)。

例如現(xiàn)有一個主鍵列為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+ 樹上找到對應(yīng)主鍵：1，然后再使用1在主鍵索引所在的B+樹上查詢出真實(shí)表的行數(shù)據(jù)后返回結(jié)果，這個操作被稱為回表。

唯一索引

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

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

全文索引

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

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

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

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

• 查詢擴(kuò)展模式：當(dāng)查詢的關(guān)鍵字太短，用戶需要隱含知識時進(jìn)行。

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

這種查詢會分2次執(zhí)行檢索，第1次是使用給定的operating system的短語進(jìn)行檢索，第2次結(jié)合第一次相關(guān)性比較高的進(jìn)行檢索。

索引原理

聚簇索引

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

對應(yīng)的聚簇索引結(jié)構(gòu)圖如下：

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

當(dāng)我們使用主鍵s_id作為查詢條件時，來看下以下sql的執(zhí)行過程。

select * from student where s_id='25'; 

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

顯然，使用索引速度會快。但是在寫入數(shù)據(jù)的時候，需要維護(hù)這顆B+樹的結(jié)構(gòu)，因此寫入性能會下降!

聚簇索引(主鍵索引)的葉子節(jié)點(diǎn)存儲的是整行數(shù)據(jù)。

非聚簇索引

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

如下圖所示：

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

請注意看name字段的非聚簇索引B+樹上的葉子節(jié)點(diǎn)，非聚簇索引的葉子節(jié)點(diǎn)并不是真實(shí)數(shù)據(jù)，它的葉子節(jié)點(diǎn)依然是索引節(jié)點(diǎn)，存放的是該索引字段的值以及對應(yīng)的主鍵(s_id)索引(聚簇索引)。

此時執(zhí)行下列查詢語句：

select * from student where name='Candy'; 

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

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

索引維護(hù)

因?yàn)锽+樹為了維護(hù)索引有序性，在插入新值或刪除數(shù)據(jù)的時候需要做必要的維護(hù)。

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

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

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

當(dāng)相鄰兩個頁由于刪除了數(shù)據(jù)，利用率很低之后，會將數(shù)據(jù)頁做合并。合并的過程，可以認(rèn)為是分裂過程的逆過程。

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

• 哪些場景下應(yīng)該使用自增主鍵?
• 哪些場景下又不應(yīng)該使用自增主鍵?

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

這樣在插入新的記錄時，是不需要指定自增主鍵列 id 值的，因?yàn)橄到y(tǒng)會獲取當(dāng)前 id 最大值后+1作為下一條記錄的自增主鍵列 id 的值。

這種插入數(shù)據(jù)的模式都是追加操作，不涉及到挪動其他記錄的操作，也就不會觸發(fā)葉子節(jié)點(diǎn)的分裂了。

從性能角度看：

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

從存儲空間角度看：

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

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

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

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

單個索引的值字符長度不能過大，因?yàn)锽+樹索引并不能直接找到行，只是找到行所在的頁，通過從磁盤把整頁讀入內(nèi)存，再在內(nèi)存中查找。

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

覆蓋索引

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

select * from student where name='Candy'; 

修改為：

select s_id from student where name='Candy'; 

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

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

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

應(yīng)用場景

1. 當(dāng)只有一個索引，且該索引一定是唯一索引。這種場景適合用業(yè)務(wù)字段直接做主鍵。業(yè)務(wù)使用時盡量使用主鍵查詢，避免回表。
2. 當(dāng)表是經(jīng)常需要更新的不適合做索引，頻繁更新會導(dǎo)致索引也會頻繁更新，降低寫的效率。
3. 使用索引進(jìn)行模糊查詢時，切記 like 后的關(guān)鍵字的前面不能使用%(例如：name like "%三")，只能在關(guān)鍵字的后面加上%，因?yàn)樗饕菑淖笾劣移ヅ涞模绻谇懊婕由?就無法找到索引。
4. 數(shù)據(jù)表過大時，當(dāng)索引字段的字符長度過長則不適合作為索引。因?yàn)椴樵兇罅繑?shù)據(jù)時，索引即使有效，但是速度依然慢。
5. 表數(shù)據(jù)量大且字段值有較多相同值的時候適合選擇使用普通索引。
6. 當(dāng)字段多且字段值沒有重復(fù)的時候用唯一索引。
7. 當(dāng)where條件后查詢字段較多，適合建立聯(lián)合索引。
8. 不會出現(xiàn)在where條件后的查詢字段，不要建立索引。

三、總結(jié)

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

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