InnoDB存儲(chǔ)引擎支持事務(wù)，是一個(gè)通用的、平衡了高可用與高性能的存儲(chǔ)引擎。它的設(shè)計(jì)目標(biāo)主要面向在線事務(wù)處理（OLTP）的應(yīng)用。它的特點(diǎn)有行鎖設(shè)計(jì)、支持外鍵、支持類似Oracle的非鎖定讀等。

InnoDB存儲(chǔ)引擎支持以下幾種常見索引：B+樹索引、全文索引、哈希索引。我們平時(shí)在開發(fā)中使用最多的，就是B+樹索引。
B+樹索引是傳統(tǒng)意義上的索引，這是目前關(guān)系數(shù)據(jù)庫(kù)系統(tǒng)中最常用和最有效的索引。需要注意的是，B+樹索引并不能找到一個(gè)給定鍵值的具體行。B+樹索引能夠找到的只是被查找的數(shù)據(jù)行所在的頁(yè)。然后通過(guò)數(shù)據(jù)庫(kù)把頁(yè)讀入內(nèi)存，在內(nèi)存中進(jìn)行查找，最后得到所需的數(shù)據(jù)。

B+樹索引是基于B+樹數(shù)據(jù)結(jié)構(gòu)構(gòu)建的，B+樹的數(shù)據(jù)結(jié)構(gòu)在很多數(shù)據(jù)結(jié)構(gòu)書中都有詳細(xì)描述，在此不再詳述。數(shù)據(jù)庫(kù)中的B+樹索引可以分為聚集索引和輔助索引，它們的內(nèi)部都是高度平衡的，葉子存放著所有的數(shù)據(jù)的樹結(jié)構(gòu)。聚集索引與輔助索引不同的是，葉子節(jié)點(diǎn)存放的是一整行信息。

聚集索引（Cluster Index)

聚集索引就是按照每張表的主鍵構(gòu)建一個(gè)B+樹，同時(shí)葉子節(jié)點(diǎn)存放的即為整張表的行記錄數(shù)據(jù)，我們也將聚集索引的葉子節(jié)點(diǎn)稱為數(shù)據(jù)頁(yè)。聚集索引的結(jié)構(gòu)決定了索引組織表中數(shù)據(jù)也是索引的一部分。由于B+樹索引本身是有序的，同時(shí)數(shù)據(jù)行也存儲(chǔ)在葉子節(jié)點(diǎn)上，因此通過(guò)聚集索引我們能夠很快的訪問(wèn)針對(duì)范圍值的查詢。

注：

• 當(dāng)你在表中創(chuàng)建了一個(gè)PRIMARY KEY時(shí)，InnoDB就將該索引作為了聚集索引，因此最好為任何一個(gè)你創(chuàng)建的表添加PRIMARY KEY。
• 如果一個(gè)表中沒有定義一個(gè)PRIMARY KEY，MySQL會(huì)把表中第一個(gè)非NULL且唯一的索引作為聚集索引。
• 如果表中沒有符合上述兩種情況的索引，InnnoDB就會(huì)隱式的在包含rowID的合成列上生成聚集索引。

（也即不管如何，InnoDB的表中一定會(huì)有一個(gè)聚集索引，不管是顯示還是隱式創(chuàng)建的）

由于實(shí)際的數(shù)據(jù)頁(yè)只能按照一顆B+樹進(jìn)行排序，因此每張表只能擁有一個(gè)聚集索引。在多數(shù)情況下，查詢優(yōu)化器傾向于采用聚集索引，因?yàn)榫奂饕軌蛟贐+樹葉子節(jié)點(diǎn)上直接找到數(shù)據(jù)。（聚集索引對(duì)主鍵的排序查找和范圍查找速度非?？?。因?yàn)槿~子節(jié)點(diǎn)就維護(hù)了數(shù)據(jù)行。）圖一展示了一個(gè)聚集索引的數(shù)據(jù)分布(所有數(shù)據(jù)行就直接在葉子節(jié)點(diǎn)上)：

