行計(jì)劃的實(shí)戰(zhàn)排查指南)
1. 一次線上慢查詢引發(fā)的索引失效排查上周五下午我正在改一個(gè)報(bào)表接口突然告警短信連響了三聲訂單表的一條查詢SQL平均響應(yīng)時(shí)間從55ms飆升到6.8s。跑過去看了慢查詢?nèi)罩径ㄎ坏揭粭l每天要跑幾十萬次的查詢?cè)臼呛撩爰?jí)完成的現(xiàn)在卻在全表掃描。這個(gè)問題的根源就是MySQL索引失效。下面把這個(gè)排查過程完整復(fù)盤一遍希望能給你一點(diǎn)參考。索引失效不是什么高深的理論但它在生產(chǎn)環(huán)境里的殺傷力往往超過我們的想象一條原本該走索引的查詢變成全表掃描代價(jià)可能就是幾秒甚至幾十秒的響應(yīng)延遲直接影響用戶體驗(yàn)。1.1 慢查詢現(xiàn)場(chǎng)還原當(dāng)時(shí)的SQL大概長(zhǎng)這樣SELECT id, order_no, user_id, amount, status, create_time FROM order_detail WHERE DATE(create_time) 2024-11-25 AND status 1 ORDER BY id DESC LIMIT 20;order_detail表有接近兩千萬行數(shù)據(jù)create_time上建有普通索引status是一個(gè)普通int列。正常情況下這個(gè)查詢應(yīng)該先在create_time索引上定位到當(dāng)天所有記錄再過濾status最后排序返回20條?？涩F(xiàn)實(shí)是執(zhí)行計(jì)劃里的type字段顯示為ALLrows預(yù)估接近兩千萬Extra列里還有Using where和Using filesort。我當(dāng)時(shí)的第一反應(yīng)是索引是不是沒建上但檢查之后發(fā)現(xiàn)create_time索引明明存在。后來才反應(yīng)過來問題出在WHERE條件里的DATE函數(shù)上——它對(duì)索引列做了函數(shù)運(yùn)算MySQL無法利用B樹有序性去范圍掃描只能把索引列的所有值都“加工”一遍再過濾于是干脆選擇了全表掃描。從原理上講B樹索引的有序性建立在“列本身的原始值”上一旦套上函數(shù)索引里存儲(chǔ)的原始鍵值和查詢條件里的加工后值就無法直接對(duì)應(yīng)優(yōu)化器自然無從下手。1.2 定位失效方式的三個(gè)關(guān)鍵動(dòng)作這個(gè)問題的定位并不復(fù)雜但當(dāng)時(shí)有效幫助我快速收斂的三個(gè)動(dòng)作你可以先記下來。第一打開慢查詢?nèi)罩竞彤?dāng)前執(zhí)行的日志開關(guān)把具體SQL和實(shí)際執(zhí)行計(jì)劃抓出來。尤其在生產(chǎn)環(huán)境不要憑記憶猜直接用SHOW INDEX FROM order_detail確認(rèn)索引是否存在、字段和順序?qū)Σ粚?duì)。也可以用SET profiling1開啟profiling拿到更詳細(xì)的每個(gè)步驟耗時(shí)。如果慢查詢?nèi)罩纠锿瑫r(shí)出現(xiàn)了多條類似的SQL最好用pt-query-digest這類工具做一次聚合分析找出共性往往幾個(gè)關(guān)鍵字就能暴露問題。第二用EXPLAIN看執(zhí)行計(jì)劃。重點(diǎn)看type字段是不是從const、ref掉到了ALL看possible_keys是否出現(xiàn)了但key是空這兩條是最直觀的索引失效信號(hào)。我習(xí)慣同時(shí)打開EXPLAIN ANALYZEMySQL 8.0.18支持它能反饋每個(gè)算子實(shí)際耗時(shí)和行數(shù)比只看估算值要準(zhǔn)確得多。有一次排查時(shí)EXPLAIN顯示rows只有幾百實(shí)際跑起來卻掃了幾百萬行就是統(tǒng)計(jì)信息和真實(shí)情況嚴(yán)重脫節(jié)這時(shí)依賴估算值很容易被誤導(dǎo)。第三把SQL中的條件逐個(gè)去掉做“最小復(fù)現(xiàn)”。比如去掉DATE(create_time)只留下create_time BETWEEN...如果執(zhí)行計(jì)劃立刻從ALL變成了range那基本就鎖定罪魁禍?zhǔn)琢恕Ｗ鲞@一步時(shí)要注意事務(wù)隔離級(jí)別和當(dāng)前數(shù)據(jù)量最好在一臺(tái)與生產(chǎn)環(huán)境硬件接近的從庫上驗(yàn)證避免在主庫上做壓力測(cè)試干擾業(yè)務(wù)。如果條件本身就在同一張表也可以用STRAIGHT_JOIN強(qiáng)制連接順序但那只適合調(diào)優(yōu)階段不適合上線。2. 索引失效的高頻操作圖譜從函數(shù)、隱式轉(zhuǎn)換到前導(dǎo)通配符上面的案例只是冰山一角。在MySQL里能讓索引失效的操作五花八門但歸結(jié)起來大部分都逃不出下面這幾個(gè)典型場(chǎng)景。我平時(shí)會(huì)把這套“失效圖譜”存在腦子里每次寫完SQL先對(duì)照過一遍命中率能降低七成。下面逐個(gè)拆解每個(gè)場(chǎng)景都會(huì)附上實(shí)際SQL和優(yōu)化思路你能直接對(duì)照著手里的查詢?nèi)ヅ挪椤?.1 對(duì)索引列做函數(shù)運(yùn)算這是最典型的失效原因也是生產(chǎn)環(huán)境出現(xiàn)最多的。比如WHERE DATE(create_time) 2024-11-25 WHERE YEAR(create_time) 2024 WHERE SUBSTRING(name, 1, 3) abc只要對(duì)索引列套了函數(shù)MySQL的優(yōu)化器就沒法直接使用索引去二分查找因?yàn)樗饕锎娴氖窃贾刀樵儣l件是函數(shù)運(yùn)算后的結(jié)果。一個(gè)例外是MySQL 8.0支持函數(shù)索引你可以專門為DATE(create_time)建一個(gè)表達(dá)式索引但這對(duì)現(xiàn)有查詢并不一定劃算因?yàn)楹瘮?shù)索引會(huì)增加寫入時(shí)的計(jì)算開銷也會(huì)占用額外的磁盤空間。最直接的方案是把SQL改寫成語義等價(jià)的形式WHERE create_time 2024-11-25 00:00:00 AND create_time 2024-11-26 00:00:00這樣就能命中索引結(jié)果范圍也一樣。這里有一個(gè)容易被忽視的坑很多人覺得DATE(create_time) 2024-11-25只是把時(shí)間“截?cái)唷绷艘幌碌玀ySQL的索引排序是基于完整時(shí)間值的哪怕只是截?cái)嗟教焖饕龢渖系奈锢眄樞蛞矌筒簧厦?。所以與其事后改寫不如一開始就避免對(duì)索引列做任何包裝。2.2 隱式類型轉(zhuǎn)換搗亂當(dāng)字段是varchar類型你卻拿一個(gè)數(shù)字去比較時(shí)MySQL會(huì)把兩者都轉(zhuǎn)成數(shù)字再比較。這個(gè)轉(zhuǎn)換發(fā)生在索引列上時(shí)索引就失效了。比如WHERE user_mobile 13812345678 -- user_mobile是varchar WHERE order_no 202411250001 -- order_no是varchar解決辦法就兩個(gè)字保持一致。要么查詢參數(shù)里帶上引號(hào)要么把列類型改成bigint。有一點(diǎn)值得多說一句如果你有user_mobile這類號(hào)碼字段最好直接用bigint存省空間還不會(huì)被類型轉(zhuǎn)換但要注意手機(jī)號(hào)如果用int可能溢出。實(shí)際工作中我還遇到過一種更隱蔽的情況字段是utf8mb4字符集應(yīng)用傳參是utf8或者連接串character_set不同雖然不會(huì)直接報(bào)錯(cuò)但會(huì)在比較時(shí)產(chǎn)生隱式字符集轉(zhuǎn)換同樣讓優(yōu)化器放棄索引。排查這類問題可以看EXPLAIN里的key_len有沒有異常變短再檢查連接參數(shù)。2.3 前導(dǎo)模糊查詢LIKE %keyword之所以失效是因?yàn)锽樹索引是有序排列的只能從前向后匹配。當(dāng)你把通配符放在最前面等于在最開始就破壞了有序匹配的起點(diǎn)。比如WHERE name LIKE %張 WHERE content LIKE %MySQL索引%如果業(yè)務(wù)真的需要這種搜索別指望普通索引應(yīng)該引入全文索引MySQL自帶全文索引或ElasticSearch。但如果只是需要“以某個(gè)詞結(jié)尾”的少數(shù)查詢可以嘗試反轉(zhuǎn)字段存儲(chǔ)WHERE reversed_name LIKE 張% -- reversed_name存儲(chǔ)的是倒過來的字符串這會(huì)犧牲一定的寫入復(fù)雜度但能換來索引利用。還有一個(gè)小技巧如果只是需要匹配后綴并且匹配字符串比較短也可以在索引列上使用LIKE 張%也就是把匹配詞倒過來變成前綴匹配但前提是你能接受額外維護(hù)一個(gè)反轉(zhuǎn)列。如果業(yè)務(wù)對(duì)實(shí)時(shí)性要求不高更推薦用ES或者專門的搜索引擎因?yàn)樗麄儗?duì)倒排索引的支持比MySQL成熟得多。2.4 OR連接了非索引列OR是一個(gè)“或”關(guān)系意味著兩邊都必須判斷。只要其中一個(gè)條件沒有索引MySQL就傾向于放棄索引走全表掃描。例如WHERE status 1 OR type 2 -- status有索引type沒索引即使兩個(gè)條件都有索引優(yōu)化器也不一定能準(zhǔn)確合并索引得到結(jié)果MySQL對(duì)索引合并的優(yōu)化有限很多時(shí)候還是全表掃描更“劃算”。改造方案是把OR拆成兩個(gè)查詢用UNION ALL或者確保所有參與OR的列都建了聯(lián)合索引但后者依賴SQL語義并不總是可行。舉個(gè)例子如果業(yè)務(wù)場(chǎng)景是“查詢某一個(gè)用戶當(dāng)天創(chuàng)建的訂單或者當(dāng)天下過單的用戶”這種條件本身就是兩段獨(dú)立邏輯應(yīng)該拆成兩個(gè)SQL分別查再在應(yīng)用層做合并而不是硬塞到一個(gè)SQL里。使用UNION ALL時(shí)要注意兩個(gè)子查詢是否會(huì)重復(fù)數(shù)據(jù)如果需要去重再用UNION但UNION的排序和去重成本通常不低要謹(jǐn)慎選擇。2.5 NOT IN、NOT EXISTS與不等于和NOT IN往往會(huì)讓MySQL放棄索引掃描。原因是B樹索引組織方式適合等值和范圍查詢而要找出所有不等于某值的記錄相當(dāng)于掃描全樹的大部分節(jié)點(diǎn)再加上統(tǒng)計(jì)信息可能誤判這張表里99%都是“不等于給定值”優(yōu)化器自然選擇全表掃描。不過這里有個(gè)經(jīng)驗(yàn)之談如果你的表上NOT IN的值只占極少比例并且MySQL統(tǒng)計(jì)信息足夠準(zhǔn)確它也有小概率走索引。所以別一棍子打死要看執(zhí)行計(jì)劃。但對(duì)于絕大多數(shù)場(chǎng)景強(qiáng)制走索引往往比全表更慢我們不要把“索引失效”絕對(duì)化。換個(gè)思路如果業(yè)務(wù)上需要排除某幾個(gè)狀態(tài)可以試著把條件改成IN一個(gè)正面的狀態(tài)列表例如WHERE status IN (1, 2, 3)這樣索引利用機(jī)會(huì)會(huì)大很多。如果確實(shí)要排除也可以考慮用LEFT JOIN加IS NULL的方式改寫但要注意數(shù)據(jù)量、連接順序和額外開銷不一定總是更好。2.6 索引列參與了數(shù)值運(yùn)算和函數(shù)一個(gè)道理WHERE price * 100 500會(huì)把price列先算出結(jié)果再去比較索引自然幫不上忙。正確的寫法是把運(yùn)算移到等號(hào)另一側(cè)WHERE price 500 / 100建議把這類SQL歸類為“標(biāo)準(zhǔn)寫法”在代碼評(píng)審時(shí)重點(diǎn)檢查。有人可能會(huì)問“MySQL優(yōu)化器那么智能能不能自動(dòng)把price * 100 500改成price 5”很遺憾MySQL的優(yōu)化器并不會(huì)這么智能尤其是當(dāng)表達(dá)式涉及列和常量混算時(shí)它無法保證做等價(jià)變形一定不改變浮點(diǎn)精度或整型溢出所以寧可保守地全表掃。這時(shí)候人工改寫是最可靠的。3. 聯(lián)合索引和排序場(chǎng)景中最容易踩的失效坑如果說上面那些是“單列索引的明槍”那聯(lián)合索引絕對(duì)是“暗箭”。很多失效并不是SQL寫錯(cuò)了而是你對(duì)聯(lián)合索引的理解不夠深。這一節(jié)我準(zhǔn)備把聯(lián)合索引、范圍查詢、排序和分組這四個(gè)場(chǎng)景放在一起講因?yàn)樗鼈兊牡讓舆壿嬍窍嗤ǖ摹?.1 最左前綴聯(lián)合索引的第一條軍規(guī)聯(lián)合索引(a, b, c)實(shí)際創(chuàng)建的是一個(gè)按a、b、c依次排序的復(fù)合結(jié)構(gòu)。MySQL可以命中索引的寫法必須符合最左前綴原則查詢條件里必須包含a并且是“從左到右連續(xù)”的。最容易犯的錯(cuò)是跳過最左列直接查cWHERE c xxx -- 無法命中(a,b,c)索引還有的人喜歡把條件順序打亂WHERE b ? AND a ?。這點(diǎn)MySQL優(yōu)化器能做優(yōu)化即使順序不同它也會(huì)重排成a ? AND b ?所以只要最左列存在就行。但如果你給的是WHERE b ? AND c ?缺失了a就是徹底失效。還有一個(gè)容易被忽略的場(chǎng)景WHERE a IN (...) AND b ?。如果你在a列用了IN它依然會(huì)走索引但是b列的后續(xù)匹配會(huì)受到一些影響因?yàn)镮N本質(zhì)上是一個(gè)區(qū)間集合優(yōu)化器把它當(dāng)成多區(qū)間處理b列的有序性在每個(gè)區(qū)間內(nèi)仍然可以保持所以嚴(yán)格說b也可以使用但要看統(tǒng)計(jì)和成本。這里建議你直接看EXPLAIN的key_len判斷實(shí)際用了幾個(gè)字段不要憑感覺。3.2 范圍條件會(huì)切斷后續(xù)列的使用繼續(xù)用(a,b,c)舉例WHERE a 1 AND b 2 AND c 3這條SQL里a和b可以用到索引但b的范圍判斷影響到了c列因?yàn)楫?dāng)b是一個(gè)不連續(xù)的范圍時(shí)c在b范圍內(nèi)的排序已經(jīng)失去意義MySQL無法繼續(xù)精確匹配c所以c的索引部分被浪費(fèi)了。這不是“索引整個(gè)失效”而是“部分失效”。很多同學(xué)在排查時(shí)看到typerange以為沒問題但看key_len會(huì)發(fā)現(xiàn)它其實(shí)比完全等值匹配短了一截。要優(yōu)化可以把b2改寫成b in (3,4,5)這種枚舉值列表或者調(diào)整聯(lián)合索引順序把等值判斷的列放在前面。舉例來說業(yè)務(wù)常見查詢是“按狀態(tài)和時(shí)間范圍查數(shù)據(jù)”那索引可以設(shè)計(jì)成(status, create_time)讓status作為等值前綴時(shí)間作為范圍后綴這樣兩部分都能用上如果設(shè)計(jì)成(create_time, status)那status就會(huì)因?yàn)閏reate_time的范圍而被浪費(fèi)。設(shè)計(jì)聯(lián)合索引時(shí)一定要先列出所有高頻查詢的條件把所有等值條件列優(yōu)先放在最前面范圍條件放后面。3.3 排序字段忘掉最左前綴filesort悄悄出現(xiàn)ORDER BY同樣要遵守最左前綴。比如聯(lián)合索引(a, b, c)以下排序是可以避免文件排序的ORDER BY a, b, c ORDER BY a DESC, b DESC, c DESC WHERE a 1 ORDER BY b, c但下面這些就會(huì)觸發(fā)filesortORDER BY b, c ORDER BY a, c WHERE a 1 ORDER BY c, b為什么WHERE a 1 ORDER BY c, b也不走索引因?yàn)樗饕樞蚴莂,b,c在a等值的情況下b的排序仍然生效但你不排序b卻排序c和索引的有序性沖突。這里有個(gè)隱藏點(diǎn)如果所有排序字段方向不一致比如一個(gè)升序一個(gè)降序MySQL 8.0之前也無法利用索引8.0僅對(duì)特定方向支持。寫排序時(shí)需要多看一眼索引定義。另外還要注意如果SQL里同時(shí)有WHERE過濾和ORDER BYMySQL會(huì)先嘗試用索引完成WHERE過濾再用同一索引完成排序。如果WHERE條件用范圍消耗掉了索引的后續(xù)列排序階段就可能重新面臨filesort。這種時(shí)候可以考慮索引等值列, 排序列把排序需求直接焊死在索引里。3.4 分組和去重一樣受制于索引順序GROUP BY在邏輯上會(huì)先排序再分組所以它和ORDER BY一樣依賴最左前綴。一個(gè)典型的失效場(chǎng)景是SELECT status, category, COUNT(*) FROM orders GROUP BY category, status如果索引是(status, category)那么這里排序順序就顛倒了觸發(fā)臨時(shí)表和filesort。你可以在EXPLAIN的Extra里看到Using temporary; Using filesort這就是索引失效帶來的連鎖反應(yīng)。臨時(shí)表可能存儲(chǔ)在內(nèi)存或磁盤上一旦數(shù)據(jù)量超過tmp_table_size就會(huì)溢寫到磁盤性能急劇下降。優(yōu)化思路是調(diào)整索引順序?yàn)?category, status)或者把分組查詢改寫為先用子查詢把必要行縮小再在外面分組。不過要注意GROUP BY本身帶有去重語義如果業(yè)務(wù)允許可以嘗試用窗口函數(shù)或先排序后去重的方式替代但兩者邏輯要完全一致。實(shí)際上MySQL的GROUP BY實(shí)現(xiàn)會(huì)把NULL也當(dāng)成一個(gè)分組所以如果分組列上NULL值很多也會(huì)影響效率這一點(diǎn)很多人并不清楚。4. 執(zhí)行計(jì)劃下鉆用EXPLAIN破解為什么沒走索引前面說了這么多原因但你實(shí)際寫SQL時(shí)不可能背完所有禁忌更可靠的手段是拿EXPLAIN去驗(yàn)證。我把最常見的檢查方法整理成一套“三板斧”遇到疑似索引失效時(shí)照著看。真正的DBA排查問題從來不是靠猜而是靠這些證據(jù)層層下鉆。4.1 type字段索引可用性的第一信號(hào)EXPLAIN中的type字段從好到差大概有systemconsteq_refrefrangeindexALL。如果出現(xiàn)ALL基本就是全表掃描索引失效或優(yōu)化器不想用索引。出現(xiàn)index時(shí)表示遍歷了整棵索引樹不是通過索引定位而是因?yàn)樗饕龢浔染奂饕?yōu)化器選擇“掃描索引樹”來避免回表只能算“部分救場(chǎng)”。range說明用了索引范圍掃描常見于BETWEEN、IN、 等這是健康的。ref、eq_ref、const都是等值命中的情況是最理想的狀態(tài)。有時(shí)候你會(huì)發(fā)現(xiàn)type是index但key明明有值于是誤以為索引被用上了其實(shí)這里全樹掃描的意義和全表差不多只是由于索引體積小掃描成本低一點(diǎn)。如果SQL需要返回大量行index掃描可能比ALL稍好但依然不理想。真正判斷是不是高效命中的關(guān)鍵還是要結(jié)合rows和key_len一起看。4.2 key_len 和 rows判斷是否“完整用上”了聯(lián)合索引key_len是判斷聯(lián)合索引到底用了多少列的核心依據(jù)。例如索引(a varchar(50), b int, c datetime)當(dāng)SQL只用到a時(shí)key_len只有a那段的長(zhǎng)度用到了a和b就會(huì)更長(zhǎng)。如果把每次EXPLAIN的key_len記錄下來對(duì)比你很容易發(fā)現(xiàn)“范圍條件切斷后續(xù)列”的小動(dòng)作。rows是優(yōu)化器預(yù)估需要掃描的行數(shù)。如果預(yù)估行數(shù)接近全表行數(shù)即便索引被使用也可能因?yàn)榛乇沓杀靖叨艞壥褂谩＿@時(shí)候需要看是否可以使用覆蓋索引把要查詢的字段都放進(jìn)索引里減少回表。比如索引(create_time, status, amount)而SQL是SELECT create_time, status, amount FROM order_detail WHERE create_time BETWEEN ... AND status 1那么所有需要的字段都從索引里拿到不需要回表Extra就會(huì)出現(xiàn)Using index。如果還要查詢order_no這個(gè)字段不在索引里就會(huì)在取出索引記錄后回表讀取完整行成本上升。所以覆蓋索引在設(shè)計(jì)時(shí)往往是“用空間換時(shí)間”的經(jīng)典手段對(duì)有大量高頻、固定字段查詢的場(chǎng)景特別有效。4.3 Extra列里的“Using where”和“Using filesort”Using where出現(xiàn)在SQL走了某個(gè)索引但還有少量字段在引擎層進(jìn)一步過濾。它不代表索引失效但如果你發(fā)現(xiàn)在索引命中的情況下仍然大量出現(xiàn)可能要考慮是否某些查詢列沒有在索引中或者索引設(shè)計(jì)有冗余。比如說索引(a, b)SQL是WHERE a 1 AND c 2這里a走了索引c的過濾就必須靠Using where。如果c的過濾選擇性很高那你可能需要把c也加入索引。Using filesort是排序索引失效的直接證據(jù)。它意味著MySQL無法利用已有索引的有序性必須另起一段內(nèi)存或磁盤進(jìn)行排序。要消除它重點(diǎn)檢查ORDER BY和GROUP BY是否對(duì)齊了索引列順序。如果在Extra里看到Using temporary; Using filesort同時(shí)出現(xiàn)通常是GROUP BY或DISTINCT把臨時(shí)表都用上了這種時(shí)候要格外小心數(shù)據(jù)量一大性能會(huì)爆炸。4.4 一個(gè)完整的EXPLAIN實(shí)戰(zhàn)分析我們用一個(gè)例子走一遍EXPLAIN SELECT id, user_id, amount FROM order_detail WHERE DATE(create_time) 2024-11-01 ORDER BY id DESC執(zhí)行計(jì)劃結(jié)果的關(guān)鍵列是typeALL,possible_keysidx_create_time,keyNULL,rows19000000,ExtraUsing where。雖然possible_keys寫出了idx_create_time但key是NULL說明因?yàn)楹瘮?shù)運(yùn)算優(yōu)化器直接放棄索引。把SQL改成WHERE create_time 2024-11-01 00:00:00再看type變成rangekey變成idx_create_timerows降到幾十萬問題清晰可見。這就是用工具還原真相的過程。如果用的是MySQL 8.0.18以上還可以加上ANALYZE關(guān)鍵字EXPLAIN ANALYZE SELECT ...它會(huì)返回每個(gè)操作的實(shí)際執(zhí)行時(shí)間和行數(shù)比靜態(tài)的EXPLAIN更真實(shí)。有一次我被一個(gè)奇怪的執(zhí)行計(jì)劃誤導(dǎo)了很久EXPLAIN顯示全表掃描但實(shí)際執(zhí)行卻很快后來才發(fā)現(xiàn)是因?yàn)閮?yōu)化器把所選列都覆蓋到了二級(jí)索引而EXPLAIN的舊版本沒有展示這個(gè)細(xì)節(jié)。所以工具要盡量用新版本多參考Extra真實(shí)反饋。5. 索引失效的預(yù)防良藥從規(guī)范約束到優(yōu)化實(shí)踐無論是定位了一次事故還是剛剛梳理完全部原因最終目標(biāo)都是“少踩坑”。下面這些方法是自己在公司實(shí)踐了一段時(shí)間后覺得最有用的。它們不是一次性的優(yōu)化技巧而是應(yīng)該固化到日常研發(fā)流程里的動(dòng)作。5.1 代碼評(píng)審階段的SQL規(guī)約我們團(tuán)隊(duì)把常見索引失效原因?qū)戇M(jìn)了一頁SQL開發(fā)規(guī)約評(píng)審時(shí)逐條打勾禁止對(duì)索引列進(jìn)行函數(shù)、運(yùn)算或隱式類型轉(zhuǎn)換。禁止使用前導(dǎo)模糊查詢除非有全文索引。聯(lián)合索引必須保證查詢條件從左到右持續(xù)匹配。排序/分組字段必須與聯(lián)合索引順序一致。使用OR時(shí)必須確保所有條件列都有可用索引盡量改成UNION ALL。你可能覺得這些約束太機(jī)械但生產(chǎn)事故往往來自“偶爾一次”的小聰明。把它落到評(píng)審里比事后救火強(qiáng)十倍。評(píng)審時(shí)不要只看SQL本身還要帶上表結(jié)構(gòu)和執(zhí)行計(jì)劃。我見過很多團(tuán)隊(duì)評(píng)審只看代碼邏輯執(zhí)行計(jì)劃壓根不看結(jié)果上線后慢查詢直接打到告警平臺(tái)。對(duì)于新上線的高頻查詢我習(xí)慣要求開發(fā)在PR描述里附上EXPLAIN關(guān)鍵字段截圖并回答“key用的哪個(gè)索引”“rows預(yù)估多少”“有沒有filesort”這三個(gè)問題能堵住絕大多數(shù)坑。5.2 數(shù)據(jù)模型層面的提前設(shè)計(jì)索引失效的也不少是建表時(shí)就埋下的雷字段類型盡量使用數(shù)值型或固定長(zhǎng)度的字符串避免不同類型比較。存儲(chǔ)手機(jī)號(hào)、身份證號(hào)這類定長(zhǎng)字段直接用char或bigint。冗余“范圍查詢”的對(duì)比值比如把日期時(shí)間拆分成日期和時(shí)分兩個(gè)列讓等值查詢有機(jī)會(huì)走上聯(lián)合索引。如果業(yè)務(wù)明確有函數(shù)查詢需求優(yōu)先考慮MySQL 8.0的函數(shù)索引或者把原始值加工結(jié)果單獨(dú)存儲(chǔ)一個(gè)列。這里想說一個(gè)真實(shí)踩過的坑我們?cè)?jīng)有一張訂單表業(yè)務(wù)方喜歡按“月”查數(shù)據(jù)SQL里寫WHERE MONTH(create_time)11后來在應(yīng)用層加了一個(gè)month字段來冗余但是代碼沒有同步更新索引倒是建了month結(jié)果SQL還在用MONTH(create_time)執(zhí)行計(jì)劃不光失效還會(huì)因?yàn)轭~外的month索引增加寫入開銷。后來我們把冗余字段落到表里并強(qiáng)制要求SQL使用month11性能才恢復(fù)正常。所以冗余字段一定要和SQL標(biāo)準(zhǔn)配合否則就是白白占空間。5.3 用慢查詢?nèi)罩竞脱矙z腳本主動(dòng)發(fā)現(xiàn)問題被動(dòng)等告警很難受不如主動(dòng)“排雷”。生產(chǎn)環(huán)境可以開啟慢查詢?nèi)罩径ㄆ趻呙鑝ysqldumpslow結(jié)果把那些長(zhǎng)時(shí)間執(zhí)行的SQL全部拎出來做EXPLAIN。再配合一個(gè)月跑一次的索引統(tǒng)計(jì)信息更新ANALYZE TABLE能有效防止統(tǒng)計(jì)值過期導(dǎo)致優(yōu)化器跑偏。我自己習(xí)慣用一條命令導(dǎo)出TOP慢SQLmysqldumpslow -s at -t 20 /var/log/mysql/slow.log然后對(duì)每個(gè)出現(xiàn)次數(shù)多的SQL執(zhí)行EXPLAIN重點(diǎn)看是否有索引失效?？梢园l(fā)現(xiàn)一個(gè)現(xiàn)象很多慢SQL并不是每一次都慢而是數(shù)據(jù)增長(zhǎng)到某個(gè)量級(jí)后突然變慢。這就是因?yàn)閮?yōu)化器基于過舊的統(tǒng)計(jì)信息做出了錯(cuò)誤判斷。定期ANALYZE TABLE的成本很低但收益非?？捎^。如果你用的是MySQL 5.7及以上還可以設(shè)置innodb_stats_auto_recalc1讓表數(shù)據(jù)變化超過10%時(shí)自動(dòng)重新計(jì)算統(tǒng)計(jì)信息。5.4 優(yōu)化器不夠聰明時(shí)該怎么辦有些情況下SQL已經(jīng)寫對(duì)了但優(yōu)化器還是選擇全表掃描。比如一個(gè)表上有索引但選擇性太低重復(fù)值太多或者統(tǒng)計(jì)信息不準(zhǔn)。這時(shí)候先強(qiáng)制執(zhí)行一下試試FORCE INDEX看是否真的更快。不要長(zhǎng)期依賴它只是臨時(shí)排查手段。重新分析統(tǒng)計(jì)信息執(zhí)行ANALYZE TABLE table_name讓優(yōu)化器“刷新認(rèn)知”。重寫SQL邏輯把大查詢拆成小查詢把復(fù)雜的關(guān)聯(lián)拆成兩步通常讓優(yōu)化器更清晰。必要時(shí)調(diào)整索引結(jié)構(gòu)增加覆蓋索引把SELECT列都放進(jìn)索引減少回表成本。這里還要提一個(gè)和索引失效容易混淆的話題——索引下推Index Condition PushdownICP。當(dāng)聯(lián)合索引(a,b)中a條件滿足后如果對(duì)b的過濾能下推到索引層MySQL會(huì)把Using index condition打在Extra里。這不叫失效反而是5.6之后的一個(gè)優(yōu)化手段。你千萬不要一看到Using index condition就覺得炸了要分清場(chǎng)景。ICP是針對(duì)“索引內(nèi)字段條件無法走最左連續(xù)匹配”時(shí)的一種補(bǔ)償它把部分WHERE過濾條件下推到存儲(chǔ)引擎在讀取索引記錄時(shí)就做判斷減少回表次數(shù)。雖然它不能像范圍匹配那樣精確利用索引順序但已經(jīng)比完全回表后再過濾要高效。理解了這一點(diǎn)再看Extra就會(huì)更從容。說到底索引失效不是玄學(xué)背后都是B樹的有序性和優(yōu)化器的成本核算。只要你在寫SQL時(shí)多問一句“這個(gè)條件能不能直接利用索引樹的有序性”多數(shù)坑都能繞過去。我自己習(xí)慣在新項(xiàng)目核心SQL上線前把EXPLAIN輸出截個(gè)圖當(dāng)作準(zhǔn)入條件久而久之線上“突然變慢”的報(bào)警少了大半。索引優(yōu)化是一項(xiàng)需要持續(xù)投入耐心的工作但它帶來的穩(wěn)定性和性能收益遠(yuǎn)比一時(shí)趕工的價(jià)值要大。 MySQL索引失效全解析：從慢查詢到執(zhí)行計(jì)劃的實(shí)戰(zhàn)排查指南
1. 一次線上慢查詢引發(fā)的索引失效排查上周五下午我正在改一個(gè)報(bào)表接口突然告警短信連響了三聲訂單表的一條查詢SQL平均響應(yīng)時(shí)間從55ms飆升到6.8s。跑過去看了慢查詢?nèi)罩径ㄎ坏揭粭l每天要跑幾十萬次的查詢?cè)臼呛撩爰?jí)完成的現(xiàn)在卻在全表掃描。這個(gè)問題的根源就是MySQL索引失效。下面把這個(gè)排查過程完整復(fù)盤一遍希望能給你一點(diǎn)參考。索引失效不是什么高深的理論但它在生產(chǎn)環(huán)境里的殺傷力往往超過我們的想象一條原本該走索引的查詢變成全表掃描代價(jià)可能就是幾秒甚至幾十秒的響應(yīng)延遲直接影響用戶體驗(yàn)。1.1 慢查詢現(xiàn)場(chǎng)還原當(dāng)時(shí)的SQL大概長(zhǎng)這樣SELECT id, order_no, user_id, amount, status, create_time FROM order_detail WHERE DATE(create_time) 2024-11-25 AND status 1 ORDER BY id DESC LIMIT 20;order_detail表有接近兩千萬行數(shù)據(jù)create_time上建有普通索引status是一個(gè)普通int列。正常情況下這個(gè)查詢應(yīng)該先在create_time索引上定位到當(dāng)天所有記錄再過濾status最后排序返回20條?？涩F(xiàn)實(shí)是執(zhí)行計(jì)劃里的type字段顯示為ALLrows預(yù)估接近兩千萬Extra列里還有Using where和Using filesort。我當(dāng)時(shí)的第一反應(yīng)是索引是不是沒建上但檢查之后發(fā)現(xiàn)create_time索引明明存在。后來才反應(yīng)過來問題出在WHERE條件里的DATE函數(shù)上——它對(duì)索引列做了函數(shù)運(yùn)算MySQL無法利用B樹有序性去范圍掃描只能把索引列的所有值都“加工”一遍再過濾于是干脆選擇了全表掃描。從原理上講B樹索引的有序性建立在“列本身的原始值”上一旦套上函數(shù)索引里存儲(chǔ)的原始鍵值和查詢條件里的加工后值就無法直接對(duì)應(yīng)優(yōu)化器自然無從下手。1.2 定位失效方式的三個(gè)關(guān)鍵動(dòng)作這個(gè)問題的定位并不復(fù)雜但當(dāng)時(shí)有效幫助我快速收斂的三個(gè)動(dòng)作你可以先記下來。第一打開慢查詢?nèi)罩竞彤?dāng)前執(zhí)行的日志開關(guān)把具體SQL和實(shí)際執(zhí)行計(jì)劃抓出來。尤其在生產(chǎn)環(huán)境不要憑記憶猜直接用SHOW INDEX FROM order_detail確認(rèn)索引是否存在、字段和順序?qū)Σ粚?duì)。也可以用SET profiling1開啟profiling拿到更詳細(xì)的每個(gè)步驟耗時(shí)。如果慢查詢?nèi)罩纠锿瑫r(shí)出現(xiàn)了多條類似的SQL最好用pt-query-digest這類工具做一次聚合分析找出共性往往幾個(gè)關(guān)鍵字就能暴露問題。第二用EXPLAIN看執(zhí)行計(jì)劃。重點(diǎn)看type字段是不是從const、ref掉到了ALL看possible_keys是否出現(xiàn)了但key是空這兩條是最直觀的索引失效信號(hào)。我習(xí)慣同時(shí)打開EXPLAIN ANALYZEMySQL 8.0.18支持它能反饋每個(gè)算子實(shí)際耗時(shí)和行數(shù)比只看估算值要準(zhǔn)確得多。有一次排查時(shí)EXPLAIN顯示rows只有幾百實(shí)際跑起來卻掃了幾百萬行就是統(tǒng)計(jì)信息和真實(shí)情況嚴(yán)重脫節(jié)這時(shí)依賴估算值很容易被誤導(dǎo)。第三把SQL中的條件逐個(gè)去掉做“最小復(fù)現(xiàn)”。比如去掉DATE(create_time)只留下create_time BETWEEN...如果執(zhí)行計(jì)劃立刻從ALL變成了range那基本就鎖定罪魁禍?zhǔn)琢恕Ｗ鲞@一步時(shí)要注意事務(wù)隔離級(jí)別和當(dāng)前數(shù)據(jù)量最好在一臺(tái)與生產(chǎn)環(huán)境硬件接近的從庫上驗(yàn)證避免在主庫上做壓力測(cè)試干擾業(yè)務(wù)。如果條件本身就在同一張表也可以用STRAIGHT_JOIN強(qiáng)制連接順序但那只適合調(diào)優(yōu)階段不適合上線。2. 索引失效的高頻操作圖譜從函數(shù)、隱式轉(zhuǎn)換到前導(dǎo)通配符上面的案例只是冰山一角。在MySQL里能讓索引失效的操作五花八門但歸結(jié)起來大部分都逃不出下面這幾個(gè)典型場(chǎng)景。我平時(shí)會(huì)把這套“失效圖譜”存在腦子里每次寫完SQL先對(duì)照過一遍命中率能降低七成。下面逐個(gè)拆解每個(gè)場(chǎng)景都會(huì)附上實(shí)際SQL和優(yōu)化思路你能直接對(duì)照著手里的查詢?nèi)ヅ挪椤?.1 對(duì)索引列做函數(shù)運(yùn)算這是最典型的失效原因也是生產(chǎn)環(huán)境出現(xiàn)最多的。比如WHERE DATE(create_time) 2024-11-25 WHERE YEAR(create_time) 2024 WHERE SUBSTRING(name, 1, 3) abc只要對(duì)索引列套了函數(shù)MySQL的優(yōu)化器就沒法直接使用索引去二分查找因?yàn)樗饕锎娴氖窃贾刀樵儣l件是函數(shù)運(yùn)算后的結(jié)果。一個(gè)例外是MySQL 8.0支持函數(shù)索引你可以專門為DATE(create_time)建一個(gè)表達(dá)式索引但這對(duì)現(xiàn)有查詢并不一定劃算因?yàn)楹瘮?shù)索引會(huì)增加寫入時(shí)的計(jì)算開銷也會(huì)占用額外的磁盤空間。最直接的方案是把SQL改寫成語義等價(jià)的形式WHERE create_time 2024-11-25 00:00:00 AND create_time 2024-11-26 00:00:00這樣就能命中索引結(jié)果范圍也一樣。這里有一個(gè)容易被忽視的坑很多人覺得DATE(create_time) 2024-11-25只是把時(shí)間“截?cái)唷绷艘幌碌玀ySQL的索引排序是基于完整時(shí)間值的哪怕只是截?cái)嗟教焖饕龢渖系奈锢眄樞蛞矌筒簧厦?。所以與其事后改寫不如一開始就避免對(duì)索引列做任何包裝。2.2 隱式類型轉(zhuǎn)換搗亂當(dāng)字段是varchar類型你卻拿一個(gè)數(shù)字去比較時(shí)MySQL會(huì)把兩者都轉(zhuǎn)成數(shù)字再比較。這個(gè)轉(zhuǎn)換發(fā)生在索引列上時(shí)索引就失效了。比如WHERE user_mobile 13812345678 -- user_mobile是varchar WHERE order_no 202411250001 -- order_no是varchar解決辦法就兩個(gè)字保持一致。要么查詢參數(shù)里帶上引號(hào)要么把列類型改成bigint。有一點(diǎn)值得多說一句如果你有user_mobile這類號(hào)碼字段最好直接用bigint存省空間還不會(huì)被類型轉(zhuǎn)換但要注意手機(jī)號(hào)如果用int可能溢出。實(shí)際工作中我還遇到過一種更隱蔽的情況字段是utf8mb4字符集應(yīng)用傳參是utf8或者連接串character_set不同雖然不會(huì)直接報(bào)錯(cuò)但會(huì)在比較時(shí)產(chǎn)生隱式字符集轉(zhuǎn)換同樣讓優(yōu)化器放棄索引。排查這類問題可以看EXPLAIN里的key_len有沒有異常變短再檢查連接參數(shù)。2.3 前導(dǎo)模糊查詢LIKE %keyword之所以失效是因?yàn)锽樹索引是有序排列的只能從前向后匹配。當(dāng)你把通配符放在最前面等于在最開始就破壞了有序匹配的起點(diǎn)。比如WHERE name LIKE %張 WHERE content LIKE %MySQL索引%如果業(yè)務(wù)真的需要這種搜索別指望普通索引應(yīng)該引入全文索引MySQL自帶全文索引或ElasticSearch。但如果只是需要“以某個(gè)詞結(jié)尾”的少數(shù)查詢可以嘗試反轉(zhuǎn)字段存儲(chǔ)WHERE reversed_name LIKE 張% -- reversed_name存儲(chǔ)的是倒過來的字符串這會(huì)犧牲一定的寫入復(fù)雜度但能換來索引利用。還有一個(gè)小技巧如果只是需要匹配后綴并且匹配字符串比較短也可以在索引列上使用LIKE 張%也就是把匹配詞倒過來變成前綴匹配但前提是你能接受額外維護(hù)一個(gè)反轉(zhuǎn)列。如果業(yè)務(wù)對(duì)實(shí)時(shí)性要求不高更推薦用ES或者專門的搜索引擎因?yàn)樗麄儗?duì)倒排索引的支持比MySQL成熟得多。2.4 OR連接了非索引列OR是一個(gè)“或”關(guān)系意味著兩邊都必須判斷。只要其中一個(gè)條件沒有索引MySQL就傾向于放棄索引走全表掃描。例如WHERE status 1 OR type 2 -- status有索引type沒索引即使兩個(gè)條件都有索引優(yōu)化器也不一定能準(zhǔn)確合并索引得到結(jié)果MySQL對(duì)索引合并的優(yōu)化有限很多時(shí)候還是全表掃描更“劃算”。改造方案是把OR拆成兩個(gè)查詢用UNION ALL或者確保所有參與OR的列都建了聯(lián)合索引但后者依賴SQL語義并不總是可行。舉個(gè)例子如果業(yè)務(wù)場(chǎng)景是“查詢某一個(gè)用戶當(dāng)天創(chuàng)建的訂單或者當(dāng)天下過單的用戶”這種條件本身就是兩段獨(dú)立邏輯應(yīng)該拆成兩個(gè)SQL分別查再在應(yīng)用層做合并而不是硬塞到一個(gè)SQL里。使用UNION ALL時(shí)要注意兩個(gè)子查詢是否會(huì)重復(fù)數(shù)據(jù)如果需要去重再用UNION但UNION的排序和去重成本通常不低要謹(jǐn)慎選擇。2.5 NOT IN、NOT EXISTS與不等于和NOT IN往往會(huì)讓MySQL放棄索引掃描。原因是B樹索引組織方式適合等值和范圍查詢而要找出所有不等于某值的記錄相當(dāng)于掃描全樹的大部分節(jié)點(diǎn)再加上統(tǒng)計(jì)信息可能誤判這張表里99%都是“不等于給定值”優(yōu)化器自然選擇全表掃描。不過這里有個(gè)經(jīng)驗(yàn)之談如果你的表上NOT IN的值只占極少比例并且MySQL統(tǒng)計(jì)信息足夠準(zhǔn)確它也有小概率走索引。所以別一棍子打死要看執(zhí)行計(jì)劃。但對(duì)于絕大多數(shù)場(chǎng)景強(qiáng)制走索引往往比全表更慢我們不要把“索引失效”絕對(duì)化。換個(gè)思路如果業(yè)務(wù)上需要排除某幾個(gè)狀態(tài)可以試著把條件改成IN一個(gè)正面的狀態(tài)列表例如WHERE status IN (1, 2, 3)這樣索引利用機(jī)會(huì)會(huì)大很多。如果確實(shí)要排除也可以考慮用LEFT JOIN加IS NULL的方式改寫但要注意數(shù)據(jù)量、連接順序和額外開銷不一定總是更好。2.6 索引列參與了數(shù)值運(yùn)算和函數(shù)一個(gè)道理WHERE price * 100 500會(huì)把price列先算出結(jié)果再去比較索引自然幫不上忙。正確的寫法是把運(yùn)算移到等號(hào)另一側(cè)WHERE price 500 / 100建議把這類SQL歸類為“標(biāo)準(zhǔn)寫法”在代碼評(píng)審時(shí)重點(diǎn)檢查。有人可能會(huì)問“MySQL優(yōu)化器那么智能能不能自動(dòng)把price * 100 500改成price 5”很遺憾MySQL的優(yōu)化器并不會(huì)這么智能尤其是當(dāng)表達(dá)式涉及列和常量混算時(shí)它無法保證做等價(jià)變形一定不改變浮點(diǎn)精度或整型溢出所以寧可保守地全表掃。這時(shí)候人工改寫是最可靠的。3. 聯(lián)合索引和排序場(chǎng)景中最容易踩的失效坑如果說上面那些是“單列索引的明槍”那聯(lián)合索引絕對(duì)是“暗箭”。很多失效并不是SQL寫錯(cuò)了而是你對(duì)聯(lián)合索引的理解不夠深。這一節(jié)我準(zhǔn)備把聯(lián)合索引、范圍查詢、排序和分組這四個(gè)場(chǎng)景放在一起講因?yàn)樗鼈兊牡讓舆壿嬍窍嗤ǖ摹?.1 最左前綴聯(lián)合索引的第一條軍規(guī)聯(lián)合索引(a, b, c)實(shí)際創(chuàng)建的是一個(gè)按a、b、c依次排序的復(fù)合結(jié)構(gòu)。MySQL可以命中索引的寫法必須符合最左前綴原則查詢條件里必須包含a并且是“從左到右連續(xù)”的。最容易犯的錯(cuò)是跳過最左列直接查cWHERE c xxx -- 無法命中(a,b,c)索引還有的人喜歡把條件順序打亂WHERE b ? AND a ?。這點(diǎn)MySQL優(yōu)化器能做優(yōu)化即使順序不同它也會(huì)重排成a ? AND b ?所以只要最左列存在就行。但如果你給的是WHERE b ? AND c ?缺失了a就是徹底失效。還有一個(gè)容易被忽略的場(chǎng)景WHERE a IN (...) AND b ?。如果你在a列用了IN它依然會(huì)走索引但是b列的后續(xù)匹配會(huì)受到一些影響因?yàn)镮N本質(zhì)上是一個(gè)區(qū)間集合優(yōu)化器把它當(dāng)成多區(qū)間處理b列的有序性在每個(gè)區(qū)間內(nèi)仍然可以保持所以嚴(yán)格說b也可以使用但要看統(tǒng)計(jì)和成本。這里建議你直接看EXPLAIN的key_len判斷實(shí)際用了幾個(gè)字段不要憑感覺。3.2 范圍條件會(huì)切斷后續(xù)列的使用繼續(xù)用(a,b,c)舉例WHERE a 1 AND b 2 AND c 3這條SQL里a和b可以用到索引但b的范圍判斷影響到了c列因?yàn)楫?dāng)b是一個(gè)不連續(xù)的范圍時(shí)c在b范圍內(nèi)的排序已經(jīng)失去意義MySQL無法繼續(xù)精確匹配c所以c的索引部分被浪費(fèi)了。這不是“索引整個(gè)失效”而是“部分失效”。很多同學(xué)在排查時(shí)看到typerange以為沒問題但看key_len會(huì)發(fā)現(xiàn)它其實(shí)比完全等值匹配短了一截。要優(yōu)化可以把b2改寫成b in (3,4,5)這種枚舉值列表或者調(diào)整聯(lián)合索引順序把等值判斷的列放在前面。舉例來說業(yè)務(wù)常見查詢是“按狀態(tài)和時(shí)間范圍查數(shù)據(jù)”那索引可以設(shè)計(jì)成(status, create_time)讓status作為等值前綴時(shí)間作為范圍后綴這樣兩部分都能用上如果設(shè)計(jì)成(create_time, status)那status就會(huì)因?yàn)閏reate_time的范圍而被浪費(fèi)。設(shè)計(jì)聯(lián)合索引時(shí)一定要先列出所有高頻查詢的條件把所有等值條件列優(yōu)先放在最前面范圍條件放后面。3.3 排序字段忘掉最左前綴filesort悄悄出現(xiàn)ORDER BY同樣要遵守最左前綴。比如聯(lián)合索引(a, b, c)以下排序是可以避免文件排序的ORDER BY a, b, c ORDER BY a DESC, b DESC, c DESC WHERE a 1 ORDER BY b, c但下面這些就會(huì)觸發(fā)filesortORDER BY b, c ORDER BY a, c WHERE a 1 ORDER BY c, b為什么WHERE a 1 ORDER BY c, b也不走索引因?yàn)樗饕樞蚴莂,b,c在a等值的情況下b的排序仍然生效但你不排序b卻排序c和索引的有序性沖突。這里有個(gè)隱藏點(diǎn)如果所有排序字段方向不一致比如一個(gè)升序一個(gè)降序MySQL 8.0之前也無法利用索引8.0僅對(duì)特定方向支持。寫排序時(shí)需要多看一眼索引定義。另外還要注意如果SQL里同時(shí)有WHERE過濾和ORDER BYMySQL會(huì)先嘗試用索引完成WHERE過濾再用同一索引完成排序。如果WHERE條件用范圍消耗掉了索引的后續(xù)列排序階段就可能重新面臨filesort。這種時(shí)候可以考慮索引等值列, 排序列把排序需求直接焊死在索引里。3.4 分組和去重一樣受制于索引順序GROUP BY在邏輯上會(huì)先排序再分組所以它和ORDER BY一樣依賴最左前綴。一個(gè)典型的失效場(chǎng)景是SELECT status, category, COUNT(*) FROM orders GROUP BY category, status如果索引是(status, category)那么這里排序順序就顛倒了觸發(fā)臨時(shí)表和filesort。你可以在EXPLAIN的Extra里看到Using temporary; Using filesort這就是索引失效帶來的連鎖反應(yīng)。臨時(shí)表可能存儲(chǔ)在內(nèi)存或磁盤上一旦數(shù)據(jù)量超過tmp_table_size就會(huì)溢寫到磁盤性能急劇下降。優(yōu)化思路是調(diào)整索引順序?yàn)?category, status)或者把分組查詢改寫為先用子查詢把必要行縮小再在外面分組。不過要注意GROUP BY本身帶有去重語義如果業(yè)務(wù)允許可以嘗試用窗口函數(shù)或先排序后去重的方式替代但兩者邏輯要完全一致。實(shí)際上MySQL的GROUP BY實(shí)現(xiàn)會(huì)把NULL也當(dāng)成一個(gè)分組所以如果分組列上NULL值很多也會(huì)影響效率這一點(diǎn)很多人并不清楚。4. 執(zhí)行計(jì)劃下鉆用EXPLAIN破解為什么沒走索引前面說了這么多原因但你實(shí)際寫SQL時(shí)不可能背完所有禁忌更可靠的手段是拿EXPLAIN去驗(yàn)證。我把最常見的檢查方法整理成一套“三板斧”遇到疑似索引失效時(shí)照著看。真正的DBA排查問題從來不是靠猜而是靠這些證據(jù)層層下鉆。4.1 type字段索引可用性的第一信號(hào)EXPLAIN中的type字段從好到差大概有systemconsteq_refrefrangeindexALL。如果出現(xiàn)ALL基本就是全表掃描索引失效或優(yōu)化器不想用索引。出現(xiàn)index時(shí)表示遍歷了整棵索引樹不是通過索引定位而是因?yàn)樗饕龢浔染奂饕?yōu)化器選擇“掃描索引樹”來避免回表只能算“部分救場(chǎng)”。range說明用了索引范圍掃描常見于BETWEEN、IN、 等這是健康的。ref、eq_ref、const都是等值命中的情況是最理想的狀態(tài)。有時(shí)候你會(huì)發(fā)現(xiàn)type是index但key明明有值于是誤以為索引被用上了其實(shí)這里全樹掃描的意義和全表差不多只是由于索引體積小掃描成本低一點(diǎn)。如果SQL需要返回大量行index掃描可能比ALL稍好但依然不理想。真正判斷是不是高效命中的關(guān)鍵還是要結(jié)合rows和key_len一起看。4.2 key_len 和 rows判斷是否“完整用上”了聯(lián)合索引key_len是判斷聯(lián)合索引到底用了多少列的核心依據(jù)。例如索引(a varchar(50), b int, c datetime)當(dāng)SQL只用到a時(shí)key_len只有a那段的長(zhǎng)度用到了a和b就會(huì)更長(zhǎng)。如果把每次EXPLAIN的key_len記錄下來對(duì)比你很容易發(fā)現(xiàn)“范圍條件切斷后續(xù)列”的小動(dòng)作。rows是優(yōu)化器預(yù)估需要掃描的行數(shù)。如果預(yù)估行數(shù)接近全表行數(shù)即便索引被使用也可能因?yàn)榛乇沓杀靖叨艞壥褂谩＿@時(shí)候需要看是否可以使用覆蓋索引把要查詢的字段都放進(jìn)索引里減少回表。比如索引(create_time, status, amount)而SQL是SELECT create_time, status, amount FROM order_detail WHERE create_time BETWEEN ... AND status 1那么所有需要的字段都從索引里拿到不需要回表Extra就會(huì)出現(xiàn)Using index。如果還要查詢order_no這個(gè)字段不在索引里就會(huì)在取出索引記錄后回表讀取完整行成本上升。所以覆蓋索引在設(shè)計(jì)時(shí)往往是“用空間換時(shí)間”的經(jīng)典手段對(duì)有大量高頻、固定字段查詢的場(chǎng)景特別有效。4.3 Extra列里的“Using where”和“Using filesort”Using where出現(xiàn)在SQL走了某個(gè)索引但還有少量字段在引擎層進(jìn)一步過濾。它不代表索引失效但如果你發(fā)現(xiàn)在索引命中的情況下仍然大量出現(xiàn)可能要考慮是否某些查詢列沒有在索引中或者索引設(shè)計(jì)有冗余。比如說索引(a, b)SQL是WHERE a 1 AND c 2這里a走了索引c的過濾就必須靠Using where。如果c的過濾選擇性很高那你可能需要把c也加入索引。Using filesort是排序索引失效的直接證據(jù)。它意味著MySQL無法利用已有索引的有序性必須另起一段內(nèi)存或磁盤進(jìn)行排序。要消除它重點(diǎn)檢查ORDER BY和GROUP BY是否對(duì)齊了索引列順序。如果在Extra里看到Using temporary; Using filesort同時(shí)出現(xiàn)通常是GROUP BY或DISTINCT把臨時(shí)表都用上了這種時(shí)候要格外小心數(shù)據(jù)量一大性能會(huì)爆炸。4.4 一個(gè)完整的EXPLAIN實(shí)戰(zhàn)分析我們用一個(gè)例子走一遍EXPLAIN SELECT id, user_id, amount FROM order_detail WHERE DATE(create_time) 2024-11-01 ORDER BY id DESC執(zhí)行計(jì)劃結(jié)果的關(guān)鍵列是typeALL,possible_keysidx_create_time,keyNULL,rows19000000,ExtraUsing where。雖然possible_keys寫出了idx_create_time但key是NULL說明因?yàn)楹瘮?shù)運(yùn)算優(yōu)化器直接放棄索引。把SQL改成WHERE create_time 2024-11-01 00:00:00再看type變成rangekey變成idx_create_timerows降到幾十萬問題清晰可見。這就是用工具還原真相的過程。如果用的是MySQL 8.0.18以上還可以加上ANALYZE關(guān)鍵字EXPLAIN ANALYZE SELECT ...它會(huì)返回每個(gè)操作的實(shí)際執(zhí)行時(shí)間和行數(shù)比靜態(tài)的EXPLAIN更真實(shí)。有一次我被一個(gè)奇怪的執(zhí)行計(jì)劃誤導(dǎo)了很久EXPLAIN顯示全表掃描但實(shí)際執(zhí)行卻很快后來才發(fā)現(xiàn)是因?yàn)閮?yōu)化器把所選列都覆蓋到了二級(jí)索引而EXPLAIN的舊版本沒有展示這個(gè)細(xì)節(jié)。所以工具要盡量用新版本多參考Extra真實(shí)反饋。5. 索引失效的預(yù)防良藥從規(guī)范約束到優(yōu)化實(shí)踐無論是定位了一次事故還是剛剛梳理完全部原因最終目標(biāo)都是“少踩坑”。下面這些方法是自己在公司實(shí)踐了一段時(shí)間后覺得最有用的。它們不是一次性的優(yōu)化技巧而是應(yīng)該固化到日常研發(fā)流程里的動(dòng)作。5.1 代碼評(píng)審階段的SQL規(guī)約我們團(tuán)隊(duì)把常見索引失效原因?qū)戇M(jìn)了一頁SQL開發(fā)規(guī)約評(píng)審時(shí)逐條打勾禁止對(duì)索引列進(jìn)行函數(shù)、運(yùn)算或隱式類型轉(zhuǎn)換。禁止使用前導(dǎo)模糊查詢除非有全文索引。聯(lián)合索引必須保證查詢條件從左到右持續(xù)匹配。排序/分組字段必須與聯(lián)合索引順序一致。使用OR時(shí)必須確保所有條件列都有可用索引盡量改成UNION ALL。你可能覺得這些約束太機(jī)械但生產(chǎn)事故往往來自“偶爾一次”的小聰明。把它落到評(píng)審里比事后救火強(qiáng)十倍。評(píng)審時(shí)不要只看SQL本身還要帶上表結(jié)構(gòu)和執(zhí)行計(jì)劃。我見過很多團(tuán)隊(duì)評(píng)審只看代碼邏輯執(zhí)行計(jì)劃壓根不看結(jié)果上線后慢查詢直接打到告警平臺(tái)。對(duì)于新上線的高頻查詢我習(xí)慣要求開發(fā)在PR描述里附上EXPLAIN關(guān)鍵字段截圖并回答“key用的哪個(gè)索引”“rows預(yù)估多少”“有沒有filesort”這三個(gè)問題能堵住絕大多數(shù)坑。5.2 數(shù)據(jù)模型層面的提前設(shè)計(jì)索引失效的也不少是建表時(shí)就埋下的雷字段類型盡量使用數(shù)值型或固定長(zhǎng)度的字符串避免不同類型比較。存儲(chǔ)手機(jī)號(hào)、身份證號(hào)這類定長(zhǎng)字段直接用char或bigint。冗余“范圍查詢”的對(duì)比值比如把日期時(shí)間拆分成日期和時(shí)分兩個(gè)列讓等值查詢有機(jī)會(huì)走上聯(lián)合索引。如果業(yè)務(wù)明確有函數(shù)查詢需求優(yōu)先考慮MySQL 8.0的函數(shù)索引或者把原始值加工結(jié)果單獨(dú)存儲(chǔ)一個(gè)列。這里想說一個(gè)真實(shí)踩過的坑我們?cè)?jīng)有一張訂單表業(yè)務(wù)方喜歡按“月”查數(shù)據(jù)SQL里寫WHERE MONTH(create_time)11后來在應(yīng)用層加了一個(gè)month字段來冗余但是代碼沒有同步更新索引倒是建了month結(jié)果SQL還在用MONTH(create_time)執(zhí)行計(jì)劃不光失效還會(huì)因?yàn)轭~外的month索引增加寫入開銷。后來我們把冗余字段落到表里并強(qiáng)制要求SQL使用month11性能才恢復(fù)正常。所以冗余字段一定要和SQL標(biāo)準(zhǔn)配合否則就是白白占空間。5.3 用慢查詢?nèi)罩竞脱矙z腳本主動(dòng)發(fā)現(xiàn)問題被動(dòng)等告警很難受不如主動(dòng)“排雷”。生產(chǎn)環(huán)境可以開啟慢查詢?nèi)罩径ㄆ趻呙鑝ysqldumpslow結(jié)果把那些長(zhǎng)時(shí)間執(zhí)行的SQL全部拎出來做EXPLAIN。再配合一個(gè)月跑一次的索引統(tǒng)計(jì)信息更新ANALYZE TABLE能有效防止統(tǒng)計(jì)值過期導(dǎo)致優(yōu)化器跑偏。我自己習(xí)慣用一條命令導(dǎo)出TOP慢SQLmysqldumpslow -s at -t 20 /var/log/mysql/slow.log然后對(duì)每個(gè)出現(xiàn)次數(shù)多的SQL執(zhí)行EXPLAIN重點(diǎn)看是否有索引失效?？梢园l(fā)現(xiàn)一個(gè)現(xiàn)象很多慢SQL并不是每一次都慢而是數(shù)據(jù)增長(zhǎng)到某個(gè)量級(jí)后突然變慢。這就是因?yàn)閮?yōu)化器基于過舊的統(tǒng)計(jì)信息做出了錯(cuò)誤判斷。定期ANALYZE TABLE的成本很低但收益非?？捎^。如果你用的是MySQL 5.7及以上還可以設(shè)置innodb_stats_auto_recalc1讓表數(shù)據(jù)變化超過10%時(shí)自動(dòng)重新計(jì)算統(tǒng)計(jì)信息。5.4 優(yōu)化器不夠聰明時(shí)該怎么辦有些情況下SQL已經(jīng)寫對(duì)了但優(yōu)化器還是選擇全表掃描。比如一個(gè)表上有索引但選擇性太低重復(fù)值太多或者統(tǒng)計(jì)信息不準(zhǔn)。這時(shí)候先強(qiáng)制執(zhí)行一下試試FORCE INDEX看是否真的更快。不要長(zhǎng)期依賴它只是臨時(shí)排查手段。重新分析統(tǒng)計(jì)信息執(zhí)行ANALYZE TABLE table_name讓優(yōu)化器“刷新認(rèn)知”。重寫SQL邏輯把大查詢拆成小查詢把復(fù)雜的關(guān)聯(lián)拆成兩步通常讓優(yōu)化器更清晰。必要時(shí)調(diào)整索引結(jié)構(gòu)增加覆蓋索引把SELECT列都放進(jìn)索引減少回表成本。這里還要提一個(gè)和索引失效容易混淆的話題——索引下推Index Condition PushdownICP。當(dāng)聯(lián)合索引(a,b)中a條件滿足后如果對(duì)b的過濾能下推到索引層MySQL會(huì)把Using index condition打在Extra里。這不叫失效反而是5.6之后的一個(gè)優(yōu)化手段。你千萬不要一看到Using index condition就覺得炸了要分清場(chǎng)景。ICP是針對(duì)“索引內(nèi)字段條件無法走最左連續(xù)匹配”時(shí)的一種補(bǔ)償它把部分WHERE過濾條件下推到存儲(chǔ)引擎在讀取索引記錄時(shí)就做判斷減少回表次數(shù)。雖然它不能像范圍匹配那樣精確利用索引順序但已經(jīng)比完全回表后再過濾要高效。理解了這一點(diǎn)再看Extra就會(huì)更從容。說到底索引失效不是玄學(xué)背后都是B樹的有序性和優(yōu)化器的成本核算。只要你在寫SQL時(shí)多問一句“這個(gè)條件能不能直接利用索引樹的有序性”多數(shù)坑都能繞過去。我自己習(xí)慣在新項(xiàng)目核心SQL上線前把EXPLAIN輸出截個(gè)圖當(dāng)作準(zhǔn)入條件久而久之線上“突然變慢”的報(bào)警少了大半。索引優(yōu)化是一項(xiàng)需要持續(xù)投入耐心的工作但它帶來的穩(wěn)定性和性能收益遠(yuǎn)比一時(shí)趕工的價(jià)值要大。