資料庫效能優化：JOIN 方法深度解析 (1) — 執行計畫與 Nested Loop

前言：突破認知瓶頸

作者分享從 800 萬核心表、3000 萬大表、光纖存儲、週備份的實戰經驗，強調理解 JOIN 算法原理比背誦技巧更重要。SQL Server / Oracle / MySQL 核心原理相通，深入理解可遷移至架構設計。

執行計畫閱讀指南

• 查詢分析器 → 「查詢」選單 → 「顯示執行計畫」
• 結果視窗「執行計畫」頁籤：從右到左為執行順序
• 圖示 = 操作，箭頭 = 資料流（滑鼠懸停可見邏輯/物理操作、行數、大小、I/O/CPU 成本、Argument）
• 注意：計畫基於統計資訊評估，非 100% 準確，但仍是分析最重要依據

JOIN 三大算法：Nested Loop / Merge / Hash

SQL Server 2000 與 Oracle 均支援三種，但選擇條件因記憶體管理不同而異（Oracle 偏好 Nested Loop）。

1. Nested Loop Join 詳解

適用場景

• Outer Input 估計行數 1,000~2,000 以下（超過 3,000 幾乎不選）
• Inner Input 於聯結欄位有可用索引

優點：三種 JOIN 中記憶體與 CPU 消耗最低（除非強制指定不當）。

案例：Table A (無索引) INNER JOIN Table B (Col1 有 PK/Clustered Index)

SELECT ... FROM TableA INNER JOIN TableB ON TableA.Col1 = TableB.Col1
WHERE TableA.Col2 = ? AND TableB.Col2 = ?

執行步驟（Clustered Index Seek 版）

1. Table Scan TableA，套用 TableA.Col2 = ? 篩選 → 輸出符合行
2. Nested Loop：對上述每一行，執行步驟 3
3. Clustered Index Seek TableB，條件 TableB.Col1 = rowA.Col1 AND TableB.Col2 = ? → 直接讀取資料列
4. 組裝最終結果集

因 Clustered Index 葉節點即資料列，TableB.Col2 = ? 可在 Seek 過程中一併篩選，無額外 Filter。

Non-Clustered Index 版差異

3. Index Seek TableB 非叢集索引 → 取得索引鍵值 + 書籤
4. Bookmark Lookup (RID/Key Lookup) 依書籤回表取完整資料列
5. Filter 套用 TableB.Col2 = ?（因 Index Seek 未觸及該欄位）

TableA 同理：Table Scan 可直接篩選 Col2，無額外 Filter。

強制指定

TableA INNER LOOP JOIN TableB
-- 或
OPTION (LOOP JOIN)

警告：超出適用範圍強制使用反而嚴重劣化效能，除非極有把握否則勿用。