相对于聚簇索引查找的非聚簇索引功能

the question is quite simple, but we've had so many issues with index/statistics updates not always resulting in the proper new execution plans in low-load environments that I need to check this with you guys here to be sure.

问题很简单，但我们在索引/统计更新方面遇到了很多问题并不总是在低负载环境中产生适当的新执行计划，我需要在这里与您确认这一点。

Say that you have the following tables:

假设您有以下表格：

/*
TABLES: 
TABLE_A (PK_ID INT, [random columns], B_ID INT (INDEXED, and references TABLE_B.PK_ID))
TABLE_B (PK_ID INT, [random columns], C_ID INT (INDEXED, and references TABLE_C.PK_ID))
TABLE_C (PK_ID INT, [random columns])
*/

SELECT *
FROM TABLE_A A
JOIN TABLE_B B ON B.PK_ID = A.B_ID
JOIN TABLE_C C ON C.PK_ID = B.C_ID
WHERE A.randcolumn1 = 'asd' AND B.randcolumn2 <> 5

Now, since B is joined to A with its clustered PK column, shouldn't that mean that the index on B.C_ID will not be used as the information is already returned through the B.PK_ID clustered index? In fact, is it not true that the index on B.C_ID will never be used unless the query specifically targets the ID values on that index?

现在，由于B通过其聚簇PK列连接到A，这不应该意味着不会使用B.C_ID上的索引，因为信息已经通过B.PK_ID聚簇索引返回了吗？事实上，除非查询专门针对该索引上的ID值，否则不会使用B.C_ID上的索引？

This may seem like a simple and even stupid question, but I want to make absolutely sure I'm getting this right. I'm thinking of making adjustments on our indexing, since we have a lot of unused indexes which have been inherited from an old datamodel and they're taking up quite a bit of space in a DB this size. And experience has shown that we cannot fully trust the execution plans on any environment apart from the production thanks to its extreme load compared to testing environments, which makes it difficult to test this out reliably.

这可能看起来像一个简单甚至是愚蠢的问题，但我想确保我做对了。我正在考虑对我们的索引进行调整，因为我们有许多未使用的索引从旧的数据模型中继承而且它们在这个大小的DB中占用了相当多的空间。经验表明，与测试环境相比，由于其极端负载，我们无法完全信任除生产之外的任何环境的执行计划，这使得难以可靠地测试它。

Thanks!

谢谢！

1 个解决方案

#1

The query optimizer is free to do as it pleases. It could execute the second join by scanning the C table, and for each row, looking up the matching row in B. The index you describe would help with that lookup.

查询优化器可以随意使用。它可以通过扫描C表来执行第二个连接，并且对于每一行，查找B中的匹配行。您描述的索引将有助于该查找。

SQL Server provides statistics to tell you if an index is actually used:

SQL Server提供统计信息以告诉您是否实际使用了索引：

select  db_name(ius.database_id) as Db
,       object_name(ius.object_id) as [Table]
,       max(ius.last_user_lookup) as LastLookup
,       max(ius.last_user_scan) as LastScan
,       max(ius.last_user_seek) as LastSeek
,       max(ius.last_user_update) as LastUpdate
from    sys.dm_db_index_usage_stats as ius
where   ius.[database_id] = db_id()
        and ius.[object_id] = object_id('YourTableName')
group by 
        ius.database_id
,       ius.object_id

If the index isn't used for more than 2 months, it is usually safe to drop it.

如果索引的使用时间不超过2个月，通常可以放心使用。

#1