MySQL 索引及其分類

標籤：

概述

用過 mysql 的童鞋都知道建立索引的必要性，可是不少人對建立索引的目的僅僅停留於建立索引可以讓查詢變快

然而，為了達到這個目的，科學合理的建立索引也是非常有必要的

索引能夠輕易將查詢效能提高几個數量級，而一個“最優”索引有時比一個“好的”索引效能要高兩個數量級

 

在 MySQL 中，索引可以包含一個活多個列的值，因為 MySQL 只能高效地使用索引的最左首碼列，所以包含多個列的索引中列的順序也十分重要

而建立一個包含兩個列的索引和建立兩個分別包含一列的索引是大不相同的

 

索引的類型

MySQL 中，索引的類型有很多類型，能夠為不同情境提供更好的效能

索引是在儲存引擎層實現的，不同儲存引擎的索引工作方式不同，也並不是所有引擎都支援全部的索引類型，而對於同一類索引，不同引擎的底層實現也可能是不同的

 

B-Tree 索引

大多數 MySQL 儲存引擎都支援 B-Tree 索引，也因此，B-Tree 索引是最常用的索引類型，如果未加說明，索引一般都指的是 B-Tree 索引

然而，雖然在建立表時關鍵字都是 B-Tree，但是各個儲存引擎的底層實現可能是不同的，如 NDB 叢集儲存引擎內部實際上使用了 T-Tree 結構，而 innoDB 使用的 B+ Tree

 

磁碟 IO 與預讀

由於磁碟讀取靠的是機械運動，每次都要花費尋道時間、旋轉延遲、傳輸時間三部分時間才能讀取資料，總計時間是非常長的，如果針對資料庫的動輒十萬百萬乃至千萬級的資料查詢，每次幾毫秒的時間，結果將會是災難性的

因此作業系統對此進行了一些最佳化，每次讀取時並不僅僅讀取需要的資料，而是把相鄰資料全部讀取到記憶體緩衝區中，這樣，每次都讀取一頁資料（4KB 或 8KB），而針對一頁上資料的讀取，事實上僅進行了一次磁碟 IO 操作

 

B-Tree 的特性

B-Tree 的結構如：

 

由於 B 樹的多分支結構特性，導致樹的高度可以大幅下降，這樣，如果每個節點都儲存一頁資料，如果需要訪問第三層資料，則只需要進行三次磁碟 IO，這顯然大幅的節省了時間

B+ 樹與 B 樹的區別在於只有葉子節點儲存真實資料，其餘非葉子結點僅作為指引搜尋方向的資料項目

這樣儲存引擎不再需要全表掃描，而是根據每個節點的指引可以快速找到需要的資料

 

同時，由於 B 樹的結構特性，也導致所有的值通常都是按順序儲存的，因此在使用 ORDER BY 操作時，這個索引也可以滿足對應的排序需求

 

多列索引的匹配規則

 CREATE TABLE People (
    a    varchar(50)    not null,
    b    varchar(50)    not null,
    c    date        not null,
    d    date        not null,
    e    enum(‘m‘, ‘f‘)    not null,
    key(a, b, c, d)
);

 

對於上面這個表，建立了四列索引，他們遵循下列規則

• 最左首碼匹配原則

這是一個非常重要的原則，MySQL 會一直向右匹配直到遇到範圍查詢（>、<、between、like）

比如查詢 a="" and b="2" and c >= 3 and d = 4

在這個查詢中，d 是用不到索引的，而如果建立 (a, b, d, c) 則是可以的

同時 where 語句中查詢的順序是可以任意調整的，即 a、b、c、d 的順序可以任意調整，MySQL 總是按照索引建立的順序進行查詢

 

• 最大區分度原則

盡量選擇區分度高的列作為索引，或是將其放置在左端，區分度越高，即選出的結果行越少，則實際查詢的次數就會越少

 

• 索引列不能參與計算

對於 from_unixtime(a) = ‘2014-05-29‘ 這樣的查詢是不能應用索引的，而應該最佳化成 a = from_unixtime(‘2014-05-29‘)

比如 a+1>5 只有最佳化為 a > 4 才會應用索引

 

• 必須以最左列開始查詢

如果查詢 b = 5 and c < 2014 則不會應用索引，這也正是最左首碼匹配原則

 

• 不能跳過索引中的列

對於查詢 a=5 and c > 2015，由於跳過了 b 列，所以 c 不會應用索引

 

• 說明

上述限制存在於 MySQL 5.5 及以前的資料庫版本中，未來的版本可能會取消某些限制

然而，可以看到，建立表時怎樣選取索引的列，以及他們的排列順序是非常重要的

 

