技術分享 | 常見索引問題處理

• 2020 年 3 月 26 日
• 筆記

作者：EneTakane

資料庫技術愛好者，愛可生 DBA 團隊成員，負責 MySQL 日常問題處理以及資料庫運維平台的問題排查，擅長 MySQL 主從複製及優化，喜歡鑽研技術問題，還有不得不提的 warship。

本文來源：原創投稿

*愛可生開源社區出品，原創內容未經授權不得隨意使用，轉載請聯繫小編並註明來源。

在滿足語句需求的情況下，盡量少的訪問資源是資料庫設計的重要原則，這和執行的 SQL 有直接的關係，索引問題又是 SQL 問題中出現頻率最高的，常見的索引問題包括：無索引（失效）、隱式轉換。

1. SQL 執行流程

看一個問題，在下面這個表 T 中，如果我要執行 select * from T where k between 3 and 5; 需要執行幾次樹的搜索操作，會掃描多少行？

mysql> create table T (        -> ID int primary key,        -> k int NOT NULL DEFAULT 0,        -> s varchar(16) NOT NULL DEFAULT '',        -> index k(k))        -> engine=InnoDB;    mysql> insert into T values(100,1, 'aa'),(200,2,'bb'),          (300,3,'cc'),(500,5,'ee'),(600,6,'ff'),(700,7,'gg');

這分別是 ID 欄位索引樹、k 欄位索引樹。

這條 SQL 語句的執行流程：

1. 在 k 索引樹上找到 k=3，獲得 ID=300

2. 回表到 ID 索引樹查找 ID=300 的記錄，對應 R3

3. 在 k 索引樹找到下一個值 k=5，ID=500

4. 再回到 ID 索引樹找到對應 ID=500 的 R4

5. 在 k 索引樹去下一個值 k=6，不符合條件，循環結束

這個過程讀取了 k 索引樹的三條記錄，回表了兩次。

因為查詢結果所需要的數據只在主鍵索引上有，所以必須得回表。所以，我們該如何通過優化索引，來避免回表呢？

2. 常見索引優化

2.1 覆蓋索引

覆蓋索引，換言之就是索引要覆蓋我們的查詢請求，無需回表。

如果執行的語句是 select ID from T wherek between 3 and 5;，這樣的話因為 ID 的值在 k 索引樹上，就不需要回表了。

覆蓋索引可以減少樹的搜索次數，顯著提升查詢性能，是常用的性能優化手段。

但是，維護索引是有代價的，所以在建立冗餘索引來支援覆蓋索引時要權衡利弊。

2.2 最左前綴原則

B+ 樹的數據項是複合的數據結構，比如 (name,sex，age) 的時候，B+ 樹是按照從左到右的順序來建立搜索樹的，當 (張三,F,26) 這樣的數據來檢索的時候，B+ 樹會優先比較 name 來確定下一步的檢索方向，如果 name 相同再依次比較 sex 和 age，最後得到檢索的數據。

# 有這樣一個表 P    mysql> create table P (id int primary key, name varchar(10) not null, sex varchar(1), age int, index tl(name,sex,age)) engine=IInnoDB;    mysql> insert into P values(1,'張三','F',26),(2,'張三','M',27),(3,'李四','F',28),(4,'烏茲','F',22),(5,'張三','M',21),(6,'王五','M',28);      # 下面的語句結果相同    mysql> select * from P where name='張三' and sex='F';     ## A1    mysql> select * from P where sex='F' and age=26;         ## A2    # explain 看一下      mysql> explain select * from P where name='張三' and sex='F';    +----+-------------+-------+------------+------+---------------+------+---------+-------------+------+----------+-------------+    | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref         | rows | filtered | Extra       |    +----+-------------+-------+------------+------+---------------+------+---------+-------------+------+----------+-------------+    |  1 | SIMPLE      | P     | NULL       | ref  | tl            | tl   | 38      | const,const |    1 |   100.00 | Using index |    +----+-------------+-------+------------+------+---------------+------+---------+-------------+------+----------+-------------+      mysql> explain select * from P where sex='F' and age=26;    +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+    | id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                    |    +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+    |  1 | SIMPLE      | P     | NULL       | index | NULL          | tl   | 43      | NULL |    6 |    16.67 | Using where; Using index |    +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+   

可以清楚的看到，A1 使用 tl 索引，A2 進行了全表掃描，雖然 A2 的兩個條件都在 tl 索引中出現，但是沒有使用到 name 列，不符合最左前綴原則，無法使用索引。

