怎么用好MySQL索引

本篇內容主要講解“怎么用好MySQL索引”，感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷，實用性強。下面就讓小編來帶大家學習“怎么用好MySQL索引”吧!

為了更好地進行解釋，我創建了一個存儲引擎為InnoDB的表user_innodb，并批量初始化了500W+條數據。包含主鍵id、姓名字段（name）、性別字段（gender，用0，1表示不同性別）、手機號字段（phone），并為name和phone字段創建了聯合索引。

CREATE TABLE `user_innodb` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `gender` tinyint(1) DEFAULT NULL,
  `phone` varchar(11) DEFAULT NULL,
  PRIMARY KEY (`id`)，
  INDEX IDX_NAME_PHONE (name, phone)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

1. 索引的代價

索引可以非常有效地提升查詢效率，既然這么好，我給每個字段都創建一個索引行不行？我勸你不要沖動。

任何事情都有兩面，索引也不例外。過度使用索引，我們在空間和時間上都會付出相應的代價。

1.1 空間上的代價

索引就是一棵B+數，每創建一個索引都需要創建一棵B+樹，每一棵B+樹的節點都是一個數據頁，每一個數據頁默認會占用16KB的磁盤空間，每一棵B+樹又會包含許許多多的數據頁。所以，大量創建索引，你的磁盤空間會被迅速消耗。

1.2 時間上的代價

空間上的代價你可以使用“鈔能力”來解決，但時間上的代價我們可能就束手無策了。

鏈表的維護

我以主鍵索引為例舉個例子，主鍵索引的B+樹的每一個節點內的記錄都是按照主鍵值由小到大的順序，采用單向鏈表的方式進行連接的。如下圖所示：

如果我現在要刪除主鍵id為1的記錄，會破壞3個數據頁內的記錄排序，需要對這3個數據頁內的記錄進行重排列，插入和修改操作也是同理。

注：這里給大家提一嘴，其實刪除操作并不會立即進行數據頁內記錄的重排列，而是會給被刪除的記錄打上一個刪除的標識，等到合適的時候，再把記錄從鏈表中移除，但是總歸需要涉及到排序的維護，勢必要消耗性能。

假如這張表有12個字段，我們為這張表的12個字段都設置了索引，我們刪除1條記錄，需要涉及到12棵B+樹的N個數據頁內記錄的排序維護。

更糟糕的是，你增刪改記錄的時候，還可能會觸發數據頁的回收和分裂。還是以上圖為例，假如我刪除了id為13的記錄，那么數據頁124就沒有存在的必要了，會被InnoDB存儲引擎回收；我插入一條id為12的記錄，如果數據頁32的空間不足以存儲該記錄，InnoDB又需要進行頁面分裂。我們不需要知道頁面回收和頁面分裂的細節，但是能夠想象到這個操作會有多復雜。

如果每個字段都創建索引，所有這些索引的維護操作帶來的性能損耗，你能想象了吧。

查詢計劃

執行查詢語句之前，MySQL查詢優化器會基于cost成本對一條查詢語句進行優化，并生成一個執行計劃。如果創建的索引太多，優化器會計算每個索引的搜索成本，導致在分析過程中耗時太多，最終影響查詢語句的執行效率。

2. 回表的代價

2.1 什么是回表

我再啰嗦一遍什么是回表，我們可以通過二級索引找到B+樹中的葉子結點，但是二級索引的葉子節點的內容并不全，只有索引列的值和主鍵值。我們需要拿著主鍵值再去聚簇索引（主鍵索引）的葉子節點中去拿到完整的用戶記錄，這個過程叫做回表。

上圖中我以name二級索引為例，并且只畫出了二級索引的葉子節點和聚簇索引的葉子節點，省略了兩棵B+樹的非葉子節點。

從二級索引的葉子節點延伸出的3條線表示的就是回表操作。

2.2 回表的代價

