如何在SQL Server中找到未索引的外键

Question

我有一个大约包含220个表的SQL Server 2000数据库，其中这些表之间有多个外键关系。通过性能分析，我们发现其中一些外键关系缺少索引。而我不想只是被动地解决性能问题，我希望主动查找所有缺失索引的外键。

如何以编程方式确定哪些外键缺少索引？

- John Naegle

---

缺失还是很少用？这篇文章写于2005年，但仍然有帮助：http://blogs.msdn.com/sqlcat/archive/2006/02/13/531339.aspx - OMG Ponies

缺失。另一个有趣的性能优化是很少使用，但这不是我在这里感兴趣的。 - John Naegle

Answer 1

SELECT  *
FROM    sys.foreign_keys fk
WHERE   EXISTS
        (
        SELECT  *
        FROM    sys.foreign_key_columns fkc
        WHERE   fkc.constraint_object_id = fk.object_id
                AND NOT EXISTS
                (
                SELECT  *
                FROM    sys.index_columns ic
                WHERE   ic.object_id = fkc.parent_object_id
                        AND ic.column_id = fkc.parent_column_id
                        AND ic.index_column_id = fkc.constraint_column_id
                )
        )

我手头没有 SQL Server 2000 的副本，但是你可能需要更改 sys.foreign_key 为 sysforeignkeys 等，就像这里所描述的那样。

此查询选择所有不具备覆盖组成键的所有列的索引的外键。

这同样支持多列外键。

然而，如果存在一个复合索引覆盖了所有列，但它们不是在该索引中最左边的列，则将返回错误结果。

比如，如果有一个 FOREIGN KEY (col2, col3) 和一个索引 (col1, col2, col3)，那么尽管该索引对于该外键是无法使用的，但此时将会返回该索引存在。

- Quassnoi

---

这可能是我想要的针对SQL Server 2005及更高版本，但我还没有测试过。当我转换它时，我会发布SQL Server 2000的等效版本。 - John Naegle

我也会在 NOT EXISTS 中加入 AND ic.index_column_id = fkc.constraint_column_id，以确保索引也符合正确的索引键列顺序。否则，就跟我日常使用的完全一样。 - gbn

这个不起作用。 - Aheho

Answer 2

这里有一个适用于SQL Server 2000的答案，由一位同事撰写：

/*
Description:
    This script outputs a table with all the current database un-indexed foreign keys.

    The table has three columns ( TableName , ColumnName, ForeignKeyName ) 
    TableName: The table containing the un-indexed foreign key
    ColumnName: The foreign key column that’s not indexed 
    ForeignKeyName: Name of foreign key witch column doesn’t have an index 
    */
DECLARE 
    @TableName varchar(255),
    @ColumnName varchar(255),
    @ForeignKeyName sysname

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

DECLARE FKColumns_cursor CURSOR Fast_Forward FOR
SELECT  cu.TABLE_NAME, cu.COLUMN_NAME, cu.CONSTRAINT_NAME
FROM    INFORMATION_SCHEMA.TABLE_CONSTRAINTS ic 
    INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE cu ON ic.CONSTRAINT_NAME = cu.CONSTRAINT_NAME
WHERE   ic.CONSTRAINT_TYPE = 'FOREIGN KEY'

CREATE TABLE #temp1(    
    TableName varchar(255),
    ColumnName varchar(255),
    ForeignKeyName sysname
)

OPEN FKColumns_cursor  
FETCH NEXT FROM FKColumns_cursor INTO @TableName, @ColumnName, @ForeignKeyName

WHILE @@FETCH_STATUS = 0  
BEGIN

    IF ( SELECT COUNT(*)
    FROM    sysobjects o    
        INNER JOIN sysindexes x ON x.id = o.id
        INNER JOIN  syscolumns c ON o.id = c.id 
        INNER JOIN sysindexkeys xk ON c.colid = xk.colid AND o.id = xk.id AND x.indid = xk.indid
    WHERE   o.type in ('U')
        AND xk.keyno <= x.keycnt
        AND permissions(o.id, c.name) <> 0
        AND (x.status&32) = 0
        AND o.name = @TableName
        AND c.name = @ColumnName
    ) = 0
    BEGIN
        INSERT INTO #temp1 SELECT @TableName, @ColumnName, @ForeignKeyName
    END


    FETCH NEXT FROM FKColumns_cursor INTO @TableName, @ColumnName, @ForeignKeyName
END  
CLOSE FKColumns_cursor  
DEALLOCATE FKColumns_cursor 

