SQL 查詢優化核心原則與 20+ 實戰技巧完整指南

優化核心原則

目標：將單次查詢讀取的資料頁數降至最低。

一、查詢重寫規則

二、讓索引生效的 SARGable 寫法

複合索引最左前綴原則

索引 (Col1, Col2, Col3) 僅以下 WHERE 能有效使用：

• WHERE Col1=@v1 AND Col2=@v2 AND Col3=@v3
• WHERE Col1=@v1 AND Col2=@v2
• WHERE Col1=@v1

跳過 Col1 直接查 Col2、Col3 將導致索引掃描。

三、子查詢優化策略

IN / EXISTS → JOIN

-- 原始
SELECT EmpID FROM Emp WHERE DeptID IN (SELECT DeptID FROM Dept WHERE DeptNo='001');
-- 優化 (DeptID 唯一)
SELECT e.EmpID FROM Emp e JOIN Dept d ON e.DeptID=d.DeptID WHERE d.DeptNo='001';
-- 優化 (DeptID 不唯一)
SELECT e.EmpID FROM Emp e JOIN (SELECT DISTINCT DeptID FROM Dept WHERE DeptNo='001') d ON e.DeptID=d.DeptID;

NOT IN / NOT EXISTS → LEFT JOIN ... IS NULL

SELECT e.EmpID FROM Emp e
LEFT JOIN Dept d ON e.DeptID=d.DeptID AND d.DeptNo='001'
WHERE d.DeptID IS NULL;

自相關子查詢 → 衍生表聚合

-- 原始：各部門薪資高於平均者
SELECT * FROM Salary a
WHERE a.Amount > (SELECT AVG(b.Amount) FROM Salary b WHERE b.DeptID=a.DeptID);

-- 優化：預先聚合
SELECT a.* FROM Salary a
JOIN (SELECT DeptID, AVG(Amount) AS AvgAmt FROM Salary GROUP BY DeptID) b
  ON a.DeptID=b.DeptID
WHERE a.Amount > b.AvgAmt;

四、參數化與計畫重用

-- 未參數化：每次不同值產生新計畫，污染 Plan Cache
SELECT * FROM Orders WHERE OrderID = 1001;
SELECT * FROM Orders WHERE OrderID = 1002;

-- 參數化：單一計畫重用
DECLARE @OrderID INT = 1001;
SELECT * FROM Orders WHERE OrderID = @OrderID;
-- 或使用 sp_executesql
EXEC sp_executesql N'SELECT * FROM Orders WHERE OrderID = @ID', N'@ID INT', @ID=1001;

實測：1000 次迴圈查詢，參數化後編譯時間從 18 秒降至 0.56 秒。

五、減少往返次數

-- 兩次查詢：先檢查再決定 INSERT/UPDATE
IF NOT EXISTS (SELECT 1 FROM Item WHERE ID=@ID) INSERT ... ELSE UPDATE ...

-- 單次 UPSERT (SQL Server 2008+ MERGE 或 OUTPUT 子句)
UPDATE Item SET Qty=Qty-@Qty WHERE ID=@ID;
IF @@ROWCOUNT=0 INSERT INTO Item (ID, Qty) VALUES (@ID, -@Qty);

六、UNION ALL 替代 UNION

業務邏輯確定無重複時，UNION ALL 省去排序去重成本。

七、索引維護與統計資訊

• 定期 UPDATE STATISTICS 或啟用 AUTO_UPDATE_STATISTICS。
• 碎片 > 30% → ALTER INDEX ... REBUILD；10-30% → REORGANIZE。
• 使用 sys.dm_db_index_usage_stats 找出未使用索引並移除。

八、常見等待類型對應優化方向