共計 10168 個字符，預(yù)計需要花費 26 分鐘才能閱讀完成。

自動寫代碼機器人，免費開通

丸趣 TV 小編給大家分享一下 MySQL 中索引的案例分析，希望大家閱讀完這篇文章后大所收獲，下面讓我們一起去探討吧！

1. 索引種類

在 MySQL 中，從索引的邏輯或者說字段特性來區(qū)分，索引大致分為以下幾個種類：普通索引、唯一索引、主鍵索引、聯(lián)合索引和前綴索引。

普通索引：最基礎(chǔ)的索引，沒有任何限制。唯一索引：索引列的值必須唯一。主鍵索引：特殊的唯一索引，作為主鍵它的值不能為空。聯(lián)合索引：聯(lián)合索引就是索引列為多個字段的普通索引，需要考慮最左前綴原則。前綴索引：對字符類型的前幾個字符或二進(jìn)制類型的前幾個字節(jié)建立索引。

還有另外一種從物理存儲上來區(qū)分的索引分類：聚簇索引和非聚簇索引。

聚簇索引：索引順序與數(shù)據(jù)存儲順序一致，其葉子節(jié)點存儲的是數(shù)據(jù)行。非聚簇索引：非聚簇索引的葉子節(jié)點存儲的是聚簇索引的值，同時它是基于聚簇索引創(chuàng)建的。

簡單來說，所謂的聚簇索引就是索引 key 與數(shù)據(jù)行在一起，而非聚簇索引的索引 key 對應(yīng)的值是聚簇索引的值。

2. 索引的數(shù)據(jù)結(jié)構(gòu)

常見的用于實現(xiàn)索引的數(shù)據(jù)結(jié)構(gòu)有哈希表、有序數(shù)組和搜索樹。

2.1 哈希索引

哈希表是一個以 key-value 形式來存儲數(shù)據(jù)的容器，和 HashMap 一樣，哈希索引也會將 key 通過特定的哈希函數(shù)計算得到索引值，然后在數(shù)組的相應(yīng)位置存放 key 對應(yīng)的 value，如果有兩個 key 通過哈希函數(shù)計算得到的索引值相同（發(fā)生哈希沖突），那么數(shù)組的這個位置就會變成一個鏈表，存放所有哈希值相同的 value。

所以在一般情況下，哈希表進(jìn)行等值查詢的時間復(fù)雜度可以達(dá)到 O(1)，但是在發(fā)生哈希沖突的情況下，還需要額外遍歷鏈表中的所有值，才能夠找到符合條件的數(shù)據(jù)。

另外，考慮到經(jīng)過哈希函數(shù)計算得到的索引是不規(guī)律的——哈希表希望所有的 key 能夠得到充分散列，這樣才能讓 key 均勻分布，不浪費空間——即哈希表的 key 是非順序的，所以使用哈希表來進(jìn)行區(qū)間查詢時很慢的，排序也是同樣的道理。

所以，哈希表僅適用于等值查詢。

2.2 有序數(shù)組

有序數(shù)組顧名思義是一個按照 key 的順序進(jìn)行排列的數(shù)組，它進(jìn)行等值查詢的時間復(fù)雜度使用二分查詢可以達(dá)到 O(logN)，這與哈希表相比遜色不少。

但是通過有序數(shù)組進(jìn)行范圍查詢的效率較高：首先通過二分查詢找到最小值（或最大值），然后反向遍歷，直到另一個邊界。

至于排序，有序數(shù)組本來就是有序的，天然已經(jīng)排好序了，當(dāng)然排序字段不是索引字段就另說了。

但是有序數(shù)組有一個缺點，由于數(shù)組元素是連續(xù)且有序的，如果此時插入新的數(shù)據(jù)行，為了維持有序數(shù)組的有序性，需要將比此元素 key 大的元素都往后移動一個單位，給他騰出一個地方插入。而這種維護(hù)索引的方式的代價是很大的。

所以，有序數(shù)組適合存儲衣服初始化過后就不再更新的數(shù)據(jù)。

2.3 搜索樹

了解過數(shù)據(jù)結(jié)構(gòu)的人應(yīng)該會知道，搜索樹是一個查詢時間復(fù)雜度為 O(logN)，更新的時間復(fù)雜度也是 O(logN) 的數(shù)據(jù)結(jié)構(gòu)。所以搜索樹相較于哈希表和有序數(shù)組來說兼顧查詢與更新兩方面。也正是由于這個原因，在 MySQL 中最常用的數(shù)據(jù)模型就是搜索樹。

