InnoDB索引，終于懂了
《數(shù)據(jù)庫(kù)索引，終于懂了》介紹了為什么B+樹適合做數(shù)據(jù)庫(kù)索引，數(shù)據(jù)庫(kù)的索引分為主鍵索引(Primary Inkex)與普通索引(Secondary Index)。InnoDB和MyISAM是怎么利用B+樹來(lái)實(shí)現(xiàn)這兩類索引，其又有什么差異呢?
問(wèn)題1：MyISAM的索引結(jié)構(gòu)是怎樣的?
MyISAM的索引與行記錄是分開存儲(chǔ)的，叫做非聚集索引(UnClustered Index)。
其主鍵索引與普通索引沒(méi)有本質(zhì)差異：
- 有連續(xù)聚集的區(qū)域單獨(dú)存儲(chǔ)行記錄;
- 主鍵索引的葉子節(jié)點(diǎn)，存儲(chǔ)主鍵，與對(duì)應(yīng)行記錄的指針;
- 普通索引的葉子結(jié)點(diǎn)，存儲(chǔ)索引列，與對(duì)應(yīng)行記錄的指針;
畫外音：MyISAM的表可以沒(méi)有主鍵。
主鍵索引與普通索引是兩棵獨(dú)立的索引B+樹，通過(guò)索引列查找時(shí)，先定位到B+樹的葉子節(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+樹索引構(gòu)造如上圖：
- 行記錄單獨(dú)存儲(chǔ);
- id為PK，有一棵id的索引樹，葉子指向行記錄;
- name為KEY，有一棵name的索引樹，葉子也指向行記錄;
問(wèn)題2：InnoDB的索引結(jié)構(gòu)是怎樣的?
InnoDB的主鍵索引與行記錄是存儲(chǔ)在一起的，故叫做聚集索引(Clustered Index)：
- 沒(méi)有單獨(dú)區(qū)域存儲(chǔ)行記錄;
- 主鍵索引的葉子節(jié)點(diǎn)，存儲(chǔ)主鍵，與對(duì)應(yīng)行記錄(而不是指針);
畫外音：因此，InnoDB的PK查詢是非?？斓?。
因?yàn)檫@個(gè)特性，InnoDB的表必須要有聚集索引：
- 如果表定義了PK，則PK就是聚集索引;
- 如果表沒(méi)有定義PK，則第一個(gè)非空unique列是聚集索引;
- 否則，InnoDB會(huì)創(chuàng)建一個(gè)隱藏的row-id作為聚集索引;
聚集索引，也只能夠有一個(gè)，因?yàn)樾袛?shù)據(jù)在物理磁盤上只能有一份聚集存儲(chǔ)。
InnoDB的普通索引可以有多個(gè)，它與聚集索引是不同的：普通索引的葉子節(jié)點(diǎn)，存儲(chǔ)主鍵(也不是指針);
問(wèn)題3：InnoDB為何建議使用趨勢(shì)遞增主鍵?
InnoDB由于數(shù)據(jù)行與索引一體，如果使用趨勢(shì)遞增主鍵，插入記錄時(shí)，不會(huì)索引分裂，不會(huì)大量行記錄移動(dòng)。
問(wèn)題4：InnoDB為何不宜使用較長(zhǎng)的列做主鍵?
假設(shè)有一個(gè)用戶中心場(chǎng)景，包含身份證號(hào)，身份證MD5，姓名，出生年月等業(yè)務(wù)屬性，這些屬性上均有查詢需求，并且有事務(wù)需求，必須使用InnoDB存儲(chǔ)引擎。
此時(shí)，如何來(lái)設(shè)計(jì)數(shù)據(jù)表呢?
最容易想到的設(shè)計(jì)方式是：
- 身份證作為主鍵;
- 其他屬性上建立索引;
- user(id_code PK,
- id_md5(index),
- name(index),
- birthday(index));
此時(shí)的索引樹與行記錄結(jié)構(gòu)如上：
- id_code聚集索引，關(guān)聯(lián)行記錄;
- 其他索引，存儲(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自增列：
- 以id自增列為聚集索引，關(guān)聯(lián)行記錄;
- 其他索引，存儲(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+樹索引分別如上圖：
- id為PK，聚集索引，葉子節(jié)點(diǎn)存儲(chǔ)行記錄;
- name為KEY，普通索引，葉子節(jié)點(diǎn)存儲(chǔ)PK值，即id;
既然從普通索引無(wú)法直接定位行記錄，那普通索引的查詢過(guò)程是怎么樣的呢?
通常情況下，需要掃碼兩遍索引樹。
例如：
- select id,name,sex from t where name='lisi';
是如何執(zhí)行的呢?
如粉紅色路徑，需要掃碼兩遍索引樹：
- 先通過(guò)普通索引定位到主鍵值id=5;
- 在通過(guò)聚集索引定位到行記錄;
這就是所謂的回表查詢，先定位主鍵值，再定位行記錄，它的性能較掃一遍索引樹更低。
問(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á)了：只需要在一棵索引樹上就能獲取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+樹來(lái)實(shí)現(xiàn)索引：
- MyISAM的索引與數(shù)據(jù)分開存儲(chǔ);
- MyISAM的索引葉子節(jié)點(diǎn)存儲(chǔ)指針，主鍵索引與普通索引無(wú)太大區(qū)別;
- InnoDB的聚集索引和行數(shù)據(jù)統(tǒng)一存儲(chǔ);
- InnoDB的聚集索引存儲(chǔ)數(shù)據(jù)行本身，普通索引存儲(chǔ)主鍵;
- InnoDB不宜使用較長(zhǎng)的列作為PK;
- InnoDB普通索引可能存在回表查詢，常見(jiàn)的解決方案是覆蓋索引;
【本文為51CTO專欄作者“58沈劍”原創(chuàng)稿件，轉(zhuǎn)載請(qǐng)聯(lián)系原作者】
