MySQL選錯(cuò)索引的原因以及解決方案

MySQL 中，可以為某張表指定多個(gè)索引，但在語(yǔ)句具體執(zhí)行時(shí)，選用哪個(gè)索引是由 MySQL 中執(zhí)行器確定的。那么執(zhí)行器選擇索引的原則是什么，以及會(huì)不會(huì)出現(xiàn)選錯(cuò)索引的情況呢？

先看這樣一個(gè)例子：

創(chuàng)建表 Y，設(shè)置兩個(gè)普通索引, 創(chuàng)建一個(gè)存儲(chǔ)過程用于插入數(shù)據(jù)。

MySQL: 5.7.27, 隔離級(jí)別: 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();

查看如下事務(wù)：

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

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

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

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

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

優(yōu)化器

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

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

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

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

計(jì)算索引的基數(shù)

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

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

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

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

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

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

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

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

之前看到，執(zhí)行 Select * from Y where a between 10000 and 20000 預(yù)估的行數(shù)是 100015，這個(gè)是能理解的，因?yàn)樽叩氖侨頀呙琛?/p>

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

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

首先先看第二個(gè)問題，選擇 100015 的原因是因?yàn)槿绻褂盟饕?a 的話，除了需要在 a 索引掃描外，還需要回表，主鍵索引上的查詢代價(jià)，優(yōu)化器也需要算進(jìn)去，所以選擇了全表掃描。

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

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

選用錯(cuò)誤索引的解決辦法

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

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

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

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

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

再看一個(gè)類似的例子：

先來看一下這句

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

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

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

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

我們可以將上述語(yǔ)句中 order by b limit 改為 order by b,a limit 1 這時(shí)由于 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)化器認(rèn)為，使用索引 b 的代價(jià)較高，進(jìn)而選擇索引 a. 其實(shí)就是通過 limit 100 誘導(dǎo)優(yōu)化器做出選擇。

調(diào)整索引

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

總結(jié)

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

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

以上就是MySQL選錯(cuò)索引的原因以及解決方案的詳細(xì)內(nèi)容，更多關(guān)于MySQL 索引的資料請(qǐng)關(guān)注億速云其它相關(guān)文章！