而考慮到索引是存放在磁盤中的，如果搜索樹是一棵二叉樹，那么它的子節(jié)點只能有左右兩個，在數(shù)據(jù)比價多的情況下，這棵二叉樹的樹高可能會非常高，當(dāng) MySQL 進(jìn)行查詢的時候，可能由于樹高導(dǎo)致磁盤 I / O 次數(shù)過多，查詢效率變慢。

2.4 全文索引

除此之外，還有一種全文索引，它通過建立倒排索引，解決了判斷字段是否包含的問題。

倒排索引是用來存儲在全文搜索下某個單詞在一個文檔或者一組文檔中的存儲位置的映射，通過倒排索引可以根據(jù)單詞快速獲取包含這個單詞的文檔列表。

當(dāng)通過關(guān)鍵詞進(jìn)行檢索的時候，全文索引就會派上用場。

3. InnoDB 中的 BTree 索引 3.1 B+ 樹

這是一棵比較簡單的 B + 樹。

圖片來源: Data Structure Visualizations

從上面這張示例圖也可以看到，這棵 B + 樹最下面的葉子節(jié)點存儲了所有的元素，并且是按順序存儲的，而非葉子節(jié)點僅存儲索引列的值。

3.2 圖解 BTree 索引

在 InnoDB 中，基于 BTree 的索引模型的最為常用的，下面以一個實際的例子來圖解 InnoDB 中 BTree 索引的結(jié)構(gòu)。

CREATE TABLE `user` (`id` int(11) NOT NULL, `name` varchar(36) DEFAULT NULL, `age` int(11) DEFAULT NULL,
 PRIMARY KEY (`id`) USING BTREE, INDEX `nameIndex`(`name`) USING BTREE
) ENGINE = InnoDB;-- 插入數(shù)據(jù) insert into user1(id,name,age) values (1, one ,21),(2, two ,22),(3, three ,23),(4, four ,24),(5, five ,25); 復(fù)制代碼 

在這張表中只有兩個字段：主鍵 id 和 name 字段，同時建立了一個以 name 字段為索引列的 BTree 索引。

以主鍵 id 字段的為索引的索引，又叫主鍵索引，它的索引樹結(jié)構(gòu)是：索引樹的非葉子階段存放的都是主鍵 id 的值，葉子節(jié)點存放的值是該主鍵 id 對應(yīng)的整個數(shù)據(jù)行，如下圖所示：

也正因為主鍵索引的葉子節(jié)點存儲的是該主鍵 id 對應(yīng)的整個數(shù)據(jù)行，主鍵索引又被稱為聚簇索引。

而以 name 字段為列的索引樹，非葉子節(jié)點存放的同樣是索引列的值，而其葉子階段存放的值是主鍵 id 的值，如下圖所示。

3.3 索引的執(zhí)行流程

首先來看下面這句 SQL，查詢 user 表中 id=1 的數(shù)據(jù)行。

select * from user where id=1; 復(fù)制代碼 

這句 SQL 的執(zhí)行流程很簡單，存儲引擎會走主鍵 id 的索引樹，當(dāng)找到 id=1 時，就會把索引樹上 id=1 的數(shù)據(jù)行返回（由于主鍵值是唯一的，所以找到命中目標(biāo)就會停止搜索，直接返回結(jié)果集）。

3.3.1 回表

接下來再看使用普通索引進(jìn)行查詢的情況，它的情況與主鍵索引略有不同。

select * from user where name= one 復(fù)制代碼 

上面這句 SQL 查詢語句的流程是這樣的：首先存儲引擎會搜索普通索引 name 列的索引樹，當(dāng)命中 name 等于 one 的記錄后，存儲引擎需要經(jīng)過一個非常重要的步驟：回表。

由于普通索引的索引樹子節(jié)點存放的是主鍵值，當(dāng)查詢語句需要查詢除主鍵 id 及索引列之外的其他字段時，需要根據(jù)主鍵 id 的值再回到主鍵索引樹中進(jìn)行查詢，得到主鍵 id 對應(yīng)的整個數(shù)據(jù)行，然后從中獲取客戶端需要的字段后，才將這一行加入結(jié)果集。

隨后存儲引擎會繼續(xù)搜索索引樹，直到遇到第一個不滿足 name= one 的記錄才會停止搜索，最后將所有命中的記錄返回客戶端。

