SQL Server 性能優化是系統工程,涉及邏輯設計、物理設計、查詢寫法、實例配置、硬體資源多個層面。本文彙總一線實戰經驗,按「索引策略、查詢重寫、配置維護、工具驗證」四大維度,提供可落地的優化清單與反模式對照。
| 場景 | 推薦索引類型 | 理由 |
|---|---|---|
| 大量重複值 + 範圍查詢 + ORDER BY/GROUP BY | 聚集索引 | 數據物理有序,範圍掃描零碎讀少,排序免費 |
| 高選擇性 + 點查/小範圍 + 覆蓋查詢 | 非聚集索引 | B+樹深度可控、Key Lookup 開銷小 |
| 多列聯合過濾/排序/連接 | 組合索引 | 前導列 = 高頻過濾列、包含列覆蓋 SELECT 列表 |
| 大表分區 + 範圍查詢 | 分區表 + 對齊索引 | 分區消除、獨立維護、併行度提升 |
INCLUDE 非關鍵列,消除 Key Lookup,實現「索引覆蓋」ALTER INDEX ... REORGANIZE/REBUILD + UPDATE STATISTICS,碎片 > 30% 重建,> 5% 整理| 反模式 | 症狀 | 重寫方案 |
|---|---|---|
| 列上運算/函數 | WHERE YEAR(CreateDate) = 2023 → 掃描 | WHERE CreateDate >= '2023-01-01' AND CreateDate < '2024-01-01' |
| 隱式轉換 | VARCHAR 列比較 INT 參數 → 掃描 | 參數類型與列定義一致,或顯式 CAST 參數 |
| LIKE '%關鍵字%' | 前導通配符無法用索引 | 全文檢索 / 反轉索引 / n-gram / 搜尋引擎 |
| OR 跨列條件 | WHERE A=1 OR B=2 → 掃描 | UNION ALL 分開查 + 去重,或加組合索引 |
| NOT IN / NOT EXISTS 子查詢 | NULL 語義陷阱、性能差 | LEFT JOIN ... WHERE RightTable.Id IS NULL |
| 游標/WHILE 循環逐行處理 | RBAR (Row By Agonizing Row) | 集合化思維:JOIN、窗口函數、CTE、MERGE |
| SELECT * | 傳輸無用列、阻礙覆蓋索引 | 顯式列出業務需要的列 |
-- IN → EXISTS (大外表、小子查詢) SELECT * FROM Orders O WHERE EXISTS (SELECT 1 FROM Customers C WHERE C.Id = O.CustId AND C.IsVip = 1); -- 多 OR → UNION ALL SELECT * FROM Products WHERE CategoryId = 1 UNION ALL SELECT * FROM Products WHERE CategoryId = 2; -- 相關子查詢 → JOIN + 聚合 SELECT C.Name, COUNT(O.Id) AS OrderCnt FROM Customers C LEFT JOIN Orders O ON C.Id = O.CustId GROUP BY C.Name; -- 分頁大偏移 → 鍵集分頁 -- OFFSET 1000000 ROWS FETCH NEXT 20 → 慢 SELECT TOP 20 * FROM Orders WHERE Id > @LastSeenId ORDER BY Id; -- 快
RESERVED_MEMORY_ALLOCATION_EXT 等待,調整 MAX_SERVER_MEMORY 留 4-8GB 給 OSAUTO_UPDATE_STATISTICS、AUTO_UPDATE_STATISTICS_ASYNC,大表考慮手動定時更新DBCC CHECKDB 定期執行(維護窗口),DBCC CHECKTABLE 單表快檢BACKUP LOG ... WITH TRUNCATE_ONLY (舊版) 或切換恢復模式;完整恢復模式定期備份日志SET SHOWPLAN_ALL ON / SET STATISTICS IO, TIME ON — 文本模式sys.dm_exec_query_stats) / 用戶投訴SET STATISTICS XML ON優化本質:在結果正確前提下,用優化器可識別的模式,最大化利用索引,最小化 I/O 與 CPU。最大的性能提升往往來自邏輯數據模型優化、索引設計優化、查詢邏輯重寫這三大架構層面,而非參數微調。把優化當作持續工程實踐,而非一次性救火任務。