InnoDB存儲(chǔ)引擎支持以下幾種常見(jiàn)的索引，如B+樹(shù)索引、哈希索引、全文索引。哈希索引是自適應(yīng)的，InnoDB會(huì)根據(jù)表的使用情況自動(dòng)為表生成哈希索引。

B+樹(shù)索引是目前關(guān)系型數(shù)據(jù)庫(kù)中最常用、最有效的索引之一，其索引結(jié)構(gòu)是一種多路平衡樹(shù)結(jié)構(gòu)（與二叉樹(shù)類(lèi)似，B代表的不是Binary，而是Balance）。通過(guò)B+樹(shù)索引能夠快速的定位要要查找的數(shù)據(jù)所在的數(shù)據(jù)頁(yè)，然后將頁(yè)讀入內(nèi)存，在通過(guò)頁(yè)字典槽快速尋找到數(shù)據(jù)行。

InnoDB引擎中實(shí)現(xiàn)了B+樹(shù)結(jié)構(gòu)的索引，其高度一般在2~3層，換句話說(shuō)，查詢記錄的IO操作次數(shù)最多3次。InnoDB索引可以分為聚簇索引和非聚簇索引，這兩種分類(lèi)的索引都是B+樹(shù)結(jié)構(gòu)的。

聚簇索引（Clustered Index）

聚簇索引，又稱(chēng)聚集索引，其是一種數(shù)據(jù)存儲(chǔ)的方式。在InnoDB存儲(chǔ)引擎中B+樹(shù)索引與數(shù)據(jù)是存儲(chǔ)在一起的，換句話說(shuō)InnoDB存儲(chǔ)引擎的數(shù)據(jù)是由B+樹(shù)索引組織的。

建表

在進(jìn)行索引講解前，我們先建立如下表：

