從數(shù)據(jù)結(jié)構(gòu)角度
1忍抽、B+樹(shù)索引
2、hash索引
3董朝、FULLTEXT索引（InnoDB引擎5.7以后支持）
4鸠项、R-Tree索引（用于對(duì)GIS數(shù)據(jù)類型創(chuàng)建SPATIAL索引）
問(wèn)題：這些索引的區(qū)別跟用途在哪？B+樹(shù)相比hash的優(yōu)點(diǎn)在哪子姜？
從物理存儲(chǔ)角度
1祟绊、聚簇索引（clustered index）
2楼入、非聚簇索引（non-clustered index）
問(wèn)題：實(shí)現(xiàn)方式有什么差異？
從邏輯角度
- 普通索引index :加速查找
- 唯一索引
主鍵索引：primary key ：加速查找+約束（不為空且唯一）
唯一索引：unique：加速查找+約束 （唯一） - 聯(lián)合索引
-primary key(id,name):聯(lián)合主鍵索引
-unique(id,name):聯(lián)合唯一索引
-index(id,name):聯(lián)合普通索引 - 全文索引fulltext :用于搜索很長(zhǎng)一篇文章的時(shí)候牧抽，效果最好嘉熊。
- 空間索引spatial :了解就好，幾乎不用
問(wèn)題：多列索引有什么命中規(guī)則扬舒？這幾種索引對(duì)加鎖有什么影響阐肤？
在《數(shù)據(jù)庫(kù)原理》一書中是這么解釋聚簇索引和非聚簇索引的區(qū)別的：聚簇索引的葉子節(jié)點(diǎn)就是數(shù)據(jù)節(jié)點(diǎn)，而非聚簇索引的葉子節(jié)點(diǎn)仍然是索引節(jié)點(diǎn)讲坎，只不過(guò)有指向?qū)?yīng)數(shù)據(jù)塊的指針孕惜。
聚簇索引
聚簇索引(innoDB的主鍵索引)：表數(shù)據(jù)按照索引的順序來(lái)存儲(chǔ)的，也就是說(shuō)索引項(xiàng)的順序與表中記錄的物理順序一致晨炕。對(duì)于聚簇索引衫画，葉子結(jié)點(diǎn)即存儲(chǔ)了真實(shí)的數(shù)據(jù)行，不再有另外單獨(dú)的數(shù)據(jù)頁(yè)瓮栗。 在一張表上最多只能創(chuàng)建一個(gè)聚簇索引（不包括二級(jí)索引削罩，二級(jí)索引可以有多個(gè)），因?yàn)檎鎸?shí)數(shù)據(jù)的物理順序只能有一種费奸。
"聚簇"指實(shí)際的數(shù)據(jù)行和相關(guān)的鍵值都保存在一起弥激。
聚簇索引的二級(jí)索引(innoDB的其他索引)：葉子節(jié)點(diǎn)不會(huì)保存引用的行的物理位置,而是保存了行的主鍵值
注意：數(shù)據(jù)的物理存放順序與索引順序是一致的，即：只要索引是相鄰的愿阐，那么對(duì)應(yīng)的數(shù)據(jù)一定也是相鄰地存放在磁盤上的秆撮，如果主鍵不是自增id，那么可以想象换况，它會(huì)干些什么职辨，不斷地調(diào)整數(shù)據(jù)的物理地址、分頁(yè)戈二，當(dāng)然也有其他一些措施來(lái)減少這些操作舒裤，但卻無(wú)法徹底避免。但觉吭，如果是自增的腾供，那就簡(jiǎn)單了，它只需要一頁(yè)一頁(yè)地寫鲜滩，索引結(jié)構(gòu)相對(duì)緊湊伴鳖，磁盤碎片少，效率也高徙硅。
非聚簇索引
非聚簇索引（MyISAM的所有索引）：表數(shù)據(jù)存儲(chǔ)順序與索引順序無(wú)關(guān)榜聂。對(duì)于非聚簇索引，葉結(jié)點(diǎn)包含索引字段值及指向數(shù)據(jù)頁(yè)數(shù)據(jù)行的邏輯指針嗓蘑，其行數(shù)量與數(shù)據(jù)表行數(shù)據(jù)量一致须肆。
MyISAM的B+Tree的葉子節(jié)點(diǎn)上的data匿乃，并不是數(shù)據(jù)本身，而是數(shù)據(jù)存放的地址豌汇。主索引和輔助索引沒(méi)啥區(qū)別幢炸，只是主索引中的key一定得是唯一的
因此，MYSQL中不同的數(shù)據(jù)存儲(chǔ)引擎對(duì)聚簇索引的支持不同就很好解釋了拒贱。下面宛徊，我們可以看一下MYSQL中MYISAM和INNODB兩種引擎的索引結(jié)構(gòu)。
如原始數(shù)據(jù)為：
| id | col1 | col2 |
|---|---|---|
| 0 | 99 | 8 |
| 1 | 12 | 56 |
| 2 | 3000 | 62 |
| ... | ... | ... |
| 9997 | 18 | 8 |
| 9998 | 4700 | 13 |
| 9999 | 3 | 93 |
MyISAM引擎的數(shù)據(jù)存儲(chǔ)方式如圖：
MYISAM是按列值與行號(hào)來(lái)組織索引的逻澳。它的葉子節(jié)點(diǎn)中保存的實(shí)際上是指向存放數(shù)據(jù)的物理塊的指針岩调。從MYISAM存儲(chǔ)的物理文件我們能看出，MYISAM引擎的索引文件（.MYI）和數(shù)據(jù)文件(.MYD)是相互獨(dú)立的赡盘。
而InnoDB按聚簇索引的形式存儲(chǔ)數(shù)據(jù)，所以它的數(shù)據(jù)布局有著很大的不同缰揪。它存儲(chǔ)數(shù)據(jù)的結(jié)構(gòu)大致如下：
注：聚簇索引中的每個(gè)葉子節(jié)點(diǎn)包含主鍵值陨享、事務(wù)ID、回滾指針(rollback pointer用于事務(wù)和MVCC）和余下的列(如col2)钝腺。
INNODB的二級(jí)索引與主鍵索引有很大的不同抛姑。InnoDB的二級(jí)索引的葉子包含主鍵值，而不是行指針(row pointers)艳狐，這減小了移動(dòng)數(shù)據(jù)或者數(shù)據(jù)頁(yè)面分裂時(shí)維護(hù)二級(jí)索引的開(kāi)銷定硝，因?yàn)镮nnoDB不需要更新索引的行指針。其結(jié)構(gòu)大致如下：
INNODB和MYISAM的主鍵索引與二級(jí)索引的對(duì)比：
InnoDB的的二級(jí)索引的葉子節(jié)點(diǎn)存放的是KEY字段加主鍵值毫目。因此蔬啡，通過(guò)二級(jí)索引查詢首先查到是主鍵值，然后InnoDB再根據(jù)查到的主鍵值通過(guò)主鍵索引找到相應(yīng)的數(shù)據(jù)塊镀虐。而MyISAM的二級(jí)索引葉子節(jié)點(diǎn)存放的還是列值與行號(hào)的組合箱蟆，葉子節(jié)點(diǎn)中保存的是數(shù)據(jù)的物理地址。所以可以看出MYISAM的主鍵索引和二級(jí)索引沒(méi)有任何區(qū)別刮便，主鍵索引僅僅只是一個(gè)叫做PRIMARY的唯一空猜、非空的索引，且MYISAM引擎中可以不設(shè)主鍵恨旱。
為了更形象說(shuō)明這兩種索引的區(qū)別辈毯，我們假想一個(gè)表如下圖存儲(chǔ)了4行數(shù)據(jù)。其中Id作為主索引搜贤，Name作為輔助索引谆沃。圖示清晰的顯示了聚簇索引和非聚簇索引的差異。
對(duì)于聚簇索引存儲(chǔ)來(lái)說(shuō)仪芒，行數(shù)據(jù)和主鍵B+樹(shù)存儲(chǔ)在一起管毙，輔助鍵B+樹(shù)只存儲(chǔ)輔助鍵和主鍵腿椎，主鍵和非主鍵B+樹(shù)幾乎是兩種類型的樹(shù)。對(duì)于非聚簇索引存儲(chǔ)來(lái)說(shuō)夭咬，主鍵B+樹(shù)在葉子節(jié)點(diǎn)存儲(chǔ)指向真正數(shù)據(jù)行的指針啃炸，而非主鍵。
InnoDB使用的是聚簇索引卓舵，將主鍵組織到一棵B+樹(shù)中南用，而行數(shù)據(jù)就儲(chǔ)存在葉子節(jié)點(diǎn)上，若使用"where id = 14"這樣的條件查找主鍵掏湾，則按照B+樹(shù)的檢索算法即可查找到對(duì)應(yīng)的葉節(jié)點(diǎn)裹虫，之后獲得行數(shù)據(jù)。若對(duì)Name列進(jìn)行條件搜索融击，則需要兩個(gè)步驟：第一步在輔助索引B+樹(shù)中檢索Name筑公，到達(dá)其葉子節(jié)點(diǎn)獲取對(duì)應(yīng)的主鍵。第二步使用主鍵在主索引B+樹(shù)種再執(zhí)行一次B+樹(shù)檢索操作尊浪，最終到達(dá)葉子節(jié)點(diǎn)即可獲取整行數(shù)據(jù)匣屡。
MyISM使用的是非聚簇索引，非聚簇索引的兩棵B+樹(shù)看上去沒(méi)什么不同拇涤，節(jié)點(diǎn)的結(jié)構(gòu)完全一致只是存儲(chǔ)的內(nèi)容不同而已捣作，主鍵索引B+樹(shù)的節(jié)點(diǎn)存儲(chǔ)了主鍵，輔助鍵索引B+樹(shù)存儲(chǔ)了輔助鍵鹅士。表數(shù)據(jù)存儲(chǔ)在獨(dú)立的地方券躁，這兩顆B+樹(shù)的葉子節(jié)點(diǎn)都使用一個(gè)地址指向真正的表數(shù)據(jù)，對(duì)于表數(shù)據(jù)來(lái)說(shuō)掉盅，這兩個(gè)鍵沒(méi)有任何差別也拜。由于索引樹(shù)是獨(dú)立的，通過(guò)輔助鍵檢索無(wú)需訪問(wèn)主鍵的索引樹(shù)趾痘。
為了更形象說(shuō)明這兩種索引的區(qū)別搪泳，我們假想一個(gè)表如下圖存儲(chǔ)了4行數(shù)據(jù)。其中Id作為主索引扼脐，Name作為輔助索引岸军。圖示清晰的顯示了聚簇索引和非聚簇索引的差異。
我們重點(diǎn)關(guān)注聚簇索引瓦侮，看上去聚簇索引的效率明顯要低于非聚簇索引艰赞，因?yàn)槊看问褂幂o助索引檢索都要經(jīng)過(guò)兩次B+樹(shù)查找，這不是多此一舉嗎肚吏？聚簇索引的優(yōu)勢(shì)在哪方妖？
1 由于行數(shù)據(jù)和葉子節(jié)點(diǎn)存儲(chǔ)在一起，這樣主鍵和行數(shù)據(jù)是一起被載入內(nèi)存的罚攀，找到葉子節(jié)點(diǎn)就可以立刻將行數(shù)據(jù)返回了党觅，如果按照主鍵Id來(lái)組織數(shù)據(jù)雌澄，獲得數(shù)據(jù)更快。
2 輔助索引使用主鍵作為"指針" 而不是使用地址值作為指針的好處是杯瞻，減少了當(dāng)出現(xiàn)行移動(dòng)或者數(shù)據(jù)頁(yè)分裂時(shí)輔助索引的維護(hù)工作镐牺，使用主鍵值當(dāng)作指針會(huì)讓輔助索引占用更多的空間，換來(lái)的好處是InnoDB在移動(dòng)行時(shí)無(wú)須更新輔助索引中的這個(gè)"指針"魁莉。也就是說(shuō)行的位置（實(shí)現(xiàn)中通過(guò)16K的Page來(lái)定位睬涧，后面會(huì)涉及）會(huì)隨著數(shù)據(jù)庫(kù)里數(shù)據(jù)的修改而發(fā)生變化（前面的B+樹(shù)節(jié)點(diǎn)分裂以及Page的分裂），使用聚簇索引就可以保證不管這個(gè)主鍵B+樹(shù)的節(jié)點(diǎn)如何變化旗唁，輔助索引樹(shù)都不受影響畦浓。
