SQL 子查詢重複問題：使用 CTE / 派生表 / 暫存表優化查詢效能

問題 SQL

SELECT m.id, m.name, m.quantity,
       (SELECT SUM(c.quantity) FROM ctable c WHERE c.mid = m.id) AS ssum,
       (m.quantity - (SELECT SUM(c.quantity) FROM ctable c WHERE c.mid = m.id)) AS jian
FROM mtable m

同一子查詢執行兩次，隨資料量增長效能急劇下降。

四種優化方案

方案一：Common Table Expression (CTE) — 推薦

WITH c AS (
    SELECT mid, SUM(quantity) AS total_qty
    FROM ctable
    GROUP BY mid
)
SELECT m.id, m.name, m.quantity,
       ISNULL(c.total_qty, 0) AS ssum,
       m.quantity - ISNULL(c.total_qty, 0) AS jian
FROM mtable m
LEFT JOIN c ON m.id = c.mid;

• 子查詢只執行一次，結果暫存於記憶體
• 可讀性最佳，支援遞迴查詢
• SQL Server / PostgreSQL / Oracle / MySQL 8.0+ 完整支援

方案二：派生表 / 子查詢因子化

SELECT m.id, m.name, m.quantity,
       ISNULL(c.total_qty, 0) AS ssum,
       m.quantity - ISNULL(c.total_qty, 0) AS jian
FROM mtable m
LEFT JOIN (
    SELECT mid, SUM(quantity) AS total_qty
    FROM ctable
    GROUP BY mid
) c ON m.id = c.mid;

• 相容舊版資料庫（MySQL 5.7、SQL Server 2000+）
• 執行計畫與 CTE 幾乎相同

方案三：暫存表 / 表變數（大數據量適用）

-- 暫存表（跨 Session 可見，支援索引）
CREATE TABLE #tmp_sum (mid INT PRIMARY KEY, total_qty INT);
INSERT INTO #tmp_sum
SELECT mid, SUM(quantity) FROM ctable GROUP BY mid;

SELECT m.id, m.name, m.quantity,
       ISNULL(t.total_qty, 0) AS ssum,
       m.quantity - ISNULL(t.total_qty, 0) AS jian
FROM mtable m
LEFT JOIN #tmp_sum t ON m.id = t.mid;

DROP TABLE #tmp_sum;

• 適合極大資料集（百萬列以上）
• 可建立索引進一步加速 JOIN
• 需注意 TempDB 壓力與清理

方案四：APPLY 運算子（SQL Server 特有）

SELECT m.id, m.name, m.quantity,
       c.total_qty AS ssum,
       m.quantity - c.total_qty AS jian
FROM mtable m
OUTER APPLY (
    SELECT SUM(quantity) AS total_qty
    FROM ctable c WHERE c.mid = m.id
) c;

• 語意清晰：每行外部資料列呼叫一次表值函數
• 優化器常能產生高效計畫

效能對比建議

最佳實務

1. 優先使用 CTE 簡化邏輯
2. 執行計畫檢查：關注 `Table Spool`、`Lazy Spool` 暫存操作
3. 為 `ctable.mid` 建立索引：`CREATE INDEX IX_ctable_mid ON ctable(mid) INCLUDE(quantity);`
4. 避免 `SELECT *`，僅選取必要欄位減少 I/O