SQL 表連接查詢優化：大小表順序、JOIN 語法與執行計劃深度解析

實驗環境

• student：999,999 行（大表），classID 無索引。
• classInfo：100 行（小表），classID 聚集主鍵。

三種寫法對比

1. 大表驅動小表（內連接）

SELECT TOP 1000 a.sUserName, b.sClassName
FROM student a
INNER JOIN classInfo b ON a.classID = b.classID;

2. 小表驅動大表（內連接）

SELECT TOP 1000 a.sUserName, b.sClassName
FROM classInfo b
INNER JOIN student a ON a.classID = b.classID;

3. 舊式 WHERE 連接

SELECT TOP 1000 a.sUserName, b.sClassName
FROM classInfo b, student a
WHERE a.classID = b.classID;

核心發現

1. 邏輯等價： 三種寫法生成完全相同的執行計劃（Hash Match / Nested Loops 視統計信息而定）。
2. 優化器重排序： SQL Server 基於成本自動決定建構端/探測端，書寫順序不影響結果。
3. TOP 交互： TOP 1000 可能觸發「行目標」優化，提前終止掃描。

性能關鍵點：索引與統計信息

• 若 student.classID 有索引 → Nested Loops（小表驅動大表索引尋找）極快。
• 無索引 → Hash Match（構建哈希表）或 Merge Join（需排序），I/O 顯著增加。
• 統計信息準確性 決定優化器是否選對 JOIN 算子與表順序。

最佳實踐建議

1. 統一使用 ANSI JOIN 語法： 可讀性強、外連接語義清晰、避免舊語法歧義。
2. 為 JOIN 列建立索引： 尤其大表側的外鍵列，收益最大。
3. 維護統計信息： AUTO_UPDATE_STATISTICS ON，大表考慮增量更新或全掃描。
4. 避免不必要的列投影： SELECT * 增加 I/O 並阻礙覆蓋索引。
5. 大結果集分頁： 使用 Keyset Pagination（WHERE id > @last_id ORDER BY id）替代 OFFSET/FETCH。

結語

JOIN 順序由優化器決策，開發者應聚焦於：索引覆蓋 JOIN 鍵、統計信息鮮度、精確列投影、分頁模式。寫法規範化是專業素養，性能優化靠索引與統計。