：子查詢物化優(yōu)化解決MySQL慢查詢)
我們直接從項目標(biāo)題入手。這個標(biāo)題是《php方案 子查詢物化優(yōu)化》看起來像是后端開發(fā)里一條冷門的性能優(yōu)化經(jīng)驗但實際一拆開里面全是干貨。我在過去幾年的PHP開發(fā)里不止一次被慢查詢折騰到凌晨最后靠子查詢物化這個思路把接口響應(yīng)時間從幾秒壓到幾十毫秒。這篇就專門聊聊子查詢物化是什么PHP側(cè)該怎么判斷、怎么用、有哪些坑。適合正在和MySQL慢查詢較勁的后端開發(fā)者、搞過SQL優(yōu)化的朋友以及準(zhǔn)備面試性能優(yōu)化話題的同學(xué)。1. 子查詢物化到底是什么先搞懂優(yōu)化器在做什么1.1 一次慢查詢事故還原先說一個很典型的場景。某天線上一個訂單統(tǒng)計接口突然變慢排查時發(fā)現(xiàn)罪魁禍?zhǔn)资窍旅孢@類查詢SELECT * FROM orders o WHERE o.user_id IN ( SELECT user_id FROM user_tags WHERE tag_type vip AND created_at 2023-01-01 );初看沒毛病邏輯很簡單——找出所有帶“VIP”標(biāo)簽的用戶然后查這些用戶的訂單。但問題是user_tags表雖然加了索引數(shù)據(jù)量一旦上了百萬級這條SQL的執(zhí)行時間就開始肉眼可見地飆升。用EXPLAIN看執(zhí)行計劃發(fā)現(xiàn)子查詢部分被反復(fù)執(zhí)行外層每一行匹配都重新跑一遍子查詢這就是典型的“相關(guān)子查詢”或者優(yōu)化器選擇不當(dāng)造成的性能災(zāi)難。這個過程的代價非常具體假設(shè)外層orders有 50 萬行子查詢每次掃描 20 萬行那么理論掃描行數(shù)就是 50 萬乘以 20 萬這是一個天文數(shù)字再快的磁盤也扛不住。實際執(zhí)行計劃往往表現(xiàn)為DEPENDENT SUBQUERY你一看就知道優(yōu)化器把子查詢當(dāng)成相關(guān)子查詢處理了。1.2 物化優(yōu)化的核心邏輯子查詢物化Subquery Materialization是 MySQL 5.6 及以后版本引入的優(yōu)化策略核心思路其實特別簡單先把子查詢的結(jié)果集抓出來生成一張內(nèi)存中的臨時表臨時表上自動建好索引然后再結(jié)合外層查詢?nèi)プ鲫P(guān)聯(lián)判斷。還是上面那條SQL物化優(yōu)化后等價于SELECT * FROM orders o INNER JOIN ( SELECT DISTINCT user_id FROM user_tags WHERE tag_type vip AND created_at 2023-01-01 ) AS tmp ON o.user_id tmp.user_id;SELECT DISTINCT user_id這部分就是物化的結(jié)果它只執(zhí)行一次。臨時表會根據(jù)user_id自動建立索引隨后外層 50 萬行訂單記錄逐一去臨時表里做索引查找每次都走索引單次查找基本是微秒級。兩種方式的消耗差距不用跑壓測都能估算出來。所以物化的本質(zhì)是用一次確定的中間結(jié)果集替代原本可能重復(fù)無數(shù)次的子查詢執(zhí)行。這個思路在很多數(shù)據(jù)庫優(yōu)化器里都有體現(xiàn)MySQL 的實現(xiàn)相對成熟PHP 開發(fā)者的任務(wù)就是理解它、觸發(fā)它、必要時手動模擬它。1.3 為什么物化能帶來數(shù)量級的提升關(guān)鍵在于“重復(fù)執(zhí)行”和“建索引”兩個詞。傳統(tǒng)相關(guān)子查詢的執(zhí)行路徑是外層每拿一行數(shù)據(jù)就去執(zhí)行一次子查詢。如果外層有 N 行子查詢本身成本是 M那總成本就是 N×M。物化之后子查詢只執(zhí)行一次假設(shè)結(jié)果是 R 行臨時表建索引成本約 R×log(R)外層關(guān)聯(lián)成本是 N×log(R)總體上從乘積關(guān)系降到了加法關(guān)系。舉個直觀的數(shù)字例子外層 10 萬行子查詢每次掃描 5 萬行。優(yōu)化前10萬 × 5萬 50 億行掃描物化后子查詢執(zhí)行一次 5 萬行 臨時表關(guān)聯(lián) 10 萬次索引查找 ≈ 15 萬行級操作差距是四個數(shù)量級。這也是為什么只把SQL邏輯“看懂了”遠(yuǎn)遠(yuǎn)不夠必須看清優(yōu)化器生成的是哪種執(zhí)行策略。注意子查詢物化并不是所有場景都自動開啟。MySQL 優(yōu)化器有自己的成本模型它會在物化和非物化之間做代價估算。很多情況下優(yōu)化器做出了錯誤的決策或者因為參數(shù)設(shè)置、表結(jié)構(gòu)統(tǒng)計信息過期導(dǎo)致沒有走物化路徑。這時候就需要PHP開發(fā)者介入通過SQL改寫或者會話級參數(shù)調(diào)整來“手動”觸發(fā)優(yōu)化。2. 哪些場景適合物化哪些場景反而得不償失2.1 典型適用場景非相關(guān)子查詢 結(jié)果集適中物化最適合的場景有三個明顯特征。第一子查詢和外層查詢之間沒有直接關(guān)聯(lián)條件也就是非相關(guān)子查詢。比如WHERE id IN (SELECT user_id FROM ...),子查詢不引用外層表的字段它的結(jié)果集是固定的天然適合先算出來再復(fù)用。第二子查詢結(jié)果集大小適中。什么算適中從實踐看幾千到幾十萬行都是物化的舒適區(qū)。結(jié)果集太大臨時表會從內(nèi)存溢出到磁盤物化反而變成一次大表復(fù)制性能反而更差。第三外層查詢量級大子查詢結(jié)果集相對小。這樣物化后的索引關(guān)聯(lián)優(yōu)勢才能充分放大。反過來如果外層只有幾十行子查詢即使每次執(zhí)行也就幾千行的成本物化的收益就不明顯甚至因為建臨時表有額外開銷比直接嵌套執(zhí)行還慢。典型例子SELECT * FROM products WHERE category_id IN ( SELECT category_id FROM categories WHERE status 1 );categories表通常只有幾百行products可能有百萬行。物化后臨時表就幾百行建索引成本極低外層百萬行索引關(guān)聯(lián)暢快無比。2.2 不適合的邊界情況大結(jié)果集與相關(guān)子查詢物化不是萬能藥。最典型的反例是子查詢結(jié)果集極大比如子查詢返回幾百萬行物化過程本身就要把這幾百萬行先寫入臨時表這個寫入成本已經(jīng)很恐怖了再加上臨時表可能落到磁盤I/O開銷直接吞掉所有收益。另一個反例是相關(guān)子查詢。相關(guān)子查詢的含義是子查詢里引用了外層表的字段SELECT * FROM orders o WHERE EXISTS ( SELECT 1 FROM user_tags t WHERE t.user_id o.user_id AND t.tag_type vip );這種子查詢的結(jié)果集依賴于外層每一行的o.user_id物化的語義天然沖突——你沒法先算一個固定的結(jié)果集。所以遇到這種查詢優(yōu)化器通常會走DEPENDENT SUBQUERY或者半連接的其他策略比如FirstMatch、Duplicate Weedout物化策略不適用。如果遇到這種情況PHP側(cè)真正有效的手段是人工改寫把相關(guān)子查詢改寫成 JOIN或者把子查詢拆成兩步先在PHP里查一次用戶ID集合再拼第二層IN查詢。2.3 PHP開發(fā)者怎么判斷優(yōu)化器有沒有走物化判斷方法很直接就是看EXPLAIN的輸出。還是剛才那條訂單查詢?nèi)绻吡宋锘瘓?zhí)行計劃里子查詢那一行的select_type顯示為MATERIALIZED然后table顯示為subquery2外層表的select_type是SIMPLE并且Extra里能看到類似Using where的標(biāo)記。我用一段偽代碼來說明判斷流程// 偽代碼: 執(zhí)行EXPLAIN并解析關(guān)鍵列 $explainResult query( EXPLAIN SELECT * FROM orders o WHERE o.user_id IN (SELECT user_id FROM user_tags WHERE tag_type vip) ); foreach ($explainResult as $row) { if ($row[select_type] MATERIALIZED) { echo 子查詢走了物化\n; } if (strpos($row[table], subquery) ! false) { echo 外層在關(guān)聯(lián)物化臨時表: {$row[table]}\n; } }執(zhí)行計劃是優(yōu)化器意圖的直接體現(xiàn)。每次優(yōu)化之前先拉一份EXPLAIN確認(rèn)瓶頸是不是子查詢再確認(rèn)優(yōu)化器當(dāng)前選擇的策略這才是對癥下藥的第一步。不看執(zhí)行計劃直接改SQL大概率是在瞎猜。3. PHP側(cè)的實操方案三板斧完成優(yōu)化落地3.1 第一板斧拉執(zhí)行計劃確認(rèn)當(dāng)前策略任何優(yōu)化動作之前第一件事是在數(shù)據(jù)庫上直接執(zhí)行EXPLAIN確認(rèn)當(dāng)前優(yōu)化器的選擇。我一般會順手把EXPLAIN FORMATJSON也跑一遍因為JSON格式里信息更全能看到materialized_from_subquery這樣的關(guān)鍵字。實際命令長這樣EXPLAIN FORMATJSON SELECT * FROM orders o WHERE o.user_id IN ( SELECT user_id FROM user_tags WHERE tag_type vip AND created_at 2023-01-01 );輸出里如果query_block下出現(xiàn)了materialized_from_subquery: { using_temporary_table: true, dependent: false, cacheable: true }那就說明物化確實生效了。看到dependent: false表示這是非相關(guān)子查詢cacheable: true表示結(jié)果可以被緩存復(fù)用這正是我們想要的。如果沒看到物化關(guān)鍵字而是看到DEPENDENT SUBQUERY那就說明優(yōu)化器選擇了最差的路徑需要進(jìn)入第二板斧。3.2 第二板斧SQL改寫把“相關(guān)”變成“不相關(guān)”遇到優(yōu)化器不開竅的情況最穩(wěn)妥的PHP側(cè)方案就是人工改寫SQL把邏輯從“相關(guān)子查詢”變成“非相關(guān)子查詢”或者“JOIN”。以我們線上最常遇到的場景為例原始寫法是這樣的SELECT * FROM orders o WHERE EXISTS ( SELECT 1 FROM user_tags t WHERE t.user_id o.user_id AND t.tag_type vip );這種寫法優(yōu)化器大概率走DEPENDENT SUBQUERY性能堪憂。改寫后的方案SELECT o.* FROM orders o INNER JOIN ( SELECT DISTINCT user_id FROM user_tags WHERE tag_type vip ) t ON o.user_id t.user_id;改寫之后子查詢部分變成了派生表Derived TableMySQL 會對派生表做物化處理并且自動根據(jù)user_id建索引。后續(xù)外層關(guān)聯(lián)走的路徑本質(zhì)就是物化臨時表上的索引查找。這個方案在 MySQL 5.7 和 8.0 上都表現(xiàn)穩(wěn)定。注意改寫SQL不能只追求“看起來優(yōu)化了”必須在改寫后立刻再跑一次EXPLAIN確認(rèn)執(zhí)行計劃。有時候派生表的物化策略會被derived_merge優(yōu)化覆蓋也就是說優(yōu)化器會把派生表合并進(jìn)外層查詢這本身也是一種優(yōu)化但場景不同表現(xiàn)不同一定要實測確認(rèn)。3.3 第三板斧會話級參數(shù)調(diào)整給優(yōu)化器“遞刀子”上一節(jié)說到物化需要臨時表來承載結(jié)果集。如果臨時表太大MySQL 會把臨時表從內(nèi)存轉(zhuǎn)到磁盤性能立刻跳水。這時候僅僅改寫SQL是不夠的還要讓臨時表這個容器足夠大。跟臨時表相關(guān)的參數(shù)主要有兩個tmp_table_size和max_heap_table_size。MySQL 的內(nèi)存臨時表大小會取這兩個參數(shù)中的較小值。默認(rèn)情況下這個值往往只有 16MB 到 64MB一旦物化結(jié)果超過這個閾值臨時表就會落到磁盤。PHP側(cè)可以在建立數(shù)據(jù)庫連接后針對當(dāng)前會話執(zhí)行// 使用 PDO $pdo-exec(SET SESSION tmp_table_size 268435456); $pdo-exec(SET SESSION max_heap_table_size 268435456);這里把臨時表上限調(diào)到了 256MB。但這個值不能盲目調(diào)大得配合實際數(shù)據(jù)量。如果物化結(jié)果經(jīng)常超過 256MB說明結(jié)果集本身太大物化方案本身就該重新評估。另外一個值得關(guān)注的參數(shù)是 MySQL 8.0 的internal_tmp_mem_storage_engine它控制內(nèi)存臨時表的存儲引擎通常設(shè)置為TempTable也比默認(rèn)的MEMORY存儲引擎更高效。SET SESSION internal_tmp_mem_storage_engine TempTable;參數(shù)調(diào)整的本質(zhì)是“告訴優(yōu)化器你可以放心大膽地物化臨時表空間足夠”。但調(diào)參之前必須清楚這畢竟只是讓優(yōu)化器有更多操作空間SQL本身的結(jié)構(gòu)才是決定性的。4. 常見問題與排查技巧實錄4.1 物化臨時表超過內(nèi)存限制性能反而更差這是我在實際項目里踩過最深的一個坑。現(xiàn)象改寫SQL后第一次執(zhí)行EXPLAIN確實看到MATERIALIZED但線上執(zhí)行時間反而從 2 秒漲到了 8 秒。排查后定位到問題物化結(jié)果集有 80 多萬行超過臨時表內(nèi)存閾值MySQL 把臨時表寫到了磁盤等于在磁盤上先復(fù)制了 80 萬行數(shù)據(jù)再回表關(guān)聯(lián)中間多了大量 I/O。解決辦法有兩種一是優(yōu)化子查詢本身通過加過濾條件、縮小取樣范圍把物化結(jié)果集降下來二是有意識地在PHP側(cè)做一個“分片物化”——把大IN查詢拆成多個批次每個批次控制在5萬行以內(nèi)分多次查詢再合并結(jié)果。這里分享一個我用過的PHP分片邏輯// 偽代碼: 大IN集合分批查詢 $userIdChunks array_chunk($vipUserIds, 50000); $result []; foreach ($userIdChunks as $chunk) { $placeholders implode(,, array_fill(0, count($chunk), ?)); $sql SELECT * FROM orders WHERE user_id IN ($placeholders); $stmt $pdo-prepare($sql); $stmt-execute(array_values($chunk)); $result array_merge($result, $stmt-fetchAll()); }分批之后每次物化的臨時表都控制在合理范圍整體執(zhí)行時間反而比一次性大IN更快。這個思路的本質(zhì)就是讓物化結(jié)果集大小始終處于最優(yōu)區(qū)間。4.2 物化臨時表上的索引失效MySQL 物化臨時表會自動建索引這一點是很多資料里明確提到的。但在某些版本或特殊場景下物化臨時表的索引可能沒有覆蓋到你需要關(guān)聯(lián)的字段或者在臨時表上繼續(xù)做過濾時優(yōu)化器選擇了全表掃描。遇到這類問題時建議直接在EXPLAIN輸出中查看table列里subqueryN對應(yīng)行的key字段。如果key為空說明物化臨時表上沒有可用索引外層關(guān)聯(lián)正在做全表掃描。這種情況比較少見但一旦遇上最有效的辦法還是SQL改寫。比如在子查詢內(nèi)部先做DISTINCT和必要的過濾確保物化結(jié)果集的粒度是唯一的。如果內(nèi)部已經(jīng)DISTINCT了優(yōu)化器通常會建立唯一索引關(guān)聯(lián)效率會有明顯提升。4.3 實戰(zhàn)排查速查表我把這幾年排查子查詢性能問題的經(jīng)驗匯總成了一張表每次遇到類似問題按表里的順序逐項排查基本都能快速定位排查項判斷方法解決方案執(zhí)行計劃是否走物化EXPLAIN中select_typeMATERIALIZED未走物化時改寫SQL、加索引物化臨時表是否落盤EXPLAIN中Extra出現(xiàn)Using temporary且狀態(tài)文件在磁盤調(diào)大tmp_table_size、減少物化結(jié)果集臨時表是否有索引EXPLAIN中table 行key列非空子查詢內(nèi)部加DISTINCT、確保關(guān)聯(lián)字段唯一子查詢是否相關(guān)子查詢是否引用外層字段改寫為JOIN或非相關(guān)子查詢外層結(jié)果集是否被過度過濾實際返回行數(shù)和掃描行數(shù)差距極大優(yōu)化索引、考慮覆蓋索引這張表基本能解決80%以上的子查詢性能問題。核心原則是每一步優(yōu)化都反復(fù)用EXPLAIN驗證不要憑感覺下結(jié)論。5. 從一次真實壓測看物化優(yōu)化的收益最后講一個我參與過的壓測案例幫助大家直觀感受物化帶來的數(shù)據(jù)變化。某統(tǒng)計接口原本依賴一條三層嵌套子查詢最內(nèi)層是百萬級訂單表中間層是用戶標(biāo)簽過濾外層再做聚合。優(yōu)化前接口P95響應(yīng)時間在4秒左右數(shù)據(jù)庫服務(wù)器CPU動不動飆到90%。第一步拉EXPLAIN確認(rèn)執(zhí)行計劃發(fā)現(xiàn)最內(nèi)層子查詢被當(dāng)作相關(guān)子查詢反復(fù)執(zhí)行掃描行數(shù)極其夸張。第二步將嵌套子查詢改寫成兩級物化派生表第一級先物化“符合條件的用戶ID集合”第二級把訂單表和物化結(jié)果做INNER JOIN然后在外層做聚合。改寫后P95響應(yīng)時間直接從4秒降到220毫秒數(shù)據(jù)庫CPU穩(wěn)定在20%左右。壓測300并發(fā)單接口吞吐量從原先的每秒40次提升到每秒350次。這個案例里子查詢物化帶來的不是百分之幾十的優(yōu)化而是數(shù)量級的吞吐改善。如果當(dāng)初停留在“給子查詢加索引”這種表面操作根本解決不了問題——因為相關(guān)子查詢每執(zhí)行一次都是全量掃描加多少索引都沒用?，F(xiàn)在再回頭看這個項目的標(biāo)題“php方案 子查詢物化優(yōu)化”其實本質(zhì)就兩件事理解MySQL優(yōu)化器的物化策略然后在PHP側(cè)通過SQL改寫和會話參數(shù)調(diào)整讓優(yōu)化器選擇正確的執(zhí)行計劃。這兩件事做扎實了大部分慢查詢問題都能迎刃而解。在我的實操經(jīng)驗里子查詢物化優(yōu)化最大的價值不是某一個SQL的提速而是提供了一種思維方式——先想清楚數(shù)據(jù)是怎么流動的再決定怎么讓數(shù)據(jù)庫少干活。你拿到的執(zhí)行計劃就是數(shù)據(jù)庫在告訴你它打算怎么干活你能做的就是讓它干得更聰明。下次如果你在PHP項目里再遇到類似的慢查詢先別急著加緩存用EXPLAIN拉一下執(zhí)行計劃看看有沒有MATERIALIZED這條路走通了很多性能問題其實沒有想象中那么難。 PHP方案實戰(zhàn)：子查詢物化優(yōu)化解決MySQL慢查詢
我們直接從項目標(biāo)題入手。這個標(biāo)題是《php方案 子查詢物化優(yōu)化》看起來像是后端開發(fā)里一條冷門的性能優(yōu)化經(jīng)驗但實際一拆開里面全是干貨。我在過去幾年的PHP開發(fā)里不止一次被慢查詢折騰到凌晨最后靠子查詢物化這個思路把接口響應(yīng)時間從幾秒壓到幾十毫秒。這篇就專門聊聊子查詢物化是什么PHP側(cè)該怎么判斷、怎么用、有哪些坑。適合正在和MySQL慢查詢較勁的后端開發(fā)者、搞過SQL優(yōu)化的朋友以及準(zhǔn)備面試性能優(yōu)化話題的同學(xué)。1. 子查詢物化到底是什么先搞懂優(yōu)化器在做什么1.1 一次慢查詢事故還原先說一個很典型的場景。某天線上一個訂單統(tǒng)計接口突然變慢排查時發(fā)現(xiàn)罪魁禍?zhǔn)资窍旅孢@類查詢SELECT * FROM orders o WHERE o.user_id IN ( SELECT user_id FROM user_tags WHERE tag_type vip AND created_at 2023-01-01 );初看沒毛病邏輯很簡單——找出所有帶“VIP”標(biāo)簽的用戶然后查這些用戶的訂單。但問題是user_tags表雖然加了索引數(shù)據(jù)量一旦上了百萬級這條SQL的執(zhí)行時間就開始肉眼可見地飆升。用EXPLAIN看執(zhí)行計劃發(fā)現(xiàn)子查詢部分被反復(fù)執(zhí)行外層每一行匹配都重新跑一遍子查詢這就是典型的“相關(guān)子查詢”或者優(yōu)化器選擇不當(dāng)造成的性能災(zāi)難。這個過程的代價非常具體假設(shè)外層orders有 50 萬行子查詢每次掃描 20 萬行那么理論掃描行數(shù)就是 50 萬乘以 20 萬這是一個天文數(shù)字再快的磁盤也扛不住。實際執(zhí)行計劃往往表現(xiàn)為DEPENDENT SUBQUERY你一看就知道優(yōu)化器把子查詢當(dāng)成相關(guān)子查詢處理了。1.2 物化優(yōu)化的核心邏輯子查詢物化Subquery Materialization是 MySQL 5.6 及以后版本引入的優(yōu)化策略核心思路其實特別簡單先把子查詢的結(jié)果集抓出來生成一張內(nèi)存中的臨時表臨時表上自動建好索引然后再結(jié)合外層查詢?nèi)プ鲫P(guān)聯(lián)判斷。還是上面那條SQL物化優(yōu)化后等價于SELECT * FROM orders o INNER JOIN ( SELECT DISTINCT user_id FROM user_tags WHERE tag_type vip AND created_at 2023-01-01 ) AS tmp ON o.user_id tmp.user_id;SELECT DISTINCT user_id這部分就是物化的結(jié)果它只執(zhí)行一次。臨時表會根據(jù)user_id自動建立索引隨后外層 50 萬行訂單記錄逐一去臨時表里做索引查找每次都走索引單次查找基本是微秒級。兩種方式的消耗差距不用跑壓測都能估算出來。所以物化的本質(zhì)是用一次確定的中間結(jié)果集替代原本可能重復(fù)無數(shù)次的子查詢執(zhí)行。這個思路在很多數(shù)據(jù)庫優(yōu)化器里都有體現(xiàn)MySQL 的實現(xiàn)相對成熟PHP 開發(fā)者的任務(wù)就是理解它、觸發(fā)它、必要時手動模擬它。1.3 為什么物化能帶來數(shù)量級的提升關(guān)鍵在于“重復(fù)執(zhí)行”和“建索引”兩個詞。傳統(tǒng)相關(guān)子查詢的執(zhí)行路徑是外層每拿一行數(shù)據(jù)就去執(zhí)行一次子查詢。如果外層有 N 行子查詢本身成本是 M那總成本就是 N×M。物化之后子查詢只執(zhí)行一次假設(shè)結(jié)果是 R 行臨時表建索引成本約 R×log(R)外層關(guān)聯(lián)成本是 N×log(R)總體上從乘積關(guān)系降到了加法關(guān)系。舉個直觀的數(shù)字例子外層 10 萬行子查詢每次掃描 5 萬行。優(yōu)化前10萬 × 5萬 50 億行掃描物化后子查詢執(zhí)行一次 5 萬行 臨時表關(guān)聯(lián) 10 萬次索引查找 ≈ 15 萬行級操作差距是四個數(shù)量級。這也是為什么只把SQL邏輯“看懂了”遠(yuǎn)遠(yuǎn)不夠必須看清優(yōu)化器生成的是哪種執(zhí)行策略。注意子查詢物化并不是所有場景都自動開啟。MySQL 優(yōu)化器有自己的成本模型它會在物化和非物化之間做代價估算。很多情況下優(yōu)化器做出了錯誤的決策或者因為參數(shù)設(shè)置、表結(jié)構(gòu)統(tǒng)計信息過期導(dǎo)致沒有走物化路徑。這時候就需要PHP開發(fā)者介入通過SQL改寫或者會話級參數(shù)調(diào)整來“手動”觸發(fā)優(yōu)化。2. 哪些場景適合物化哪些場景反而得不償失2.1 典型適用場景非相關(guān)子查詢 結(jié)果集適中物化最適合的場景有三個明顯特征。第一子查詢和外層查詢之間沒有直接關(guān)聯(lián)條件也就是非相關(guān)子查詢。比如WHERE id IN (SELECT user_id FROM ...),子查詢不引用外層表的字段它的結(jié)果集是固定的天然適合先算出來再復(fù)用。第二子查詢結(jié)果集大小適中。什么算適中從實踐看幾千到幾十萬行都是物化的舒適區(qū)。結(jié)果集太大臨時表會從內(nèi)存溢出到磁盤物化反而變成一次大表復(fù)制性能反而更差。第三外層查詢量級大子查詢結(jié)果集相對小。這樣物化后的索引關(guān)聯(lián)優(yōu)勢才能充分放大。反過來如果外層只有幾十行子查詢即使每次執(zhí)行也就幾千行的成本物化的收益就不明顯甚至因為建臨時表有額外開銷比直接嵌套執(zhí)行還慢。典型例子SELECT * FROM products WHERE category_id IN ( SELECT category_id FROM categories WHERE status 1 );categories表通常只有幾百行products可能有百萬行。物化后臨時表就幾百行建索引成本極低外層百萬行索引關(guān)聯(lián)暢快無比。2.2 不適合的邊界情況大結(jié)果集與相關(guān)子查詢物化不是萬能藥。最典型的反例是子查詢結(jié)果集極大比如子查詢返回幾百萬行物化過程本身就要把這幾百萬行先寫入臨時表這個寫入成本已經(jīng)很恐怖了再加上臨時表可能落到磁盤I/O開銷直接吞掉所有收益。另一個反例是相關(guān)子查詢。相關(guān)子查詢的含義是子查詢里引用了外層表的字段SELECT * FROM orders o WHERE EXISTS ( SELECT 1 FROM user_tags t WHERE t.user_id o.user_id AND t.tag_type vip );這種子查詢的結(jié)果集依賴于外層每一行的o.user_id物化的語義天然沖突——你沒法先算一個固定的結(jié)果集。所以遇到這種查詢優(yōu)化器通常會走DEPENDENT SUBQUERY或者半連接的其他策略比如FirstMatch、Duplicate Weedout物化策略不適用。如果遇到這種情況PHP側(cè)真正有效的手段是人工改寫把相關(guān)子查詢改寫成 JOIN或者把子查詢拆成兩步先在PHP里查一次用戶ID集合再拼第二層IN查詢。2.3 PHP開發(fā)者怎么判斷優(yōu)化器有沒有走物化判斷方法很直接就是看EXPLAIN的輸出。還是剛才那條訂單查詢?nèi)绻吡宋锘瘓?zhí)行計劃里子查詢那一行的select_type顯示為MATERIALIZED然后table顯示為subquery2外層表的select_type是SIMPLE并且Extra里能看到類似Using where的標(biāo)記。我用一段偽代碼來說明判斷流程// 偽代碼: 執(zhí)行EXPLAIN并解析關(guān)鍵列 $explainResult query( EXPLAIN SELECT * FROM orders o WHERE o.user_id IN (SELECT user_id FROM user_tags WHERE tag_type vip) ); foreach ($explainResult as $row) { if ($row[select_type] MATERIALIZED) { echo 子查詢走了物化\n; } if (strpos($row[table], subquery) ! false) { echo 外層在關(guān)聯(lián)物化臨時表: {$row[table]}\n; } }執(zhí)行計劃是優(yōu)化器意圖的直接體現(xiàn)。每次優(yōu)化之前先拉一份EXPLAIN確認(rèn)瓶頸是不是子查詢再確認(rèn)優(yōu)化器當(dāng)前選擇的策略這才是對癥下藥的第一步。不看執(zhí)行計劃直接改SQL大概率是在瞎猜。3. PHP側(cè)的實操方案三板斧完成優(yōu)化落地3.1 第一板斧拉執(zhí)行計劃確認(rèn)當(dāng)前策略任何優(yōu)化動作之前第一件事是在數(shù)據(jù)庫上直接執(zhí)行EXPLAIN確認(rèn)當(dāng)前優(yōu)化器的選擇。我一般會順手把EXPLAIN FORMATJSON也跑一遍因為JSON格式里信息更全能看到materialized_from_subquery這樣的關(guān)鍵字。實際命令長這樣EXPLAIN FORMATJSON SELECT * FROM orders o WHERE o.user_id IN ( SELECT user_id FROM user_tags WHERE tag_type vip AND created_at 2023-01-01 );輸出里如果query_block下出現(xiàn)了materialized_from_subquery: { using_temporary_table: true, dependent: false, cacheable: true }那就說明物化確實生效了。看到dependent: false表示這是非相關(guān)子查詢cacheable: true表示結(jié)果可以被緩存復(fù)用這正是我們想要的。如果沒看到物化關(guān)鍵字而是看到DEPENDENT SUBQUERY那就說明優(yōu)化器選擇了最差的路徑需要進(jìn)入第二板斧。3.2 第二板斧SQL改寫把“相關(guān)”變成“不相關(guān)”遇到優(yōu)化器不開竅的情況最穩(wěn)妥的PHP側(cè)方案就是人工改寫SQL把邏輯從“相關(guān)子查詢”變成“非相關(guān)子查詢”或者“JOIN”。以我們線上最常遇到的場景為例原始寫法是這樣的SELECT * FROM orders o WHERE EXISTS ( SELECT 1 FROM user_tags t WHERE t.user_id o.user_id AND t.tag_type vip );這種寫法優(yōu)化器大概率走DEPENDENT SUBQUERY性能堪憂。改寫后的方案SELECT o.* FROM orders o INNER JOIN ( SELECT DISTINCT user_id FROM user_tags WHERE tag_type vip ) t ON o.user_id t.user_id;改寫之后子查詢部分變成了派生表Derived TableMySQL 會對派生表做物化處理并且自動根據(jù)user_id建索引。后續(xù)外層關(guān)聯(lián)走的路徑本質(zhì)就是物化臨時表上的索引查找。這個方案在 MySQL 5.7 和 8.0 上都表現(xiàn)穩(wěn)定。注意改寫SQL不能只追求“看起來優(yōu)化了”必須在改寫后立刻再跑一次EXPLAIN確認(rèn)執(zhí)行計劃。有時候派生表的物化策略會被derived_merge優(yōu)化覆蓋也就是說優(yōu)化器會把派生表合并進(jìn)外層查詢這本身也是一種優(yōu)化但場景不同表現(xiàn)不同一定要實測確認(rèn)。3.3 第三板斧會話級參數(shù)調(diào)整給優(yōu)化器“遞刀子”上一節(jié)說到物化需要臨時表來承載結(jié)果集。如果臨時表太大MySQL 會把臨時表從內(nèi)存轉(zhuǎn)到磁盤性能立刻跳水。這時候僅僅改寫SQL是不夠的還要讓臨時表這個容器足夠大。跟臨時表相關(guān)的參數(shù)主要有兩個tmp_table_size和max_heap_table_size。MySQL 的內(nèi)存臨時表大小會取這兩個參數(shù)中的較小值。默認(rèn)情況下這個值往往只有 16MB 到 64MB一旦物化結(jié)果超過這個閾值臨時表就會落到磁盤。PHP側(cè)可以在建立數(shù)據(jù)庫連接后針對當(dāng)前會話執(zhí)行// 使用 PDO $pdo-exec(SET SESSION tmp_table_size 268435456); $pdo-exec(SET SESSION max_heap_table_size 268435456);這里把臨時表上限調(diào)到了 256MB。但這個值不能盲目調(diào)大得配合實際數(shù)據(jù)量。如果物化結(jié)果經(jīng)常超過 256MB說明結(jié)果集本身太大物化方案本身就該重新評估。另外一個值得關(guān)注的參數(shù)是 MySQL 8.0 的internal_tmp_mem_storage_engine它控制內(nèi)存臨時表的存儲引擎通常設(shè)置為TempTable也比默認(rèn)的MEMORY存儲引擎更高效。SET SESSION internal_tmp_mem_storage_engine TempTable;參數(shù)調(diào)整的本質(zhì)是“告訴優(yōu)化器你可以放心大膽地物化臨時表空間足夠”。但調(diào)參之前必須清楚這畢竟只是讓優(yōu)化器有更多操作空間SQL本身的結(jié)構(gòu)才是決定性的。4. 常見問題與排查技巧實錄4.1 物化臨時表超過內(nèi)存限制性能反而更差這是我在實際項目里踩過最深的一個坑。現(xiàn)象改寫SQL后第一次執(zhí)行EXPLAIN確實看到MATERIALIZED但線上執(zhí)行時間反而從 2 秒漲到了 8 秒。排查后定位到問題物化結(jié)果集有 80 多萬行超過臨時表內(nèi)存閾值MySQL 把臨時表寫到了磁盤等于在磁盤上先復(fù)制了 80 萬行數(shù)據(jù)再回表關(guān)聯(lián)中間多了大量 I/O。解決辦法有兩種一是優(yōu)化子查詢本身通過加過濾條件、縮小取樣范圍把物化結(jié)果集降下來二是有意識地在PHP側(cè)做一個“分片物化”——把大IN查詢拆成多個批次每個批次控制在5萬行以內(nèi)分多次查詢再合并結(jié)果。這里分享一個我用過的PHP分片邏輯// 偽代碼: 大IN集合分批查詢 $userIdChunks array_chunk($vipUserIds, 50000); $result []; foreach ($userIdChunks as $chunk) { $placeholders implode(,, array_fill(0, count($chunk), ?)); $sql SELECT * FROM orders WHERE user_id IN ($placeholders); $stmt $pdo-prepare($sql); $stmt-execute(array_values($chunk)); $result array_merge($result, $stmt-fetchAll()); }分批之后每次物化的臨時表都控制在合理范圍整體執(zhí)行時間反而比一次性大IN更快。這個思路的本質(zhì)就是讓物化結(jié)果集大小始終處于最優(yōu)區(qū)間。4.2 物化臨時表上的索引失效MySQL 物化臨時表會自動建索引這一點是很多資料里明確提到的。但在某些版本或特殊場景下物化臨時表的索引可能沒有覆蓋到你需要關(guān)聯(lián)的字段或者在臨時表上繼續(xù)做過濾時優(yōu)化器選擇了全表掃描。遇到這類問題時建議直接在EXPLAIN輸出中查看table列里subqueryN對應(yīng)行的key字段。如果key為空說明物化臨時表上沒有可用索引外層關(guān)聯(lián)正在做全表掃描。這種情況比較少見但一旦遇上最有效的辦法還是SQL改寫。比如在子查詢內(nèi)部先做DISTINCT和必要的過濾確保物化結(jié)果集的粒度是唯一的。如果內(nèi)部已經(jīng)DISTINCT了優(yōu)化器通常會建立唯一索引關(guān)聯(lián)效率會有明顯提升。4.3 實戰(zhàn)排查速查表我把這幾年排查子查詢性能問題的經(jīng)驗匯總成了一張表每次遇到類似問題按表里的順序逐項排查基本都能快速定位排查項判斷方法解決方案執(zhí)行計劃是否走物化EXPLAIN中select_typeMATERIALIZED未走物化時改寫SQL、加索引物化臨時表是否落盤EXPLAIN中Extra出現(xiàn)Using temporary且狀態(tài)文件在磁盤調(diào)大tmp_table_size、減少物化結(jié)果集臨時表是否有索引EXPLAIN中table 行key列非空子查詢內(nèi)部加DISTINCT、確保關(guān)聯(lián)字段唯一子查詢是否相關(guān)子查詢是否引用外層字段改寫為JOIN或非相關(guān)子查詢外層結(jié)果集是否被過度過濾實際返回行數(shù)和掃描行數(shù)差距極大優(yōu)化索引、考慮覆蓋索引這張表基本能解決80%以上的子查詢性能問題。核心原則是每一步優(yōu)化都反復(fù)用EXPLAIN驗證不要憑感覺下結(jié)論。5. 從一次真實壓測看物化優(yōu)化的收益最后講一個我參與過的壓測案例幫助大家直觀感受物化帶來的數(shù)據(jù)變化。某統(tǒng)計接口原本依賴一條三層嵌套子查詢最內(nèi)層是百萬級訂單表中間層是用戶標(biāo)簽過濾外層再做聚合。優(yōu)化前接口P95響應(yīng)時間在4秒左右數(shù)據(jù)庫服務(wù)器CPU動不動飆到90%。第一步拉EXPLAIN確認(rèn)執(zhí)行計劃發(fā)現(xiàn)最內(nèi)層子查詢被當(dāng)作相關(guān)子查詢反復(fù)執(zhí)行掃描行數(shù)極其夸張。第二步將嵌套子查詢改寫成兩級物化派生表第一級先物化“符合條件的用戶ID集合”第二級把訂單表和物化結(jié)果做INNER JOIN然后在外層做聚合。改寫后P95響應(yīng)時間直接從4秒降到220毫秒數(shù)據(jù)庫CPU穩(wěn)定在20%左右。壓測300并發(fā)單接口吞吐量從原先的每秒40次提升到每秒350次。這個案例里子查詢物化帶來的不是百分之幾十的優(yōu)化而是數(shù)量級的吞吐改善。如果當(dāng)初停留在“給子查詢加索引”這種表面操作根本解決不了問題——因為相關(guān)子查詢每執(zhí)行一次都是全量掃描加多少索引都沒用?，F(xiàn)在再回頭看這個項目的標(biāo)題“php方案 子查詢物化優(yōu)化”其實本質(zhì)就兩件事理解MySQL優(yōu)化器的物化策略然后在PHP側(cè)通過SQL改寫和會話參數(shù)調(diào)整讓優(yōu)化器選擇正確的執(zhí)行計劃。這兩件事做扎實了大部分慢查詢問題都能迎刃而解。在我的實操經(jīng)驗里子查詢物化優(yōu)化最大的價值不是某一個SQL的提速而是提供了一種思維方式——先想清楚數(shù)據(jù)是怎么流動的再決定怎么讓數(shù)據(jù)庫少干活。你拿到的執(zhí)行計劃就是數(shù)據(jù)庫在告訴你它打算怎么干活你能做的就是讓它干得更聰明。下次如果你在PHP項目里再遇到類似的慢查詢先別急著加緩存用EXPLAIN拉一下執(zhí)行計劃看看有沒有MATERIALIZED這條路走通了很多性能問題其實沒有想象中那么難。