帶你把MySQL索引吃透了

本篇文章給大家?guī)砹岁P(guān)于mysql的相關(guān)知識，其中主要介紹了mysql高級篇的一些問題，包括了索引是什么、索引底層實現(xiàn)等等問題，下面一起來看一下，希望對大家有幫助。

推薦學(xué)習(xí)：mysql

mysql，一個熟悉又陌生的名詞，早在學(xué)習(xí)Javaweb的時候，我們就用到了MySQL數(shù)據(jù)庫，在那個階段，MySQL對我們來說似乎只是一個存儲數(shù)據(jù)的好東西，存儲時一股腦往里邊塞，查詢時也是盲目的全表查詢(不帶一點點優(yōu)化)。

我們總是自欺欺人的覺得，我們通過其他方面來優(yōu)化就好了阿，遲遲不愿面對MySQL高級，轉(zhuǎn)而學(xué)習(xí)一些看似更為”高級”的東西，學(xué)Redis，來分擔MySQL的壓力，學(xué)MyCat等中間件，實現(xiàn)主從復(fù)制，讀寫分離，分庫分表等等。(說的就是melo沒錯了)

到了準備面試的時候，發(fā)現(xiàn)面試題里邊的MySQL一問三不知~

而自己學(xué)到的前沿中間件，問得幾乎很少！！自己也只是會用，寫簡歷時只能弱弱寫上”了解”xxx中間件……

當然了，學(xué)習(xí)MySQL高級篇，不單單只是為了面試，實際的項目中，這一塊的優(yōu)化是十分重要的，體驗過服務(wù)器宕機后，只能默默……..

從現(xiàn)在開始吧，此時上岸還來得及！！！趁著金三銀四，補充補充MySQL高級篇的知識點，從如下幾方面開啟 MySQL高級篇之旅

建議通過側(cè)邊欄目錄檢索對您有幫助的部分，其中有emoji表情前綴屬于重點部分，覺得對您有幫助的話，小編還會持續(xù)更進完善本篇文章和MySQL專欄。

索引定義

MySQL官方對索引的定義為：索引（index）是幫助MySQL高效獲取數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)（有序）。索引是在數(shù)據(jù)庫表的字段上添加的，是為了提高查詢效率存在的一種機制。除了數(shù)據(jù)之外，數(shù)據(jù)庫系統(tǒng)還維護著滿足特定查找算法的數(shù)據(jù)結(jié)構(gòu)，這些數(shù)據(jù)結(jié)構(gòu)以某種方式引用（指向）數(shù)據(jù)， 這樣就可以在這些數(shù)據(jù)結(jié)構(gòu)上實現(xiàn)高級查找算法，這種數(shù)據(jù)結(jié)構(gòu)就是索引。如下面的示意圖所示 :

其實簡單來說，索引就是一個排好序的數(shù)據(jù)結(jié)構(gòu)

左邊是數(shù)據(jù)表，一共有兩列七條記錄，最左邊的是數(shù)據(jù)記錄的物理地址（注意邏輯上相鄰的記錄在磁盤上也并不是一定物理相鄰的）。為了加快Col2的查找，可以維護一個右邊所示的二叉查找樹，每個節(jié)點分別包含索引鍵值和一個指向?qū)?yīng)數(shù)據(jù)記錄物理地址的指針，這樣就可以運用二叉查找快速獲取到相應(yīng)數(shù)據(jù)。

索引優(yōu)勢

• 加快查找和排序的速率，降低數(shù)據(jù)庫的IO成本以及CPU的消耗
• 通過創(chuàng)建唯一性索引，可以保證數(shù)據(jù)庫表中每一行數(shù)據(jù)的唯一性。

索引劣勢

1. 索引實際上也是一張表，保存了主鍵和索引字段，并指向?qū)嶓w類的記錄，本身需要占用空間
2. 雖然增加了查詢效率，但對于增刪改，每次改動表，還需要更新一下索引 新增：自然需要在索引樹中新增節(jié)點 刪除：索引樹中指向的記錄可能會失效，意味著這棵索引樹很多節(jié)點，都是失效的 改動：索引樹中節(jié)點的指向可能需要改變

但實際上呢，我們MySQL中并不是用二叉查找樹來存儲，為何呢？

