SQL 查詢優化實戰：LEFT JOIN 轉 INNER JOIN、子查詢重寫與 Left Join 消除規則深度解析

前言

JOIN 查詢是 SQL 效能調校的高頻戰場。不當的 LEFT JOIN、過度的關聯表、冗餘的子查詢常導致執行計劃退化為全表掃描與臨時表。本文透過 4 個真實優化案例，示範 LEFT JOIN → INNER JOIN、子查詢 → 關聯衍生表/標量子查詢、Left Join 消除規則 等核心技巧，並附上 MySQL 內核優化邏輯原理解析。

案例一：8 表關聯、全 LEFT JOIN、多條件 → 6 表、混合 JOIN、精簡條件

優化前 (耗時 ~60s，Web 超時)

SELECT ...
FROM Resource_Resources A
LEFT JOIN Resource_Clients B ON A.ResourceId = B.ResourceId
LEFT JOIN Resource_Files C ON B.ClientId = C.ClientId
LEFT JOIN Resource_ClientsModels D ON B.ClientId = D.ClientId
LEFT JOIN Mobile_Models E ON D.ModelId = E.ModelId
LEFT JOIN Resource_Images F ON A.ResourceId = F.ResourceId
LEFT JOIN Resource_Tags G ON G.ResourceId = A.ResourceId
LEFT JOIN Website_Tags H ON G.TagId = H.TagId
LEFT JOIN Resource_Categories I ON A.CategoryId = I.CategoryId
WHERE A.Name LIKE ...
  AND C.Extend LIKE ...
  AND D.ModelId = ...
  AND H.Name LIKE ...
  AND I.Code LIKE ...
  AND E.Name LIKE ...

資料量：A 表 2萬、B > A、C > B、H 表極大。

優化後 (耗時 < 1s)

SELECT ...
FROM Resource_Resources A
INNER JOIN Resource_Clients B ON A.ResourceId = B.ResourceId
INNER JOIN Resource_Files C ON B.ClientId = C.ClientId
LEFT JOIN Resource_ClientsModels D ON B.ClientId = D.ResourceClientId
LEFT JOIN Resource_Tags G ON G.ResourceId = A.ResourceId
INNER JOIN Website_Tags H ON G.TagId = H.TagId
INNER JOIN Resource_Categories I ON A.CategoryId = I.CategoryId
WHERE A.Name LIKE ...
  AND H.Name LIKE ...

核心優化點

1. 語義轉換：WHERE 條件中引用右表欄位 (C.Extend, D.ModelId, H.Name, I.Code, E.Name) 且非 IS NULL 判斷，等價於 INNER JOIN。MySQL 優化器自動將此類 LEFT JOIN 轉為 INNER JOIN。
2. 刪除無效關聯：移除 Mobile_Models E、Resource_Images F (未在 SELECT/WHERE 使用)。
3. 精簡 WHERE 條件：僅保留高選擇性條件 (A.Name, H.Name)，低選擇性條件下推至應用層或捨棄。
4. 驅動表選擇：A 表 (2萬行) 作為驅動表，配合 INNER JOIN 逐步縮小結果集。

案例二：多個衍生表 LEFT JOIN → 標量子查詢

優化前：4 個衍生表 LEFT JOIN

SELECT M.clientid, M.CardFaceID,
       N.NormalBalance, D.DateWorth, T.TimesWorth, B.BookingWorth, B.BookingTimesManyBalance
FROM (SELECT clientid, CardFaceID FROM cimain) M
LEFT JOIN (SELECT clientid, SUM(...) AS NormalBalance FROM ccNormal GROUP BY clientid) N ON M.clientid = N.clientid
LEFT JOIN (SELECT clientid, SUM(...) AS DateWorth FROM ccDate GROUP BY clientid) D ON M.clientid = D.clientid
LEFT JOIN (SELECT clientid, SUM(...) AS TimesWorth FROM ccTimes GROUP BY clientid) T ON M.clientid = T.clientid
LEFT JOIN (SELECT clientid, SUM(...) AS BookingWorth, SUM(TimesBalance) AS BookingTimesManyBalance FROM ccBooking GROUP BY clientid) B ON M.clientid = B.clientid

問題：每個衍生表需物化暫存表，再進行 4 次 JOIN，臨時表 I/O 開銷極大。

優化後：標量子查詢 (單表掃描 + 索引查找)

SELECT M.clientid, M.CardFaceID,
       (SELECT SUM(CASE IFGive WHEN '是' THEN Balance*ItemZkl ELSE Balance END) FROM ccNormal WHERE clientid = M.clientid) AS NormalBalance,
       (SELECT SUM(ConsumeBalance*ItemZkl) FROM ccDate WHERE clientid = M.clientid) AS DateWorth,
       (SELECT SUM(AveragePrice*TimesBalance*ItemZKL) FROM ccTimes WHERE clientid = M.clientid) AS TimesWorth,
       (SELECT SUM(PriceDiscount*TimesBalance) FROM ccBooking WHERE clientid = M.clientid) AS BookingWorth,
       (SELECT SUM(TimesBalance) FROM ccBooking WHERE clientid = M.clientid) AS BookingTimesManyBalance
FROM cimain M

效果：cimain 為驅動表，每行透過 clientid 索引單點查找 4 張聚合表，無臨時表、無 JOIN、極低延遲。前提：4 張聚合表皆有 clientid 索引。

案例三：COUNT DISTINCT + 多 LEFT JOIN → 分別聚合再合併

優化前

