10 | MySQL為什么有時候會選錯索引？

使用哪個索引是由 MySQL 來確定的

可能遇到的情況：一條本來可以執行得很快的陳述句，卻由于 MySQL 選錯了索引，而導致執行速度變得很慢

先建一個簡單的表，表里有 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 萬行記錄，取值按整數遞增，即：(1,1,1)，(2,2,2)，(3,3,3) 直到 (100000,100000,100000)，

分析一條 SQL 陳述句：

select * from t where a between 10000 and 20000;

使用explain查看命令執行情況：

這條查詢陳述句的執行也確實符合預期，key 這個欄位值是’a’，表示優化器選擇了索引 a，

接著做如下操作：

session A 開啟了一個事務，隨后，session B 把資料都洗掉后，又呼叫了 idata 這個存盤程序，插入了 10 萬行資料

這時候，session B 的查詢陳述句 select * from t where a between 10000 and 20000 就不會再選擇索引 a 了

使用對照 force index(a) 證明優化器真的選錯了索引

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*/

第一句，是將慢查詢日志的閾值設定為 0，表示這個執行緒接下來的陳述句都會被記錄入慢查詢日志中；
第二句，Q1 是 session B 原來的查詢；
第三句，Q2 是加了 force index(a) 來和 session B 原來的查詢陳述句執行情況對比，

如果使用索引 a，每次從索引 a 上拿到一個值，都要回到主鍵索引上查出整行資料，這個代價優化器也要算進去的，

而如果選擇掃描 10 萬行，是直接在主鍵索引上掃描的，沒有額外的代價，

優化器會估算這兩個選擇的代價，從結果看來，優化器認為直接掃描主鍵索引更快，然后就錯了

例子對應的是我們平常不斷地洗掉歷史資料和新增資料的場景

優化器的邏輯

優化器選擇索引的目的，是找到一個最優的執行方案，并用最小的代價去執行陳述句

在資料庫里面，掃描行數是影響執行代價的因素之一，掃描的行數越少，意味著訪問磁盤資料的次數越少，消耗的 CPU 資源越少，

掃描行數并不是唯一的判斷標準，優化器還會結合是否使用臨時表、是否排序等因素進行綜合判斷，

掃描行數是怎么判斷的？

MySQL 在真正開始執行陳述句之前，并不能精確地知道滿足這個條件的記錄有多少條，而只能根據統計資訊來估算記錄數，

這個統計資訊就是索引的“區分度”，一個索引上不同的值越多，這個索引的區分度就越好，而一個索引上不同的值的個數，我們稱之為“基數”（cardinality），也就是說，這個基數越大，索引的區分度越好，

下圖為表 t 的 show index 的結果，雖然這個表的每一行的三個欄位值都是一樣的，但是在統計資訊中，這三個索引的基數值并不同，而且其實都不準確，

MySQL 是怎樣得到索引的基數

MySQL 使用采樣統計的方法，因為把整張表取出來一行行統計，雖然可以得到精確的結果，但是代價太高了，所以只能選擇“采樣統計”，

采樣統計的時候，InnoDB 默認會選擇 N 個資料頁，統計這些頁面上的不同值，得到一個平均值，然后乘以這個索引的頁面數，就得到了這個索引的基數S，

資料表是會持續更新的，索引統計資訊也不會固定不變，所以，當變更的資料行數X，X/S超過 1/M 的時候，會自動觸發重新做一次索引統計，

設定為 on 的時候，表示統計資訊會持久化存盤，這時，默認的 N 是 20，M 是 10，
設定為 off 的時候，表示統計資訊只存盤在記憶體中，這時，默認的 N 是 8，M 是 16，

索引統計只是一個輸入，對于一個具體的陳述句來說，優化器還要判斷，執行這個陳述句本身要掃描多少行，考慮回表，

如果發現 explain 的結果預估的 rows 值跟實際情況差距比較大，使用analyze table t來重新統計索引資訊

