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