上節(jié)我們講了mysql的整體架構(gòu)苹支，知道了優(yōu)化器的作用是優(yōu)化sql嗜历，選擇索引，生成執(zhí)行計劃吨铸。索引是優(yōu)化器階段自己選擇的行拢，優(yōu)化器大部分情況下索引的選擇都是比較合理的，但是也有特殊情況下诞吱，優(yōu)化器選擇的索引并不是最優(yōu)的舟奠。下面我們通過實驗來說明這個問題竭缝，并給出優(yōu)化方案（需要說明的是本實驗的環(huán)境是mysql版本是5.7,事務隔離級別是RR,事務自動提交）。
首先我們來建一個表沼瘫，表里面有id抬纸、a、b三個字段耿戚，下面是建表語句
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萬行記錄，記錄是遞增的 (1,1,1)膜蛔， (2,2,2)坛猪， (3,3,3)......(100000,100000,100000)，插入語句如下：
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默認是自動提交飞几，且
sync_binlog=1,innodb_flush_log_at_trx_commit=1砚哆，所以說上面的存儲過程每次插入數(shù)據(jù)，在提交的時候binlog和redolog都會寫到磁盤屑墨，這大大增加了IO交互躁锁，導致整個插入過程非常慢，在我的電腦上運行了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(就是不做任何操作以躯，因為redo log最開始生成的時候就在redo log buffer 中槐秧，Innodb 后臺有一個線程，每1s就會將redo log buffer里面的數(shù)據(jù)調(diào)用write寫到page cache 然后再調(diào)用 fsync 寫到磁盤)忧设，還需注意的是binlog cache 是線程私有的刁标，redo log buffer是公共的，所以處以prepare階段的redo log是可能被持久化的址晕。
插入數(shù)據(jù)后膀懈，我們分析一下sql 語句的執(zhí)行計劃
EXPLAIN SELECT * from t where a BETWEEN 10000 and 20000;
你肯定會想這還不簡單嗎?字段a上有索引肯定走的索引a，事實也是這樣的谨垃，下面是執(zhí)行計劃的截圖
我們再做如下操作启搂，看看情況怎樣
| 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í)行這句話就啟動了事務，就生成一致性視圖刘陶，如果只是start transaction只有在第一次查詢的時候才會生成一致性視圖胳赌，需要說明的是start transaction并不是事務的起點，在執(zhí)行到start transaction之后的第一個操作才啟動事務匙隔，第一個查詢語句生成的trx_id 是虛假的疑苫，是為顯示用的，只有事務性操作生成的trx_id 才會存于一致性視圖數(shù)組中。這個一致性視圖數(shù)組是mvcc實現(xiàn)的基礎(chǔ)缀匕。
然后纳决，我們現(xiàn)在再來看一下sessionB 的 EXPLAIN SELECT * from t where a BETWEEN 10000 and 20000;
發(fā)現(xiàn)此時是全表掃描，key處為空乡小，rows為100015行阔加，沒有走索引a，和我們預想的可能不太一樣满钟，為了更準確的說明胜榔，是mysql選錯了索引，我們再來做個對比湃番。
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**/
- 第一條語句是打開慢日志的開關(guān) 夭织，這是個全局的開關(guān)
- 第二條語句是慢日志的標準，就是說什么樣的語句是慢日志吠撮，才會被慢日志記錄下來尊惰。這里的意思是只要操作時間超過0s就會記錄下來，所以這里是記錄所有的操作語句（增刪改查都會記錄）泥兰。
- 第三條是在sessionB下的查詢語句(Q1)
- 第四條是在sessionB下的查詢語句(Q2)弄屡，但是強制使用的索引a
我們來看看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)]有使用強制索引a的Q1語句確實是全表掃描了Rows_examined: 100000,Query_time: 0.035942,強制使用了索引a的Q2語句Rows_examined: 10001,Query_time: 0.021942，到這里我們可以肯定說鞋诗，mysql沒有使用最優(yōu)的方案來查詢膀捷，下面我們就來具體分析分析為什么！
首先我們要明白的是優(yōu)化器是怎么選擇索引的削彬，索引的選擇有很多因素影響包括是否排序全庸、是否使用到臨時表、掃描行數(shù)等因素融痛，優(yōu)化器是綜合考慮各個因素選擇的最優(yōu)方案壶笼，這里我們的語句SELECT * from t where a BETWEEN 10000 and 20000;，沒有使用到臨時表雁刷，也沒有排序拌消，所以這里最關(guān)鍵的影響就是掃描行數(shù)，掃描行數(shù)越少安券，訪問磁盤的次數(shù)越少，需要消耗的CPU資源就越少氓英。接下來問題又來了侯勉，掃描行數(shù)是怎么判斷的呢？
我們在優(yōu)化器階段就判斷了掃描的行數(shù)（執(zhí)行計劃中的Rows）铝阐，但這個掃描的行數(shù)是預估的址貌，不是準確的，為什么要預估呢？因為掃描所有數(shù)據(jù)需要消耗大量的資源练对，比如CPU等等遍蟋，那是怎么預估的呢。這里有個基數(shù)的概念螟凭，基數(shù)是什么呢虚青？基數(shù)是一個索引上不同值的個數(shù)，索引的基數(shù)越大說明索引的區(qū)分度越好螺男，掃描的行數(shù)可能就越少棒厘，索引的基數(shù)不是實時變化的，它也是一個統(tǒng)計的結(jié)果下隧，那它是怎么統(tǒng)計的呢奢人？
show index from t來查看表t上所有索引的基數(shù)
基數(shù)的統(tǒng)計 ：基數(shù)的統(tǒng)計是采用統(tǒng)計的方法計算的，在統(tǒng)計的時候默認隨機取N頁數(shù)據(jù)淆院，統(tǒng)計這N頁數(shù)據(jù)所有不同的數(shù)值的個數(shù)得到平均值何乎，然后再乘以這個索引的總頁數(shù)，最后得到基數(shù)土辩。數(shù)據(jù)表里面的數(shù)據(jù)在不斷變更支救，當變更的數(shù)據(jù)超過總行數(shù)的1/M時會再次觸發(fā)統(tǒng)計，修正原先統(tǒng)計的基數(shù)脯燃。至于M和N 到底是多少搂妻？在mysql中有兩種存儲索引的方式，可以通過設(shè)置參數(shù)innodb_stats_persistent 的值來選擇：
設(shè)置為 on 的時候辕棚，表示統(tǒng)計信息會持久化存儲欲主。這時，默認的 N 是 20逝嚎，M 是 10扁瓢。
設(shè)置為 off 的時候，表示統(tǒng)計信息只存儲在內(nèi)存中补君。這時引几，默認的 N 是 8，M 是 16
通過基數(shù)挽铁，優(yōu)化器可以判斷會不會選擇該索引(這里猜測有一個閾值)伟桅，如果區(qū)分度實在是太小則放棄該索引（這就是建議我們不要在區(qū)分度不高的列上加索引，因為沒啥用）叽掘，經(jīng)過第一層過濾楣铁，優(yōu)化器還要判斷掃描行數(shù)，就是執(zhí)行計劃的rows更扁，這個rows也是一個統(tǒng)計的結(jié)果(根據(jù)where條件在索引上統(tǒng)計)盖腕，看rows是不是太大赫冬，如果太大，優(yōu)化器想我還不如直接全表掃描算了溃列，免得還要回表劲厌，所以這里還有一個掃描行數(shù)的權(quán)衡，到這里我們基本講完了索引是怎么選擇的了听隐。
回到本文的示例补鼻，sessionB中的查詢語句為什么不走索引a呢？那就是mysql的統(tǒng)計的rows太大遵绰，優(yōu)化器覺得使用索引a不劃算 辽幌。那為什么mysql的統(tǒng)計的rows太大，是因為 sessionA語句來了就start transaction with consistent snapshot; 椿访，這個語句一執(zhí)行就創(chuàng)建了視圖乌企，根據(jù)mvcc規(guī)則，它要看見它能看見的數(shù)據(jù)成玫，sessionB執(zhí)行刪除操作加酵，只是把每行的數(shù)據(jù)標記為已刪除，實際并沒有刪除(ibd文件大小并沒有發(fā)生改變)哭当，新插入的數(shù)據(jù)因為id是遞增的不能復用被標記刪除的位置猪腕，所以通過頁分裂的形式把新增的數(shù)據(jù)放入對應的數(shù)據(jù)頁中，這樣在統(tǒng)計rows的時候因為連帶被標記刪除的行一起統(tǒng)計钦勘，導致rows統(tǒng)計的行數(shù)偏大陋葡，從而進一步導致優(yōu)化器選擇了全表掃描。
這里要補充一下頁的知識：
Innodb與磁盤交互的最小單位是頁彻采，所以說加載數(shù)據(jù)到內(nèi)存是按照頁來加載的而不是一行一行加載腐缤，索引只能定位到頁不能定位到具體哪一行，哪一行只能在頁中通過二分法來進行查找
頁與頁之間是通過雙向鏈表來鏈接的肛响，頁內(nèi)部的數(shù)據(jù)行是通過單向鏈表來鏈接的岭粤。
插入數(shù)據(jù)可能會導致頁的分裂，尤其是非自增主鍵特笋，從而導致頁空洞（就是頁的利用率不高）剃浇；刪除數(shù)據(jù)會導致數(shù)據(jù)的空洞，也可能會導致頁的合并
頁的結(jié)構(gòu) 如附件表所示
那我們知道了原因猎物，如何進行優(yōu)化呢虎囚。主要有以下幾種解決方法
-
analyze table，通過該命令重新統(tǒng)計基數(shù)信息
analyze.png
-
通過
force index,強制使用某個索引蔫磨，告訴優(yōu)化器你不用選擇索引了溜宽，我就用這個，但是該方案十分的不優(yōu)雅MySQL 會根據(jù)詞法解析的結(jié)果分析出可能可以使用的索引作為候選項质帅，我們經(jīng)常可以看到執(zhí)行計劃的possible_key有幾個候選項，然后在候選列表中依次判斷每個索引需要掃描多少行煤惩〖掂郑基本上是選擇掃描行數(shù)最少的作為最后的索引，如果 force index 指定的索引在候選索引列表中魄揉，就直接選擇這個索引剪侮，不再評估其他索引的執(zhí)行代價。
在不改變語義的情況下洛退，修改sql語句瓣俯，引導執(zhí)行計劃選擇我們想要的索引
新建一個更合適的索引或者刪除誤用或沒有的索引
小結(jié)
本篇文章我們具體分析了mysql是怎么選擇索引的，以及為什么會選錯索引兵怯，并給出了四種優(yōu)化方案彩匕，謝謝。
附件
頁結(jié)構(gòu)表：
