這個 MySQL bug 99% 的人會踩坑！
1、公司產(chǎn)品太多了，怎么實現(xiàn)一次登錄產(chǎn)品互通？ 2、用了BigDecimal就不會資損？了解下BigDecimal這五個坑 3、一個依賴搞定 Spring Boot 反爬蟲，防止接口盜刷！ 4、千萬不要把 Request 傳遞到異步線程里面！有坑！ 5、不卷了！入職字節(jié)一周就果斷跑了。
最近收到一個 Sentry 報警，如下 SQL 查詢超時了。
select * from order_info where uid = 5837661 order by id asc limit 1執(zhí)行 show create table order_info 發(fā)現(xiàn)這個表其實是有加索引的：
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
理論上執(zhí)行上述 SQL 會命中 idx_uid_stat 這個索引，但實際執(zhí)行 explain 查看
explain select * from order_info where uid = 5837661 order by id asc limit 1
可以看到它的 possible_keys（此 SQL 可能涉及到的索引） 是 idx_uid_stat，但實際上（key）用的卻是全表掃描。
我們知道 MySQL 是基于成本來選擇是基于全表掃描還是選擇某個索引來執(zhí)行最終的執(zhí)行計劃的，所以看起來是全表掃描的成本小于基于 idx_uid_stat 索引執(zhí)行的成本。
不過我的第一感覺很奇怪，這條 SQL 雖然是回表，但它的 limit 是 1。也就是說只選擇了滿足 uid = 5837661 中的其中一條語句，就算回表也只回一條記錄，這種成本幾乎可以忽略不計，優(yōu)化器怎么會選擇全表掃描呢。
為了查看 MySQL 優(yōu)化器為啥選擇了全表掃描，我打開了 optimizer_trace 來一探究竟。
畫外音：在MySQL 5.6 及之后的版本中，我們可以使用 optimizer trace 功能查看優(yōu)化器生成執(zhí)行計劃的整個過程。
使用 optimizer_trace 的具體過程如下：
SET optimizer_trace="enabled=on"; // 打開 optimizer_traceSELECT * FROM order_info where uid = 5837661 order by id asc limit 1SELECT * FROM information_schema.OPTIMIZER_TRACE; // 查看執(zhí)行計劃表SET optimizer_trace="enabled=off"; // 關閉 optimizer_trace
MySQL 優(yōu)化器首先會計算出全表掃描的成本，然后選出該 SQL 可能涉及到的所有索引并且計算索引的成本，然后選出所有成本最小的那個來執(zhí)行。
來看下 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": { // 經(jīng)過上面的各個成本比較后選擇的最終結果"range_access_plan": {"type": "range_scan","index": "idx_uid_stat", // 可以看到最終選擇了idx_uid_stat這個索引來執(zhí)行"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 看到的選擇是執(zhí)行 PRIMARY 也就是全表掃描呢？難道這個執(zhí)行計劃有誤？
仔細再看了一下這個執(zhí)行計劃，果然發(fā)現(xiàn)了貓膩。
執(zhí)行計劃中有一個 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"}}}
這個選擇表示：由于排序的原因再進行了一次索引選擇優(yōu)化。
由于我們的 SQL 使用了 id 排序（order by id asc limit 1），優(yōu)化器最終選擇了 PRIMARY 也就是全表掃描來執(zhí)行。也就是說這個選擇會無視之前的基于索引成本的選擇。
為什么會有這樣的一個選項呢？
主要原因如下：
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 的排序寫法，優(yōu)化器認為排序是個昂貴的操作，所以為了避免排序，并且它認為 limit n 的 n 如果很小的話即使使用全表掃描也能很快執(zhí)行完，所以它選擇了全表掃描。也就避免了 id 的排序（全表掃描其實就是基于 id 主鍵的聚簇索引的掃描，本身就是基于 id 排好序的）。
如果這個選擇是對的那也罷了，然而實際上這個優(yōu)化卻是有 bug 的！實際選擇 idx_uid_stat 執(zhí)行會快得多（只要 28 ms）！
網(wǎng)上有不少人反饋這個問題，而且出現(xiàn)這個問題基本只與 SQL 中出現(xiàn) order by id asc limit n 這種寫法有關。如果 n 比較小很大概率會走全表掃描，如果 n 比較大則會選擇正確的索引。
這個 bug 最早追溯到 2014 年，不少人都呼吁官方及時修正這個 bug。可能是實現(xiàn)比較困難，直到 MySQL 5.7，8.0 都還沒解決，所以在官方修復前我們要盡量避免這種寫法，如果一定要用這種寫法，怎么辦呢？
主要有兩種方案
1. 使用 force index 來強制使用指定的索引。
如下：
select * from order_info force index(idx_uid_stat) where uid = 5837661 order by id asc limit 1這種寫法雖然可以，但不夠優(yōu)雅，如果這個索引被廢棄了咋辦？于是有了第二種比較優(yōu)雅的方案。
2. 使用 order by (id+0) 方案。
如下：
select * from order_info where uid = 5837661 order by (id+0) asc limit 1這種方案也可以讓優(yōu)化器選擇正確的索引，更推薦！
為什么這個 trick 可以呢？因為此 SQL 雖然是按 id 排序的，但在 id 上作了加法這樣耗時的操作（雖然只是加個無用的 0，但足以騙過優(yōu)化器），優(yōu)化器認為此時基于全表掃描會更耗性能，于是會選擇基于成本大小的方式來選擇索引。
往期熱門文章：
1、線上MySQL的自增id用盡怎么辦？被面試官干趴下了！ 2、計算機專業(yè)會不會成為下一個土木？ 3、xxl-job驚艷的設計，怎能叫人不愛 4、ArrayList#subList這四個坑，一不小心就中招 5、面試官：大量請求 Redis 不存在的數(shù)據(jù)，從而影響數(shù)據(jù)庫，該如何解決？ 6、MySQL 暴跌！ 7、超越 Xshell！號稱下一代 Terminal 終端神器，用完愛不釋手！ 8、IDEA 官宣全新默認 UI，太震撼了！！ 9、讓你直呼「臥槽」的 GitHub 項目！ 10、Kafka又笨又重，為啥不選Redis？