99%的人會(huì)踩MySQL這個(gè)坑！
圖片來(lái)自 包圖網(wǎng)
這周收到一個(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 是基于成本來(lái)選擇是基于全表掃描還是選擇某個(gè)索引來(lái)執(zhí)行最終的執(zhí)行計(jì)劃的，所以看起來(lái)是全表掃描的成本小于基于 idx_uid_stat 索引執(zhí)行的成本。
不過(guò)我的第一感覺(jué)很奇怪，這條 SQL 雖然是回表，但它的 limit 是 1，也就是說(shuō)只選擇了滿足 uid = 5837661 中的其中一條語(yǔ)句，就算回表也只回一條記錄，這種成本幾乎可以忽略不計(jì)，優(yōu)化器怎么會(huì)選擇全表掃描呢。
為了查看 MySQL 優(yōu)化器為啥選擇了全表掃描，我打開(kāi)了 optimizer_trace 來(lái)一探究竟。
畫(huà)外音：在 MySQL 5.6 及之后的版本中，我們可以使用 optimizer trace 功能查看優(yōu)化器生成執(zhí)行計(jì)劃的整個(gè)過(guò)程。
使用 optimizer_trace 的具體過(guò)程如下：
- SET optimizer_trace="enabled=on"; // 打開(kāi) 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è)來(lái)執(zhí)行。
來(lái)看下 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)過(guò)上面的各個(gè)成本比較后選擇的最終結(jié)果
- "range_access_plan": {
- "type": "range_scan",
- "index": "idx_uid_stat", // 可以看到最終選擇了idx_uid_stat這個(gè)索引來(lái)執(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)來(lái)看，確實(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 也就是全表掃描來(lái)執(zhí)行。
也就是說(shuō)這個(gè)選擇會(huì)無(wú)視之前的基于索引成本的選擇，為什么會(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è)問(wèn)題，而且出現(xiàn)這個(gè)問(wèn)題基本只與 SQL 中出現(xiàn) order by id asc limit n這種寫(xiě)法有關(guān)，如果 n 比較小很大概率會(huì)走全表掃描，如果 n 比較大則會(huì)選擇正確的索引。
這個(gè) bug 最早追溯到 2014 年，不少人都呼吁官方及時(shí)修正這個(gè) bug，可能是實(shí)現(xiàn)比較困難，直到 MySQL 5.7，8.0 都還沒(méi)解決。
所以在官方修復(fù)前我們要盡量避免這種寫(xiě)法，如果一定要用這種寫(xiě)法，怎么辦呢，主要有兩種方案。
①使用 force index 來(lái)強(qiáng)制使用指定的索引，如下：
- select * from order_info force index(idx_uid_stat) where uid = 5837661 order by id asc limit 1
這種寫(xiě)法雖然可以，但不夠優(yōu)雅，如果這個(gè)索引被廢棄了咋辦?于是有了第二種比較優(yōu)雅的方案。
②使用 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è)無(wú)用的 0，但足以騙過(guò)優(yōu)化器)，優(yōu)化器認(rèn)為此時(shí)基于全表掃描會(huì)更耗性能，于是會(huì)選擇基于成本大小的方式來(lái)選擇索引。
作者：坤哥，前獨(dú)角獸技術(shù)專家，現(xiàn)創(chuàng)業(yè)者，持續(xù)分享個(gè)人的成長(zhǎng)收獲。
編輯：陶家龍
出處：轉(zhuǎn)載自公眾號(hào)碼海(ID：seaofcode)