不是由于索引統計資訊不準確導致索引選錯的情況：

依然是基于表 t，另外一個陳述句：

select * from t where (a between 1 and 1000)  and (b between 50000 and 100000) order by b limit 1;

從條件上看，這個查詢沒有符合條件的記錄，因此會回傳空集合，

人工判斷會選擇哪一個索引？

如果使用索引 a 進行查詢，那么就是掃描索引 a 的前 1000 個值，然后取到對應的 id，再到主鍵索引上去查出每一行，然后根據欄位 b 來過濾，顯然這樣需要掃描 1000 行，

如果使用索引 b 進行查詢，那么就是掃描索引 b 的最后 50001 個值，與上面的執行程序相同，也是需要回到主鍵索引上取值再判斷，所以需要掃描 50001 行，

explain select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1;

key 欄位顯示，這次優化器選擇了索引 b，而 rows 欄位顯示需要掃描的行數是 50198，

修改陳述句，引導 MySQL 使用我們期望的索引，

法一：

把“order by b limit 1” 改成 “order by b,a limit 1” ，語意的邏輯是相同的，

之前優化器選擇使用索引 b，是因為它認為使用索引 b 可以避免排序（b 本身是索引，已經是有序的了，如果選擇索引 b 的話，不需要再做排序，只需要遍歷），所以即使掃描行數多，也判定為代價更小，

現在 order by b,a 這種寫法，要求按照 b,a 排序，就意味著使用這兩個索引都需要排序，因此，掃描行數成了影響決策的主要條件，于是此時優化器選了只需要掃描 1000 行的索引 a，

！注意：

這種修改并不是通用的優化手段，只是剛好在這個陳述句里面有 limit 1，因此如果有滿足條件的記錄， order by b limit 1 和 order by b,a limit 1 都會回傳 b 是最小的那一行，邏輯上一致，才可以這么做，

法二：

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;

在這個例子里，我們用 limit 100 讓優化器意識到，使用 b 索引代價是很高的，其實是我們根據資料特征誘導了一下優化器，也不具備通用性，

法三：

在有些場景下，我們可以新建一個更合適的索引，來提供給優化器做選擇，或刪掉誤用的索引，

總結：

由于索引統計資訊不準確導致行數判斷失誤的問題，你可以用 analyze table 來解決，

對于其他優化器誤判的情況，你可以在應用端用 force index 來強行指定索引，也可以通過修改陳述句來引導優化器，還可以通過增加或者洗掉索引來繞過這個問題，

Q:在構造第一個例子的程序中，通過 session A 的配合，讓 session B 洗掉資料后又重新插入了一遍資料，然后就發現 explain 結果中，rows 欄位從 10001 變成 37000 多，

而如果沒有 session A 的配合，只是單獨執行 delete from t 、call idata()、explain 這三句話，會看到 rows 欄位其實還是 10000 左右，這是什么原因呢？

delete 陳述句刪掉了所有的資料，然后再通過 call idata() 插入了 10 萬行資料，看上去是覆寫了原來的 10 萬行，

但是，session A 開啟了事務并沒有提交，所以之前插入的 10 萬行資料是不能洗掉的，這樣，之前的資料每一行資料都有兩個版本，舊版本是 delete 之前的資料，新版本是標記為 deleted 的資料，這樣，索引 a 上的資料其實就有兩份，

但是主鍵索引不是通過采樣生成的，是直接按照表的行數來估計的，而表的行數，優化器直接用的是show table status 的值，

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/500143.html

標籤：MySQL

上一篇：MySQL實戰45講 6,7,8

下一篇：MySQL實戰45講 11

MySQL實戰45講 10

10 | MySQL為什么有時候會選錯索引？

優化器的邏輯

掃描行數是怎么判斷的？

MySQL 是怎樣得到索引的基數

不是由于索引統計資訊不準確導致索引選錯的情況：

總結：