SQL Server 資料庫效能優化全方位指南：設計、硬體、索引與查詢調校

資料庫效能優化是系統工程，涵蓋邏輯設計、物理佈署、硬體資源、查詢撰寫與維運監控。本文以 SQL Server 為例，提供結構化優化建議。

一、資料庫邏輯與物理設計

1. 正規化與反正規化平衡

• 遵循 3NF (第三正規化) 消除冗余，減少資料頁數量與 I/O。
• 若查詢頻繁需 4+ 資料表 JOIN，考慮反正規化：
  • 增加冗余欄位 (如常用計算欄位：總金額、最大值)。
  • 垂直分割：將熱門欄位與冷門欄位分表 (共用 PK)，利於平行處理。
  • 水平分割：依時間/區域/部門分表 (分割資料表/檢視/檔案群組)，歷史資料冷存。

2. 資料型別最小化原則

• 能用 SMALLINT 勿用 INT；能用 VARCHAR(20) 勿用 VARCHAR(100)。
• 索引鍵欄位越窄，索引頁可容納更多列，樹高更低，I/O 越少。

3. 檔案群組與磁碟佈局

• 資料表置於獨立磁碟/陣列，非叢集索引置於另一磁碟/陣列 (利用 SQL Server 檔案群組)。
• 大型資料表跨多磁碟控制器分割 (資料分離)，多磁頭並行搜尋。
• TEXT/IMAGE/MAX 大型物件 (LOB) 置於專用檔案群組/磁碟。

二、硬體資源規劃

三、索引策略

• 叢集索引：窄、唯一、遞增 (身分識別欄最佳)，避免 GUID/複合鍵導致分頁分裂。
• 非叢集索引：覆蓋查詢 (INCLUDE 欄位)、過濾索引 (WHERE 子句)、避免過度索引 (寫入成本)。
• 定期重建/重組 (ALTER INDEX ... REBUILD/REORGANIZE) 依碎片率 ( >30% 重建, 5-30% 重組)。
• 統計資料自動更新 + 定期全掃描更新 (UPDATE STATISTICS ... WITH FULLSCAN)。

四、查詢與程式碼優化

• 避免 SELECT *，明確列出欄位。
• 使用參數化查詢/預存程序，重用執行計畫，防 SQL Injection。
• 善用 SET NOCOUNT ON 減少網路往返。
• 大量操作分批次 (如每 5000 列 Commit)，避免長交易鎖定與記錄檔暴增。
• 分頁查詢改用 Keyset Pagination (WHERE id > @last_id ORDER BY id LIMIT 20)。

五、監控與基準建立

• 建立效能基準 (PerfMon: Buffer Cache Hit Ratio, Page Life Expectancy, Batch Requests/sec, SQL Compilations/sec, Lock Waits/sec)。
• 啟用 Query Store (SQL 2016+) 追蹤計畫退化。
• 定期檢視 sys.dm_exec_query_stats、sys.dm_exec_requests、sys.dm_os_wait_stats 找出瓶頸等待類型。

優化是持續迭代過程：測量 → 分析 → 調整 → 驗證 → 重複。