當(dāng)你寫(xiě)下這段SQL的時(shí)候：SELECT * FROM userWHERE id IN (SELECT user_id FROM order);
會(huì)發(fā)現(xiàn)瘋狂掃描，運(yùn)行速度很慢，很頭大。我們常常在工作中/面試中會(huì)遇到這樣的問(wèn)題，這里做點(diǎn)總結(jié)。一、為什么子查詢慢？
1. 執(zhí)行次數(shù)多（相關(guān)子查詢）
子查詢依賴外層查詢的值，導(dǎo)致每行都執(zhí)行一次子查詢。如:
SELECT * FROM orders oWHERE o.customer_id IN ( SELECT c.id FROM customers c WHERE c.region = 'US');
2. 數(shù)據(jù)量大 + 無(wú)索引
二、如何定位和優(yōu)化？
看執(zhí)行計(jì)劃
EXPLAIN ANALYZE SELECT ...
如果不理解EXPLAIN ANALYZE，這里可以簡(jiǎn)單解釋一下:
EXPLAIN ANALYZE 是一種非常有用的工具，用于分析和優(yōu)化 SQL 查詢的性能。它可以幫助你理解數(shù)據(jù)庫(kù)是如何執(zhí)行查詢的，以及查詢的各個(gè)部分是如何影響性能的。
EXPLAIN 用于顯示數(shù)據(jù)庫(kù)如何執(zhí)行查詢的詳細(xì)信息。它提供了一個(gè)執(zhí)行計(jì)劃，顯示了查詢的各個(gè)步驟，包括表的掃描方式、連接方法、使用的索引等。EXPLAIN 不實(shí)際執(zhí)行查詢，因此它不會(huì)返回查詢的結(jié)果，也不會(huì)對(duì)數(shù)據(jù)庫(kù)產(chǎn)生實(shí)際影響。它主要用于分析查詢的邏輯結(jié)構(gòu)和優(yōu)化方向。
ANALYZE用于收集數(shù)據(jù)庫(kù)表的統(tǒng)計(jì)信息。這些統(tǒng)計(jì)信息包括表的行數(shù)、列的分布、索引的使用情況等。數(shù)據(jù)庫(kù)優(yōu)化器使用這些統(tǒng)計(jì)信息來(lái)生成更高效的查詢執(zhí)行計(jì)劃。通常在數(shù)據(jù)庫(kù)表結(jié)構(gòu)或數(shù)據(jù)發(fā)生較大變化后運(yùn)行，以確保優(yōu)化器能夠生成最佳的執(zhí)行計(jì)劃。
EXPLAIN ANALYZE 是將 EXPLAIN 和 ANALYZE 結(jié)合起來(lái)的一個(gè)命令，它不僅顯示查詢的執(zhí)行計(jì)劃，還會(huì)實(shí)際執(zhí)行查詢，并提供實(shí)際的執(zhí)行時(shí)間和資源使用情況。
怎么使用呢?
直接加在查詢的語(yǔ)句之前即可。如:
EXPLAIN ANALYZE SELECT * FROM orders oWHERE o.customer_id IN ( SELECT c.id FROM customers c WHERE c.region = 'US');
完成！
EXPLAIN ANALYZE 的輸出通常包括：
查詢計(jì)劃：
實(shí)際執(zhí)行時(shí)間：
資源使用情況：
說(shuō)了這么多怎么查看為什么慢，那怎么解決呢？
優(yōu)化策略可以用下面的方法：
1. 改成 JOIN
SELECT * FROM orders oWHERE o.customer_id IN ( SELECT c.id FROM customers c WHERE c.region = 'US');
SELECT o.*FROM orders oJOIN customers c ON o.customer_id = c.idWHERE c.region = 'US';
如果現(xiàn)有的解釋器有點(diǎn)舊，用原來(lái)的子查詢，則數(shù)據(jù)庫(kù)拿到 orders 的每一行，都要拿它的 customer_id 去子查詢里做“有沒(méi)有”的判斷。
但是，如果用join，則數(shù)據(jù)庫(kù)只需要把兩個(gè)表按customer_id做一次集合匹配：
2. 使用 EXISTS 替代 IN
... WHERE o.customer_id IN (SELECT c.id ...)
... WHERE EXISTS ( SELECT 1 FROM customers c WHERE c.id = o.customer_id AND c.region = 'US');
把 IN (子查詢) 改寫(xiě)成 EXISTS (相關(guān)子查詢) 之所以常常更快，根本原因在于 “半連接” 的執(zhí)行方式變了：
前者容易退化成“先算完子查詢，再拿著結(jié)果集去主表一條條比對(duì)”；
而后者天然就是“逐行短路”，一旦找到匹配就立刻返回，不再繼續(xù)找，也不再把子查詢結(jié)果物化成臨時(shí)集合。
IN：數(shù)據(jù)庫(kù)必須先把子查詢里所有滿足 region='US' 的 id 收集齊全，再去和主表的 customer_id 做集合成員測(cè)試。
EXISTS：對(duì)主表的每一行，只需在子查詢里發(fā)現(xiàn)第一條滿足 c.id = o.customer_id 的記錄即可返回 true，后面的行不再掃描。
此外，IN 子查詢的結(jié)果在不少數(shù)據(jù)庫(kù)里會(huì)被做成臨時(shí)表（derived 表、hash 表、或排序后的數(shù)組）。
EXISTS 子查詢由于與主表行相關(guān)，優(yōu)化器通常直接把它轉(zhuǎn)成嵌套循環(huán)或半連接，不再物化，內(nèi)存和 CPU 都省一步。
3. 提前聚合
SELECT o.*, ( SELECT SUM(amount) FROM payments p WHERE p.order_id = o.id) AS total_paidFROM orders o;
SELECT o.*, p.total_paidFROM orders oLEFT JOIN ( SELECT order_id, SUM(amount) AS total_paid FROM payments GROUP BY order_id) p ON p.order_id = o.id;
提前聚合，就是把“每一行都要重新算一次”的聚合計(jì)算，改成“先一次性把所有結(jié)果算完，再拿現(xiàn)成的值去匹配”。
原來(lái)的寫(xiě)法運(yùn)行是這樣的：
數(shù)據(jù)庫(kù)先掃 orders 表，拿到第一行 order；
然后針對(duì)這一行的 id，再去 payments 表里把所有 amount 求和；
再拿下一行 order，重復(fù)一次求和……
orders 有多少行，payments 就被掃多少遍（或索引回表多少次）。
這種“一行觸發(fā)一次聚合”的模式，行數(shù)放大、I/O 放大，自然慢。
優(yōu)化聚合后：
在標(biāo)準(zhǔn) SQL 的執(zhí)行模型里，LEFT JOIN 的左右兩邊會(huì)先被當(dāng)成兩個(gè)獨(dú)立的輸入流，優(yōu)化器決定誰(shuí)先誰(shuí)后、用哪種算法（嵌套循環(huán)、哈希連接、排序合并等）。概括起來(lái)：
邏輯順序
FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT
我們看到 LEFT JOIN 發(fā)生在 FROM/JOIN 階段，此時(shí)左右兩邊都必須先準(zhǔn)備好各自的結(jié)果，然后才能做連接。
物理順序由優(yōu)化器決定
如果右表很小、左表很大，優(yōu)化器可能先掃右表，把結(jié)果裝進(jìn)內(nèi)存（哈希表），再掃左表去匹配。
如果右表很大、左表很小，也可能反過(guò)來(lái)先掃左表。
也有可能兩邊同時(shí)掃（并行哈希連接）。
子查詢提前聚合的那一步
在上面的例子里，右表是一個(gè)派生表（子查詢），這個(gè)子查詢要先算完，它是被優(yōu)化器當(dāng)成一個(gè)整體“右表”。
閱讀原文：原文鏈接
該文章在 2025/8/26 13:08:04 編輯過(guò)
400 186 1886
