SELECT * FROM userWHERE id IN (SELECT user_id FROM order);

一、為什么子查詢慢？

1. 執行次數多（相關子查詢）

子查詢依賴外層查詢的值，導致每行都執行一次子查詢。如:

-- 例子：每行都查一次SELECT * FROM orders oWHERE o.customer_id IN (    SELECT c.id FROM customers c WHERE c.region = 'US');

2. 數據量大 + 無索引

• 子查詢返回大量數據，且沒有命中索引。

• MySQL 可能對子查詢結果做臨時表或文件排序，導致性能差。

二、如何定位和優化？

看執行計劃

EXPLAIN ANALYZE SELECT ...

• 看子查詢是否被物化為臨時表（Using temporary）。

• 看是否使用了索引（key 字段）。

• 看是否每行都執行子查詢（DEPENDENT SUBQUERY）。

EXPLAIN ANALYZE 是一種非常有用的工具，用于分析和優化 SQL 查詢的性能。它可以幫助你理解數據庫是如何執行查詢的，以及查詢的各個部分是如何影響性能的。

EXPLAIN 用于顯示數據庫如何執行查詢的詳細信息。它提供了一個執行計劃，顯示了查詢的各個步驟，包括表的掃描方式、連接方法、使用的索引等。EXPLAIN 不實際執行查詢，因此它不會返回查詢的結果，也不會對數據庫產生實際影響。它主要用于分析查詢的邏輯結構和優化方向。

ANALYZE用于收集數據庫表的統計信息。這些統計信息包括表的行數、列的分布、索引的使用情況等。數據庫優化器使用這些統計信息來生成更高效的查詢執行計劃。通常在數據庫表結構或數據發生較大變化后運行，以確保優化器能夠生成最佳的執行計劃。

EXPLAIN ANALYZE 是將 EXPLAIN 和 ANALYZE 結合起來的一個命令，它不僅顯示查詢的執行計劃，還會實際執行查詢，并提供實際的執行時間和資源使用情況。

怎么使用呢?

直接加在查詢的語句之前即可。如:

EXPLAIN ANALYZE SELECT * FROM orders oWHERE o.customer_id IN (    SELECT c.id FROM customers c WHERE c.region = 'US');

完成！

EXPLAIN ANALYZE 的輸出通常包括：

• 查詢計劃：

• 顯示查詢的邏輯結構，包括表的掃描方式、連接方法、使用的索引等。

• 例如，Seq Scan 表示順序掃描，Index Scan 表示索引掃描。

• 實際執行時間：

• 顯示每個步驟的實際執行時間，單位通常是毫秒。

• 例如，actual time=0.002..0.003 rows=1 loops=1 表示該步驟實際執行時間為 0.002 毫秒到 0.003 毫秒，返回了 1 行數據，循環了 1 次。

• 資源使用情況：

• 顯示查詢執行過程中使用的資源，如臨時表的使用、內存使用情況等。

說了這么多怎么查看為什么慢，那怎么解決呢？

優化策略可以用下面的方法：

1. 改成 JOIN

-- 原：子查詢SELECT * FROM orders oWHERE o.customer_id IN (    SELECT c.id FROM customers c WHERE c.region = 'US');
-- 改為 JOINSELECT o.*FROM orders oJOIN customers c ON o.customer_id = c.idWHERE c.region = 'US';

如果現有的解釋器有點舊，用原來的子查詢，則數據庫拿到 orders 的每一行，都要拿它的 customer_id 去子查詢里做“有沒有”的判斷。

• 如果優化器不夠聰明，它真的會對 orders 的每一行觸發一次子查詢，行數就是驅動表的行數。

• 即便優化器能做“半連接”轉換，也只是把“逐行問”變成“一次性問”，但這一步轉換本身不一定成功，也不一定徹底。

但是，如果用join，則數據庫只需要把兩個表按customer_id做一次集合匹配：

• 先掃 customers，把 region='US' 的行篩出來；

• 再拿這批 id 一次性去 orders 里做匹配。
  整個過程只掃兩遍表（或利用索引），沒有“逐行觸發”這回事。

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 (子查詢) 改寫成 EXISTS (相關子查詢) 之所以常常更快，根本原因在于 “半連接” 的執行方式變了：
前者容易退化成“先算完子查詢，再拿著結果集去主表一條條比對”；
而后者天然就是“逐行短路”，一旦找到匹配就立刻返回，不再繼續找，也不再把子查詢結果物化成臨時集合。

IN：數據庫必須先把子查詢里所有滿足 region='US' 的 id 收集齊全，再去和主表的 customer_id 做集合成員測試。
EXISTS：對主表的每一行，只需在子查詢里發現第一條滿足 c.id = o.customer_id 的記錄即可返回 true，后面的行不再掃描。

此外，IN 子查詢的結果在不少數據庫里會被做成臨時表（derived 表、hash 表、或排序后的數組）。
EXISTS 子查詢由于與主表行相關，優化器通常直接把它轉成嵌套循環或半連接，不再物化，內存和 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;

提前聚合，就是把“每一行都要重新算一次”的聚合計算，改成“先一次性把所有結果算完，再拿現成的值去匹配”。

原來的寫法運行是這樣的：

• 數據庫先掃 orders 表，拿到第一行 order；

• 然后針對這一行的 id，再去 payments 表里把所有 amount 求和；

• 再拿下一行 order，重復一次求和……

• orders 有多少行，payments 就被掃多少遍（或索引回表多少次）。
  這種“一行觸發一次聚合”的模式，行數放大、I/O 放大，自然慢。

優化聚合后：

• 先把payments全表掃一遍（或走索引），按order_id分組，把每個 order_id 對應的SUM(amount)算出來，生成一張只有order_id和 total_paid兩列的小結果集；

• 再把這張小結果集跟orders做連接；

• orders 有多少行，都只跟這張已經算好的小表做一次匹配，不再重復求和。

在標準 SQL 的執行模型里，LEFT JOIN 的左右兩邊會先被當成兩個獨立的輸入流，優化器決定誰先誰后、用哪種算法（嵌套循環、哈希連接、排序合并等）。概括起來：

1. 邏輯順序
   FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT
   我們看到 LEFT JOIN 發生在 FROM/JOIN 階段，此時左右兩邊都必須先準備好各自的結果，然后才能做連接。

2. 物理順序由優化器決定

   如果右表很小、左表很大，優化器可能先掃右表，把結果裝進內存（哈希表），再掃左表去匹配。

   如果右表很大、左表很小，也可能反過來先掃左表。

   也有可能兩邊同時掃（并行哈希連接）。

3. 子查詢提前聚合的那一步
   在上面的例子里，右表是一個派生表（子查詢），這個子查詢要先算完，它是被優化器當成一個整體“右表”。

閱讀原文：原文鏈接