關(guān)于這個(gè)專題

想寫MySQL的索引專題是源于之前自己在學(xué)習(xí)MySQL索引時(shí)痛苦的經(jīng)歷，你在網(wǎng)上搜索關(guān)于MySQL的索引的文章，大多是支離破碎，沒有系統(tǒng)性的對(duì)知識(shí)點(diǎn)的羅列堆砌，文章中會(huì)說明你要如何如何做，但是很少涉及去講為什么要這么做，哪些不能做，很難對(duì)MySQL有一個(gè)系統(tǒng)性的認(rèn)知，學(xué)習(xí)如果沒有系統(tǒng)性的話，就很難在實(shí)際的項(xiàng)目中靈活運(yùn)用，出于此目的，自己就打算寫一個(gè)關(guān)于MySQL索引的專題系列，算是自己一個(gè)學(xué)習(xí)的總結(jié)，如果同時(shí)能幫到你那再好不過了。下面進(jìn)入正題，我們先來了解一下什么是索引以及索引的類型。

認(rèn)識(shí)索引

認(rèn)識(shí)索引是什么東西非常關(guān)鍵，一個(gè)非常恰當(dāng)?shù)谋扔骶褪菚哪夸涰撆c書的正文內(nèi)容之間的關(guān)系，為了方便查找書中的內(nèi)容，通過對(duì)內(nèi)容建立索引形成目錄。因此，首先你要明白的一點(diǎn)就是，索引它也是一個(gè)文件，它是要占據(jù)物理空間的。

比如對(duì)于MyISAM存儲(chǔ)引擎來說:

.frm后綴的文件存儲(chǔ)的是表結(jié)構(gòu)。

.myd后綴的文件存儲(chǔ)的是表數(shù)據(jù)。

.myi后綴的文件存儲(chǔ)的就是索引文件。

如下圖所示:

對(duì)于InnoDB 存儲(chǔ)引擎來說:

.frm后綴的文件存儲(chǔ)的是表結(jié)構(gòu)。

.ibd后綴的文件存放索引文件和數(shù)據(jù)(需要開啟innodb_file_per_table 參數(shù))

如下圖所示:

因此，當(dāng)你對(duì)一張表建立索引時(shí)，索引文件的大小也會(huì)改變，當(dāng)你數(shù)據(jù)表中的數(shù)據(jù)因?yàn)樵鰟h改變化時(shí)，索引文件也會(huì)變化的，只不過MySQL會(huì)自動(dòng)維護(hù)索引，這個(gè)過程不需要你介入，這也是為什么不恰當(dāng)?shù)乃饕龝?huì)影響MySQL性能的原因。

總結(jié):

1. 索引是按照特定的數(shù)據(jù)結(jié)構(gòu)把數(shù)據(jù)表中的數(shù)據(jù)放在索引文件中，以便于快速查找;

2. 索引存在于磁盤中，會(huì)占據(jù)物理空間。

索引的類型

上面說到，索引文件時(shí)按照不同的數(shù)據(jù)結(jié)構(gòu)來存儲(chǔ)的，數(shù)據(jù)結(jié)構(gòu)的不同也產(chǎn)生了不同的索引類型，常見的索引類型包括:

1. B-Tree索引
2. 哈希索引
3. 空間數(shù)據(jù)索引(R-Tree)
4. 全文索引

下面做一一介紹:

1. B-Tree索引

B-Tree索引是最常用的一種索引，如果沒有指定特定的類型，那么多半就是B-Tree索引，事實(shí)上，很多搜索引擎使用的是它的變種B+Tree，這是對(duì)B-Tree的一個(gè)優(yōu)化，如果需要詳細(xì)了解，可以參考數(shù)據(jù)結(jié)構(gòu)方面的書籍，這里不做詳細(xì)探討。以下統(tǒng)稱為B-Tree索引。

絕大多數(shù)的存儲(chǔ)引擎，比如MyISAM和InnoDB都支持這種索引，因此說它是應(yīng)用最廣泛，最常用的一種索引方式，但是不同的存儲(chǔ)引擎在具體實(shí)現(xiàn)時(shí)會(huì)稍有不同，比如MyISAM會(huì)使用前綴壓縮的方式對(duì)索引進(jìn)行壓縮，InnoDB則不會(huì)。

下圖展示了B-Tree索引是如何存儲(chǔ)被索引的數(shù)據(jù)的: 

 

說明:

左圖是一個(gè)包含三列的數(shù)據(jù)表，右圖則展示了數(shù)據(jù)是如何被索引的。

可以看出B-Tree是對(duì)索引列是按照順序存儲(chǔ)的，每個(gè)葉子節(jié)點(diǎn)指向被索引的數(shù)據(jù)，這也是B-Tree索引支持范圍查找數(shù)據(jù)的原因。

2. 哈希索引

相比于B-Tree索引，哈希索引的實(shí)現(xiàn)就比較簡單了，它是基于哈希表來實(shí)現(xiàn)的，對(duì)于要索引的列，存儲(chǔ)引擎會(huì)計(jì)算出一一對(duì)應(yīng)的哈希碼，然后把哈希碼存放在哈希表中作為key，value值是指向該行數(shù)據(jù)的指針。

