?前言

索引是什么 ： MySQL 官方對(duì)索引的定義：索引（Index）是幫助 MySQL 高效獲取數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)?？梢缘玫剿饕谋举|(zhì)：索引是數(shù)據(jù)結(jié)構(gòu)。索引的目的在于提高查詢效率。可以簡(jiǎn)單理解為，排好序的快速查找數(shù)據(jù)結(jié)構(gòu)。在數(shù)據(jù)之外，數(shù)據(jù)系統(tǒng)還維護(hù)著滿足特定查找算法的數(shù)據(jù)結(jié)構(gòu)，這些結(jié)構(gòu)已某種特定方式引用（指向）數(shù)據(jù)，這樣就可以在這些數(shù)據(jù)結(jié)構(gòu)上實(shí)現(xiàn)高級(jí)查找算法。這種數(shù)據(jù)結(jié)構(gòu)，就是索引。如下：

為了加快 Col2 的快速查找，可以維護(hù)一個(gè)右邊所示的二叉查找樹(shù)，每個(gè)節(jié)點(diǎn)分別包含索引的鍵值對(duì)和一個(gè)指向?qū)?yīng)數(shù)據(jù)記錄的物理地址的指針，這樣就可以運(yùn)用二叉樹(shù)查找在一定復(fù)雜度內(nèi)獲取相應(yīng)的數(shù)據(jù)，從而快速檢索出符合條件的記錄。

• 一般來(lái)說(shuō)索引本身也很大，不可能全部存儲(chǔ)在內(nèi)存中，因此索引往往以文件的形式存儲(chǔ)在磁盤上。
• 我們 Java 程序員平常所說(shuō)的索引：如果沒(méi)有特別指明，都指的是B樹(shù)（多路搜索樹(shù)，并不一定是二叉樹(shù)）結(jié)構(gòu)組織索引，其中聚集索引、次要索引、覆蓋索引、復(fù)合索引、前綴索引、唯一索引默認(rèn)都是B+樹(shù)索引，統(tǒng)稱索引。初B樹(shù)外，還有哈希（hash index）索引等。

優(yōu)勢(shì)：

• 類似大學(xué)圖書(shū)館建書(shū)目錄，提高數(shù)據(jù)檢索的效率，降低數(shù)據(jù)庫(kù)的 IO 成本。
• 通過(guò)索引列對(duì)數(shù)據(jù)進(jìn)行排序，降低數(shù)據(jù)排序的成本，降低了 CPU 的消耗。

劣勢(shì)：

• 實(shí)際上索引也是一張表，該表保存了主鍵與索引字段，并指向?qū)嶓w記錄，索引索引列也是要占用空間的。
• 雖然索引大大提高了查詢速度，同時(shí)又降低了更新表（Insert、Update、Delete）的速度。因?yàn)楦卤淼臅r(shí)候還要保存一下索引文件每次更新添加了索引列的字段或者更新所帶來(lái)的鍵值變化后的索引信息。
• 索引只是提高效率的一個(gè)因素，如果 MySQL 有大量的數(shù)據(jù)表，就需要花時(shí)間研究建立最優(yōu)秀的索引，或優(yōu)化查詢。（索引的建立并非一時(shí)）。

索引有很多種類型，為不同的場(chǎng)景提供更好的性能。在MySQL中，索引是在存儲(chǔ)引擎層而不是服務(wù)器層實(shí)現(xiàn)。不同存儲(chǔ)引擎的索引其工作方式并不一樣。也不是所有存儲(chǔ)引擎都支持所有類型的索引。即使多個(gè)存儲(chǔ)引擎支持同一種類型的索引，其底層實(shí)現(xiàn)也可能不同。

一、B-Tree索引

