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 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 的語義，誘導優化器做出正確的選擇。

以上就是MySQL選錯索引的原因以及解決方案的詳細內容，更多關于MySQL 索引的資料請關注腳本之家其它相關文章！