0 前言
這篇文章不會(huì)講解索引的基礎(chǔ)知識(shí)，主要是關(guān)于MySQL數(shù)據(jù)庫(kù)的B+樹(shù)索引的相關(guān)原理，里面的一些知識(shí)都參考了MySQL技術(shù)內(nèi)幕這本書(shū)，也算對(duì)于這些知識(shí)的總結(jié)。對(duì)于B樹(shù)和B+樹(shù)相關(guān)的知識(shí)，可以參考我的這篇博客：面試官問(wèn)你B樹(shù)和B+樹(shù)，就把這篇文章丟給他
1 索引的管理
索引有很多中類型：普通索引、唯一索引、主鍵索引、組合索引、全文索引，下面我們看看如何創(chuàng)建和刪除下面這些類型的索引。
1.1 索引的創(chuàng)建方式
索引的創(chuàng)建是可以在很多種情況下進(jìn)行的。
- 直接創(chuàng)建索引
[UNIQUE|FULLLTEXT]：表示可選擇的索引類型，唯一索引還是全文索引，不加話就是普通索引。
table_name：表的名稱，表示為哪個(gè)表添加索引。
column_name(length)：column_name是表的列名，length表示為這一列的前l(fā)ength行記錄添加索引。
- 修改表結(jié)構(gòu)的方式添加索引
- 創(chuàng)建表的時(shí)候同時(shí)創(chuàng)建索引
1.2 主鍵索引和組合索引創(chuàng)建的方式
前面講的都是普通索引、唯一索引和全文索引創(chuàng)建的方式，但是，主鍵索引和組合索引創(chuàng)建的方式卻是有點(diǎn)不一樣的，所以單獨(dú)拿出來(lái)講一下。
組合索引創(chuàng)建方式
- 創(chuàng)建表的時(shí)候同時(shí)創(chuàng)建索引
- 修改表結(jié)構(gòu)的方式添加索引
主鍵索引創(chuàng)建方式
主鍵索引是一種特殊的唯一索引，一個(gè)表只能有一個(gè)主鍵，不允許有空值。一般是在建表的時(shí)候同時(shí)創(chuàng)建主鍵索引。
1.3 刪除索引
刪除索引可利用ALTER TABLE或DROP INDEX語(yǔ)句來(lái)刪除索引。類似于CREATE INDEX語(yǔ)句，DROP INDEX可以在ALTER TABLE內(nèi)部作為一條語(yǔ)句處理，語(yǔ)法如下。
(1)DROP INDEX index_name ON talbe_name
(2)ALTER TABLE table_name DROP INDEX index_name
(3)ALTER TABLE table_name DROP PRIMARY KEY
第3條語(yǔ)句只在刪除PRIMARY KEY索引時(shí)使用，因?yàn)橐粋€(gè)表只可能有一個(gè)PRIMARY KEY索引，因此不需要指定索引名。
1.4 索引實(shí)例
上面講了一下基本的知識(shí)，接下來(lái)，還是通過(guò)一個(gè)具體的例子來(lái)體會(huì)一下。
- step1：創(chuàng)建表
- step2：添加索引
首先，我們使用直接添加索引的方式添加一個(gè)普通索引。
接著，我們用修改表結(jié)構(gòu)的時(shí)候添加索引。
最后，我們?cè)偬砑右粋€(gè)組合索引。
這樣，我們就把前面索引的方式都用上一遍了，我相信你也熟悉這些操作了。
- step3：使用SHOW INDEX命令查看索引信息
如果想要查看表中的索引信息，可以使用命令SHOW INDEX,下面的例子，我們查看表table_index的索引信息。
得到上面的信息，上面的信息什么意思呢？我們逐一介紹！
| 字段 | 解釋 |
|---|---|
| Table | 索引所在的表 |
| Non_unique | 非唯一索引，如果是0，代表唯一的，也就是說(shuō)如果該列索引中不包括重復(fù)的值則為0 否則為1 |
| Key_name | 索引的名字，如果是主鍵的話 則為PRIMARY |
| Seq_in_index | 索引中該列的位置，從1開(kāi)始,如果是組合索引 那么按照字段在建立索引時(shí)的順序排列 |
| Collation | 列是以什么方式存儲(chǔ)在索引中的?？梢允茿或者NULL，B+樹(shù)索引總是A，排序的， |
| Sub_part | 是否列的部分被索引，如果只是前100行索引，就顯示100，如果是整列，就顯示NULL |
| Packed | 關(guān)鍵字是否被壓縮，如果沒(méi)有，為NULL |
| Index_type | 索引的類型，對(duì)于InnoDB只支持B+樹(shù)索引，所以都是顯示BTREE |
- step4：刪除索引
直接刪除索引方式
修改表結(jié)構(gòu)時(shí)刪除索引
1.5 Cardinality關(guān)鍵字解析
在上面介紹了那么多個(gè)關(guān)鍵字的意思，但是Cardinality這個(gè)關(guān)鍵字非常的關(guān)鍵，優(yōu)化器會(huì)根據(jù)這個(gè)值來(lái)判斷是否使用這個(gè)索引。在B+樹(shù)索引中，只有高選擇性的字段才是有意義的，高選擇性就是這個(gè)字段的取值范圍很廣，比如姓名字段，會(huì)有很多的名字，可選擇性就高了。
一般來(lái)說(shuō)，判斷是否需要使用索引，就可以通過(guò)Cardinality關(guān)鍵字來(lái)判斷，如果非常接近1，說(shuō)明有必要使用，如果非常小，那么就要考慮是否使用索引了。
需要注意的一個(gè)問(wèn)題時(shí)，這個(gè)關(guān)鍵字不是及時(shí)更新的，需要更新的話，需要使用ANALYZE TABLE，例如。
因?yàn)槟壳皼](méi)有數(shù)據(jù)，所以，你會(huì)發(fā)現(xiàn)，這個(gè)值一直都是0，沒(méi)有變化。
InoDB存儲(chǔ)引擎Cardinality的策略
在InnoDB存儲(chǔ)引擎中，這個(gè)關(guān)鍵字的更新發(fā)生在兩個(gè)操作中：insert和update。但是，并不是每次都會(huì)更新，這樣會(huì)增加負(fù)荷，所以，對(duì)于這個(gè)關(guān)鍵字的更新有它的策略：
- 表中1/16的數(shù)據(jù)發(fā)生變化
- InnoDB存儲(chǔ)引擎的計(jì)數(shù)器stat_modified_conter>2000000000
默認(rèn)InnoDB存儲(chǔ)引擎會(huì)對(duì)8個(gè)葉子節(jié)點(diǎn)進(jìn)行采樣，采樣過(guò)程如下：
- B+樹(shù)索引中葉子節(jié)點(diǎn)數(shù)量，記做A
- 隨機(jī)取得B+樹(shù)索引中的8個(gè)葉子節(jié)點(diǎn)。統(tǒng)計(jì)每個(gè)頁(yè)不同的記錄個(gè)數(shù)，分別為p1-p8
- 根據(jù)采樣信息得到Cardinality的預(yù)估值：(p1+p2+p3+...+p8)*A/8
因?yàn)殡S機(jī)采樣，所以，每次的Cardinality值都是不一樣的，只有一種情況會(huì)一樣的，就是表中的葉子節(jié)點(diǎn)小于或者等于8，這時(shí)候，怎么隨機(jī)采樣都是這8個(gè)，所以也就一樣的。
1.6 Fast Index Creation
在MySQL 5.5之前，對(duì)于索引的添加或者刪除，每次都需要?jiǎng)?chuàng)建一張臨時(shí)表，然后導(dǎo)入數(shù)據(jù)到臨時(shí)表，接著刪除原表，如果一張大表進(jìn)行這樣的操作，會(huì)非常的耗時(shí)，這是一個(gè)很大的缺陷。
InnoDB存儲(chǔ)引擎從1.0.x版本開(kāi)始加入了一種Fast Index Creation（快速索引創(chuàng)建）的索引創(chuàng)建方式。
這種方式的策略為：每次為創(chuàng)建索引的表加上一個(gè)S鎖（共享鎖），在創(chuàng)建的時(shí)候，不需要重新建表，刪除輔助索引只需要更新內(nèi)部視圖，并將輔助索引空間標(biāo)記為可用，所以，這種效率就大大提高了。
1.7 在線數(shù)據(jù)定義
MySQL5.6開(kāi)始支持的在線數(shù)據(jù)定義操作就是：允許輔助索引創(chuàng)建的同時(shí)，還允許其他insert、update、delete這類DM操作，這就極大提高了數(shù)據(jù)庫(kù)的可用性。
所以，我們可以使用新的語(yǔ)法進(jìn)行創(chuàng)建索引：
ALGORITHM指定創(chuàng)建或者刪除索引的算法
- COPY：創(chuàng)建臨時(shí)表的方式
- INPLACE：不需要?jiǎng)?chuàng)建臨時(shí)表
- DEFAULT：根據(jù)參數(shù)old_alter_table參數(shù)判斷，如果是OFF,采用INPLACE的方式
LOCK表示對(duì)表添加鎖的情況
- NONE：不加任何鎖
- SHARE：加一個(gè)S鎖，并發(fā)讀可以進(jìn)行，寫操作需要等待
- EXCLUSIVE：加一個(gè)X鎖，讀寫都不能并發(fā)進(jìn)行
- DEFAULT：先判斷是否可以使用NONE，如不能，判斷是否可以使用SHARE，如不能，再判斷是否可以使用EXCLUSIVE模式。
2 B+ 樹(shù)索引的使用
2.1 聯(lián)合索引
聯(lián)合索引是指對(duì)表上的多個(gè)列進(jìn)行索引，這一部分我們將通過(guò)幾個(gè)例子來(lái)講解聯(lián)合索引的相關(guān)知識(shí)點(diǎn)。
首先，我們先創(chuàng)建一張表以及為這張表創(chuàng)建聯(lián)合索引。
創(chuàng)建聯(lián)合索引
插入幾條測(cè)試數(shù)據(jù)
到這一步，我們已經(jīng)基本準(zhǔn)備好了需要的數(shù)據(jù)，我們可以進(jìn)行更深一步的聯(lián)合索引的探討。
我們什么時(shí)候需要?jiǎng)?chuàng)建聯(lián)合索引呢
索引建立的主要目的就是為了提高查詢的效率，那么聯(lián)合索引的目的也是類似的，聯(lián)合索引的目的就是為了提高存在多個(gè)查詢條件的情況下的效率，就如上面建立的表一樣，有多個(gè)字段，當(dāng)我們需要利用多個(gè)字段進(jìn)行查詢的時(shí)候，我們就需要利用到聯(lián)合索引了。
什么時(shí)候聯(lián)合索引才會(huì)發(fā)揮作用呢
有時(shí)候，我們會(huì)用聯(lián)合索引，但是，我們并不清楚其原理，不知道什么時(shí)候聯(lián)合索引會(huì)起到作用，什么時(shí)候又是會(huì)失效的？
帶著這個(gè)問(wèn)題，我們了解一下聯(lián)合索引的最左匹配原則。
最左匹配原則：這個(gè)原則的意思就是創(chuàng)建組合索引，以最左邊的為準(zhǔn)，只要查詢條件中帶有最左邊的列，那么查詢就會(huì)使用到索引。
下面，我們用幾個(gè)例子來(lái)看看這個(gè)原則。
我們看看這條語(yǔ)句的結(jié)果，首先，我們看到使用了索引，因?yàn)椴樵儣l件中帶有最左邊的列a，那么利用了幾個(gè)索引呢？這個(gè)我們需要看key_len這個(gè)字段，我們知道utf8編碼的一個(gè)字符3個(gè)字節(jié)，而我們使用的數(shù)據(jù)類型是char(2)，占兩個(gè)字節(jié)，索引就是2*3等于6個(gè)字節(jié)，所以只有一個(gè)索引起到了作用。
這個(gè)語(yǔ)句我們可以看出，這個(gè)沒(méi)有使用索引，因?yàn)閜ossible_keys為空，而且，從查詢的行數(shù)rows可以看出為6（我們測(cè)試數(shù)據(jù)總共6條），說(shuō)明進(jìn)行了全盤掃描的，說(shuō)明這種情況是不符合最左匹配原則，所以不會(huì)使用索引查詢。
這種情況又有點(diǎn)不一樣了，我們使用了一個(gè)排序，可以看出使用了索引，通過(guò)key_len為12可以得到使用了2個(gè)索引a、b，另外在Extra選項(xiàng)中可以看到使用了Using filesort，也就是文件排序，這里使用文件排序的原因是這樣的：上面的查詢使用了a、b索引，但是當(dāng)我們用d字段來(lái)排序時(shí)，（a，d）或者（b，d）這兩個(gè)索引是沒(méi)有排序的，聯(lián)合索引的使用有一個(gè)好處，就是索引的下一個(gè)字段是會(huì)自動(dòng)排序的，在這里的這種情況來(lái)說(shuō)，c字段就是排序的，但是d是不會(huì)，如果我們用c來(lái)排序就會(huì)得到不一樣的結(jié)果。
是不是可以看到，當(dāng)我們用c進(jìn)行排序的時(shí)候，因?yàn)槭褂昧薬、b索引，所以c就自動(dòng)排序了，所以也就不用filesort了。
講到這里，我相信通過(guò)上面的幾個(gè)例子，對(duì)于聯(lián)合索引的相關(guān)知識(shí)已經(jīng)非常的透徹清晰了，最后，我們?cè)賮?lái)聊幾個(gè)常見(jiàn)的問(wèn)題。
Q1：為什么不對(duì)表中的每一個(gè)列創(chuàng)建一個(gè)索引呢
第一，創(chuàng)建索引和維護(hù)索引要耗費(fèi)時(shí)間，這種時(shí)間隨著數(shù)據(jù)量的增加而增加。
第二，索引需要占物理空間，除了數(shù)據(jù)表占數(shù)據(jù)空間之外，每一個(gè)索引還要占一定的物理空間，如果要建立聚簇索引，那么需要的空間就會(huì)更大。
第三，當(dāng)對(duì)表中的數(shù)據(jù)進(jìn)行增加、刪除和修改的時(shí)候，索引也要?jiǎng)討B(tài)的維護(hù)，這樣就降低了數(shù)據(jù)的維護(hù)速度。
Q2：為什么需要使用聯(lián)合索引
減少開(kāi)銷。建一個(gè)聯(lián)合索引(col1,col2,col3)，實(shí)際相當(dāng)于建了(col1),(col1,col2),(col1,col2,col3)三個(gè)索引。每多一個(gè)索引，都會(huì)增加寫操作的開(kāi)銷和磁盤空間的開(kāi)銷。對(duì)于大量數(shù)據(jù)的表，使用聯(lián)合索引會(huì)大大的減少開(kāi)銷！
覆蓋索引。對(duì)聯(lián)合索引(col1,col2,col3)，如果有如下的sql: select col1,col2,col3 from test where col1=1 and col2=2。那么MySQL可以直接通過(guò)遍歷索引取得數(shù)據(jù)，而無(wú)需回表，這減少了很多的隨機(jī)io操作。減少io操作，特別的隨機(jī)io其實(shí)是dba主要的優(yōu)化策略。所以，在真正的實(shí)際應(yīng)用中，覆蓋索引是主要的提升性能的優(yōu)化手段之一。
效率高。索引列越多，通過(guò)索引篩選出的數(shù)據(jù)越少。有1000W條數(shù)據(jù)的表，有如下sql:select from table where col1=1 and col2=2 and col3=3,假設(shè)假設(shè)每個(gè)條件可以篩選出10%的數(shù)據(jù)，如果只有單值索引，那么通過(guò)該索引能篩選出1000W10%=100w條數(shù)據(jù)，然后再回表從100w條數(shù)據(jù)中找到符合col2=2 and col3= 3的數(shù)據(jù)，然后再排序，再分頁(yè)；如果是聯(lián)合索引，通過(guò)索引篩選出1000w10% 10% *10%=1w，效率提升可想而知！
覆蓋索引
覆蓋索引是一種從輔助索引中就可以得到查詢的記錄，而不需要查詢聚集索引中的記錄，使用覆蓋索引的一個(gè)好處是輔助索引不包含整行記錄的所有信息，所以大小遠(yuǎn)小于聚集索引，因此可以大大減少IO操作。覆蓋索引的另外一個(gè)好處就是對(duì)于統(tǒng)計(jì)問(wèn)題有優(yōu)化，我們看下面的一個(gè)例子。
如果是myisam引擎，Extra列會(huì)輸出Select tables optimized away語(yǔ)句，myisam引擎已經(jīng)保存了記錄的總數(shù)，直接返回結(jié)果，就不需要覆蓋索引優(yōu)化了。
如果是InnoDB引擎，Extra列會(huì)輸出Using index語(yǔ)句，說(shuō)明InnoDB引擎優(yōu)化器使用了覆蓋索引操作。
2.2 索引提示
MySQL數(shù)據(jù)庫(kù)支持索引提示功能，索引提示功能就是我們可以顯示的告訴優(yōu)化器使用哪個(gè)索引，一般有下面兩種情況可能使用到索引提示功能（INDEX HINT）：
- MySQL數(shù)據(jù)庫(kù)的優(yōu)化器錯(cuò)誤的選擇了某個(gè)索引，導(dǎo)致SQL運(yùn)行很慢
- 某SQL語(yǔ)句可以選擇的索引非常的多，這時(shí)優(yōu)化器選擇執(zhí)行計(jì)劃時(shí)間的開(kāi)銷可能會(huì)大于SQL語(yǔ)句本身。
這里我們接著上面的例子來(lái)講解，首先，我們先為上面的t_index表添加幾個(gè)索引；
接著，我們執(zhí)行下面的語(yǔ)句；
你會(huì)發(fā)現(xiàn)這條語(yǔ)句就可以使用三個(gè)索引，這個(gè)時(shí)候，我們可以顯示的使用索引提示來(lái)使用a這個(gè)索引，如下：
這樣就顯示的使用索引a了，如果這種方式有時(shí)候優(yōu)化器還是沒(méi)有選擇你想要的索引，那么，我們可以另外一種方式FORCE INDEX。
這種方式則一定會(huì)選擇你想要的索引。
2.3 索引優(yōu)化
Multi-Range Read 優(yōu)化
MySQL5.6開(kāi)始支持，這種優(yōu)化的目的是為了減少磁盤的隨機(jī)訪問(wèn)，并且將隨機(jī)訪問(wèn)轉(zhuǎn)化為較為順序的數(shù)據(jù)訪問(wèn)，這種優(yōu)化適用于range、ref、eq_ref類型的查詢。
Multi-Range Read 優(yōu)化的好處：
- 讓數(shù)據(jù)訪問(wèn)變得較為順序。
- 減少緩沖區(qū)中頁(yè)被替換的次數(shù)。
- 批量處理對(duì)鍵值的查詢操作。
我們可以使用參數(shù)optimizer_switch中的標(biāo)記來(lái)控制是否開(kāi)啟Multi-Range Read 優(yōu)化。下面的方式將設(shè)置為總是開(kāi)啟狀態(tài)：
Index Condition Pushdown（ICP） 優(yōu)化
這種優(yōu)化方式也是從MySQL5.6開(kāi)始支持的，不支持這種方式之前，當(dāng)進(jìn)行索引查詢時(shí)，首先我們先根據(jù)索引查找記錄，然后再根據(jù)where條件來(lái)過(guò)濾記錄。然而，當(dāng)支持ICP優(yōu)化后，MySQL數(shù)據(jù)庫(kù)會(huì)在取出索引的同時(shí)，判斷是否可以進(jìn)行where條件過(guò)濾，也就是將where過(guò)濾部分放在了存儲(chǔ)引擎層，大大減少了上層SQL對(duì)記錄的索取。
ICP支持range、ref、eq_ref、ref_or_null類型的查詢，當(dāng)前支持MyISAM和InnoDB存儲(chǔ)引擎。
我們可以使用下面語(yǔ)句開(kāi)啟ICP：
或者關(guān)閉：
當(dāng)開(kāi)啟了ICP之后，在執(zhí)行計(jì)劃Extra可以看到Using index condition提示。
3 索引的特點(diǎn)、優(yōu)點(diǎn)、缺點(diǎn)及適用場(chǎng)景
索引的特點(diǎn)
- 可以加快數(shù)據(jù)庫(kù)的檢索速度
- 降低數(shù)據(jù)庫(kù)插入、修改、刪除等維護(hù)的速度
- 只能創(chuàng)建在表上，不能創(chuàng)建在視圖上
- 既可以直接創(chuàng)建也可以間接創(chuàng)建
索引的優(yōu)點(diǎn)
- 創(chuàng)建唯一性索引，保證數(shù)據(jù)庫(kù)表中的每一行數(shù)據(jù)的唯一性
- 大大加快數(shù)據(jù)的檢索速度
- 加快數(shù)據(jù)庫(kù)表之間的連接，特別是在實(shí)現(xiàn)數(shù)據(jù)的參考完整性方面特別有意義
- 在使用分組和排序字句進(jìn)行數(shù)據(jù)檢索時(shí)，同樣可以顯著減少查詢的時(shí)間
- 通過(guò)使用索引，可以在查詢中使用優(yōu)化隱藏器，提高系統(tǒng)性能
索引的缺點(diǎn)
- 第一，創(chuàng)建索引和維護(hù)索引要耗費(fèi)時(shí)間，這種時(shí)間隨著數(shù)據(jù)量的增加而增加。
- 第二，索引需要占物理空間，除了數(shù)據(jù)表占數(shù)據(jù)空間之外，每一個(gè)索引還要占一定的物理空間，如果要建立聚簇索引，那么需要的空間就會(huì)更大。
- 第三，當(dāng)對(duì)表中的數(shù)據(jù)進(jìn)行增加、刪除和修改的時(shí)候，索引也要?jiǎng)討B(tài)的維護(hù)，這樣就降低了數(shù)據(jù)的維護(hù)速度。
索引的適用場(chǎng)景
- 匹配全值
對(duì)索引中所有列都指定具體值，即是對(duì)索引中的所有列都有等值匹配的條件。
- 匹配值的范圍查詢
對(duì)索引的值能夠進(jìn)行范圍查找。
- 匹配最左前綴
僅僅使用索引中的最左邊列進(jìn)行查詢，比如在 col1 + col2 + col3 字段上的聯(lián)合索引能夠被包含 col1、(col1 + col2)、（col1 + col2 + col3）的等值查詢利用到，可是不能夠被 col2、（col2、col3）的等值查詢利用到。
最左匹配原則可以算是 MySQL 中 B-Tree 索引使用的首要原則。
- 僅僅對(duì)索引進(jìn)行查詢
當(dāng)查詢的列都在索引的字段中時(shí)，查詢的效率更高，所以應(yīng)該盡量避免使用 select *，需要哪些字段，就只查哪些字段。
- 匹配列前綴
僅僅使用索引中的第一列，并且只包含索引第一列的開(kāi)頭一部分進(jìn)行查找。
- 能夠?qū)崿F(xiàn)索引匹配部分精確而其他部分進(jìn)行范圍匹配
- 如果列名是索引，那么使用 column_name is null 就會(huì)使用索引，例如下面的就會(huì)使用索引：
- 經(jīng)常出現(xiàn)在關(guān)鍵字order by、group by、distinct后面的字段
- 在union等集合操作的結(jié)果集字段
- 經(jīng)常用作表連接的字段
- 考慮使用索引覆蓋，對(duì)數(shù)據(jù)很少被更新，如果用戶經(jīng)常值查詢其中你的幾個(gè)字段，可以考慮在這幾個(gè)字段上建立索引，從而將表的掃描變?yōu)樗饕膾呙?/li>
索引失效情況
- 以%開(kāi)頭的 like 查詢不能利用 B-Tree 索引，執(zhí)行計(jì)劃中 key 的值為 null 表示沒(méi)有使用索引
- 數(shù)據(jù)類型出現(xiàn)隱式轉(zhuǎn)換的時(shí)候也不會(huì)使用索引，例如，where 'age'+10=30
- 對(duì)索引列進(jìn)行函數(shù)運(yùn)算，原因同上
- 正則表達(dá)式不會(huì)使用索引
- 字符串和數(shù)據(jù)比較不會(huì)使用索引
- 復(fù)合索引的情況下，假如查詢條件不包含索引列最左邊部分，即不滿足最左原則 leftmost，是不會(huì)使用復(fù)合索引的
- 如果 MySQL 估計(jì)使用索引比全表掃描更慢，則不使用索引
- 用 or 分割開(kāi)的條件，如果 or 前的條件中的列有索引，而后面的列中沒(méi)有索引，那么涉及的索引都不會(huì)被用到
- 使用負(fù)向查詢（not ，not in， not like ，<> ,!= ,!> ,!< ） 不會(huì)使用索引