我們把根據(jù)從普通索引查詢到的主鍵 id 值，再在主鍵索引中查詢整個數(shù)據(jù)行的過程稱之為回表。

當(dāng)數(shù)據(jù)量十分龐大時，回表是一個十分耗時的過程，所以我們應(yīng)該盡量避免回表發(fā)生，這就引出了下一個問題：使用覆蓋索引避免回表。

3.3.2 覆蓋索引

不知道你有沒有注意到，在上一個回表的問題中有這樣一句描述：“當(dāng)查詢語句需要查詢除主鍵 id 及索引列之外的其他字段時 …”，在這種場景下需要通過回表來獲取其他的查詢字段。也就是說，如果查詢語句需要查詢的字段僅有主鍵 id 和索引列的字段時，是不是就不需要回表了？

下面來分析一波這個過程，首先建立一個聯(lián)合索引。

alter table user add index name_age (name , age 復(fù)制代碼 

那么這棵索引樹的結(jié)構(gòu)圖應(yīng)該是下面這樣：

聯(lián)合索引索引樹的子節(jié)點順序是按照聲明索引時的字段來排序的，類似于 order by name, age，而它索引對應(yīng)的值與普通索引一樣是主鍵值。

select name,age from user where name= one 復(fù)制代碼 

上面這條 SQL 是查詢所有 name= one 記錄的 name 和 age 字段，理想的執(zhí)行計劃應(yīng)該是搜索剛剛建立的聯(lián)合索引。

與普通索引一樣，存儲引擎會搜索聯(lián)合索引，由于聯(lián)合索引的順序是先按照 name 再按照 age 進(jìn)行排序的，所以當(dāng)找到第一個 name 不是 one 的索引時，才會停止搜索。

而由于 SQL 語句查詢的只是 name 和 age 字段，恰好存儲引擎命中查詢條件時得到的數(shù)據(jù)正是 name, age 和 id 字段，已經(jīng)包含了客戶端需要的字段了，所以就不需要再回表了。

我們把只需要在一棵索引樹上就可以得到查詢語句所需要的所有字段的索引成為覆蓋索引，覆蓋索引無須進(jìn)行回表操作，速度會更快一些，所以我們在進(jìn)行 SQL 優(yōu)化時可以考慮使用覆蓋索引來優(yōu)化。

4. 最左前綴原則

上面所舉的例子都是使用索引的情況，事實上在項目中復(fù)雜的查詢語句中，也可能存在不使用索引的情況。首先我們要知道，MySQL 在執(zhí)行 SQL 語句的時候一張表只會選擇一棵索引樹進(jìn)行搜索，所以一般在建立索引時需要盡可能覆蓋所有的查詢條件，建立聯(lián)合索引。

而對于聯(lián)合索引，MySQL 會遵循最左前綴原則：查詢條件與聯(lián)合索引的最左列或最左連續(xù)多列一致，那么就可以使用該索引。

為了詳細(xì)說明最左前綴原則，同時說明最左前綴原則的一些特殊情況。

5. 索引失效場景

即便我們根據(jù)最左前綴的原則創(chuàng)建了聯(lián)合索引，還是會有一些特殊的場景會導(dǎo)致索引失效，下面舉例說明。

假設(shè)有一張 table 表，它有一個聯(lián)合索引，索引列為 a,b,c 這三個字段，這三個字段的長度均為 10。

CREATE TABLE `demo` (`a` varchar(1) DEFAULT NULL, `b` varchar(1) DEFAULT NULL, `c` varchar(1) DEFAULT NULL, INDEX `abc_index`(`a`, `b`, `c`) USING BTREE
) ENGINE = InnoDB; 復(fù)制代碼 

5.1 全字段匹配

第一種情況是查詢條件與索引字段全部一致，并且用的是等值查詢，如：

select * from demo where a= 1 and b= 1 and c= 1 select * from demo where c= 1 and a= 1 and b= 1 復(fù)制代碼 

輸出上述兩條 SQL 的執(zhí)行計劃來看它們使用索引的情況。

mysql explain select * from demo where a= 1 and b= 1 and c= 1 
+----+-------------+-------+------------+------+---------------+-----------+---------+-------------------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------------------+------+----------+-------------+| 1 | SIMPLE | demo | NULL | ref | abc_index | abc_index | 18 | const,const,const | 1 | 100.00 | Using index |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------------------+------+----------+-------------+1 row in set, 1 warning (0.00 sec)
mysql explain select * from demo where c= 1 and a= 1 and b= 1 
+----+-------------+-------+------------+------+---------------+-----------+---------+-------------------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------------------+------+----------+-------------+| 1 | SIMPLE | demo | NULL | ref | abc_index | abc_index | 18 | const,const,const | 1 | 100.00 | Using index |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------------------+------+----------+-------------+1 row in set, 1 warning (0.00 sec) 復(fù)制代碼 

第一條 SQL 很顯然能夠用到聯(lián)合索引。

從執(zhí)行計劃中可以看到，第二條 SQL 與第一條 SQL 使用的索引以及索引長度是一致的，都是使用 abc_index 索引，索引長度為 18 個字節(jié)。

按理說查詢條件與索引的順序不一致，應(yīng)該不會用到索引，但是由于 MySQL 有優(yōu)化器存在，它會把第二條 SQL 優(yōu)化成第一條 SQL 的樣子，所以第二條 SQL 也使用到了聯(lián)合索引 abc_index。

綜上所述，全字段匹配且為等值查詢的情況下，查詢條件的順序不一致也能使用到聯(lián)合索引。

5.2 部分字段匹配

第二種情況是查詢條件與索引字段部分保持一致，這里就需要遵循最左前綴的原則，如：

select * from demo where a= 1 and b= 1 select * from demo where a= 1 and c= 1 復(fù)制代碼 

上述的兩條查詢語句分別對應(yīng)三個索引字段只用到兩個字段的情況，它們的執(zhí)行計劃是：

mysql explain select * from demo where a= 1 and b= 1 
+----+-------------+-------+------------+------+---------------+-----------+---------+-------------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------------+------+----------+-------------+| 1 | SIMPLE | demo | NULL | ref | abc_index | abc_index | 12 | const,const | 1 | 100.00 | Using index |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------------+------+----------+-------------+1 row in set, 1 warning (0.00 sec)
mysql explain select * from demo where a= 1 and c= 1 
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+--------------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+--------------------------+| 1 | SIMPLE | demo | NULL | ref | abc_index | abc_index | 6 | const | 1 | 100.00 | Using where; Using index |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+--------------------------+1 row in set, 1 warning (0.00 sec) 復(fù)制代碼 

從它們的執(zhí)行計劃可以看到，這兩條查詢語句都使用到了 abc_index 索引，不同的是，它們使用到索引的長度分別是：12、6 字節(jié)。

在這里需要額外提一下索引長度的計算方式，對于本例中聲明為 varchar(1) 類型的 a 字段，它的索引長度 = 1 * (3) + 1 + 2 = 6。

第一個數(shù)字 1 是該字段聲明時的長度。第二個數(shù)字 3 是該字段字符類型的長度：utf8=3, gbk=2, latin1=1。第三個數(shù)字 1 是該字段的默認(rèn)類型，若默認(rèn)允許 NULL，第三個數(shù)字是 1，因為 NULL 需要一個字節(jié)的額外空間；若默認(rèn)不允許 NULL，這里應(yīng)該是 0。第四個數(shù)字 2 是 varchar 類型的變長字段需要附加的字節(jié)。

所以這兩條查詢語句使用索引的情況是：

使用聯(lián)合索引，索引長度為 12 字節(jié)，使用到的索引字段是 a,b 字段；使用聯(lián)合索引，索引長度為 6 字節(jié)，使用到的索引字段是 a 字段；

由此可見：最左前綴原則要求，查詢條件必須是從索引最左列開始的連續(xù)幾列。

5.3 范圍查詢

第三種情況是查詢條件用的是范圍查詢（, ,!=, =, =,between,like）時，如：

select * from demo where a= 1 and b!= 1 and c= 1 復(fù)制代碼 

這兩條查詢語句的執(zhí)行計劃是：

mysql EXPLAIN select * from demo where a= 1 and b!= 1 and c= 1 
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+| 1 | SIMPLE | demo | NULL | range | abc_index | abc_index | 12 | NULL | 2 | 10.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+1 row in set, 1 warning (0.00 sec) 復(fù)制代碼 

從執(zhí)行計劃可以看到，第一條 SQL 使用了聯(lián)合索引，且索引長度為 12 字節(jié)，即用到了 a,b 兩個字段；第二條 SQL 也使用了聯(lián)合索引，索引長度為 6 字節(jié)，僅使用了聯(lián)合索引中的 a 字段。

綜上所述，在全字段匹配且為范圍查詢的情況下，也能使用聯(lián)合索引，但只能使用到聯(lián)合索引中第一個出現(xiàn)范圍查詢條件的字段。

需要注意的是：

like 必須要求是左模糊匹配才能用到索引，因為字符類型字段的索引樹也是有序的。between 并不一定是范圍查詢，它相當(dāng)于使用 in 多值精確匹配，所以 between 并不會因為是范圍查詢就讓聯(lián)合索引后面的索引列失效。5.4 查詢條件為函數(shù)或表達(dá)式

第四種情況是查詢條件中帶有函數(shù)或特殊表達(dá)式的，比如：

select * from demo where id + 1 = 2;select * from demo where concat(a, 1) = 11 復(fù)制代碼 

可能由于數(shù)據(jù)的原因（空表），我輸出的執(zhí)行計劃是使用了聯(lián)合索引的，但是事實上，在查詢條件中，等式不等式左側(cè)的字段包含表達(dá)式或函數(shù)時，該字段是不會用到索引的。

至于原因，是因為使用函數(shù)或表達(dá)式的情況下，索引字段本身的值已不具備有序性。

5.5 其他索引失效的場景查詢影響行數(shù)大于全表的 25% 查詢條件使用 (!=), not in, is not nullin 查詢條件中值數(shù)據(jù)類型不一致，MySQL 會將所有值轉(zhuǎn)化為與索引列一致的數(shù)據(jù)類型，從而無法使用索引 6. 索引下推

上文中已經(jīng)羅列了聯(lián)合索引的實際結(jié)構(gòu)、最左前綴原則以及索引失效的場景，這里再說一下索引下推這個重要的優(yōu)化規(guī)則。

select * from demo where a 1 and b= 1 
mysql explain select * from demo where a 1 and b= 1 
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+| 1 | SIMPLE | demo | NULL | range | abc_index | abc_index | 6 | NULL | 1 | 10.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+1 row in set, 1 warning (0.00 sec) 復(fù)制代碼 

上面這條查詢語句，從它的執(zhí)行計劃也可以看出，它使用的索引長度為 6 個字節(jié)，只用到了第一個字段。

所以 MySQL 在查詢過程中，只會對第一個字段 a 進(jìn)行 a 1 的條件判斷，當(dāng)滿足條件后，存儲引擎并不會進(jìn)行 b=1 的判斷，而是通過回表拿到整個數(shù)據(jù)行之后再進(jìn)行判斷。

這好像很蠢，就算索引只用到了第一個字段，但明明索引樹中就有 b 字段的數(shù)據(jù)，為什么不直接進(jìn)行判斷呢？

聽上去好像是個 bug，其實在未使用索引下推之前整個查詢邏輯是：由存儲引擎檢索索引樹，就算索引樹中存在 b 字段的值，但由于這條查詢語句的執(zhí)行計劃使用了聯(lián)合索引但沒有用到 b 字段，所以也無法進(jìn)行 b 字段的條件判斷，當(dāng)存儲引擎拿到滿足條件（a 1）的數(shù)據(jù)后，再由 MySQL 服務(wù)器進(jìn)行條件判斷。

在 MySQL5.6 版本中對這樣的情況進(jìn)行優(yōu)化，引入索引下推技術(shù)：在搜索索引樹的過程中，就算沒能用到聯(lián)合索引的其他字段，也能優(yōu)先對查詢條件中包含且索引也包含的字段進(jìn)行判斷，減少回表次數(shù)，提高查詢效率。

在使用索引下推優(yōu)化之后，b 字段作為聯(lián)合索引列，又存在于查詢條件中，同時又沒有在搜索索引樹時被使用到，MySQL 服務(wù)器會把查詢條件中關(guān)于 b 字段的部分也傳給存儲引擎，存儲引擎會在搜索索引樹命中數(shù)據(jù)之后再進(jìn)行 b 字段查詢條件的判斷，滿足的才會加入結(jié)果集。

Ps: 執(zhí)行計劃中 Extra 字段的值包含 Using index condition 就代表使用到了索引下推。

看完了這篇文章，相信你對 MySQL 中索引的案例分析有了一定的了解，想了解更多相關(guān)知識，歡迎關(guān)注丸趣 TV 行業(yè)資訊頻道，感謝各位的閱讀！

向 AI 問一下細(xì)節(jié)

丸趣 TV 網(wǎng) – 提供最優(yōu)質(zhì)的資源集合！