《數(shù)據(jù)庫(kù)索引胸哥，終于懂了》介紹了為什么B+樹(shù)適合做數(shù)據(jù)庫(kù)索引涯竟，數(shù)據(jù)庫(kù)的索引分為主鍵索引（Primary Inkex）與普通索引（Secondary Index）。InnoDB和MyISAM是怎么利用B+樹(shù)來(lái)實(shí)現(xiàn)這兩類索引，其又有什么差異呢庐船？問(wèn)題1：MyISAM的索引結(jié)構(gòu)是怎樣的银酬？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)行記錄的指針喷屋；畫外音：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ù)，葉子也指向行記錄淹遵；****問(wèn)題2：InnoDB的索引結(jié)構(gòu)是怎樣的口猜？****InnoDB的主鍵索引與行記錄是存儲(chǔ)在一起的，故叫做聚集索引（Clustered Index）：（1）沒(méi)有單獨(dú)區(qū)域存儲(chǔ)行記錄透揣；（2）主鍵索引的葉子節(jié)點(diǎn)济炎，存儲(chǔ)主鍵，與對(duì)應(yīng)行記錄（而不是指針）辐真；畫外音：因此须尚，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ù)在物理磁盤上只能有一份聚集存儲(chǔ)溉苛。
此時(shí)茸炒，如何來(lái)設(shè)計(jì)數(shù)據(jù)表呢？最容易想到的設(shè)計(jì)方式是：
（1）身份證作為主鍵阵苇；
（2）其他屬性上建立索引壁公；
user(id_code PK,
id_md5(index),
name(index),
birthday(index));
此時(shí)的索引樹(shù)與行記錄結(jié)構(gòu)如上：
（1）id_code聚集索引器瘪，關(guān)聯(lián)行記錄池凄；
（2）其他索引打月，存儲(chǔ)id_code屬性值趁怔；
身份證號(hào)id_code是一個(gè)比較長(zhǎng)的字符串湿硝，每個(gè)索引都存儲(chǔ)這個(gè)值润努，在數(shù)據(jù)量大痢畜，內(nèi)存珍貴的情況下吼拥，MySQL有限的緩沖區(qū)，存儲(chǔ)的索引與數(shù)據(jù)會(huì)減少线衫，磁盤IO的概率會(huì)增加授账。
畫外音：同時(shí)枯跑，索引占用的磁盤空間也會(huì)增加。此時(shí)白热，應(yīng)該新增一個(gè)無(wú)業(yè)務(wù)含義的id自增列：
（1）以id自增列為聚集索引敛助，關(guān)聯(lián)行記錄；
（2）其他索引屋确，存儲(chǔ)id值纳击；
user(id PK auto inc,
id_code(index),
id_md5(index),
name(index),
birthday(index));
如此一來(lái)，有限的緩沖區(qū)攻臀，能夠緩沖更多的索引與行數(shù)據(jù)评疗，磁盤IO的頻率會(huì)降低，整體性能會(huì)增加茵烈。InnoDB為何不宜使用較長(zhǎng)的列作為主鍵，這下懂了吧砌些？問(wèn)題5：InnoDB的普通索引存儲(chǔ)主鍵鍵值呜投，可能存在什么問(wèn)題？使用普通索引查詢時(shí)存璃，可能出現(xiàn)回表查詢仑荐。什么是回表查詢？還是上面的例子：
t(id PK, name KEY, sex, flag);
畫外音：id是聚集索引纵东，name是普通索引粘招。
表中有四條記錄：
1, shenjian, m, A
3, zhangsan, m, A
5, lisi, m, A
9, wangwu, f, B
兩個(gè)B+樹(shù)索引分別如上圖：
（1）id為PK，聚集索引偎球，葉子節(jié)點(diǎn)存儲(chǔ)行記錄洒扎；
（2）name為KEY，普通索引衰絮，葉子節(jié)點(diǎn)存儲(chǔ)PK值袍冷，即id；
既然從普通索引無(wú)法直接定位行記錄猫牡，那普通索引的查詢過(guò)程是怎么樣的呢胡诗？
通常情況下，需要掃碼兩遍索引樹(shù)。
例如：
select id,name,sex from t where name='lisi';
是如何執(zhí)行的呢煌恢？
如粉紅色路徑骇陈，需要掃碼兩遍索引樹(shù)：
（1）先通過(guò)普通索引定位到主鍵值id=5；
（2）在通過(guò)聚集索引定位到行記錄瑰抵；
這就是所謂的回表查詢你雌，先定位主鍵值，再定位行記錄谍憔，它的性能較掃一遍索引樹(shù)更低匪蝙。
問(wèn)題6：如何優(yōu)化回表查詢？
常見(jiàn)的解決方案是覆蓋索引习贫。
什么是索引覆蓋****(Covering index)****逛球？
額，樓主并沒(méi)有在MySQL的官網(wǎng)找到這個(gè)概念苫昌。
畫外音：治學(xué)嚴(yán)謹(jǐn)吧颤绕？
借用一下SQL-Server官網(wǎng)的說(shuō)法。
MySQL官網(wǎng)祟身，類似的說(shuō)法出現(xiàn)在explain查詢計(jì)劃優(yōu)化章節(jié)奥务，即explain的輸出結(jié)果Extra字段為Using index時(shí)，能夠觸發(fā)索引覆蓋袜硫。
不管是SQL-Server官網(wǎng)氯葬，還是MySQL官網(wǎng)，都表達(dá)了：只需要在一棵索引樹(shù)上就能獲取SQL所需的所有列數(shù)據(jù)婉陷，無(wú)需回表帚称，速度更快。
如何實(shí)現(xiàn)索引覆蓋秽澳？
常見(jiàn)的方法是：將被查詢的字段闯睹，建立到聯(lián)合索引里去。
對(duì)于查詢需求
select id,name,sex from t where name='lisi';
將單列索引(name)升級(jí)為聯(lián)合索引(name, sex)担神，即可避免回表楼吃。 畫外音：屬性sex不用到聚集索引查詢了。總結(jié)MyISAM和InnoDB都使用B+樹(shù)來(lái)實(shí)現(xiàn)索引：（1）MyISAM的索引與數(shù)據(jù)分開(kāi)存儲(chǔ)妄讯；（2）MyISAM的索引葉子節(jié)點(diǎn)存儲(chǔ)指針孩锡，主鍵索引與普通索引無(wú)太大區(qū)別；（3）InnoDB的聚集索引和行數(shù)據(jù)統(tǒng)一存儲(chǔ)捞挥；（4）InnoDB的聚集索引存儲(chǔ)數(shù)據(jù)行本身浮创，普通索引存儲(chǔ)主鍵；（5）InnoDB不宜使用較長(zhǎng)的列作為PK砌函；（6）InnoDB普通索引可能存在回表查詢斩披，常見(jiàn)的解決方案是覆蓋索引溜族；
