SQL 查詢優化實戰三部曲：索引設計、連接條件與 WHERE 子句改寫

案例資料：62 萬筆記錄的 record 表

一、索引設計對範圍查詢的決定性影響

索引設計黃金法則

1. 大量重複值 + 範圍查詢/ORDER BY/GROUP BY → 考慮叢集索引。
2. 多欄位常同時存取且各有重複值 → 組合索引。
3. 組合索引前導欄 = 使用頻率最高的欄位，並盡量形成索引覆蓋。

二、連接條件完整性決定執行計畫優劣

card (7,896 筆, card_no 非叢集) JOIN account (191,122 筆, account_no 非叢集)

-- 僅 JOIN card_no：20 秒
SELECT SUM(a.amount) FROM account a, card b WHERE a.card_no = b.card_no;

-- 加上 account_no：<1 秒
SELECT SUM(a.amount) FROM account a, card b
WHERE a.card_no = b.card_no AND a.account_no = b.account_no;

原理：優化器依連接條件估算 I/O 成本。
條件 1：外部 account (22,541 頁) + 191,122 × 3 = 595,907 I/O。
條件 2：外部 card (1,944 頁) + 7,896 × 4 = 33,528 I/O → 優化器選擇最佳路徑。
結論：提供完整連接條件 (含所有索引欄位)，讓優化器找到真正最佳計畫。

三、WHERE 子句寫法決定索引是否生效

IN / OR 陷阱

-- 慢：IN 轉為 OR 策略 → 建立暫存工作表 → 23 秒 (20 萬筆) / 220 秒 (62 萬筆)
SELECT COUNT(*) FROM stuff WHERE id_no IN ('0','1');

-- 快：拆分查詢 + UNION ALL 或預存程序彙總 → 3-4 秒
SELECT COUNT(*) FROM stuff WHERE id_no = '0';
SELECT COUNT(*) FROM stuff WHERE id_no = '1';

IN/OR 常觸發「OR 策略」：建立暫存表、去重複，無法使用索引。若值不重複，拆分為多查詢再彙總。

總結：優化 = 寫出優化器可識別的 SARGable 條件 + 充分索引 + 完整連接

• 任何欄位運算 (函數、計算) 移至等號右側常數。
• IN/OR 拆分或改用 UNION ALL / 暫存表 / 資料表值參數。
• 善用預存程序封裝邏輯，減少網路往返、利用計畫快取。

SQL 優化是系統工程：應用層寫法 → 資料庫層索引/統計 → 作業系統/硬體資源。從寫法入手，投資報酬率最高。