SQL 效能優化實戰指南：30 個避免全表掃描的關鍵技巧

SQL 查詢優化的核心原則是盡量避免全表掃描。本文整理 30 個實戰優化技巧，涵蓋索引設計、WHERE 條件撰寫、JOIN 處理、臨時表使用、游標替代方案等核心領域，助你撰寫高效能的 T-SQL 程式碼。

一、索引與 WHERE 條件優化

1. 優先在 WHERE 與 ORDER BY 涉及的欄位建立索引，讓查詢能透過索引尋找而非掃描全表。
2. 避免在 WHERE 中判斷 NULL 值（如 WHERE num IS NULL），這會導致索引失效。建議將欄位設為 NOT NULL 並給予預設值（如 0），改用 WHERE num = 0 查詢。
3. 避免使用 != 或 <> 操作符，同樣會放棄索引改走全表掃描。
4. 謹慎使用 OR 連接條件：若一側有索引、另一側無索引，會導致全表掃描。改用 UNION ALL 分開查詢再合併結果。
5. IN / NOT IN 慎用，易導致全表掃描。連續數值改用 BETWEEN；子查詢場景優先考慮 EXISTS 替代 IN。
6. LIKE '%abc%' 開頭萬用字元會失效索引，若需模糊搜尋可考慮全文檢索。
7. WHERE 條件中使用參數（變數） 會導致編譯時無法預估選擇率，進而全表掃描。可用 WITH (INDEX(索引名)) 強制指定索引。
8. 避免在 WHERE 欄位上進行運算或函數處理（如 num/2=100、SUBSTRING(name,1,3)='abc'），應改寫為 num=200、name LIKE 'abc%' 等 SARGable 形式。
9. 複合索引必須符合最左前綴原則：查詢條件需包含索引第一欄位，且欄位順序宜與索引一致。

二、查詢寫法與資料型別選擇

10. 避免無意義查詢：建立空表結構請用 CREATE TABLE，勿用 SELECT ... WHERE 1=0。
11. UPDATE 僅更新必要欄位，避免全欄位更新造成效能損耗與大量記錄檔。
12. 大表 JOIN 先分頁再關聯，否則邏輯讀數飆升。
13. 杜絕無條件 COUNT(*)，會觸發全表掃描且無業務意義。
14. 索引不是越多越好：每表建議不超過 6 個索引，過多會拖慢 INSERT/UPDATE。
15. 避免頻繁更新叢集索引鍵，會導致資料物理順序重組，資源消耗極大。
16. 優先使用數值型欄位，字串比較需逐字元檢查，數值型單次比較即完成。
17. 優先用 VARCHAR/NVARCHAR 替代 CHAR/NCHAR，節省空間且縮小搜尋範圍。
18. 嚴禁 SELECT *，明確列出所需欄位，減少網路傳輸與 I/O。

三、臨時表與表變數策略

19. 優先使用表變數替代臨時表（大量資料時注意索引限制，僅支援主鍵）。
20. 避免頻繁建立/刪除臨時表，減少系統表資源爭用。一次性事件改用導出表（Derived Table）。
21. 大量資料插入臨時表用 SELECT INTO 避免大量 Log；小量資料用 CREATE TABLE + INSERT 緩和系統表壓力。
22. 預存程序結束時顯式清理臨時表：先 TRUNCATE TABLE 再 DROP TABLE，避免長時間鎖定系統表。

四、游標與集合式思維

23. 盡量避免游標，效率較差；超過 1 萬列建議改寫為集合式操作。
24. 優先尋找基於集合的解決方案，通常比逐列處理更高效。
25. 小型資料集可考慮 FAST_FORWARD 唯讀順向游標，在需關聯多表取得資料時表現優於其他逐列法。

五、交易與並發控制

26. 預存程序/觸發器首尾加 SET NOCOUNT ON/OFF，抑制 DONE_IN_PROC 訊息降低網路負擔。
27. 避免大型交易，縮短鎖定時間，提升並發能力。
28. 避免向客戶端回傳大量資料，若資料量過大應重新評估需求合理性。

六、實戰案例：大量 DELETE/INSERT 批次提交

線上環境執行大量刪除/插入會鎖表導致服務停擺。解法是分批處理 + 間歇性釋放鎖。MySQL 範例：

WHILE (1) {
    -- 每次只處理 1000 筆
    mysql_query("DELETE FROM logs WHERE log_date <= '2012-11-01' LIMIT 1000");
    IF (mysql_affected_rows() == 0) BREAK; -- 刪除完成
    usleep(50000); -- 暫停 0.05 秒釋放鎖
}

SQL Server 同理可用 TOP (1000) 配合 WAITFOR DELAY 實現相同效果。