您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

SQL Server 效能調優 16 大核心技巧：從查詢重寫到索引策略

編輯：關於SqlServer

前言：為什麼 SQL 效能差距可達 27 倍？

同一個查詢需求，優寫法 0.96 秒完成，差寫法卻需 26 秒。本文整理 16 個實戰驗證的 SQL Server 效能優化技巧，涵蓋查詢重寫、索引設計、執行計畫分析等核心領域，助你將查詢效能提升數量級。

一、查詢重寫層面優化

1. FROM 子句表順序決定驅動表（Rule-based 優化器有效）

SQL Server 解析器以從右至左順序處理 FROM 子句表名，最後寫入的表（驅動表）最先被掃描。多表聯結時，應將筆數最少的表置於 FROM 子句末尾。

-- 最佳：小表 TAB2 (5筆) 在後，大表 TAB1 (16,384筆) 在前
SELECT COUNT(*) FROM TAB1, TAB2  -- 0.96 秒

-- 不佳：大表在後
SELECT COUNT(*) FROM TAB2, TAB1  -- 26.09 秒

三表以上聯結，選擇交叉表（被其他表引用最多的表）作為驅動表效能最佳。

2. WHERE 子句條件順序：過濾力強的條件寫在最後

解析器採用自下而上順序解析 WHERE 子句。表聯結條件優先，過濾率最高的條件放最後。

-- 低效：156.3 秒
SELECT * FROM EMP E 
WHERE SAL > 50000 
  AND JOB = 'MANAGER' 
  AND 25 < (SELECT COUNT(*) FROM EMP WHERE MGR = E.EMPNO);

-- 高效：10.6 秒
SELECT * FROM EMP E 
WHERE 25 < (SELECT COUNT(*) FROM EMP WHERE MGR = E.EMPNO)
  AND SAL > 50000 
  AND JOB = 'MANAGER';

3. 避免 SELECT *：明確列出欄位名稱

SELECT * 強制引擎查詢資料字典轉換欄位名，增加解析開銷。明確指定欄位可減少網路傳輸、記憶體佔用與解析時間。

4. 減少資料庫往返次數：批次處理勝過迴圈

每次 SQL 執行都伴隨解析、優化、綁定、I/O 等開銷。合併多次查詢為單一陳述式：

-- 最差：兩次獨立查詢
SELECT ... WHERE EMP_NO = 342;
SELECT ... WHERE EMP_NO = 291;

-- 次差：游標迴圈
DECLARE CURSOR C1 ... 

-- 最佳：單一查詢自聯結
SELECT A.EMP_NAME, A.SALARY, B.EMP_NAME, B.SALARY
FROM EMP A, EMP B
WHERE A.EMP_NO = 342 AND B.EMP_NO = 291;

5. 善用 DECODE/CASE 避免重複掃描

將多條聚合查詢合併為單次掃描：

-- 原始：兩次全表掃描
SELECT COUNT(*), SUM(SAL) FROM EMP WHERE DEPT_NO = '0020' AND ENAME LIKE 'SMITH%';
SELECT COUNT(*), SUM(SAL) FROM EMP WHERE DEPT_NO = '0030' AND ENAME LIKE 'SMITH%';

-- 優化：單次掃描搭配 CASE
SELECT 
  COUNT(CASE WHEN DEPT_NO = '0020' THEN 1 END) D0020_COUNT,
  COUNT(CASE WHEN DEPT_NO = '0030' THEN 1 END) D0030_COUNT,
  SUM(CASE WHEN DEPT_NO = '0020' THEN SAL END) D0020_SAL,
  SUM(CASE WHEN DEPT_NO = '0030' THEN SAL END) D0030_SAL
FROM EMP WHERE ENAME LIKE 'SMITH%';

6. WHERE 取代 HAVING：提前過濾減少排序開銷

HAVING 在分組後過濾，需先完成排序與聚合；WHERE 在分組前過濾，大幅減少處理行數。

7. 減少子查詢對表的重複存取

合併相關子查詢，使用元組比較或單一子查詢回傳多欄位：

-- 低效：兩次掃描 TAB_COLUMNS
SELECT TAB_NAME FROM TABLES 
WHERE TAB_NAME = (SELECT TAB_NAME FROM TAB_COLUMNS WHERE VERSION = 604)
  AND DB_VER = (SELECT DB_VER FROM TAB_COLUMNS WHERE VERSION = 604);

-- 高效：單次掃描
SELECT TAB_NAME FROM TABLES 
WHERE (TAB_NAME, DB_VER) = (SELECT TAB_NAME, DB_VER FROM TAB_COLUMNS WHERE VERSION = 604);

8. 善用表別名：減少解析歧義與時間

多表聯結時為每個表指定簡短別名，並在所有欄位引用前加上別名前綴。

9. EXISTS 取代 IN：語意相同但效能更優

EXISTS 只要找到一筆符合即停止掃描，IN 需完整掃描子查詢結果集。

10. NOT EXISTS 取代 NOT IN：避免全表掃描與 NULL 陷阱

NOT IN 遇到 NULL 值會導致邏輯錯誤且效能極差。改寫為 NOT EXISTS 或 LEFT JOIN ... WHERE IS NULL。

11. 表聯結優於 EXISTS：利用優化器連結演算法

內聯結通常比 EXISTS 更有效率，優化器有更多連結策略可選擇。

12. EXISTS 取代 DISTINCT：避免昂貴的去重排序

多表查詢需去重時，改用 EXISTS 半聯結，優化器可在滿足條件時立即返回。

二、索引策略深度解析

13. 索存取模式與適用場景

掃描類型	適用條件	效能特徵
Index Unique Scan	唯一索引 + 等值條件	單一 ROWID，最快
Index Range Scan	唯一索引範圍 / 非唯一索引	多行 ROWID，次之
Full Table Scan	無索引 / 領先萬用字元 / 函數運算	最慢，但大數據量有時更優