SQL 巢狀查詢優化實戰：使用 CTE 消除重複子查詢提升效能

原始查詢問題分析

SELECT * FROM (
    SELECT subject, COUNT(Subject) AS cout
    FROM Questions
    WHERE UserID = 'banianji' AND AskDate > '2011-01-01' AND AskDate < '2014-01-01'
    GROUP BY Subject
) AS a
WHERE a.cout = (
    SELECT MAX(cout) FROM (
        SELECT subject, COUNT(Subject) AS cout
        FROM Questions
        WHERE UserID = 'banianji' AND AskDate > '2011-01-01' AND AskDate < '2014-01-01'
        GROUP BY Subject
    ) AS b
);

核心缺陷：相同的聚合子查詢執行了兩次，資料量大時效能嚴重下降，且維護困難（修改條件需改兩處）。

優化方案：Common Table Expression (CTE)

WITH cte AS (
    SELECT subject, COUNT(Subject) AS cout
    FROM Questions
    WHERE UserID = 'banianji'
      AND AskDate > '2011-01-01'
      AND AskDate < '2014-01-01'
    GROUP BY Subject
)
SELECT * FROM cte AS a
WHERE a.cout = (SELECT MAX(cout) FROM cte AS b);

CTE 優勢解析

進階優化：視資料庫特性調整

SQL Server / PostgreSQL / Oracle：CTE 通常物化（Materialize）

CTE 結果可能被暫存為臨時表，適合重複引用場景。但若 CTE 只被引用一次，優化器可能選擇內聯展開而非物化。

MySQL 8.0+：CTE 支援良好，但需注意

• MySQL 8.0 開始支援 CTE，預設不物化，每次引用重新執行。
• 若需強制物化，可用 WITH cte AS (SELECT ... ) SELECT * FROM cte JOIN cte ... 或建立臨時表。

大數據量替代方案：臨時表 / 物化視圖

-- 臨時表（Session 級，自動清理）
CREATE TEMPORARY TABLE tmp_subject_stats AS
SELECT subject, COUNT(*) AS cnt
FROM Questions
WHERE UserID = 'banianji' AND AskDate BETWEEN '2011-01-01' AND '2014-01-01'
GROUP BY subject;

SELECT * FROM tmp_subject_stats
WHERE cnt = (SELECT MAX(cnt) FROM tmp_subject_stats);

-- 索引加速
CREATE INDEX idx_cnt ON tmp_subject_stats(cnt);

通用 SQL 優化檢查清單

1. 消除重複子查詢：用 CTE、臨時表、變數。
2. 避免 SELECT *：明確列出欄位，減少 I/O、利用覆蓋索引。
3. WHERE 條件下推：確保過濾條件在 JOIN/聚合前執行。
4. 索引策略：(UserID, AskDate, Subject) 複合索引可支援此查詢的過濾與分組。
5. 統計資訊更新：UPDATE STATISTICS / ANALYZE TABLE 確保優化器有準確基數估計。
6. 執行計畫分析：EXPLAIN / SET STATISTICS IO ON 觀察實際掃描行數、索引使用。

延伸技巧：取得「最大值對應的所有列」的標準寫法

窗口函數（現代 SQL 標準，支援：PG, Oracle, SQL Server, MySQL 8.0+）

WITH ranked AS (
    SELECT subject, COUNT(*) AS cnt,
           RANK() OVER (ORDER BY COUNT(*) DESC) AS rk
    FROM Questions
    WHERE UserID = 'banianji' AND AskDate BETWEEN '2011-01-01' AND '2014-01-01'
    GROUP BY subject
)
SELECT subject, cnt FROM ranked WHERE rk = 1;

優點：單次掃描、無需自我連接、語意清晰。

相關子查詢（通用但可能較慢）

SELECT subject, COUNT(*) AS cnt
FROM Questions q1
WHERE UserID = 'banianji' AND AskDate BETWEEN '2011-01-01' AND '2014-01-01'
GROUP BY subject
HAVING COUNT(*) = (
    SELECT MAX(cnt) FROM (
        SELECT COUNT(*) AS cnt
        FROM Questions q2
        WHERE UserID = 'banianji' AND AskDate BETWEEN '2011-01-01' AND '2014-01-01'
        GROUP BY subject
    ) t
);

總結

將重複的巢狀子查詢重構為 CTE 是提升可讀性、維護性與效能的首選手段。現代資料庫（PG, Oracle, SQL Server, MySQL 8.0+）均完整支援 CTE 與窗口函數，建議優先採用 WITH ... RANK() OVER ... 模式解決「取得極值對應所有列」問題。配合適當索引、統計資訊維護與執行計畫驗證，可獲得穩定高效的查詢效能。