99%的人會踩MySQL這個坑！「來源： ｜51CTO技術棧 ID：blog51cto」
關注
51CTO技術棧
，悅享技術，成就 CTO 夢想
“
今天跟大家聊一個 MySQL 的 Bug，這個 Bug 99% 的人會踩坑，不信咱們一起來看一看。
圖片來自 Pexels
這周收到一個 sentry 報警，如下 SQL 查詢超時了。
select * from order_info where uid = 5837661orderbyidasclimit1
執行 show create table order_info 發現這個表其實是有加索引的：
CREATETABLE`order_info` （
`id`bigint（20） unsignedNOTNULL AUTO_INCREMENT，
`uid`int（11） unsigned，
`order_status` tinyint（3） DEFAULTNULL，
。。。 省略其它欄位和索引
PRIMARY KEY （`id`），
KEY`idx_uid_stat` （`uid`，`order_status`），
） ENGINE=InnoDBDEFAULTCHARSET=utf8
理論上執行上述 SQL 會命中 idx_uid_stat 這個索引，但實際執行 explain 檢視：
explainselect * from order_info where uid = 5837661orderbyidasclimit1
可以看到它的 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 = 5837661orderbyidasclimit1
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 forceindex（idx_uid_stat） where uid = 5837661orderbyidasclimit1
這種寫法雖然可以，但不夠優雅，如果這個索引被廢棄了咋辦？於是有了第二種比較優雅的方案。
②使用 order by （id+0） 方案，如下：
select * from order_info where uid = 5837661orderby （id+0） asclimit1
這種方案也可以讓最佳化器選擇正確的索引，更推薦！
為什麼這個 trick 可以呢，因為此 SQL 雖然是按 id 排序的，但在 id 上作了加法這樣耗時的操作（雖然只是加個無用的 0，但足以騙過最佳化器），最佳化器認為此時基於全表掃描會更耗效能，於是會選擇基於成本大小的方式來選擇索引。
