首页 文章

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

提问于
浏览
15

我有一个SQL Server 2000数据库,大约有220个表 . 这些表之间有许多外键关系 . 通过性能分析,我们发现许多这些外键关系都缺少索引 . 我不想对性能问题做出反应,而是积极主动地找到所有缺少索引的外键 .

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

6 回答

  • 0
    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 等,如here所述 .

    此查询选择所有没有索引的外键,该索引覆盖组成键的所有列 .

    这也支持多列外键 .

    但是,如果存在覆盖所有列的复合索引但是它们不是此索引中最左侧的列,则会返回误报 .

    就像,如果在 (col1, col2, col3) 上有一个 FOREIGN KEY (col2, col3) 和一个索引,这将返回有一个索引,尽管该索引对于这个外键是不可用的 .

  • 0

    这是一个适用于由同事创作的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
    
  • 5

    基于上面的代码构建,删除临时表并获取脚本来创建索引 .

    /*
    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
    
  • 7

    在我的帖子"SQL Script to create indexes for Foreign keys"我已经链接到2个实现:paul_nielsentklimczak's (login to sqlservercentral是必需的)

  • 1

    首先:列出具有外键约束的列 . 这将有助于:

    Query to get all foreign key constraints in SQL Server 2000

    sysindexessyscolumns 表交叉比较; sysindexes 中的 keys 字段包含索引中所有键的列表 .

  • 17

    注意:这是针对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
    

相关问题