我們通過(guò)提到索引時(shí)，多半說(shuō)的都是 B-Tree 索引，使用 B-Tree 數(shù)據(jù)結(jié)構(gòu)來(lái)存儲(chǔ)數(shù)據(jù)。大多數(shù) MySQL 引擎都支持這種索引。之所以稱之為“B-Tree” 是因?yàn)?MySQL 在創(chuàng)建表和其他語(yǔ)句中也使用該關(guān)鍵字。不過(guò)，底層的存儲(chǔ)引擎也可能使用不同的存儲(chǔ)結(jié)構(gòu)，例如：InnoDB 則使用 B+Tree。存儲(chǔ)引擎以不同的方式使用 B-Tree 索引，性能也各有不同，各有優(yōu)劣。例如，MyISAM 使用前綴壓縮技術(shù)使得索引更小，但 InnoDB 則按照原數(shù)據(jù)格式進(jìn)行存儲(chǔ)。再如 MyISAM 索引通過(guò)數(shù)據(jù)的物理位置引用被索引的行，而 InnoDB 則根據(jù)主鍵引用被索引的行。

B-Tree（多路搜索樹(shù)）：通常意味著所有的值都是按順序存儲(chǔ)的，并且每一個(gè)葉子頁(yè)到根的距離相同。如下圖：展示了 B-Tree 索引的抽象表示，大致反映了 InnoDB 索引是如何工作的。InnoDB 的葉子節(jié)點(diǎn)稱為葉子頁(yè)，大小為 16K。

B-Tree 索引能夠加快訪問(wèn)數(shù)據(jù)的速度，因?yàn)榇鎯?chǔ)引擎不再需要進(jìn)行全表掃描來(lái)獲取需要的數(shù)據(jù)，取而代之的是從索引的根節(jié)點(diǎn)開(kāi)始進(jìn)行搜索。根節(jié)點(diǎn)的槽中存放了指向子節(jié)點(diǎn)的指針，存儲(chǔ)引擎根據(jù)這些指針指向下層查找。通過(guò)比較節(jié)點(diǎn)頁(yè)的值和要查找的值可以找到合適的指針進(jìn)入下層子節(jié)點(diǎn)，這些指針實(shí)際上定義了子節(jié)點(diǎn)頁(yè)中值的上限和下限。最終存儲(chǔ)引擎要么是找到對(duì)應(yīng)的值，要么該記錄不存在。

葉子節(jié)點(diǎn)比較特別，它們的指針指向的是被索引的數(shù)據(jù)，而不是其他的節(jié)點(diǎn)頁(yè)（不同引擎的“指針”類型不同）。如下圖，繪制了一個(gè)節(jié)點(diǎn)和其對(duì)應(yīng)的葉子節(jié)點(diǎn)，其實(shí)在跟節(jié)點(diǎn)和葉子節(jié)點(diǎn)之間可能有很多節(jié)點(diǎn)頁(yè)，樹(shù)的深度和表的大小直接相關(guān)。B-Tree 對(duì)索引列是順序組織存儲(chǔ)的，所以很適合查找范圍數(shù)據(jù)。例如下圖，基于文本域的索引樹(shù)上，按字母順序傳遞連續(xù)的值進(jìn)行查找是非常合適的，所以像“找出所有以A到C開(kāi)頭的名字”這樣的查詢效率會(huì)非常高。假設(shè)數(shù)據(jù)表信息如下：

CREATE TABLE People(
    last_name VARCHAR(50) NOT NULL,
    first_name VARCHAR(50) NOT NULL,
    birthday DATE NOT NULL,
    gender ENUM('m','f') NOT NULL,
    KEY(last_name,first_name,birthday)
);

對(duì)于表中的每一行數(shù)據(jù)，索引中包含 last_name,first_name 和 birthday列的值，如下圖表示索引是如何組織數(shù)據(jù)的存儲(chǔ)的。索引對(duì)多個(gè)值進(jìn)行排序的依據(jù)是 CREATE TABLE 語(yǔ)句中定義索引時(shí)列的順序，看一下最后兩個(gè)條目，兩個(gè)人的姓和名都相同時(shí)，則根據(jù)他們的出生日期來(lái)排列順序。

可以使用 B-Tree 索引的查詢類型。B-Tree 索引使用于全鍵值、范圍鍵值或鍵前綴查找（值where條件）。其中鍵前綴查找只適用于根據(jù)最左前綴的查找。前面所述的索引對(duì)如下類型的查詢有效：

