为什么非聚簇索引扫描比聚簇索引扫描更快? [英] Why NonClustered index scan faster than Clustered Index scan?
问题描述
据我所知，堆表是没有聚簇索引并且没有物理顺序的表. 我有一个具有12万行的堆表扫描"，并且正在使用以下选择:
SELECT id FROM scan
如果我为"id"列创建非聚集索引，则会得到 223次物理读取. 如果删除非聚集索引并更改表以使"id"成为主键(以及聚集索引)，则会得到 515次物理读取.
如果聚集索引表如下图所示:
为什么聚簇索引扫描像表扫描一样工作? (或者在检索所有行的情况下更糟).为什么它不使用块较少且已经具有我所需ID的聚集索引表"?
SQL Server索引是b树.非聚集索引仅包含索引列，而b树的叶子节点是指向适当数据页面的指针.聚簇索引是不同的:它的叶子节点是数据页本身，而聚簇索引的b树成为表本身的后备存储.该表的堆不再存在.
您的非聚集索引包含单个(可能是整数)列.首先是一个小的紧凑索引.您的查询select id from scan具有覆盖索引:只需检查索引即可满足查询的要求.但是，如果查询中的列不包含在索引中，则假设优化器选择使用非聚集索引，则将需要进行额外的查找以从集群索引或堆中获取所需的数据页.
要了解发生了什么，您需要检查优化程序选择的执行计划:
- 请参见显示图形执行计划
- 请参阅Grant Fritchey的Red Gate的 SQL Server执行计划.
As I know, heap tables are tables without clustered index and has no physical order. I have a heap table "scan" with 120k rows and I am using this select:
SELECT id FROM scan
If I create a non-clustered index for the column "id", I get 223 physical reads. If I remove the non-clustered index and alter the table to make "id" my primary key (and so my clustered index), I get 515 physical reads.
If the clustered index table is something like this picture:
Why Clustered Index Scans workw like the table scan? (or worse in case of retrieving all rows). Why it is not using the "clustered index table" that has less blocks and already has the ID that I need?
SQL Server indices are b-trees. A non-clustered index just contains the indexed columns, with the leaf nodes of the b-tree being pointers to the approprate data page. A clustered index is different: its leaf nodes are the data page itself and the clustered index's b-tree becomes the backing store for the table itself; the heap ceases to exist for the table.
Your non-clustered index contains a single, presumably integer column. It's a small, compact index to start with. Your query select id from scan has a covering index: the query can be satisfied just by examining the index, which is what is happening. If, however, your query included columns not in the index, assuming the optimizer elected to use the non-clustered index, an additional lookup would be required to fetch the data pages required, either from the clustering index or from the heap.
To understand what's going on, you need to examine the execution plan selected by the optimizer:
- See Displaying Graphical Execution Plans
- See Red Gate's SQL Server Execution Plans, by Grant Fritchey
这篇关于为什么非聚簇索引扫描比聚簇索引扫描更快?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
