聚集索引列上的非聚簇索引可提高性能？ [英] Non-Clustered Index on a Clustered Index column improves performance?

问题描述

在SQL Server 2005中，查询分析器多次告诉我在已经具有聚簇索引的表的主ID列上创建非聚簇索引。遵循此建议后，查询执行计划报告查询应该更快。

In SQL Server 2005, the query analyzer has told me many times to create a non-clustered index on a primary ID column of a table which already has a clustered index. After following this recommendation, the query execution plan reports that the query should be faster.

为什么同一列上的非聚集索引（具有相同的排序顺序）是否比聚集索引更快？

Why would a Non-Clustered index on the same column (with the same sort order) be faster than a Clustered index?

推荐答案

聚簇索引具有表的所有数据，而非聚簇索引仅具有column +聚簇索引的位置或行（如果它在堆上）（没有聚簇索引的表）。因此，如果您执行计数（列）并且该列使用非聚集索引编制索引，则SQL Server只需扫描比聚簇索引更快的非聚簇索引，因为更多内容将适用于8K页

A clustered index has all the data for the table while a non clustered index only has the column + the location of the clustered index or the row if it is on a heap (a table without a clustered index). So if you do a count(column) and that column is indexed with a non clustered index SQL server only has to scan the non clustered index which is faster than the clustered index because more will fit on 8K pages

这篇关于聚集索引列上的非聚簇索引可提高性能？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！