要知道，二叉查找樹，此處一個節(jié)點只能存儲一條數(shù)據(jù)，而一個節(jié)點呢，在MySQL里邊又對應(yīng)一個磁盤塊，這樣我們每次讀取一個磁盤塊，只能獲取一條數(shù)據(jù)，效率特別的低，所以我們會想到采用B樹這種結(jié)構(gòu)來存儲。

索引結(jié)構(gòu)

索引是在MySQL的存儲引擎層中實現(xiàn)的，而不是在服務(wù)器層實現(xiàn)的。所以每種存儲引擎的索引都不一定完全相同，而且也不是所有的引擎都支持所有的索引類型。

• BTREE 索引 ： 最常見的索引類型，大部分索引都支持 B 樹索引。
• HASH 索引：只有Memory引擎支持 ， 使用場景簡單 。
• R-tree 索引（空間索引）：空間索引是MyISAM引擎的一個特殊索引類型，主要用于地理空間數(shù)據(jù)類型，通常使用較少，不做特別介紹。
• Full-text （全文索引） ：全文索引也是MyISAM的一個特殊索引類型，主要用于全文索引，InnoDB從Mysql5.6版本開始支持全文索引。

MyISAM、InnoDB、Memory三種存儲引擎對各種索引類型的支持

我們平常所說的索引，如果沒有特別指明，都是指B+樹（多路搜索樹，并不一定是二叉的）結(jié)構(gòu)組織的索引。其中聚集索引、復(fù)合索引、前綴索引、唯一索引默認都是使用 B+tree 索引，統(tǒng)稱為 索引。

BTREE

多路平衡搜索樹，一棵m階(m叉)BTREE滿足：

• 每個節(jié)點最多m個孩子 孩子個數(shù)：ceil(m/2) 到 m 關(guān)鍵字個數(shù)：ceil(m/2)-1 到 m-1

ceil表示向上取整，ceil(2.3)=3

插入關(guān)鍵字案例

保證不破壞m階B樹的性質(zhì)

由于3階，最多只能2個節(jié)點，所以一開始26和30在一起，之后再來個85就要開始分裂了，30作為中間上位，26保持，85去到右邊
即：中間位置上位，然后左邊留在舊節(jié)點，右邊去到新結(jié)點

如圖中的70再插入的時候，70剛好是中間位置上位，然后62保持，85又去分一個新節(jié)點出來

上位后又需要分裂

繼續(xù)向上分裂即可，同理的

相比優(yōu)勢

相比二叉搜索樹，高度/深度更低，自然查詢效率更高。

B+TREE

• B+樹有兩種類型的節(jié)點：內(nèi)部結(jié)點（也稱索引結(jié)點）和葉子結(jié)點。內(nèi)部節(jié)點就是非葉子節(jié)點，內(nèi)部節(jié)點不存儲數(shù)據(jù)，只存儲索引，數(shù)據(jù)都存儲在葉子節(jié)點。
• 內(nèi)部結(jié)點中的key都按照從小到大的順序排列，對于內(nèi)部結(jié)點中的一個key，左樹中的所有key都小于它，右子樹中的key都大于等于它。葉子結(jié)點中的記錄也按照key的大小排列。
• 每個葉子結(jié)點都存有相鄰葉子結(jié)點的指針，葉子結(jié)點本身依關(guān)鍵字的大小自小而大順序連接。
• 父節(jié)點存有右孩子的第一個元素的索引。

相比優(yōu)勢

• B+Tree的查詢效率更加穩(wěn)定。由于B+Tree只有葉子節(jié)點保存key信息，查詢?nèi)魏蝛ey都要從root走到葉子，所以更穩(wěn)定。
• 只需遍歷葉子節(jié)點，就可以實現(xiàn)整棵樹的遍歷。

MySQL中的B+Tree

MySql索引數(shù)據(jù)結(jié)構(gòu)對經(jīng)典的B+Tree進行了優(yōu)化。在原B+Tree的基礎(chǔ)上，增加一個指向相鄰葉子節(jié)點的鏈表指針(整體類似一個雙向鏈表的結(jié)構(gòu))，就形成了帶有順序指針的B+Tree，提高區(qū)間訪問的性能。