我們根據name字段查找二級索引的葉子節點的代價還是比較小的，原因有二：

• 葉子節點所在的頁通過雙向鏈表進行關聯，遍歷的速度比較快；
• MySQL會盡量讓同一個索引的葉子節點的數據頁在磁盤空間中相鄰，盡力避免隨機IO。

但是二級索引葉子節點中的主鍵id的排布就沒有任何規律了，畢竟name索引是對name字段進行排序的。進行回表的時候，極有可能出現主鍵id所在的記錄在聚簇索引葉子節點中反復橫跳的情況（正如上圖中回表的3條線表示的那樣），也就是隨機IO。如果目標數據頁恰好在內存中的話效果倒也不會太差，但如果不在內存中，還要從磁盤中加載一個數據頁的內容（16KB）到內存中，這個速度可就太慢了。

是不是說完了回表的代價之后，我會給出一種更高效的搜索方式？不是，回表已經是一種比較高效的搜索方式了，我們需要做的就是盡量地減少回表操作帶來的損耗，總結起來就是兩點：

• 能不回表就不回；
• 必須回表就減少回表的次數。

接下來先給大家介紹兩個與回表相關的重要概念，這兩個概念涉及到的方法也是索引使用原則的一部分，因為比較重要，在這里我把這兩個概念先解釋給大家聽。

3. 索引覆蓋、索引下推

3.1 索引覆蓋

想一下，如果非聚簇索引的葉子節點上有你想要的所有數據，是不是就不需要回表了呢？比如我為name和phone字段創建了一個聯合索引，如下圖：

如果我們恰好只想搜索name、phone以及主鍵字段，

SELECT id, name,  phone FROM user_innodb WHERE name = "蟬沐風";

可以直接從葉子節點獲取所有數據，根本不需要回表操作。

我們把索引中已經包含了所有需要讀取的列數據的查詢方式稱為覆蓋索引（或索引覆蓋）。

3.2 索引下推

3.2.1 概念

還是拿name和phone的聯合索引為例，我們要查詢所有name為「蟬沐風」，并且手機尾號為6606的記錄，查詢SQL如下：

SELECT * FROM user_innodb WHERE name = "蟬沐風" AND phone LIKE "%6606";

由于聯合索引的葉子節點的記錄是先按照name字段排序，name字段相同的情況下再按照phone字段排序，因此把%加在phone字段前面的時候，是無法利用索引的順序性來進行快速比較的，也就是說這條查詢語句中只有name字段可以使用索引進行快速比較和過濾。正常情況下查詢過程是這個樣子的：

• InnoDB使用聯合索引查出所有name為蟬沐風的二級索引數據，得到3個主鍵值：3485，78921，423476；

• 拿到主鍵索引進行回表，到聚簇索引中拿到這三條完整的用戶記錄；

• InnoDB把這3條完整的用戶記錄返回給MySQL的Server層，在Server層過濾出尾號為6606的用戶。

如下面兩幅圖所示，第一幅圖表示InnoDB通過3次回表拿到3條完整的用戶記錄，交給Server層；第二幅圖表示Server層經過phone LIKE "%6606"條件的過濾之后找到符合搜索條件的記錄，返給客戶端。

值得我們關注的是，索引的使用是在存儲引擎中進行的，而數據記錄的比較是在Server層中進行的。現在我們把上述搜索考慮地極端一點，假如數據表中10萬條記錄都符合name='蟬沐風'的條件，而只有1條符合phone LIKE "%6606"條件，這就意味著，InnoDB需要將99999條無效的記錄傳輸給Server層讓其自己篩選，更嚴重的是，這99999條數據都是通過回表搜索出來的啊！關于回表的代價你已經知道了。

現在引入索引下推。準確來說，應該叫做索引條件下推（Index Condition Pushdown，ICP），就是過濾的動作由下層的存儲引擎層通過使用索引來完成，而不需要上推到Server層進行處理。ICP是在MySQL5.6之后完善的功能。

