MySQL選錯索引的原因以及解決方案

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();

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

但如果安裝 Session A，Session B 的順序執(zhí)行，發(fā)現(xiàn)索引的選擇如下：

可以發(fā)現(xiàn)，在 Session B 的場景下，執(zhí)行器卻沒有選擇 a 所在的索引，而是選擇基于主鍵索引的全表掃描。

set long_query_time=0;
--將慢查詢?nèi)罩敬蜷_，并將闕值設(shè)為 0. 在記錄的日志中，可以發(fā)現(xiàn) MySQL 并沒有選擇 a 所在的索引，同時花費了更長的時間。

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

優(yōu)化器

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

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

• 掃描的行數(shù)：掃描的行數(shù)越少，就證明訪問磁盤數(shù)據(jù)的次數(shù)越少，消耗的 CPU 資源就越少。
• 有沒有涉及到臨時表
• 排序

關(guān)于掃描行數(shù)的確定

計算索引的基數(shù)

MySQL 在執(zhí)行語句前，其實并不能準(zhǔn)確的計算出掃描的行數(shù)，而是通過數(shù)學(xué)統(tǒng)計信息來估算記錄數(shù)。這個統(tǒng)計信息被稱為索引的“區(qū)分度”，在索引上不同的值越多，區(qū)分度就越高。在一個索引上不同值的個數(shù)，稱為“基數(shù)”?；鶖?shù)越大，索引的區(qū)分度越好。

這里的 Cardinality 就是索引的基數(shù)，但基數(shù)并不是完全準(zhǔn)確的。MySQL 是在獲取基數(shù)時，實際上是采用采樣統(tǒng)計的方式。

計算時，會選擇 N 個數(shù)據(jù)頁，并統(tǒng)計這些頁面上的不同值，得到一個平均值，然后乘以該索引的頁面數(shù)，然后得到的就是索引的基數(shù)。

在 MySQL 中，有兩種存儲索引的方式，可通過設(shè)置 innodb_stats_persistent 來切換：

• on 時：表示統(tǒng)計信息會持久化存儲，默認 N 為 20，M 為 10.
• off 時，統(tǒng)計信息僅會存儲在內(nèi)存中，默認 N 為 8，M 為 16.
  

由于表中數(shù)據(jù)是不斷變化的，所以當(dāng)更新的值超過 1/M 時，會自動觸發(fā)索引統(tǒng)計。

但需要注意的是，由于是采樣統(tǒng)計，所以基數(shù)的值不是準(zhǔn)確的。

預(yù)估掃描行數(shù)的錯誤

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

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

而且更奇怪的是，雖然 37116 行的預(yù)估行數(shù)不太合理，但也遠小于全表掃描的 100015，為什么優(yōu)化器還是選擇全表掃描呢？

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

這時再看第一個問題，為什么沒有得到正確的行數(shù)。這個就和一致性視圖有關(guān)了，首先 Session A 中，開啟了一致性視圖，并沒有提交。之后的 Session 清空了 Y 表后，又重新創(chuàng)建了相同的數(shù)據(jù)，這時每行數(shù)據(jù)都有兩個版本，舊版本是 delete 前的數(shù)據(jù)，新版本是標(biāo)記為刪除的數(shù)據(jù)。所以索引 a 上的數(shù)據(jù)其實有兩份。也就造成了行數(shù)的預(yù)估錯誤。

mysql 是通過標(biāo)記刪除的方法來刪除記錄的，并不是在索引和數(shù)據(jù)文件中真正的刪除。而且由于一致性讀的保證，不能刪除 delete 的空間，再加上 insert 的空間。導(dǎo)致統(tǒng)計信息有誤。

選用錯誤索引的解決辦法

對于行數(shù)預(yù)估錯誤的情況， 可采用如下的方法：

如果遇到 EXPLAIN 和預(yù)估的行數(shù)，數(shù)值相差較大時，可以通過analyze table 來重新統(tǒng)計索引信息。

直接通過 force index 強制指定需要使用的索引，不讓優(yōu)化器進行判斷。但使用 force 也可能帶來一些問題：

• 遷移數(shù)據(jù)庫時，語法不支持
• 不容易變更并且不太方便，因為選錯索引的情況一般不會經(jīng)常發(fā)生，在生產(chǎn)環(huán)境出現(xiàn)問題后，才需要改代碼，但還需要重新進行上線測試，部署。

優(yōu)化 SQL 語句，引導(dǎo)優(yōu)化器使用正確的索引

再看一個類似的例子：

先來看一下這句

SQL select * from Y where a between 1 and 1000 and b between5000 100000 order by b limit 1;

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

這里選擇 b 作為索引的原因，是因為優(yōu)化器看到了后面的 order by 語句，由于要排序，而 B+ 樹本身就是有序的，省去了排序的過程，所以選擇了 b 作為索引。

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

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

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

還有一種改發(fā)

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;

現(xiàn)在可以看到，優(yōu)化器選擇了合適的索引。原因在于 limit 100 讓優(yōu)化器認為，使用索引 b 的代價較高，進而選擇索引 a. 其實就是通過 limit 100 誘導(dǎo)優(yōu)化器做出選擇。

調(diào)整索引

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

總結(jié)

現(xiàn)在我們知道，MySQL 在選擇索引時，是會出現(xiàn)錯誤的情況的。優(yōu)化器選擇索引的原則主要有三個，掃描的行數(shù)，是否存在臨時表，以及排序。行數(shù)的掃描，主要和基數(shù)有關(guān)，而基數(shù)的統(tǒng)計則是通過統(tǒng)計抽樣決定的，進而預(yù)估的行數(shù)可能會是不準(zhǔn)確的。

在遇到掃描的行數(shù)不正確時，可以通過 analyze table 來重新統(tǒng)計表的信息，通過 force index 強制指定索引，或通過手動改變 sql 的語義，誘導(dǎo)優(yōu)化器做出正確的選擇。

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