晚上十一點(diǎn)半線上訂單庫(kù)的 CPU 突然飆到 80%一條 select 把整張訂單表從第一個(gè)數(shù)據(jù)頁一路啃到最后一個(gè)數(shù)據(jù)頁慢查詢?nèi)罩纠锛t得刺眼。這種場(chǎng)面我接過不少每次排查到最后原因往往不在 SQL 語法上而在于沒把這條 SQL 的生命周期給看透。MySQL 里一次全表掃描并不是“掃全表”三個(gè)字就能概括的它從觸發(fā)到執(zhí)行再到收尾中間要過優(yōu)化器、存儲(chǔ)引擎、Buffer Pool、排序臨時(shí)文件好幾道關(guān)任何一道環(huán)節(jié)出了問題結(jié)果都是大量無用 IO 和 CPU 空轉(zhuǎn)。今天這篇就把全表掃描的四段生命周期——觸發(fā)、決策、執(zhí)行、收尾——一節(jié)一節(jié)切開講清楚每一步 MySQL 內(nèi)部到底在做什么、為什么這么做、哪些環(huán)節(jié)最容易翻車。適合剛接觸 MySQL 性能調(diào)優(yōu)的開發(fā)者、被慢查詢反復(fù)折磨的運(yùn)維以及所有想知道“一條 select 到底經(jīng)歷了什么”的人。1. 哪些 SQL 會(huì)把 MySQL 逼成全表掃描六大高發(fā)場(chǎng)景全表掃描不是隨機(jī)發(fā)生的絕大多數(shù)時(shí)候是優(yōu)化器在“沒得選”或者“不想選”的情況下做出的決定。我整理了自己在生產(chǎn)環(huán)境里見過的高發(fā)場(chǎng)景按出現(xiàn)頻率排個(gè)序你對(duì)照自己的慢查詢?nèi)罩究椿灸軐?duì)上。1.1 條件列沒有索引最常見也最直白這是最基礎(chǔ)的情況。where條件里的列既不在主鍵上也沒有普通索引優(yōu)化器想走索引都找不到路只能從聚簇索引的第一個(gè)葉子節(jié)點(diǎn)開始把整棵 B 樹的所有葉子節(jié)點(diǎn)全量讀一遍。-- user_phone 列沒有索引 SELECT * FROM users WHERE user_phone 13800138000;這種 SQL 執(zhí)行的時(shí)候InnoDB 不知道哪一頁里有這個(gè)手機(jī)號(hào)只能把整張表的所有數(shù)據(jù)頁都翻一遍期間Handler_read_rnd_next這個(gè)狀態(tài)變量會(huì)飆升它統(tǒng)計(jì)的就是“隨機(jī)讀下一頁”的次數(shù)在全表掃描場(chǎng)景下基本等于表里的行數(shù)。1.2 隱式類型轉(zhuǎn)換導(dǎo)致索引失效這個(gè)是我見過最冤枉的坑。表的字段是 varchar但你傳入的參數(shù)是數(shù)字MySQL 會(huì)把字段列本身轉(zhuǎn)成數(shù)字再比較函數(shù)一作用在列上索引就廢了。-- mobile 列是 varchar(20)索引建得好好的 SELECT * FROM users WHERE mobile 13912345678;MySQL 的隱式轉(zhuǎn)換規(guī)則里字符串和數(shù)字比較時(shí)會(huì)把字符串轉(zhuǎn)成浮點(diǎn)數(shù)相當(dāng)于執(zhí)行了CAST(mobile AS DOUBLE)。一旦索引列被函數(shù)包裹優(yōu)化器就知道這個(gè)索引沒法用了老老實(shí)實(shí)全表掃。想要驗(yàn)證很簡(jiǎn)單EXPLAIN一眼就能看出來type 從ref變成ALL同時(shí)extra里可能出現(xiàn)Using where。1.3 條件列上套了函數(shù)或計(jì)算這個(gè)比隱式轉(zhuǎn)換更明顯但也經(jīng)常有人忽略SELECT * FROM orders WHERE DATE(created_at) 2024-06-01;created_at上就算有索引也用不上因?yàn)閮?yōu)化器無法對(duì)這個(gè)表達(dá)式做范圍推導(dǎo)。標(biāo)準(zhǔn)的解法是改成范圍查詢created_at 2024-06-01 AND created_at 2024-06-02這樣既保留索引起始點(diǎn)又能利用索引的范圍掃描能力。1.4 LIKE 前置通配符%開頭的模糊查詢like abc%還能走索引因?yàn)榍熬Y是確定的B 樹可以根據(jù)前三個(gè)字符定位。但like %abc%沒有任何前綴信息索引樹的節(jié)點(diǎn)順序幫不上忙優(yōu)化器只能認(rèn)為全表掃描更劃算。1.5 優(yōu)化器判斷“索引不如全表掃”小表場(chǎng)景很多剛?cè)腴T的同學(xué)看到EXPLAIN結(jié)果是ALL就慌其實(shí)不用。如果一張表只有 200 行InnoDB 讀完整張表也就三五個(gè)數(shù)據(jù)頁還是順序讀。這種情況下要走索引反而要多一次回表操作CPU 成本和隨機(jī) IO 成本全上去了優(yōu)化器不傻它會(huì)果斷選擇全表。MySQL 的這個(gè)判斷依據(jù)是成本模型8.0 里SERVER和ENGINE兩套成本表都可以自定義默認(rèn)配置下順序讀一個(gè)數(shù)據(jù)頁的成本是 1.0隨機(jī)讀是 4.0memory_block_read_cost為 0.25。當(dāng)表特別小而索引回表代價(jià)高時(shí)全表掃描的估算成本反而最低。1.6 OR 連接條件且其中一個(gè)分支無索引SELECT * FROM orders WHERE status 1 OR amount 10000;如果status有索引而amount沒有優(yōu)化器沒法對(duì)兩條分支分別走索引再合并結(jié)果因?yàn)閍mount 10000這個(gè)分支只能全表掃。最終 MySQL 會(huì)直接選擇全表掃描整張表因?yàn)闊o論如何都要涉及全量判斷。這種 SQL 改造思路是把OR拆成兩個(gè)查詢?cè)賃NION寫起來麻煩但效果立竿見影。2. 優(yōu)化器是怎么“拍板”選全表掃描的成本模型與估算邏輯很多人以為“全表掃描”是執(zhí)行的時(shí)候臨時(shí)決定的其實(shí)不是。真正拍板的是優(yōu)化器它在解析完 SQL 之后、執(zhí)行之前就用一套成本模型把所有執(zhí)行方案都算了一遍然后挑一個(gè)“算下來最便宜”的。2.1 成本模型的兩個(gè)關(guān)鍵角色I(xiàn)O 成本和 CPU 成本MySQL 8.0 里成本估算被拆成兩部分。IO 成本指的是讀取數(shù)據(jù)頁的開銷CPU 成本指的是每行數(shù)據(jù)做條件判斷、投影操作時(shí)消耗的 CPU 周期。兩張成本字典表server_cost和engine_cost存在mysql庫(kù)下我一般會(huì)查一遍確認(rèn)生產(chǎn)環(huán)境的成本參數(shù)是不是默認(rèn)值SELECT * FROM mysql.engine_cost; SELECT * FROM mysql.server_cost;默認(rèn)情況下InnoDB 里順序讀取一個(gè)數(shù)據(jù)頁的成本是 1.0隨機(jī)讀取是 4.0而每處理一行數(shù)據(jù)的 CPU 成本是 0.1 左右。全表掃描走的是聚簇索引的葉子節(jié)點(diǎn)鏈本質(zhì)上是順序讀所以它的 IO 成本約等于“總頁數(shù) × 1.0”這個(gè)數(shù)字在各種執(zhí)行方案里通常是最穩(wěn)定的。2.2 行數(shù)估算一切決策的地基卻可能虛高全表掃描的成本估算依賴于一個(gè)關(guān)鍵輸入表里大概有多少行、涉及多少個(gè)數(shù)據(jù)頁。聽起來簡(jiǎn)單實(shí)際很坑。InnoDB 對(duì)這些行數(shù)的統(tǒng)計(jì)來自統(tǒng)計(jì)采樣information_schema.tables里的TABLE_ROWS是預(yù)估值不是精確值。它通過隨機(jī)抽取幾個(gè) B 樹索引頁除以采樣比例推算出來的誤差在 10% 到 20% 很正常。更麻煩的是如果這張表的大多數(shù)行已經(jīng)被刪除但空間沒被回收統(tǒng)計(jì)信息里記錄的頁面數(shù)量不會(huì)立刻減少優(yōu)化器會(huì)認(rèn)為這張表還有很多頁要讀從而在多個(gè)執(zhí)行方案里把全表掃描估得更貴。反過來也有問題如果一張表的統(tǒng)計(jì)信息很久沒更新實(shí)際上已經(jīng)膨脹了幾倍優(yōu)化器卻按老數(shù)據(jù)估算誤以為全表掃描很便宜照樣選全表。所以運(yùn)維上有個(gè)習(xí)慣我一直保留對(duì)頻繁批量刪除、大批量導(dǎo)入的表定期執(zhí)行ANALYZE TABLE刷新統(tǒng)計(jì)信息避免優(yōu)化器拿著過期的“地圖”做路線規(guī)劃。2.3 eq_range_index_dive_limit一個(gè)參數(shù)如何影響索引選擇MySQL 在估算“等值查詢能命中多少行”時(shí)有兩種方式。當(dāng)where條件里的等值數(shù)量小于等于eq_range_index_dive_limit默認(rèn) 200時(shí)優(yōu)化器會(huì)實(shí)際去索引里“潛水”統(tǒng)計(jì)每個(gè)等值對(duì)應(yīng)的記錄數(shù)如果大于 200就改用索引的基數(shù)估算。這個(gè)細(xì)節(jié)對(duì)全表掃描的決策影響很大。曾經(jīng)有同事在張大表上跑一條IN (幾百個(gè)值)的查詢優(yōu)化器因?yàn)榈戎禂?shù)量超過閾值改用基數(shù)估算把某個(gè)索引的選擇性估得過高反倒選擇了全表掃描其他條件列。排查時(shí)調(diào)大eq_range_index_dive_limit再重新跑執(zhí)行計(jì)劃立刻變成走索引。2.4 優(yōu)化器追蹤讓決策過程無所遁形光知道結(jié)論不夠我還想看看優(yōu)化器到底是怎么算的這時(shí)候就用optimizer_trace。開啟之后MySQL 會(huì)把優(yōu)化器考慮過的每個(gè)方案、每一筆成本都記錄成 JSONSET SESSION optimizer_traceenabledon; EXPLAIN SELECT * FROM orders WHERE status 1; SELECT * FROM information_schema.OPTIMIZER_TRACE\G輸出里有rows_estimation和considered_execution_plans能看到“為什么選了全表掃描”的完整賬本。有一次排查一條明明有條件卻全表掃的 SQL就是這個(gè) trace 告訴我優(yōu)化器在該條件列的統(tǒng)計(jì)信息里看到“超過一半的行都滿足這個(gè)條件”算下來走索引回表的成本比全表還高這才放下了心。3. InnoDB 執(zhí)行掃描時(shí)到底在忙什么預(yù)讀、快照讀與鎖的糾纏優(yōu)化器拍板之后執(zhí)行器開始調(diào)用 InnoDB 的接口正式干活。這一階段是生命周期里最核心的一段也是 CPU 和 IO 壓力真正起來的地方。很多人以為全表掃描就是把數(shù)據(jù)頁挨個(gè)讀一遍實(shí)際沒這么簡(jiǎn)單。3.1 掃描起點(diǎn)聚簇索引葉子節(jié)點(diǎn)鏈InnoDB 的表本身是一棵以主鍵為 key 的 B 樹葉子節(jié)點(diǎn)存整行數(shù)據(jù)。全表掃描就是從這棵樹的最左葉子節(jié)點(diǎn)開始順著葉子節(jié)點(diǎn)之間的雙向鏈表一路往右讀讀到最右端再結(jié)束。如果你建的是普通索引情況會(huì)復(fù)雜一些。比如select * from t where name like %xxx%優(yōu)化器如果選擇掃普通索引的 B 樹雖然每個(gè)葉子節(jié)點(diǎn)里存的不是整行而是索引列 主鍵值掃描的數(shù)據(jù)量更小但因?yàn)椴榈氖?每條記錄都要根據(jù)主鍵回聚簇索引取完整行——這個(gè)“回表”操作可能引起大量隨機(jī) IO。所以很多情況下優(yōu)化器寧可直接掃聚簇索引省掉回表環(huán)節(jié)。3.2 預(yù)讀機(jī)制MySQL 提前把“還沒用到”的頁搬進(jìn) Buffer Pool順序掃描葉子節(jié)點(diǎn)時(shí)InnoDB 不會(huì)一個(gè)頁一個(gè)頁地讀它有一個(gè)很聰明的預(yù)讀機(jī)制。MySQL 8.0 里默認(rèn)開啟線性預(yù)讀innodb_read_ahead_threshold默認(rèn)是 56意思是如果 InnoDB 檢測(cè)到正在順序讀取某個(gè)數(shù)據(jù)文件中的連續(xù) 56 個(gè)頁就會(huì)異步發(fā)起額外 IO把后續(xù)的更多頁提前加載到 Buffer Pool 里。這個(gè)機(jī)制讓全表掃描的“順序讀”變得非常高效但也帶來一個(gè)副作用Buffer Pool 會(huì)被掃描進(jìn)來的頁大量占用把業(yè)務(wù)經(jīng)常訪問的熱點(diǎn)數(shù)據(jù)頁擠出去。我見過一次性select *跑完把整個(gè) Buffer Pool 的命中率從 99% 砸到 70% 的案例。所以大表掃描之后show status like Innodb_buffer_pool_read_hit_rate這個(gè)指標(biāo)務(wù)必盯一眼掉得太快就該考慮給掃描任務(wù)加限流或者把 Buffer Pool 里的 LRU 鏈表按 young 和 old 區(qū)域的比例調(diào)一下。3.3 一致性快照讀掃描過程中為什么看不到別的事務(wù)的修改全表掃描期間別的會(huì)話可能正在 insert、update、delete但掃描看到的卻是一個(gè)“凍結(jié)在某一時(shí)刻”的數(shù)據(jù)視圖。這靠的是 MVCC多版本并發(fā)控制和 undo log 的配合。當(dāng)這條 select 開啟事務(wù)并執(zhí)行第一次讀時(shí)InnoDB 會(huì)生成一個(gè) ReadView里面記錄了當(dāng)前活躍事務(wù)的 ID。掃描過程中每讀到一行數(shù)據(jù)InnoDB 會(huì)比較該行記錄上的事務(wù) ID 和 ReadView 里的快照信息如果該行的最新版本事務(wù) ID 在 ReadView 的活躍事務(wù)列表里說明這行正在被別的事務(wù)修改InnoDB 會(huì)順著 undo log 往前找到該行在快照時(shí)點(diǎn)的舊版本返回舊值如果修改事務(wù)已經(jīng)提交而且提交時(shí)刻比快照更晚同樣需要從 undo log 里取舊版本。這就是一個(gè)長(zhǎng)期跑著的全表掃描為什么會(huì)導(dǎo)致 undo log 膨脹的原因——它一直占著舊版本其他事務(wù)提交后更新產(chǎn)生的 undo 信息不能及時(shí)清理undo tablespace越占越大甚至把磁盤塞滿。掃描時(shí)間越長(zhǎng)這個(gè)風(fēng)險(xiǎn)越大。3.4 掃描會(huì)不會(huì)鎖住整張表鎖粒度帶來的錯(cuò)覺全表掃描不等于全表加鎖。默認(rèn)的REPEATABLE READ隔離級(jí)別下這條 select 走的是一致性非鎖定讀它通過 MVCC 讀快照不需要對(duì)掃描過的行加共享鎖。所以你跑一條大掃描并不能阻止別人更新數(shù)據(jù)。但如果這條 select 寫成了select ... for update或者lock in share mode事情就不一樣了。InnoDB 會(huì)在掃描過程中對(duì)每一行加鎖而且在REPEATABLE READ下為了避免幻讀它還會(huì)對(duì)掃描范圍內(nèi)的間隙加 gap lock導(dǎo)致區(qū)間內(nèi)其他事務(wù)的插入被阻塞。更糟的是MySQL 的加鎖行為是按掃描過程逐行鎖定的掃描到哪一行鎖就加到哪一行并不是一次性鎖全表但最終效果上其他事務(wù)往這張表插入數(shù)據(jù)的動(dòng)作會(huì)大面積受阻。這就是為什么我在生產(chǎn)環(huán)境特別忌諱對(duì)線上大表直接執(zhí)行select * from ... for update去“導(dǎo)出數(shù)據(jù)”看起來只讀實(shí)際會(huì)對(duì)后續(xù)寫入造成強(qiáng)烈的鎖競(jìng)爭(zhēng)。4. 掃描完成后數(shù)據(jù)去了哪里臨時(shí)表、排序與結(jié)果返回全表掃描把行撈出來之后生命周期并沒有結(jié)束。如果需要排序、去重、分組MySQL 還得在內(nèi)存或磁盤上做二次加工然后才通過 MySQL 協(xié)議把結(jié)果集推送給客戶端。這一段的損耗往往被低估。4.1 排序的三層路徑內(nèi)存排序到磁盤歸并order by是全表掃描伴侶。掃描出來的數(shù)據(jù)往往不是最終順序MySQL 需要排序。排序優(yōu)先使用sort_buffer_size默認(rèn) 256KB這塊內(nèi)存。如果待排序數(shù)據(jù)量不大直接在內(nèi)存里做快速排序一條 SQL 就跑完了。一旦數(shù)據(jù)量超過 sort buffer 的容量MySQL 就把數(shù)據(jù)分塊每排好一塊就寫到磁盤臨時(shí)文件最后再把多個(gè)有序分塊做歸并排序。歸并過程會(huì)額外讀寫臨時(shí)文件IO 影響比想象中大得多。遇到大結(jié)果集的order by我習(xí)慣觀察SHOW STATUS LIKE Sort_merge_passes這個(gè)值如果大于 20說明排序大量走了磁盤臨時(shí)文件光排序這一項(xiàng)就把 SQL 拖垮了。4.2 Filesort 還是索引排序優(yōu)化器怎么選如果order by的字段正好是索引列InnoDB 掃描索引本身就是有序的根本不需要額外排序這就是Using index優(yōu)化。但如果排序字段不在索引上或者order by和where條件用了不同的索引優(yōu)化器只能在掃描完之后再做 filesort。這里有個(gè)典型的決策場(chǎng)景where status 1 order by created_at。如果 status 選擇性差走 status 索引掃描大量行再按 created_at 文件排序可能比直接全表掃描 文件排序更慢。優(yōu)化器會(huì)把兩條路徑的成本都算一遍最終選便宜的那個(gè)。從這個(gè)角度說某些“全表掃描 filesort”的執(zhí)行計(jì)劃其實(shí)是優(yōu)化器在兩害相權(quán)之后的理性選擇。4.3 臨時(shí)表分組和去重的隱形成本group by、distinct、union這類操作往往都會(huì)涉及臨時(shí)表。老版本的 MySQL 會(huì)把臨時(shí)表建在磁盤的 tmpdir 上磁盤臨時(shí)表沒有索引數(shù)據(jù)量一大查詢只能反復(fù)全表掃臨時(shí)表速度雪崩。MySQL 8.0 有個(gè)重要改進(jìn)臨時(shí)表統(tǒng)一使用 TempTable 引擎先在內(nèi)存里維護(hù)內(nèi)存占用超過temptable_max_ram默認(rèn) 1GB以后才轉(zhuǎn)磁盤。但這不意味著你可以無限制地在內(nèi)存里跑大分組轉(zhuǎn)磁盤依賴tmpdir所在文件系統(tǒng)的 IO 性能如果 tmpdir 和業(yè)務(wù)數(shù)據(jù)在同一塊普通磁盤上競(jìng)爭(zhēng)會(huì)非常明顯。我一般會(huì)把 tmpdir 指向 tmpfs但前提是確認(rèn)服務(wù)器內(nèi)存足夠別把系統(tǒng)擠爆。4.4 把結(jié)果發(fā)給客戶端網(wǎng)絡(luò) IO 也可能是瓶頸全部加工完之后MySQL 通過協(xié)議把結(jié)果集的每一行發(fā)送給客戶端這一階段消耗的網(wǎng)絡(luò) IO 也是全表掃描生命周期的一部分。Net_send相關(guān)狀態(tài)變量統(tǒng)計(jì)了發(fā)送等待時(shí)間如果客戶端接收速度慢MySQL 的服務(wù)線程會(huì)一直掛著。這就是為什么我一直強(qiáng)調(diào)select *全表掃描加limit也不能盲目樂觀。limit 10看起來只返回 10 行但如果前 10 行要等掃描到表尾才能確定——比如order by一個(gè)非索引字段——那前面所有數(shù)據(jù)還是得掃完算完limit只影響“發(fā)送多少行”不影響“內(nèi)部掃描多少行”。5. 一次完整全表掃描的現(xiàn)場(chǎng)還原從 EXPLAIN 到狀態(tài)變量把前面幾段串起來我用一條典型 SQL 走一遍全流程。假設(shè)有一張 500 萬行的訂單表結(jié)構(gòu)大致是這樣CREATE TABLE orders ( id BIGINT PRIMARY KEY, user_id INT, status TINYINT, amount DECIMAL(10,2), created_at DATETIME, KEY idx_user_id (user_id) ) ENGINEInnoDB;業(yè)務(wù)端報(bào)了一個(gè)慢查詢SELECT COUNT(*) FROM orders WHERE status 1;status 沒有索引。我的排查鏈路是這樣一步步展開的。第一步看執(zhí)行計(jì)劃EXPLAIN SELECT COUNT(*) FROM orders WHERE status 1;結(jié)果里type ALL、rows 520萬左右、extra是Using where。這說明優(yōu)化器選擇了全表掃描掃描過程中對(duì)每一行做 status 條件過濾只計(jì)數(shù)符合條件的數(shù)據(jù)。第二步查實(shí)際執(zhí)行代價(jià)。通過 optimizer trace 能看到全表掃描的成本組成500 萬行數(shù)據(jù)大約占了 8 萬個(gè)數(shù)據(jù)頁IO 成本 80000 × 1.0CPU 成本 500萬 × 0.1全表掃描總成本估算比“掃 idx_user_id 索引再回表”低了幾個(gè)量級(jí)因?yàn)?status 條件沒有可用的二級(jí)索引。這就徹底解釋了優(yōu)化器的選擇它沒有“犯錯(cuò)”是結(jié)構(gòu)上就沒有優(yōu)化空間。第三步觀測(cè)執(zhí)行期的狀態(tài)變化。開一個(gè)會(huì)話不斷采樣SHOW GLOBAL STATUS LIKE Innodb_pages_read; SHOW GLOBAL STATUS LIKE Handler_read_rnd_next; SHOW GLOBAL STATUS LIKE Select_scan;運(yùn)行期間Handler_read_rnd_next從幾萬漲到幾百萬Innodb_pages_read也在持續(xù)攀升說明確實(shí)在逐頁讀數(shù)據(jù)。SQL 跑完后這幾個(gè)數(shù)字的增長(zhǎng)量基本可以估算出本次掃描的頁數(shù)與行數(shù)分析慢查詢時(shí)很有用。第四步看收尾階段的狀態(tài)。如果 SQL 里帶排序或分組重點(diǎn)關(guān)注Sort_merge_passes、Created_tmp_disk_tables這些計(jì)數(shù)。這條COUNT(*)沒有排序需求所以這些指標(biāo)沒有變化說明它的生命周期止步于“掃描 過濾計(jì)數(shù)”沒有再往下游流轉(zhuǎn)。整個(gè)還原下來這條 SQL 的瓶頸清晰可見status沒有索引優(yōu)化器只能全表。要解決它方案是在status上建索引讓優(yōu)化器可以走二級(jí)索引掃描。但注意如果 status 分布很集中比如 99% 的行都是 status 1優(yōu)化器建了索引也可能仍然選全表因?yàn)閽叨?jí)索引再回表取數(shù)據(jù)確實(shí)沒有全表順序讀劃算。到時(shí)候你EXPLAIN看到的還是一個(gè)ALL別慌這是正常行為加索引的意義在于等值查詢能精準(zhǔn)定位到那一小撮不同的值。6. 常見問題速查這幾種“全表掃描”根本不用管排查多了之后你會(huì)發(fā)現(xiàn)并不是所有全表掃描都需要治理。區(qū)分“該治”和“不該治”的邊界比見一個(gè)殺一個(gè)重要得多。6.1 明確不該治理的三種情況第一單表數(shù)據(jù)量只有幾千行且查詢條件返回結(jié)果集很大。比如一張配置表本來就 300 行全表掃描的成本和走索引回表的成本差距可以忽略甚至全表掃描更優(yōu)。這種情況下EXPLAIN里的ALL不值得花時(shí)間。第二OLAP 場(chǎng)景里的統(tǒng)計(jì)報(bào)表比如每天凌晨執(zhí)行的匯總查詢本來就要掃描幾百萬行做聚合。建索引對(duì)這類查詢沒有意義它的核心訴求是讓掃描盡量順、盡量少占用業(yè)務(wù)高峰期資源。第三select count(*) from t這類無過濾條件的計(jì)數(shù)查詢。InnoDB 8.0 仍然需要逐行數(shù)因?yàn)?MVCC 導(dǎo)致每一行對(duì)每個(gè)事務(wù)的可見性可能不同它不可能像 MyISAM 那樣存一個(gè)計(jì)數(shù)器直接返回。所以別看不起它它掃描全表是生存需要不是優(yōu)化器偷懶。6.2 全表掃描類慢查詢的排查清單遇到真的需要治理的全表掃描我一般按這個(gè)順序排查先確認(rèn)條件列有沒有索引show index from table一眼的事。再確認(rèn)有沒有隱式類型轉(zhuǎn)換。用EXPLAIN看type和key再看表結(jié)構(gòu)字段類型與傳參類型是否一致。用optimizer trace確認(rèn)優(yōu)化器不是“被迫”選擇全表掃描。如果是統(tǒng)計(jì)信息不準(zhǔn)執(zhí)行ANALYZE TABLE刷新。看掃描時(shí)有沒有把 Buffer Pool 沖垮。掃描后抽查Innodb_buffer_pool_read_hit_rate如果明顯低于 95%建議要么給掃描任務(wù)錯(cuò)峰要么考慮用備份庫(kù)跑分析查詢。看排序、分組、臨時(shí)表環(huán)節(jié)是否額外放大損耗。Sort_merge_passes和Created_tmp_disk_tables是重點(diǎn)檢查對(duì)象。6.3 一個(gè)容易忽略的坑全表掃描在 binlog 和主從復(fù)制下的后果全表掃描的 SQL 本身不改數(shù)據(jù)但它可能引發(fā)后續(xù)的數(shù)據(jù)變更操作放慢間接影響復(fù)制。前面講過長(zhǎng)事務(wù)占著舊版本的 undo log 不釋放從庫(kù)的復(fù)制線程如果也跑了一個(gè)長(zhǎng)查詢主庫(kù)的 binlog 在從庫(kù)執(zhí)行時(shí)會(huì)因?yàn)殒i等待而滯后主從延遲就是這么拖出來的。所以生產(chǎn)環(huán)境我有一條鐵律大查詢要么走只讀從庫(kù)或分析庫(kù)要么在低峰期執(zhí)行。線上主庫(kù)的 Buffer Pool 和 undo 資源經(jīng)不起長(zhǎng)掃描反復(fù)蹂躪。6.4 工具與命令速查調(diào)試和復(fù)盤時(shí)這幾條命令是我最常用的直接貼出來# 查看當(dāng)前正在執(zhí)行的 SQL重點(diǎn)看 Time 列 SELECT * FROM information_schema.processlist WHERE command ! Sleep; # 統(tǒng)計(jì) SQL 語句維度的掃描行數(shù)和耗時(shí) SELECT digest_text, sum_rows_examined, sum_rows_sent, count_star FROM performance_schema.events_statements_summary_by_digest ORDER BY sum_rows_examined DESC LIMIT 20; # 全表掃描沖突的鎖等待來源 SELECT * FROM sys.innodb_lock_waits;用 performance_schema 的 digest 表定位高頻全表掃描比翻慢查詢?nèi)罩靖煲驗(yàn)槁罩局挥涗洺^閾值的個(gè)別語句而 digest 表能按模板聚合直接排出“最費(fèi)行”的 TOP20 語句。7. 一點(diǎn)私貨我對(duì)全表掃描的重新認(rèn)識(shí)早幾年我有一個(gè)很暴力的執(zhí)念慢查詢?nèi)罩纠锍霈F(xiàn)ALL等于事故必須先加索引再說。后來看多了才發(fā)現(xiàn)全表掃描不只是“性能事故”它也是一種能力。MySQL 在數(shù)據(jù)量不大的場(chǎng)景下用順序讀高效地解決問題在無法用索引的情況下保證查詢?nèi)匀荒軋?zhí)行這套機(jī)制本身設(shè)計(jì)得并不差。真正值得注意的是那些因?yàn)椤霸O(shè)計(jì)缺陷”而被迫全表掃描的查詢——索引失效、統(tǒng)計(jì)信息過期、SQL 寫法不規(guī)范。每一次這種掃描都是對(duì)存儲(chǔ)引擎資源的一次無差別暴力讀取短期看是某一條 SQL 慢了長(zhǎng)期看它擠占 Buffer Pool、膨脹 undo log、拖累主從同步是系統(tǒng)性風(fēng)險(xiǎn)的積累。我個(gè)人這幾年把排查慢 SQL 的習(xí)慣固定成了三層第一層看執(zhí)行計(jì)劃第二層看 optimizer trace 的賬本第三層看狀態(tài)變量和 performance_schema。每一條異常的全表掃描都會(huì)走一遍這三步找到它從觸發(fā)到執(zhí)行再到收尾的完整生命周期問題基本就能定死。我希望這套方法論對(duì)你也有用下次再被慢查詢喊起來的時(shí)候至少你手上有一把解剖刀能像庖丁解牛那樣順著紋理下刀而不是一刀剁下去。