細心的同學(xué)可以看出，這張圖跟我們的二叉查找樹簡圖的一個最大區(qū)別是什么？

• 從二叉查找樹過渡到B樹，有一個顯著的變化就是，一個節(jié)點可以存儲多個數(shù)據(jù)了，相當于一個磁盤塊里邊可以存儲多個數(shù)據(jù)，大大減少了我們的 IO次數(shù)！！

MySQL中的 B+Tree 索引結(jié)構(gòu)示意圖：

二叉查找樹簡圖：

索引原理

BTree索引：

初始化介紹

淺藍色的稱之為一個磁盤塊，可以看到每個磁盤塊包含幾個數(shù)據(jù)項（深藍色所示）和指針（黃色所示)
如磁盤塊1包含數(shù)據(jù)項17和35，包含指針P1、P2、P3,
P1表示小于17的磁盤塊，P2表示在17和35之間的磁盤塊，P3表示大于35的磁盤塊。

• 真實的數(shù)據(jù)存在于葉子節(jié)點即3、5、9、10、13、15、28、29、36、60、75、79、90、99。`
• 非葉子節(jié)點不存儲真實的數(shù)據(jù)，只存儲指引搜索方向的數(shù)據(jù)項，如17、35并不真實存在于數(shù)據(jù)表中。`

查找過程

如果要查找數(shù)據(jù)項29，那么首先會把磁盤塊1由磁盤加載到內(nèi)存，此時發(fā)生一次IO。在內(nèi)存中用二分查找確定29在17和35之間，鎖定磁盤塊1的P2指針，內(nèi)存時間因為非常短（相比磁盤的IO）可以忽略不計，通過磁盤塊1的P2指針的磁盤地址把磁盤塊3由磁盤加載到內(nèi)存，發(fā)生第二次IO，29在26和30之間，鎖定磁盤塊3的P2指針，通過指針加載磁盤塊8到內(nèi)存，發(fā)生第三次IO，同時內(nèi)存中通過二分查找搜索到29，結(jié)束查詢，總計三次IO。

真實的情況是，3層的B+樹可以表示上百萬的數(shù)據(jù)，如果上百萬的數(shù)據(jù)查找只需要三次IO，性能提高將是巨大的，如果沒有索引，每個數(shù)據(jù)項都要發(fā)生一次IO，那么總共需要百萬次的IO，顯然成本非常非常高。

索引分類

在InnoDB中，表都是根據(jù)主鍵順序以索引的形式存放的，這種存儲方式的表稱為索引組織表。又因為前面我們提到的，InnoDB使用了B+樹索引模型，所以數(shù)據(jù)都是存儲在B+樹中的。

每一個索引在InnoDB里面對應(yīng)一棵B+樹。
假設(shè)，我們有一個主鍵列為ID的表，表中有字段k，并且在k上有索引。
這個表的建表語句是：

mysql> create table T(    id int primary key,    k int not null,     name varchar(16),    index (k))engine=InnoDB;  復(fù)制代碼

表中R1~R5的(ID,k)值分別為(100,1)、(200,2)、(300,3)、(500,5)和(600,6)，兩棵樹的示例示意圖如下：

從圖中不難看出，根據(jù)葉子節(jié)點的內(nèi)容，索引類型分為主鍵索引和非主鍵索引。

主鍵索引

數(shù)據(jù)表的主鍵列使用的就是主鍵索引，且會默認創(chuàng)建，這也是為什么，我們還沒學(xué)索引的時候，老師經(jīng)常跟我們說根據(jù)主鍵查會快一點，原來主鍵本身就建好了索引。
主鍵索引的葉子節(jié)點存的是整行數(shù)據(jù)。在InnoDB里，主鍵索引也被稱為聚簇索引（clustered index）。

輔助索引

輔助索引的葉子節(jié)點內(nèi)容是主鍵的值。在InnoDB里，輔助索引也被稱為二級索引（secondary index）。

如下圖：

• 主鍵索引存放了整行數(shù)據(jù)
• 輔助索引只存放了自己本身，以及id主鍵用于回表查詢

根據(jù)上面的索引結(jié)構(gòu)，我們來討論一個問題：基于主鍵索引和輔助索引的查詢有什么區(qū)別？

