SQL 外部連接效能陷阱：WHERE 條件位置錯誤導致查詢慢 50 倍

案例背景

專案經理回報網頁載入緩慢，開發已加入 Memcached 20 分鐘快取仍無改善。經 SQL 分析，發現單一查詢耗時 5 秒以上，為效能瓶頸。

問題 SQL（慢）

SELECT *
FROM (
    SELECT u.NAME AS UniversityName,
           u.id AS UniversityId,
           COUNT(a.SIGNUPNUMBER) AS playercnt
    FROM T_B_UNIVERSITY u
    LEFT JOIN T_D_EDUCATION e ON e.UNIVERSITY_ID = u.id
    LEFT JOIN T_D_VIDEO_PLAYER a 
        ON a.USER_ID = e.user_id
        AND e.ISDEFAULT = 1
        AND e.ISVALID = 1
        AND a.AUDITSTATUS = 1
        AND a.ISVALID = 1
    LEFT JOIN T_D_USER c 
        ON a.USER_ID = c.id
        AND c.ISVALID = 1
    WHERE u.REGION_CODE LIKE '43%'
    GROUP BY u.NAME, u.id
) t
ORDER BY playercnt DESC;

執行計畫關鍵點（慢）

• Cost 170，Estimated Rows 672
• Predicate 顯示 E.ISVALID=1 AND E.ISDEFAULT=1 在 FILTER (Id 10) 執行，而非 JOIN 時過濾
• T_D_VIDEO_PLAYER 全表掃描 (Id 11)

修正 SQL（快）

將右表的過濾條件從 WHERE 移至對應的 ON 子句：

SELECT *
FROM (
    SELECT u.NAME AS UniversityName,
           u.id AS UniversityId,
           COUNT(a.SIGNUPNUMBER) AS playercnt
    FROM T_B_UNIVERSITY u
    LEFT JOIN T_D_EDUCATION e 
        ON e.UNIVERSITY_ID = u.id
        AND e.ISDEFAULT = 1
        AND e.ISVALID = 1
    LEFT JOIN T_D_VIDEO_PLAYER a 
        ON a.USER_ID = e.user_id
        AND a.AUDITSTATUS = 1
        AND a.ISVALID = 1
    LEFT JOIN T_D_USER c 
        ON a.USER_ID = c.id
        AND c.ISVALID = 1
    WHERE u.REGION_CODE LIKE '43%'
    GROUP BY u.NAME, u.id
) t
ORDER BY playercnt DESC;

執行計畫關鍵點（快）

• Cost 25，Estimated Rows 301
• Predicate 下推至 JOIN 層級 (Id 6, 9)
• T_D_VIDEO_PLAYER 讀取行數從全表降至 78 行

核心原理

LEFT JOIN 右表條件放在 WHERE 會隱性轉為 INNER JOIN，導致：

1. 先做 Cartesian Product 再過濾，中間結果集暴增
2. 無法利用索引 Seek，退化為 Scan
3. JOIN 順序被優化器錯誤選擇

放在 ON 子句則保持外部連接語義，優化器可提前過濾右表，大幅縮小中間結果。

效能對比

最佳實務

• LEFT/RIGHT/FULL JOIN 時，右表過濾條件必須寫在 ON 子句
• 僅左表（保留表）條件放 WHERE
• 定期用 SET STATISTICS IO, TIME ON 驗證執行計畫