所以在建立聯合索引的時候，如何安排索引內的欄位排序是關鍵。評估標準是索引的復用能力，因為支援最左前綴，所以當建立（a，b）這個聯合索引之後，就不需要給 a 單獨建立索引。

原則上，如果通過調整順序，可以少維護一個索引，那麼這個順序往往就是需要優先考慮採用的。

上面這個例子中，如果查詢條件里只有 b，就是沒法利用（a，b）這個聯合索引的，這時候就不得不維護另一個索引，也就是說要同時維護（a，b）、（b）兩個索引。這樣的話，就需要考慮空間佔用了，比如，name 和 age 的聯合索引，name 欄位比 age 欄位佔用空間大，所以創建（name，age）聯合索引和（age）索引佔用空間是要小於（age，name）、(name）索引的。

2.3 索引下推

以人員表的聯合索引（name, age）為例。如果現在有一個需求：檢索出表中「名字第一個字是張，而且年齡是26歲的所有男性」。那麼，SQL 語句是這麼寫的

mysql> select * from tuser where name like '張%' and age=26 and sex=M;

通過最左前綴索引規則，會找到 ID1，然後需要判斷其他條件是否滿足

在 MySQL 5.6 之前，只能從 ID1 開始一個個回表。到主鍵索引上找出數據行，再對比欄位值。

而 MySQL 5.6 引入的索引下推優化(index condition pushdown)，可以在索引遍歷過程中，對索引中包含的欄位先做判斷，直接過濾掉不滿足條件的記錄，減少回表次數。

這樣，減少了回表次數和之後再次過濾的工作量，明顯提高檢索速度。

2.4 隱式類型轉化

隱式類型轉化主要原因是，表結構中指定的數據類型與傳入的數據類型不同，導致索引無法使用。

所以有兩種方案：

• 修改表結構，修改欄位數據類型。
• 修改應用，將應用中傳入的字元類型改為與表結構相同類型。

3. 為什麼會選錯索引

3.1 優化器

選擇索引是優化器的工作，其目的是找到一個最優的執行方案，用最小的代價去執行語句。

在資料庫中，掃描行數是影響執行代價的因素之一。掃描的行數越少，意味著訪問磁碟數據的次數越少，消耗的 CPU 資源越少。當然，掃描行數並不是唯一的判斷標準，優化器還會結合是否使用臨時表、是否排序等因素進行綜合判斷。

3.2 掃描行數

MySQL 在真正開始執行語句之前，並不能精確的知道滿足這個條件的記錄有多少條，只能通過索引的區分度來判斷。顯然，一個索引上不同的值越多，索引的區分度就越好，而一個索引上不同值的個數我們稱為「基數」，也就是說，這個基數越大，索引的區分度越好。

# 通過 show index 方法，查看索引的基數    mysql> show index from t;    +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+    | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |    +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+    | t     |          0 | PRIMARY  |            1 | id          | A         |       95636 |     NULL | NULL   |      | BTREE      |         |               |    | t     |          1 | a        |            1 | a           | A         |       96436 |     NULL | NULL   | YES  | BTREE      |         |               |    | t     |          1 | b        |            1 | b           | A         |       96436 |     NULL | NULL   | YES  | BTREE      |         |               |    +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

MySQL 使用取樣統計方法來估算基數：

取樣統計的時候，InnoDB 默認會選擇 N 個數據頁，統計這些頁面上的不同值，得到一個平均值，然後乘以這個索引的頁面數，就得到了這個索引的基數。

而數據表是會持續更新的，索引統計資訊也不會固定不變。所以，當變更的數據行數超過 1/M 的時候，會自動觸發重新做一次索引統計。

在 MySQL 中，有兩種存儲索引統計的方式，可以通過設置參數 innodb_stats_persistent 的值來選擇：

• on 表示統計資訊會持久化存儲。默認 N = 20，M = 10。
• off 表示統計資訊只存儲在記憶體中。默認 N = 8，M = 16。

由於是取樣統計，所以不管 N 是 20 還是 8，這個基數都很容易不準確。

所以，冤有頭債有主，MySQL 選錯索引，還得歸咎到沒能準確地判斷出掃描行數。

可以用 analyze table 來重新統計索引資訊，進行修正。

ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...

3.3 索引選擇異常和處理

1. 採用 force index 強行選擇一個索引。

2. 可以考慮修改語句，引導 MySQL 使用我們期望的索引。

3. 有些場景下，可以新建一個更合適的索引，來提供給優化器做選擇，或刪掉誤用的索引。