MySQL 選錯索引的原因？

以終為始發表於2020-05-21
MySql索引

MySQL 中，可以為某張表指定多個索引，但在語句具體執行時，選用哪個索引是由 MySQL 中執行器確定的。那麼執行器選擇索引的原則是什麼，以及會不會出現選錯索引的情況呢？

先看這樣一個例子：

建立表 Y，設定兩個普通索引, 建立一個儲存過程用於插入資料。

MySQL: 5.7.27, 隔離級別: RR

CREATE TABLE `Y` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `a` (`a`),
  KEY `b` (`b`)
) ENGINE=InnoDB；

delimiter ;;
create procedure idata()
begin
  declare i int;
  set i=1;
  while(i<=100000)do
     insert into Y (`a`,`b`) values(i, i);
    set i=i+1;
  end while;
end;;
delimiter ;
call idata();

檢視如下事務：

Session A Session B
start transaction with consistent snapshot;
delete from t;
call idata();
explain select * from Y where a between 10000 and 20000;
explain select * from Y force index(a) where a between 10000 and 20000;
commit;

如果單獨執行 Session B 中 select * from Y where a between 10000 and 20000;，毫無疑問會選擇 a 這個索引。

但如果安裝 Session A，Session B 的順序執行，發現索引的選擇如下：

可以發現，在 Session B 的場景下，執行器卻沒有選擇 a 所在的索引，而是選擇基於主鍵索引的全表掃描。

set long_query_time=0;
將慢查詢日誌開啟，並將闕值設為 0. 在記錄的日誌中，
可以發現 MySQL 並沒有選擇 a 所在的索引，同時花費了更長的時間。

這樣看，MySQL 的優化器不一定每次都能選擇合適的索引。想要理解出現該現象的原因，就要從優化器的選擇邏輯說起。

優化器

MySQL 中優化器的目的就是找到一個最優的執行方案，從而用最小的代價去執行語句。

優化器在選擇索引時，主要會考慮如下的因素：

  • 掃描的行數：掃描的行數越少，就證明訪問磁碟資料的次數越少，消耗的 CPU 資源就越少。
  • 有沒有涉及到臨時表
  • 排序

關於掃描行數的確定

計算索引的基數

MySQL 在執行語句前，其實並不能準確的計算出掃描的行數，而是通過數學統計資訊來估算記錄數。這個統計資訊被稱為索引的“區分度”，在索引上不同的值越多，區分度就越高。在一個索引上不同值的個數，稱為“基數”。基數越大，索引的區分度越好。

這裡的 Cardinality 就是索引的基數，但基數並不是完全準確的。MySQL 是在獲取基數時，實際上是採用取樣統計的方式。

計算時，會選擇 N 個資料頁，並統計這些頁面上的不同值，得到一個平均值，然後乘以該索引的頁面數，然後得到的就是索引的基數。

在 MySQL 中，有兩種儲存索引的方式，可通過設定 innodb_stats_persistent 來切換：

  • on 時：表示統計資訊會持久化儲存，預設 N 為 20，M 為 10.
  • off 時，統計資訊僅會儲存在記憶體中，預設 N 為 8，M 為 16.

由於表中資料是不斷變化的，所以當更新的值超過 1/M 時，會自動觸發索引統計。

但需要注意的是，由於是取樣統計，所以基數的值不是準確的。

預估掃描行數的錯誤

之前看到，執行 Select * from Y where a between 10000 and 20000 預估的行數是 100015，這個是能理解的，因為走的是全表掃描。

之後執行 select * from Y force index(a) where a between 10000 and 20000 預估的行數是 37116，這個就不能理解了，理想的情況下應該是 10001 行 (需要遍歷到 20001)。

而且更奇怪的是，雖然 37116 行的預估行數不太合理，但也遠小於全表掃描的 100015，為什麼優化器還是選擇全表掃描呢？

首先先看第二個問題，選擇 100015 的原因是因為如果使用索引 a 的話，除了需要在 a 索引掃描外，還需要回表，主鍵索引上的查詢代價，優化器也需要算進去，所以選擇了全表掃描。

這時再看第一個問題，為什麼沒有得到正確的行數。這個就和一致性檢視有關了，首先 Session A 中，開啟了一致性檢視，並沒有提交。之後的 Session 清空了 Y 表後，又重新建立了相同的資料，這時每行資料都有兩個版本，舊版本是 delete 前的資料，新版本是標記為刪除的資料。所以索引 a 上的資料其實有兩份。也就造成了行數的預估錯誤。

mysql 是通過標記刪除的方法來刪除記錄的，並不是在索引和資料檔案中真正的刪除。而且由於一致性讀的保證，不能刪除 delete 的空間，再加上 insert 的空間。導致統計資訊有誤。

選用錯誤索引的解決辦法

對於行數預估錯誤的情況， 可採用如下的方法：

  • 如果遇到 EXPLAIN 和預估的行數，數值相差較大時，可以通過analyze table 來重新統計索引資訊。

  • 直接通過 force index 強制指定需要使用的索引，不讓優化器進行判斷。但使用 force 也可能帶來一些問題：
    • 遷移資料庫時，語法不支援
    • 不容易變更並且不太方便，因為選錯索引的情況一般不會經常發生，在生產環境出現問題後，才需要改程式碼，但還需要重新進行上線測試，部署。

優化 SQL 語句，引導優化器使用正確的索引

再看一個類似的例子：

先來看一下這句 SQL select * from Y where a between 1 and 1000 and b between5000 100000 order by b limit 1;

在執行這句話時，可以選索引 a，也可以選索引 b. 我們知道，每個索引對應了一顆B+樹。這裡由於取得是 a 和 b 的交集，如果選用索引 a 的話，需要遍歷 1 - 10001 行。選用索引 b 需要遍歷 50000 - 100001 行。理論上來說，應該選擇 a 作為索引，可以優化器又偏偏選擇了 b 作為索引。

這裡選擇 b 作為索引的原因，是因為優化器看到了後面的 order by 語句，由於要排序，而 B+ 樹本身就是有序的，省去了排序的過程，所以選擇了 b 作為索引。

但從實際的執行時間來看，索引 a 執行時間更短，所以這裡 MySQL 又選擇了錯誤的索引。

我們可以將上述語句中 order by b limit 改為 order by b,a limit 1 這時由於 a，b 索引都要排序，掃描的行數就成為執行器主要參考的條件，引導選擇正確的索引。

這樣做的前提一定要保證執行的邏輯結果是一致的，比如在 limit 1 的情況下，order by b,a 和 order by b 的結果一致，如果換成 limit 100 就不一定了。

還有一種改法，select * from (select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 100)alias limit 1;

現在可以看到，優化器選擇了合適的索引。原因在於 limit 100 讓優化器認為，使用索引 b 的代價較高，進而選擇索引 a. 其實就是通過 limit 100 誘導優化器做出選擇。

調整索引

能否找到更優，更合適的索引，或者利用索引的原則，刪除一些不必要的索引。

總結

現在我們知道，MySQL 在選擇索引時，是會出現錯誤的情況的。優化器選擇索引的原則主要有三個，掃描的行數，是否存在臨時表，以及排序。行數的掃描，主要和基數有關，而基數的統計則是通過統計抽樣決定的，進而預估的行數可能會是不準確的。

在遇到掃描的行數不正確時，可以通過 analyze table 來重新統計表的資訊，通過 force index 強制指定索引，或通過手動改變 sql 的語義，誘導優化器做出正確的選擇。

相關文章