SELECT * FROM #temp1 ORDER BY TableName

- John Naegle

Answer 3

在上述代码的基础上进行改进，去除临时表并获取创建索引脚本。

   /*
Description:

    */
DECLARE 
    @SchemaName varchar(255),
    @TableName varchar(255),
    @ColumnName varchar(255),
    @ForeignKeyName sysname

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

DECLARE FKColumns_cursor CURSOR Fast_Forward FOR
SELECT  cu.TABLE_SCHEMA, cu.TABLE_NAME, cu.COLUMN_NAME, cu.CONSTRAINT_NAME
FROM    INFORMATION_SCHEMA.TABLE_CONSTRAINTS ic 
    INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE cu ON ic.CONSTRAINT_NAME = cu.CONSTRAINT_NAME
WHERE   ic.CONSTRAINT_TYPE = 'FOREIGN KEY'

CREATE TABLE #temp1(    
    SchemaName varchar(255),
    TableName varchar(255),
    ColumnName varchar(255),
    ForeignKeyName sysname
)

OPEN FKColumns_cursor  
FETCH NEXT FROM FKColumns_cursor INTO @SchemaName,@TableName, @ColumnName, @ForeignKeyName

WHILE @@FETCH_STATUS = 0  
BEGIN

    IF ( SELECT COUNT(*)
    FROM        sysobjects o    
        INNER JOIN sysindexes x ON x.id = o.id
        INNER JOIN  syscolumns c ON o.id = c.id 
        INNER JOIN sysindexkeys xk ON c.colid = xk.colid AND o.id = xk.id AND x.indid = xk.indid
    WHERE       o.type in ('U')
        AND xk.keyno <= x.keycnt
        AND permissions(o.id, c.name) <> 0
        AND (x.status&32) = 0
        AND o.name = @TableName
        AND c.name = @ColumnName
    ) = 0
    BEGIN
        INSERT INTO #temp1 SELECT @SchemaName, @TableName, @ColumnName, @ForeignKeyName
    END


    FETCH NEXT FROM FKColumns_cursor INTO @SchemaName,@TableName, @ColumnName, @ForeignKeyName
END  
CLOSE FKColumns_cursor  
DEALLOCATE FKColumns_cursor 

SELECT 'CREATE INDEX IDX_' + ForeignKeyName + ' ON ' + SchemaName + '.' + TableName + '(' + ColumnName +')'
FROM #temp1 
ORDER BY TableName

drop table #temp1 

- trainer

Answer 4

在我的帖子"SQL脚本创建外键索引"中，我放了两个实现的链接：paul_nielsen和tklimczak's (需要登录到sqlservercentral)。

- Michael Freidgeim

Answer 5

注意：这是针对SQL Server 2005+的，但这是我在这个主题上找到的唯一问题。

--Finds foreign keys without indexes
--How to interpret:
--When we delete frpm PkTable, it checks FkColumn for the PkId we are deleting.
--So if FkTable doesn't have an index on FkColumn, then we cannot delete a row from PkTable because it is too slow.
SELECT  rt.name as PkTableName, rc.name as PkColumnName,
fk.name FkName, t.name as FkTableName, c.name as FkColumnName, ddps.row_count, i.name as IndexName
FROM    sys.foreign_key_columns fkc
inner join sys.foreign_keys fk on fkc.constraint_object_id = fk.object_id
inner join sys.tables t on fkc.parent_object_id = t.object_id
inner join sys.columns c on fkc.parent_object_id = c.object_id and fkc.parent_column_id = c.column_id
inner join sys.tables rt on fkc.referenced_object_id = rt.object_id
inner join sys.columns rc on fkc.referenced_object_id = rc.object_id and fkc.referenced_column_id = rc.column_id
inner join sys.indexes ri on t.object_id = ri.object_id
inner JOIN sys.dm_db_partition_stats AS ddps ON ri.OBJECT_ID = ddps.OBJECT_ID AND ri.index_id = ddps.index_id 
left join sys.index_columns ic on ic.object_id = t.object_id and ic.column_id = c.column_id
left join sys.indexes i on ic.object_id = i.object_id and i.index_id = ic.index_id
where ri.index_id < 2 and i.index_id is null and ddps.row_count > 0
order by 
--PkTableName,
ddps.row_count desc

- user868538

Answer 6

首先：列出具有外键约束的列。这将有所帮助： 在SQL Server 2000中获取所有外键约束的查询 与sysindexes和syscolumns表进行交叉比较；sysindexes中的keys字段具有索引中所有键的列表。

- Jeremy Smyth