下圖是簡單的原理展示:

說明:

1. 左邊紫色圖表示一個(gè)二列的數(shù)據(jù)表。
2. 中間表示對(duì)fname列進(jìn)行哈希索引，計(jì)算出哈希值。
3. 右邊綠色圖表示把生成的哈希值存放于哈希表中。

當(dāng)我們執(zhí)行以下查詢時(shí):

select * from testTable where fname = "mary"; 

MySQL會(huì)首先計(jì)算查詢條件mary的哈希值，然后到哈希表中去找該哈希值，如果找到了根據(jù)對(duì)應(yīng)的指針也就找到了需要尋找的數(shù)據(jù)行。

哈希表的優(yōu)勢與限制:

優(yōu)勢:

1. 只需比對(duì)哈希值，因此速度非?？?，性能優(yōu)勢明顯;

限制:

1. 不支持任何范圍查詢，比如where price > 150，因?yàn)槭腔诠Ｓ?jì)算，支持等值比較。
2. 哈希表是無序存儲(chǔ)的，因此索引數(shù)據(jù)無法用于排序。
3. 主流存儲(chǔ)引擎不支持該類型，比如MyISAM和InnoDB。哈希索引只有Memory, NDB兩種引擎支持。

因此，哈希索引雖然速度快，但其實(shí)使用很受限，只適用于某些特殊的場合。

3. 空間數(shù)據(jù)索引(R-Tree)

空間索引可用于地理數(shù)據(jù)存儲(chǔ)，它需要GIS相關(guān)函數(shù)的支持，由于MySQL的GIS支持并不完善，所以該索引方式在MySQL中很少有人使用。

4. 全文索引

全文索引主要用于海量數(shù)據(jù)的搜索，比如淘寶或者京東對(duì)商品的搜索，你不可能使用like進(jìn)行模糊匹配吧，MySQL從5.6開始支持InnoDB引擎的全文索引，功能沒有專業(yè)的搜索引擎比如Sphinx或Solr豐富，如果你的需求比較簡單，可以嘗試一下MySQL的全文索引，否則建議使用專業(yè)的搜索引擎。

總結(jié):

1. B-Tree索引使用最廣泛，主流引擎都支持。

2. 哈希索引性能高，適用于特殊場合。

3. R-Tree不常用。

4. 全文索引適用于海量數(shù)據(jù)的關(guān)鍵字模糊搜索。

索引和存儲(chǔ)引擎之間的關(guān)系

上面講述了索引有不同的類型，存儲(chǔ)引擎也有不同的類型，那么索引和存儲(chǔ)引擎之間有什么關(guān)系呢?

首先你需要知道，在MySQL中，索引是在存儲(chǔ)引擎中實(shí)現(xiàn)的，并不是所有的存儲(chǔ)引擎都支持所有的索引類型，比如哈希索引，MyISAM和InnoDB是不支持的;同樣，即使對(duì)于同一類型的索引，不同的存儲(chǔ)引擎實(shí)現(xiàn)的方式也可能是不同的，比如MyISAM和InnoDB對(duì)B-Tree索引，具體的實(shí)現(xiàn)是有差別的。

總結(jié):

1. 不同的存儲(chǔ)引擎可能支持不同的索引類型;

2. 不同的存儲(chǔ)引擎對(duì)同一中索引類型可能有不同的實(shí)現(xiàn)方式。

B-Tree索引與唯一索引，主鍵索引，普通索引的關(guān)系

最開始對(duì)B-Tree索引與唯一索引，主鍵索引，普通索引這幾種索引的關(guān)系很模糊，網(wǎng)上也沒搜索到相關(guān)的資料，以為他們的關(guān)系是并列的，其實(shí)并不是，B-Tree只是底層的算法實(shí)現(xiàn)，唯一索引，主鍵索引，普通索引都是基于B-Tree索引算法的，只不過又有各自的特點(diǎn)。

通過下圖也可看出這種關(guān)系:

至于唯一索引，主鍵索引，普通索引之間的區(qū)別，下面補(bǔ)充一下:

• 主鍵索引:數(shù)據(jù)列不允許重復(fù)，不允許為NULL.一個(gè)表只能有一個(gè)主鍵。
• 唯一索引:數(shù)據(jù)列不允許重復(fù)，允許為NULL值，一個(gè)表允許多個(gè)列創(chuàng)建唯一索引。
• 普通索引:基本的索引類型，沒有唯一性的限制，允許為NULL值。

總結(jié):

這篇文章先說到這里，目的主要是對(duì)MySQL的索引有個(gè)概念上的認(rèn)識(shí)，以及了解索引的類型，索引和存儲(chǔ)引擎之間的關(guān)系，本專題會(huì)繼續(xù)更新，繼續(xù)對(duì)MySQL索引知識(shí)逐漸展開，如果你感興趣的話可以關(guān)注該專欄，以及順便動(dòng)動(dòng)手指關(guān)注一下我(^_^),希望本文對(duì)你有所幫助。