您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

SQL Server 索引管理實用視圖：發現遺漏叢集索引、主鍵、冗餘與倒序索引

編輯：關於SqlServer

來源與部署

整理自 SQLServerCentral 與 SQL-Server-Performance 社群共享腳本。需在每個欲檢測的資料庫建立視圖，再執行附帶查詢。

視圖一：`INDEXVIEW`（全方位索引概覽）

CREATE VIEW dbo.INDEXVIEW
AS
SELECT 
    t.name AS TableName,
    i.name AS IndexName,
    i.index_id AS IndexID,
    i.type_desc AS IndexType,
    i.is_unique AS IsUnique,
    i.is_primary_key AS IsPrimaryKey,
    i.fill_factor AS FillFactor,
    c.name AS ColName1,
    c2.name AS ColName2,
    c3.name AS ColName3,
    CASE WHEN i.type_desc = 'HEAP' THEN 'HEAP' ELSE 'CLUSTERED' END AS ClusterType,
    CASE WHEN i.is_primary_key = 1 THEN 'PRIMARY KEY' 
         WHEN i.is_unique = 1 THEN 'UNIQUE' ELSE 'NONUNIQUE' END AS UniqueType,
    OBJECT_ID(t.name) AS TableID
FROM sys.tables t
INNER JOIN sys.indexes i ON t.object_id = i.object_id
LEFT JOIN sys.index_columns ic1 ON i.object_id = ic1.object_id AND i.index_id = ic1.index_id AND ic1.key_ordinal = 1
LEFT JOIN sys.columns c ON ic1.object_id = c.object_id AND ic1.column_id = c.column_id
LEFT JOIN sys.index_columns ic2 ON i.object_id = ic2.object_id AND i.index_id = ic2.index_id AND ic2.key_ordinal = 2
LEFT JOIN sys.columns c2 ON ic2.object_id = c2.object_id AND ic2.column_id = c2.column_id
LEFT JOIN sys.index_columns ic3 ON i.object_id = ic3.object_id AND i.index_id = ic3.index_id AND ic3.key_ordinal = 3
LEFT JOIN sys.columns c3 ON ic3.object_id = c3.object_id AND ic3.column_id = c3.column_id
WHERE i.type_desc <> 'HEAP' OR i.type_desc = 'HEAP';  -- 保留 HEAP 以便偵測無叢集索引表
GO

檢測查詢套件（建立視圖後執行）

1. 遺漏叢集索引的表（HEAP 表）

SELECT 'Tables Missing Clustered Index' AS CheckItem, *
FROM dbo.INDEXVIEW
WHERE ClusterType = 'HEAP';

2. 遺漏主鍵的表

SELECT 'Tables Missing Primary Key' AS CheckItem, I.*
FROM dbo.INDEXVIEW I
LEFT JOIN dbo.INDEXVIEW I2 
    ON I.TableID = I2.TableID AND I2.UniqueType = 'PRIMARY KEY'
WHERE I2.TableID IS NULL;

3. 疑似冗餘索引（首欄相同、索引名不同）

SELECT 'Possible Redundant Index Keys' AS CheckItem, I.*
FROM dbo.INDEXVIEW I
JOIN dbo.INDEXVIEW I2 
    ON I.TableID = I2.TableID 
   AND I.ColName1 = I2.ColName1 
   AND I.IndexName <> I2.IndexName
ORDER BY I.TableName, I.IndexName;

4. 疑似倒序索引（前兩欄互換）

SELECT 'Possible Reverse Index Keys' AS CheckItem, I.*
FROM dbo.INDEXVIEW I
JOIN dbo.INDEXVIEW I2 
    ON I.TableID = I2.TableID 
   AND I.ColName1 = I2.ColName2 
   AND I.ColName2 = I2.ColName1 
   AND I.IndexName <> I2.IndexName;

視圖二：`vw_index_list`（專注重複索引偵測）

CREATE VIEW dbo.vw_index_list
AS
SELECT 
    OBJECT_SCHEMA_NAME(i.object_id) AS schema_name,
    OBJECT_NAME(i.object_id) AS table_name,
    i.name AS index_name,
    i.type_desc AS index_type,
    i.is_unique,
    i.is_primary_key,
    STUFF((SELECT ', ' + c.name
           FROM sys.index_columns ic
           JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
           WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.is_included_column = 0
           ORDER BY ic.key_ordinal
           FOR XML PATH('')), 1, 2, '') AS key_columns,
    STUFF((SELECT ', ' + c.name
           FROM sys.index_columns ic
           JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
           WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.is_included_column = 1
           ORDER BY ic.index_column_id
           FOR XML PATH('')), 1, 2, '') AS included_columns
FROM sys.indexes i
WHERE i.object_id > 100 AND i.type_desc <> 'HEAP';
GO

重複索引檢測查詢

SELECT 
    t.table_name,
    t.constraint_name,
    i.index_name,
    t.constraint_type
FROM information_schema.table_constraints t
INNER JOIN dbo.vw_index_list i 
    ON t.constraint_name = i.index_name
WHERE t.constraint_type IN ('PRIMARY KEY', 'UNIQUE');