聚集索引(局部)

輔助索引(Secondary Index)

InnoDB中所有除聚集索引以外的所有索引都被稱為輔助索引。對(duì)于輔助索引，葉子節(jié)點(diǎn)并不包含行記錄的全部數(shù)據(jù)。葉子節(jié)點(diǎn)除了包含鍵值以外，每個(gè)葉子節(jié)點(diǎn)的索引行中還包含一個(gè)主鍵值。通過(guò)主鍵值InnoDB存儲(chǔ)引擎可以再次在聚集索引中找到對(duì)應(yīng)索引項(xiàng)的行數(shù)據(jù)。輔助索引的存在不會(huì)影響數(shù)據(jù)在聚集索引中的組織，因此每張表上可以存在多個(gè)輔助索引。當(dāng)通過(guò)輔助索引來(lái)查找數(shù)據(jù)時(shí)，InnoDB存儲(chǔ)引擎會(huì)遍歷輔助索引并獲得頁(yè)中的指向主鍵索引的主鍵，然后再通過(guò)主鍵索引依次找到一個(gè)完整的行記錄。因此，這就意味著通過(guò)輔助索引查找行，存儲(chǔ)引擎先找到輔助索引的葉子節(jié)點(diǎn)，得到對(duì)應(yīng)的主鍵值，然后根據(jù)該主鍵值去聚集索引上查找對(duì)應(yīng)的行數(shù)據(jù)。也即一次輔助索引查找需要兩次對(duì)B+樹進(jìn)行查找。
輔助索引數(shù)據(jù)分布圖示：

輔助索引

B+樹索引的管理

索引的創(chuàng)建刪除通過(guò)兩種方法，一種是ALTER TABLE，一種是CREATE/DROP INDEX，具體語(yǔ)法如下：

CREATE INDEX | KEY idx_b ON t
ALTER TABLE t ADD INDEX | KEY idx_b (b(100));//前綴索引
ALTER TABLE t ADD INDEX | KEY idx_a_b (a,b);//聯(lián)合索引
ALTER TABLE t DROP INDEX | KEY idx_b;
DROP INDEX idx_b ON T;//刪除索引

通過(guò)SQL命令

SHOW INDEX FROM t;

可以查看表t上的索引情況，圖示如下：

該結(jié)果每行的含義如下：

在所有這些參數(shù)中，Cardinality的值十分關(guān)鍵，優(yōu)化器會(huì)根據(jù)該值來(lái)判斷是否使用該索引。但并非每次更新索引都會(huì)更新該值，這樣做的代價(jià)太大。如果我們需要主動(dòng)去更新該值，可以使用ANALYZE TABLE命令。

這里，我們會(huì)多了解一下Cardinality值的意義：Cardinality值表示索引中不重復(fù)記錄的預(yù)估值。雖然該值是一個(gè)預(yù)估值，但是對(duì)于我們是否需要在某列上建立索引具有很重要的指導(dǎo)意義。有一個(gè)參數(shù)Cardinality/n_rows_in_tables應(yīng)該盡可能的接近1，此時(shí)表明該列的值基本是相互唯一的，表明該列具有很高的選擇性，在該列上建立索引并通過(guò)該列進(jìn)行條件查詢，可以極大的改善查詢速度,如果該值過(guò)于小，我們就需要考慮是否有必要在該列上建立索引。舉一個(gè)例子：

CREATE TABLE test(
    id BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '',
    customer_id BIGINT(20) NOT NULL COMMENT '客戶ID',
    staff_id BIGINT(20) NOT NULL COMMENT '職員ID',
    sex TINYINT(1) DEFAULT 0 COMMENT '性別0-女 1-男',
    company VARCHAR(50) NOT NULL COMMENT '公司名稱'.
    PRIMARY KEY (id),
    KEY idx_sex (sex),
    KEY idx_cus_sta (customer_id,staff_id)
)ENGINE=InnoDB CHARSET=utf8;