SELECT COUNT(DISTINCT T1.A1) + COUNT(DISTINCT T2.B1) + COUNT(DISTINCT T3.C1)
FROM T1
LEFT JOIN T2 ON T1.A1 = T2.A1
LEFT JOIN T3 ON T1.A1 = T3.A1
LEFT JOIN T4 ON T3.C1 = T4.C1
GROUP BY T1.A2, T1.A3

優化分析

1. COUNT(DISTINCT T1.A1)：T1 為主表，LEFT JOIN 不影響主表行數，等價於 SELECT COUNT(DISTINCT A1) FROM T1 GROUP BY A2, A3。
2. COUNT(DISTINCT T2.B1)：T2 為從表，COUNT(DISTINCT) 自動忽略 NULL，等價於 SELECT COUNT(DISTINCT B1) FROM T1 INNER JOIN T2 ON T1.A1=T2.A1 GROUP BY T1.A2, T1.A3。
3. 同理 T3、T4 可轉為 INNER JOIN 鏈。

優化後：分別聚合再橫向合併

SELECT t1.cnt1 + t2.cnt2 + t3.cnt3
FROM (SELECT COUNT(DISTINCT A1) AS cnt1 FROM T1 GROUP BY A2, A3) t1
JOIN (SELECT COUNT(DISTINCT B1) AS cnt2 FROM T1 INNER JOIN T2 ON T1.A1=T2.A1 GROUP BY T1.A2, T1.A3) t2
JOIN (SELECT COUNT(DISTINCT C1) AS cnt3 FROM T1 INNER JOIN T3 ON T1.A1=T3.A1 INNER JOIN T4 ON T3.C1=T4.C1 GROUP BY T1.A2, T1.A3) t3;

優勢：避免大表 JOIN 後再聚合，改為小結果集 JOIN，記憶體與 CPU 開銷大幅下降。

案例四：Left Join 消除規則理論基礎 (MySQL 內核 simplify_joins)

核心定理

對於查詢：

SELECT * FROM t1 LEFT JOIN t2 ON t1.c1 = t2.d1 WHERE condition;

若 WHERE 條件中至少存在一個滿足「非空條件」的條件 K，且 K 引用 t2 的任意欄位，則該 LEFT JOIN 可等價轉換為 INNER JOIN。

非空條件定義

條件類型 不為 t2.column IS NULL。以下皆為非空條件：

• t2.d1 = 5
• t2.d1 > 10
• t2.d1 IN (1,2,3)
• t2.d1 = t1.c2
• t2.d1 IS NOT NULL
• t2.d1 LIKE 'abc%'
• t2.d1 BETWEEN 1 AND 100

多層 LEFT JOIN 遞歸消除

SELECT * FROM t1
LEFT JOIN t2 ON t1.c1 = t2.d1
LEFT JOIN t3 ON t2.d2 = t3.e1
WHERE t3.e1 = 1;  -- t3.e1=1 為非空條件，引用 t3 欄位

-- 步驟 1：t3 層消除
=> SELECT * FROM t1 LEFT JOIN t2 ON t1.c1=t2.d1 INNER JOIN t3 ON t2.d2=t3.e1 WHERE t3.e1=1

-- 步驟 2：t2 層消除 (內層已成 INNER JOIN，t2.d2=t3.e1 為等值條件，滿足非空條件)
=> SELECT * FROM t1 INNER JOIN t2 ON t1.c1=t2.d1 INNER JOIN t3 ON t2.d2=t3.e1 WHERE t3.e1=1

-- 等價於標準 INNER JOIN 鏈
=> SELECT * FROM t1, t2, t3 WHERE t1.c1=t2.d1 AND t2.d2=t3.e1 AND t3.e1=1

MySQL 內核實現 (sql_select.cc: simplify_joins)

• 遍歷 JOIN 表依賴圖，從葉子節點向根收集「非空條件」。
• 標記可轉為 INNER JOIN 的表。
• 重寫 JOIN 類型，調整存取路徑 (驅動表可能改變)。
• 應用開發者若能手動改寫，可避免優化器誤判或統計資訊不足導致的次優計劃。

Sybase 索引優化補遺 (原文附錄)

原文第 5 節為 Sybase SQL Server 索引策略，核心觀點與 MySQL 通用：

• 聚簇索引：資料物理順序 = 索引順序，適合範圍查詢、GROUP BY、ORDER BY、高頻 JOIN 欄位。避免單調遞增鍵 (身份證/自增 ID) 造成熱點頁爭用。
• 非聚簇索引：葉子節點存鍵值 + Row Pointer (Heap) 或 Cluster Key。適合高選擇性點查、覆蓋查詢。維護成本高，慎用。
• 覆蓋索引：索引包含查詢所有欄位，避免回表/書籤查找。空間換時間，核心查詢值得投資。
• 索引維護：DBCC SHOWCONTIG 檢查碎片、UPDATE STATISTICS 刷新統計、重建索引消除分裂。

總結：JOIN 優化決策樹

1. 檢查 WHERE 條件：引用右表欄位且非 IS NULL → 改 INNER JOIN。
2. 檢查業務語義：右表必須存在 (如訂單必有用戶) → INNER JOIN。
3. 檢查衍生表：單點查找聚合結果 → 改標量子查詢 (需索引支援)。
4. 檢查聚合邏輯：COUNT(DISTINCT) + LEFT JOIN → 拆解為獨立聚合再合併。
5. 驗證索引：所有 JOIN ON、WHERE、GROUP BY、ORDER BY 欄位皆有適當索引。
6. EXPLAIN 驗收：type=ref/eq_ref、rows 合理、Extra 無 Using temporary/filesort。