再回顧一下，我們第一步已經通過name = "蟬沐風"在聯合索引的葉子節點中找到了符合條件的3條記錄，而且phone字段也恰好在聯合索引的葉子節點的記錄中。這個時候可以直接在聯合索引的葉子節點中進行遍歷，篩選出尾號為6606的記錄，找到主鍵值為78921的記錄，最后只需要進行1次回表操作即可找到符合全部條件的1條記錄，返回給Server層。

很明顯，使用ICP的方式能有效減少回表的次數。

另外，ICP是默認開啟的，對于二級索引，只要能把條件甩給下面的存儲引擎，存儲引擎就會進行過濾，不需要我們干預。

3.2.2 演示

查看一下當前ICP的狀態：

SHOW VARIABLES LIKE 'optimizer_switch';

執行以下SQL語句，并用EXPLAIN查看一下執行計劃，此時的執行計劃是Using index condition

EXPLAIN SELECT * FROM user_innodb WHERE name = "蟬沐風" AND phone LIKE "%6606";

然后關閉ICP

SET optimizer_switch="index_condition_pushdown=off";

再查看一下ICP的狀態

再次執行查詢語句，并用EXPLAIN查看一下執行計劃，此時的執行計劃是Using where

EXPLAIN SELECT * FROM user_innodb WHERE name = "蟬沐風" AND phone LIKE "%6606";

注：即使滿足索引下推的使用條件，查詢優化器也未必會使用索引下推，因為可能存在更高效的方式。

由于之前我給name字段創建了索引，導致一直沒有使用索引下推，EXPLAIN語句顯示使用了name索引，而不是name和phone的聯合索引；刪除name索引之后，才獲得上述截圖的效果。大家做實驗的時候需要注意。

到目前為止大家應該清楚了索引和回表帶來的性能問題，講這些自然不是為了恐嚇大家讓大家遠離索引，相反，我們要以正確的方式積極擁抱索引，最大限度降低其帶來的負面影響，放大其優勢。如何用好索引，從兩個方面考慮：

• 高效發揮已經創建的索引的作用（避免索引失效）
• 為合適的列創建合適的索引（索引創建原則）

4. 什么時候索引會失效？

4.1 違反最左前綴原則

拿我們文章開始創建的聯合索引為例，該聯合索引的B+樹數據頁內的記錄首先按照name字段進行排序，name字段相同的情況下，再按照phone字段進行排序。

所以，如果我們直接使用phone字段進行搜索，無法利用索引的順序性。

EXPLAIN SELECT * FROM user_innodb WHERE phone = "13203398311";

EXPLAIN可以查看搜索語句的執行計劃，其中，possible_keys列表示在當前查詢中，可能用到的索引有哪一些；key列表示實際用到的索引有哪一些。

但是一旦加上name的搜索條件，就會使用到聯合索引，而且不需要在意name在WHERE子句中的位置，因為查詢優化器會幫我們優化。

EXPLAIN SELECT * FROM user_innodb WHERE phone = "13203398311" AND name = '蟬沐風';

4.2 使用反向查詢（!=, <>,NOT LIKE）

MySQL在使用反向查詢（!=, <>, NOT LIKE）的時候無法使用索引，會導致全表掃描，覆蓋索引除外。

EXPLAIN SELECT * FROM user_innodb WHERE name != '蟬沐風';

4.3 LIKE以通配符開頭

當使用name LIKE '%沐風'或者name LIKE '%沐%'這兩種方式都會使索引失效，因為聯合索引的B+樹數據頁內的記錄首先按照name字段進行排序，這兩種搜索方式不在意name字段的開頭是什么，自然就無法使用索引，只能通過全表掃描的方式進行查詢。

EXPLAIN SELECT * FROM user_innodb WHERE name LIKE '%沐風';

但是使用通配符結尾就沒有問題