• 如果語句是select * from T where ID=500，即主鍵查詢方式，則只需要搜索ID這棵B+樹；
• 如果語句是select * from T where k=5，即普通索引查詢方式，則需要先搜索k索引樹，得到ID的值為500，再到ID索引樹搜索一次。這個過程稱為回表。

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

除非說，我們所要查詢的數(shù)據(jù)，剛好就是我們索引樹上存在的，此時我們稱之為覆蓋索引–即索引列中包含了我們要查詢的所有數(shù)據(jù)。

同時，二級索引又分為了如下幾種(先簡單略過即可，后續(xù)我們再慢慢了解)：

• 唯一索引(Unique Key) ：唯一索引也是一種約束。唯一索引的屬性列不能出現(xiàn)重復(fù)的數(shù)據(jù)，但是允許數(shù)據(jù)為 NULL，一張表允許創(chuàng)建多個唯一索引。 建立唯一索引的目的大部分時候都是為了該屬性列的數(shù)據(jù)的唯一性，而不是為了查詢效率。
• 普通索引(Index) ：普通索引的唯一作用就是為了快速查詢數(shù)據(jù)，一張表允許創(chuàng)建多個普通索引，并允許數(shù)據(jù)重復(fù)和 NULL。
• 前綴索引(Prefix) ：前綴索引只適用于字符串類型的數(shù)據(jù)。前綴索引是對文本的前幾個字符創(chuàng)建索引，相比普通索引建立的數(shù)據(jù)更小， 因為只取前幾個字符。
• 全文索引(Full Text) ：全文索引主要是為了檢索大文本數(shù)據(jù)中的關(guān)鍵字的信息，是目前搜索引擎數(shù)據(jù)庫使用的一種技術(shù)。Mysql5.6 之前只有 MYISAM 引擎支持全文索引，5.6 之后 InnoDB 也支持了全文索引

擴展–索引下推

所謂下推，顧名思義，其實是推遲我們的回表操作，MySQL不會輕而易舉讓我們?nèi)セ乇恚驗楹芾速M。什么意思呢？來看下邊這個例子。

我們建立了一個復(fù)合索引(name，status，address)，索引中也是按這個字段來存儲的，類似圖中這樣：

復(fù)合索引樹(只存儲索引列和主鍵用于回表)

我們執(zhí)行這樣一條語句：

SELECT name FROM tb_seller WHERE name like '小米%' and status ='1' ; 復(fù)制代碼

1. 首先我們在復(fù)合索引樹上，找到了第一個以小米開頭的name — 小米1
2. 此時我們不著急回表(回到主鍵索引樹搜索的過程，我們稱為回表)，而是先在復(fù)合索引樹判斷status是否=1，此時status=0，我們直接就不回表了，直接繼續(xù)找下一個以小米開頭的name

1. 找到第二個– 小米2，判斷status=1，則根據(jù)id=2去主鍵索引樹上找，得到所有的數(shù)據(jù)

這種先在自身索引樹上判斷是否滿足其他的where條件，不滿足則直接pass掉，不進行回表的操作，就叫做索引下推。

最左前綴原則

所謂最左前綴，可以想象成一個爬樓梯的過程，假設(shè)我們有一個復(fù)合索引：name，status，address，那這個樓梯由低到高依次順序是：name，status，address，最左前綴，要求我們不能出現(xiàn)跳躍樓梯的情況，否則會導(dǎo)致我們的索引失效：

1. 按樓梯從低到高，無出現(xiàn)跳躍的情況–此時符合最左前綴原則，索引不會失效

   

2. 出現(xiàn)跳躍的情況

• 直接第一層name都不走，當然都失效

  

• 走了第一層，但是后續(xù)直接第三層，只有出現(xiàn)跳躍情況前的不會失效（此處就只有name成功）

  

• 同時，這個順序并不是由我們where中的排列順序決定，比如： where name=’小米科技’ and status=’1′ and address=’北京市’ where status=’1′ and name=’小米科技’ and address=’北京市’

這兩個盡管where中字段的順序不一樣，第二個看起來越級了，但實際上效果是一樣的

其實是因為我們MySQL有一個Optimizer（查詢優(yōu)化器），查詢優(yōu)化器會將SQL進行優(yōu)化，選擇最優(yōu)的查詢計劃來執(zhí)行。