• 全值匹配： 和索引中的所有列進(jìn)行匹配，例如前面提到的索引可用于查找姓名為 Cuba Allen、出生于 1960-01-01 的人。
• 匹配最左前綴： 前面提到的索引可用于查找所有姓為 Allen 的人，即只使用索引的第一列。
• 匹配列前綴： 也可以只匹配某一列的值的開(kāi)頭部分。例如前面提到的索引可用于查找所有以 A 開(kāi)頭姓的人。這里也只使用了索引的第一列。模糊查詢以常量開(kāi)頭，那么可以使用上索引。
• 匹配范圍值： 例如前面提到的索引可用于查找姓在 Allen 和 Barrymore 之間的人。這里也只使用了索引的第一列。
• 精準(zhǔn)匹配某一列并范圍匹配另外一列： 前面提到的索引也可用于查找姓為 Allen，并且名字是字母 K 開(kāi)頭的人。即第一列 last_name 全匹配，第二列 first_name 范圍匹配。
• 只訪問(wèn)索引的查詢： B_Tree 通?？梢灾С?“只訪問(wèn)索引的查詢”，即查詢只需要訪問(wèn)索引，而無(wú)需訪問(wèn)數(shù)據(jù)行。稱之為“覆蓋索引” 的優(yōu)化。

所以，索引列的順序是很重要的，上面的限制都和索引列的順序有關(guān)。在優(yōu)化性能的時(shí)候，可能需要使用相同的列但順序不同的索引來(lái)滿足不同類型的查詢需求。也有些限制并不是 B-Tree 本身導(dǎo)致的，而是 MySQL 優(yōu)化器和存儲(chǔ)引擎使用索引的方式導(dǎo)致的。這部分限制在未來(lái)的版本中可能就不再是限制了。

二、哈希索引

哈希索引（hash index）是基于哈希表實(shí)現(xiàn)的，只有精確匹配索引所有列的查詢才有效。對(duì)于每一行數(shù)據(jù)，存儲(chǔ)引擎都會(huì)對(duì)所有的索引列計(jì)算一個(gè)哈希碼（hash code），哈希碼是一個(gè)較小的值，并且不同鍵值的行計(jì)算出來(lái)的哈希碼也是不一樣。哈希索引將所有的哈希碼存儲(chǔ)在索引中，同時(shí)在哈希表中保存指向每個(gè)數(shù)據(jù)行的指針。

MySQL中：只有 Memory 引擎顯示支持哈希索引。這也是 Memory 引擎表的默認(rèn)索引類型，Memory 引擎同時(shí)也支持 B-Tree 索引。值得一提的是，Memory 引擎是支持非唯一哈希索引的，這在數(shù)據(jù)庫(kù)世界里面是比較與眾不同的。如果多個(gè)列的哈希值相同，索引會(huì)以鏈表的方式存放多個(gè)記錄指針到同一個(gè)哈希條目中。

CREATE TABLE People(
    last_name VARCHAR(50) NOT NULL,
    first_name VARCHAR(50) NOT NULL,
    KEY USING HASH(last_name)
)ENGINE=MEMORY;

表中包含的數(shù)據(jù)如下：

f('Allen')= 1223
f('Peter')= 8493
f('Baron')= 3490

則哈希索引的數(shù)據(jù)結(jié)構(gòu)如下：索引（hash值，指針），每個(gè)槽的編號(hào)是順序的，但是數(shù)據(jù)行不是。

舉個(gè)栗子：進(jìn)行如下查詢：

SELECT last_name FROM People WHERE last_name='Peter';

MySQL 先計(jì)算 ‘Peter’ 的哈希值，并使用該值尋找對(duì)應(yīng)的記錄指針。因?yàn)?f(‘Peter’)=8493，所以對(duì) MySQL 在索引中查找 8493，可以找到指向第二行的指針，最后一步是比較第二行的值是否為’Peter’，以確保就是要查找的行。因?yàn)樗饕陨碇恍璐鎯?chǔ)對(duì)應(yīng)的哈希值，所以索引的結(jié)構(gòu)十分緊湊，這也讓哈希索引查找的速度非?？臁Ｈ欢?，哈希索引也有它的限制：