-- ----------------------------
-- Table structure for index_test
-- ----------------------------
DROP TABLE IF EXISTS `index_test`;
CREATE TABLE `index_test` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `age` int NOT NULL,
  `profession` varchar(100) NOT NULL,
  `sex` char(1) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `index_name_profession` (`name`,`profession`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of index_test
-- ----------------------------
BEGIN;
INSERT INTO `index_test` (`id`, `name`, `age`, `profession`, `sex`) VALUES (1, 'Tom', 33, 'teacher', 'm');
INSERT INTO `index_test` (`id`, `name`, `age`, `profession`, `sex`) VALUES (2, 'Ryan', 25, 'programmer', 'm');
INSERT INTO `index_test` (`id`, `name`, `age`, `profession`, `sex`) VALUES (3, 'Li', 18, 'student', 'w');
INSERT INTO `index_test` (`id`, `name`, `age`, `profession`, `sex`) VALUES (4, 'Bob', 40, 'doctor', 'm');
INSERT INTO `index_test` (`id`, `name`, `age`, `profession`, `sex`) VALUES (5, 'Jim', 60, 'doctor', 'm');
INSERT INTO `index_test` (`id`, `name`, `age`, `profession`, `sex`) VALUES (6, 'Ben', 34, 'teacher', 'm');
INSERT INTO `index_test` (`id`, `name`, `age`, `profession`, `sex`) VALUES (7, 'Joy', 28, 'programmer', 'w');
COMMIT;

聚簇索引結(jié)構(gòu)

數(shù)據(jù)行實(shí)際存儲(chǔ)在數(shù)據(jù)頁(yè)中，通過(guò)B+樹(shù)索引結(jié)構(gòu)的葉子節(jié)點(diǎn)將數(shù)據(jù)頁(yè)組織起來(lái)。如下圖所示（若對(duì)B+樹(shù)結(jié)構(gòu)不了解可以看我另外一篇文章《數(shù)據(jù)結(jié)構(gòu)-B樹(shù)族》）：

聚簇索引結(jié)構(gòu)

• B+樹(shù)的每一個(gè)葉子節(jié)點(diǎn)都是一個(gè)數(shù)據(jù)頁(yè)。
• B+樹(shù)的內(nèi)部節(jié)點(diǎn)都是索引節(jié)點(diǎn)，鍵的左右指針指向的都是數(shù)據(jù)頁(yè)。
• 葉子節(jié)點(diǎn)間（數(shù)據(jù)頁(yè)）是通過(guò)指針（頁(yè)指針）相連的，是一個(gè)雙向鏈表結(jié)構(gòu)。
• 葉子節(jié)點(diǎn)（數(shù)據(jù)頁(yè)）內(nèi)部是數(shù)據(jù)行，數(shù)據(jù)行之間也是通過(guò)指針相連。
• 葉子節(jié)點(diǎn)（數(shù)據(jù)頁(yè)）與葉子節(jié)點(diǎn)內(nèi)的數(shù)據(jù)行，都是按照主鍵順序排列的（注意：葉子節(jié)點(diǎn)之間與行之間都不是物理連續(xù)的，而都是鏈表結(jié)構(gòu)）。

主鍵選擇原則

使用B+樹(shù)作為數(shù)據(jù)存儲(chǔ)的結(jié)構(gòu)我們需要讓主鍵（鍵值）滿足以下特性：

鍵值長(zhǎng)度盡量小：鍵值是會(huì)占用空間的，我們希望的是其越小越好。

鍵值盡量單調(diào)遞增：B+樹(shù)的插入可能會(huì)引起節(jié)點(diǎn)的分裂，如果不是單調(diào)遞增，我們可能會(huì)插入到頁(yè)中間位置，這就可能導(dǎo)致數(shù)據(jù)的分裂以及數(shù)據(jù)的挪動(dòng)，嚴(yán)重的影響插入性能。

非聚簇索引（Secondary Index）

非聚簇索引，也稱(chēng)非聚集索引、二級(jí)索引、輔助索引等。在InnoDB中，非聚簇索引的頁(yè)節(jié)點(diǎn)除了包含鍵之外，還包含一個(gè)bookmark，也就是一個(gè)可以找到該鍵對(duì)應(yīng)的數(shù)據(jù)行所在位置。結(jié)合我們上面講到的，這里的書(shū)簽值就對(duì)應(yīng)的是聚簇索引的鍵。

輔助索引與聚簇索引關(guān)系

單列索引

單列索引，即一個(gè)索引樹(shù)中只包含一個(gè)列的值，一張表可以建立多個(gè)單列索引，如果一個(gè)查詢語(yǔ)句中包含了單列索引列，優(yōu)化器可能只會(huì)選擇一個(gè)最優(yōu)的單列索引，具體遵循如下原則：

• 如果查詢條件是AND連接，且用到的所有（或部分）列都建立了索引，則優(yōu)化器會(huì)按照最優(yōu)策略，可能會(huì)命中一個(gè)或多個(gè)索引。
• 如果查詢條件是OR連接，且用到的所有列都建立了索引，則所有索引都會(huì)命中。
• 如果查詢條件是OR連接，且用到的只有部分列建立了索引，則執(zhí)行全表掃表。

1、2兩條原則涉及到了一個(gè)index_merge策略，這是一個(gè)多索引合并優(yōu)化策略，這個(gè)概念我們下面會(huì)講。

單列索引

索引合并

合并索引是在MySQL 5.7的InnoDB引擎引入的一個(gè)策略，我們稱(chēng)之為index_merge，如果使用到了這種策略，執(zhí)行計(jì)劃會(huì)返回type：index_merge，它具有有以下的特性：

• 它會(huì)將幾個(gè)索引的范圍掃描結(jié)果合并成（AND取交集、OR取并集）一個(gè)。
• 該策略只適用于單表操作，多表查詢失效。
• 如果存在某個(gè)OR條件沒(méi)有建立單列索引，則失效。
• 如果所有條件對(duì)應(yīng)的列都是索引，則AND和OR組合使用也會(huì)命中該類(lèi)型索引。
• 執(zhí)行如下語(yǔ)句我們可以看到type為index_merge，Extra為sort_union。

EXPLAIN SELECT * FROM index_test WHERE name='Tom' OR professinotallow='teacher';

執(zhí)行結(jié)果

組合索引

在沒(méi)有建立組合索引的情況下，可通過(guò)多個(gè)單列索引UNION操作快速得到結(jié)果。接下來(lái)我們介紹一下組合索引，先見(jiàn)下圖：

組合索引

對(duì)于組合索引來(lái)說(shuō)，所有參與索引的列都會(huì)出現(xiàn)在索引樹(shù)上。如上圖，是一個(gè)index_profession_name組合索引，存儲(chǔ)引擎首先會(huì)根據(jù)profession列值順序建立第一個(gè)索引列，緊接在第一個(gè)列的基礎(chǔ)上建立第二個(gè)索引列。

組合索引有以下特性：

• 查詢遵循最左原則，查詢條件必須包含第一個(gè)索引列，即profession、profession&name、profession&sex等組合；
• 如果查詢條件包含了第一個(gè)索引列，則查詢條件的書(shū)寫(xiě)順序沒(méi)有要求，即name&profession、age&profession等寫(xiě)法都可以，優(yōu)化器會(huì)處理順序;
• OR查詢會(huì)讓組合索引失效；

回表查詢

組合索引的查詢可能涉及到回表查詢操作，什么是回表查詢呢？

當(dāng)SELECT的列中包含了非索引列時(shí)，我們需要通過(guò)聚簇索引來(lái)補(bǔ)齊數(shù)據(jù)，這個(gè)就叫回表查詢。

我們來(lái)舉個(gè)例子：

SELECT profession,name,age,sex FROM index_test WHERE profession = 'xxx';

此時(shí)的age、sex列不在索引index_profession_name中，則需要通過(guò)查詢index_test的聚簇索引補(bǔ)齊age、sex列信息。

如果我們SELECT的列都是索引列呢？是否就不需要回表查詢了，這個(gè)有涉及另一個(gè)概念即索引覆蓋。

索引覆蓋

從上面的一個(gè)例子我們很容易得出，索引覆蓋就是：

當(dāng)SELECT的列中包含都是索引列時(shí)，我們通過(guò)該非聚簇索引就能拿到所有數(shù)據(jù)，這就叫做索引覆蓋。

如下圖是索引覆蓋時(shí)的執(zhí)行計(jì)劃的內(nèi)容，我們可以看到Extra為Usering index。

索引覆蓋

索引下推

關(guān)于索引下推從字面上不太好理解（這個(gè)詞很唬人，但是我們了解了其邏輯后，你會(huì)發(fā)現(xiàn)極其簡(jiǎn)單，論起名的重要性），我們先看下面這張圖：

SELECT * FROM index_test WHERE name like 'J%' AND profession = 'programmer' AND sex = 'm';

索引下推

在MySQL5.6以前，只要第一個(gè)索引列滿足查詢條件，就會(huì)回表查詢，如上圖有3次回表查詢。

在MySQL5.6之后，通過(guò)索引下推，會(huì)依次匹配多個(gè)索引列，過(guò)濾掉不符合的，從而減少回表次數(shù)，如上圖不等于programmer直接跳過(guò)了，減少了1次回表操作。

索引下推可以有效減少回表次數(shù)，從而提升查詢效率（也就是多個(gè)if判斷，搞個(gè)名詞唬人）。

索引的選擇性

索引的選擇性，就是指該索引的建立是否有必要性，因?yàn)椴⒉皇撬胁樵儣l件中出現(xiàn)的列都需要添加索引。比如性別（男、女），整張表除了男就是女，浪費(fèi)索引存儲(chǔ)空間且起不到任何提升查詢速度的作用。

