Hash Join 兩階段深度剖析
Build 階段：建立 Hash Table
- 輸入：Build Input（較小表）
- Hash Key：聯結欄位（JOIN）、GROUP BY 欄位、UNION 全欄位
- 流程：逐行讀取 → 計算 Hash 值 → 映射至 Hash Buckets → 衝突以鏈表串聯
- 目標：將 Build Input 所有行掛載至 Hash Table
Probe 階段：探測匹配
- 輸入：Probe Input（較大表）
- 流程：逐行讀取 → 同一 Hash 函數計算 Hash 值 → 定位 Bucket → 掃描衝突鏈尋找匹配行
- 原理：以 Hash 縮小搜尋範圍，避免全表掃描，提升大資料量匹配效率
記憶體管理與分區策略
In-Memory Hash Join（理想）
- Build Input 較小，Hash Table 完全置於記憶體
- Probe 階段全在記憶體完成，效能最佳
Grace Hash Join（記憶體不足）
- Build Input 過大 → 將雙輸入分區為多組成對的 Partition
- 每組 Partition 獨立進行 In-Memory Hash Join
- Partition 檔案寫入磁碟，逐組載入記憶體處理
極端情況
- Standard External Merge Sorts、Multiple Merge Levels、Multiple Partitioning Steps/Levels
- 可能觸發 Recursive Hash Join 等進階優化
Hash Join 適用場景與代價
- 大資料量 JOIN、聯結欄位雙方皆無索引
- 記憶體/CPU 殺手，尤其涉及分區時：大量非同步 I/O、單執行緒可佔滿多顆磁碟、阻塞其他執行緒
強制指定
INNER HASH JOIN
OPTION (HASH JOIN)
優化三大鐵律
- 避免大資料 Hash Join → 轉為 Merge Join / Nested Loop Join
- 手段：表結構設計、索引調整、SQL 改寫、業務冗余欄位、定時跑批統計表、AOP 同步更新
- 壓縮兩端輸入資料量
- 條件需符合 SARG 形式
- 子查詢內部條件下推（優化器常失智，需手工下推）
- 業務邏輯限制輸入規模
- 重視業務分析設計：參考「系統分析設計 一個 JOIN 問題解決方案的感想」
SQL Server 2005 差異補充
- 執行計畫圖形可儲存/離線分析（2000 僅能存文字）
- Bookmark Lookup → RID Lookup + Nested Loops 顯示更直觀
- 工具提示新增 Output List，清楚展示各操作輸出欄位
