10 - MySQL選錯索引
關(guān)鍵字
索引選擇
0.錯誤情況
首先，建立一個簡單的表，表中有 a、b 兩個字段，并且分別建立了索引：
CREATE TABLE `t` (
`id` int(11) NOT NULL,
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `a` (`a`),
KEY `b` (`b`)
) ENGINE=InnoDB；
然后，我們往表 t 中插入 10 萬行記錄，取值按整數(shù)遞增，即：(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 values(i, i, i);
set i=i+1;
end while;
end;;
delimiter ;
call idata();
接下來，我們分析一條語句：
mysql> select * from t where a between 10000 and 20000;
使用 explain 分析這條語句的執(zhí)行情況：
沒錯，這條語句的執(zhí)行是符合預(yù)期的。下面，我們來看一個會出錯的情況，我們在這個已經(jīng)有數(shù)據(jù)的表中，再做如下操作：
這時，session B 的 select * from t where a between 10000 and 20000 語句就不會選擇 a 索引了，而是直接使用了全表掃描。
執(zhí)行下面三條語句：
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*/
- 第一句，是將慢查詢?nèi)罩镜拈撝翟O(shè)置為 0，表示這個線程接下來的語句都會被記錄入慢查詢?nèi)罩局校?/li>
- 第二句，Q1 是 session B 原來的查詢；
- 第三句，Q2 是加了 force index(a) 來和 session B 原來的查詢語句執(zhí)行情況對比。
下圖是他們生成的慢查詢?nèi)罩荆?div id="u0z1t8os" class="image-package">
10-慢查詢?nèi)罩?png
可以看到，Q1 掃描了 10萬 行，進行了全表掃描，Q2 使用了 a 索引掃描了 10001行。
為什么會這樣呢？實際上，索引的選擇是由優(yōu)化器決定的，但是在某些特殊情況下，優(yōu)化器會選擇并非最優(yōu)的方案。一般我們很難事先防范這種 bug ，但是你有必要理解其中的邏輯。
1.優(yōu)化器的邏輯
優(yōu)化器判斷語句執(zhí)行方式，會用到 掃描行數(shù)、數(shù)據(jù)排序 等。
1.1掃描行數(shù)
優(yōu)化器選擇索引的邏輯之一，就是計算需要掃描的行數(shù)。需要執(zhí)行的行數(shù)越少，意味著消耗資源越少，優(yōu)化器也就更加傾向選擇這種操作。
在上面的例子中，影響優(yōu)化器選擇的最主要的原因就是掃描行數(shù)。實際上，MySQL 在執(zhí)行一個語句之前是不可能知道它需要掃描多少行的，所以它需要使用表的統(tǒng)計信息來估算記錄數(shù)。
1.1.1索引基數(shù)
一個索引上有多少個不同的值，這稱之為“基數(shù)”。一個索引的基數(shù)越大，索引越容易被區(qū)分。在 MySQL 中，使用抽樣統(tǒng)計的方法獲取索引的基數(shù)值。而索引的基數(shù)，可以通過 show index 查看：
你會發(fā)現(xiàn)，即使抽樣統(tǒng)計有誤差，但是三個索引的索引基數(shù)是差不多的，所以，其實選錯索引這件事，和索引基數(shù)的關(guān)系并不太大。
1.1.2預(yù)估掃描行數(shù)
既然索引錯誤與索引基數(shù)關(guān)系不大，那么我們不妨分析一下 Q1 和 Q2 這兩條語句：
在圖中，row 表示預(yù)計掃描的行數(shù)：
- Q1 的結(jié)果是符合預(yù)期的，預(yù)估將掃描 10w+ 數(shù)據(jù)。
- Q2 的結(jié)果就不對了，我們已經(jīng)在最開始試過，該命令當時的 row 只有 10001 ，是這個誤差誤導(dǎo)了優(yōu)化器的判斷。
你可能會疑問，即使 Q2 的 row 錯誤，但是 3w+ 的值依然小于 10w+ 啊，為什么優(yōu)化器依然選擇了 10w+ 的索引方式呢？這里簡單說一下：因為使用 a 索引涉及到回表操作，優(yōu)化器將這部分內(nèi)容也算了進去，綜合來看，優(yōu)化器認為使用主鍵索引更快。
所以，歸根結(jié)底，MySQL 選錯索引，主要原因是它錯誤的判斷了掃描行數(shù)，至于為什么會有錯誤的掃描行數(shù)，這留作今天的思考題。
既然問題出在統(tǒng)計信息，那么我們可以使用 analyze table t ，重新統(tǒng)計 t 表的索引信息，執(zhí)行效果如下：
這次就對了。
1.2數(shù)據(jù)排序
同樣是這個表，我們執(zhí)行下面的語句：
mysql> select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1;
很明顯，
- 我們知道 t 表中所有數(shù)據(jù)的內(nèi)容，使用 a 索引是最快的，只需要掃描 a 的前 1000 個值，然后進行回表。
- 而如果使用 b 索引，需要掃描 5w 行，再進行回表。
然而，MySQL 又選錯了索引，如果不使用強制索引，MySQL 會使用 b 作為索引，而它的執(zhí)行花費非常高。
為什么會這樣呢？因為語句的末尾有 order by b，所以優(yōu)化器認為使用 b 索引可以避免排序，而使用 a 的化還需要根據(jù) b 再進行一次排序。這就導(dǎo)致了錯誤選擇。
2.如何處理索引選擇異常 & 總結(jié)
之前已經(jīng)說過，索引選擇異常非常復(fù)雜。實際上，在大多數(shù)情況下，優(yōu)化器的選擇都是正確的，只有在非常特殊的時候，才會遇到上面我們說的情況。因此，因為一個較小概率的事重建優(yōu)化器的選擇方案，是得不償失的。
所以，在出現(xiàn)索引選擇異常的時候，給你一些小的處理方法：
- 第一種方法，使用force index強行選擇索引：在你非常確定使用某個索引是最優(yōu)選擇的時候，你可以為 MySQL 決定選擇哪個索引。
- 第二種方法，使用analyze table重建表的統(tǒng)計信息。
- 第三種方法，修改語句，引導(dǎo) MySQL 使用我們期望的索引：在排序的例子中，把“order by b limit 1” 改成 “order by b,a limit 1” ，語義的邏輯是相同的。但是在這種引導(dǎo)下，MySQL 會使用 a 作為索引。但是，這樣的方法并不通用，因為你無法確定 a和b 之間的關(guān)系。
- 第四種方法，新建一個更適合的索引，來給優(yōu)化器做選擇，或者刪掉誤用的索引：當然，你要確定這個索引可以刪除。
思考題
前面我們在構(gòu)造第一個例子的過程中，通過 session A 的配合，讓 session B 刪除數(shù)據(jù)后又重新插入了一遍數(shù)據(jù)，然后就發(fā)現(xiàn) explain 結(jié)果中，rows 字段從 10001 變成 37000 多。
而如果沒有 session A 的配合，只是單獨執(zhí)行 delete from t 、call idata()、explain 這三句話，會看到 rows 字段其實還是 10000 左右。你可以自己驗證一下這個結(jié)果。
這是什么原因？請你分析一下。
上期答案
上一篇文章的問題是，如果某次寫入使用了 change buffer 機制，之后主機異常重啟，是否會丟失 change buffer 和數(shù)據(jù)。
答案是不會丟失，雖然是只更新內(nèi)存，但是在事務(wù)提交的時候，我們把 change buffer 的操作也記錄到 redo log 里了，所以崩潰恢復(fù)的時候，change buffer 也能找回來。
以上就是本節(jié)內(nèi)容，希望在人生路上，你能獲得正確索引的指引。
注：本文章的主要內(nèi)容來自我對極客時間app的《MySQL實戰(zhàn)45講》專欄的總結(jié)，我使用了大量的原文、代碼和截圖，如果想要了解具體內(nèi)容，可以前往極客時間
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點，簡書系信息發(fā)布平臺，僅提供信息存儲服務(wù)。