索引的選擇性有一個(gè)非常重要的指標(biāo)，即Cardinality（基數(shù)），即該索引所統(tǒng)計(jì)的不重復(fù)記錄數(shù)，如果其越接近于聚簇索引，那么其利用率及效率越高，如下圖所示：

索引的選擇性

索引的選擇性公式為：索引的選擇性 = 不重復(fù)的索引值數(shù) / 數(shù)據(jù)表的記錄總數(shù)。

聚簇索引選擇性為1，也就是說(shuō)如果一個(gè)索引的選擇性約接近1，其查詢效率越高，但是索引所占用的空間越大。

索引失效

• OR 前后查詢條件不都是索引字段。
• 未遵循最左N個(gè)字段。
• 模糊查詢 LIKE 以 % 開(kāi)頭。
• 需要類(lèi)型轉(zhuǎn)換。
• WHERE 中索引列有計(jì)算。
• WHERE 中索引列用到了函數(shù)。
• 索引字段上使用 NOT、<> 、!= 。
• 當(dāng)全表掃描速度比索引速度快時(shí)。

前綴索引

我們先來(lái)看如下兩個(gè)索引：

ALTER TABLE index_test ADD INDEX index_name(name);

ALTER TABLE index_test ADD INDEX index_name_pre(name(1));

上面兩個(gè)索引的唯一不同點(diǎn)就是，index_name_pre索引是一個(gè)name的前綴索引，前綴的長(zhǎng)度為1，也就是說(shuō)index_name_pre只包含name字段的第一個(gè)字符。

我們分別執(zhí)行下面的語(yǔ)句，看一下兩個(gè)索引的使用情況：

EXPLAIN SELECT * FROM index_test WHERE name like 'Ben';

• index_name_pre索引

index_name_pre索引

• index_name索引

index_name索引

從兩條執(zhí)行計(jì)劃可以看出，若在index_name_pre索引下查詢會(huì)掃描2行記錄，而index_name索引下只需要掃描1行記錄。那是不是前綴索引就沒(méi)有存在的意義了呢？然而并不是，我們接著看。

前綴索引的選擇原則

• 列值很長(zhǎng)且需要建立索引：如果我們?yōu)楸韎ndex_test表建立了一個(gè)新列：address varchar(500)，該列是一個(gè)存儲(chǔ)用戶的地址列，其實(shí)際長(zhǎng)度可能有幾百個(gè)字符。如果我們?yōu)槠浣⒁粋€(gè)完整索引，其所占用的索引空間將是巨大的，這時(shí)我們可以為其建立一個(gè)前綴索引。
• 前綴索引需要列的一部分前綴作為索引，這個(gè)“一部分”的計(jì)算依據(jù)是根據(jù)索引的選擇性來(lái)決定的。

