助你進(jìn)大廠，這些Mysql索引底層知識(shí)你是必須知道的。
前言
上一篇總結(jié)了Mysql的鎖機(jī)制，通過讀者的反映和閱讀量顯示，總體還是不錯(cuò)的，感興趣的可以閱讀一下[大廠面試官必問的Mysql鎖機(jī)制]。
寫了那么多的Mysql文章，有讀者問我是不是dba，工作真的需要掌握那么深嗎。我想說的是：我是一名Java全職開發(fā)人員不是dba。
假如你只滿足于日常的crud，你可以放棄這些底層的知識(shí)，可以不必學(xué)的那么深，若是你想往高處走，這些底層的知識(shí)，是你必備的。
這一篇是講解Mysql中做使用到的「索引的種類」，「索引正確使用的原則」、「怎么優(yōu)化索引」、「以及兩種存儲(chǔ)引擎InnoDB和MyISAM索引的數(shù)據(jù)布局原理」。
索引種類
在說索引之前，我們先來說一說什么是索引呢？對(duì)于索引個(gè)人的理解就是，索引是一種加快查詢數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)。
所以，索引就是一種數(shù)據(jù)結(jié)構(gòu)，作用就是發(fā)揮這種數(shù)據(jù)結(jié)構(gòu)的作用，加快查詢的效率，例如：InnoDB存儲(chǔ)引擎中使用的是就是B+tree這種數(shù)據(jù)結(jié)構(gòu)來組織索引。
Mysql中索引的種類也不是很多，不同類型的索引有不同的作用，索引的作用相互之間也存在交叉關(guān)系，Mysql中索引主要分為以下幾類：
-
「主鍵索引」（ PRIMARY KEY）：主鍵索引一般都是在創(chuàng)建表的時(shí)候指定， 「一個(gè)表只有一個(gè)主鍵索引」，特點(diǎn)是 「唯一、非空」。 -
「唯一索引」（ UNIQUE）：唯一索引具有的特點(diǎn)就是唯一性，可以在創(chuàng)建表的時(shí)候指定，也可以在創(chuàng)建表后創(chuàng)建。 -
「普通索引」（ INDEX）：普通索引唯一的作用就是加快查詢。 -
「組合索引」（ INDEX）：組合索引是創(chuàng)建一個(gè) 「多個(gè)字段的索引」，這個(gè)概念是相對(duì)于上上面的單列索引而言，組合索引查詢遵循 「最左前綴原則」。 -
「全文索引」（ FULLTEXT）：全文索引是針對(duì)一些大的 「文本字段」創(chuàng)建的索引，也稱為 「全文檢索」。 -
「聚簇索引」和 「非聚簇索引」：聚簇索引和非聚簇索引的概念比上面的概念要大，屬于包含和被包含的關(guān)系。例如：InnoDB中主鍵索引使用的就是聚簇索引。
若是你想查看一個(gè)表的所有索引，可以執(zhí)行下面的sql來查看：
show index from 表名
例如，查看我自己的測(cè)試表里面的索引，如下圖所示，Key_name表示索引的名字，Column_name表示索引的字段。
上面大概的說了主要索引的概念，下面詳細(xì)的介紹一下這幾大索引的特點(diǎn)和使用。
主鍵索引
主鍵索引在InnoDB存儲(chǔ)引擎中是最常見的索引類型，一個(gè)表都會(huì)有一個(gè)主鍵索引，它索引的字段不允許為空值，并且唯一。
一般是在創(chuàng)建表的時(shí)候，可以通過RIMARY KEY指定主鍵索引，在InnoDB存儲(chǔ)引擎中，若是創(chuàng)建表的時(shí)候沒有主觀創(chuàng)建主鍵索引，Mysql就會(huì)看表中是否有唯一索引，有，就會(huì)指定「非空的唯一索引」為主鍵索引；
沒有，就會(huì)默認(rèn)生成一個(gè)6byte空間的自動(dòng)增長(zhǎng)主鍵作為主鍵索引，可以通過select _rowid from 表名查詢的是對(duì)應(yīng)的主鍵值.。
MyISAM儲(chǔ)存引擎是可以不存在主鍵索引，MyISAM和InnoDB儲(chǔ)存數(shù)據(jù)的結(jié)構(gòu)方式還是有明顯的區(qū)別，這個(gè)后面篇章會(huì)詳細(xì)講解。
唯一索引
唯一索引與主鍵索引的區(qū)別就是，唯一索引允許為空，若是在組合索引中，只要?jiǎng)?chuàng)建的列值是唯一的
唯一索引在實(shí)際中更多的是用來保證數(shù)據(jù)的唯一性，假如你僅僅要數(shù)據(jù)能夠快速查詢，你也可以使用普通索引，所以唯一索引重在體現(xiàn)它的唯一性。
實(shí)際的業(yè)務(wù)場(chǎng)景，有些庫表字段要求唯一，就可以使用唯一索引，創(chuàng)建唯一索引的方式有三種。
（1）一個(gè)是在創(chuàng)建表的時(shí)候指定，如下sql：
CREATE TABLE user(
id INT PRIMARY KEY NOT NULL,
name VARCHAR(16) NOT NULL,
UNIQUE unique_name (name(10))
);
（2）也可以在表創(chuàng)建后創(chuàng)建，如下sql：
CREATE UNIQUE INDEX unique_name ON user(name(10))；
（3）通過修改表結(jié)構(gòu)創(chuàng)建，如下sql：
ALTER user ADD UNIQUE unique_name ON (name(10))
這里有一個(gè)細(xì)節(jié)要注意的是創(chuàng)建的name字段，指定的長(zhǎng)度是16字符，而創(chuàng)建的索引的長(zhǎng)度制定的是10字符，因?yàn)橐矝]有人的名字長(zhǎng)度會(huì)超過10個(gè)字符，所以減少索引長(zhǎng)度，能夠減少索引所占的空間的大小。
普通索引
普通索引的唯一作用就是加快數(shù)據(jù)的查詢，一般對(duì)查詢語句WHERE和ORDER BY后面的字段創(chuàng)建普通索引。
創(chuàng)建普通索引的方式也有三種，基本和創(chuàng)建唯一索引的方式一樣，只是把關(guān)鍵字UNIQUE換成INDEX，如下所示：
// 創(chuàng)建表的時(shí)候創(chuàng)建
CREATE TABLE user(
id INT PRIMARY KEY NOT NULL,
name VARCHAR(16) NOT NULL,
INDEX index_name (name(10))
);
// 創(chuàng)建表后創(chuàng)建
CREATE INDEX INDEX index_name ON user(name(10))；
// 修改表結(jié)構(gòu)創(chuàng)建
ALTER user ADD INDEX index_name ON (name(10))
若是想刪除索引，可以通過執(zhí)行下面的sql進(jìn)行刪除索引：
DROP INDEX index_name ON user;
組合索引
組合索引即用多個(gè)字段創(chuàng)建一個(gè)索引，組合索引能夠避免「回表查詢」，相對(duì)于多字段的單列索引，組合索引的查詢效率更高。
創(chuàng)建組合索引(聯(lián)合索引)的方式和上面創(chuàng)建普通索引的方式一樣，只不過字段的數(shù)目多了，如下sql創(chuàng)建：
// 其它方式和上面的一樣，這里就只列舉修改表結(jié)構(gòu)的方式創(chuàng)建
ALTER TABLE employee ADD INDEX name_age_sex (name(10),age,sex);
回表查詢
什么是回表查詢呢？回表查詢簡(jiǎn)單來說「通過二級(jí)索引查詢數(shù)據(jù)，得不到完整的數(shù)據(jù)行，需要再次查詢主鍵索引來獲得數(shù)據(jù)行」。
InnoDB存儲(chǔ)引擎中，索引分為 「聚簇索引」和「二級(jí)索引」，主鍵索引就是聚簇索引，其它的索引為二級(jí)索引。
聚簇索引中的葉子節(jié)點(diǎn)保存著完整的數(shù)據(jù)行，而二級(jí)索引的葉子節(jié)點(diǎn)并不是保存完整的數(shù)據(jù)行。
上面提到InnoDB表是一定要有主鍵索引的，雖然索引占據(jù)空間，但是索引符合二分查找的算法，查找數(shù)據(jù)非常的快。
假設(shè)還是上面的employee表，里面有主鍵索引id，和普通的索引name，那么在InnoDB中就會(huì)存在兩棵B+Tree，一棵是主鍵索引樹：
在主鍵索引樹中的葉子節(jié)點(diǎn)存儲(chǔ)的是完整的數(shù)據(jù)行，另外一棵是name字段的二級(jí)索引樹，如下圖所示：
倘若你執(zhí)行這條sql：select name, age, sex from employee where id ='as';就會(huì)先執(zhí)行二級(jí)索引的查詢，當(dāng)查詢name='as'時(shí)，得到主鍵為50，再根據(jù)主鍵查詢主鍵索引樹，得到完整的數(shù)據(jù)行，具體的執(zhí)行流程如下：
這個(gè)就是回表查詢，回表查詢會(huì)查詢兩次，這樣就會(huì)降低查詢的效率，為了避免回表查詢，只查詢一次就能得到完整的數(shù)據(jù)呢？
索引覆蓋
常見的方式就是「建立組合索引（聯(lián)合索引）「進(jìn)行」索引覆蓋」，什么是索引覆蓋呢？索引覆蓋就是「索引的葉子節(jié)點(diǎn)已經(jīng)包含了查詢的數(shù)據(jù)，沒必要再回表進(jìn)行查詢?！?/strong>
假如我還是執(zhí)行如下sql：select name, age, sex from employee where name ='as';因?yàn)槠胀ㄋ饕挥衝ame字段才建立了索引，這必然會(huì)導(dǎo)致回表查詢。
為了提高查詢效率，就(name)「單列索引升級(jí)為聯(lián)合索引」(name, age, sex)就不同了。
因?yàn)榻⒌穆?lián)合索引，在二級(jí)節(jié)點(diǎn)的葉子階段就會(huì)同時(shí)存在name, age, sex三個(gè)的值，一次性就會(huì)獲得所需要的數(shù)據(jù)，這樣就避免了回表，但是所有的方案都不是完美的。
若是這個(gè)聯(lián)合索引哪一天某一個(gè)數(shù)據(jù)行的name值改變了或者age改變了，我就需要同時(shí)維護(hù)主鍵索引和聯(lián)合索引兩棵樹，這樣的維護(hù)成本就高了，性能開銷也大了。
相比之前數(shù)據(jù)的改變，我只需要維護(hù)主鍵索引即可，聯(lián)合索引的創(chuàng)建就導(dǎo)致了需要同時(shí)維護(hù)兩棵樹，這樣就會(huì)影響插入、更新數(shù)據(jù)的操作，所以并沒有哪種方案是完美的。
最左前綴原則
我們知道單列索引是按照索引列有序性的進(jìn)行組織B+Tree結(jié)構(gòu)的，聯(lián)合索引又是怎么組織B+Tree呢？
聯(lián)合索引其實(shí)也是按照創(chuàng)建索引的時(shí)候，最左邊的進(jìn)行最開始的排序，也就是「最左前綴原則」，比如一個(gè)表中有如下數(shù)據(jù)：
| name | age | sex |
|---|---|---|
| ad | 23 | 男 |
| bc | 21 | 男 |
| bc | 24 | 女 |
| bc | 25 | 男 |
| de | 21 | 女 |
如上圖所示，對(duì)于聯(lián)合索引中name字段是放在最前面的，所以name是完全有序的，但是age字段就不是有序的，只有當(dāng)name相同，例如：name='bc'此時(shí)age字段的索引排序才是完全有序的。
所以你會(huì)發(fā)現(xiàn)，在聯(lián)合索引中你只有使用以下的規(guī)則的方式查詢才會(huì)使用到索引：
-
name,age,sex -
name,age -
name
因?yàn)镸ysql的底層有查詢優(yōu)化器，會(huì)判斷sql執(zhí)行的時(shí)候若是使用全表掃描的效率比使用索引的效率更高，就會(huì)使用全表掃描。
假如，我查詢的時(shí)候使用age>=23,sex='男';兩個(gè)字段作為查詢條件，但是沒有使用name字段，因?yàn)樵趎ame不知情的條件下，對(duì)于age是無序的。
對(duì)于age>=23條件可能在很多的name不同中都有符合條件的出現(xiàn)，所以就沒有辦法使用索引，這也是索引實(shí)現(xiàn)的原因，一定要遵循「查找有序，充分的利用索引的有序性」。
假如你是分別在name，age，sex三個(gè)字段中分別建立三個(gè)單列索引，就相當(dāng)于建立三顆索引樹，那么它的查詢效率，比我們使用一棵索引樹查詢效率就可想而知了。
有一種情況即使使用到了最左邊的name字段也不會(huì)使用索引，例如：WHERE name like '%d%'；這種like條件的模糊查詢是會(huì)使索引失效。
我們可以這樣理解，「查詢字符串也是遵循最左前綴原則的」，字符串的查詢是對(duì)字符串里面的字符一個(gè)一個(gè)的匹配，「若是字符串最左邊為%表示一個(gè)不確定的字符串，那么是沒辦法利用到索引的有序性」。
但是若是修改為 ：WHERE name like 'd%'；就可以使用索引，因?yàn)樽钭筮叺淖址谴_定的，這種稱為「匹配列前綴」。
實(shí)際業(yè)務(wù)場(chǎng)景中聯(lián)合索引的創(chuàng)建，「我們應(yīng)該把識(shí)別度比較高的字段放在前面，提高索引的命中率，充分的利用索引」。
索引下推
Mysql5.6版本提出了索引下推的原則，「用于查詢優(yōu)化，主要是用于like關(guān)鍵字的查詢的優(yōu)化」，什么是索引下推呢？
下面通過演示來說明一下他的概念，還是利用原來的employee測(cè)試表，假如我要執(zhí)行下面的sql進(jìn)行查詢：SELECT * from user where name like '張%' and age=40；
假如沒有索引下推，執(zhí)行的過程如下圖所示：
查詢會(huì)直接忽略age字段，將name查詢的張開頭的id=5、id=7的結(jié)果返回給Mysql服務(wù)器，再執(zhí)行兩次的回表查詢。
若是上面的查詢操作使用了索引下推，執(zhí)行的過程如下：
Mysql會(huì)將查詢條件age=40的查詢條件傳遞給存儲(chǔ)引擎，再次過濾掉age=50的數(shù)據(jù)行，這樣回表的次數(shù)就變?yōu)榱艘淮危岣吡瞬樵冃省?/p>
總結(jié)起來索引下推就是在執(zhí)行sql查詢的時(shí)候，會(huì)將一部分的索引列的判斷條件傳遞給存儲(chǔ)引擎，由存儲(chǔ)引擎通過判斷是否符合條件，只有符合條件的數(shù)據(jù)才會(huì)返回給Mysql服務(wù)器。
全文索引
全文索引也稱為全文檢索，可以通過以下sql建立全文索引：ALTER TABLE employee ADD FULLTEXT fulltext_name(name);或者CREATE INDEX的方式創(chuàng)建。
全文索引主要是針對(duì)CHAR、VARCHAR或TEXT這種文本類的字段有效，有人說不也可以使用like關(guān)鍵字來查詢文本嗎。
普通索引（單列索引）的查詢只能加快字段內(nèi)容中最前面的字符串的檢索，若是對(duì)于多個(gè)單詞組成文本的查詢普通索引就無能為力了。
索引一經(jīng)創(chuàng)建就沒有辦法修改，若是想要修改索引，必須重建，可以使用以下sql來刪除索引：DROP INDEX fulltext_name ON employee;
聚簇索引和非聚簇索引
聚簇索引和非聚簇索引是相對(duì)于存儲(chǔ)引擎的概念，范圍比較大，包含上面所提到的索引類型。
「聚簇索引就是葉子節(jié)點(diǎn)中存儲(chǔ)的就是完整的行數(shù)據(jù)，索引和數(shù)據(jù)存儲(chǔ)在一起；而非聚簇索引的索引文件和數(shù)據(jù)文件是分開的，所以查詢數(shù)據(jù)會(huì)多一次查詢」。
因此聚簇索引的查詢速度會(huì)快于非聚簇索引的查詢速度，在Mysql的存儲(chǔ)引擎中，「InnoDB支持聚簇索引，MyISAM不支持聚簇索引，MyISAM支持非聚簇索引」。
聚簇索引
下面我們來看看InnoDB中的聚簇索引，前面說到InnoDB都會(huì)有一個(gè)主鍵，該主鍵就是用于支持聚簇索引，聚簇索引結(jié)構(gòu)圖，大致如下圖所示：
InnoDB中適用于最好的主鍵選擇就是給出一個(gè)AUTO_INCREMENT的列作為自增的主鍵，有的人可能會(huì)使用UUID作為隨機(jī)主鍵。
因?yàn)樗饕S持有序性，若是使用隨機(jī)的主鍵，主鍵的插入需要尋找合適的位置進(jìn)行放置，這樣維護(hù)主鍵索引樹的成本就會(huì)變得更高。
相反的，自增主鍵，主鍵都是自增變大，在維護(hù)主鍵索引樹的成本就會(huì)變得更小，隨意應(yīng)該盡量避免隨機(jī)主鍵。
非聚簇索引
MyISAM使用的是非聚簇索引，新插入數(shù)據(jù)的時(shí)候，會(huì)按順序的寫入的磁盤中，并且給每一行數(shù)據(jù)標(biāo)記一個(gè)行號(hào)，從小逐漸增大。
當(dāng)MyISAM創(chuàng)建主鍵索引的時(shí)候，形成的主鍵索引樹的結(jié)構(gòu)圖如下圖所示：
在主鍵索引中，數(shù)據(jù)也是非空且唯一，主鍵索引樹中存儲(chǔ)的是數(shù)據(jù)行的行號(hào)，當(dāng)查詢數(shù)據(jù)的時(shí)候使用主鍵索引查詢需要查詢到行號(hào)，然后通過行號(hào)獲取數(shù)據(jù)。
非主鍵索引和主鍵索引一樣葉子節(jié)點(diǎn)也是存儲(chǔ)著行號(hào)，唯一的區(qū)別就是非主鍵索引不要求非空、唯一。
我們可以通對(duì)比圖來對(duì)比一下「InnoDB(聚簇索引)」 和 「MyISAM(非聚簇索引)」 的索引數(shù)據(jù)布局，如下圖所示：
說到這里相信應(yīng)該大家對(duì)于「InnoDB(聚簇索引)」 和 「MyISAM(非聚簇索引)」 有了非常清晰的認(rèn)識(shí)和理解，下面是來說一說索引的優(yōu)化，這個(gè)也是和我們?nèi)粘ｉ_發(fā)最密切相關(guān)的。
索引原則和優(yōu)化
要正確的使用索引，就要正確的創(chuàng)建索引，用索引正確的查詢，不要使索引失效，因此索引的設(shè)計(jì)和優(yōu)化的原則應(yīng)該遵循下面的幾個(gè)原則：
-
索引列不要在表達(dá)式中出現(xiàn)，這樣會(huì)導(dǎo)致索引失效。如： 「SELECT ...... WHERE id+1=5」; -
索引列不要作為函數(shù)的參數(shù)使用。 -
索引列盡量不要使用like關(guān)鍵字。如： 「SELECT ...... WHERE name like '%d%'」; -
數(shù)字型的索引列不要當(dāng)作字符串類型進(jìn)行條件查詢。如： 「SELECT ...... WHERE id = '35'」; -
盡量不要在條件NOT IN、<>、!= 中使用索引。 -
在索引列的字段中不要出現(xiàn)NULL值，NULL值會(huì)使索引失效，可以用特殊的字符比如空字符串' '或者0來代替NULL值。 -
聯(lián)合索引的查詢應(yīng)該遵循最左前綴原則。 -
一般對(duì)于區(qū)別性比較大的字段建立索引，在聯(lián)合索引中區(qū)別性比較大（識(shí)別度比較高）放在最前面，提高索引的命中率。 -
索引的大小要適度，不易過大，避免索引的冗余。
總結(jié)
索引是我們工作經(jīng)常會(huì)使用到的數(shù)據(jù)查詢方式，正確的使用索引可以大大提高查詢的效率。
-
一方面索引減少了索引服務(wù)器需要掃描的數(shù)據(jù)行的數(shù)量，將原來的全表掃描，使用特定的數(shù)據(jù)結(jié)構(gòu)，能夠快速的定位數(shù)據(jù)行。 -
另一方面使用有序的索引，避免了排序，將原來的隨機(jī)的IO操作，變成了順序的IO操作，執(zhí)行有序。
但是索引也不是十全十美的，也有自己的缺點(diǎn)，不正確的使用索引，將會(huì)導(dǎo)致索引大量的占據(jù)空間，索引并非是越多越好，索引文件會(huì)越發(fā)的膨脹，這樣嚴(yán)重的影響查詢的性能。
對(duì)于插入、更新 、刪除數(shù)據(jù)，除了維護(hù)數(shù)據(jù)以外，還要維護(hù)索引文件，這樣也會(huì)影響這些操作的性能，但是對(duì)于查詢的頻率遠(yuǎn)高于更新和插入數(shù)據(jù)的業(yè)務(wù)場(chǎng)景，索引是再適合不過了。
特別推薦一個(gè)分享架構(gòu)+算法的優(yōu)質(zhì)內(nèi)容，還沒關(guān)注的小伙伴，可以長(zhǎng)按關(guān)注一下：
長(zhǎng)按訂閱更多精彩▼
如有收獲，點(diǎn)個(gè)在看，誠摯感謝
免責(zé)聲明：本文內(nèi)容由21ic獲得授權(quán)后發(fā)布，版權(quán)歸原作者所有，本平臺(tái)僅提供信息存儲(chǔ)服務(wù)。文章僅代表作者個(gè)人觀點(diǎn)，不代表本平臺(tái)立場(chǎng)，如有問題，請(qǐng)聯(lián)系我們，謝謝！
下載文檔
