MyISAM與InnoDB的索引，究竟有什么差異？

數(shù)據(jù)庫(kù)的索引分為主鍵索引（Primary Inkex）與普通索引（Secondary Index）。InnoDB和MyISAM是怎么利用B+樹(shù)來(lái)實(shí)現(xiàn)這兩類(lèi)索引，其又有什么差異呢？這是今天要聊的內(nèi)容。

 

一，MyISAM的索引

MyISAM的索引與行記錄是分開(kāi)存儲(chǔ)的，叫做非聚集索引（UnClustered Index）。

其主鍵索引與普通索引沒(méi)有本質(zhì)差異：

（1）有連續(xù)聚集的區(qū)域單獨(dú)存儲(chǔ)行記錄；

（2）主鍵索引的葉子節(jié)點(diǎn)，存儲(chǔ)主鍵，與對(duì)應(yīng)行記錄的指針；

（3）普通索引的葉子結(jié)點(diǎn)，存儲(chǔ)索引列，與對(duì)應(yīng)行記錄的指針；

畫(huà)外音：MyISAM的表可以沒(méi)有主鍵。

 

主鍵索引與普通索引是兩棵獨(dú)立的索引B+樹(shù)，通過(guò)索引列查找時(shí)，先定位到B+樹(shù)的葉子節(jié)點(diǎn)，再通過(guò)指針定位到行記錄。

 

舉個(gè)例子，MyISAM：

t(id PK, name KEY, sex, flag);

 

表中有四條記錄：

1, shenjian, m, A

3, zhangsan, m, A

5, lisi, m, A

9, wangwu, f, B

其B+樹(shù)索引構(gòu)造如上圖：

（1）行記錄單獨(dú)存儲(chǔ)；

（2）id為PK，有一棵id的索引樹(shù)，葉子指向行記錄；

（3）name為KEY，有一棵name的索引樹(shù)，葉子也指向行記錄；

 

二、InnoDB的索引

InnoDB的主鍵索引與行記錄是存儲(chǔ)在一起的，故叫做聚集索引（Clustered Index）：

（1）沒(méi)有單獨(dú)區(qū)域存儲(chǔ)行記錄；

（2）主鍵索引的葉子節(jié)點(diǎn)，存儲(chǔ)主鍵，與對(duì)應(yīng)行記錄（而不是指針）；

畫(huà)外音：因此，InnoDB的PK查詢是非常快的。

 

因?yàn)檫@個(gè)特性，InnoDB的表必須要有聚集索引：

（1）如果表定義了PK，則PK就是聚集索引；

（2）如果表沒(méi)有定義PK，則第一個(gè)非空unique列是聚集索引；

（3）否則，InnoDB會(huì)創(chuàng)建一個(gè)隱藏的row-id作為聚集索引；

 

聚集索引，也只能夠有一個(gè)，因?yàn)閿?shù)據(jù)行在物理磁盤(pán)上只能有一份聚集存儲(chǔ)。

 

InnoDB的普通索引可以有多個(gè)，它與聚集索引是不同的：

（1）普通索引的葉子節(jié)點(diǎn)，存儲(chǔ)主鍵（也不是指針）；

 

對(duì)于InnoDB表，這里的啟示是：

（1）不建議使用較長(zhǎng)的列做主鍵，例如char(64)，因?yàn)樗械钠胀ㄋ饕紩?huì)存儲(chǔ)主鍵，會(huì)導(dǎo)致普通索引過(guò)于龐大；

（2）建議使用趨勢(shì)遞增的key做主鍵，由于數(shù)據(jù)行與索引一體，這樣不至于插入記錄時(shí)，有大量索引分裂，行記錄移動(dòng)；

 

仍是上面的例子，只是存儲(chǔ)引擎換成InnoDB：

t(id PK, name KEY, sex, flag);

 

表中還是四條記錄：

1, shenjian, m, A

3, zhangsan, m, A

5, lisi, m, A

9, wangwu, f, B

 

其B+樹(shù)索引構(gòu)造如上圖：

（1）id為PK，行記錄和id索引樹(shù)存儲(chǔ)在一起；

（2）name為KEY，有一棵name的索引樹(shù)，葉子存儲(chǔ)id；

 

當(dāng)：

select * from t where name=‘lisi’;

會(huì)先通過(guò)name輔助索引定位到B+樹(shù)的葉子節(jié)點(diǎn)得到id=5，再通過(guò)聚集索引定位到行記錄。

畫(huà)外音：所以，其實(shí)掃了2遍索引樹(shù)。

 

三，總結(jié)

MyISAM和InnoDB都使用B+樹(shù)來(lái)實(shí)現(xiàn)索引：

（1）MyISAM的索引與數(shù)據(jù)分開(kāi)存儲(chǔ)；

（2）MyISAM的索引葉子存儲(chǔ)指針，主鍵索引與普通索引無(wú)太大區(qū)別；

（3）InnoDB的聚集索引和數(shù)據(jù)行統(tǒng)一存儲(chǔ)；

（4）InnoDB的聚集索引存儲(chǔ)數(shù)據(jù)行本身，普通索引存儲(chǔ)主鍵；

（5）InnoDB一定有且只有一個(gè)聚集索引；

（6）InnoDB建議使用趨勢(shì)遞增整數(shù)作為PK，而不宜使用較長(zhǎng)的列作為PK；

四，如何系統(tǒng)學(xué)習(xí)數(shù)據(jù)庫(kù)知識(shí)體系？

騰訊技TDSQL術(shù)專(zhuān)家秦瑋聯(lián)手前58集團(tuán)高級(jí)架構(gòu)師陳東，打磨了一套《數(shù)據(jù)庫(kù)實(shí)戰(zhàn)32講》在線專(zhuān)欄課，幫你快速掌握數(shù)據(jù)庫(kù)核心架構(gòu)技術(shù)。

課題：《數(shù)據(jù)庫(kù)實(shí)戰(zhàn)32講》

講師：秦瑋、陳東

時(shí)間：8.30-9.1 三天，20:00開(kāi)始

費(fèi)用：1.99（原價(jià)499，粉絲福利價(jià) 1.99）

名額：200名，先到先得

如何參加？

掃碼報(bào)名，耐心等待顧問(wèn)老師通過(guò)

希望對(duì)大家的數(shù)據(jù)庫(kù)學(xué)習(xí)有幫助。

閱讀原文，了解更多。