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

SQL Server 多表查詢優化實戰：從執行路徑到連接順序

編輯：關於SqlServer

核心優化原則

1. 執行路徑與共用記憶體

Oracle 單表統計極快（0.02秒），多表連接慢數十倍，因為高速緩衝僅支援簡單表。需在 init.ora 設定適當共用池大小，增加語句共用機率。

2. FROM 子句表順序：小表靠右（基礎表優先）

Oracle 從右往左解析 FROM 子句，最後的表最先處理。選擇筆數最少的表作為基礎表。

-- TAB1: 16,384 筆, TAB2: 1 筆
-- 好：小表在右
SELECT COUNT(*) FROM tab1, tab2;  -- 0.96秒
-- 差：大表在右
SELECT COUNT(*) FROM tab2, tab1;  -- 26.09秒

三表以上：選擇交叉表（被其他表引用的表）作為基礎表。

3. WHERE 子句連接順序：限制性最強的條件寫在最後

Oracle 自下而上解析 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';

4. 避免 SELECT *

* 需查詢資料字典轉換欄位名，增加解析開銷。明確列出所需欄位。

5. 減少資料庫存取次數

-- 低效：兩次存取
SELECT EMP_NAME, SALARY, GRADE FROM EMP WHERE EMP_NO = 342;
SELECT EMP_NAME, SALARY, GRADE FROM EMP WHERE EMP_NO = 291;

-- 高效：一次存取
SELECT A.EMP_NAME, A.SALARY, A.GRADE, 
       B.EMP_NAME, B.SALARY, B.GRADE
FROM EMP A, EMP B
WHERE A.EMP_NO = 342 AND B.EMP_NO = 291;

6. 刪除重複記錄：用 ROWID 最快

DELETE FROM EMP E 
WHERE E.ROWID > (SELECT MIN(X.ROWID) FROM EMP X WHERE X.EMP_NO = E.EMP_NO);

7. TRUNCATE 替代 DELETE

TRUNCATE 不產生回滾段資料，不可恢復，但資源消耗極低、速度極快。

8. 多用 COMMIT

釋放：回滾段資訊、鎖、Redo Log Buffer 空間。注意事務完整性平衡。

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

-- 低效：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);

10. EXISTS 替代 IN，NOT EXISTS 替代 NOT IN

-- 低效
SELECT * FROM EMP WHERE DEPTNO IN (SELECT DEPTNO FROM DEPT WHERE LOC = 'MELB');

-- 高效
SELECT * FROM EMP WHERE EXISTS (SELECT 'X' FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = 'MELB');

-- NOT IN 最差（全表掃描+排序合併），改寫：
-- 方法一：外部連接
SELECT ... FROM EMP A, DEPT B 
WHERE A.DEPT_NO = B.DEPT_NO(+) AND B.DEPT_NO IS NULL AND B.DEPT_CAT(+) = 'A';

-- 方法二：NOT EXISTS（最推薦）
SELECT ... FROM EMP E 
WHERE NOT EXISTS (SELECT 'X' FROM DEPT D WHERE D.DEPT_NO = E.DEPT_NO AND DEPT_CAT = 'A');

11. 識別低效 SQL 工具查詢

SELECT EXECUTIONS, DISK_READS, BUFFER_GETS,
       ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2) AS Hit_ratio,
       ROUND(DISK_READS/EXECUTIONS,2) AS Reads_per_run,
       SQL_TEXT
FROM V$SQLAREA
WHERE EXECUTIONS > 0 AND BUFFER_GETS > 0 
  AND (BUFFER_GETS-DISK_READS)/BUFFER_GETS < 0.8
ORDER BY 4 DESC;

UPDATE 多欄位優化

-- 低效：兩次子查詢
UPDATE EMP SET 
  EMP_CAT = (SELECT MAX(CATEGORY) FROM EMP_CATEGORIES),
  SAL_RANGE = (SELECT MAX(SAL_RANGE) FROM EMP_CATEGORIES)
WHERE EMP_DEPT = 0020;

-- 高效：單次子查詢
UPDATE EMP SET 
  (EMP_CAT, SAL_RANGE) = (SELECT MAX(CATEGORY), MAX(SAL_RANGE) FROM EMP_CATEGORIES)
WHERE EMP_DEPT = 0020;