如該表t所示，sex列只有兩種情況,可以想象在一般情況下通過(guò)SHOW INDEX查詢?cè)摫淼玫絪ex列的Cardinality的值一定是2，Cardinality/n_rows_in_tables的值一定非常?。ń咏?）以sex為條件進(jìn)行查詢時(shí)，每次也只能篩選一半的符合條件的數(shù)據(jù)記錄，該列的選擇性很低，一般沒有必要在這種列上建立索引。

在InnoDB存儲(chǔ)引擎中，Cardinality統(tǒng)計(jì)信息的更新發(fā)生在兩個(gè)操作中：INSERT和UPDATE。如果每次發(fā)生INSERT和UPDATE操作就進(jìn)行Cardinality信息統(tǒng)計(jì)更新，會(huì)給數(shù)據(jù)庫(kù)帶來(lái)很大的負(fù)擔(dān)，因此InnoDB存儲(chǔ)引擎內(nèi)部對(duì)Cardinality值的更新策略是：

• 表中1/6數(shù)據(jù)發(fā)生了變化；
• stat_modified_counter>2000 000 000 ；這是為了應(yīng)對(duì)這樣一種情況，表中某一行記錄頻繁的更新，但實(shí)際上表中的數(shù)據(jù)行數(shù)并沒有增加，故在InnoDB存儲(chǔ)引擎內(nèi)部有一個(gè)計(jì)數(shù)器stat_modified_counter,用來(lái)表示發(fā)生變化的次數(shù)，大于2000 000 000同樣更新Cardinality的值。

InnoDB存儲(chǔ)引擎內(nèi)部對(duì)Cardinality值的統(tǒng)計(jì)和更新操作是通過(guò)采樣方法的。InnoDB存儲(chǔ)引擎默認(rèn)對(duì)8個(gè)葉子節(jié)點(diǎn)進(jìn)行采樣，過(guò)程如下：

• 取得B+樹索引中葉子節(jié)點(diǎn)的數(shù)目，記為N
• 隨機(jī)取得B+樹索引中的8個(gè)葉子節(jié)點(diǎn)，統(tǒng)計(jì)每個(gè)頁(yè)中不同記錄的個(gè)數(shù)，記為M1,M2,M3...M8
• Cardinality=(M1+M2+...+M8)*N/8

很明顯我們可以看出，Cardinality值是通過(guò)對(duì)8個(gè)葉子節(jié)點(diǎn)預(yù)估而來(lái)。根據(jù)它的計(jì)算方法，我們可以知道Cardinality值是在不斷變化的。

B+樹索引的使用

在OLTP應(yīng)用中，我們的查詢操作一次只是從數(shù)據(jù)庫(kù)中獲取一小部分?jǐn)?shù)據(jù)，如根據(jù)主鍵值查詢訂單信息，這就是一種典型的OLTP查詢。在這種情況下，通過(guò)該主鍵建立的B+樹索引能夠很快的獲取對(duì)應(yīng)數(shù)據(jù)。在實(shí)際使用中，我們可能會(huì)用到以下一些索引。

• 前綴索引

對(duì)于Mysql中的字符列，我們可以選擇在某些列上建立前綴索引。因?yàn)橛行r(shí)候，索引列很長(zhǎng)，這會(huì)使得索引變得大且慢，通常這時(shí)候，我們可以索引該字符列開始的部分字符，這樣可以大幅節(jié)約索引空間，提高索引效率，但是需要注意的是，這樣也可能會(huì)降低索引的選擇性。因此，前綴索引的目標(biāo)應(yīng)該是：要選擇足夠長(zhǎng)的前綴以保證高的選擇性，同時(shí)前綴不要太長(zhǎng)（以節(jié)約空間）還是以test表為例，我們?cè)赾ompany字符列上創(chuàng)建一個(gè)前綴索引：

ALTER TABLE test ADD INDEX idx_com company(20);//示例，前綴長(zhǎng)度按實(shí)際情況選擇

具體在company字段上選擇多長(zhǎng)的前綴呢？這個(gè)需要我們實(shí)際去試一試。

