当前位置：文江博客话题详情

我应该怎样做才能获得聚集索引查找而不是聚集索引扫描？

发布于 2024-08-03 22:16:13 字数 161 浏览 5 评论 0原文

我在 SQL Server 2005 中有一个存储过程，当我运行它并查看它的执行计划时，我注意到它正在执行聚集索引扫描，这花费了 84% 的成本。我读到我必须修改一些东西才能在那里进行聚集索引搜索，但我不知道要修改什么。

我将不胜感激任何帮助。

谢谢，

布莱恩

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

各自安好 2024-08-10 22:16:13

如果没有任何细节，很难猜测问题是什么，甚至根本无法猜测是否是问题。选择扫描而不是查找可能由许多因素驱动：

查询表示覆盖整个表的结果集。 IE。该查询是一个简单的SELECT * FROM 。这是一个简单的情况，可以通过聚集索引扫描完美地覆盖，无需考虑其他任何事情。
优化器没有其他选择：
- 查询表示整个表的子集，但过滤谓词针对不属于聚集键的列，并且这些列上也没有非聚集索引。除了全面扫描之外，这些不是替代计划。
- 查询对聚集索引键中的列有过滤谓词，但它们不是SARGable 。过滤谓词通常需要重写以使其可SARGable，正确的重写取决于具体情况。由于隐式转换规则，可能会出现更微妙的问题，例如。过滤谓词为 WHERE column = @value，但列为 VARCHAR (Ascii)，@value 为 NVARCHAR (Unicode)。
- 查询对聚集键中的列具有 SARGale 过滤谓词，但最左边的列未被过滤。 IE。聚集索引位于列 (foo, bar) 上，但 WHERE 子句仅位于 bar 上。
优化器选择扫描。
- 当替代方案是非聚集索引时，然后进行扫描（或范围查找），但选择使用聚集索引，则原因通常可以追溯到索引临界点由于查询投影缺乏非聚集索引覆盖。请注意，这不是您的问题，因为您期望聚集索引查找，而不是非聚集索引查找（假设问题 100% 准确并有记录......）
- 基数估计。查询成本估计基于聚集索引键统计信息，该统计信息提供结果基数的估计（即有多少行将匹配）。在简单查询上这种情况不会发生，因为无论统计数据有多么偏差，对搜索或范围搜索的任何估计都会低于扫描的估计，但在带有连接的复杂查询上并在多个表上进行过滤，事情会更加复杂，并且计划可能包括预期查找的扫描，因为查询优化器可能会选择连接评估顺序与观察者期望相反的计划。相反的顺序选择可能是正确的（大多数时候），也可能是有问题的（通常是由于统计数据过时或参数嗅探）。
- 订购保证。扫描将以保证的顺序产生结果，并且执行树上较高的元素可以从该顺序中受益（例如，可以消除排序或假脱机，或者可以使用合并连接来代替散列/嵌套连接）。总体而言，由于选择了明显较慢的访问路径，查询成本更好。

这些是一些快速提示，说明为什么在预期聚集索引查找时可能会出现聚集索引扫描。这个问题非常笼统，除了依靠 8 号球之外不可能给出“为什么”的答案。现在，如果我认为您的问题被正确记录并正确表达，那么期望聚集索引seek意味着您正在基于聚集键值搜索唯一记录。在这种情况下，问题必须出在 WHERE 子句的 SARGability 上。

W/o any detail is hard to guess what the problem is, and even whether is a problem at all. The choice of a scan instead of a seek could be driven by many factors:

The query expresses a result set that covers the entire table. Ie. the query is a simple SELECT * FROM <table>. This is a trivial case that would be perfectly covered by a clustred index scan with no need to consider anything else.
The optimizer has no alternatives:
- the query expresses a subset of the entire table, but the filtering predicate is on columns that are not part of the clustered key and there are no non-clustred indexes on those columns either. These is no alternate plan other than a full scan.
- The query has filtering predicates on columns in the clustred index key, but they are not SARGable. The filtering predicate usually needs to be rewritten to make it SARGable, the proper rewrite depends from case to case. A more subtle problem can appear due to implicit conversion rules, eg. the filtering predicate is WHERE column = @value but column is VARCHAR (Ascii) and @value is NVARCHAR (Unicode).
- The query has SARGale filtering predicates on columns in the clustered key, but the leftmost column is not filtered. Ie. clustred index is on columns (foo, bar) but the WHERE clause is on bar alone.
The optimizer chooses a scan.
- When the alternative is a non-clustered index then scan (or range seek) but the choice is a to use the clustered index the cause can be usually tracked down to the index tipping point due to lack of non-clustered index coverage for the query projection. Note that this is not your question, since you expect a clustered index seek, not a non-clustred index seek (assumming the question is 100% accurate and documented...)
- Cardinality estimates. The query cost estimate is based on the clustered index key(s) statistics which provide an estimate of the cardinality of the result (ie. how many rows will match). On a simple query This cannot happen, as any estimate for a seek or range seek will be lower than the one for a scan, no matter how off the statistics are, but on a complex query, with joins and filters on multiple tables, things are more complex and the plan may include a scan where a seek was expected because the query optimizer may choose plan on which the join evaluation order is reversed to what the observer expects. The reverse order choice may e correct (most times) or may be problematic (usually due to statistics being obsolete or to parameter sniffing).
- An ordering guarantee. A scan will produce results in a guaranteed order and elements higher on the execution tree may benefit from this order (eg. a sort or spool may be eliminated, or a merge join can be used instead of hash/nested joins). Overall the query cost is better as a result of choosing an apparently slower access path.

These are some quick pointers why a clustered index scan may be present when a clustered index seek is expected. The question is extremly generic and is impossible to give an answer 'why', other than relying on an 8 ball. Now if I take your question to be properly documented and correctly articulated, then to expect a clustered index seek it means you are searching an unique record based on a clustred key value. In this case the problem has to be with the SARGability of the WHERE clause.

回复收藏 0 原文