萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> SQL Server 教學

影響 SQL Server 效能的關鍵設計原則:邏輯模型、索引策略與查詢模式

資料庫效能 80% 決定於設計階段。本文針對邏輯模型正規化、索引設計原則、查詢模式優化三大支柱,提供可落地的設計指導。

一、邏輯資料庫與資料表設計

正規化的效能紅利

  • 窄表優於寬表:更多列的表 → 更多頁面 → 掃描/排序/索引建立更慢
  • 窄表 = 更緊湊索引:索引鍵越小,非葉層級越少,搜尋越快
  • 多表 = 更多索引機會:各表可建針對性索引,避免萬能寬索引
  • 少索引 = 寫入更快:每個索引皆需維護,INSERT/UPDATE/DELETE 成本線性增長
  • 減少 NULL 與冗餘:正規化消除重複資料,提升緊湊度

過度正規化的代價

  • 查詢需 JOIN 更多表 → CPU、I/O、鎖定開銷指數級增長
  • 建議:3NF 為基準,視查詢模式適度反正規化 (加入衍生欄位、物化視圖、摘要表)

主鍵/外來鍵設計鐵律

  • 連接鍵**資料型別、長度、排序規則必須完全一致**
  • 優先使用整數代理鍵 (IDENTITY/SEQUENCE),避免業務鍵變更連鎖更新
  • 外來鍵建立索引 (FK 欄位自動不建索引),否則刪除父表時全表掃描子表

二、索引設計黃金法則

核心目標:避免 Table Scan / Clustered Index Scan

-- 檢查查詢計畫是否出現 Scan
SET STATISTICS IO ON;
SELECT ... WHERE Col1 = @val;  -- 期望 Index Seek

單欄索引 vs 複合索引

  • WHERE Col1 = @val → 單欄索引 Col1 或複合索引 (Col1, ...) 且 **Col1 為首欄**
  • WHERE Col1 = @val AND Col2 = @val2 → 複合索引 (Col1, Col2) 或 (Col2, Col1) 視選擇性定
  • 複合索引順序 = WHERE/JOIN/ORDER BY/GROUP BY 欄位出現順序與選擇性綜合決定

實例:pubs.authors 表

SELECT au_id, au_lname, au_fname FROM authors WHERE au_lname = 'White';
索引定義優化器用途
au_lname✅ Seek
au_lname, au_fname✅ Seek (覆蓋索引)
au_address❌ Scan
au_fname, au_lname❌ Scan (首欄非篩選條件)

進階索引策略

  1. 包含欄位 (INCLUDE):非鍵欄位納入葉層,實現覆蓋索引,避免 Key Lookup
  2. 篩選索引:WHERE Status = 'Active' 只索引熱門資料,縮小體積
  3. 壓縮索引:DATA_COMPRESSION = PAGE 節省空間與 I/O
  4. 避免過度索引:單表 ≤ 5-8 個,定期用 sys.dm_db_index_usage_stats 清理未用索引

三、統計資料與查詢模式

  • 自動建立/更新統計資料預設開啟,**大表需手動 UPDATE STATISTICS ... WITH FULLSCAN**
  • 參數嗅探:首次編譯參數值決定計畫,後續不同參數可能不適 → OPTION (RECOMPILE) / OPTIMIZE FOR UNKNOWN / 計畫指南
  • 動態 SQL 使用 sp_executesql 參數化,促進計畫重用

設計即決策。在正規化與反正規化、索引數量與寫入速度、編譯時間與執行時間間找到業務最佳平衡點。

copyright © 萬盛學電腦網 all rights reserved