您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

MySQL FROM 子查詢與 EXISTS/NOT EXISTS 實戰指南：衍生表、半連接與效能優化

編輯：MySQL 資料庫相關知識

前言

FROM 子查詢（衍生表）與 EXISTS/NOT EXISTS 是 SQL 進階查詢的兩大核心工具。前者將子查詢結果作為臨時表參與連接，後者以「存在性判斷」替代資料比對，常能大幅簡化邏輯並提升效能。本文詳細解析語法、執行機制、優化技巧與實戰案例。

一、FROM 子查詢（衍生表）

1.1 語法規則

SELECT ... FROM (subquery) AS alias ...

**必須指定別名** (AS alias)，否則語法錯誤。
子查詢可回傳標量、單列、多列、多行。
**不支援關聯引用** 外層欄位 (MySQL 8.0.14+ 可用 LATERAL 突破)。

1.2 執行機制

先執行子查詢，結果寫入**內部臨時表** (記憶體或磁碟)。
外層查詢掃描臨時表，再與其他表連接或過濾。
MySQL 5.6+ 支援 **Derived Merge** 優化：若衍生表無聚合、無 DISTINCT、無 LIMIT，優化器會嘗試將其合併到外層查詢，避免物化。

1.3 範例：二次聚合

-- 每位作者的平均文章長度，再篩選平均長度 > 1000 的作者
SELECT author_id, AVG(article_len) AS avg_len
FROM (
    SELECT uid AS author_id, CHAR_LENGTH(content) AS article_len
    FROM article
) AS t
GROUP BY author_id
HAVING avg_len > 1000;

1.4 避免衍生表物化的技巧

使用 WITH cte AS (...) SELECT ... (MySQL 8.0 CTE)，優化器更易合併。
將條件下推：WHERE 條件寫在子查詢內而非外層。
必要時加 /*+ MAX_EXECUTION_TIME(1000) */ Hint 限制執行時間。

二、EXISTS / NOT EXISTS

2.1 語義與執行流程

SELECT * FROM outer_table o
WHERE EXISTS (
    SELECT 1 FROM inner_table i WHERE o.id = i.outer_id
);

**執行邏輯**：對外層每行，執行子查詢；**只要找到第一行匹配即返回 TRUE 停止掃描** (Short-circuit)。
SELECT 1、SELECT *、SELECT id 效能相同，優化器忽略選擇清單。

2.2 EXISTS vs IN

場景	推薦	原因
子查詢結果集小、外層大	`IN`	子查詢物化成臨時表，外層索引查找
子查詢結果集大、外層小	`EXISTS`	外層驅動，子查詢索引查找並短路
子查詢含 `NULL`	`EXISTS`	`IN` 遇 NULL 判斷為 UNKNOWN，導致不匹配
需檢查「不存在」	`NOT EXISTS`	`NOT IN` 同樣受 NULL 影響

2.3 實戰：找出從未下單的用戶

-- NOT EXISTS (推薦)
SELECT u.* FROM user u
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.uid = u.uid
);

-- LEFT JOIN IS NULL (等效，舊版本常用)
SELECT u.* FROM user u
LEFT JOIN orders o ON u.uid = o.uid
WHERE o.uid IS NULL;

2.4 進階：EXISTS 實現「任意一條符合」邏輯

-- 找出至少有一篇文章瀏覽量 > 10000 的作者
SELECT u.* FROM user u
WHERE EXISTS (
    SELECT 1 FROM article a
    WHERE a.uid = u.uid AND a.views > 10000
);

三、半連接優化

MySQL 5.6.5+ 引入 **Semi-Join** 優化策略，自動將符合條件的 IN/EXISTS 子查詢轉換為連接，避免物化臨時表。
optimizer_switch 控制：semijoin=on (預設開啟)。
適用子查詢特徵：
- 無聚合、無 DISTINCT、無 LIMIT
- 不包含 UNION
- 外層查詢無 GROUP BY/HAVING

四、效能對比實測

# 測試資料：user 10萬行，orders 50萬行，uid 有索引
# 查詢：找出有訂單的用戶

# 方案 A: IN
SELECT * FROM user WHERE uid IN (SELECT uid FROM orders);

# 方案 B: EXISTS
SELECT * FROM user u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.uid = u.uid);

# 方案 C: INNER JOIN (去重)
SELECT DISTINCT u.* FROM user u INNER JOIN orders o ON u.uid = o.uid;

# 方案 D: JOIN + GROUP BY
SELECT u.* FROM user u INNER JOIN orders o ON u.uid = o.uid GROUP BY u.uid;

典型結果 (MySQL 8.0)：
方案 B (EXISTS) ≈ 方案 C (JOIN) < 方案 A (IN) < 方案 D (GROUP BY)。
**建議**：優先用 EXISTS 或 JOIN，配合 EXPLAIN 確認 type=ref、Extra=Using index。

五、常見陷阱與修正

陷阱	症狀	修正
`NOT IN` 子查詢含 NULL	回傳 0 行	改用 `NOT EXISTS` 或 `LEFT JOIN ... IS NULL`
衍生表物化導致磁碟 I/O 飆升	`EXPLAIN` 顯示 `derived`、`Using temporary`	改用 CTE、下推條件、或增加索引滿足 Derived Merge
`EXISTS` 子查詢無索引	每行外層全掃內表	在內表關聯欄位建索引

結語： FROM 子查詢適合「先聚合再關聯」的二次運算；EXISTS/NOT EXISTS 適合「存在性檢查」，天然短路特性配合索引極為高效。MySQL 8.0 CTE 與 Derived Merge 讓衍生表不再是效能殺手。實戰中請以 EXPLAIN 為準，選擇執行計畫最優的寫法。