MySQL 子查詢基礎教學：表子查詢與關聯子查詢語法、原理與優化實戰

前言

子查詢是 SQL 表達複雜邏輯的利器。MySQL 支援標準 SQL 子查詢並有自有優化規則。本文系統講解**表子查詢**、**關聯子查詢**的語法、執行流程、效能特徵，並示範如何改寫為 JOIN 或 EXISTS 獲得更佳效能。

一、表子查詢

1.1 定義

子查詢回傳 **N 行 M 列** 的結果集，作為外層查詢的比較對象或來源表。

1.2 語法範例

-- 找出 article 與 blog 完全相同的行 (標題、內容、作者均相同)
SELECT * FROM article
WHERE (title, content, uid) IN (
    SELECT title, content, uid FROM blog
);

等價改寫（效能更好）：

SELECT a.* FROM article a
INNER JOIN blog b
  ON a.title = b.title
 AND a.content = b.content
 AND a.uid = b.uid;

1.3 適用場景

• IN / NOT IN 子查詢回傳多行單列或多列元組。
• EXISTS / NOT EXISTS 子查詢回傳任意列 (只判斷是否存在)。
• FROM 子句中作為衍生表。

二、關聯子查詢

2.1 定義

子查詢**引用外層查詢的欄位**，外層每行資料都會觸發一次子查詢執行。

2.2 執行流程

1. 外層查詢取得一行資料。
2. 將該行的相關欄位值傳入子查詢作為參數。
3. 執行子查詢，回傳 TRUE/FALSE 或結果集。
4. 外層根據結果決定保留/捨棄該行。
5. 重複直到外層所有行處理完畢。

2.3 範例：找出有發文的使用者

SELECT * FROM article a
WHERE uid IN (
    SELECT uid FROM user u WHERE a.uid = u.uid
);

等價改寫（推薦）：

-- INNER JOIN (若 user.uid 唯一)
SELECT a.* FROM article a
INNER JOIN user u ON a.uid = u.uid;

-- EXISTS (語意更清晰，優化器常自動轉換)
SELECT a.* FROM article a
WHERE EXISTS (
    SELECT 1 FROM user u WHERE a.uid = u.uid
);

2.4 效能陷阱

• 外層 10 萬行 → 子查詢執行 10 萬次 → **全表掃描或索引查找 N 次**，極慢。
• MySQL 8.0+ 引入 **子查詢到衍生表轉換** 與 **半連接優化**，部分關聯子查詢會自動改寫，但不保證所有場景。

三、優化策略總表

四、實戰：分頁查詢優化

錯誤寫法：

SELECT * FROM article ORDER BY id LIMIT 100000, 20;  -- 掃描 10 萬行再丟棄

優化：延遲關聯

SELECT a.* FROM article a
INNER JOIN (
    SELECT id FROM article ORDER BY id LIMIT 100000, 20
) b USING (id);

原理：內層只掃描主鍵索引 (Covering Index)，外層回表僅 20 行。

五、MySQL 8.0 新特性：CTE 與側寫連接

WITH ranked AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY uid ORDER BY created_at DESC) AS rn
    FROM article
)
SELECT * FROM ranked WHERE rn = 1;  -- 每位使用者最新一篇文章

LATERAL JOIN (MySQL 8.0.14+) 允許子查詢引用前面表的欄位，替代關聯子查詢：

SELECT u.*, a.title
FROM user u
LEFT JOIN LATERAL (
    SELECT title FROM article WHERE uid = u.uid ORDER BY created_at DESC LIMIT 1
) a ON TRUE;

結語： 子查詢語法靈活但易寫出低效 SQL。掌握「關聯子查詢 → JOIN/EXISTS」、「分頁 → 延遲關聯」、「複雜邏輯 → CTE」三大改寫模式，配合 EXPLAIN 驗證 type=ref/eq_ref、Extra=Using index，即可寫出高效能 SQL。