萬盛學電腦網

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

SQL Server 查詢資料表索引完整指令碼與建立原則

系統檢視表查詢單一資料表索引細節

適用 SQL Server 2005 以上版本,輸出:索引名稱、資料表、索引欄位、欄位順序、排序方向、是否主鍵、是否唯一、Fill Factor 等。

DECLARE @TableName SYSNAME = N'cz201';  -- 修改為目標表名

SELECT
    TableName      = OBJECT_NAME(idx.[object_id]),
    IndexName      = idx.name,
    IndexType      = idx.type_desc,
    ColumnName     = col.name,
    ColumnOrder    = idxc.index_column_id,
    SortDirection  = CASE INDEXKEY_PROPERTY(idxc.[object_id], idxc.index_id, idxc.index_column_id, 'IsDescending')
                        WHEN 1 THEN 'DESC' WHEN 0 THEN 'ASC' ELSE '' END,
    IsPrimaryKey   = CASE WHEN idx.is_primary_key = 1 THEN N'✓' ELSE N'' END,
    IsUnique       = CASE WHEN idx.is_unique = 1 THEN N'✓' ELSE N'' END,
    IgnoreDupKey   = CASE WHEN idx.ignore_dup_key = 1 THEN N'✓' ELSE N'' END,
    IsDisabled     = CASE WHEN idx.is_disabled = 1 THEN N'✓' ELSE N'' END,
    FillFactor     = idx.fill_factor,
    IsPadded       = 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
INNER JOIN sys.columns col
    ON idxc.[object_id] = col.[object_id] AND idxc.column_id = col.column_id
INNER JOIN sys.objects obj
    ON obj.[object_id] = idx.[object_id]
WHERE obj.type = 'U'                     -- 使用者資料表
  AND obj.is_ms_shipped = 0              -- 排除系統表
  AND obj.name = @TableName
  AND idx.index_id > 0                  -- 排除堆疊
ORDER BY idx.name, idxc.index_column_id;

舊版相容查詢(SQL 2000 風格,僅供參考)

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.name = 'message';  -- 指定表名

Oracle 查詢使用者表索引

SELECT index_name, index_type, table_name
FROM user_indexes
WHERE table_name = UPPER('你的表名');

索引建立與維護黃金法則

情況建議
頻繁出現在 WHERE/JOIN/ORDER BY 的欄位建立索引(單欄或複合)
外鍵欄位必須建立索引,避免子表鎖定父表
高選擇性(唯一值多)欄位適合 B-Tree 索引
低選擇性(如性別、狀態碼)不建立索引,全表掃描更快
頻繁 UPDATE 的欄位避免納入索引,增加寫入開銷
大型文字/二進位欄位使用全文索引或檔案流,勿建一般索引

索引維護作業

  • 定期重建/重組碎片:ALTER INDEX ... REBUILD / REORGANIZE。
  • 監控 sys.dm_db_index_physical_stats 碎片率 > 30% 重建,10-30% 重組。
  • 更新統計資料:UPDATE STATISTICS 或啟用 AUTO_UPDATE_STATISTICS。
copyright © 萬盛學電腦網 all rights reserved