• 哈希索引只包含哈希值和指針，而不存儲(chǔ)字段值，所以不能使用索引中的值來(lái)避免讀取行。不過(guò)，訪問(wèn)內(nèi)存中的行的速度很快，所以大部分情況下這一點(diǎn)對(duì)性能的影響并不明顯。
• 哈希索引數(shù)據(jù)并不是按照索引值順序存儲(chǔ)的，所以也就無(wú)法用于排序。
• 哈希索引也不支持部分索引列匹配查找，因?yàn)楣Ｋ饕冀K是使用索引列的全部?jī)?nèi)容來(lái)計(jì)算哈希值的。例如，在數(shù)據(jù)列（A,B）上建立索引，如果查詢只使用A，則無(wú)法使用該索引。是不遵循最左前綴的思想。
• 哈希索引只支持等值查詢，也不支持任何范圍查詢。
• 訪問(wèn)哈希索引的數(shù)據(jù)非?？?，除非有很多哈希沖突。當(dāng)出現(xiàn)哈希沖突的時(shí)候，存儲(chǔ)引擎必須遍歷鏈表中所有的行指針，逐行進(jìn)行比較，直到找到所有符合條件的行。
• 如果哈希沖突很多的話，一些索引維護(hù)操作的代價(jià)也會(huì)很高。

因?yàn)樯鲜鱿拗?，哈希索引只適用于某些特定的場(chǎng)合。而一旦適合哈希索引，則它的性能會(huì)將非常顯著。除了 Memory 引擎外，NDB 集群引擎也支持唯一哈希索引。InnoDB 引擎有一個(gè)特殊的功能叫做“自適應(yīng)哈希索引（adaptive hash index）” 當(dāng)InnoDB 注意到某些索引值被使用得非常頻繁時(shí)，它會(huì)在內(nèi)存中基于 B-Tree 索引之上再創(chuàng)建一個(gè)哈希索引，這樣就使 B-Tree 索引也具有哈希索引的一些優(yōu)點(diǎn)，比如快速的哈希查找。這是一個(gè)完全自動(dòng)化的、內(nèi)部的行為，用戶無(wú)法控制或者配置，不過(guò)該功能可以關(guān)閉。

創(chuàng)建自定義哈希索引：如果存儲(chǔ)引擎不支持哈希索引，則可以模擬像 InnoDB 一樣創(chuàng)建哈希索引。思路很簡(jiǎn)單：在 B-Tree 基礎(chǔ)上創(chuàng)建一個(gè)偽哈希索引，這和真正的哈希索引不是一回事，因?yàn)檫€是使用 B-Tree 進(jìn)行查找，但是使用 Hash值進(jìn)行查找而非鍵值本身。只需要在 WHERE 子句中手動(dòng)指定使用哈希函數(shù)。

舉個(gè)栗子：例如表中存儲(chǔ)了大量的 URL，并需要根據(jù)URL 進(jìn)行搜索查詢。如果使用 B-Tree 來(lái)存儲(chǔ) URL，存儲(chǔ)的內(nèi)容就會(huì)很大，因?yàn)?URL本身就很長(zhǎng)。若在原有的表中，新增一個(gè)被索引的 url_crc列（使用CRC32 對(duì) URL 進(jìn)行哈希）。使用 CRC32 做哈希就可以使用如下方式查詢：性能會(huì)提升很多，因?yàn)?MySQL 優(yōu)化器會(huì)使用選擇性高而體積小的 url_crc 列的索引來(lái)查詢。

SELECT id FROM url WHERE url="http://www.mysql.com" AND url_crc=CRC32("http://www.mysql.com");

上述的缺點(diǎn)是需要維護(hù)哈希值?？梢允謩?dòng)維護(hù)，也可以使用觸發(fā)器實(shí)現(xiàn)。如下：先臨時(shí)修改一下語(yǔ)句分隔符，這樣就可以在觸發(fā)器定義中使用分號(hào)；

DELIMITER //
CREATE OR REPLACE TRIGGER People_insert_trigger BEFORE INSERT ON  People FOR EACH ROW BEGIN
SET NEW.url_cc=CRC32(NEW.url);
END;
//
DELIMITER ;

