**減少 I/O、減少 CPU、減少鎖爭用、利用索引、避免排序/雜湊溢寫**。每條 SQL 提交都會經歷解析→綁定→優化→執行,優化器依賴統計資訊與索引做決策。
-- 差
SELECT * FROM Orders WHERE CustomerID = 1001;
-- 優
SELECT OrderID, OrderDate, TotalAmount FROM Orders WHERE CustomerID = 1001;
減少網路傳輸、緩衝池佔用、覆蓋索引機會、觸發器/計算欄位開銷。每少一欄位,速度提升與欄位大小成正比。
-- 差
IF (SELECT COUNT(*) FROM Orders WHERE CustomerID = 1001) > 0
-- 優
IF EXISTS (SELECT 1 FROM Orders WHERE CustomerID = 1001)
EXISTS 支援**短路**:找到第一筆即停止;COUNT 必須掃描所有匹配行。
-- 差 (NULL 陷阱 + 效能差)
SELECT * FROM Dept WHERE DeptNo NOT IN (SELECT DeptNo FROM Emp);
-- 優
SELECT * FROM Dept d WHERE NOT EXISTS (SELECT 1 FROM Emp e WHERE e.DeptNo = d.DeptNo);
-- 或
SELECT d.* FROM Dept d LEFT JOIN Emp e ON d.DeptNo = e.DeptNo WHERE e.DeptNo IS NULL;
NOT IN 遇到子查詢含 NULL 會返回空結果集;NOT EXISTS/LEFT JOIN 語意正確且可用索引。
-- 差:導致全表掃描
SELECT * FROM Orders WHERE CustomerID = 1001 OR SalesPersonID = 5;
-- 優:分開查詢合併 (注意去重)
SELECT * FROM Orders WHERE CustomerID = 1001
UNION ALL
SELECT * FROM Orders WHERE SalesPersonID = 5 AND CustomerID <> 1001;
OR 通常阻礙索引搜尋 (Index Seek),改寫後各分支可獨立用索引。
(A, B, C) → WHERE 條件順序盡量 A = ? AND B > ? AND C = ?。=, IN) 先於範圍條件 (>, LIKE 'prefix%')。-- 差
WHERE ID > 1000
-- 優
WHERE ID >= 1001
某些優化器對 >= 能更精確估算範圍行數,並可能觸發索引搜尋而非掃描。
WHERE YEAR(OrderDate) = 2024 → WHERE OrderDate >= '2024-01-01' AND OrderDate < '2025-01-01'。WHERE VarcharCol = 123 → WHERE VarcharCol = '123'。IS NULL, <>, !=, NOT, NOT IN, NOT LIKE 通常導致掃描 (除非有篩選索引/計算欄索引)。LIKE '%abc' → 全掃或全文索引。-- 差
WHERE SUBSTRING(ID,1,1) = 'A'
WHERE Amount * 1.05 > 1000
-- 優
WHERE ID LIKE 'A%'
WHERE Amount > 1000 / 1.05
使優化器能直接對索引欄位進行搜尋 (Seek)。
CREATE NONCLUSTERED INDEX IX_Orders_Cust_Date_Cover
ON Orders (CustomerID, OrderDate DESC)
INCLUDE (TotalAmount, Status); -- 非鍵欄位,不計入索引樹深度
查詢 SELECT TotalAmount, Status FROM Orders WHERE CustomerID = ? ORDER BY OrderDate DESC 可**完全在索引層完成**,避免回表 (Key Lookup/RID Lookup)。
SET STATISTICS IO, TIME ON; 觀察邏輯讀次數、CPU/經過時間。SET SHOWPLAN_XML ON; / SSMS「顯示實際執行計畫」分析運算子、估算/實際行數、缺失索引建議。UPDATE STATISTICS / sp_updatestats 保持統計資訊新鮮。IX_<Table>_<KeyCols>[_Include<Cols>]、UK_...、FK_...。將九大鐵律內化為編碼肌肉記憶,配合持續監控與執行計畫分析,查詢效能將穩步提升。