這個 MySQL bug 讓我大開眼界
- 2021 年 9 月 25 日
- 筆記
這周收到一個 sentry 報警，如下 SQL 查詢超時了。
select * from order_info where uid = 5837661 order by id asc limit 1
執行show create table order_info 發現這個表其實是有加索引的
CREATE TABLE `order_info` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`uid` int(11) unsigned,
`order_status` tinyint(3) DEFAULT NULL,
... 省略其它欄位和索引
PRIMARY KEY (`id`),
KEY `idx_uid_stat` (`uid`,`order_status`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8
理論上執行上述 SQL 會命中 idx_uid_stat 這個索引，但實際執行 explain 查看
explain select * from order_info where uid = 5837661 order by id asc limit 1
可以看到它的 possible_keys（此 SQL 可能涉及到的索引） 是 idx_uid_stat，但實際上（key）用的卻是全表掃描
我們知道 MySQL 是基於成本來選擇是基於全表掃描還是選擇某個索引來執行最終的執行計劃的，所以看起來是全表掃描的成本小於基於 idx_uid_stat 索引執行的成本，不過我的第一感覺很奇怪，這條 SQL 雖然是回表，但它的 limit 是 1，也就是說只選擇了滿足 uid = 5837661 中的其中一條語句，就算回表也只回一條記錄，這種成本幾乎可以忽略不計，優化器怎麼會選擇全表掃描呢。
當然懷疑歸懷疑，為了查看 MySQL 優化器為啥選擇了全表掃描，我打開了 optimizer_trace 來一探究竟
畫外音：在MySQL 5.6 及之後的版本中，我們可以使用 optimizer trace 功能查看優化器生成執行計劃的整個過程
使用 optimizer_trace 的具體過程如下
SET optimizer_trace="enabled=on"; // 打開 optimizer_trace
SELECT * FROM order_info where uid = 5837661 order by id asc limit 1
SELECT * FROM information_schema.OPTIMIZER_TRACE; // 查看執行計劃表
SET optimizer_trace="enabled=off"; // 關閉 optimizer_trace
MySQL 優化器首先會計算出全表掃描的成本，然後選出該 SQL 可能涉及到到的所有索引並且計算索引的成本，然後選出所有成本最小的那個來執行，來看下 optimizer trace 給出的關鍵資訊
{
"rows_estimation": [
{
"table": "`rebate_order_info`",
"range_analysis": {
"table_scan": {
"rows": 21155996,
"cost": 4.45e6 // 全表掃描成本
}
},
...
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "idx_uid_stat",
"ranges": [
"5837661 <= uid <= 5837661"
],
"index_dives_for_eq_ranges": true,
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
"rows": 255918,
"cost": 307103, // 使用idx_uid_stat索引的成本
"chosen": true
}
],
"chosen_range_access_summary": { // 經過上面的各個成本比較後選擇的最終結果
"range_access_plan": {
"type": "range_scan",
"index": "idx_uid_stat", // 可以看到最終選擇了idx_uid_stat這個索引來執行
"rows": 255918,
"ranges": [
"58376617 <= uid <= 58376617"
]
},
"rows_for_plan": 255918,
"cost_for_plan": 307103,
"chosen": true
}
}
...
可以看到全表掃描的成本是 4.45e6，而選擇索引 idx_uid_stat 的成本是 307103，遠小於全表掃描的成本，而且從最終的選擇結果（chosen_range_access_summary）來看，確實也是選擇了 idx_uid_stat 這個索引，但為啥從 explain 看到的選擇是執行 PRIMARY 也就是全表掃描呢，難道這個執行計劃有誤？
仔細再看了一下這個執行計劃，果然發現了貓膩，執行計劃中有一個 reconsidering_access_paths_for_index_ordering 選擇引起了我的注意
{
"reconsidering_access_paths_for_index_ordering": {
"clause": "ORDER BY",
"index_order_summary": {
"table": "`rebate_order_info`",
"index_provides_order": true,
"order_direction": "asc",
"index": "PRIMARY", // 可以看到選擇了主鍵索引
"plan_changed": true,
"access_type": "index_scan"
}
}
}
這個選擇表示由於排序的原因再進行了一次索引選擇優化，由於我們的 SQL 使用了 id 排序（order by id asc limit 1）,優化器最終選擇了 PRIMARY 也就是全表掃描來執行，也就是說這個選擇會無視之前的基於索引成本的選擇，為什麼會有這樣的一個選項呢，主要原因如下：
The short explanation is that the optimizer thinks — or should I say hopes — that scanning the whole table (which is already sorted by the id field) will find the limited rows quick enough, and that this will avoid a sort operation. So by trying to avoid a sort, the optimizer ends-up losing time scanning the table.
從這段解釋可以看出主要原因是由於我們使用了 order by id asc 這種基於 id 的排序寫法，優化器認為排序是個昂貴的操作，所以為了避免排序，並且它認為 limit n 的 n 如果很小的話即使使用全表掃描也能很快執行完，這樣使用全表掃描也就避免了 id 的排序（全表掃描其實也就是基於 id 主鍵的聚簇索引的掃描，本身就是基於 id 排好序的）
如果這個選擇是對的那也罷了，然而實際上這個優化卻是有 bug 的！實際選擇 idx_uid_stat 執行會快得多（只要 28 ms）！網上有不少人回饋這個問題，而且出現這個問題基本只與 SQL 中出現 order by id asc limit n這種寫法有關，如果 n 比較小很大概率會走全表掃描，如果 n 比較大則會選擇正確的索引。
這個 bug 最早追溯到 2014 年，不少人都呼籲官方及時修正這個bug，可能是實現比較困難，直到 MySQL 5.7，8.0 都還沒解決，所以在官方修復前我們要盡量避免這種寫法，那麼怎麼避免呢，主要有兩種方案
- 使用 force index 來強制使用指定的索引，如下：
select * from order_info force index(idx_uid_stat) where uid = 5837661 order by id asc limit 1
這種寫法雖然可以，但不夠優雅，如果這個索引被廢棄了咋辦？於是有了第二種比較優雅的方案
- 使用 order by (id+0) 方案，如下
select * from order_info where uid = 5837661 order by (id+0) asc limit 1
這種方案也可以讓優化器選擇正確的索引，更推薦！
巨人的肩膀
- mysql 優化器 bug //4zsw5.cn/L1zEi
最後，求關注，原創不易，需要一些正回饋，歡迎大家關注我的公眾號「碼海」，一起進階，一起牛逼