• 關(guān)于這個查詢優(yōu)化器，后續(xù)文章我們也會談?wù)凪ySQL的邏輯架構(gòu)與存儲引擎

索引設(shè)計原則

針對表

1. 查詢頻次高，且數(shù)據(jù)量多的表

針對字段

1. 最好從where子句的條件中提取，如果where子句中的組合比較多，那么應(yīng)當挑選最常用、過濾效果最好的列的組合。

其他原則

1. 最好用唯一索引，區(qū)分度越高，使用索引的效率越高
2. 不是越多越好，維護也需要時間和空間代價，建議單張表索引不超過 5 個

因為 MySQL 優(yōu)化器在選擇如何優(yōu)化查詢時，會根據(jù)統(tǒng)一信息，對每一個可以用到的索引來進行評估，以生成出一個最好的執(zhí)行計劃，如果同時有很多個索引都可以用于查詢，就會增加 MySQL 優(yōu)化器生成執(zhí)行計劃的時間，同樣會降低查詢性能。

比如：

我們創(chuàng)建了三個單列索引，name，status，address

當我們where中根據(jù)status和address兩個字段來查詢時，數(shù)據(jù)庫只會選擇最優(yōu)的一個索引，不會所有單列索引都使用。

最優(yōu)的索引：具體是指所查詢表中，辨識度最高(所占比例最少)的索引列，比如此處address中有一個辨識度很高的 ‘西安市’數(shù)據(jù)；

1. 使用短索引,索引創(chuàng)建之后也是使用硬盤來存儲的，因此提升索引訪問的I/O效率，也可以提升總體的訪問效率。假如構(gòu)成索引的字段總長度比較短，那么在給定大小的存儲塊內(nèi)可以存儲更多的索引值，相應(yīng)的可以有效的提升MySQL訪問索引的I/O效率。
2. 利用最左前綴，比如有N個字段，我們不一定需要創(chuàng)建N個索引，可以用復(fù)合索引

也就是說，我們盡量創(chuàng)建復(fù)合索引，而不是單列索引

創(chuàng)建復(fù)合索引: 	CREATE INDEX idx_name_email_status ON tb_seller(name,email,status);  就相當于 	對name 創(chuàng)建索引 ; 	對name , email 創(chuàng)建了索引 ; 	對name , email, status 創(chuàng)建了索引 ; 復(fù)制代碼

舉個栗子

假設(shè)我們有這么一個表，id為主鍵，沒有創(chuàng)建索引：

CREATE TABLE `tuser` (   `id` int(11) NOT NULL,   `name` varchar(32) DEFAULT NULL,   `age` int(11) DEFAULT NULL,   PRIMARY KEY (`id`), ) ENGINE=InnoDB 復(fù)制代碼

如果要在此處建立復(fù)合索引，我們要遵循什么原則呢？

通過調(diào)整順序，可以少維護一個索引

• 比如我們的業(yè)務(wù)需求里邊，有如下兩種查詢方式： 根據(jù)name查詢 根據(jù)name和age查詢

如果我們建立索引（age,name），由于最左前綴原則，我們這個索引能實現(xiàn)的是根據(jù)age，根據(jù)age和name查詢，并不能單純根據(jù)name查詢（因為跳躍了），為了實現(xiàn)我們的需求，我們還得再建立一個name索引；

而如果我們通過調(diào)整順序，改成（name，age），就能實現(xiàn)我們的需求了，無需再維護一個name索引，這就是通過調(diào)整順序，可以少維護一個索引。

考慮空間->短索引

• 比如我們的業(yè)務(wù)需求里邊，有以下兩種查詢方式： 根據(jù)name查詢 根據(jù)age查詢 根據(jù)name和age查詢

我們有兩種方案：

1. 建立聯(lián)合索引(name,age)，建立單列索引：age索引。
2. 建立聯(lián)合索引(age,name)，建立單列索引：name索引。

這兩種方案都能實現(xiàn)我們的需求，這個時候我們就要考慮空間了，name字段是比age字段大的，顯然方案1所耗費的空間是更小的，所以我們更傾向于方案1。

何時建立索引