EXPLAIN SELECT * FROM user_innodb WHERE name LIKE '蟬沐%';

4.4 對索引列做任何操作

如果不是單純使用索引列，而是對索引列做了其他操作，例如數值計算、使用函數、（手動或自動）類型轉換等操作，會導致索引失效。

4.4.1 使用函數

EXPLAIN SELECT * FROM user_innodb WHERE LEFT(name,3) = '蟬沐風';

MySQL8.0新增了函數索引的功能，我們可以給函數作用之后的結果創建索引，使用以下語句

ALTER TABLE user_innodb ADD KEY IDX_NAME_LEFT ((left(name,3)));

再次執行EXPLAIN語句，此時索引生效

4.4.2 使用表達式

EXPLAIN SELECT * FROM user_innodb WHERE id + 1 = 1100000;

換一種方式，單獨使用id，就能高效使用索引：

EXPLAIN SELECT * FROM user_innodb WHERE id = 1100000 - 1;

4.4.3 使用類型轉換

例1

user_innodb中的phone字段為varchar類型，實驗之前我們先給phone字段創建個索引

ALTER TABLE user_innodb ADD INDEX IDX_PHONE (phone);

隨便搜索一個存在的手機號，看一下索引是否成功

EXPLAIN SELECT * FROM user_innodb WHERE phone = '13203398311';

可以看到能使用到索引，現在我們稍微修改一下，把phone = '13203398311'修改為phone = 13203398311，這意味著我們將字符串的搜索條件改成了整形的搜索條件，再看一下還會不會使用到索引：

EXPLAIN SELECT * FROM user_innodb WHERE phone = 13203398311;

顯示索引失效。

例2

我們再看一個例子，主鍵id類型是bigint，但是在搜索條件中我估計使用字符串類型：

EXPLAIN SELECT * FROM user_innodb WHERE id = '1099999';

總結

稍微總結一下這個問題，當索引字段類型為字符串時，使用數字類型進行搜索不會用到索引；而索引字段類型為數字類型時，使用字符串類型進行搜索會使用到索引。

要搞明白這個問題，我們需要知道MySQL的數據類型轉換規則是什么。簡單地說就是MySQL會自動將數字轉化為字符串，還是將字符串轉化為數字。

一個簡單的方法是，通過SELECT '10' > 9的結果來確定MySQL的類型轉換規則：

• 結果為1，說明MySQL會自動將字符串類型轉化為數字，相當于執行了SELECT 10 > 9;
• 結果為0，說明MySQL會自動將數字轉化為字符串，相當于執行了SELECT '10' > '9'。

mysql> SELECT '10' > 9;
+----------+
| '10' > 9 |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

上面的執行結果為1，說明MySQL遇到類型轉換時，會自動將字符串轉換為數字類型，因此對于例1:

EXPLAIN SELECT * FROM user_innodb WHERE phone = 13203398311;

就相當于

EXPLAIN SELECT * FROM user_innodb WHERE CAST(phone AS signed int) = 13203398311;

也就是對索引字段使用了函數，按照前文的介紹，對索引使用函數是不會使用到索引的。

對于例2:

EXPLAIN SELECT * FROM user_innodb WHERE id = '1099999';

就相當于

EXPLAIN SELECT * FROM user_innodb WHERE id = CAST('1099999' AS unsigned int);

沒有在索引字段添加任何操作，因此能夠使用到索引。

4.5 OR連接

使用OR連接的查詢語句，如果OR之前的條件列是索引列，但是OR之后的條件列不是索引列，則不會使用索引。舉例：

EXPLAIN SELECT * FROM user_innodb WHERE id = 1099999 OR gender = 0;

上面總結了一些索引失效的場景，這些經驗的總結往往對SQL的優化很有益處，但同時需要注意的是這些經驗并非金科玉律。

比如使用<>查詢時，在某些時候是可以用到索引的：