雜湊索引 簡介

 CREATE TABLE testhash (
    a varchar(50) not null,
    b varchar(50) not null,
    KEY USING HASH(a)
) ENGINE=MEMORY;

 

上面建立表的過程中建立了一個雜湊索引

 

顧名思義，雜湊索引的底層資料結構是用雜湊表實現的，只有精確匹配索引所有列的查詢才有效

索引會為每一行資料建立一個很小的雜湊碼，因此雜湊索引佔用空間小，執行效率高，但只支援等值查詢，而不支援範圍查詢

同時，由於雜湊表並不按照值的大小順序儲存，因此在 ORDER BY 操作中並不會應用該索引，也不支援僅使用索引中部分列進行尋找

但是，如果是某些特定適合使用雜湊索引的場合，索引所帶來的效能提升將非常顯著，如經典的“星型” schema，需要關聯很多尋找表，雜湊索引就非常適合尋找表的需求

 

雜湊索引與儲存引擎

雜湊索引是 MEMORY 儲存引擎的預設索引方式，MEMORY 引擎同時也支援 B-Tree 索引，目前，在 MySQL 中，只有 MEMORY 引擎顯式支援雜湊索引

 

InnoDB 引擎有一個特殊的功能 -- 自適應雜湊索引，對於被頻繁使用的索引值，InnoDB 引擎會自動在記憶體中建立一個雜湊索引，使用者只能通過配置選擇是否啟用這一特性，一旦啟用，該過程將是完全自動，使用者無法察覺的

InnoDB 建立的自適應雜湊索引和真正的雜湊索引並不是一回事，而是在原有的 B-Tree 索引的基礎上，將檢索的值變成雜湊碼，以降低磁碟使用

 

自訂雜湊索引

針對不支援雜湊索引的儲存引擎，使用者也可以採用類似 InnoDB 的思路去自訂雜湊索引

典型的如將 url 變成 CRC32，可以有效節省磁碟使用，並且提高查詢速度

 

如針對下面的查詢：

 SELECT id FROM url WHERE url = ‘http://www.techlog.cn/article/list/10182793‘;

這樣的查詢顯然是很耗時的，且如果為 url 建立索引，索引也將非常龐大

 

最佳化成以下這樣：

 SELECT id FROM url WHERE crc32_url = CRC32(‘http://www.techlog.cn/article/list/10182793‘);

這樣，我們為 crc32_url 欄位建立索引，索引的大小、查詢效率都會有顯著的提升

 

但是，這樣又需要維護一個新的欄位 crc32_url，通過建立觸發器，可以自動的添加該欄位：

 CREATE TABLE pseudohash (
    id    int unsigned NOT NULL auto_increment,
    url    varchar(255) NOT NULL,
    url_crc    int unsigned NOT NULL DEFAULT 0,
    PRIMARY KEY(id)
    KEY(url_crc);
);

DELIMITER //

CREATE TRIGGER pseudohash_crc_ins BEFORE INSERT ON pseudohash FOR EACH ROW BEGIN
SET NEW.url_crc = crc32(NEW.url);
END;
//

CREATE TRIGGER pseudohash_crc_upd BEFORE UPDATE ON pseudohash FOR EACH ROW BEGIN
SET NEW.url_crc = crc32(NEW.url);
END;
//

DELIMITER ;

這樣，每當添加或修改 url 欄位，觸發器會自動更新 url_crc 欄位

 

由於可能存在的雜湊衝突，所以直接查詢可能會出現多條記錄，可以最佳化為：

 SELECT id FROM url WHERE crc32_url = CRC32(‘http://www.techlog.cn/article/list/10182793‘) and url = ‘http://www.techlog.cn/article/list/10182793‘;

 

空間資料索引（R-Tree）

MyISAM 表支援空間索引，可以用作地理資料儲存

與 B-Tree 索引不同，空間資料索引無需首碼查詢，他會從所有維度索引資料，可以任意組合查詢

但是必須使用 MySQL 的 GIS 相關函數，如 MBRCONTAINS() 來維護資料，然而 MySQL 對 GIS 支援並不完善，所以大部分人不會使用這個特性

PostgreSQL 的 PostGIS 對 GIS 支援很好

 

全文索引

全文索引尋找的是文本中的關鍵詞，而不是比較索引中的值，類似於搜尋引擎

使用 MATCH AGAINST 操作進行索引，目前不支援中文

 

其他索引

還有很多第三方儲存引擎使用其他不同類型的資料結構來儲存索引，他們各自有不同的適用情境和優勢

MySQL 索引及其分類