关于count(*)得 非聚合索引 性能大于聚合索引性能研究

今天在回答别人问题得时候发现了这样一个问题

经过以下 语句测试后 ，发现确实如此

set statistics io on select  COUNT(*)  from dbo.tb_Info with(index(PK_tb_Info))select  COUNT(*)  from dbo.tb_Info with(index(IX_TB_INFO))set statistics io oFF
(1 row(s) affected)
Table 'tb_Info'. Scancount 1, logical reads 3269, physical reads 0, read-ahead reads 0, lob logicalreads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
Table 'tb_Info'. Scancount 1, logical reads 68, physical reads 0, read-ahead reads 0, lob logical reads0, lob physical reads 0, lob read-ahead reads 0.

然后我通过对这两个索引详细信息进行查询

SELECT I.name , P.index_level , P.page_count , P.avg_page_space_used_in_percent FROM sys.dm_db_index_physical_stats     ( db_id(), object_id('ConnectionPool.tb_Info'), default, default, 'DETAILED') PJOIN sys.indexes ION I.object_id = P.object_id AND I.index_id = P.index_idWHERE I.name = 'PK_tb_Info';Nameindex_level                page_count        avg_page_space_usedPK_tb_Info03258        96.8682357301705PK_tb_Info1958.117123795404PK_tb_Info211.42080553496417SELECT I.name , P.index_level , P.page_count , P.avg_page_space_used_in_percent FROM sys.dm_db_index_physical_stats     ( db_id(), object_id('ConnectionPool.tb_Info'), default, default, 'DETAILED') PJOIN sys.indexes ION I.object_id = P.object_id AND I.index_id = P.index_idWHERE I.name = 'IX_TB_INFO';Nameindex_level                 page_count        avg_page_space_usedIX_TB_INFO06699.9414133926365IX_TB_INFO1110.575735112429logical reads 3269=3258+9+1+1(计算)logical reads 68=66+1+1(计算)

我们可以看到在count查询语句，索引都会去读取叶层级，再进行输出

而聚合索引因为叶子节点就是实际数据所以需要读取次数远大于非聚合索引。

而logical reads=索引各层级页面之和+1

故，索引的完全扫描会导致逻辑读次数=索引页数

这也就是导致在使用count(*)时非聚合索引性能大于聚合索引性能得原因

而其他大多数情况中，不会出现该情况