行計劃看懂SQL調(diào)優(yōu)：一次慢查詢優(yōu)化的完整實(shí)戰(zhàn))
聊起SQL調(diào)優(yōu)很多一線研發(fā)團(tuán)隊其實(shí)早就形成了一套標(biāo)準(zhǔn)動作先把慢查詢?nèi)罩敬蜷_把那些跑了幾秒甚至幾十秒的SQL撈出來再用EXPLAIN看執(zhí)行計劃最后針對計劃里的全表掃描、文件排序去補(bǔ)索引、改SQL。三步走完一條慢SQL從定位到優(yōu)化落地往往不超過半小時。這中間最核心、也最值得花時間掌握的就是執(zhí)行計劃分析——它能讓你在幾秒內(nèi)判斷一條SQL到底是缺索引、關(guān)聯(lián)方式選錯還是查詢邏輯本身就有問題。我見過不少業(yè)務(wù)同事剛開始接觸調(diào)優(yōu)時習(xí)慣把慢SQL甩給DBA或者到處找人問其實(shí)只要你學(xué)會看執(zhí)行計劃里的幾列關(guān)鍵信息很多問題的答案自己就能讀出來問人的時候也能問到點(diǎn)子上。這篇文章會圍繞執(zhí)行計劃分析展開用一個非常典型的電商查詢場景從頭到尾走一遍完整調(diào)優(yōu)流程從開啟慢日志、定位問題SQL到EXPLAIN分析、設(shè)計索引再到優(yōu)化前后的執(zhí)行計劃對比。內(nèi)容面向有一定SQL基礎(chǔ)、但還沒系統(tǒng)接觸過執(zhí)行計劃的開發(fā)者也適合那些已經(jīng)會用EXPLAIN但停留在看到typeALL就知道要加索引層面的朋友——你會在后面的避坑章節(jié)里看到不少執(zhí)行計劃不會直接告訴你、但線上實(shí)戰(zhàn)里非常要命的細(xì)節(jié)。1. 為什么慢SQL看了執(zhí)行計劃就能定位很多人第一次接觸EXPLAIN下意識會覺得這是某個高深工具。其實(shí)它的本質(zhì)特別樸素一條SQL交給MySQL之后優(yōu)化器要先決定按什么順序訪問哪些表、用哪個索引、大概讀多少行數(shù)據(jù)這個決策過程的結(jié)果以一張表的形式返回給你就是執(zhí)行計劃。換句話說執(zhí)行計劃是MySQL對這條SQL怎么做的一個自白書分析它你看到的不是結(jié)果對不對而是過程慢不慢、慢在哪。1.1 執(zhí)行計劃到底在表達(dá)什么我把執(zhí)行計劃理解成一份開車導(dǎo)航路線圖。SQL是目的地優(yōu)化器是導(dǎo)航引擎執(zhí)行計劃就是它給出的路線方案。同一段路可能有不同走法走高速索引掃描快但有限制條件走國道全表掃描什么車都能走但慢中途可能還要繞道文件排序、臨時表。EXPLAIN把路線攤開給你看哪一段是高速、哪一段是土路、哪一段在繞圈子全都能在計劃里找到對應(yīng)標(biāo)記。所以分析執(zhí)行計劃的第一步不是背字段而是建立一種直覺這條SQL當(dāng)前是有索引可用且用上了還是明明有索引卻沒用還是壓根沒有可用索引只能全表掃。這三種情況對應(yīng)的優(yōu)化動作完全不同而判斷依據(jù)就是type、key、rows、Extra這幾列。把這個直覺訓(xùn)練出來你會發(fā)現(xiàn)自己看慢SQL的速度快得驚人。1.2 一線團(tuán)隊為什么都推崇這條調(diào)優(yōu)鏈路大廠都在用這個說法核心指的并不是某個神秘商業(yè)工具而是一套被反復(fù)驗(yàn)證過的協(xié)作方法慢日志負(fù)責(zé)發(fā)現(xiàn)問題EXPLAIN負(fù)責(zé)定位病灶索引設(shè)計負(fù)責(zé)解決問題再用EXPLAIN驗(yàn)證問題是否真的解決。四個環(huán)節(jié)形成一個閉環(huán)任何人都能按照這個流程獨(dú)立完成調(diào)優(yōu)不需要碰運(yùn)氣。我之前在一個項目里帶過幾個剛轉(zhuǎn)崗過來的同事他們沒有專職DBA支持靠的就是這套流程。第一次遇到線上接口超時按流程打開慢日志發(fā)現(xiàn)一條訂單列表查詢跑了3秒鐘EXPLAIN一看typeALL、rows二十多萬Extra里還有Using filesort和Using temporary。順著執(zhí)行計劃的信息給查詢條件補(bǔ)了一個聯(lián)合索引2分鐘改完線上耗時從3秒降到200毫秒。整個過程沒有用到任何商業(yè)工具也沒有經(jīng)驗(yàn)玄學(xué)就是老老實(shí)實(shí)看執(zhí)行計劃、對癥下藥。這也是為什么我會把看懂執(zhí)行計劃列為SQL調(diào)優(yōu)第一優(yōu)先級技能——它是所有后續(xù)操作的基礎(chǔ)。2. 執(zhí)行計劃5個關(guān)鍵列一眼看穿SQL真實(shí)成本EXPLAIN返回的結(jié)果集一共有幾十個字段但日常調(diào)優(yōu)真正高頻使用、而且信息量最大的基本集中在5列type、key、rows、filtered、Extra?？炊@5列就能覆蓋絕大多數(shù)慢SQL的初始判斷。2.1 最該先看的type列訪問方式?jīng)Q定性能級別type列描述的是MySQL如何查找數(shù)據(jù)行從好到差大致是system const eq_ref ref range index ALL。實(shí)際業(yè)務(wù)中你看到最多的是ref、range、index、ALL這四個。ref使用非唯一索引或前綴索引查找一次索引查找能命中多行這是索引用對了的典型標(biāo)志。range索引范圍掃描比如WHERE里帶BETWEEN、、、IN這類條件用索引去圈定一個范圍。index全索引掃描從索引頭掃到尾相當(dāng)于把整棵索引樹遍歷一遍。這雖然用了索引但成本往往不低。ALL全表掃描這是最需要警惕的級別。執(zhí)行計劃里出現(xiàn)ALL意味著MySQL打算把整張表的數(shù)據(jù)一行一行讀出來過濾數(shù)據(jù)量一大慢是必然的。我自己看執(zhí)行計劃的習(xí)慣是第一眼先掃type列如果看到ALL先別管別的列這條SQL大概率就是缺索引或者索引失效的問題如果看到index需要注意它是不是在掃描一棵很大的輔助索引如果看到ref或range說明訪問路徑本身是健康的問題可能要往排序、臨時表、或者返回行數(shù)太多的方向排查。2.2 索引利用度與成本估算key、rows、filteredkey列告訴你這條SQL最終選了哪個索引沒有走索引時這一列是NULL。它和type是配套的typeref但keyidx_name說明這個索引確實(shí)被用上了typeALL且key為NULL說明優(yōu)化器找不到可用索引或者找到了但覺得用了不如不用。rows列表示優(yōu)化器估算的需要掃描的行數(shù)注意是估算值不是實(shí)際掃描行數(shù)。它體現(xiàn)的是這條SQL大概要摸多少行數(shù)據(jù)數(shù)值越小通常越好。filtered表示經(jīng)過WHERE條件過濾后剩余行數(shù)占掃描行數(shù)的百分比。filtered100說明過濾前后行數(shù)沒變化filtered10說明掃描100行最終只留下10行。我舉一個真實(shí)觀察某條SQL的rows是20萬filtered卻是5%說明它掃描了20萬行但最終有用的只有1萬行。這種情況往往是索引選擇不合適讓MySQL在一個過大的范圍內(nèi)做過濾優(yōu)化方向是讓rows降下來、filtered升上去。rows和filtered結(jié)合起來看能判斷掃描的代價和篩選的效率是否匹配。注意rows是CBO基于成本的優(yōu)化器拍腦袋估出來的它依賴統(tǒng)計信息而統(tǒng)計信息不一定實(shí)時準(zhǔn)確。千萬不要把rows當(dāng)成實(shí)際行數(shù)去寫監(jiān)控告警它只適合做趨勢判斷和方案對比。2.3 Extra列里的隱藏信號Extra列是執(zhí)行計劃里信息最豐富、但也最容易被忽略的一列。它不會說你錯了而是用幾個固定短語暗示執(zhí)行過程中額外做了什么。我最關(guān)注三個信號。Using filesort表示MySQL需要額外做一次文件排序來滿足ORDER BY。它不代表真的在磁盤上建了文件而是說沒法直接利用索引順序取數(shù)得自己排一遍。一條SQL如果出現(xiàn)filesort且數(shù)據(jù)量大排序就是主要瓶頸。優(yōu)化方式通常是讓排序字段進(jìn)入索引由索引天然保證順序。Using temporary表示執(zhí)行過程中創(chuàng)建了臨時表常見于GROUP BY、DISTINCT、多表關(guān)聯(lián)去重等場景。臨時表可能落在內(nèi)存也可能落到磁盤落到磁盤就會明顯變慢。出現(xiàn)這個信號時我會重點(diǎn)檢查是不是可以改寫SQL減少去重和分組或者讓分組字段進(jìn)入索引。Using index是最好的一種信號表示覆蓋索引生效查詢需要的字段都在索引里不需要回表取數(shù)據(jù)行。覆蓋索引是SQL調(diào)優(yōu)里性價比非常高的優(yōu)化手段后面我會專門展開講一次實(shí)際案例。3. 完整實(shí)操從慢日志到索引優(yōu)化的全流程說再多不如跑一遍。這一節(jié)我用一個典型的商戶訂單查詢場景分別演示優(yōu)化前和優(yōu)化后的執(zhí)行計劃讓你直觀看到一條SQL是怎么從全表掃描一步一步變成索引高效命中的。3.1 場景建模與慢SQL復(fù)現(xiàn)模擬項目X里有一張訂單表結(jié)構(gòu)大致如下CREATE TABLE order_info ( id bigint NOT NULL AUTO_INCREMENT, order_no varchar(32) NOT NULL, user_id bigint NOT NULL, seller_id bigint NOT NULL, amount decimal(12,2) NOT NULL, order_status tinyint NOT NULL, create_time datetime NOT NULL, PRIMARY KEY (id) ) ENGINEInnoDB;表中有一百多萬條測試數(shù)據(jù)。業(yè)務(wù)反饋說某個商家訂單查詢接口在晚高峰超時定位到一條慢SQL需求是查某個店鋪?zhàn)罱恢艿挠唵瘟斜戆聪聠螘r間倒序分頁返回。SELECT order_no, amount, order_status, create_time FROM order_info WHERE seller_id 10086 AND create_time 2024-11-01 00:00:00 AND create_time 2024-11-08 00:00:00 ORDER BY create_time DESC LIMIT 20;這條SQL表面看沒有任何問題條件清晰、字段干凈。第一次EXPLAIN之前我甚至懷疑是不是分頁深翻頁的問題結(jié)果一分析問題比想象的直接。3.2 第一次EXPLAIN看問題出在哪在SQL前面加上EXPLAIN關(guān)鍵字直接執(zhí)行EXPLAIN SELECT order_no, amount, order_status, create_time FROM order_info WHERE seller_id 10086 AND create_time 2024-11-01 00:00:00 AND create_time 2024-11-08 00:00:00 ORDER BY create_time DESC LIMIT 20;執(zhí)行計劃結(jié)果如下idselect_typetabletypekeyrowsfilteredExtra1SIMPLEorder_infoALLNULL12000000.01Using where; Using filesort看到這張表我的判斷流程是這樣的typeALL說明走了全表掃描意味著這張表目前只有主鍵索引WHERE條件里的seller_id和create_time都沒有索引可用key為NULL印證了這個判斷rows是120萬意味著MySQL計劃把整張表掃一遍雖然filtered0.01看起來過濾效果好但這是在掃了120萬行之后才過濾出來的結(jié)果Extra里還帶著Using filesort說明ORDER BY create_time還得額外排序。全表掃描加上文件排序兩個慢的因素疊加這條SQL在百萬級數(shù)據(jù)量下跑出好幾秒完全正常。優(yōu)化方向也就明確了給seller_id和create_time建一個聯(lián)合索引讓過濾條件走索引同時讓排序也能利用索引順序。3.3 索引設(shè)計與前后對比從全表掃描到ref/range的蛻變這里有一個非常關(guān)鍵的判斷點(diǎn)聯(lián)合索引的字段順序怎么排。WHERE條件是seller_id等值加create_time范圍ORDER BY是create_time倒序。按照最左前綴原則我把等值條件字段seller_id放前面把排序字段create_time放后面建立索引ALTER TABLE order_info ADD INDEX idx_seller_create (seller_id, create_time);建完后重新執(zhí)行EXPLAINidselect_typetabletypekeyrowsfilteredExtra1SIMPLEorder_inforangeidx_seller_create3200100.00Using index conditiontype從ALL變成了range說明這次不是全表掃描了而是通過索引去圈定范圍rows從120萬直接降到3200掃描量縮小了將近400倍這是性能提升最核心的來源Extra里的Using filesort也消失了因?yàn)镺RDER BY create_time正好命中索引的第二個字段MySQL可以順著索引順序直接取數(shù)排序被索引天然解決。這條SQL的真實(shí)耗時從優(yōu)化前的2.8秒降到了220毫秒雖然沒有夸張到數(shù)10倍那么懸殊但也已經(jīng)是超過一個數(shù)量級的提升。如果數(shù)據(jù)量到千萬級這個差距還會進(jìn)一步拉開。執(zhí)行計劃前后對比清楚地說明一切的優(yōu)化效果都體現(xiàn)在type、rows、Extra這三個字段的變化上。注意索引不是越多越好。聯(lián)合索引idx_seller_create雖然把查詢和排序都覆蓋了但如果業(yè)務(wù)里還有大量按user_id單獨(dú)查詢的場景就得額外評估是否單獨(dú)建user_id索引。加索引前先看這個索引到底服務(wù)哪些高頻SQL避免出現(xiàn)為一條SQL建一個索引的過度設(shè)計。4. 避坑實(shí)錄執(zhí)行計劃不會告訴你的5個細(xì)節(jié)執(zhí)行計劃能幫你定位80%的問題但剩下20%的坑它不會直接告訴你。這些細(xì)節(jié)來自我在多個項目里的真實(shí)排障記錄每條都對應(yīng)過線上事故或者反復(fù)調(diào)優(yōu)的教訓(xùn)。4.1 聯(lián)合索引的最左前綴與順序選擇很多初學(xué)者加索引的時候完全不思考字段順序今天看這個查詢慢就建一個含這個字段的索引明天看那個查詢慢再建一個。結(jié)果就是一張表五六個索引每個索引還有三四列寫入性能被拖累查詢也沒快到哪里去。最左前綴原則其實(shí)很好理解聯(lián)合索引(a, b, c)只能用來加速a、a,b、a,b,c這三種條件的查詢想從b或者c開條件索引就用不上。所以設(shè)計聯(lián)合索引時第一要務(wù)是找到出現(xiàn)頻率最高的等值條件字段放最前面范圍條件放后面。如果排序字段也想靠索引最好放在等值條件之后、范圍條件之前或者之后具體要看排序方向和數(shù)據(jù)分布。我之前遇到一個把范圍字段放最前面的案例索引是(create_time, seller_id)查詢條件是seller_idxxx AND create_timexxx執(zhí)行計劃顯示typeindex走了全索引掃描。原因就是create_time作為第一列只能命中范圍無法直接定位seller_idMySQL只好把整棵索引樹掃一遍再過濾。調(diào)整順序重建成(seller_id, create_time)后type立刻變成了range。4.2 rows是估算值別當(dāng)實(shí)數(shù)用執(zhí)行計劃里的rows是優(yōu)化器根據(jù)統(tǒng)計信息估算出來的不是真實(shí)的掃描行數(shù)。統(tǒng)計信息基于采樣采樣過時了rows就會失真。我見過一條SQL執(zhí)行計劃里rows顯示8000實(shí)際卻掃了80萬行——原因是表數(shù)據(jù)發(fā)生了大量增刪統(tǒng)計信息沒有及時更新。真正要看準(zhǔn)確行數(shù)可以在分析前先執(zhí)行ANALYZE TABLE刷新統(tǒng)計信息或者在EXPLAIN后加FORMATTREE看更細(xì)節(jié)的信息。做方案對比時看rows的變化趨勢完全夠用但不要基于rows去做精確的容量評估。4.3 索引失效的場景遠(yuǎn)比想象的多有些SQL看起來有索引可用執(zhí)行計劃里type卻是ALL這種情況基本可以歸為索引沒被用上。最常見的三種失效原因我都踩過。第一種是函數(shù)包裹。WHERE YEAR(create_time) 2024create_time列被函數(shù)包住索引自然失效。解決辦法是改寫條件讓列獨(dú)立在表達(dá)式一側(cè)create_time 2024-01-01 AND create_time 2025-01-01。第二種是隱式類型轉(zhuǎn)換。字段是varchar類型SQL里卻傳了數(shù)字或者字段是intSQL里卻傳了字符串。MySQL會統(tǒng)一轉(zhuǎn)類型再比較導(dǎo)致索引失效而且這種問題很難從肉眼發(fā)現(xiàn)。第三種是LIKE查詢以通配符開頭。WHERE name LIKE %張%最前面的%讓MySQL無法從索引樹的某個錨點(diǎn)開始找只能全部遍歷。如果業(yè)務(wù)確實(shí)需要這種模糊匹配通常只能走全文索引或者搜索引擎靠普通B樹索引解決不了。4.4 深分頁的優(yōu)化LIMIT并不總是很貴執(zhí)行計劃顯示索引都用上了type也在range級別但SQL還是慢這時候多半是分頁太深了。LIMIT 100000, 20的意思是先掃描前100020行丟掉前100000行只返回最后20行。掃描的行數(shù)不少但執(zhí)行計劃的rows可能顯示得很小因?yàn)閮?yōu)化器是按理想情況下估算的。深分頁的常見優(yōu)化方案有兩種第一種是延遲關(guān)聯(lián)先查出主鍵或唯一鍵再回表拿完整數(shù)據(jù)。原理是利用索引樹上的主鍵定位比直接掃大范圍數(shù)據(jù)行快得多把代價大的回表推遲到最后20行才做。第二種是記錄上一頁最后一條數(shù)據(jù)的某個有序字段值下一頁用WHERE create_time 上頁最小值來取跳過已經(jīng)翻過的數(shù)據(jù)這個方案實(shí)際效果最好但需要業(yè)務(wù)配合改造。4.5 覆蓋索引讓查詢徹底不回表Extra里的Using index指的是查詢所需的所有字段都包含在索引中MySQL只需要掃描索引樹就能返回結(jié)果完全不需要回表讀取聚簇索引里的數(shù)據(jù)行。這個優(yōu)化空間在大字段回表場景下特別明顯。舉個例子查詢只需要order_no和amount兩個小字段而創(chuàng)建的這個聯(lián)合索引恰好包含這兩個字段那么執(zhí)行計劃里就會看到Using index?；乇黹_銷雖然單次不大但百萬行級別的掃描下每一次回表都可能觸發(fā)額外的隨機(jī)I/O累積起來就是秒級差距。所以在設(shè)計高頻查詢的索引時我會盡量把SELECT里出現(xiàn)的字段在不影響索引體積的前提下追加到索引末尾。這就是常說的覆蓋索引。個人習(xí)慣每完成一個聯(lián)合索引設(shè)計我都會檢查一下這個索引能否覆蓋線上Top 10高頻查詢的SELECT字段。能覆蓋就盡量覆蓋這是成本最低、收益最穩(wěn)定的提速手段。5. 順手整理一份執(zhí)行計劃排查速查表如果你剛接觸執(zhí)行計劃記不住各種字段的優(yōu)先級可以把下面這張表收藏起來。它是我在實(shí)際項目中反復(fù)用到的一張判斷清單基本覆蓋了慢SQL初始定位的常見路徑。執(zhí)行計劃證據(jù)初步判斷首選優(yōu)化動作typeALL, keyNULL缺索引或索引失效檢查WHERE條件字段是否有合適索引typerange/index用上了索引但掃描范圍偏大收緊條件、調(diào)整聯(lián)合索引字段順序ExtraUsing filesort排序無法利用索引把ORDER BY字段加入索引ExtraUsing temporary分組去重產(chǎn)生臨時表改寫SQL、讓分組字段進(jìn)索引ExtraUsing index覆蓋索引生效保持考慮是否可推廣到更多高頻SQLkey列與實(shí)際預(yù)期不符優(yōu)化器選了另一個索引檢查統(tǒng)計信息必要時用FORCE INDEX做對比這張表不解決所有問題但它能幫你把90%的慢SQL在幾分鐘內(nèi)給出一個正確方向的判斷。這幾次調(diào)優(yōu)經(jīng)驗(yàn)下來我最深的體會是SQL優(yōu)化的核心并不在于會背多少種奇技淫巧而在于你能不能把一個看起來玄乎的性能問題拆解成掃描方式、掃描范圍、額外操作三個可以量化的點(diǎn)然后針對那個最痛的點(diǎn)下手。執(zhí)行計劃正是把這些信息擺在你面前的工具用熟了之后你會發(fā)現(xiàn)自己看慢SQL的心態(tài)會從慌變成穩(wěn)。最后再分享一個小建議如果你所在的項目還完全沒有建立SQL review機(jī)制不妨從今天開始每次上線前把最近一周的慢日志SQL批量跑一遍EXPLAIN把新出現(xiàn)的全表掃描和filesort提前攔在發(fā)布之前。這個習(xí)慣堅持三個月你會回來感謝它。 從執(zhí)行計劃看懂SQL調(diào)優(yōu)：一次慢查詢優(yōu)化的完整實(shí)戰(zhàn)
聊起SQL調(diào)優(yōu)很多一線研發(fā)團(tuán)隊其實(shí)早就形成了一套標(biāo)準(zhǔn)動作先把慢查詢?nèi)罩敬蜷_把那些跑了幾秒甚至幾十秒的SQL撈出來再用EXPLAIN看執(zhí)行計劃最后針對計劃里的全表掃描、文件排序去補(bǔ)索引、改SQL。三步走完一條慢SQL從定位到優(yōu)化落地往往不超過半小時。這中間最核心、也最值得花時間掌握的就是執(zhí)行計劃分析——它能讓你在幾秒內(nèi)判斷一條SQL到底是缺索引、關(guān)聯(lián)方式選錯還是查詢邏輯本身就有問題。我見過不少業(yè)務(wù)同事剛開始接觸調(diào)優(yōu)時習(xí)慣把慢SQL甩給DBA或者到處找人問其實(shí)只要你學(xué)會看執(zhí)行計劃里的幾列關(guān)鍵信息很多問題的答案自己就能讀出來問人的時候也能問到點(diǎn)子上。這篇文章會圍繞執(zhí)行計劃分析展開用一個非常典型的電商查詢場景從頭到尾走一遍完整調(diào)優(yōu)流程從開啟慢日志、定位問題SQL到EXPLAIN分析、設(shè)計索引再到優(yōu)化前后的執(zhí)行計劃對比。內(nèi)容面向有一定SQL基礎(chǔ)、但還沒系統(tǒng)接觸過執(zhí)行計劃的開發(fā)者也適合那些已經(jīng)會用EXPLAIN但停留在看到typeALL就知道要加索引層面的朋友——你會在后面的避坑章節(jié)里看到不少執(zhí)行計劃不會直接告訴你、但線上實(shí)戰(zhàn)里非常要命的細(xì)節(jié)。1. 為什么慢SQL看了執(zhí)行計劃就能定位很多人第一次接觸EXPLAIN下意識會覺得這是某個高深工具。其實(shí)它的本質(zhì)特別樸素一條SQL交給MySQL之后優(yōu)化器要先決定按什么順序訪問哪些表、用哪個索引、大概讀多少行數(shù)據(jù)這個決策過程的結(jié)果以一張表的形式返回給你就是執(zhí)行計劃。換句話說執(zhí)行計劃是MySQL對這條SQL怎么做的一個自白書分析它你看到的不是結(jié)果對不對而是過程慢不慢、慢在哪。1.1 執(zhí)行計劃到底在表達(dá)什么我把執(zhí)行計劃理解成一份開車導(dǎo)航路線圖。SQL是目的地優(yōu)化器是導(dǎo)航引擎執(zhí)行計劃就是它給出的路線方案。同一段路可能有不同走法走高速索引掃描快但有限制條件走國道全表掃描什么車都能走但慢中途可能還要繞道文件排序、臨時表。EXPLAIN把路線攤開給你看哪一段是高速、哪一段是土路、哪一段在繞圈子全都能在計劃里找到對應(yīng)標(biāo)記。所以分析執(zhí)行計劃的第一步不是背字段而是建立一種直覺這條SQL當(dāng)前是有索引可用且用上了還是明明有索引卻沒用還是壓根沒有可用索引只能全表掃。這三種情況對應(yīng)的優(yōu)化動作完全不同而判斷依據(jù)就是type、key、rows、Extra這幾列。把這個直覺訓(xùn)練出來你會發(fā)現(xiàn)自己看慢SQL的速度快得驚人。1.2 一線團(tuán)隊為什么都推崇這條調(diào)優(yōu)鏈路大廠都在用這個說法核心指的并不是某個神秘商業(yè)工具而是一套被反復(fù)驗(yàn)證過的協(xié)作方法慢日志負(fù)責(zé)發(fā)現(xiàn)問題EXPLAIN負(fù)責(zé)定位病灶索引設(shè)計負(fù)責(zé)解決問題再用EXPLAIN驗(yàn)證問題是否真的解決。四個環(huán)節(jié)形成一個閉環(huán)任何人都能按照這個流程獨(dú)立完成調(diào)優(yōu)不需要碰運(yùn)氣。我之前在一個項目里帶過幾個剛轉(zhuǎn)崗過來的同事他們沒有專職DBA支持靠的就是這套流程。第一次遇到線上接口超時按流程打開慢日志發(fā)現(xiàn)一條訂單列表查詢跑了3秒鐘EXPLAIN一看typeALL、rows二十多萬Extra里還有Using filesort和Using temporary。順著執(zhí)行計劃的信息給查詢條件補(bǔ)了一個聯(lián)合索引2分鐘改完線上耗時從3秒降到200毫秒。整個過程沒有用到任何商業(yè)工具也沒有經(jīng)驗(yàn)玄學(xué)就是老老實(shí)實(shí)看執(zhí)行計劃、對癥下藥。這也是為什么我會把看懂執(zhí)行計劃列為SQL調(diào)優(yōu)第一優(yōu)先級技能——它是所有后續(xù)操作的基礎(chǔ)。2. 執(zhí)行計劃5個關(guān)鍵列一眼看穿SQL真實(shí)成本EXPLAIN返回的結(jié)果集一共有幾十個字段但日常調(diào)優(yōu)真正高頻使用、而且信息量最大的基本集中在5列type、key、rows、filtered、Extra?？炊@5列就能覆蓋絕大多數(shù)慢SQL的初始判斷。2.1 最該先看的type列訪問方式?jīng)Q定性能級別type列描述的是MySQL如何查找數(shù)據(jù)行從好到差大致是system const eq_ref ref range index ALL。實(shí)際業(yè)務(wù)中你看到最多的是ref、range、index、ALL這四個。ref使用非唯一索引或前綴索引查找一次索引查找能命中多行這是索引用對了的典型標(biāo)志。range索引范圍掃描比如WHERE里帶BETWEEN、、、IN這類條件用索引去圈定一個范圍。index全索引掃描從索引頭掃到尾相當(dāng)于把整棵索引樹遍歷一遍。這雖然用了索引但成本往往不低。ALL全表掃描這是最需要警惕的級別。執(zhí)行計劃里出現(xiàn)ALL意味著MySQL打算把整張表的數(shù)據(jù)一行一行讀出來過濾數(shù)據(jù)量一大慢是必然的。我自己看執(zhí)行計劃的習(xí)慣是第一眼先掃type列如果看到ALL先別管別的列這條SQL大概率就是缺索引或者索引失效的問題如果看到index需要注意它是不是在掃描一棵很大的輔助索引如果看到ref或range說明訪問路徑本身是健康的問題可能要往排序、臨時表、或者返回行數(shù)太多的方向排查。2.2 索引利用度與成本估算key、rows、filteredkey列告訴你這條SQL最終選了哪個索引沒有走索引時這一列是NULL。它和type是配套的typeref但keyidx_name說明這個索引確實(shí)被用上了typeALL且key為NULL說明優(yōu)化器找不到可用索引或者找到了但覺得用了不如不用。rows列表示優(yōu)化器估算的需要掃描的行數(shù)注意是估算值不是實(shí)際掃描行數(shù)。它體現(xiàn)的是這條SQL大概要摸多少行數(shù)據(jù)數(shù)值越小通常越好。filtered表示經(jīng)過WHERE條件過濾后剩余行數(shù)占掃描行數(shù)的百分比。filtered100說明過濾前后行數(shù)沒變化filtered10說明掃描100行最終只留下10行。我舉一個真實(shí)觀察某條SQL的rows是20萬filtered卻是5%說明它掃描了20萬行但最終有用的只有1萬行。這種情況往往是索引選擇不合適讓MySQL在一個過大的范圍內(nèi)做過濾優(yōu)化方向是讓rows降下來、filtered升上去。rows和filtered結(jié)合起來看能判斷掃描的代價和篩選的效率是否匹配。注意rows是CBO基于成本的優(yōu)化器拍腦袋估出來的它依賴統(tǒng)計信息而統(tǒng)計信息不一定實(shí)時準(zhǔn)確。千萬不要把rows當(dāng)成實(shí)際行數(shù)去寫監(jiān)控告警它只適合做趨勢判斷和方案對比。2.3 Extra列里的隱藏信號Extra列是執(zhí)行計劃里信息最豐富、但也最容易被忽略的一列。它不會說你錯了而是用幾個固定短語暗示執(zhí)行過程中額外做了什么。我最關(guān)注三個信號。Using filesort表示MySQL需要額外做一次文件排序來滿足ORDER BY。它不代表真的在磁盤上建了文件而是說沒法直接利用索引順序取數(shù)得自己排一遍。一條SQL如果出現(xiàn)filesort且數(shù)據(jù)量大排序就是主要瓶頸。優(yōu)化方式通常是讓排序字段進(jìn)入索引由索引天然保證順序。Using temporary表示執(zhí)行過程中創(chuàng)建了臨時表常見于GROUP BY、DISTINCT、多表關(guān)聯(lián)去重等場景。臨時表可能落在內(nèi)存也可能落到磁盤落到磁盤就會明顯變慢。出現(xiàn)這個信號時我會重點(diǎn)檢查是不是可以改寫SQL減少去重和分組或者讓分組字段進(jìn)入索引。Using index是最好的一種信號表示覆蓋索引生效查詢需要的字段都在索引里不需要回表取數(shù)據(jù)行。覆蓋索引是SQL調(diào)優(yōu)里性價比非常高的優(yōu)化手段后面我會專門展開講一次實(shí)際案例。3. 完整實(shí)操從慢日志到索引優(yōu)化的全流程說再多不如跑一遍。這一節(jié)我用一個典型的商戶訂單查詢場景分別演示優(yōu)化前和優(yōu)化后的執(zhí)行計劃讓你直觀看到一條SQL是怎么從全表掃描一步一步變成索引高效命中的。3.1 場景建模與慢SQL復(fù)現(xiàn)模擬項目X里有一張訂單表結(jié)構(gòu)大致如下CREATE TABLE order_info ( id bigint NOT NULL AUTO_INCREMENT, order_no varchar(32) NOT NULL, user_id bigint NOT NULL, seller_id bigint NOT NULL, amount decimal(12,2) NOT NULL, order_status tinyint NOT NULL, create_time datetime NOT NULL, PRIMARY KEY (id) ) ENGINEInnoDB;表中有一百多萬條測試數(shù)據(jù)。業(yè)務(wù)反饋說某個商家訂單查詢接口在晚高峰超時定位到一條慢SQL需求是查某個店鋪?zhàn)罱恢艿挠唵瘟斜戆聪聠螘r間倒序分頁返回。SELECT order_no, amount, order_status, create_time FROM order_info WHERE seller_id 10086 AND create_time 2024-11-01 00:00:00 AND create_time 2024-11-08 00:00:00 ORDER BY create_time DESC LIMIT 20;這條SQL表面看沒有任何問題條件清晰、字段干凈。第一次EXPLAIN之前我甚至懷疑是不是分頁深翻頁的問題結(jié)果一分析問題比想象的直接。3.2 第一次EXPLAIN看問題出在哪在SQL前面加上EXPLAIN關(guān)鍵字直接執(zhí)行EXPLAIN SELECT order_no, amount, order_status, create_time FROM order_info WHERE seller_id 10086 AND create_time 2024-11-01 00:00:00 AND create_time 2024-11-08 00:00:00 ORDER BY create_time DESC LIMIT 20;執(zhí)行計劃結(jié)果如下idselect_typetabletypekeyrowsfilteredExtra1SIMPLEorder_infoALLNULL12000000.01Using where; Using filesort看到這張表我的判斷流程是這樣的typeALL說明走了全表掃描意味著這張表目前只有主鍵索引WHERE條件里的seller_id和create_time都沒有索引可用key為NULL印證了這個判斷rows是120萬意味著MySQL計劃把整張表掃一遍雖然filtered0.01看起來過濾效果好但這是在掃了120萬行之后才過濾出來的結(jié)果Extra里還帶著Using filesort說明ORDER BY create_time還得額外排序。全表掃描加上文件排序兩個慢的因素疊加這條SQL在百萬級數(shù)據(jù)量下跑出好幾秒完全正常。優(yōu)化方向也就明確了給seller_id和create_time建一個聯(lián)合索引讓過濾條件走索引同時讓排序也能利用索引順序。3.3 索引設(shè)計與前后對比從全表掃描到ref/range的蛻變這里有一個非常關(guān)鍵的判斷點(diǎn)聯(lián)合索引的字段順序怎么排。WHERE條件是seller_id等值加create_time范圍ORDER BY是create_time倒序。按照最左前綴原則我把等值條件字段seller_id放前面把排序字段create_time放后面建立索引ALTER TABLE order_info ADD INDEX idx_seller_create (seller_id, create_time);建完后重新執(zhí)行EXPLAINidselect_typetabletypekeyrowsfilteredExtra1SIMPLEorder_inforangeidx_seller_create3200100.00Using index conditiontype從ALL變成了range說明這次不是全表掃描了而是通過索引去圈定范圍rows從120萬直接降到3200掃描量縮小了將近400倍這是性能提升最核心的來源Extra里的Using filesort也消失了因?yàn)镺RDER BY create_time正好命中索引的第二個字段MySQL可以順著索引順序直接取數(shù)排序被索引天然解決。這條SQL的真實(shí)耗時從優(yōu)化前的2.8秒降到了220毫秒雖然沒有夸張到數(shù)10倍那么懸殊但也已經(jīng)是超過一個數(shù)量級的提升。如果數(shù)據(jù)量到千萬級這個差距還會進(jìn)一步拉開。執(zhí)行計劃前后對比清楚地說明一切的優(yōu)化效果都體現(xiàn)在type、rows、Extra這三個字段的變化上。注意索引不是越多越好。聯(lián)合索引idx_seller_create雖然把查詢和排序都覆蓋了但如果業(yè)務(wù)里還有大量按user_id單獨(dú)查詢的場景就得額外評估是否單獨(dú)建user_id索引。加索引前先看這個索引到底服務(wù)哪些高頻SQL避免出現(xiàn)為一條SQL建一個索引的過度設(shè)計。4. 避坑實(shí)錄執(zhí)行計劃不會告訴你的5個細(xì)節(jié)執(zhí)行計劃能幫你定位80%的問題但剩下20%的坑它不會直接告訴你。這些細(xì)節(jié)來自我在多個項目里的真實(shí)排障記錄每條都對應(yīng)過線上事故或者反復(fù)調(diào)優(yōu)的教訓(xùn)。4.1 聯(lián)合索引的最左前綴與順序選擇很多初學(xué)者加索引的時候完全不思考字段順序今天看這個查詢慢就建一個含這個字段的索引明天看那個查詢慢再建一個。結(jié)果就是一張表五六個索引每個索引還有三四列寫入性能被拖累查詢也沒快到哪里去。最左前綴原則其實(shí)很好理解聯(lián)合索引(a, b, c)只能用來加速a、a,b、a,b,c這三種條件的查詢想從b或者c開條件索引就用不上。所以設(shè)計聯(lián)合索引時第一要務(wù)是找到出現(xiàn)頻率最高的等值條件字段放最前面范圍條件放后面。如果排序字段也想靠索引最好放在等值條件之后、范圍條件之前或者之后具體要看排序方向和數(shù)據(jù)分布。我之前遇到一個把范圍字段放最前面的案例索引是(create_time, seller_id)查詢條件是seller_idxxx AND create_timexxx執(zhí)行計劃顯示typeindex走了全索引掃描。原因就是create_time作為第一列只能命中范圍無法直接定位seller_idMySQL只好把整棵索引樹掃一遍再過濾。調(diào)整順序重建成(seller_id, create_time)后type立刻變成了range。4.2 rows是估算值別當(dāng)實(shí)數(shù)用執(zhí)行計劃里的rows是優(yōu)化器根據(jù)統(tǒng)計信息估算出來的不是真實(shí)的掃描行數(shù)。統(tǒng)計信息基于采樣采樣過時了rows就會失真。我見過一條SQL執(zhí)行計劃里rows顯示8000實(shí)際卻掃了80萬行——原因是表數(shù)據(jù)發(fā)生了大量增刪統(tǒng)計信息沒有及時更新。真正要看準(zhǔn)確行數(shù)可以在分析前先執(zhí)行ANALYZE TABLE刷新統(tǒng)計信息或者在EXPLAIN后加FORMATTREE看更細(xì)節(jié)的信息。做方案對比時看rows的變化趨勢完全夠用但不要基于rows去做精確的容量評估。4.3 索引失效的場景遠(yuǎn)比想象的多有些SQL看起來有索引可用執(zhí)行計劃里type卻是ALL這種情況基本可以歸為索引沒被用上。最常見的三種失效原因我都踩過。第一種是函數(shù)包裹。WHERE YEAR(create_time) 2024create_time列被函數(shù)包住索引自然失效。解決辦法是改寫條件讓列獨(dú)立在表達(dá)式一側(cè)create_time 2024-01-01 AND create_time 2025-01-01。第二種是隱式類型轉(zhuǎn)換。字段是varchar類型SQL里卻傳了數(shù)字或者字段是intSQL里卻傳了字符串。MySQL會統(tǒng)一轉(zhuǎn)類型再比較導(dǎo)致索引失效而且這種問題很難從肉眼發(fā)現(xiàn)。第三種是LIKE查詢以通配符開頭。WHERE name LIKE %張%最前面的%讓MySQL無法從索引樹的某個錨點(diǎn)開始找只能全部遍歷。如果業(yè)務(wù)確實(shí)需要這種模糊匹配通常只能走全文索引或者搜索引擎靠普通B樹索引解決不了。4.4 深分頁的優(yōu)化LIMIT并不總是很貴執(zhí)行計劃顯示索引都用上了type也在range級別但SQL還是慢這時候多半是分頁太深了。LIMIT 100000, 20的意思是先掃描前100020行丟掉前100000行只返回最后20行。掃描的行數(shù)不少但執(zhí)行計劃的rows可能顯示得很小因?yàn)閮?yōu)化器是按理想情況下估算的。深分頁的常見優(yōu)化方案有兩種第一種是延遲關(guān)聯(lián)先查出主鍵或唯一鍵再回表拿完整數(shù)據(jù)。原理是利用索引樹上的主鍵定位比直接掃大范圍數(shù)據(jù)行快得多把代價大的回表推遲到最后20行才做。第二種是記錄上一頁最后一條數(shù)據(jù)的某個有序字段值下一頁用WHERE create_time 上頁最小值來取跳過已經(jīng)翻過的數(shù)據(jù)這個方案實(shí)際效果最好但需要業(yè)務(wù)配合改造。4.5 覆蓋索引讓查詢徹底不回表Extra里的Using index指的是查詢所需的所有字段都包含在索引中MySQL只需要掃描索引樹就能返回結(jié)果完全不需要回表讀取聚簇索引里的數(shù)據(jù)行。這個優(yōu)化空間在大字段回表場景下特別明顯。舉個例子查詢只需要order_no和amount兩個小字段而創(chuàng)建的這個聯(lián)合索引恰好包含這兩個字段那么執(zhí)行計劃里就會看到Using index?；乇黹_銷雖然單次不大但百萬行級別的掃描下每一次回表都可能觸發(fā)額外的隨機(jī)I/O累積起來就是秒級差距。所以在設(shè)計高頻查詢的索引時我會盡量把SELECT里出現(xiàn)的字段在不影響索引體積的前提下追加到索引末尾。這就是常說的覆蓋索引。個人習(xí)慣每完成一個聯(lián)合索引設(shè)計我都會檢查一下這個索引能否覆蓋線上Top 10高頻查詢的SELECT字段。能覆蓋就盡量覆蓋這是成本最低、收益最穩(wěn)定的提速手段。5. 順手整理一份執(zhí)行計劃排查速查表如果你剛接觸執(zhí)行計劃記不住各種字段的優(yōu)先級可以把下面這張表收藏起來。它是我在實(shí)際項目中反復(fù)用到的一張判斷清單基本覆蓋了慢SQL初始定位的常見路徑。執(zhí)行計劃證據(jù)初步判斷首選優(yōu)化動作typeALL, keyNULL缺索引或索引失效檢查WHERE條件字段是否有合適索引typerange/index用上了索引但掃描范圍偏大收緊條件、調(diào)整聯(lián)合索引字段順序ExtraUsing filesort排序無法利用索引把ORDER BY字段加入索引ExtraUsing temporary分組去重產(chǎn)生臨時表改寫SQL、讓分組字段進(jìn)索引ExtraUsing index覆蓋索引生效保持考慮是否可推廣到更多高頻SQLkey列與實(shí)際預(yù)期不符優(yōu)化器選了另一個索引檢查統(tǒng)計信息必要時用FORCE INDEX做對比這張表不解決所有問題但它能幫你把90%的慢SQL在幾分鐘內(nèi)給出一個正確方向的判斷。這幾次調(diào)優(yōu)經(jīng)驗(yàn)下來我最深的體會是SQL優(yōu)化的核心并不在于會背多少種奇技淫巧而在于你能不能把一個看起來玄乎的性能問題拆解成掃描方式、掃描范圍、額外操作三個可以量化的點(diǎn)然后針對那個最痛的點(diǎn)下手。執(zhí)行計劃正是把這些信息擺在你面前的工具用熟了之后你會發(fā)現(xiàn)自己看慢SQL的心態(tài)會從慌變成穩(wěn)。最后再分享一個小建議如果你所在的項目還完全沒有建立SQL review機(jī)制不妨從今天開始每次上線前把最近一周的慢日志SQL批量跑一遍EXPLAIN把新出現(xiàn)的全表掃描和filesort提前攔在發(fā)布之前。這個習(xí)慣堅持三個月你會回來感謝它。