記住不要使用 SHA1() 和 MD5() 作為哈希函數(shù)。因?yàn)檫@兩個(gè)函數(shù)計(jì)算出來(lái)的哈希值是非常長(zhǎng)的字符串，會(huì)浪費(fèi)大量空間，比較時(shí)也會(huì)更慢。SHA1() 和 MD5() 是強(qiáng)加密函數(shù)，設(shè)計(jì)目的是最大限制消費(fèi)沖突，但這里并不需要這么高的要求，簡(jiǎn)單哈希函數(shù)的沖突在一個(gè)可以接受的范圍，同時(shí)又能夠提供更好的性能。如果數(shù)據(jù)表非常大，CRC32() 會(huì)出現(xiàn)大量的哈希沖突，則可以考慮自己實(shí)現(xiàn)一個(gè)簡(jiǎn)單的 64位哈希函數(shù)。這個(gè)自定義函數(shù)要返回整數(shù)，而不是字符串。一個(gè)簡(jiǎn)單的辦法可以使用 MD5() 函數(shù)返回值的一部分作為自定義哈希函數(shù)。這可能比自己寫一個(gè)哈希算法的性能要差。

處理哈希沖突：當(dāng)使用哈希索引進(jìn)行查詢的時(shí)候，必須在 WHERE 子句中包含常量值。CRC32() 返回的是32位的整數(shù)，當(dāng)索引有93,000 條記錄時(shí)出現(xiàn)沖突的概率是 1%。所以，避免哈希沖突的辦法就是在 WHERE 條件中帶入列值?；蛘呤褂?FNV64()函數(shù)，這是移植 Percona Server 的函數(shù)，可以以插件的方式在任何 MySQL版本中使用，哈希值為 64位，速度快，且沖突比CRC32() 要少很多。

SELECT id FROM url WHERE url="http://www.mysql.com" AND url_crc=CRC32("http://www.mysql.com");

三、空間數(shù)據(jù)索引（R-Tree）

MyISAM 表支持空間索引，可以用作地理數(shù)據(jù)存儲(chǔ)。和B-Tree 索引不同，這類索引無(wú)需前綴查詢?？臻g索引會(huì)從所有維度來(lái)索引數(shù)據(jù)。查詢時(shí)，可以有效地使用任意維度來(lái)組合查詢。必須使用 MySQL 的 GIS 相關(guān)函數(shù)如 MBRCONTAINS() 等來(lái)維護(hù)數(shù)據(jù)。MySQL 的 GIS 支持并不完善，所以大部分人都不會(huì)使用這個(gè)特性。開(kāi)源關(guān)系數(shù)據(jù)庫(kù)系統(tǒng)中對(duì) GIS 的解決方案做得比較好的是 PostgreSQL 的 PostGIS。

四、全文索引

全文索引是一種特殊類型的索引，他查找的是文本中的關(guān)鍵詞，而不是直接比較索引中的值。全文搜索和其他幾類索引的匹配方式完全不一樣。他有許多需要注意的細(xì)節(jié)，如停用詞、詞干和復(fù)詞、布爾搜索等。全文索引更類似 solr這種搜索引擎，而不是簡(jiǎn)單的 WHERE 條件匹配。同時(shí)在列上創(chuàng)建全文索引和基于值的 B-Tree 索引不會(huì)有沖突，全文索引適用于 MATCH AGAINST 操作，而不是普通的 WHERE 條件操作。

CREATE TABLE articles (
     id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
     title VARCHAR(200),
     body TEXT,
     FULLTEXT (title,body)
) ENGINE=InnoDB;

【全文索引的使用】： 通過(guò)在 title和body 兩個(gè)字段中查找含有 ‘database’ 內(nèi)容的行。

SELECT * FROM articles
WHERE MATCH (title,body)
AGAINST ('database' IN NATURAL LANGUAGE MODE);

五、其他索引類型

還有第三方的存儲(chǔ)引擎使用不同類型的數(shù)據(jù)結(jié)構(gòu)來(lái)存儲(chǔ)索引。例如 TokuDB 使用分型樹(shù)索引（fractal tree index），這是一類較新開(kāi)發(fā)的數(shù)據(jù)結(jié)構(gòu)，既有 B-Tree 的很多優(yōu)點(diǎn)，也避免了 B-Tree 的一些缺點(diǎn)。

六、優(yōu)化

問(wèn)題：性能下降， SQL 慢，執(zhí)行時(shí)間長(zhǎng)，等待時(shí)間長(zhǎng)

