SQL Server切换到索引扫描的基数（与搜索相对） [英] At what cardinality does SQL Server switch to an index scan (vs. seek)
问题描述
假设一个表包含足够的信息以保证索引搜索，SQL Server（或PostgreSQL）选择索引扫描的基数是什么？
原因我问这是我之前发过的一个问题（
引爆点是总页数的25％-33％的准则在表格内，表示为行，例如10k数据页面的临界点为2500-3333行。作为指导方针，这是非常好的，并且你会得到的好 - 请记住查询计划引擎是一个黑盒子，虽然它会给你一个查询计划，它只会说明它的决定，而不是原因。
然而，就小费覆盖指数而言，这实际上并不容易，即使选择了100％的数据，覆盖索引仍会在大多数情况下寻求过度扫描。
这是有道理的，如果你认为成本优化器没有为索引页面层次结构分配任何实际成本，那么任何成本只会导致访问叶子页面的成本。指数。此时，扫描或寻求100％覆盖索引的成本相同。
我从自己的实验中找到（ http://sqlfascination.com/2009/11/07/can- a-covering-nc-index-be-tipped ）使用一个between子句会导致它扫描，但其他where子句不会 - 从我可以告诉它纯粹是通过查询引擎的路径。
Assuming that a table contains sufficient information to warrant an index seek, at what cardinality will SQL Server (or PostgreSQL) opt for an index scan?
The reason I ask this is I previously posted a question (link) in which two queries performed at the same speed, yet one didn't attempt to use the index on the processed columns. After SQL Server suggested I put a covering index that included the columns being queried (it suggested this for both queries), I started looking for reasons as to why it would make such a strange suggestion.
I experimented with making the indexes covering and composite, but both executed in the same time (we're talking 3 million rows).
Finally I concluded it was because of the ultra-high cardinality of the data. Every row is unique. I am deducing this caused SQL server to choose an index scan. However, the query stated "WHERE Col1 > ? AND Col2 < ?", so this is a little confusing.
My questions are:
- At what cardinality will a RDBMS always opt for an index scan?
- Can anyone explain why SQL Server would not use the index when the WHERE statement would indicate this would make sense?
I have attached the execution plan.
In terms of SQL Server, this has been referred to as the tipping point, of which Kimberley's blog post is a good read on it. http://www.sqlskills.com/BLOGS/KIMBERLY/category/The-Tipping-Point.aspx
The tipping point is a guideline of 25%-33% of the total number of pages within the table, expressed as rows, e.g. 10k data pages would give a tipping point of 2500-3333 rows. As guidelines go this is pretty good, and as good as you will get - remember the query plan engine is a black box, and whilst it will give you a query plan, it only says what it decided, not why.
In terms of tipping a covering index though, that is not actually very easy, even with 100% of the data being selected a covering index will still seek over scan in the majority of cases.
That makes sense, if you consider that the cost optimizer doesn't assign any real cost to the index pages hierarchy, any only costs up the access to the leaf pages of the index. At that point, scanning or seeking 100% of a covering index is costed the same.
I found from my own experimentation (http://sqlfascination.com/2009/11/07/can-a-covering-nc-index-be-tipped ) using a between clause would cause it to scan, but other where clauses would not - from what I could tell it was purely down to the route through the query engine.
这篇关于SQL Server切换到索引扫描的基数（与搜索相对）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
