SQL2005索引
正在扫描 SQL2005 数据库，看到表有以下两个索引：
**PK_CLUSTERED_INDEX**
USER_ID
COMPANY_ID
DEPARTMENT_ID
**NON-unique_NON-clustered_INDEX**
USER_ID
COMPANY_ID
我最初的想法是，删除最后一个索引，因为 PK_CLUSTERED_INDEX 已经包含这些列、正确的顺序和排序。最后一个指数有任何收益吗？
Was scanning through a SQL2005 database and saw the following two indexes for a table:
**PK_CLUSTERED_INDEX**
USER_ID
COMPANY_ID
DEPARTMENT_ID
**NON-unique_NON-clustered_INDEX**
USER_ID
COMPANY_ID
My initial thought is, drop the last index since the PK_CLUSTERED_INDEX already contains those columns, correct order and sort. Do the last index provide any gains at all?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
如果按 User_ID 或 User_ID 和 Company_ID 列进行搜索，两个索引都可以满足该要求。
但是，如果除了这 2 个字段之外还查询 Department_Id，则只有 PK 索引才是理想的。
如果查询根据 User_ID 和 Company_ID 进行筛选，并且需要返回其他数据列，那么 PK 索引仍然是最好的，因为它拥有所有可用的数据。而非聚集索引则不然，因此可能需要键查找来提取效率不高的额外字段。
它对我来说看起来多余，所以我肯定会考虑删除它。
要查看索引是否实际被使用/了解使用级别，您可以运行各种索引使用情况统计脚本之一。 此处就是一个很好的例子。
If searching by User_ID, or User_ID and Company_ID columns, both indexes could fulfil that.
However, only the PK index would then be ideal if the Department_Id is also queried on in addition to those 2 fields.
If a query filters on User_ID and Company_ID, and needs to return other data columns then the PK index is still best as it has all the data there to hand. Whereas the nonclustered index doesn't so would probably need a Key Lookup to pull out the extra fields which is not as efficient.
It looks redundant to me, so I'd definitely be considering removing it.
To see whether an index is actually being used/get a feel for the level of usage, you can run one of the various index usage stats scripts out there. A good example is here.
在这种情况下，删除索引，因为它不唯一，我敢打赌优化器永远不会命中它，第一个索引更唯一，并且在找到匹配项后不涉及行查找。
第一个更好，你不会因为放弃第二个而失去任何东西。
In this case, drop the index, given it's non-unique, I would bet the optimizer never hits it, the first index is more unique and doesn't involve a row lookup after finding a match.
First one's all around better, you're not losing anything by dropping the second.
我会删除 NON-unique_NON-clustered_INDEX 索引，它是多余的并且不需要。
I would drop the NON-unique_NON-clustered_INDEX index, it is redundant and not needed.