SELECT COUNT(DISTINCT LEFT(company,4))/COUNT(*) AS sel4,
       COUNT(DISTINCT LEFT(company,5))/COUNT(*) AS sel5,
       COUNT(DISTINCT LEFT(company,6))/COUNT(*) AS sel6,
       ......
       FROM test;

通過(guò)對(duì)比sel4,sel5,sel6...的結(jié)果，就可以找到一個(gè)比較合適的前綴長(zhǎng)度。

• 聯(lián)合索引

聯(lián)合索引是指在多個(gè)列上建立的索引。如下sql語(yǔ)句：

ALTER TABLE t ADD INDEX | KEY idx_a_b (a,b);//聯(lián)合索引

在B+樹中，所有鍵值都是排序的，而對(duì)于聯(lián)合索引，索引列的順序意味著索引首先按照最左列進(jìn)行排序，其次是第二列，依次等等。因此，索引的順序非常重要。一個(gè)簡(jiǎn)單的聯(lián)合索引的例子如下圖所示：

聯(lián)合索引

當(dāng)不需要考慮排序和分組時(shí)，將選擇性最高的列放在索引的前面通常是更好的。這么做可以最快的過(guò)濾出所需要的行。以前面的test表為例，如果我們想要建立一個(gè)包含customer_id與staff_id的聯(lián)合索引，我們可以先比較一下兩個(gè)列的選擇性，可以執(zhí)行一下SQL語(yǔ)句：

SELECT COUNT(DISTINCT coustomer_id)/COUNT(*) AS customer_id_selectivity,
       COUNT(DISTINCT staff_id)/COUNT(*) AS staff_id_selectivity 
FROM test;

比較customer_id_selectivity與staff_id_selectivity哪個(gè)值更接近1，也即對(duì)應(yīng)列的選擇性就更高，該列就更適合放在聯(lián)合索引的前面。

• 覆蓋索引

InnoDB支持覆蓋索引（覆蓋索引并非是一種可以通過(guò)SQL語(yǔ)句創(chuàng)建的索引，與前綴索引、聯(lián)合索引不同，我們可以認(rèn)為這是一種邏輯上的索引，即符合一定條件的索引我們都可以稱之為是覆蓋索引），即從輔助索引中就可以直接得到查詢的記錄，而不需要再次查詢聚集索引中的記錄。使用覆蓋索引的一個(gè)好處就是輔助索引不包含整行記錄，因此它的大小遠(yuǎn)小于聚集索引，可以減少大量IO操作，查詢速度很快。

不是所有的索引都能夠成為覆蓋索引，覆蓋索引必須要存儲(chǔ)索引列的值。當(dāng)我們發(fā)起一個(gè)一個(gè)被索引覆蓋的查詢時(shí)，在EXPLAIN的Extra列可以看到Using Index信息，表示該次查詢?cè)谒饕暇椭苯荧@得了所有需要的數(shù)據(jù)，也即這是一個(gè)覆蓋索引。如下圖所示：

由于customer_id與staff_id同時(shí)存在于索引中，因此當(dāng)查詢customer_id與staff_id時(shí)，就可以直接從輔助索引中可以獲取所需的字段，而不需要再次去查找聚集索引。

最后

InnoDB存儲(chǔ)引擎是我們平時(shí)使用最廣泛的MySQL存儲(chǔ)引擎。同時(shí)B+樹索引也是我們默認(rèn)選擇的索引類型。正確的創(chuàng)建和使用索引是實(shí)現(xiàn)高性能查詢的基礎(chǔ)。而這些合理的索引操作是基于我們對(duì)InnoDB存儲(chǔ)引擎的內(nèi)部原理有一定的了解之上的。

本文只是關(guān)于InnoDB索引的簡(jiǎn)單總結(jié)，如有問(wèn)題，歡迎大家一起交流、討論。

參考

• mysql reference
• 高性能MySQL
• MySQL技術(shù)內(nèi)幕