EXPLAIN SELECT * FROM user_innodb WHERE id <> 1099999;

最終是否使用索引，完全取決于MySQL的優化器，而優化器的判定依據就是cost開銷（Cost Base Optimizer），優化器并非基于具體的規則，也不是基于語義，就是單純地執行開銷小的方案罷了。所以在·EXPLAIN·的結果中你會看到possible_keys一列，優化器會把這里邊的索引都試一遍（是不是又加深了對不能隨便創建索引的認識呢？），然后選一個開銷最小的，如果都不太行，那就直接全表掃描好了。

而cost開銷，和數據庫版本、數據量等都有關系，因此如果想更精準地提升索引功能性，擁抱EXPLAIN吧！

5. 索引創建（使用）原則

之前講過的索引覆蓋和索引下推都可以作為索引創建的原則，就是在創建索引的時候，盡量發揮索引覆蓋和索引下推的優勢。

盡量避免上述提及到的索引可能失效的情況的出現，同樣是索引的使用原則。

除此之外，再給大家介紹一些。

5.1 不為離散度低的列創建索引

先來看一下列的離散度公式：COUNT(DISTINCT(column_name)) / COUNT(*)，列的不重復值的個數與所有數據行的比例。簡而言之，如果列的重復值越多，列的離散度越低。重復值越少，離散度就越高。

舉個例子，gender（性別）列只有0、1兩個值，列的離散度非常低，假如我們為該列創建索引，我們會在二級索引中搜索到大量的重復數據，然后進行大量回表操作。大量回表哈？你懂了吧。

不要為重復值多的列創建索引

5.2 只為用于搜索、排序或分組的列創建索引

我們只為出現在WHERE子句中的列或者出現在ORDER BY和GROUP BY子句中的列創建索引即可。僅出現在查詢列表中的列不需要創建索引。

5.3 用好聯合索引

用2條SQL語句來說明這個問題：

1. SELECT * FROM user_innodb WHERE name = '蟬沐風' AND phone = '13203398311';

2. SELECT * FROM user_innodb WHERE name = '蟬沐風';

語句1和語句2都能夠使用索引，這帶給我們的一個索引設計原則就是：

不要為聯合索引的第一個索引列單獨創建索引

因為聯合索引本身就是先按照name列進行排序，因此聯合索引對name的搜索是有效的，不需要單獨為name再創建索引了。也正因為此

建立聯合索引的時候，一定要把最常用的列放在最左邊

5.4 對過長的字段，建立前綴索引

如果一個字符串格式的列占用的空間比較大（就是說允許存儲比較長的字符串數據），為該列創建索引，就意味著該列的數據會被完整地記錄在每個數據頁的每條記錄中，會占用相當大的存儲空間。

對此，我們可以為該列的前幾個字符創建索引，也就是在二級索引的記錄中只會保留字符串的前幾個字符。比如我們可以為phone列創建索引，索引只保留手機號的前3位：

ALTER TABLE user_innodb ADD INDEX IDX_PHONE_3 (phone(3));

然后執行下面的SQL語句：

EXPLAIN SELECT * FROM user_innodb WHERE phone = '1320';

由于在IDX_PHONE_3索引中只保留了手機號的前3位數字，所以我們只能定位到以132開頭的二級索引記錄，然后在遍歷所有的這些二級索引記錄時再判斷它們是否滿足第4位數為0的條件。

當列中存儲的字符串包含的字符較多時，為該字段建立前綴索引可以有效節省磁盤空間

5.5 頻繁更新的值，不要作為主鍵或索引

因為可能涉及到數據頁分裂的情況，會影響性能。

5.6 隨機無序的值，不建議作為索引，例如身份證、UUID

到此，相信大家對“怎么用好MySQL索引”有了更深的了解，不妨來實際操作一番吧！這里是億速云網站，更多相關內容可以進入相關頻道進行查詢，關注我們，繼續學習！