使用包含列的聚集索引或非聚集索引更好? [英] Better to use a Clustered index or a Non-Clustered index with included columns?

问题描述

当我查看特定查询的执行计划时，我发现 77% 的成本是在聚集索引查找中.

When I look at the execution plan for a particular query I find that 77% of my cost is in a Clustered Index seek.

我使用聚集索引的事实是否意味着我不会看到由于我输出的列而导致的性能问题?

Does the fact that I'm using a clustered index mean that I won't see performance issues due to the columns that I am outputting?

创建一个非集群版本并包含所有正在输出的列对我来说会更好吗?

Would it be better for me to create a Non-Clustered version of this and include all of the columns that are being output?

更新:聚集索引使用组合键.不确定这是否有影响.

UPDATE: The clustered index uses a composite key. Not sure if this makes a difference.

推荐答案

在非聚集索引上使用包含列的原因是为了避免对聚集数据进行书签查找".问题是，如果 SQL Server 理论上可以使用特定的非聚集索引，但优化器估计会有太多"的书签查找，那么该索引将被忽略.但是，如果所有选定的列都可以直接从索引访问，则不需要进行书签查找.

The reason you use include columns on a non-clustered index is to avoid "bookmark-lookups" into the clustered data. The thing is that if SQL Server could theoretically use a particular non-clustered index but the Optimiser estimates there will be 'too many' bookmark-lookups then said index will be ignored. However, if all selected columns are accessible directly from the index, there'll be no need for a bookmark-lookup.

在您的情况下，您通过聚集索引查找"访问数据这一事实非常有希望.提高其性能将非常困难.包含所有选定列的非聚集索引可能会稍微快一点，但这仅仅是因为原始数据少一点.(但不要忘记增加插入/更新时间的成本.)

In your case the fact that you're accessing the data via "clustered index seek" is very promising. It will be very hard to improve on its performance. A non-clustered index including all selected columns may be slightly faster, but only because the raw data is a little less. (But don't forget the cost of increased insert/update time.)

但是，您应该检查详细信息...

However, you should check the detail...

• 如果您使用的是复合键并且查找实际上只是在键的开头，那么您可能就没有那么幸运了.您可能会发现搜索仅缩小到 500,000 行，然后根据其他条件进行搜索.在这种情况下，请尝试使用一些非聚集索引.
• 聚集索引本身可能没问题；但是，如果在您的查询中执行 100,000 次，因为某些其他方面效率低下返回太多行 - 那么您将不会通过提高聚集索引查找的性能获得多少收益.

最后，详细说明 davek 的评论:成本是相对的".仅仅因为集群占查询成本的 77% 并不意味着存在问题.可以编写一个简单的 1 表查询，以 100% 的速度返回单个行和聚集索引查找成本.(但当然，作为唯一完成的工作"，它将 100% 的工作......而且 100% 的即时仍然是即时.
所以:别担心；要开心！"

Finally, to elaborate on davek's comment: "cost is relative". Just because the clustered is 77% of your query cost doesn't mean there's a problem. It is possible to write a trivial 1 table query that returns a sinlge row and clustered index seek cost at 100%. (But of course, being the only 'work' done, it will be 100% of the work... and 100% of instant is still instant.
So: "Don't worry; be happy!"

这篇关于使用包含列的聚集索引或非聚集索引更好?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！