索引設計黃金法則

1. 索引數量控制

OLTP 核心表 建議不超過 6 個索引（含聚簇索引）。每個索引增加寫入開銷（INSERT/UPDATE/DELETE 需同步維護 B+ 樹），過多索引反而拖垮寫性能。

2. 聚簇索引選擇原則

• 窄：鍵值越小越好（INT/BIGINT > CHAR(36) UUID）。
• 唯一：避免唯一化器（uniquifier）額外開銷。
• 不常更新：聚簇鍵更新等同於刪除+插入，導致頁分裂。
• 有序增長：自增 ID / 序列號 / 時間戳，減少頁分裂與碎片。

最佳實踐：ID BIGINT IDENTITY(1,1) PRIMARY KEY CLUSTERED。

3. 非聚簇索引設計

• 覆蓋索引：INCLUDE 非鍵列，避免回表查詢（Key Lookup）。
• 聯合索引順序：WHERE a=1 AND b=2 ORDER BY c → 索引 (a, b, c) 或 (a, b) INCLUDE (c)。
  原則：等值條件在前、範圍條件在後、排序列最後。
• 選擇性：高選擇性列（唯一值多）放前面；低選擇性（性別、狀態）單獨建索引效果差，考慮過濾索引。
• 過濾索引：CREATE INDEX idx_status ON orders(status) WHERE status IN (1,2); 大幅縮小索引體積。

4. 避免索引失效的常見模式

反模式	原因	修正
WHERE col + 1 = 10	列參與運算/函數，SARGable 失效	WHERE col = 9
WHERE SUBSTRING(name,1,3)='ABC'	函數作用於列	WHERE name LIKE 'ABC%'
WHERE ISNULL(col,'') = ''	函數包裹	WHERE col IS NULL OR col = ''
WHERE col LIKE '%keyword%'	前導通配符	全文索引 / 反轉索引 / 搜索引擎
聯合索引跳過首列	索引 (a,b,c) 查 WHERE b=1	補全首列或建獨立索引
隱式轉換	WHERE varchar_col = 123	參數類型與列定義一致

索引維護體系

1. 碎片檢測與重建/重組閾值

SELECT 
  OBJECT_NAME(ips.object_id) AS table_name,
  i.name AS index_name,
  ips.index_type_desc,
  ips.avg_fragmentation_in_percent,
  ips.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
WHERE ips.page_count > 1000  -- 忽略小表
ORDER BY ips.avg_fragmentation_in_percent DESC;

• 5% ~ 30%：ALTER INDEX ... REORGANIZE（在線、單線程、輕量）。
• > 30%：ALTER INDEX ... REBUILD（可 ONLINE=ON、重建統計信息）。
• < 5%：無需處理。

2. 統計信息自動/手動更新

• AUTO_UPDATE_STATISTICS ON（默認）：閾值約 20% + 500 行修改觸發異步更新。
• 大表/關鍵查詢：UPDATE STATISTICS tbl idx WITH FULLSCAN 定時任務（維護窗口）。
• 升級/大批量導入後：sp_updatestats 或全庫 UPDATE STATISTICS。

3. 定期審視未用/重複索引

-- 未用索引（需結合業務周期，謹慎刪除）
SELECT 
  OBJECT_NAME(s.object_id) AS table_name,
  i.name AS index_name,
  s.user_seeks, s.user_scans, s.user_lookups, s.user_updates
FROM sys.dm_db_index_usage_stats s
JOIN sys.indexes i ON s.object_id = i.object_id AND s.index_id = i.index_id
WHERE s.database_id = DB_ID()
  AND i.is_primary_key = 0 AND i.is_unique_constraint = 0
  AND (s.user_seeks + s.user_scans + s.user_lookups) = 0
  AND s.user_updates > 0;  -- 只有維護開銷，無讀收益

-- 重複索引（鍵列前綴完全相同）
-- 可用 sp_helpindex 或第三方腳本檢測

強制索引與計劃穩定

-- 強制使用指定索引（謹慎使用，優先優化查詢/統計信息）
SELECT * FROM orders WITH (INDEX(idx_cust_date)) WHERE customer_id = 100 AND order_date > '2024-01-01';

-- 計劃指南 / 查詢存儲 強制計劃（企業版功能）

特殊索引類型速查

類型	適用場景	限制
列存索引 (Columnstore)	數據倉庫、大表掃描聚合	不支持單行查找、更新開銷大
全文索引	文本搜索、語義查詢	需全文目錄、維護較重
空間索引	地理/幾何數據	僅支持幾何/地理類型
XML 索引	XML 列路徑查詢	體積大、維護複雜
優化索引 (Filtered)	稀疏值、軟刪除標記	查詢必須精確匹配過濾條件

索引是「雙刃劍」：讀快寫慢、佔空間、需維護。遵循「按需創建、覆蓋查詢、定期整理、清理無用」八字方針，讓索引真正成為性能加速器而非負擔。