• 表結(jié)構(gòu)設(shè)計(jì)不當(dāng)。
• 查詢語(yǔ)句寫的爛。
• 索引失效（單值索引、復(fù)合索引）
• 關(guān)聯(lián)查詢太多 join（設(shè)計(jì)缺陷或者不得以的需求）
• 服務(wù)器調(diào)優(yōu)或者各個(gè)參數(shù)的設(shè)置（緩沖、線程數(shù)等）

哪些情況下需要?jiǎng)?chuàng)建索引：

• 主鍵會(huì)自動(dòng)創(chuàng)建唯一索引。
• 頻繁作為查詢條件的字段。
• 查詢中與其他表關(guān)聯(lián)的字段，外鍵關(guān)系建立索引。
• 頻繁修改的字段不建議創(chuàng)建索引。
• where 條件用不到的字段不要?jiǎng)?chuàng)建索引。
• 單例索引與復(fù)合建議選擇復(fù)合索引。
• 查詢的字段若通過(guò)索引的順序去訪問(wèn)將大大提高排序速度。
• 查詢中統(tǒng)計(jì)和分組字段。

什么情況下不建議創(chuàng)建索引：

• 表記錄太少
• 經(jīng)常增刪改的表
• 數(shù)據(jù)重復(fù)且分布平均的字段。

索引分析：

• 單表：有范圍時(shí)，后邊的索引失效。
• 雙表：左連接為右表建索引。
• 三表：參考上一條。

結(jié)論：

• 永遠(yuǎn)用小的結(jié)果集驅(qū)動(dòng)大的結(jié)果集。
• 優(yōu)先優(yōu)化NestedLoop內(nèi)層循環(huán)。
• 保證 Join 語(yǔ)句中被驅(qū)動(dòng)表上 Join 條件字段已被索引。
• 當(dāng)無(wú)法保證上述 join 字段時(shí)，當(dāng)內(nèi)存允許的條件下，不要太吝嗇 joinBuffer 字段的設(shè)置。

索引失效（應(yīng)該避免）：

• 全值匹配我最愛(ài)
• 最佳左前綴法則
• 不在索引列上做任何操作（計(jì)算、函數(shù)、類型轉(zhuǎn)換），會(huì)導(dǎo)致索引失效而轉(zhuǎn)向全表掃描。
• 存儲(chǔ)引擎不能使用索引中范圍條件右邊的列。
• 盡量使用覆蓋索引（只訪問(wèn)索引的查詢），減少select *。
• MySQL在使用不等于（！=或<>）的時(shí)候無(wú)法使用索引會(huì)導(dǎo)致全表掃描。
• is null,is not null 也無(wú)法使用索引。
• like 已通配符開(kāi)頭，MySQL 索引會(huì)失效會(huì)變成全表掃描。（%右邊的是會(huì)進(jìn)行rang索引的同是不同于>它會(huì)后面的索引不會(huì)失效。同時(shí)當(dāng)使用左%時(shí)，想使用索引直接從索引緩存中查詢即覆蓋索引）
• 字符串不添加單引號(hào)索引會(huì)失效。
• 少用 or，用它來(lái)連接時(shí)會(huì)索引失效。

總結(jié)： 定值、范圍還是排序，一般 order by 是給個(gè)范圍。group by 基本上都是需要排序，會(huì)有臨時(shí)表產(chǎn)生（如果錯(cuò)亂時(shí)）

一般性建議： 在 where 查詢條件中條件不安索引的順序排列查找和順序排列查找的效果相同，原因是：MySQL 自身會(huì)對(duì) sql 進(jìn)行優(yōu)化。（都是常量的提前）

• 對(duì)于單值索引，盡量選擇針對(duì)當(dāng)前 query 過(guò)濾性更好的索引。
• 在選擇組合索引的時(shí)候，當(dāng)前 query 中過(guò)濾性最好的字段在索引字段順序中，位置越靠左越好。
• 在選擇組合索引的時(shí)候，盡量選擇可以包含當(dāng)前 query 中的 where 子句中更多字段的索引。
• 盡可能通過(guò)分析統(tǒng)計(jì)信息和調(diào)整 query 的寫法來(lái)達(dá)到選擇合適索引的目的。

以上就是MySQL關(guān)于索引的分類與優(yōu)化詳解的詳細(xì)內(nèi)容，更多關(guān)于MySQL索引分類與優(yōu)化的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！

最新評(píng)論