用于查找冗余索引的 T-SQL

2021-12-21 00:00:00 indexing tsql sql-server

有人知道可以检测整个数据库中冗余索引的 T-SQL 脚本吗?表中冗余索引的示例如下:

Is anyone aware of a T-SQL script that can detect redundant indexes across an entire database? An example of a redundant index in a table would be as follows:

Index 1: 'ColumnA', 'ColumnB', 'ColumnC'
Index 2: 'ColumnA', 'ColumnB'

忽略其他考虑因素，例如列宽和覆盖索引，索引 2 将是多余的.

Ignoring other considerations, such as the width of columns and covering indexes, Index 2 would be redundant.

谢谢.

推荐答案

有些情况下冗余不成立.例如，假设 ColumnC 是一个巨大的字段，但有时您必须快速检索它.您的 index 1 不需要键查找:

There are situations where the redundancy doesn't hold. For example, say ColumnC was a huuge field, but you'd sometimes have to retrieve it quickly. Your index 1 would not require a key lookup for:

select ColumnC from YourTable where ColumnnA = 12

另一方面，index 2要小得多，因此可以在内存中读取需要索引扫描的查询:

On the other hand index 2 is much smaller, so it can be read in memory for queries that require an index scan:

select * from YourTable where ColumnnA like '%hello%'

所以它们并不是真的多余.

So they're not really redundant.

如果您不相信我的上述论点，您可以找到冗余"索引，例如:

If you're not convinced by my above argument, you can find "redundant" indexes like:

;with ind as (
    select  a.object_id
    ,       a.index_id
    ,       cast(col_list.list as varchar(max)) as list
    from    (
            select  distinct object_id
            ,       index_id
            from    sys.index_columns
            ) a
    cross apply
            (
            select  cast(column_id as varchar(16)) + ',' as [text()]
            from    sys.index_columns b
            where   a.object_id = b.object_id
                    and a.index_id = b.index_id
            for xml path(''), type
            ) col_list (list)
)
select  object_name(a.object_id) as TableName
,       asi.name as FatherIndex
,       bsi.name as RedundantIndex
from    ind a
join    sys.sysindexes asi
on      asi.id = a.object_id
        and asi.indid = a.index_id
join    ind b
on      a.object_id = b.object_id
        and a.object_id = b.object_id
        and len(a.list) > len(b.list)
        and left(a.list, LEN(b.list)) = b.list
join    sys.sysindexes bsi
on      bsi.id = b.object_id
        and bsi.indid = b.index_id

为您的用户带来蛋糕，以防性能意外"下降:-)

Bring cake for your users in case performance decreases "unexpectedly" :-)

相关文章