我們希望的是：前綴n的選擇性無(wú)限趨近于全列的選擇性，但n的值需要盡量?。ü?jié)省空間），計(jì)算步驟如下：

column_name的全列選擇性計(jì)算方式：

• SELECT COUNT(DISTINCT column_name) / COUNT(*) FROM table_name;

column_name的前綴n的選擇性計(jì)算方式：

• SELECT COUNT(DISTINCT LEFT(column_name, n)) / COUNT(*) FROM table_name;

通過(guò)調(diào)整n的大小，得到一個(gè)接近全列選擇性的n值，同時(shí)又能保證前綴足夠小。

Hash索引

MySQL的Memory引擎支持Hash索引，但我們今天講的不是該引擎，而是InnoDB的存儲(chǔ)引擎的哈希索引。我這里說(shuō)的哈希索引，嚴(yán)格意義上說(shuō)應(yīng)該叫自適應(yīng)哈希索引（Adaptive Hash Index，AHI）。

自適應(yīng)哈希索引是不能用戶手動(dòng)創(chuàng)建的，它是由引擎根據(jù)當(dāng)前視圖的數(shù)據(jù)訪問(wèn)頻次在緩沖池建立一個(gè)哈希索引。通過(guò)訪問(wèn)頻次建立，換句話說(shuō)就是為高頻熱點(diǎn)數(shù)據(jù)建立索引。

結(jié)構(gòu)

哈希索引是通過(guò)哈希表來(lái)實(shí)現(xiàn)的，Key是利用查詢條件中的鍵通過(guò)哈希函數(shù)計(jì)（CRC32算）算得到，Value則是直接指向數(shù)據(jù)頁(yè)中的值。

自適應(yīng)哈希索引結(jié)構(gòu)

如上圖通過(guò)Hash索引可以做到O(1)的時(shí)間復(fù)雜度查詢，而利用輔助索引則需要N次（與樹(shù)的高度有關(guān)）。

自適應(yīng)的觸發(fā)條件

• 使用相同的條件訪問(wèn)了同一個(gè)索引17次；

例如表index_test表有index_profession_name組合索引，如果我們使用以下任意語(yǔ)句訪問(wèn)（不能是交替訪問(wèn)）可創(chuàng)建自適應(yīng)索引：

SELECT * FROM index_test WHERE profession = 'programmer';

SELECT * FROM index_test WHERE profession = 'programmer' AND name = 'Tom';

如果以同一查詢條件進(jìn)行了100次以上的訪問(wèn)；

數(shù)據(jù)頁(yè)被相同查詢語(yǔ)句訪問(wèn)了N次（N = 頁(yè)記錄數(shù) * 1/16）;

缺點(diǎn)

• 自適應(yīng)哈希索引的維護(hù)勢(shì)必會(huì)用到鎖來(lái)控制并發(fā)，那么該鎖可能導(dǎo)致性能損耗。
• 自適應(yīng)哈希索引在DML操作下引發(fā)的數(shù)據(jù)變化時(shí)處理效率成本高。
• 自適應(yīng)哈希索引的條件很苛刻，需要相同的查詢條件連續(xù)訪問(wèn)，且只適用于等值搜索條件，order by、模糊查詢等都不行。
• 其本身會(huì)可能會(huì)占用大量的內(nèi)存池空間，從而加重引擎的負(fù)擔(dān)，需要做好參數(shù)調(diào)節(jié)。

總結(jié)

• InnoDB存儲(chǔ)引擎的索引共有以下幾種：B+樹(shù)索引、哈希索引、全文索引，本文主要介紹了前兩種。
• InnoDB存儲(chǔ)引擎的數(shù)據(jù)是由B+樹(shù)索引組織的，換句話說(shuō)：聚簇索引即使索引又存儲(chǔ)完整記錄數(shù)據(jù)。
• 可以利用多個(gè)單列索引的索引合并來(lái)實(shí)現(xiàn)組合索引的效果，但是不推薦這么做。
• 在設(shè)計(jì)組合索引時(shí)需要注意索引的選擇性，約趨近于1的索引會(huì)越高效，但是索引存儲(chǔ)空間也會(huì)變大。
• 可以利用覆蓋索引來(lái)快速的查詢，覆蓋索引不用回表查詢，非常高效。
• 當(dāng)遇到非常大的列需要建立索引時(shí)可以考慮使用前綴索引，但要注意前綴的長(zhǎng)度選擇，可通過(guò)索引的選擇性公式計(jì)算。
• 索引下推可以有效減少組合索引的回表次數(shù)，提示查詢效率。
• 自適應(yīng)哈希索引的條件非常的苛刻，因此要設(shè)法利用它來(lái)提升查詢效率。