1. 從“能用”到“好用”一次慢SQL引發(fā)的索引深度思考那天下午監(jiān)控系統(tǒng)突然告警一個(gè)核心業(yè)務(wù)接口的響應(yīng)時(shí)間從平時(shí)的幾十毫秒飆升至數(shù)秒。登錄服務(wù)器一看CPU使用率倒是不高但磁盤I/O等待隊(duì)列長得嚇人。用SHOW PROCESSLIST一查果然有幾條“老朋友”SQL正在慢吞吞地執(zhí)行。這已經(jīng)不是第一次了每次業(yè)務(wù)量一上來這些查詢就成了性能瓶頸。我意識(shí)到過去那種“給WHERE條件加個(gè)索引”的初級(jí)優(yōu)化手段已經(jīng)不夠用了。我們需要的不是讓SQL“能跑”而是讓它“跑得快”、“跑得穩(wěn)”。這背后涉及到對(duì)MySQL索引機(jī)制更深層次的理解和應(yīng)用比如如何讓查詢完全“躺”在索引上完成覆蓋索引如何為超長字段設(shè)計(jì)高效的索引前綴索引以及如何讓存儲(chǔ)引擎在掃描索引時(shí)就提前過濾數(shù)據(jù)索引下推。今天我就結(jié)合那次排查和后續(xù)一系列優(yōu)化的實(shí)戰(zhàn)經(jīng)歷把這些高級(jí)篇里的核心知識(shí)點(diǎn)掰開揉碎了講清楚它們正是將數(shù)據(jù)庫性能從及格線提升到優(yōu)秀線的關(guān)鍵。2. 覆蓋索引讓查詢告別回表的“性能加速器”2.1 核心原理為什么“不回表”如此重要要理解覆蓋索引首先得明白一次普通索引查詢的完整路徑。當(dāng)你執(zhí)行一條SELECT * FROM users WHERE name ‘張三’的查詢并且name字段上有索引時(shí)MySQL的InnoDB引擎會(huì)經(jīng)歷兩個(gè)關(guān)鍵步驟索引掃描在name索引的B樹中快速定位到name’張三’的記錄并獲取到該記錄對(duì)應(yīng)的主鍵ID。回表查詢拿著這個(gè)主鍵ID回到**主鍵索引聚簇索引**的B樹中去查找該ID對(duì)應(yīng)的完整數(shù)據(jù)行即*代表的所有列。這個(gè)“回表”操作意味著額外的磁盤I/O如果數(shù)據(jù)頁不在內(nèi)存中和主鍵索引樹的查找開銷。當(dāng)需要查詢的數(shù)據(jù)量很大時(shí)大量的隨機(jī)I/O會(huì)迅速成為性能殺手。而覆蓋索引的精髓就在于只需要掃描索引本身就能獲取查詢所需要的全部數(shù)據(jù)從而徹底避免回表操作。如何實(shí)現(xiàn)就是讓查詢的字段列表SELECT后的字段和查詢條件WHERE后的字段都“包含”在某個(gè)索引的列中。舉個(gè)例子我們有一張訂單表orders經(jīng)常需要根據(jù)用戶ID和訂單狀態(tài)來查詢訂單號(hào)和金額SELECT order_no, amount FROM orders WHERE user_id 1001 AND status ‘PAID’;如果我們?cè)?user_id, status)上建立一個(gè)普通索引查詢時(shí)依然需要回表去取order_no和amount。但如果我們建立的是(user_id, status, order_no, amount)這樣一個(gè)聯(lián)合索引奇跡就發(fā)生了。這個(gè)索引的葉子節(jié)點(diǎn)按順序存儲(chǔ)了user_id, status, order_no, amount的值。當(dāng)執(zhí)行上述查詢時(shí)引擎在(user_id, status)這兩列上快速定位后發(fā)現(xiàn)需要的order_no和amount就在當(dāng)前索引葉子節(jié)點(diǎn)上伸手可得于是直接返回結(jié)果整個(gè)過程完全在索引樹上完成效率極高。注意覆蓋索引的優(yōu)勢(shì)在查詢數(shù)據(jù)量較大時(shí)尤為明顯。對(duì)于只返回幾條記錄的查詢回表開銷可以忽略。但當(dāng)需要掃描索引的很大一部分比如分頁查詢靠后的數(shù)據(jù)時(shí)避免回表帶來的隨機(jī)I/O性能提升是指數(shù)級(jí)的。2.2 設(shè)計(jì)與權(quán)衡如何構(gòu)建高效的覆蓋索引覆蓋索引雖好但不能濫用。索引本身需要占用存儲(chǔ)空間并會(huì)增加數(shù)據(jù)插入、更新、刪除時(shí)的維護(hù)成本。在設(shè)計(jì)時(shí)需要權(quán)衡以下幾點(diǎn)遵循最左前綴原則聯(lián)合索引(a, b, c)其生效方式可以是(a),(a,b),(a,b,c)。你的查詢條件必須從最左列開始匹配。把上面例子中的索引設(shè)計(jì)成(status, user_id, order_no, amount)對(duì)于WHERE user_id ?的查詢就是無效的。選擇性高的列放前面在滿足最左前綴的前提下將區(qū)分度更高唯一值更多的列放在聯(lián)合索引的前面能讓索引過濾掉更多的數(shù)據(jù)行縮小掃描范圍。例如(user_id, status)通常比(status, user_id)更好因?yàn)閡ser_id的選擇性一般遠(yuǎn)高于status。謹(jǐn)慎包含過長字段為了覆蓋查詢而將TEXT、VARCHAR(1000)這樣的超長字段加入索引會(huì)導(dǎo)致索引樹變得非常龐大雖然可能覆蓋了查詢但掃描索引本身的代價(jià)就變大了可能得不償失。這時(shí)就需要考慮下一節(jié)要講的前綴索引。利用索引完成排序如果查詢包含ORDER BY子句而排序字段的順序與覆蓋索引的列順序一致時(shí)MySQL可以直接利用索引的有序性來返回結(jié)果避免額外的排序操作Using filesort。例如索引(user_id, create_time)對(duì)于WHERE user_id? ORDER BY create_time的查詢就是完美的。實(shí)操心得在真實(shí)業(yè)務(wù)中我經(jīng)常使用EXPLAIN命令來驗(yàn)證覆蓋索引是否生效。當(dāng)Extra字段出現(xiàn)Using index時(shí)恭喜你覆蓋索引成功命中。這是一個(gè)非常直觀且重要的優(yōu)化信號(hào)。3. 前綴索引針對(duì)超長字段的“空間換性能”藝術(shù)3.1 適用場(chǎng)景與權(quán)衡當(dāng)表中存在VARCHAR(255)、TEXT甚至BLOB類型的字段又需要根據(jù)這些字段進(jìn)行查詢時(shí)為其建立完整長度的索引是極其奢侈且低效的。索引樹中每個(gè)節(jié)點(diǎn)都要存儲(chǔ)完整的字段值導(dǎo)致索引體積暴增內(nèi)存中能緩存的索引頁變少磁盤I/O增加。前綴索引就是解決這一矛盾的利器只對(duì)字段的前面一部分字符建立索引。例如為一個(gè)存儲(chǔ)郵箱地址的VARCHAR(100)字段只對(duì)其前10個(gè)字符建立索引。這樣索引體積會(huì)小很多查詢時(shí)先通過前綴索引快速定位到一批“候選行”然后再回到聚簇索引中取出這批次數(shù)據(jù)的完整字段值進(jìn)行精確匹配。這里的關(guān)鍵在于前綴長度的選擇。長度太短區(qū)分度不夠會(huì)掃描出大量無效的候選行增加回表次數(shù)長度太長又失去了節(jié)約空間的意義。目標(biāo)是在保證足夠區(qū)分度的前提下盡可能選擇短的長度。3.2 如何科學(xué)確定最佳前綴長度靠猜是不行的MySQL提供了數(shù)據(jù)支撐的方法。假設(shè)我們要為users表的email字段建立前綴索引計(jì)算完整列的選擇性選擇性是指不重復(fù)的索引值基數(shù)與數(shù)據(jù)表總行數(shù)的比值范圍在0到1之間。值越高索引效率越好。SELECT COUNT(DISTINCT email) / COUNT(*) AS selectivity FROM users;假設(shè)得到結(jié)果0.95。計(jì)算不同前綴長度的選擇性通過LEFT()函數(shù)截取不同長度的前綴計(jì)算其選擇性。SELECT COUNT(DISTINCT LEFT(email, 5)) / COUNT(*) AS sel5, COUNT(DISTINCT LEFT(email, 10)) / COUNT(*) AS sel10, COUNT(DISTINCT LEFT(email, 15)) / COUNT(*) AS sel15, COUNT(DISTINCT LEFT(email, 20)) / COUNT(*) AS sel20 FROM users;假設(shè)得到結(jié)果sel50.65,sel100.92,sel150.95,sel200.95。分析結(jié)果并決策從結(jié)果看前綴長度從10增加到15選擇性從0.92提升到0.95提升顯著但從15到20選擇性沒有變化。因此選擇前綴長度15是一個(gè)性價(jià)比很高的點(diǎn)。它用15個(gè)字符的長度獲得了與完整字段近乎相同的區(qū)分度。創(chuàng)建前綴索引ALTER TABLE users ADD INDEX idx_email_prefix (email(15));重要注意事項(xiàng)前綴索引無法用于ORDER BY和GROUP BY操作也無法作為覆蓋索引使用因?yàn)樗饕锊话侄蔚耐暾?。如果你的查詢需要用到這些操作就需要慎重考慮。踩坑記錄我曾經(jīng)對(duì)一個(gè)存儲(chǔ)文件路徑的字段使用了前綴索引。大部分路徑前綴都很相似如/uploads/2023/導(dǎo)致前綴索引區(qū)分度極低查詢性能甚至比全表掃描還差。后來改為對(duì)路徑的哈希值例如CRC32(path)建立索引查詢時(shí)先匹配哈希值再精確匹配路徑性能大幅提升。這是前綴索引不適用的一個(gè)典型案例。4. 索引下推MySQL 5.6帶來的“查詢革命”4.1 什么是索引下推索引下推是MySQL 5.6版本引入的一項(xiàng)重大優(yōu)化它的全稱是Index Condition Pushdown。在沒有ICP之前存儲(chǔ)引擎的職責(zé)相對(duì)簡(jiǎn)單根據(jù)索引的查找條件定位到相關(guān)的記錄然后把這些記錄的主鍵返回給Server層。Server層再根據(jù)其他的WHERE條件對(duì)這些主鍵對(duì)應(yīng)的完整數(shù)據(jù)行進(jìn)行過濾。引入ICP之后事情發(fā)生了變化。存儲(chǔ)引擎在掃描索引的過程中就可以利用索引中包含的列對(duì)WHERE條件中索引相關(guān)的部分進(jìn)行判斷。如果某條索引記錄不滿足這些條件存儲(chǔ)引擎會(huì)直接將其跳過而不會(huì)將其主鍵返回給Server層。這相當(dāng)于把一部分過濾工作“下推”到了更底層、更靠近數(shù)據(jù)的地方減少了向上層傳輸?shù)臄?shù)據(jù)量。4.2 一個(gè)經(jīng)典案例解析假設(shè)我們有一張人員表people有聯(lián)合索引(zipcode, lastname, firstname)?，F(xiàn)在要執(zhí)行一條查詢SELECT * FROM people WHERE zipcode‘95054’ AND lastname LIKE ‘%etrunia%’ AND address LIKE ‘%Main Street%’;在這個(gè)查詢中zipcode使用了等值匹配可以利用索引。lastname使用了LIKE ‘%xxx%’這是范圍查詢但因?yàn)樗苍谒饕星椅挥趜ipcode之后所以索引可以用于范圍掃描到lastname為止。address字段不在索引中。在沒有ICP的情況下存儲(chǔ)引擎使用索引找到所有zipcode‘95054’的記錄。由于lastname LIKE ‘%etrunia%’無法使用索引進(jìn)行精確過濾因?yàn)榍熬Y是通配符%存儲(chǔ)引擎會(huì)將所有zipcode‘95054’的記錄的主鍵都返回給Server層。Server層根據(jù)這些主鍵回表取出完整數(shù)據(jù)行然后依次用lastname LIKE ‘%etrunia%’和address LIKE ‘%Main Street%’進(jìn)行過濾。在啟用ICP的情況下存儲(chǔ)引擎同樣使用索引找到所有zipcode‘95054’的記錄。關(guān)鍵區(qū)別來了存儲(chǔ)引擎在掃描索引時(shí)發(fā)現(xiàn)lastname也在索引列中。雖然LIKE ‘%etrunia%’不能用于索引查找但可以用于索引過濾因此存儲(chǔ)引擎會(huì)在索引層面就對(duì)每一條記錄的lastname值應(yīng)用LIKE ‘%etrunia%’條件進(jìn)行判斷。只有那些同時(shí)滿足zipcode‘95054’且lastname LIKE ‘%etrunia%’的索引記錄其主鍵才會(huì)被返回給Server層。Server層回表后只需用address LIKE ‘%Main Street%’這一個(gè)條件進(jìn)行過濾?？梢钥吹絀CP極大地減少了從存儲(chǔ)引擎層返回到Server層的主鍵數(shù)量從而減少了回表操作的次數(shù)尤其是在lastname條件能過濾掉大量數(shù)據(jù)的情況下性能提升會(huì)非常顯著。4.3 如何確認(rèn)與使用ICPICP是默認(rèn)開啟的。你可以通過EXPLAIN命令查看查詢執(zhí)行計(jì)劃如果Extra列中出現(xiàn)了Using index condition就說明該查詢使用了索引下推優(yōu)化。優(yōu)化階段存儲(chǔ)引擎工作Server層工作傳輸數(shù)據(jù)量無ICP僅根據(jù)索引最左前綴(zipcode)定位數(shù)據(jù)負(fù)責(zé)所有非索引列條件過濾(lastname,address)大 (所有zipcode匹配的主鍵)有ICP根據(jù)索引最左前綴(zipcode)定位并利用索引列(lastname)提前過濾負(fù)責(zé)非索引列條件過濾(address)小 (經(jīng)過lastname過濾后的主鍵)實(shí)操心得ICP優(yōu)化效果的好壞取決于被“下推”的那個(gè)條件如例子中的lastname LIKE的過濾性。如果這個(gè)條件能過濾掉90%的數(shù)據(jù)那么ICP效果拔群如果它幾乎過濾不掉數(shù)據(jù)那ICP的收益就微乎其微。理解這一點(diǎn)有助于你在分析執(zhí)行計(jì)劃時(shí)判斷Using index condition是否真的帶來了實(shí)質(zhì)性的性能提升。5. 系統(tǒng)性SQL優(yōu)化從編寫到執(zhí)行的完整心法索引是利器但寫出好的SQL才是根本。優(yōu)化是一個(gè)系統(tǒng)工程需要從編寫、到執(zhí)行計(jì)劃分析、再到持續(xù)監(jiān)控的完整閉環(huán)。5.1 編寫階段的避坑指南避免使用SELECT ***這是老生常談但至關(guān)重要。明確列出需要的字段是使用覆蓋索引的前提。網(wǎng)絡(luò)傳輸和內(nèi)存開銷也會(huì)更小。謹(jǐn)慎使用OR多個(gè)OR條件往往導(dǎo)致索引失效。例如WHERE a1 OR b2如果a和b上各有單列索引MySQL通常只能使用其中一個(gè)或者退而求其次使用全表掃描。考慮改用UNION或UNION ALL來改寫。-- 低效 SELECT * FROM t WHERE a1 OR b2; -- 改寫為 SELECT * FROM t WHERE a1 UNION ALL SELECT * FROM t WHERE b2 AND a!1; -- 注意去重或用UNION注意LIKE查詢的寫法LIKE ‘%關(guān)鍵字%’和LIKE ‘%關(guān)鍵字’會(huì)導(dǎo)致索引失效因?yàn)锽樹無法從模糊的頭部開始比較。盡量使用LIKE ‘關(guān)鍵字%’如果業(yè)務(wù)必須前綴模糊考慮使用全文索引FULLTEXT或?qū)ｉT的搜索引擎。小心數(shù)據(jù)類型轉(zhuǎn)換在WHERE子句中如果對(duì)索引字段使用函數(shù)或進(jìn)行類型轉(zhuǎn)換索引會(huì)失效。例如WHERE DATE(create_time)‘2023-10-01’應(yīng)該改為范圍查詢WHERE create_time ‘2023-10-01’ AND create_time ‘2023-10-02’。優(yōu)化IN和NOT ININ查詢?cè)诹斜碇递^少時(shí)效率尚可。但當(dāng)列表值非常多時(shí)優(yōu)化器可能認(rèn)為全表掃描成本更低。對(duì)于NOT IN則幾乎總是低效的可考慮用NOT EXISTS或LEFT JOIN ... IS NULL來改寫。5.2 深入理解與使用EXPLAINEXPLAIN是你的最佳診斷工具?？磮?zhí)行計(jì)劃要重點(diǎn)關(guān)注以下幾列type訪問類型從好到壞大致是system const eq_ref ref range index ALL。至少要達(dá)到range級(jí)別最好能到ref。key實(shí)際使用的索引。如果為NULL說明沒用到索引。rowsMySQL預(yù)估需要掃描的行數(shù)。這是一個(gè)非常重要的估值結(jié)合filtered列可以判斷查詢效率。Extra包含額外信息是優(yōu)化的關(guān)鍵提示。Using index使用了覆蓋索引大好事。Using index condition使用了索引下推。Using whereServer層在存儲(chǔ)引擎返回行之后進(jìn)行了過濾。如果rows值很大這可能是個(gè)警告。Using temporary使用了臨時(shí)表常見于GROUP BY和ORDER BY子句的列不屬于驅(qū)動(dòng)表的索引。Using filesort使用了文件排序意味著無法利用索引順序需要在內(nèi)存或磁盤進(jìn)行額外排序性能殺手。一個(gè)分析案例一個(gè)分頁查詢SELECT * FROM logs WHERE type‘ERROR’ ORDER BY id DESC LIMIT 100000, 20;非常慢。EXPLAIN顯示typeref用到了type索引但Extra里有Using filesort。原因是ORDER BY id和WHERE type的索引順序不匹配。優(yōu)化方法是在(type, id)上建立聯(lián)合索引讓索引本身就能按type篩選后按id排序執(zhí)行計(jì)劃中的Using filesort就會(huì)消失性能提升百倍。5.3 連接查詢的優(yōu)化要點(diǎn)小表驅(qū)動(dòng)大表這是JOIN優(yōu)化的基本原則。在嵌套循環(huán)連接中應(yīng)該讓結(jié)果集小的表作為驅(qū)動(dòng)表外層循環(huán)。MySQL優(yōu)化器通常會(huì)幫你做這件事但復(fù)雜的查詢有時(shí)會(huì)選錯(cuò)?？梢允褂肧TRAIGHT_JOIN強(qiáng)制連接順序但要謹(jǐn)慎。為連接條件建立索引ON子句和WHERE子句中的等值連接字段必須要有索引。例如A JOIN B ON A.b_id B.id那么A.b_id和B.id上都應(yīng)該有索引。子查詢的陷阱相關(guān)子查詢子查詢依賴外層查詢的值性能往往很差因?yàn)樗鼤?huì)對(duì)外層查詢的每一行都執(zhí)行一次子查詢。盡可能將其改寫為JOIN。6. 主鍵設(shè)計(jì)數(shù)據(jù)庫性能的基石與業(yè)務(wù)演進(jìn)的伏筆主鍵的設(shè)計(jì)影響深遠(yuǎn)它不僅是數(shù)據(jù)的唯一標(biāo)識(shí)更直接決定了聚簇索引的組織方式進(jìn)而影響幾乎所有查詢的性能。6.1 自增ID的利與弊優(yōu)點(diǎn)簡(jiǎn)單高效插入時(shí)順序追加不會(huì)導(dǎo)致頁分裂寫入性能極高?？臻g緊湊通常是BIGINT占用空間小所有二級(jí)索引都存儲(chǔ)主鍵值主鍵小則二級(jí)索引也小。缺點(diǎn)缺乏業(yè)務(wù)意義對(duì)業(yè)務(wù)查詢無直接幫助。分布式場(chǎng)景挑戰(zhàn)在分庫分表或分布式數(shù)據(jù)庫中需要解決全局唯一性問題如雪花算法、UUID等。安全性問題連續(xù)的自增ID可能暴露業(yè)務(wù)量且容易被人遍歷爬取數(shù)據(jù)。6.2 業(yè)務(wù)主鍵的考量使用有業(yè)務(wù)意義的字段如訂單號(hào)、用戶身份證號(hào)作為主鍵。優(yōu)點(diǎn)某些查詢可以直接通過主鍵定位無需二級(jí)索引。缺點(diǎn)無序插入如果業(yè)務(wù)主鍵不是單調(diào)遞增的如UUID、哈希值插入時(shí)會(huì)導(dǎo)致聚簇索引頻繁的頁分裂與重組嚴(yán)重影響寫入性能。占用空間大如果業(yè)務(wù)主鍵是較長的字符串不僅主鍵索引龐大所有二級(jí)索引的葉子節(jié)點(diǎn)都要存儲(chǔ)這個(gè)龐大的主鍵值空間浪費(fèi)嚴(yán)重。6.3 推薦的設(shè)計(jì)策略在實(shí)踐中我傾向于采用一種混合策略主鍵使用一個(gè)與業(yè)務(wù)無關(guān)的自增BIGINT或分布式ID作為技術(shù)主鍵。它唯一、緊湊、有序保證了寫入性能和存儲(chǔ)效率。業(yè)務(wù)唯一鍵將具有業(yè)務(wù)意義的唯一標(biāo)識(shí)字段如訂單號(hào)order_no、用戶郵箱email設(shè)置為UNIQUE KEY。這樣既可以通過該字段快速查詢因?yàn)槲ㄒ凰饕屎芨哂直苊饬怂鳛橹麈I帶來的無序插入和空間膨脹問題。CREATE TABLE orders ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT ‘技術(shù)主鍵’, order_no VARCHAR(32) NOT NULL COMMENT ‘業(yè)務(wù)訂單號(hào)唯一’, user_id BIGINT NOT NULL, amount DECIMAL(10,2) NOT NULL, status TINYINT NOT NULL, create_time DATETIME NOT NULL, PRIMARY KEY (id), -- 聚簇索引有序緊湊 UNIQUE KEY uk_order_no (order_no), -- 業(yè)務(wù)唯一索引用于按訂單號(hào)查詢 KEY idx_user_status (user_id, status) -- 覆蓋索引用于用戶訂單查詢 ) ENGINEInnoDB;這種設(shè)計(jì)分離了“技術(shù)標(biāo)識(shí)”和“業(yè)務(wù)標(biāo)識(shí)”在數(shù)據(jù)庫效率與業(yè)務(wù)需求之間取得了很好的平衡。id負(fù)責(zé)高性能的存儲(chǔ)和關(guān)聯(lián)order_no負(fù)責(zé)對(duì)外的業(yè)務(wù)查詢和展示。最后的忠告數(shù)據(jù)庫優(yōu)化沒有銀彈。覆蓋索引、前綴索引、索引下推、SQL優(yōu)化、主鍵設(shè)計(jì)這些技術(shù)是工具箱里的一套組合拳。真正的優(yōu)化始于對(duì)業(yè)務(wù)查詢模式的深刻理解輔以EXPLAIN工具的持續(xù)驗(yàn)證并在不斷的監(jiān)控、分析與調(diào)整中迭代。每次優(yōu)化后記得觀察慢查詢?nèi)罩竞捅O(jiān)控指標(biāo)用數(shù)據(jù)來證明優(yōu)化的有效性從而形成一個(gè)持續(xù)改進(jìn)的正向循環(huán)。