1. 從一段真實業(yè)務(wù)需求說起子查詢到底解決什么問題前幾天排查一個線上慢查詢業(yè)務(wù)方丟過來一段SQL說頁面加載要七八秒用戶快把客服電話打爆了。我先看了一眼表結(jié)構(gòu)和數(shù)據(jù)量又跑了一遍EXPLAIN問題很快就定位到了——三層嵌套子查詢最內(nèi)層掃了全表中間層又做了文件排序外層再逐個匹配。這大概是MySQL子查詢最典型也最容易被寫壞的場景之一。先給不太熟的朋友補個基礎(chǔ)定義子查詢就是嵌套在另一個SQL父查詢內(nèi)部的SELECT語句它可以出現(xiàn)在WHERE、FROM、HAVING、SELECT列表、甚至UPDATE/DELETE語句中。你可能會問明明用JOIN也能實現(xiàn)大部分需求為什么還要用子查詢因為有些邏輯用子查詢表達更直觀比如“找出比部門平均工資高的員工”“查詢最近一單金額超過500的用戶”——這類需求如果硬改成JOIN要么需要先聚合生成臨時表要么得用窗口函數(shù)繞一大圈代碼可讀性反而更差。子查詢這個語法從MySQL 4.1就開始支持了經(jīng)歷過好幾個版本的優(yōu)化器改寫策略迭代。5.6之前性能經(jīng)常被吐槽5.7開始引入了半連接優(yōu)化和物化策略8.0又對派生表做了Lateral Derived優(yōu)化、窗口函數(shù)配合子查詢也更好用了。所以這篇文章不是簡單羅列語法而是結(jié)合我實際排查和優(yōu)化過的案例把子查詢的分類、執(zhí)行原理、性能陷阱、改寫技巧一次講透。2. 子查詢的七種形態(tài)與實戰(zhàn)拆解2.1 標(biāo)量子查詢最靈活但最容易忽視NULL標(biāo)量子查詢返回的是一行一列的單個值可以用在幾乎所有需要單個值的地方。舉幾個最常見的場景-- 查詢每個員工的工資與部門平均工資的差額 SELECT emp_name, salary, salary - (SELECT AVG(salary) FROM dept_emp WHERE dept_id e.dept_id) AS diff FROM employee e;這段SQL中SELECT AVG(salary)返回的是一個數(shù)值這就是標(biāo)量子查詢。實際開發(fā)中標(biāo)量子查詢最常見的問題不是性能而是NULL參與運算導(dǎo)致結(jié)果變成NULL。如果某個部門還沒有員工內(nèi)層AVG返回NULL外層salary - NULL的結(jié)果就是NULL。很多報表因此出現(xiàn)大面積的空值排查半天才發(fā)現(xiàn)是子查詢返回了NULL。我的習(xí)慣是凡是參與數(shù)值運算的標(biāo)量子查詢外層一定要加IFNULL或者用COALESCE兜底SELECT emp_name, salary, salary - COALESCE((SELECT AVG(salary) FROM dept_emp WHERE dept_id e.dept_id), 0) AS diff FROM employee e;標(biāo)量子查詢還有個隱蔽的性能問題如果它在SELECT列表里并且是相關(guān)子查詢引用了外層表的字段MySQL會對每一行外層記錄都執(zhí)行一次內(nèi)層查詢。也就是說外層有10萬行內(nèi)層就執(zhí)行10萬次。這在數(shù)據(jù)量小的時候沒感覺一旦上了規(guī)模就是災(zāi)難性的后面我會專門講相關(guān)子查詢的優(yōu)化方案。2.2 列子查詢與行子查詢語法糖背后的邏輯列子查詢返回一列多行通常配合IN、ANY、ALL使用-- 找出所有在“銷售部”待過的員工 SELECT emp_name FROM employee WHERE emp_id IN ( SELECT emp_id FROM dept_history WHERE dept_name 銷售部 );行子查詢返回一行多列可以同時比較多個字段-- 找出與張三在同一個部門且薪資相同的員工 SELECT emp_name FROM employee WHERE (dept_id, salary) ( SELECT dept_id, salary FROM employee WHERE emp_name 張三 );這里有個需要留意的點MySQL對行子查詢的等值比較支持得不錯但如果比較的是多個字段且其中某個字段允許NULL行為可能和直覺不一致。比如(dept_id, salary) (NULL, 5000)這種比較在SQL的三值邏輯下永遠不是TRUE查詢結(jié)果會靜默丟失。我建議能用列子查詢加IN解決的就不要用行子查詢組合比較因為可讀性更好優(yōu)化器處理IN的改寫策略也成熟很多。2.3 表子查詢與派生表FROM子句里的臨時視圖表子查詢出現(xiàn)在FROM子句中它的結(jié)果集被當(dāng)作一張派生表使用必須起一個別名SELECT d.dept_name, tmp.avg_salary FROM department d JOIN ( SELECT dept_id, AVG(salary) AS avg_salary FROM employee GROUP BY dept_id ) tmp ON d.dept_id tmp.dept_id;這里tmp就是派生表MySQL8.0之前派生表默認是物化的也就是說MySQL會先把內(nèi)層查詢的結(jié)果存在一張內(nèi)部臨時表里再做外層JOIN。8.0.14之后優(yōu)化器支持了Lateral Derived Optimization允許派生表引用前面表的數(shù)據(jù)配合LATERAL關(guān)鍵字使用SELECT d.dept_name, tmp.emp_name FROM department d LEFT JOIN LATERAL ( SELECT emp_name FROM employee WHERE dept_id d.dept_id ORDER BY salary DESC LIMIT 1 ) tmp ON TRUE;這個寫法可以很方便地實現(xiàn)“取每個部門工資最高的員工”以前需要用窗口函數(shù)或者反連接技巧現(xiàn)在一條LATERAL派生表就搞定了。我實測過在分層匯總類需求中LATERAL派生表比傳統(tǒng)相關(guān)子查詢的性能好很多因為它可以被優(yōu)化器識別并采用更合理的連接順序。2.4 EXISTS與IN謂詞子查詢的抉擇EXISTS和IN是日常開發(fā)中用的最多的兩種謂詞子查詢但很多人并不清楚它們在MySQL內(nèi)部的不同處理路徑。先說結(jié)論半連接場景下MySQL 5.7 對IN和EXISTS的改寫策略已經(jīng)非常接近并沒有絕對的好壞之分關(guān)鍵取決于數(shù)據(jù)分布和索引情況。簡單記憶外層表小、內(nèi)層表大且內(nèi)層查詢能命中索引時IN通常表現(xiàn)更好外層表大、內(nèi)層表小且需要快速短路判斷時EXISTS配合索引通常更穩(wěn)NOT IN一定要小心NULL值一旦子查詢結(jié)果中有NULLNOT IN的判定會變成“不是UNKNOWN”導(dǎo)致整個查詢返回空集。相比之下NOT EXISTS不會踩這個坑。舉個例子公司有個接口要查詢“從未下單的用戶”新手往往寫SELECT user_id, user_name FROM users WHERE user_id NOT IN ( SELECT user_id FROM orders );只要orders表里有任何一條user_id為NULL的記錄這條查詢就永遠返回空。這不是MySQL的bug而是SQL標(biāo)準(zhǔn)中NULL的邏輯語義。正確寫法應(yīng)該用NOT EXISTSSELECT u.user_id, u.user_name FROM users u WHERE NOT EXISTS ( SELECT 1 FROM orders o WHERE o.user_id u.user_id );我處理過好幾次生產(chǎn)事故都是NOT IN遇到NULL導(dǎo)致報表數(shù)據(jù)清零排查起來還很繞。所以我的鐵律是遇到NOT IN先問一句子查詢列允許NULL嗎允許就立刻改成NOT EXISTS。3. 子查詢的執(zhí)行計劃與性能優(yōu)化深挖3.1 看懂EXPLAIN中的子查詢線索優(yōu)化子查詢的第一件事是學(xué)會看執(zhí)行計劃。MySQL的EXPLAIN輸出中和子查詢直接相關(guān)的字段主要在select_type列常見取值有select_type 取值含義常見場景PRIMARY最外層查詢所有SQL都有SUBQUERY非相關(guān)子查詢會被物化WHERE中獨立的IN子查詢DEPENDENT SUBQUERY相關(guān)子查詢每行執(zhí)行一次EXISTS、相關(guān)標(biāo)量子查詢MATERIALIZED子查詢被物化為臨時表IN子查詢被優(yōu)化為物化DERIVEDFROM子句中的派生表派生表查詢UNIONUNION語句中的子查詢聯(lián)合查詢看到DEPENDENT SUBQUERY就要警覺它意味著內(nèi)層查詢會依賴外層行數(shù)據(jù)逐行執(zhí)行。DEPENDENT并不是優(yōu)化器給出的“相關(guān)性描述”而是執(zhí)行方式的聲明對應(yīng)的是每行重復(fù)執(zhí)行的嵌套循環(huán)策略。另一種值得留意的是MATERIALIZED它說明子查詢結(jié)果被提前物化成了一張內(nèi)部臨時表。物化本身不是壞事它避免了重復(fù)執(zhí)行但如果物化出來的臨時表很大且沒有合適的索引后續(xù)Join時可能出現(xiàn)大表驅(qū)動小表的尷尬這個時候需要檢查臨時表的訪問路徑。3.2 半連接與物化MySQL如何悄悄改寫你的SQLMySQL優(yōu)化器中有一個非常重要的概念叫半連接Semi-join專門處理IN、EXISTS、ANY等形式的子查詢。半連接的含義是不關(guān)心子查詢匹配了多少行只關(guān)心“是否存在至少一行匹配”。優(yōu)化器會把這類子查詢改寫為半連接執(zhí)行計劃可選的策略主要包括FirstMatch對外層每一行只要在內(nèi)層找到第一條匹配就停止掃描。這是最樸素也最常用的策略等價于EXISTS的短路邏輯。DuplicateWeedout把內(nèi)層匹配結(jié)果去重后再做普通連接。適用于子查詢內(nèi)部本身會產(chǎn)生重復(fù)行的場景。LooseScan利用內(nèi)層表的索引對每個分組只取第一條匹配值避免重復(fù)掃描。Materialization先把子查詢結(jié)果物化成臨時表在臨時表上建索引再做連接。這些策略不是DBA手動指定的是優(yōu)化器基于統(tǒng)計信息自動選擇的。但在某些情況下優(yōu)化器會失明。比如內(nèi)外層表的數(shù)據(jù)分布嚴重傾斜、統(tǒng)計信息過期、或者WHERE條件里寫了函數(shù)導(dǎo)致索引失效優(yōu)化器就可能選錯策略。這時候可以手動改寫成JOIN形式來繞過錯誤執(zhí)行計劃也可以調(diào)整optimizer_switch中的半連接開關(guān)。我在故障排查時見過最典型的案例一個IN子查詢被優(yōu)化器選成了FirstMatch但內(nèi)層表過濾后選擇性太差導(dǎo)致外層每一行都要掃描大量內(nèi)層記錄。把optimizer_switch里的firstmatch臨時關(guān)閉強制走物化查詢時間從3秒降到0.1秒。3.3 子查詢改JOIN的黃金法則很多優(yōu)化文章提倡“子查詢一律改JOIN”這個說法過于絕對。JOIN改寫后由于普通JOIN會產(chǎn)生重復(fù)行往往還要額外加DISTINCT去重反而可能引入新的性能問題。我的黃金法則是能保持語義不變的前提下才改JOIN。IN子查詢改JOIN一般要加DISTINCTEXISTS改成JOIN則不需要去重但要注意多表連接會放大行數(shù)。優(yōu)先檢查索引而不是優(yōu)先改寫法。子查詢慢很多情況下不是子查詢本身的問題而是缺少合適的索引。相關(guān)子查詢優(yōu)先考慮改寫為派生表或EXISTS而不是盲目改JOIN因為相關(guān)子查詢意味著N1模式改成JOIN可能直接用哈希連接或索引嵌套循環(huán)解決。聚合類子查詢改成JOINGROUP BY往往能利用索引和優(yōu)化器的排序優(yōu)化。用一句話總結(jié)子查詢和JOIN只是同一邏輯的兩種不同表達最終執(zhí)行性能取決于優(yōu)化器能不能把SQL翻譯成高效的執(zhí)行計劃。我們的目標(biāo)是給優(yōu)化器更多選擇而不是替代它做決策。4. 實戰(zhàn)案例從慢查詢到秒回的完整優(yōu)化4.1 場景構(gòu)建與初始SQL為了講清楚我構(gòu)造一個貼近真實業(yè)務(wù)的模擬場景。某電商平臺有兩張表orders訂單表約200萬行order_id、user_id、amount、status、order_timeusers用戶表約50萬行user_id、user_name、user_level業(yè)務(wù)需求是找出所有“黃金會員”中最近30天內(nèi)有下單但訂單金額低于100元的用戶。新手第一版SQL通常寫成相關(guān)子查詢SELECT u.user_id, u.user_name FROM users u WHERE u.user_level gold AND EXISTS ( SELECT 1 FROM orders o WHERE o.user_id u.user_id AND o.amount 100 AND o.order_time NOW() - INTERVAL 30 DAY );單獨看這條SQL的執(zhí)行計劃還行但如果users表中g(shù)old會員有20萬EXISTS子查詢每行執(zhí)行一次orders表上又沒有(user_id, amount, order_time)的聯(lián)合索引后果就是20萬次全表掃描耗時輕松超過10秒。4.2 逐步拆解與改寫第一步我不急著改SQL先檢查索引。orders表上原來只有主鍵和user_id單列索引user_id過濾后還要回表判斷amount和order_time這是一個典型的索引覆蓋不足問題。我先加上聯(lián)合索引ALTER TABLE orders ADD INDEX idx_user_amount_time (user_id, amount, order_time);加完索引后再跑一遍EXISTS版本性能提升已經(jīng)很明顯因為內(nèi)層查詢可以直接走索引覆蓋掃描。第二步考慮把相關(guān)子查詢改成半連接JOIN。由于EXISTS只關(guān)心存在性JOIN時不會產(chǎn)生重復(fù)行無需額外DISTINCTSELECT DISTINCT u.user_id, u.user_name FROM users u JOIN orders o ON o.user_id u.user_id WHERE u.user_level gold AND o.amount 100 AND o.order_time NOW() - INTERVAL 30 DAY;注意這里我加了DISTINCT原因是理論上一個用戶可能有多條符合條件的訂單不加DISTINCT用戶會重復(fù)出現(xiàn)。雖然在這個業(yè)務(wù)場景下一對多的可能性不小所以保留DISTINCT更穩(wěn)妥。第三步進一步考慮能不能減少外層掃描的行數(shù)。如果users表上user_level沒有索引那每次都要全表掃描50萬行去過濾gold會員這也是一個優(yōu)化點ALTER TABLE users ADD INDEX idx_level (user_level);4.3 優(yōu)化效果對比我在模擬環(huán)境實測了三組數(shù)據(jù)均為本地測試數(shù)據(jù)結(jié)果供參考優(yōu)化階段執(zhí)行耗時掃描行數(shù)備注原始EXISTS無聯(lián)合索引8.6秒訂單表多次全表掃描相關(guān)子查詢20萬次全表掃描加聯(lián)合索引后的EXISTS0.45秒訂單表索引范圍掃描索引覆蓋生效改JOIN并保留DISTINCT0.32秒兩張表均走索引優(yōu)化器選擇半連接策略最終版本再加大緩沖池0.28秒基本命中緩存冷啟動場景接近這個案例的核心結(jié)論是第一步永遠是索引第二步才是SQL改寫。很多開發(fā)同學(xué)一上來就改寫SQL結(jié)果索引沒建換什么寫法都白搭。5. 常見問題與避坑實錄5.1 子查詢的NULL陷阱前面提過NOT IN遇到NULL的問題這里再補兩個容易被坑的場景。第一個是NOT IN子查詢中包含NULL?？催@個例子SELECT user_name FROM users WHERE user_id NOT IN (1, 2, NULL);直覺上應(yīng)該返回除了1、2之外的所有用戶實際結(jié)果卻是一個空集。原因user_id NULL的結(jié)果是UNKNOWNNOT IN等價于user_id 1 AND user_id 2 AND user_id NULL只要有一個AND條件是UNKNOWN整條過濾條件就是UNKNOWN行不會被選中。第二個是聚合函數(shù)內(nèi)部的NULL。COUNT(col)會跳過NULLAVG(col)也會忽略NULL參與計算。如果你在子查詢里聚合而目標(biāo)列本身有NULL值統(tǒng)計結(jié)果會和預(yù)期不一致。常見解決方式是提前用WHERE col IS NOT NULL過濾或者在聚合函數(shù)里顯式處理。5.2 相關(guān)子查詢的隱式性能殺手在開發(fā)環(huán)境測試相關(guān)子查詢數(shù)據(jù)量只有幾千行時效果很好一上線到生產(chǎn)環(huán)境幾百萬行就瞬間卡死。這類問題最隱蔽因為單看SQL完全找不出問題。我的排查習(xí)慣是遇到DEPENDENT SUBQUERY第一反應(yīng)是確認外層驅(qū)動表的行數(shù)。如果外層只有幾十行相關(guān)子查詢完全沒問題如果外層幾十萬行每條內(nèi)層查詢哪怕只有0.01秒總耗時也要幾十分鐘。優(yōu)化手段按優(yōu)先級排序給內(nèi)層子查詢的關(guān)聯(lián)字段加索引把相關(guān)子查詢改寫成非相關(guān)的IN子查詢改寫成JOIN交優(yōu)化器決定連接順序如果邏輯復(fù)雜拆成多條SQL在應(yīng)用層做內(nèi)存關(guān)聯(lián)。我個人實際經(jīng)驗是可讀性優(yōu)先保留子查詢性能敏感路徑一律改JOIN或拆SQL。生產(chǎn)環(huán)境穩(wěn)定性比代碼優(yōu)雅重要得多。5.3 子查詢與索引失效的邊界子查詢中經(jīng)常包含函數(shù)或隱式類型轉(zhuǎn)換這會讓索引失效。比如WHERE DATE(order_time) 2024-01-01即使order_time上有索引因為套了DATE函數(shù)MySQL無法使用索引范圍掃描只能全表掃描。更隱蔽的是隱式類型轉(zhuǎn)換。如果user_id是VARCHAR類型而子查詢傳入的是INTMySQL會把字符串列轉(zhuǎn)成數(shù)字再比較索引也可能失效。排查這類問題可以直接用EXPLAIN看type列如果從ref降級成ALL優(yōu)先考慮函數(shù)或類型轉(zhuǎn)換的影響。常見的保留索引寫法-- 不推薦對索引列使用函數(shù) WHERE DATE(order_time) 2024-01-01 -- 推薦使用范圍條件 WHERE order_time 2024-01-01 AND order_time 2024-01-025.4 子查詢的常見執(zhí)行計劃速查給一張速查表方便日常排查執(zhí)行計劃特征可能原因優(yōu)先處理方式DEPENDENT SUBQUERY 全表掃描相關(guān)列缺索引加索引或改JOINMATERIALIZED 大臨時表子查詢結(jié)果集過大檢查子查詢過濾條件考慮改EXISTSSUBQUERY DERIVEDFROM子查詢?nèi)頀呙杞o派生表關(guān)聯(lián)字段加索引Impossible WHERE子查詢條件恒為假檢查NULL和常量條件Using temporary / Using filesort排序和去重過大優(yōu)化索引減少掃描行數(shù)這套速查表是我自己排查慢查詢時經(jīng)常對照的清單遇到?jīng)]見過的執(zhí)行計劃形態(tài)先按表格分類再順著方向排查效率會高很多。6. 幾個值得記住的子查詢擴展用法6.1 子查詢與窗口函數(shù)配合MySQL 8.0開始支持窗口函數(shù)子查詢和它的配合可以解決很多“取每組TopN”的問題。以前實現(xiàn)“每個部門工資最高的前兩人”需要用相關(guān)子查詢或者復(fù)雜的自連接現(xiàn)在可以這樣寫SELECT dept_id, emp_name, salary FROM ( SELECT dept_id, emp_name, salary, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn FROM employee ) tmp WHERE rn 2;這里的派生表子查詢把窗口函數(shù)的計算結(jié)果當(dāng)作一列外層再基于rn過濾邏輯簡潔清晰性能也遠好于同邏輯的相關(guān)子查詢版本。6.2 子查詢直接驅(qū)動UPDATE和DELETE子查詢還可以用在UPDATE和DELETE中實現(xiàn)精確的批量操作-- 批量刪除“近一年未下單”的用戶 DELETE FROM users WHERE user_id IN ( SELECT user_id FROM ( SELECT u.user_id FROM users u LEFT JOIN orders o ON o.user_id u.user_id WHERE o.order_id IS NULL AND u.register_time NOW() - INTERVAL 1 YEAR ) t );這里需要特別注意的是MySQL不允許直接在同一張表上同時進行SELECT和DELETE所以要包一層派生表來繞開限制。這種寫法有坑如果子查詢結(jié)果非常大DELETE操作會長時間持有行鎖建議分批刪除DELETE FROM users WHERE user_id IN ( SELECT user_id FROM ( SELECT u.user_id FROM users u LEFT JOIN orders o ON o.user_id u.user_id WHERE o.order_id IS NULL AND u.register_time NOW() - INTERVAL 1 YEAR LIMIT 5000 ) t );一批刪除5000行循環(huán)執(zhí)行避免長時間鎖表。我處理過不止一次因為一次性DELETE數(shù)據(jù)量過大導(dǎo)致主從延遲飆升的事故都是靠分批刪除解決的。6.3 子查詢優(yōu)化器的版本差異最后提一個版本相關(guān)的問題。不同MySQL版本對子查詢的處理差異很大如果要做版本升級一定要重新壓測子查詢相關(guān)SQL。以我的經(jīng)歷來說5.6到5.7的升級中IN子查詢的執(zhí)行計劃從單純的SUBQUERY變成了支持Materialization和Semi-join很多原本慢的查詢?nèi)庋劭梢姷刈兛炝?。?.7到8.0的升級中派生表的Merge和Lateral能力增強但同時也可能因為哈希連接、窗口函數(shù)等新特性的引入導(dǎo)致一些子查詢的代價估算發(fā)生改變。升級前跑一遍全量慢查詢回歸是我見過最有效的防坑手段。不要光看兼容性文檔要用真實數(shù)據(jù)和真實SQL測試執(zhí)行計劃的變化。寫在最后的實操心得做了這么多年數(shù)據(jù)庫優(yōu)化我的體會是子查詢不是洪水猛獸真正危險的是對執(zhí)行機制的無知。該用子查詢的時候就大膽用但寫完務(wù)必做三件事第一跑EXPLAIN確認select_type和執(zhí)行策略第二檢查相關(guān)子查詢的嵌套層級盡量控制在兩層以內(nèi)第三對該子查詢涉及的所有關(guān)聯(lián)列確認有索引。最后再分享一個小技巧如果查詢中用了IN子查詢且懷疑優(yōu)化器選錯策略可以在SQL前面加EXPLAIN FORMATJSON輸出里會有optimized_away、semijoin_strategy、materialized_from_subquery這些詳細信息它們比單看EXPLAIN表格更直觀地展示優(yōu)化器到底做了什么決策。遇到玄學(xué)性能問題把JSON執(zhí)行計劃拉出來對照著看比盲目改SQL靠譜得多。