2. mysql 為什么會(huì)選錯(cuò)索引

上節(jié)我們講了mysql的整體架構(gòu)，知道了優(yōu)化器的作用是優(yōu)化sql，選擇索引，生成執(zhí)行計(jì)劃。索引是優(yōu)化器階段自己選擇的，優(yōu)化器大部分情況下索引的選擇都是比較合理的，但是也有特殊情況下，優(yōu)化器選擇的索引并不是最優(yōu)的。下面我們通過(guò)實(shí)驗(yàn)來(lái)說(shuō)明這個(gè)問(wèn)題，并給出優(yōu)化方案（需要說(shuō)明的是本實(shí)驗(yàn)的環(huán)境是mysql版本是5.7,事務(wù)隔離級(jí)別是RR,事務(wù)自動(dòng)提交）。

首先我們來(lái)建一個(gè)表，表里面有id、a、b三個(gè)字段，下面是建表語(yǔ)句

CREATE TABLE `t` (
  `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；

然后我們向表t里面插入10萬(wàn)行記錄，記錄是遞增的 (1,1,1)， (2,2,2)， (3,3,3)......(100000,100000,100000)，插入語(yǔ)句如下：


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

這里要注意的是我的mysql默認(rèn)是自動(dòng)提交，且sync_binlog=1,innodb_flush_log_at_trx_commit=1，所以說(shuō)上面的存儲(chǔ)過(guò)程每次插入數(shù)據(jù)，在提交的時(shí)候binlog和redolog都會(huì)寫到磁盤，這大大增加了IO交互，導(dǎo)致整個(gè)插入過(guò)程非常慢，在我的電腦上運(yùn)行了30分鐘才插入完成，解決方法有很多可以設(shè)置sync_binlog=0,innodb_flush_log_at_trx_commit=0，sync_binlog=0表示每次提交只write(從binlog cache 寫到page cache)不fsync(寫到磁盤)，innodb_flush_log_at_trx_commit=0表示每次提交只寫到redo log buffer(就是不做任何操作，因?yàn)閞edo log最開(kāi)始生成的時(shí)候就在redo log buffer 中，Innodb 后臺(tái)有一個(gè)線程，每1s就會(huì)將redo log buffer里面的數(shù)據(jù)調(diào)用write寫到page cache 然后再調(diào)用 fsync 寫到磁盤)，還需注意的是binlog cache 是線程私有的，redo log buffer是公共的，所以處以prepare階段的redo log是可能被持久化的。

插入數(shù)據(jù)后，我們分析一下sql 語(yǔ)句的執(zhí)行計(jì)劃

EXPLAIN SELECT * from t where a BETWEEN 10000 and 20000;

你肯定會(huì)想這還不簡(jiǎn)單嗎?字段a上有索引肯定走的索引a，事實(shí)也是這樣的，下面是執(zhí)行計(jì)劃的截圖


正常索引.png

我們?cè)僮鋈缦虏僮鳎纯辞闆r怎樣

sessionA sessionB
start transaction with consistent snapshot;
delete from t;
call idata();
EXPLAIN SELECT * from t where a BETWEEN 10000 and 20000;
commit;

start transaction with consistent snapshot; 代表的意思是執(zhí)行這句話就啟動(dòng)了事務(wù)，就生成一致性視圖，如果只是start transaction 只有在第一次查詢的時(shí)候才會(huì)生成一致性視圖，需要說(shuō)明的是start transaction并不是事務(wù)的起點(diǎn)，在執(zhí)行到start transaction之后的第一個(gè)操作才啟動(dòng)事務(wù)，第一個(gè)查詢語(yǔ)句生成的trx_id 是虛假的，是為顯示用的，只有事務(wù)性操作生成的trx_id 才會(huì)存于一致性視圖數(shù)組中。這個(gè)一致性視圖數(shù)組是mvcc實(shí)現(xiàn)的基礎(chǔ)。

然后，我們現(xiàn)在再來(lái)看一下sessionB 的 EXPLAIN SELECT * from t where a BETWEEN 10000 and 20000;

有誤的索引.png

發(fā)現(xiàn)此時(shí)是全表掃描，key處為空，rows為100015行，沒(méi)有走索引a，和我們預(yù)想的可能不太一樣，為了更準(zhǔn)確的說(shuō)明，是mysql選錯(cuò)了索引，我們?cè)賮?lái)做個(gè)對(duì)比。

set GLOBAL slow_query_log = 1;

set long_query_time = 0;

SELECT * from t where a BETWEEN 10000 and 20000;/**Q1**/

SELECT * from t FORCE INDEX(a) where a BETWEEN 10000 and 20000;/**Q2**/
  • 第一條語(yǔ)句是打開(kāi)慢日志的開(kāi)關(guān) ，這是個(gè)全局的開(kāi)關(guān)
  • 第二條語(yǔ)句是慢日志的標(biāo)準(zhǔn)，就是說(shuō)什么樣的語(yǔ)句是慢日志，才會(huì)被慢日志記錄下來(lái)。這里的意思是只要操作時(shí)間超過(guò)0s就會(huì)記錄下來(lái)，所以這里是記錄所有的操作語(yǔ)句（增刪改查都會(huì)記錄）。
  • 第三條是在sessionB下的查詢語(yǔ)句(Q1)
  • 第四條是在sessionB下的查詢語(yǔ)句(Q2)，但是強(qiáng)制使用的索引a

我們來(lái)看看Q1與Q2在慢日志中的具體查詢信息

# Time: 2021-05-03T02:17:03.047811Z
# User@Host: root[root] @ localhost [::1]  Id:    20
# Query_time: 0.035942  Lock_time: 0.000000 Rows_sent: 10001  Rows_examined: 100000
SET timestamp=1620008223;
SELECT * from t where a BETWEEN 10000 and 20000;

# Time: 2021-05-03T02:18:08.055825Z
# User@Host: root[root] @ localhost [::1]  Id:    20
# Query_time: 0.021942  Lock_time: 0.000000 Rows_sent: 10001  Rows_examined: 10001
SET timestamp=1620008288;
SELECT * from t FORCE INDEX(a) where a BETWEEN 10000 and 20000;

可以看到?jīng)]有使用強(qiáng)制索引a的Q1語(yǔ)句確實(shí)是全表掃描了Rows_examined: 100000,Query_time: 0.035942,強(qiáng)制使用了索引a的Q2語(yǔ)句Rows_examined: 10001,Query_time: 0.021942，到這里我們可以肯定說(shuō)，mysql沒(méi)有使用最優(yōu)的方案來(lái)查詢，下面我們就來(lái)具體分析分析為什么！

首先我們要明白的是優(yōu)化器是怎么選擇索引的，索引的選擇有很多因素影響包括是否排序、是否使用到臨時(shí)表、掃描行數(shù)等因素，優(yōu)化器是綜合考慮各個(gè)因素選擇的最優(yōu)方案，這里我們的語(yǔ)句SELECT * from t where a BETWEEN 10000 and 20000;，沒(méi)有使用到臨時(shí)表，也沒(méi)有排序，所以這里最關(guān)鍵的影響就是掃描行數(shù)，掃描行數(shù)越少，訪問(wèn)磁盤的次數(shù)越少，需要消耗的CPU資源就越少。接下來(lái)問(wèn)題又來(lái)了，掃描行數(shù)是怎么判斷的呢？

我們?cè)趦?yōu)化器階段就判斷了掃描的行數(shù)（執(zhí)行計(jì)劃中的Rows），但這個(gè)掃描的行數(shù)是預(yù)估的，不是準(zhǔn)確的，為什么要預(yù)估呢？因?yàn)閽呙杷袛?shù)據(jù)需要消耗大量的資源，比如CPU等等，那是怎么預(yù)估的呢。這里有個(gè)基數(shù)的概念，基數(shù)是什么呢？基數(shù)是一個(gè)索引上不同值的個(gè)數(shù)，索引的基數(shù)越大說(shuō)明索引的區(qū)分度越好，掃描的行數(shù)可能就越少，索引的基數(shù)不是實(shí)時(shí)變化的，它也是一個(gè)統(tǒng)計(jì)的結(jié)果，那它是怎么統(tǒng)計(jì)的呢？

show index from t 來(lái)查看表t上所有索引的基數(shù)

基數(shù)的統(tǒng)計(jì) ：基數(shù)的統(tǒng)計(jì)是采用統(tǒng)計(jì)的方法計(jì)算的，在統(tǒng)計(jì)的時(shí)候默認(rèn)隨機(jī)取N頁(yè)數(shù)據(jù)，統(tǒng)計(jì)這N頁(yè)數(shù)據(jù)所有不同的數(shù)值的個(gè)數(shù)得到平均值，然后再乘以這個(gè)索引的總頁(yè)數(shù)，最后得到基數(shù)。數(shù)據(jù)表里面的數(shù)據(jù)在不斷變更，當(dāng)變更的數(shù)據(jù)超過(guò)總行數(shù)的1/M時(shí)會(huì)再次觸發(fā)統(tǒng)計(jì)，修正原先統(tǒng)計(jì)的基數(shù)。至于M和N 到底是多少？在mysql中有兩種存儲(chǔ)索引的方式，可以通過(guò)設(shè)置參數(shù)innodb_stats_persistent 的值來(lái)選擇：

  • 設(shè)置為 on 的時(shí)候，表示統(tǒng)計(jì)信息會(huì)持久化存儲(chǔ)。這時(shí)，默認(rèn)的 N 是 20，M 是 10。

  • 設(shè)置為 off 的時(shí)候，表示統(tǒng)計(jì)信息只存儲(chǔ)在內(nèi)存中。這時(shí)，默認(rèn)的 N 是 8，M 是 16

通過(guò)基數(shù)，優(yōu)化器可以判斷會(huì)不會(huì)選擇該索引(這里猜測(cè)有一個(gè)閾值)，如果區(qū)分度實(shí)在是太小則放棄該索引（這就是建議我們不要在區(qū)分度不高的列上加索引，因?yàn)闆](méi)啥用），經(jīng)過(guò)第一層過(guò)濾，優(yōu)化器還要判斷掃描行數(shù)，就是執(zhí)行計(jì)劃的rows，這個(gè)rows也是一個(gè)統(tǒng)計(jì)的結(jié)果(根據(jù)where條件在索引上統(tǒng)計(jì))，看rows是不是太大，如果太大，優(yōu)化器想我還不如直接全表掃描算了，免得還要回表，所以這里還有一個(gè)掃描行數(shù)的權(quán)衡，到這里我們基本講完了索引是怎么選擇的了。

回到本文的示例，sessionB中的查詢語(yǔ)句為什么不走索引a呢？那就是mysql的統(tǒng)計(jì)的rows太大，優(yōu)化器覺(jué)得使用索引a不劃算 。那為什么mysql的統(tǒng)計(jì)的rows太大，是因?yàn)?sessionA語(yǔ)句來(lái)了就start transaction with consistent snapshot; ，這個(gè)語(yǔ)句一執(zhí)行就創(chuàng)建了視圖，根據(jù)mvcc規(guī)則，它要看見(jiàn)它能看見(jiàn)的數(shù)據(jù)，sessionB執(zhí)行刪除操作，只是把每行的數(shù)據(jù)標(biāo)記為已刪除，實(shí)際并沒(méi)有刪除(ibd文件大小并沒(méi)有發(fā)生改變)，新插入的數(shù)據(jù)因?yàn)閕d是遞增的不能復(fù)用被標(biāo)記刪除的位置，所以通過(guò)頁(yè)分裂的形式把新增的數(shù)據(jù)放入對(duì)應(yīng)的數(shù)據(jù)頁(yè)中，這樣在統(tǒng)計(jì)rows的時(shí)候因?yàn)檫B帶被標(biāo)記刪除的行一起統(tǒng)計(jì)，導(dǎo)致rows統(tǒng)計(jì)的行數(shù)偏大，從而進(jìn)一步導(dǎo)致優(yōu)化器選擇了全表掃描。

這里要補(bǔ)充一下頁(yè)的知識(shí)：

  1. Innodb與磁盤交互的最小單位是頁(yè)，所以說(shuō)加載數(shù)據(jù)到內(nèi)存是按照頁(yè)來(lái)加載的而不是一行一行加載，索引只能定位到頁(yè)不能定位到具體哪一行，哪一行只能在頁(yè)中通過(guò)二分法來(lái)進(jìn)行查找

  2. 頁(yè)與頁(yè)之間是通過(guò)雙向鏈表來(lái)鏈接的，頁(yè)內(nèi)部的數(shù)據(jù)行是通過(guò)單向鏈表來(lái)鏈接的。

  3. 插入數(shù)據(jù)可能會(huì)導(dǎo)致頁(yè)的分裂，尤其是非自增主鍵，從而導(dǎo)致頁(yè)空洞（就是頁(yè)的利用率不高）；刪除數(shù)據(jù)會(huì)導(dǎo)致數(shù)據(jù)的空洞，也可能會(huì)導(dǎo)致頁(yè)的合并

  4. 頁(yè)的結(jié)構(gòu) 如附件表所示

那我們知道了原因，如何進(jìn)行優(yōu)化呢。主要有以下幾種解決方法

  1. analyze table ，通過(guò)該命令重新統(tǒng)計(jì)基數(shù)信息
    analyze.png

  1. 通過(guò) force index ,強(qiáng)制使用某個(gè)索引，告訴優(yōu)化器你不用選擇索引了，我就用這個(gè)，但是該方案十分的不優(yōu)雅

    MySQL 會(huì)根據(jù)詞法解析的結(jié)果分析出可能可以使用的索引作為候選項(xiàng)，我們經(jīng)常可以看到執(zhí)行計(jì)劃的possible_key有幾個(gè)候選項(xiàng)，然后在候選列表中依次判斷每個(gè)索引需要掃描多少行?；旧鲜沁x擇掃描行數(shù)最少的作為最后的索引，如果 force index 指定的索引在候選索引列表中，就直接選擇這個(gè)索引，不再評(píng)估其他索引的執(zhí)行代價(jià)。

  2. 在不改變語(yǔ)義的情況下，修改sql語(yǔ)句，引導(dǎo)執(zhí)行計(jì)劃選擇我們想要的索引

  3. 新建一個(gè)更合適的索引或者刪除誤用或沒(méi)有的索引

小結(jié)

本篇文章我們具體分析了mysql是怎么選擇索引的，以及為什么會(huì)選錯(cuò)索引，并給出了四種優(yōu)化方案，謝謝。

附件

頁(yè)結(jié)構(gòu)表：


頁(yè)結(jié)構(gòu).png
最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請(qǐng)結(jié)合常識(shí)與多方信息審慎甄別。
平臺(tái)聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡(jiǎn)書(shū)系信息發(fā)布平臺(tái)，僅提供信息存儲(chǔ)服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容