SQL Server 索引查詢完全手冊：舊系統表 vs 新版 DMV、Oracle 對照、建索引判斷準則

一、舊版系統表查詢 (SQL 2000/2005 相容，不推薦新專案)

SELECT
    索引名稱 = a.name,
    表名 = c.name,
    索引欄位 = d.name,
    欄位位置 = d.colid
FROM sysindexes a
JOIN sysindexkeys b ON a.id = b.id AND a.indid = b.indid
JOIN sysobjects c ON b.id = c.id
JOIN syscolumns d ON b.id = d.id AND b.colid = d.colid
WHERE a.indid NOT IN (0, 255)      -- 排除堆表/文字圖像
  AND c.xtype = 'U'                -- 僅使用者表
  AND c.name = 'message';          -- 指定表名

二、新版 DMV 查詢 (SQL 2005+ 標準，資訊完整、效能佳)

SELECT
    TableId = O.[object_id],
    TableName = O.Name,
    IndexId = ISNULL(KC.[object_id], IDX.index_id),
    IndexName = IDX.Name,
    IndexType = ISNULL(KC.type_desc, 'Index'),
    Index_Column_id = IDXC.index_column_id,
    ColumnID = C.column_id,
    ColumnName = C.Name,
    Sort = CASE INDEXKEY_PROPERTY(IDXC.[object_id], IDXC.index_id, IDXC.index_column_id, 'IsDescending')
        WHEN 1 THEN 'DESC' WHEN 0 THEN 'ASC' ELSE '' END,
    PrimaryKey = CASE WHEN IDX.is_primary_key = 1 THEN N'√' ELSE N'' END,
    [Unique] = CASE WHEN IDX.is_unique = 1 THEN N'√' ELSE N'' END,
    Ignore_dup_key = CASE WHEN IDX.ignore_dup_key = 1 THEN N'√' ELSE N'' END,
    Disabled = CASE WHEN IDX.is_disabled = 1 THEN N'√' ELSE N'' END,
    Fill_factor = IDX.fill_factor,
    Padded = CASE WHEN IDX.is_padded = 1 THEN N'√' ELSE N'' END
FROM sys.indexes IDX
INNER JOIN sys.index_columns IDXC ON IDX.[object_id] = IDXC.[object_id] AND IDX.index_id = IDXC.index_id
LEFT JOIN sys.key_constraints KC ON IDX.[object_id] = KC.[parent_object_id] AND IDX.index_id = KC.unique_index_id
INNER JOIN sys.objects O ON O.[object_id] = IDX.[object_id]
INNER JOIN sys.columns C ON O.[object_id] = C.[object_id] AND IDXC.column_id = C.column_id
WHERE O.type = 'U' AND O.is_ms_shipped = 0
  AND O.name = 'cz201';  -- 目標表名

輸出欄位解讀

• `IndexType`：`CLUSTERED` / `NONCLUSTERED` / `PRIMARY_XML` / `SPATIAL`...
• `Sort`：欄位在索引內排序方向。
• `PrimaryKey` / `Unique` / `Disabled` / `Padded`：關鍵屬性一目瞭然。
• `Fill_factor`：頁面填滿度，0/100=預設滿頁。

三、Oracle 索引查詢

SELECT index_name, index_type, table_name
FROM user_indexes
WHERE table_name = 'YOUR_TABLE';

• 進階：`USER_IND_COLUMNS` 連查欄位順序、`USER_CONSTRAINTS` 連查主鍵/唯一約束。

四、建立索引判斷準則 (何時建、何時不建)

✅ 必須/強烈建議建立索引

1. **主鍵 / 唯一約束**：自動建立唯一索引 (叢集/非叢集)。
2. **外鍵欄位**：`JOIN`、`DELETE` 父表驗證子表必須求助索引，否則全表掃描鎖升級。
3. **WHERE / JOIN / ORDER BY / GROUP BY 高頻欄位**：查詢計畫出現 `Index Seek` / `Scan` 且成本高。
4. **覆蓋索引**：`INCLUDE` 查詢所需非鍵欄位，消除 Key Lookup。
5. **篩選索引** (Filtered Index, SQL 2008+)：`WHERE status = 1` 等固定條件，極大縮小索引體積。

❌ 不建議/禁止建立索引

1. **極低選擇性欄位**：性別 (M/F)、狀態 (0/1/2)、布林型 — 索引無效，優化器選擇掃描。
2. **極少查詢/從不查詢的欄位**：純寫入表 (如日誌、審計) 僅建主鍵/必要外鍵。
3. **大型 LOB 欄位** (`VARCHAR(MAX)`, `VARBINARY(MAX)`)：**不可**直接建索引，需全文/語意索引或計算欄位。
4. **頻繁大量批次更新的欄位**：索引維護成本 > 查詢收益。

⚖️ 審慎評估

• **複合索引欄位順序**：等值條件優先、篩選性高優先、範圍條件最後。
• **索引數量上限**：單表非叢集索引建議 ≤ 5-8 個 (視寫入頻率)，超過需嚴格評估 ROI。
• **填充因子**：高寫入表設 80-90% 減少分頁分裂；唯讀表 100%。

五、索引維護自動化腳本片段

-- 1. 找出未使用索引 (讀取=0, 寫入>0)
SELECT OBJECT_NAME(object_id) AS TableName, name AS IndexName,
       user_seeks, user_scans, user_lookups, user_updates
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID() AND OBJECTPROPERTY(object_id, 'IsUserTable') = 1
  AND user_seeks + user_scans + user_lookups = 0 AND user_updates > 0
ORDER BY user_updates DESC;

-- 2. 碎片重建/重組 (每周排程)
-- 碎片 5-30% → REORGANIZE; >30% → REBUILD (ONLINE=ON)
-- 參考 Ola Hallengren MaintenanceSolution 或 Minion Reindex

總結

**新版 DMV 取代舊系統表**，輸出完整索引屬性 (類型、排序、約束、填充因子)。「高頻查詢/外鍵/覆蓋/篩選」必建，「低選擇性/大LOB/高寫入」不建。「定期掃描未用/碎片」納入維護計畫，索引才能持續創造價值而非成為負擔。