《數(shù)據(jù)庫索引熏矿,到底是什么做的?》介紹了B+樹孝凌,它是一種非常適合用來做數(shù)據(jù)庫索引的數(shù)據(jù)結(jié)構(gòu):
(1)很適合磁盤存儲(chǔ)方咆,能夠充分利用局部性原理,磁盤預(yù)讀蟀架;
(2)很低的樹高度瓣赂,能夠存儲(chǔ)大量數(shù)據(jù);
(3)索引本身占用的內(nèi)存很衅摹煌集;
(4)能夠很好的支持單點(diǎn)查詢,范圍查詢捌省,有序性查詢苫纤;
數(shù)據(jù)庫的索引分為主鍵索引(Primary Inkex)與普通索引(Secondary Index)。InnoDB和MyISAM是怎么利用B+樹來實(shí)現(xiàn)這兩類索引纲缓,其又有什么差異呢卷拘?這是今天要聊的內(nèi)容。
一色徘,MyISAM的索引
MyISAM的索引與行記錄是分開存儲(chǔ)的恭金,叫做非聚集索引(UnClustered Index)操禀。
其主鍵索引與普通索引沒有本質(zhì)差異:
1.有連續(xù)聚集的區(qū)域單獨(dú)存儲(chǔ)行記錄
2.主鍵索引的葉子節(jié)點(diǎn)褂策,存儲(chǔ)主鍵,與對(duì)應(yīng)行記錄的指針
3.普通索引的葉子結(jié)點(diǎn),存儲(chǔ)索引列斤寂,與對(duì)應(yīng)行記錄的指針
畫外音:MyISAM的表可以沒有主鍵耿焊。
主鍵索引與普通索引是兩棵獨(dú)立的索引B+樹,通過索引列查找時(shí)遍搞,先定位到B+樹的葉子節(jié)點(diǎn)罗侯,再通過指針定位到行記錄。
舉個(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+樹索引構(gòu)造如上圖:
1.行記錄單獨(dú)存儲(chǔ)
2.id為PK钩杰,有一棵id的索引樹,葉子指向行記錄
3.name為KEY诊县,有一棵name的索引樹讲弄,葉子也指向行記錄
二、InnoDB的索引
InnoDB的主鍵索引與行記錄是存儲(chǔ)在一起的依痊,故叫做聚集索引(Clustered Index):
1.沒有單獨(dú)區(qū)域存儲(chǔ)行記錄
2.主鍵索引的葉子節(jié)點(diǎn)避除,存儲(chǔ)主鍵,與對(duì)應(yīng)行記錄(而不是指針)
畫外音:因此胸嘁,InnoDB的PK查詢是非称堪冢快的。
因?yàn)檫@個(gè)特性性宏,InnoDB的表必須要有聚集索引:
(1)如果表定義了PK群井,則PK就是聚集索引;
(2)如果表沒有定義PK毫胜,則第一個(gè)非空unique列是聚集索引蝌借;
(3)否則,InnoDB會(huì)創(chuàng)建一個(gè)隱藏的row-id作為聚集索引指蚁;
聚集索引菩佑,也只能夠有一個(gè),因?yàn)閿?shù)據(jù)行在物理磁盤上只能有一份聚集存儲(chǔ)凝化。
InnoDB的普通索引可以有多個(gè)稍坯,它與聚集索引是不同的:
1.普通索引的葉子節(jié)點(diǎn),存儲(chǔ)主鍵(也不是指針)
對(duì)于InnoDB表搓劫,這里的啟示是:
(1)不建議使用較長(zhǎng)的列做主鍵瞧哟,例如char(64),因?yàn)樗械钠胀ㄋ饕紩?huì)存儲(chǔ)主鍵枪向,會(huì)導(dǎo)致普通索引過于龐大勤揩;
(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+樹索引構(gòu)造如上圖:
1.id為PK权纤,行記錄和id索引樹存儲(chǔ)在一起
2.name為KEY构资,有一棵name的索引樹乾闰,葉子存儲(chǔ)id
當(dāng):select * from t where name=‘lisi’;
會(huì)先通過name輔助索引定位到B+樹的葉子節(jié)點(diǎn)得到id=5速客,再通過聚集索引定位到行記錄。
畫外音:所以欲账,其實(shí)掃了2遍索引樹屡江。
三,總結(jié)
1 MyISAM和InnoDB都使用B+樹來實(shí)現(xiàn)索引:
2 MyISAM的索引與數(shù)據(jù)分開存儲(chǔ)
3 MyISAM的索引葉子存儲(chǔ)指針赛不,主鍵索引與普通索引無太大區(qū)別
4 InnoDB的聚集索引和數(shù)據(jù)行統(tǒng)一存儲(chǔ)
5 InnoDB的聚集索引存儲(chǔ)數(shù)據(jù)行本身盼理,普通索引存儲(chǔ)主鍵
6 InnoDB一定有且只有一個(gè)聚集索引
7 InnoDB建議使用趨勢(shì)遞增整數(shù)作為PK,而不宜使用較長(zhǎng)的列作為PK