?前言

索引是什么 ： MySQL 官方對索引的定義：索引（Index）是幫助 MySQL 高效獲取數(shù)據(jù)的數(shù)據(jù)結構?？梢缘玫剿饕谋举|(zhì)：索引是數(shù)據(jù)結構。索引的目的在于提高查詢效率?？梢院唵卫斫鉃椋藕眯虻目焖俨檎覕?shù)據(jù)結構。在數(shù)據(jù)之外，數(shù)據(jù)系統(tǒng)還維護著滿足特定查找算法的數(shù)據(jù)結構，這些結構已某種特定方式引用（指向）數(shù)據(jù)，這樣就可以在這些數(shù)據(jù)結構上實現(xiàn)高級查找算法。這種數(shù)據(jù)結構，就是索引。如下：

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

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

優(yōu)勢：

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

劣勢：

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

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

一、B-Tree索引

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

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

B-Tree 索引能夠加快訪問數(shù)據(jù)的速度，因為存儲引擎不再需要進行全表掃描來獲取需要的數(shù)據(jù)，取而代之的是從索引的根節(jié)點開始進行搜索。根節(jié)點的槽中存放了指向子節(jié)點的指針，存儲引擎根據(jù)這些指針指向下層查找。通過比較節(jié)點頁的值和要查找的值可以找到合適的指針進入下層子節(jié)點，這些指針實際上定義了子節(jié)點頁中值的上限和下限。最終存儲引擎要么是找到對應的值，要么該記錄不存在。

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

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

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

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

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

二、哈希索引

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

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

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ù)結構如下：索引（hash值，指針），每個槽的編號是順序的，但是數(shù)據(jù)行不是。

舉個栗子：進行如下查詢：

SELECT last_name FROM People WHERE last_name='Peter';

MySQL 先計算 ‘Peter’ 的哈希值，并使用該值尋找對應的記錄指針。因為 f(‘Peter’)=8493，所以對 MySQL 在索引中查找 8493，可以找到指向第二行的指針，最后一步是比較第二行的值是否為’Peter’，以確保就是要查找的行。因為索引自身只需存儲對應的哈希值，所以索引的結構十分緊湊，這也讓哈希索引查找的速度非?？臁Ｈ欢?，哈希索引也有它的限制：

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

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

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

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

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

上述的缺點是需要維護哈希值。可以手動維護，也可以使用觸發(fā)器實現(xiàn)。如下：先臨時修改一下語句分隔符，這樣就可以在觸發(fā)器定義中使用分號；

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

處理哈希沖突：當使用哈希索引進行查詢的時候，必須在 WHERE 子句中包含常量值。CRC32() 返回的是32位的整數(shù)，當索引有93,000 條記錄時出現(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ù)存儲。和B-Tree 索引不同，這類索引無需前綴查詢?？臻g索引會從所有維度來索引數(shù)據(jù)。查詢時，可以有效地使用任意維度來組合查詢。必須使用 MySQL 的 GIS 相關函數(shù)如 MBRCONTAINS() 等來維護數(shù)據(jù)。MySQL 的 GIS 支持并不完善，所以大部分人都不會使用這個特性。開源關系數(shù)據(jù)庫系統(tǒng)中對 GIS 的解決方案做得比較好的是 PostgreSQL 的 PostGIS。

四、全文索引

全文索引是一種特殊類型的索引，他查找的是文本中的關鍵詞，而不是直接比較索引中的值。全文搜索和其他幾類索引的匹配方式完全不一樣。他有許多需要注意的細節(jié)，如停用詞、詞干和復詞、布爾搜索等。全文索引更類似 solr這種搜索引擎，而不是簡單的 WHERE 條件匹配。同時在列上創(chuàng)建全文索引和基于值的 B-Tree 索引不會有沖突，全文索引適用于 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;

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

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

五、其他索引類型

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

六、優(yōu)化

問題：性能下降， SQL 慢，執(zhí)行時間長，等待時間長

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

哪些情況下需要創(chuàng)建索引：

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

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

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

索引分析：

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

結論：

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

索引失效（應該避免）：

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

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

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

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

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

最新評論