這個(gè) MySQL bug 99% 的人會(huì)踩坑!

點(diǎn)擊關(guān)注公眾號(hào)，Java干貨及時(shí)送達(dá)

這周收到一個(gè) sentry 報(bào)警，如下 SQL 查詢超時(shí)了。

select?*?from?order_info?where?uid?=?5837661?order?by?id?asc?limit?1

執(zhí)行show create table order_info?發(fā)現(xiàn)這個(gè)表其實(shí)是有加索引的

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 會(huì)命中 idx_uid_stat 這個(gè)索引，但實(shí)際執(zhí)行 explain 查看

explain?select?*?from?order_info?where?uid?=?5837661?order?by?id?asc?limit?1

可以看到它的 possible_keys（此 SQL 可能涉及到的索引） 是 idx_uid_stat，但實(shí)際上（key）用的卻是全表掃描

我們知道 MySQL 是基于成本來選擇是基于全表掃描還是選擇某個(gè)索引來執(zhí)行最終的執(zhí)行計(jì)劃的，所以看起來是全表掃描的成本小于基于 idx_uid_stat 索引執(zhí)行的成本，不過我的第一感覺很奇怪，這條 SQL 雖然是回表，但它的 limit 是 1，也就是說只選擇了滿足 uid = 5837661 中的其中一條語句，就算回表也只回一條記錄，這種成本幾乎可以忽略不計(jì)，優(yōu)化器怎么會(huì)選擇全表掃描呢。

為了查看 MySQL 優(yōu)化器為啥選擇了全表掃描，我打開了 optimizer_trace 來一探究竟

畫外音：在MySQL 5.6 及之后的版本中，我們可以使用 optimizer trace 功能查看優(yōu)化器生成執(zhí)行計(jì)劃的整個(gè)過程

使用 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;????//?查看執(zhí)行計(jì)劃表
SET?optimizer_trace="enabled=off";?//?關(guān)閉?optimizer_trace

MySQL 優(yōu)化器首先會(huì)計(jì)算出全表掃描的成本，然后選出該 SQL 可能涉及到的所有索引并且計(jì)算索引的成本，然后選出所有成本最小的那個(gè)來執(zhí)行，來看下 optimizer trace 給出的關(guān)鍵信息

{
??"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)過上面的各個(gè)成本比較后選擇的最終結(jié)果
?????????"range_access_plan":?{
?????????????"type":?"range_scan",
?????????????"index":?"idx_uid_stat",??//?可以看到最終選擇了idx_uid_stat這個(gè)索引來執(zhí)行
?????????????"rows":?255918,
?????????????"ranges":?[
?????????????"58376617?<=?uid?<=?58376617"
?????????????]
?????????},
?????????"rows_for_plan":?255918,
?????????"cost_for_plan":?307103,
?????????"chosen":?true
?????????}
?????????}??
????...

可以看到全表掃描的成本是 4.45e6，而選擇索引 idx_uid_stat 的成本是 307103，遠(yuǎn)小于全表掃描的成本，而且從最終的選擇結(jié)果（chosen_range_access_summary）來看，確實(shí)也是選擇了 idx_uid_stat 這個(gè)索引，但為啥從 explain 看到的選擇是執(zhí)行 PRIMARY 也就是全表掃描呢，難道這個(gè)執(zhí)行計(jì)劃有誤？

仔細(xì)再看了一下這個(gè)執(zhí)行計(jì)劃，果然發(fā)現(xiàn)了貓膩，執(zhí)行計(jì)劃中有一個(gè) 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"
????????}
????}
}

這個(gè)選擇表示由于排序的原因再進(jìn)行了一次索引選擇優(yōu)化，由于我們的 SQL 使用了 id 排序（order by id asc limit 1）,優(yōu)化器最終選擇了 PRIMARY 也就是全表掃描來執(zhí)行，也就是說這個(gè)選擇會(huì)無視之前的基于索引成本的選擇，為什么會(huì)有這樣的一個(gè)選項(xiàng)呢，主要原因如下：

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 的排序?qū)懛?，?yōu)化器認(rèn)為排序是個(gè)昂貴的操作，所以為了避免排序，并且它認(rèn)為?limit n 的 n 如果很小的話即使使用全表掃描也能很快執(zhí)行完，所以它選擇了全表掃描，也就避免了 id 的排序（全表掃描其實(shí)就是基于 id 主鍵的聚簇索引的掃描，本身就是基于 id 排好序的）

如果這個(gè)選擇是對(duì)的那也罷了，然而實(shí)際上這個(gè)優(yōu)化卻是有 bug 的！實(shí)際選擇 idx_uid_stat 執(zhí)行會(huì)快得多（只要 28 ms）！網(wǎng)上有不少人反饋這個(gè)問題，而且出現(xiàn)這個(gè)問題基本只與 SQL 中出現(xiàn)?order by id asc limit n這種寫法有關(guān)，如果 n 比較小很大概率會(huì)走全表掃描，如果 n 比較大則會(huì)選擇正確的索引。

這個(gè) bug 最早追溯到 2014 年，不少人都呼吁官方及時(shí)修正這個(gè)bug，可能是實(shí)現(xiàn)比較困難，直到 MySQL 5.7，8.0 都還沒解決，所以在官方修復(fù)前我們要盡量避免這種寫法，如果一定要用這種寫法，怎么辦呢，主要有兩種方案

1. 使用 force index 來強(qiáng)制使用指定的索引，如下：

select?*?from?order_info?force?index(idx_uid_stat)?where?uid?=?5837661?order?by?id?asc?limit?1

這種寫法雖然可以，但不夠優(yōu)雅，如果這個(gè)索引被廢棄了咋辦？于是有了第二種比較優(yōu)雅的方案

2. 使用 order by (id+0) 方案，如下

select?*?from?order_info?where?uid?=?5837661?order?by?(id+0)?asc?limit?1

這種方案也可以讓優(yōu)化器選擇正確的索引，更推薦！為什么這個(gè) trick 可以呢，因?yàn)榇?SQL 雖然是按 id 排序的，但在 id 上作了加法這樣耗時(shí)的操作(雖然只是加個(gè)無用的 0，但足以騙過優(yōu)化器)，優(yōu)化器認(rèn)為此時(shí)基于全表掃描會(huì)更耗性能，于是會(huì)選擇基于成本大小的方式來選擇索引

巨人的肩膀

• mysql 優(yōu)化器 bug http://4zsw5.cn/L1zEi

往
期
推
薦
1、致歉！抖音Semi Design承認(rèn)參考阿里Ant Design
2、對(duì)比7種分布式事務(wù)方案，還是偏愛阿里開源的Seata，真香！
3、Redis存儲(chǔ)結(jié)構(gòu)體信息，選hash還是string？
4、掃盲 docker 常用命令
5、最全分布式Session解決方案
6、21 款 yyds 的 IDEA插件
7、真香！用 IDEA 神器看源碼，效率真高！
點(diǎn)分享
點(diǎn)收藏
點(diǎn)點(diǎn)贊
點(diǎn)在看