sql server:必要时在外键上创建索引[英] sql server : create indexes on foreign keys where necessary

本文是小编为大家收集整理的关于sql server:必要时在外键上创建索引的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

我有很多带外国钥匙的桌子,有些则有索引,而另一些则没有. 所有外键均名为FK_<name of the foreign key>,索引名为IX_<name of the foreign key>.

鉴于外键的列基数,是否有一些好的实践来创建(或不创建)索引? 可以将其脚本称为T-SQL命令吗?

推荐答案

是通过T-SQL脚本或设计器创建的,这无关紧要.您的问题有些模棱两可,因此我不确定您是否还询问是否可以为所有外国钥匙索引.但是,如果是,则应在查询中经常引用的列上创建索引,您可以做以下操作以提高性能:

  • 运行数据库调整向导,该向导将提供改进的摘要并推荐索引.

  • 索引所有外键并运行执行计划(查看查询的执行速度更快还是较慢).

通过T-SQL创建索引:

CREATE INDEX IX_INDEX_NAME
ON Table (FieldName); 

获取所有外国密钥的列表:

SELECT f.name AS ForeignKey, 
 OBJECT_NAME(f.parent_object_id) AS TableName, 
 COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ColumnName, 
 OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName, 
 COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS ReferenceColumnName 
FROM sys.foreign_keys AS f 
INNER JOIN sys.foreign_key_columns AS fc 
ON f.OBJECT_ID = fc.constraint_object_id

要生成一个在所有外国密钥上应用索引的脚本,您可以执行此操作:

SELECT 'CREATE INDEX [IX_' + f.name + '] ON ' + OBJECT_NAME(f.parent_object_id) + '(' + COL_NAME(fc.parent_object_id, fc.parent_column_id) + ')]'
FROM sys.foreign_keys AS f 
INNER JOIN sys.foreign_key_columns AS fc 
ON f.OBJECT_ID = fc.constraint_object_id

MS188783.aspx

其他推荐答案

大家做得很好,非常有帮助.

添加包括表格架构的增强功能.如果您愿意,也可以排除FK名称(我倾向于不在小表上添加索引)

SELECT
    * 
FROM 
(
    SELECT TOP 99 PERCENT
            f.name AS ForeignKeyName

        , s.name 
                + '.'
                + OBJECT_NAME(f.parent_object_id) 
                + '.'
                + COL_NAME(fc.parent_object_id, fc.parent_column_id) 
            ParentTable

        , referencedSchema.name
                + '.'
                + OBJECT_NAME (f.referenced_object_id)
                + '.'
                + COL_NAME(fc.referenced_object_id, fc.referenced_column_id)
            ReferencedTable

        , 'CREATE INDEX [IX_' + f.name + ']'
                + ' ON ' 
                    + '[' + referencedSchema.name + ']'
                    + '.'
                    + '[' + OBJECT_NAME(f.parent_object_id) + ']'
                    + '(' 
                        + COL_NAME(fc.parent_object_id, fc.parent_column_id) 
                    + ')'
            CreateIndexSql          

    FROM 
        sys.foreign_keys AS f 
        INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id
        inner join sys.schemas s on f.schema_id = s.schema_id

        inner join sys.tables referencedTable on f.referenced_object_id = referencedTable.object_id
        inner join sys.schemas referencedSchema on referencedTable.schema_id = referencedSchema.schema_id

    ORDER BY
        2, 3, 1 
) a
where a.ParentTable not in (
    -- Add any exclusions here so you can forget about them
      ''
)

本文地址:https://www.itbaoku.cn/post/597714.html

问题描述

I have lots of tables with foreign keys and some have an index while others have not. All foreign keys are named FK_<name of the foreign key> with indexes named IX_<name of the foreign key>.

Are there some good practices, given the column cardinality of the foreign key, to create (or not) indexes ? Could this be scripted as T-SQL commands ?

推荐答案

It does not matter if they are created via a T-SQL Script or via the Designer. Your question is a little ambiguous, so I am unsure if you are also asking if it is okay to index all of the foreign keys. However, if you are, indexes should be created on columns that are referenced frequently in queries and you can do the following to improve performance:

  • Run the database tuning wizard which will supply a summary of improvements and recommend indexes.

  • Index all of the foreign keys and run the execution plan (To see if queries are performing faster or slower).

To create an index via T-SQL:

CREATE INDEX IX_INDEX_NAME
ON Table (FieldName); 

To get a list of all Foreign keys:

SELECT f.name AS ForeignKey, 
 OBJECT_NAME(f.parent_object_id) AS TableName, 
 COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ColumnName, 
 OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName, 
 COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS ReferenceColumnName 
FROM sys.foreign_keys AS f 
INNER JOIN sys.foreign_key_columns AS fc 
ON f.OBJECT_ID = fc.constraint_object_id

To generate a script that applies indexes across all foreign keys you could do this:

SELECT 'CREATE INDEX [IX_' + f.name + '] ON ' + OBJECT_NAME(f.parent_object_id) + '(' + COL_NAME(fc.parent_object_id, fc.parent_column_id) + ')]'
FROM sys.foreign_keys AS f 
INNER JOIN sys.foreign_key_columns AS fc 
ON f.OBJECT_ID = fc.constraint_object_id

http://msdn.microsoft.com/en-us/library/ms188783.aspx

其他推荐答案

Great work everyone, very helpful.

Adding an enhancement that includes the table schema. You can also exclude FK names if you prefer (I tend not to add indexes on small tables)

SELECT
    * 
FROM 
(
    SELECT TOP 99 PERCENT
            f.name AS ForeignKeyName

        , s.name 
                + '.'
                + OBJECT_NAME(f.parent_object_id) 
                + '.'
                + COL_NAME(fc.parent_object_id, fc.parent_column_id) 
            ParentTable

        , referencedSchema.name
                + '.'
                + OBJECT_NAME (f.referenced_object_id)
                + '.'
                + COL_NAME(fc.referenced_object_id, fc.referenced_column_id)
            ReferencedTable

        , 'CREATE INDEX [IX_' + f.name + ']'
                + ' ON ' 
                    + '[' + referencedSchema.name + ']'
                    + '.'
                    + '[' + OBJECT_NAME(f.parent_object_id) + ']'
                    + '(' 
                        + COL_NAME(fc.parent_object_id, fc.parent_column_id) 
                    + ')'
            CreateIndexSql          

    FROM 
        sys.foreign_keys AS f 
        INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id
        inner join sys.schemas s on f.schema_id = s.schema_id

        inner join sys.tables referencedTable on f.referenced_object_id = referencedTable.object_id
        inner join sys.schemas referencedSchema on referencedTable.schema_id = referencedSchema.schema_id

    ORDER BY
        2, 3, 1 
) a
where a.ParentTable not in (
    -- Add any exclusions here so you can forget about them
      ''
)