1. where中的查詢字段
2. 查詢中與其他表關(guān)聯(lián)的字段，比如外鍵
3. 排序的字段
4. 統(tǒng)計或分組的字段

何時達咩索引

1. 表中數(shù)據(jù)量很少
2. 經(jīng)常改動的表
3. 頻繁更新的字段
4. 數(shù)據(jù)重復(fù)且分布均勻的表字段（比如包含了很多重復(fù)數(shù)據(jù)，那此時多叉樹的二分查找，其實用處不大，可以理解為O(logn)退化了）

索引相關(guān)語法

創(chuàng)建索引

默認會為主鍵創(chuàng)建索引–primary

CREATE 	[UNIQUE|FULLTEXT|SPATIAL]  INDEX index_name  [USING  index_type] ON tbl_name(index_col_name,...)  index_col_name : column_name[(length)][ASC | DESC] 復(fù)制代碼

查找索引

結(jié)尾加上G，可以變成豎屏顯示

select index from tbl_nameG; 復(fù)制代碼

刪除索引

drop INDEX index_name on tbl_name ; 復(fù)制代碼

變更索引

1). alter  table  tb_name  add  primary  key(column_list);  	該語句添加一個主鍵，這意味著索引值必須是唯一的，且不能為NULL	 	 2). alter  table  tb_name  add  unique index_name(column_list); 	這條語句創(chuàng)建索引的值必須是唯一的（除了NULL外，NULL可能會出現(xiàn)多次） 	 3). alter  table  tb_name  add  index index_name(column_list); 	添加普通索引， 索引值可以出現(xiàn)多次。 	 4). alter  table  tb_name  add  fulltext  index_name(column_list); 	該語句指定了索引為FULLTEXT， 用于全文索引 復(fù)制代碼

查看索引使用情況

show status like 'Handler_read%';	 -- 查看當前會話索引使用情況  show global status like 'Handler_read%';	-- 查看全局索引使用情況 復(fù)制代碼

Handler_read_first：索引中第一條被讀的次數(shù)。如果較高，表示服務(wù)器正執(zhí)行大量全索引掃描（這個值越低越好）。

Handler_read_key：如果索引正在工作，這個值代表一個行被索引值讀的次數(shù)，如果值越低，表示索引得到的性能改善不高，因為索引不經(jīng)常使用（這個值越高越好）。

Handler_read_next ：按照鍵順序讀下一行的請求數(shù)。如果你用范圍約束或如果執(zhí)行索引掃描來查詢索引列，該值增加。

Handler_read_prev：按照鍵順序讀前一行的請求數(shù)。該讀方法主要用于優(yōu)化ORDER BY … DESC。

Handler_read_rnd ：根據(jù)固定位置讀一行的請求數(shù)。如果你正執(zhí)行大量查詢并需要對結(jié)果進行排序該值較高。你可能使用了大量需要MySQL掃描整個表的查詢或你的連接沒有正確使用鍵。這個值較高，意味著運行效率低，應(yīng)該建立索引來補救。

Handler_read_rnd_next：在數(shù)據(jù)文件中讀下一行的請求數(shù)。如果你正進行大量的表掃描，該值較高。通常說明你的表索引不正確或?qū)懭氲牟樵儧]有利用索引。

總結(jié)

1. 索引簡單來說就是一個排好序的數(shù)據(jù)結(jié)構(gòu)，可以方便我們檢索數(shù)據(jù)，而不需要盲目的進行全表掃描。
2. 索引底層有很多種實現(xiàn)結(jié)構(gòu)，這篇主要只是講解了BTREE索引，如果對樹這一數(shù)據(jù)結(jié)構(gòu)還不太熟悉的小伙伴，可以關(guān)注我后續(xù)數(shù)據(jù)結(jié)構(gòu)專欄，會整理關(guān)于普通樹，二叉樹，二叉排序樹的文章。
3. 索引分類：
   1. 主鍵索引
   2. 輔助索引

這里我們還擴展了索引下推，是一個十分重要的知識點，需要仔細回味。

1. 索引的相關(guān)設(shè)計原則，索引雖好，但也不可貪杯，不能為了用索引而建索引。
2. 索引的相關(guān)語法，很容易上手的。
3. 查看索引的使用情況。

推薦學(xué)習(xí)：mysql