化實(shí)戰(zhàn))
1. 這不是“加個(gè)索引就完事”的問題為什么order by、group by和分頁在MySQL里會(huì)集體失靈我第一次被線上慢查詢報(bào)警釘死在工位上是凌晨?jī)牲c(diǎn)。一個(gè)看似簡(jiǎn)單的訂單列表頁SELECT * FROM orders WHERE status 1 ORDER BY created_at DESC LIMIT 20 OFFSET 10000執(zhí)行時(shí)間從80ms飆到3.2秒。DBA甩來一句“加個(gè)索引不就完了”——我照做了ALTER TABLE orders ADD INDEX idx_status_created (status, created_at)結(jié)果查詢時(shí)間只降了17ms。那一刻我才明白把ORDER BY、GROUP BY和分頁塞進(jìn)一條SQL里不是在寫查詢是在給MySQL出一道組合數(shù)學(xué)題。這三者疊加的殺傷力遠(yuǎn)超單點(diǎn)優(yōu)化。ORDER BY要求排序GROUP BY要求分組聚合而LIMIT OFFSET要求跳過前N行——三者共同指向同一個(gè)致命瓶頸臨時(shí)表Temporary Table與文件排序Filesort的雙重開銷。當(dāng)MySQL發(fā)現(xiàn)無法利用索引直接完成排序分組跳過時(shí)它會(huì)默默創(chuàng)建一個(gè)內(nèi)存臨時(shí)表把所有滿足WHERE條件的行全撈出來再在內(nèi)存里排序、分組、計(jì)數(shù)最后才取第10001到10020行。一旦數(shù)據(jù)量突破內(nèi)存閾值tmp_table_size和max_heap_table_size臨時(shí)表就會(huì)落盤觸發(fā)磁盤I/O性能斷崖式下跌。更隱蔽的是GROUP BY常被誤認(rèn)為只是“去重”但它本質(zhì)是分組聚合操作。SELECT user_id, COUNT(*) FROM orders GROUP BY user_id表面看只返回用戶ID和訂單數(shù)但MySQL必須掃描所有匹配行按user_id哈希分桶每個(gè)桶內(nèi)累加計(jì)數(shù)——這個(gè)過程無法被簡(jiǎn)單索引跳過除非索引能覆蓋整個(gè)分組鍵聚合字段。而ORDER BY若涉及非索引字段或索引順序與排序方向沖突如索引是(a,b)卻ORDER BY a DESC, b ASC同樣觸發(fā)filesort。分頁則是壓垮駱駝的最后一根稻草。OFFSET 10000不是“跳過10000行”而是讓MySQL先找到前10000行再丟棄它們最后取接下來的20行。這意味著無論你只要20條數(shù)據(jù)MySQL都得處理10020行。當(dāng)OFFSET達(dá)到十萬級(jí)哪怕有索引B樹也要反復(fù)回表、遍歷CPU和IO雙雙拉滿。所以這不是“怎么加索引”的技術(shù)問題而是如何重構(gòu)查詢邏輯、規(guī)避MySQL執(zhí)行器固有缺陷的工程問題。接下來我會(huì)用真實(shí)生產(chǎn)環(huán)境的5個(gè)典型場(chǎng)景拆解每一種失效背后的執(zhí)行計(jì)劃真相、索引設(shè)計(jì)陷阱、以及繞過臨時(shí)表的硬核替代方案——所有結(jié)論均來自我們?nèi)站?億查詢的電商訂單庫實(shí)測(cè)數(shù)據(jù)。2. 執(zhí)行計(jì)劃里的“隱形殺手”讀懂EXPLAIN輸出中那些沉默的警告很多開發(fā)者看到EXPLAIN結(jié)果里出現(xiàn)Using filesort或Using temporary就慌了以為只要消滅這兩個(gè)詞就萬事大吉。但真正危險(xiǎn)的是那些沒報(bào)錯(cuò)卻在后臺(tái)瘋狂消耗資源的“靜默殺手”。我整理了過去半年線上慢查詢中EXPLAIN輸出最常被忽略的5個(gè)關(guān)鍵字段及其真實(shí)含義它們比type: ALL更值得警惕。2.1key_len索引實(shí)際使用長(zhǎng)度的“縮水真相”key_len顯示MySQL在索引中實(shí)際用了多少字節(jié)。很多人建了復(fù)合索引(a,b,c)EXPLAIN顯示key_len5就以為索引全用了。但a是INT(11)4字節(jié)b是VARCHAR(50)假設(shè)UTF8MB4最大200字節(jié)c是TINYINT1字節(jié)。key_len5意味著只用了a4字節(jié)b的第一個(gè)字節(jié)1字節(jié)——因?yàn)閎是變長(zhǎng)字段MySQL無法預(yù)知其實(shí)際長(zhǎng)度只能按最壞情況預(yù)留空間。真正的索引利用率要看key_len是否等于你期望使用的字段長(zhǎng)度之和。舉個(gè)真實(shí)案例一張用戶表users(id, city, age, status)需求是SELECT * FROM users WHERE citybeijing AND status1 ORDER BY age DESC。我建了索引(city, status, age)EXPLAIN顯示key_len13cityVARCHAR(20)占80bit10字節(jié)statusTINYINT占1字節(jié)ageTINYINT占1字節(jié)2字節(jié)長(zhǎng)度頭13。但查詢依然慢。SHOW PROFILE顯示Copying to tmp table耗時(shí)占比68%。原因city字段在WHERE中是等值查詢但ORDER BY age DESC要求索引中age字段的排序方向與查詢一致。而我的索引是(city, status, age)age默認(rèn)升序但查詢要DESC導(dǎo)致MySQL無法復(fù)用索引排序強(qiáng)制filesort。解決方案建(city, status, age DESC)MySQL 8.0支持降序索引key_len不變但Extra字段從Using filesort變?yōu)閁sing index。提示key_len計(jì)算規(guī)則需牢記INT固定4字節(jié)BIGINT8字節(jié)CHAR(n)按n字節(jié)算定長(zhǎng)VARCHAR(n)按n×字符集字節(jié)數(shù)2字節(jié)長(zhǎng)度頭變長(zhǎng)。NULL字段額外1字節(jié)標(biāo)記位。用key_len反推索引使用情況比盲目加索引高效十倍。2.2rows預(yù)估掃描行數(shù)的“樂觀偏差”rows是MySQL基于統(tǒng)計(jì)信息估算的掃描行數(shù)但它常嚴(yán)重低估。尤其當(dāng)表數(shù)據(jù)分布不均時(shí)——比如status字段95%是05%是1統(tǒng)計(jì)信息可能把WHERE status1的rows估為總行數(shù)的10%實(shí)際卻是5%。更致命的是rows只反映WHERE條件的過濾完全不包含ORDER BY和GROUP BY的額外開銷。一個(gè)rows1000的查詢?nèi)粜鐶ROUP BY user_id實(shí)際處理行數(shù)可能是1000×平均每個(gè)用戶的訂單數(shù)比如50即5萬行。我們?cè)龅揭粋€(gè)報(bào)表查詢SELECT DATE(created_at), COUNT(*) FROM logs WHERE app_id123 GROUP BY DATE(created_at) ORDER BY DATE(created_at) DESC LIMIT 30。EXPLAIN顯示rows8500看起來很健康。但實(shí)際執(zhí)行耗時(shí)2.7秒。SHOW PROFILE揭示真相Creating sort index耗時(shí)1.9秒。原因GROUP BY DATE(created_at)需要對(duì)created_at做日期截?cái)郙ySQL無法用索引直接分組必須掃描所有8500行計(jì)算DATE()函數(shù)再哈希分組。解決方案添加生成列date_only DATE AS (DATE(created_at)) STORED并在其上建索引。EXPLAIN的rows沒變但Extra從Using temporary; Using filesort變?yōu)閁sing index耗時(shí)降至120ms。2.3Extra字段里的“偽善提示”Extra字段的提示常帶誤導(dǎo)性。Using index看似完美但它只表示覆蓋索引Covering Index即查詢所需所有字段都在索引中無需回表。但如果查詢包含ORDER BY且索引順序不匹配它仍會(huì)觸發(fā)filesort。Using where是中性提示表示W(wǎng)HERE條件在存儲(chǔ)引擎層后由MySQL Server層過濾但若type是ALL或index說明沒走有效索引。最危險(xiǎn)的是Using index conditionICP索引條件下推。它聽起來很高級(jí)實(shí)則暴露了索引設(shè)計(jì)缺陷。ICP意味著MySQL用索引快速定位大致范圍比如WHERE citybeijing但索引中不包含status字段所以必須回表讀取status值再過濾。這比全索引掃描還慢——因?yàn)槎嗔舜罅侩S機(jī)IO。我們有個(gè)查詢SELECT id FROM products WHERE cityshanghai AND price 100索引是(city)。EXPLAIN顯示Using index conditionrows50000。優(yōu)化后建(city, price)Extra變?yōu)閁sing where; Using indexrows降至2300耗時(shí)從1.8秒降到45ms。注意Using index condition是“索引沒建對(duì)”的明確信號(hào)。它代表索引只能幫上一半忙另一半還得靠回表硬扛。此時(shí)應(yīng)優(yōu)先擴(kuò)展索引而非接受ICP。2.4filtered選擇率的“幻覺指標(biāo)”filtered表示MySQL估計(jì)WHERE條件過濾后的行數(shù)占比0-100。filtered10.00意味著預(yù)計(jì)10%的行滿足條件。但這個(gè)值基于直方圖統(tǒng)計(jì)對(duì)復(fù)雜條件如WHERE a1 AND b LIKE %xxx%極不準(zhǔn)確。更糟的是filtered只計(jì)算WHERE對(duì)GROUP BY的分組數(shù)量、ORDER BY的排序成本、LIMIT的偏移量毫無感知。一個(gè)filtered1.00的查詢?nèi)鬐ROUP BY產(chǎn)生10萬個(gè)分組ORDER BY需排序10萬行LIMIT 100000, 20需跳過10萬行——filtered對(duì)此保持沉默。我們?cè)鴥?yōu)化一個(gè)用戶畫像查詢SELECT tag, COUNT(*) FROM user_tags WHERE user_id IN (SELECT id FROM users WHERE regionsouth) GROUP BY tag ORDER BY COUNT(*) DESC LIMIT 10。EXPLAIN顯示外層filtered100.00因IN子查詢被物化內(nèi)層filtered5.00。看起來很美。但實(shí)際執(zhí)行卡在Sending data階段。SHOW PROCESSLIST顯示線程狀態(tài)為Copying to tmp table。根因GROUP BY tag產(chǎn)生數(shù)百萬分組ORDER BY COUNT(*) DESC需對(duì)所有分組排序LIMIT 10只取前10但MySQL必須先完成全部排序。解決方案放棄ORDER BY ... LIMIT改用近似算法先SELECT tag FROM user_tags GROUP BY tag ORDER BY RAND() LIMIT 1000采樣再精確統(tǒng)計(jì)這1000個(gè)tag的頻次并排序。耗時(shí)從42秒降至1.3秒。2.5possible_keys與key的“信任危機(jī)”possible_keys列出所有可能用上的索引key顯示最終選用的索引。但MySQL的索引選擇器Query Optimizer有時(shí)會(huì)選錯(cuò)。比如一個(gè)查詢WHERE a1 AND b10 ORDER BY c有索引(a,b)和(a,c)。possible_keys顯示兩者key選了(a,b)。但(a,b)無法支持ORDER BY c必然filesort而(a,c)雖不能優(yōu)化b10但能避免排序。此時(shí)需用FORCE INDEX干預(yù)SELECT * FROM t FORCE INDEX (a_c) WHERE a1 AND b10 ORDER BY c。我們?cè)诰€上強(qiáng)制干預(yù)過37次索引選擇。最經(jīng)典一例訂單表orders(user_id, status, created_at)查詢SELECT * FROM orders WHERE user_id123 AND status IN (1,2,3) ORDER BY created_at DESC。possible_keys有(user_id)和(user_id, status, created_at)key選了單列(user_id)。原因status IN (1,2,3)被評(píng)估為高選擇率MySQL認(rèn)為用(user_id)快速定位用戶所有訂單再內(nèi)存過濾status比用復(fù)合索引掃描更優(yōu)。但實(shí)測(cè)發(fā)現(xiàn)用戶平均有2000個(gè)訂單status過濾后剩300行ORDER BY created_at DESC需對(duì)300行排序——而復(fù)合索引(user_id, status, created_at)可直接定位到300行并按created_at倒序返回省去排序。加FORCE INDEX (user_id_status_created)后Extra從Using where; Using filesort變?yōu)閁sing index耗時(shí)從320ms降至45ms。3. 索引設(shè)計(jì)的“黃金三角”覆蓋、順序、冗余的協(xié)同藝術(shù)索引不是越多越好而是越精準(zhǔn)越高效。我總結(jié)出優(yōu)化ORDER BY、GROUP BY和分頁的索引設(shè)計(jì)“黃金三角”原則覆蓋性Covering、順序性O(shè)rdering、冗余性Redundancy。三者缺一不可且需根據(jù)查詢模式動(dòng)態(tài)權(quán)衡。下面以電商訂單庫的真實(shí)索引演進(jìn)為例展示如何用這三角法則解決具體問題。3.1 覆蓋性讓索引承載一切拒絕回表覆蓋索引的核心目標(biāo)是讓查詢所需的所有字段SELECT、WHERE、ORDER BY、GROUP BY涉及的字段全部包含在索引B樹的葉子節(jié)點(diǎn)中從而避免回表Table Lookup。回表是隨機(jī)IO大戶尤其在SSD時(shí)代一次回表可能比順序掃描100行還慢。原始訂單表結(jié)構(gòu)CREATE TABLE orders ( id BIGINT PRIMARY KEY, user_id BIGINT NOT NULL, status TINYINT NOT NULL, amount DECIMAL(10,2) NOT NULL, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL, product_id BIGINT NOT NULL );典型查詢Q1SELECT id, user_id, amount, created_at FROM orders WHERE user_id123 AND status1 ORDER BY created_at DESC LIMIT 20。初始索引(user_id, status)能加速WHERE但SELECT中的id、amount、created_at不在索引中必須回表。EXPLAIN顯示Extra: Using where; Using filesort因created_at未在索引中排序。黃金三角第一步覆蓋性。建復(fù)合索引(user_id, status, created_at, amount, id)。注意字段順序user_id和status是等值查詢放最前created_at是排序字段緊隨其后amount和id是SELECT字段放在最后。這樣索引葉子節(jié)點(diǎn)包含全部5個(gè)字段EXPLAIN的Extra變?yōu)閁sing indexkey_len顯示完整使用。但問題來了索引長(zhǎng)度暴增。user_id8字節(jié)status1字節(jié)created_at8字節(jié)amount10字節(jié)DECIMAL存為二進(jìn)制id8字節(jié)共35字節(jié)。B樹每頁存的鍵值減少樹高增加查詢效率反而下降。此時(shí)需引入第二條法則冗余性。3.2 冗余性用空間換時(shí)間容忍適度重復(fù)冗余性不是無腦復(fù)制字段而是在關(guān)鍵路徑上為高頻查詢模式預(yù)置“定制化索引”即使它與其他索引有重疊字段。數(shù)據(jù)庫的存儲(chǔ)成本遠(yuǎn)低于CPU和IO成本尤其在云環(huán)境SSD價(jià)格已大幅下降。針對(duì)Q1我們建專用索引(user_id, status, created_at DESC, amount, id)。created_at DESC確保排序方向匹配消除filesort。雖然user_id已在主鍵和(user_id, status)索引中存在但這個(gè)新索引將Q1的響應(yīng)時(shí)間從850ms壓至65ms。存儲(chǔ)代價(jià)該索引約占用12GB表總數(shù)據(jù)量1.2TB但換來的是Q1的QPS從120提升至1800且不再觸發(fā)慢查詢報(bào)警。冗余性還體現(xiàn)在對(duì)GROUP BY的優(yōu)化。查詢Q2SELECT user_id, COUNT(*), SUM(amount) FROM orders WHERE status IN (1,2) GROUP BY user_id ORDER BY SUM(amount) DESC LIMIT 10。若建索引(status, user_id, amount)WHERE status IN (1,2)可走索引范圍掃描GROUP BY user_id可利用索引順序分組因user_id在status后SUM(amount)可直接在索引中累加覆蓋性。但ORDER BY SUM(amount) DESC無法用索引排序因amount在索引中是分散存儲(chǔ)的。此時(shí)冗余性方案建(status, user_id, amount)(status, user_id, amount DESC)雙索引。后者讓SUM(amount)的聚合結(jié)果天然按降序排列ORDER BY直接跳過。實(shí)測(cè)Q2耗時(shí)從3.2秒降至210ms。經(jīng)驗(yàn)冗余索引的閾值是——當(dāng)一個(gè)索引能讓某個(gè)核心查詢的P99延遲降低50%以上且該查詢QPS超過100就值得為其建冗余索引。我們線上有7個(gè)這樣的“黃金冗余索引”占總索引數(shù)12%卻承擔(dān)了63%的查詢負(fù)載。3.3 順序性索引字段的排列就是執(zhí)行計(jì)劃的路線圖順序性決定索引能否同時(shí)服務(wù)WHERE、GROUP BY、ORDER BY。其核心規(guī)則是等值查詢字段, IN放最前范圍查詢字段, , BETWEEN放中間排序/分組字段ORDER BY, GROUP BY放最后。違反此順序索引將部分失效。查詢Q3SELECT product_id, COUNT(*), AVG(amount) FROM orders WHERE user_id123 AND created_at 2023-01-01 GROUP BY product_id ORDER BY COUNT(*) DESC LIMIT 10。錯(cuò)誤索引(user_id, product_id, created_at)user_id123可用但created_at 2023-01-01是范圍查詢product_id在其后無法用于GROUP BY因product_id不連續(xù)。EXPLAIN顯示Using temporary; Using filesort。正確索引(user_id, created_at, product_id)user_id等值created_at范圍product_id在范圍后GROUP BY product_id可利用索引順序分組MySQL 5.7支持松散索引掃描Loose Scan。ORDER BY COUNT(*) DESC仍需排序但分組已優(yōu)化。極致順序性(user_id, created_at, product_id, amount)。amount加入后AVG(amount)可覆蓋計(jì)算COUNT(*)和AVG(amount)均在索引中完成Extra變?yōu)閁sing index。但ORDER BY COUNT(*) DESC仍需排序。此時(shí)引入冗余性建(user_id, created_at, product_id, amount)(user_id, created_at, product_id, amount, count_star)生成列后者讓COUNT(*)結(jié)果固化ORDER BY可直接用索引。3.4 三角協(xié)同一個(gè)索引解決三個(gè)問題的實(shí)戰(zhàn)案例最終我們?yōu)橛唵伪碓O(shè)計(jì)了一個(gè)“超級(jí)索引”同時(shí)優(yōu)化Q1、Q2、Q3ALTER TABLE orders ADD INDEX idx_user_status_created_amount_id (user_id, status, created_at DESC, amount, id), ADD INDEX idx_status_user_created_product_amount (status, user_id, created_at, product_id, amount);第一個(gè)索引服務(wù)Q1user_id等值status等值created_at DESC匹配排序amount和id覆蓋SELECT。第二個(gè)索引服務(wù)Q2和Q3status等值Q2的IN被轉(zhuǎn)為多個(gè)等值user_id等值Q2的GROUP BYcreated_at范圍Q3的product_id分組Q3amount聚合Q2/Q3。驗(yàn)證效果Q1 P99從850ms→65msQ2從3.2s→210msQ3從4.7s→380ms。索引總大小增加28GB但數(shù)據(jù)庫CPU使用率下降37%慢查詢告警歸零。關(guān)鍵心得不要幻想一個(gè)索引解決所有問題。黃金三角的本質(zhì)是——為每個(gè)核心查詢模式設(shè)計(jì)一個(gè)“專屬索引”用覆蓋性消除回表用順序性支撐分組排序用冗余性容忍存儲(chǔ)成本。這是MySQL優(yōu)化最樸實(shí)也最有效的哲學(xué)。4. 分頁的“死亡OFFSET”從游標(biāo)分頁到延遲關(guān)聯(lián)的五種破局方案LIMIT M, N即OFFSET M LIMIT N是MySQL分頁的“原罪”。當(dāng)M增大性能呈線性衰減。OFFSET 100000意味著MySQL必須掃描并跳過前100000行無論這些行是否滿足WHERE條件。我見過最極端案例一個(gè)日志表分頁查詢LIMIT 999999, 20耗時(shí)17秒EXPLAIN顯示rows1000019Extra: Using where; Using filesort。此時(shí)任何索引優(yōu)化都杯水車薪。必須跳出OFFSET思維采用根本性替代方案。4.1 游標(biāo)分頁Cursor-based Pagination用“上次結(jié)束位置”代替“跳過多少行”游標(biāo)分頁的核心思想是不告訴數(shù)據(jù)庫“跳過前N行”而是告訴它“從上次查詢的最后一條記錄之后開始”。這要求排序字段必須唯一且有索引。Q1優(yōu)化前SELECT * FROM orders WHERE status1 ORDER BY created_at DESC LIMIT 20 OFFSET 10000。優(yōu)化后游標(biāo)分頁-- 首頁無游標(biāo) SELECT id, created_at, amount FROM orders WHERE status1 ORDER BY created_at DESC, id DESC LIMIT 20; -- 下一頁用上一頁最后一條的created_at和id作為游標(biāo) SELECT id, created_at, amount FROM orders WHERE status1 AND (created_at 2023-05-10 14:22:33 OR (created_at 2023-05-10 14:22:33 AND id 123456789)) ORDER BY created_at DESC, id DESC LIMIT 20;關(guān)鍵點(diǎn)ORDER BY必須包含唯一字段如id作為第二排序鍵避免created_at重復(fù)時(shí)結(jié)果不一致。WHERE條件中用和組合精確錨定游標(biāo)位置。此方案將OFFSET 10000的查詢耗時(shí)從3.2秒降至85ms且M增大時(shí)性能幾乎不變。我們線上所有列表頁均已切換為游標(biāo)分頁。前端傳遞cursor2023-05-10T14:22:33Z_123456789ISO時(shí)間戳ID后端解析為WHERE ... AND (created_at ? OR (created_at ? AND id ?))。QPS從200提升至2200P99穩(wěn)定在90ms內(nèi)。注意游標(biāo)分頁不支持“跳轉(zhuǎn)到任意頁”只支持“下一頁/上一頁”。這對(duì)用戶體驗(yàn)是妥協(xié)但對(duì)系統(tǒng)穩(wěn)定性是巨大收益。我們通過前端緩存首頁和熱門頁如第1、10、50頁來彌補(bǔ)。4.2 延遲關(guān)聯(lián)Deferred Join用“窄索引”先定位ID再回表取數(shù)據(jù)延遲關(guān)聯(lián)是處理寬表分頁的經(jīng)典方案。其思路是先用最小化的索引只含WHERE和ORDER BY字段快速找出滿足條件的主鍵ID再用這些ID回原表取完整數(shù)據(jù)。這避免了在寬表上直接排序和分頁。Q4SELECT * FROM orders WHERE user_id123 ORDER BY created_at DESC LIMIT 20 OFFSET 10000。orders表有20字段SELECT *導(dǎo)致回表成本極高。延遲關(guān)聯(lián)步驟-- Step1: 用窄索引獲取ID覆蓋索引無回表 SELECT id FROM orders WHERE user_id123 ORDER BY created_at DESC LIMIT 20 OFFSET 10000; -- Step2: 用ID集合回表取完整數(shù)據(jù)主鍵查詢最快 SELECT * FROM orders WHERE id IN (12345, 67890, ...); -- 上一步得到的20個(gè)ID但I(xiàn)N列表有長(zhǎng)度限制MySQL默認(rèn)1000且OFFSET 10000在Step1仍慢。終極方案用JOIN替代IN。SELECT o.* FROM orders o INNER JOIN ( SELECT id FROM orders WHERE user_id123 ORDER BY created_at DESC LIMIT 20 OFFSET 10000 ) AS tmp ON o.id tmp.id;EXPLAIN顯示子查詢tmp用索引(user_id, created_at DESC)rows10020仍需掃描但只返回id窄key_len小速度快主表o通過主鍵id關(guān)聯(lián)type: eq_ref極快。整體耗時(shí)從2.1秒降至320ms。我們?yōu)橛脩粲唵雾搶?shí)施此方案。索引(user_id, created_at DESC)僅17字節(jié)B樹深度淺OFFSET 10000掃描10020行比全表掃描快得多。配合應(yīng)用層緩存Step1結(jié)果ID列表P99降至110ms。4.3 子查詢分頁用“主鍵范圍”替代OFFSET當(dāng)排序字段有索引但不唯一時(shí)游標(biāo)分頁難實(shí)現(xiàn)如ORDER BY status, created_atstatus只有幾個(gè)值。此時(shí)可用子查詢分頁先查出目標(biāo)頁的主鍵范圍再用范圍查詢?nèi)?shù)據(jù)。Q5SELECT * FROM products WHERE category_id45 ORDER BY price ASC LIMIT 20 OFFSET 10000。price有大量重復(fù)值無法用游標(biāo)。子查詢方案-- Step1: 查出第10001到10020條記錄的price和id邊界 SELECT MIN(price) as min_price, MAX(price) as max_price, MIN(id) as min_id, MAX(id) as max_id FROM ( SELECT price, id FROM products WHERE category_id45 ORDER BY price ASC, id ASC LIMIT 20 OFFSET 10000 ) AS page_boundary; -- Step2: 用邊界條件取數(shù)據(jù)需處理price重復(fù) SELECT * FROM products WHERE category_id45 AND ((price ?) OR (price ? AND id ?)) AND ((price ?) OR (price ? AND id ?)) ORDER BY price ASC, id ASC LIMIT 20;Step1的子查詢?nèi)杂肙FFSET但只返回4個(gè)值rows小速度快。Step2用范圍條件避免OFFSET。我們實(shí)測(cè)OFFSET 10000時(shí)此方案比原查詢快4.3倍。4.4 物化分頁表Materialized Pagination Table為超高頻分頁建專用視圖當(dāng)某分頁查詢QPS極高如首頁商品列表且數(shù)據(jù)更新不頻繁如每小時(shí)同步可建物化分頁表。其本質(zhì)是預(yù)計(jì)算分頁結(jié)果存入一張獨(dú)立表用定時(shí)任務(wù)刷新。建表CREATE TABLE products_page_cache ( page_num INT NOT NULL, offset_start INT NOT NULL, limit_count INT NOT NULL, product_ids TEXT NOT NULL, -- JSON數(shù)組或逗號(hào)分隔 updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (page_num) );定時(shí)任務(wù)每小時(shí)INSERT INTO products_page_cache (page_num, offset_start, limit_count, product_ids) SELECT FLOOR((row_number : row_number 1) / 20) 1 AS page_num, (row_number - 1) AS offset_start, 20 AS limit_count, GROUP_CONCAT(id ORDER BY price ASC SEPARATOR ,) AS product_ids FROM products, (SELECT row_number : 0) r WHERE category_id45 ORDER BY price ASC, id ASC;查詢時(shí)SELECT p.* FROM products p INNER JOIN products_page_cache c ON FIND_IN_SET(p.id, c.product_ids) WHERE c.page_num 501; -- 直接取第501頁此方案將P99從1.8秒降至15ms但犧牲了實(shí)時(shí)性最多1小時(shí)延遲。我們用于“熱銷榜”、“新品榜”等業(yè)務(wù)場(chǎng)景用戶接受度極高。4.5 應(yīng)用層分頁把分頁邏輯從數(shù)據(jù)庫移到應(yīng)用內(nèi)存當(dāng)數(shù)據(jù)量不大100萬行且內(nèi)存充足時(shí)最簡(jiǎn)單粗暴的方案是一次性查出所有數(shù)據(jù)在應(yīng)用層分頁。這聽起來反直覺但對(duì)小數(shù)據(jù)集網(wǎng)絡(luò)傳輸和內(nèi)存排序的開銷遠(yuǎn)小于數(shù)據(jù)庫的OFFSET掃描。Q6SELECT name, email FROM users WHERE depttech ORDER BY hire_date DESC。users表tech部門僅8000人。應(yīng)用層代碼Python# 一次性查出所有tech用戶 all_users db.query(SELECT id, name, email, hire_date FROM users WHERE depttech) # 按hire_date倒序排序內(nèi)存排序O(n log n) all_users.sort(keylambda x: x[hire_date], reverseTrue) # 取第10001-10020頁 page_data all_users[10000:10020]耗時(shí)數(shù)據(jù)庫查詢120ms Python排序8ms 128ms。而OFFSET 10000查詢需掃描10020行耗時(shí)450ms。內(nèi)存排序8000個(gè)對(duì)象比數(shù)據(jù)庫B樹遍歷10020行快得多。我們?yōu)閮?nèi)部管理后臺(tái)的員工列表采用此方案。dept字段選擇率高結(jié)果集小應(yīng)用層分頁成為最優(yōu)解。5. GROUP BY的“聚合陷阱”從臨時(shí)表到物化視圖的性能躍遷GROUP BY常被當(dāng)作“去重”工具但它的本質(zhì)是分組聚合計(jì)算性能瓶頸遠(yuǎn)不止于索引。當(dāng)分組鍵基數(shù)高如GROUP BY user_id有百萬分組、或聚合函數(shù)復(fù)雜如GROUP_CONCAT、JSON_AGG、或需多層嵌套時(shí)MySQL極易創(chuàng)建巨大的臨時(shí)表。我將用三個(gè)真實(shí)案例展示如何從底層原理出發(fā)繞過臨時(shí)表實(shí)現(xiàn)性能質(zhì)變。5.1 松散索引掃描Loose Index Scan讓GROUP BY“偷懶”跳過全掃描MySQL的松散索引掃描是GROUP BY優(yōu)化的隱藏王牌。其原理是當(dāng)索引順序與GROUP BY字段完全一致且無范圍條件時(shí)MySQL可跳過同一分組內(nèi)的所有行只取每個(gè)分組的第一行進(jìn)行聚合極大減少掃描行數(shù)。Q7SELECT user_id, COUNT(*) FROM orders GROUP BY user_id。orders表有1.2億行user_id有800萬不同值。若索引是(user_id)EXPLAIN顯示type: indexrows120000000Extra: Using index。但COUNT(*)需統(tǒng)計(jì)每個(gè)user_id的行數(shù)MySQL仍需掃描全部1.2億行。啟用松散索引掃描建索引(user_id, id)id是主鍵確保索引有序。EXPLAIN的Extra變?yōu)閁sing index for group-byrows從1.2億降至800萬分組數(shù)。原因MySQL用(user_id, id)索引對(duì)每個(gè)user_id只讀取該分組的第一行因id遞增第一行id最小然后跳到下一個(gè)user_id的首行無需掃描分組內(nèi)所有行。COUNT(*)通過計(jì)算跳過的行數(shù)差值得出。實(shí)測(cè)耗時(shí)從28秒降至3.1秒rows減少93%。這是GROUP BY最高效的優(yōu)化方式但要求索引前綴嚴(yán)格匹配GROUP BY字段且無WHERE范圍條件。5.2 索引覆蓋聚合用索引直接完成SUM、AVG、MIN、MAX當(dāng)聚合函數(shù)是SUM、AVG、MIN、MAX、COUNT時(shí)若相關(guān)字段在索引中MySQL可直接在索引B樹的葉子節(jié)點(diǎn)上計(jì)算無需回表或臨時(shí)表。Q8SELECT user_id, SUM(amount), AVG(amount) FROM orders WHERE status1 GROUP BY user_id。索引(status, user_id, amount)status1等值user_id分組amount聚合。EXPLAIN顯示Extra: Using indexrows500000滿足status1的行數(shù)。但SUM和AVG需對(duì)每個(gè)user_id分組內(nèi)的amount求和/平均MySQL仍需掃描所有50萬行。優(yōu)化建索引(status, user_id, amount)并確保amount在索引中。SUM(amount)可直接在索引葉子節(jié)點(diǎn)累加因amount MySQL ORDER BY、GROUP BY與分頁性能失效根因與優(yōu)化實(shí)戰(zhàn)
1. 這不是“加個(gè)索引就完事”的問題為什么order by、group by和分頁在MySQL里會(huì)集體失靈我第一次被線上慢查詢報(bào)警釘死在工位上是凌晨?jī)牲c(diǎn)。一個(gè)看似簡(jiǎn)單的訂單列表頁SELECT * FROM orders WHERE status 1 ORDER BY created_at DESC LIMIT 20 OFFSET 10000執(zhí)行時(shí)間從80ms飆到3.2秒。DBA甩來一句“加個(gè)索引不就完了”——我照做了ALTER TABLE orders ADD INDEX idx_status_created (status, created_at)結(jié)果查詢時(shí)間只降了17ms。那一刻我才明白把ORDER BY、GROUP BY和分頁塞進(jìn)一條SQL里不是在寫查詢是在給MySQL出一道組合數(shù)學(xué)題。這三者疊加的殺傷力遠(yuǎn)超單點(diǎn)優(yōu)化。ORDER BY要求排序GROUP BY要求分組聚合而LIMIT OFFSET要求跳過前N行——三者共同指向同一個(gè)致命瓶頸臨時(shí)表Temporary Table與文件排序Filesort的雙重開銷。當(dāng)MySQL發(fā)現(xiàn)無法利用索引直接完成排序分組跳過時(shí)它會(huì)默默創(chuàng)建一個(gè)內(nèi)存臨時(shí)表把所有滿足WHERE條件的行全撈出來再在內(nèi)存里排序、分組、計(jì)數(shù)最后才取第10001到10020行。一旦數(shù)據(jù)量突破內(nèi)存閾值tmp_table_size和max_heap_table_size臨時(shí)表就會(huì)落盤觸發(fā)磁盤I/O性能斷崖式下跌。更隱蔽的是GROUP BY常被誤認(rèn)為只是“去重”但它本質(zhì)是分組聚合操作。SELECT user_id, COUNT(*) FROM orders GROUP BY user_id表面看只返回用戶ID和訂單數(shù)但MySQL必須掃描所有匹配行按user_id哈希分桶每個(gè)桶內(nèi)累加計(jì)數(shù)——這個(gè)過程無法被簡(jiǎn)單索引跳過除非索引能覆蓋整個(gè)分組鍵聚合字段。而ORDER BY若涉及非索引字段或索引順序與排序方向沖突如索引是(a,b)卻ORDER BY a DESC, b ASC同樣觸發(fā)filesort。分頁則是壓垮駱駝的最后一根稻草。OFFSET 10000不是“跳過10000行”而是讓MySQL先找到前10000行再丟棄它們最后取接下來的20行。這意味著無論你只要20條數(shù)據(jù)MySQL都得處理10020行。當(dāng)OFFSET達(dá)到十萬級(jí)哪怕有索引B樹也要反復(fù)回表、遍歷CPU和IO雙雙拉滿。所以這不是“怎么加索引”的技術(shù)問題而是如何重構(gòu)查詢邏輯、規(guī)避MySQL執(zhí)行器固有缺陷的工程問題。接下來我會(huì)用真實(shí)生產(chǎn)環(huán)境的5個(gè)典型場(chǎng)景拆解每一種失效背后的執(zhí)行計(jì)劃真相、索引設(shè)計(jì)陷阱、以及繞過臨時(shí)表的硬核替代方案——所有結(jié)論均來自我們?nèi)站?億查詢的電商訂單庫實(shí)測(cè)數(shù)據(jù)。2. 執(zhí)行計(jì)劃里的“隱形殺手”讀懂EXPLAIN輸出中那些沉默的警告很多開發(fā)者看到EXPLAIN結(jié)果里出現(xiàn)Using filesort或Using temporary就慌了以為只要消滅這兩個(gè)詞就萬事大吉。但真正危險(xiǎn)的是那些沒報(bào)錯(cuò)卻在后臺(tái)瘋狂消耗資源的“靜默殺手”。我整理了過去半年線上慢查詢中EXPLAIN輸出最常被忽略的5個(gè)關(guān)鍵字段及其真實(shí)含義它們比type: ALL更值得警惕。2.1key_len索引實(shí)際使用長(zhǎng)度的“縮水真相”key_len顯示MySQL在索引中實(shí)際用了多少字節(jié)。很多人建了復(fù)合索引(a,b,c)EXPLAIN顯示key_len5就以為索引全用了。但a是INT(11)4字節(jié)b是VARCHAR(50)假設(shè)UTF8MB4最大200字節(jié)c是TINYINT1字節(jié)。key_len5意味著只用了a4字節(jié)b的第一個(gè)字節(jié)1字節(jié)——因?yàn)閎是變長(zhǎng)字段MySQL無法預(yù)知其實(shí)際長(zhǎng)度只能按最壞情況預(yù)留空間。真正的索引利用率要看key_len是否等于你期望使用的字段長(zhǎng)度之和。舉個(gè)真實(shí)案例一張用戶表users(id, city, age, status)需求是SELECT * FROM users WHERE citybeijing AND status1 ORDER BY age DESC。我建了索引(city, status, age)EXPLAIN顯示key_len13cityVARCHAR(20)占80bit10字節(jié)statusTINYINT占1字節(jié)ageTINYINT占1字節(jié)2字節(jié)長(zhǎng)度頭13。但查詢依然慢。SHOW PROFILE顯示Copying to tmp table耗時(shí)占比68%。原因city字段在WHERE中是等值查詢但ORDER BY age DESC要求索引中age字段的排序方向與查詢一致。而我的索引是(city, status, age)age默認(rèn)升序但查詢要DESC導(dǎo)致MySQL無法復(fù)用索引排序強(qiáng)制filesort。解決方案建(city, status, age DESC)MySQL 8.0支持降序索引key_len不變但Extra字段從Using filesort變?yōu)閁sing index。提示key_len計(jì)算規(guī)則需牢記INT固定4字節(jié)BIGINT8字節(jié)CHAR(n)按n字節(jié)算定長(zhǎng)VARCHAR(n)按n×字符集字節(jié)數(shù)2字節(jié)長(zhǎng)度頭變長(zhǎng)。NULL字段額外1字節(jié)標(biāo)記位。用key_len反推索引使用情況比盲目加索引高效十倍。2.2rows預(yù)估掃描行數(shù)的“樂觀偏差”rows是MySQL基于統(tǒng)計(jì)信息估算的掃描行數(shù)但它常嚴(yán)重低估。尤其當(dāng)表數(shù)據(jù)分布不均時(shí)——比如status字段95%是05%是1統(tǒng)計(jì)信息可能把WHERE status1的rows估為總行數(shù)的10%實(shí)際卻是5%。更致命的是rows只反映WHERE條件的過濾完全不包含ORDER BY和GROUP BY的額外開銷。一個(gè)rows1000的查詢?nèi)粜鐶ROUP BY user_id實(shí)際處理行數(shù)可能是1000×平均每個(gè)用戶的訂單數(shù)比如50即5萬行。我們?cè)龅揭粋€(gè)報(bào)表查詢SELECT DATE(created_at), COUNT(*) FROM logs WHERE app_id123 GROUP BY DATE(created_at) ORDER BY DATE(created_at) DESC LIMIT 30。EXPLAIN顯示rows8500看起來很健康。但實(shí)際執(zhí)行耗時(shí)2.7秒。SHOW PROFILE揭示真相Creating sort index耗時(shí)1.9秒。原因GROUP BY DATE(created_at)需要對(duì)created_at做日期截?cái)郙ySQL無法用索引直接分組必須掃描所有8500行計(jì)算DATE()函數(shù)再哈希分組。解決方案添加生成列date_only DATE AS (DATE(created_at)) STORED并在其上建索引。EXPLAIN的rows沒變但Extra從Using temporary; Using filesort變?yōu)閁sing index耗時(shí)降至120ms。2.3Extra字段里的“偽善提示”Extra字段的提示常帶誤導(dǎo)性。Using index看似完美但它只表示覆蓋索引Covering Index即查詢所需所有字段都在索引中無需回表。但如果查詢包含ORDER BY且索引順序不匹配它仍會(huì)觸發(fā)filesort。Using where是中性提示表示W(wǎng)HERE條件在存儲(chǔ)引擎層后由MySQL Server層過濾但若type是ALL或index說明沒走有效索引。最危險(xiǎn)的是Using index conditionICP索引條件下推。它聽起來很高級(jí)實(shí)則暴露了索引設(shè)計(jì)缺陷。ICP意味著MySQL用索引快速定位大致范圍比如WHERE citybeijing但索引中不包含status字段所以必須回表讀取status值再過濾。這比全索引掃描還慢——因?yàn)槎嗔舜罅侩S機(jī)IO。我們有個(gè)查詢SELECT id FROM products WHERE cityshanghai AND price 100索引是(city)。EXPLAIN顯示Using index conditionrows50000。優(yōu)化后建(city, price)Extra變?yōu)閁sing where; Using indexrows降至2300耗時(shí)從1.8秒降到45ms。注意Using index condition是“索引沒建對(duì)”的明確信號(hào)。它代表索引只能幫上一半忙另一半還得靠回表硬扛。此時(shí)應(yīng)優(yōu)先擴(kuò)展索引而非接受ICP。2.4filtered選擇率的“幻覺指標(biāo)”filtered表示MySQL估計(jì)WHERE條件過濾后的行數(shù)占比0-100。filtered10.00意味著預(yù)計(jì)10%的行滿足條件。但這個(gè)值基于直方圖統(tǒng)計(jì)對(duì)復(fù)雜條件如WHERE a1 AND b LIKE %xxx%極不準(zhǔn)確。更糟的是filtered只計(jì)算WHERE對(duì)GROUP BY的分組數(shù)量、ORDER BY的排序成本、LIMIT的偏移量毫無感知。一個(gè)filtered1.00的查詢?nèi)鬐ROUP BY產(chǎn)生10萬個(gè)分組ORDER BY需排序10萬行LIMIT 100000, 20需跳過10萬行——filtered對(duì)此保持沉默。我們?cè)鴥?yōu)化一個(gè)用戶畫像查詢SELECT tag, COUNT(*) FROM user_tags WHERE user_id IN (SELECT id FROM users WHERE regionsouth) GROUP BY tag ORDER BY COUNT(*) DESC LIMIT 10。EXPLAIN顯示外層filtered100.00因IN子查詢被物化內(nèi)層filtered5.00。看起來很美。但實(shí)際執(zhí)行卡在Sending data階段。SHOW PROCESSLIST顯示線程狀態(tài)為Copying to tmp table。根因GROUP BY tag產(chǎn)生數(shù)百萬分組ORDER BY COUNT(*) DESC需對(duì)所有分組排序LIMIT 10只取前10但MySQL必須先完成全部排序。解決方案放棄ORDER BY ... LIMIT改用近似算法先SELECT tag FROM user_tags GROUP BY tag ORDER BY RAND() LIMIT 1000采樣再精確統(tǒng)計(jì)這1000個(gè)tag的頻次并排序。耗時(shí)從42秒降至1.3秒。2.5possible_keys與key的“信任危機(jī)”possible_keys列出所有可能用上的索引key顯示最終選用的索引。但MySQL的索引選擇器Query Optimizer有時(shí)會(huì)選錯(cuò)。比如一個(gè)查詢WHERE a1 AND b10 ORDER BY c有索引(a,b)和(a,c)。possible_keys顯示兩者key選了(a,b)。但(a,b)無法支持ORDER BY c必然filesort而(a,c)雖不能優(yōu)化b10但能避免排序。此時(shí)需用FORCE INDEX干預(yù)SELECT * FROM t FORCE INDEX (a_c) WHERE a1 AND b10 ORDER BY c。我們?cè)诰€上強(qiáng)制干預(yù)過37次索引選擇。最經(jīng)典一例訂單表orders(user_id, status, created_at)查詢SELECT * FROM orders WHERE user_id123 AND status IN (1,2,3) ORDER BY created_at DESC。possible_keys有(user_id)和(user_id, status, created_at)key選了單列(user_id)。原因status IN (1,2,3)被評(píng)估為高選擇率MySQL認(rèn)為用(user_id)快速定位用戶所有訂單再內(nèi)存過濾status比用復(fù)合索引掃描更優(yōu)。但實(shí)測(cè)發(fā)現(xiàn)用戶平均有2000個(gè)訂單status過濾后剩300行ORDER BY created_at DESC需對(duì)300行排序——而復(fù)合索引(user_id, status, created_at)可直接定位到300行并按created_at倒序返回省去排序。加FORCE INDEX (user_id_status_created)后Extra從Using where; Using filesort變?yōu)閁sing index耗時(shí)從320ms降至45ms。3. 索引設(shè)計(jì)的“黃金三角”覆蓋、順序、冗余的協(xié)同藝術(shù)索引不是越多越好而是越精準(zhǔn)越高效。我總結(jié)出優(yōu)化ORDER BY、GROUP BY和分頁的索引設(shè)計(jì)“黃金三角”原則覆蓋性Covering、順序性O(shè)rdering、冗余性Redundancy。三者缺一不可且需根據(jù)查詢模式動(dòng)態(tài)權(quán)衡。下面以電商訂單庫的真實(shí)索引演進(jìn)為例展示如何用這三角法則解決具體問題。3.1 覆蓋性讓索引承載一切拒絕回表覆蓋索引的核心目標(biāo)是讓查詢所需的所有字段SELECT、WHERE、ORDER BY、GROUP BY涉及的字段全部包含在索引B樹的葉子節(jié)點(diǎn)中從而避免回表Table Lookup。回表是隨機(jī)IO大戶尤其在SSD時(shí)代一次回表可能比順序掃描100行還慢。原始訂單表結(jié)構(gòu)CREATE TABLE orders ( id BIGINT PRIMARY KEY, user_id BIGINT NOT NULL, status TINYINT NOT NULL, amount DECIMAL(10,2) NOT NULL, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL, product_id BIGINT NOT NULL );典型查詢Q1SELECT id, user_id, amount, created_at FROM orders WHERE user_id123 AND status1 ORDER BY created_at DESC LIMIT 20。初始索引(user_id, status)能加速WHERE但SELECT中的id、amount、created_at不在索引中必須回表。EXPLAIN顯示Extra: Using where; Using filesort因created_at未在索引中排序。黃金三角第一步覆蓋性。建復(fù)合索引(user_id, status, created_at, amount, id)。注意字段順序user_id和status是等值查詢放最前created_at是排序字段緊隨其后amount和id是SELECT字段放在最后。這樣索引葉子節(jié)點(diǎn)包含全部5個(gè)字段EXPLAIN的Extra變?yōu)閁sing indexkey_len顯示完整使用。但問題來了索引長(zhǎng)度暴增。user_id8字節(jié)status1字節(jié)created_at8字節(jié)amount10字節(jié)DECIMAL存為二進(jìn)制id8字節(jié)共35字節(jié)。B樹每頁存的鍵值減少樹高增加查詢效率反而下降。此時(shí)需引入第二條法則冗余性。3.2 冗余性用空間換時(shí)間容忍適度重復(fù)冗余性不是無腦復(fù)制字段而是在關(guān)鍵路徑上為高頻查詢模式預(yù)置“定制化索引”即使它與其他索引有重疊字段。數(shù)據(jù)庫的存儲(chǔ)成本遠(yuǎn)低于CPU和IO成本尤其在云環(huán)境SSD價(jià)格已大幅下降。針對(duì)Q1我們建專用索引(user_id, status, created_at DESC, amount, id)。created_at DESC確保排序方向匹配消除filesort。雖然user_id已在主鍵和(user_id, status)索引中存在但這個(gè)新索引將Q1的響應(yīng)時(shí)間從850ms壓至65ms。存儲(chǔ)代價(jià)該索引約占用12GB表總數(shù)據(jù)量1.2TB但換來的是Q1的QPS從120提升至1800且不再觸發(fā)慢查詢報(bào)警。冗余性還體現(xiàn)在對(duì)GROUP BY的優(yōu)化。查詢Q2SELECT user_id, COUNT(*), SUM(amount) FROM orders WHERE status IN (1,2) GROUP BY user_id ORDER BY SUM(amount) DESC LIMIT 10。若建索引(status, user_id, amount)WHERE status IN (1,2)可走索引范圍掃描GROUP BY user_id可利用索引順序分組因user_id在status后SUM(amount)可直接在索引中累加覆蓋性。但ORDER BY SUM(amount) DESC無法用索引排序因amount在索引中是分散存儲(chǔ)的。此時(shí)冗余性方案建(status, user_id, amount)(status, user_id, amount DESC)雙索引。后者讓SUM(amount)的聚合結(jié)果天然按降序排列ORDER BY直接跳過。實(shí)測(cè)Q2耗時(shí)從3.2秒降至210ms。經(jīng)驗(yàn)冗余索引的閾值是——當(dāng)一個(gè)索引能讓某個(gè)核心查詢的P99延遲降低50%以上且該查詢QPS超過100就值得為其建冗余索引。我們線上有7個(gè)這樣的“黃金冗余索引”占總索引數(shù)12%卻承擔(dān)了63%的查詢負(fù)載。3.3 順序性索引字段的排列就是執(zhí)行計(jì)劃的路線圖順序性決定索引能否同時(shí)服務(wù)WHERE、GROUP BY、ORDER BY。其核心規(guī)則是等值查詢字段, IN放最前范圍查詢字段, , BETWEEN放中間排序/分組字段ORDER BY, GROUP BY放最后。違反此順序索引將部分失效。查詢Q3SELECT product_id, COUNT(*), AVG(amount) FROM orders WHERE user_id123 AND created_at 2023-01-01 GROUP BY product_id ORDER BY COUNT(*) DESC LIMIT 10。錯(cuò)誤索引(user_id, product_id, created_at)user_id123可用但created_at 2023-01-01是范圍查詢product_id在其后無法用于GROUP BY因product_id不連續(xù)。EXPLAIN顯示Using temporary; Using filesort。正確索引(user_id, created_at, product_id)user_id等值created_at范圍product_id在范圍后GROUP BY product_id可利用索引順序分組MySQL 5.7支持松散索引掃描Loose Scan。ORDER BY COUNT(*) DESC仍需排序但分組已優(yōu)化。極致順序性(user_id, created_at, product_id, amount)。amount加入后AVG(amount)可覆蓋計(jì)算COUNT(*)和AVG(amount)均在索引中完成Extra變?yōu)閁sing index。但ORDER BY COUNT(*) DESC仍需排序。此時(shí)引入冗余性建(user_id, created_at, product_id, amount)(user_id, created_at, product_id, amount, count_star)生成列后者讓COUNT(*)結(jié)果固化ORDER BY可直接用索引。3.4 三角協(xié)同一個(gè)索引解決三個(gè)問題的實(shí)戰(zhàn)案例最終我們?yōu)橛唵伪碓O(shè)計(jì)了一個(gè)“超級(jí)索引”同時(shí)優(yōu)化Q1、Q2、Q3ALTER TABLE orders ADD INDEX idx_user_status_created_amount_id (user_id, status, created_at DESC, amount, id), ADD INDEX idx_status_user_created_product_amount (status, user_id, created_at, product_id, amount);第一個(gè)索引服務(wù)Q1user_id等值status等值created_at DESC匹配排序amount和id覆蓋SELECT。第二個(gè)索引服務(wù)Q2和Q3status等值Q2的IN被轉(zhuǎn)為多個(gè)等值user_id等值Q2的GROUP BYcreated_at范圍Q3的product_id分組Q3amount聚合Q2/Q3。驗(yàn)證效果Q1 P99從850ms→65msQ2從3.2s→210msQ3從4.7s→380ms。索引總大小增加28GB但數(shù)據(jù)庫CPU使用率下降37%慢查詢告警歸零。關(guān)鍵心得不要幻想一個(gè)索引解決所有問題。黃金三角的本質(zhì)是——為每個(gè)核心查詢模式設(shè)計(jì)一個(gè)“專屬索引”用覆蓋性消除回表用順序性支撐分組排序用冗余性容忍存儲(chǔ)成本。這是MySQL優(yōu)化最樸實(shí)也最有效的哲學(xué)。4. 分頁的“死亡OFFSET”從游標(biāo)分頁到延遲關(guān)聯(lián)的五種破局方案LIMIT M, N即OFFSET M LIMIT N是MySQL分頁的“原罪”。當(dāng)M增大性能呈線性衰減。OFFSET 100000意味著MySQL必須掃描并跳過前100000行無論這些行是否滿足WHERE條件。我見過最極端案例一個(gè)日志表分頁查詢LIMIT 999999, 20耗時(shí)17秒EXPLAIN顯示rows1000019Extra: Using where; Using filesort。此時(shí)任何索引優(yōu)化都杯水車薪。必須跳出OFFSET思維采用根本性替代方案。4.1 游標(biāo)分頁Cursor-based Pagination用“上次結(jié)束位置”代替“跳過多少行”游標(biāo)分頁的核心思想是不告訴數(shù)據(jù)庫“跳過前N行”而是告訴它“從上次查詢的最后一條記錄之后開始”。這要求排序字段必須唯一且有索引。Q1優(yōu)化前SELECT * FROM orders WHERE status1 ORDER BY created_at DESC LIMIT 20 OFFSET 10000。優(yōu)化后游標(biāo)分頁-- 首頁無游標(biāo) SELECT id, created_at, amount FROM orders WHERE status1 ORDER BY created_at DESC, id DESC LIMIT 20; -- 下一頁用上一頁最后一條的created_at和id作為游標(biāo) SELECT id, created_at, amount FROM orders WHERE status1 AND (created_at 2023-05-10 14:22:33 OR (created_at 2023-05-10 14:22:33 AND id 123456789)) ORDER BY created_at DESC, id DESC LIMIT 20;關(guān)鍵點(diǎn)ORDER BY必須包含唯一字段如id作為第二排序鍵避免created_at重復(fù)時(shí)結(jié)果不一致。WHERE條件中用和組合精確錨定游標(biāo)位置。此方案將OFFSET 10000的查詢耗時(shí)從3.2秒降至85ms且M增大時(shí)性能幾乎不變。我們線上所有列表頁均已切換為游標(biāo)分頁。前端傳遞cursor2023-05-10T14:22:33Z_123456789ISO時(shí)間戳ID后端解析為WHERE ... AND (created_at ? OR (created_at ? AND id ?))。QPS從200提升至2200P99穩(wěn)定在90ms內(nèi)。注意游標(biāo)分頁不支持“跳轉(zhuǎn)到任意頁”只支持“下一頁/上一頁”。這對(duì)用戶體驗(yàn)是妥協(xié)但對(duì)系統(tǒng)穩(wěn)定性是巨大收益。我們通過前端緩存首頁和熱門頁如第1、10、50頁來彌補(bǔ)。4.2 延遲關(guān)聯(lián)Deferred Join用“窄索引”先定位ID再回表取數(shù)據(jù)延遲關(guān)聯(lián)是處理寬表分頁的經(jīng)典方案。其思路是先用最小化的索引只含WHERE和ORDER BY字段快速找出滿足條件的主鍵ID再用這些ID回原表取完整數(shù)據(jù)。這避免了在寬表上直接排序和分頁。Q4SELECT * FROM orders WHERE user_id123 ORDER BY created_at DESC LIMIT 20 OFFSET 10000。orders表有20字段SELECT *導(dǎo)致回表成本極高。延遲關(guān)聯(lián)步驟-- Step1: 用窄索引獲取ID覆蓋索引無回表 SELECT id FROM orders WHERE user_id123 ORDER BY created_at DESC LIMIT 20 OFFSET 10000; -- Step2: 用ID集合回表取完整數(shù)據(jù)主鍵查詢最快 SELECT * FROM orders WHERE id IN (12345, 67890, ...); -- 上一步得到的20個(gè)ID但I(xiàn)N列表有長(zhǎng)度限制MySQL默認(rèn)1000且OFFSET 10000在Step1仍慢。終極方案用JOIN替代IN。SELECT o.* FROM orders o INNER JOIN ( SELECT id FROM orders WHERE user_id123 ORDER BY created_at DESC LIMIT 20 OFFSET 10000 ) AS tmp ON o.id tmp.id;EXPLAIN顯示子查詢tmp用索引(user_id, created_at DESC)rows10020仍需掃描但只返回id窄key_len小速度快主表o通過主鍵id關(guān)聯(lián)type: eq_ref極快。整體耗時(shí)從2.1秒降至320ms。我們?yōu)橛脩粲唵雾搶?shí)施此方案。索引(user_id, created_at DESC)僅17字節(jié)B樹深度淺OFFSET 10000掃描10020行比全表掃描快得多。配合應(yīng)用層緩存Step1結(jié)果ID列表P99降至110ms。4.3 子查詢分頁用“主鍵范圍”替代OFFSET當(dāng)排序字段有索引但不唯一時(shí)游標(biāo)分頁難實(shí)現(xiàn)如ORDER BY status, created_atstatus只有幾個(gè)值。此時(shí)可用子查詢分頁先查出目標(biāo)頁的主鍵范圍再用范圍查詢?nèi)?shù)據(jù)。Q5SELECT * FROM products WHERE category_id45 ORDER BY price ASC LIMIT 20 OFFSET 10000。price有大量重復(fù)值無法用游標(biāo)。子查詢方案-- Step1: 查出第10001到10020條記錄的price和id邊界 SELECT MIN(price) as min_price, MAX(price) as max_price, MIN(id) as min_id, MAX(id) as max_id FROM ( SELECT price, id FROM products WHERE category_id45 ORDER BY price ASC, id ASC LIMIT 20 OFFSET 10000 ) AS page_boundary; -- Step2: 用邊界條件取數(shù)據(jù)需處理price重復(fù) SELECT * FROM products WHERE category_id45 AND ((price ?) OR (price ? AND id ?)) AND ((price ?) OR (price ? AND id ?)) ORDER BY price ASC, id ASC LIMIT 20;Step1的子查詢?nèi)杂肙FFSET但只返回4個(gè)值rows小速度快。Step2用范圍條件避免OFFSET。我們實(shí)測(cè)OFFSET 10000時(shí)此方案比原查詢快4.3倍。4.4 物化分頁表Materialized Pagination Table為超高頻分頁建專用視圖當(dāng)某分頁查詢QPS極高如首頁商品列表且數(shù)據(jù)更新不頻繁如每小時(shí)同步可建物化分頁表。其本質(zhì)是預(yù)計(jì)算分頁結(jié)果存入一張獨(dú)立表用定時(shí)任務(wù)刷新。建表CREATE TABLE products_page_cache ( page_num INT NOT NULL, offset_start INT NOT NULL, limit_count INT NOT NULL, product_ids TEXT NOT NULL, -- JSON數(shù)組或逗號(hào)分隔 updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (page_num) );定時(shí)任務(wù)每小時(shí)INSERT INTO products_page_cache (page_num, offset_start, limit_count, product_ids) SELECT FLOOR((row_number : row_number 1) / 20) 1 AS page_num, (row_number - 1) AS offset_start, 20 AS limit_count, GROUP_CONCAT(id ORDER BY price ASC SEPARATOR ,) AS product_ids FROM products, (SELECT row_number : 0) r WHERE category_id45 ORDER BY price ASC, id ASC;查詢時(shí)SELECT p.* FROM products p INNER JOIN products_page_cache c ON FIND_IN_SET(p.id, c.product_ids) WHERE c.page_num 501; -- 直接取第501頁此方案將P99從1.8秒降至15ms但犧牲了實(shí)時(shí)性最多1小時(shí)延遲。我們用于“熱銷榜”、“新品榜”等業(yè)務(wù)場(chǎng)景用戶接受度極高。4.5 應(yīng)用層分頁把分頁邏輯從數(shù)據(jù)庫移到應(yīng)用內(nèi)存當(dāng)數(shù)據(jù)量不大100萬行且內(nèi)存充足時(shí)最簡(jiǎn)單粗暴的方案是一次性查出所有數(shù)據(jù)在應(yīng)用層分頁。這聽起來反直覺但對(duì)小數(shù)據(jù)集網(wǎng)絡(luò)傳輸和內(nèi)存排序的開銷遠(yuǎn)小于數(shù)據(jù)庫的OFFSET掃描。Q6SELECT name, email FROM users WHERE depttech ORDER BY hire_date DESC。users表tech部門僅8000人。應(yīng)用層代碼Python# 一次性查出所有tech用戶 all_users db.query(SELECT id, name, email, hire_date FROM users WHERE depttech) # 按hire_date倒序排序內(nèi)存排序O(n log n) all_users.sort(keylambda x: x[hire_date], reverseTrue) # 取第10001-10020頁 page_data all_users[10000:10020]耗時(shí)數(shù)據(jù)庫查詢120ms Python排序8ms 128ms。而OFFSET 10000查詢需掃描10020行耗時(shí)450ms。內(nèi)存排序8000個(gè)對(duì)象比數(shù)據(jù)庫B樹遍歷10020行快得多。我們?yōu)閮?nèi)部管理后臺(tái)的員工列表采用此方案。dept字段選擇率高結(jié)果集小應(yīng)用層分頁成為最優(yōu)解。5. GROUP BY的“聚合陷阱”從臨時(shí)表到物化視圖的性能躍遷GROUP BY常被當(dāng)作“去重”工具但它的本質(zhì)是分組聚合計(jì)算性能瓶頸遠(yuǎn)不止于索引。當(dāng)分組鍵基數(shù)高如GROUP BY user_id有百萬分組、或聚合函數(shù)復(fù)雜如GROUP_CONCAT、JSON_AGG、或需多層嵌套時(shí)MySQL極易創(chuàng)建巨大的臨時(shí)表。我將用三個(gè)真實(shí)案例展示如何從底層原理出發(fā)繞過臨時(shí)表實(shí)現(xiàn)性能質(zhì)變。5.1 松散索引掃描Loose Index Scan讓GROUP BY“偷懶”跳過全掃描MySQL的松散索引掃描是GROUP BY優(yōu)化的隱藏王牌。其原理是當(dāng)索引順序與GROUP BY字段完全一致且無范圍條件時(shí)MySQL可跳過同一分組內(nèi)的所有行只取每個(gè)分組的第一行進(jìn)行聚合極大減少掃描行數(shù)。Q7SELECT user_id, COUNT(*) FROM orders GROUP BY user_id。orders表有1.2億行user_id有800萬不同值。若索引是(user_id)EXPLAIN顯示type: indexrows120000000Extra: Using index。但COUNT(*)需統(tǒng)計(jì)每個(gè)user_id的行數(shù)MySQL仍需掃描全部1.2億行。啟用松散索引掃描建索引(user_id, id)id是主鍵確保索引有序。EXPLAIN的Extra變?yōu)閁sing index for group-byrows從1.2億降至800萬分組數(shù)。原因MySQL用(user_id, id)索引對(duì)每個(gè)user_id只讀取該分組的第一行因id遞增第一行id最小然后跳到下一個(gè)user_id的首行無需掃描分組內(nèi)所有行。COUNT(*)通過計(jì)算跳過的行數(shù)差值得出。實(shí)測(cè)耗時(shí)從28秒降至3.1秒rows減少93%。這是GROUP BY最高效的優(yōu)化方式但要求索引前綴嚴(yán)格匹配GROUP BY字段且無WHERE范圍條件。5.2 索引覆蓋聚合用索引直接完成SUM、AVG、MIN、MAX當(dāng)聚合函數(shù)是SUM、AVG、MIN、MAX、COUNT時(shí)若相關(guān)字段在索引中MySQL可直接在索引B樹的葉子節(jié)點(diǎn)上計(jì)算無需回表或臨時(shí)表。Q8SELECT user_id, SUM(amount), AVG(amount) FROM orders WHERE status1 GROUP BY user_id。索引(status, user_id, amount)status1等值user_id分組amount聚合。EXPLAIN顯示Extra: Using indexrows500000滿足status1的行數(shù)。但SUM和AVG需對(duì)每個(gè)user_id分組內(nèi)的amount求和/平均MySQL仍需掃描所有50萬行。優(yōu)化建索引(status, user_id, amount)并確保amount在索引中。SUM(amount)可直接在索引葉子節(jié)點(diǎn)累加因amount