关于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,lob logical reads0,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,'DETAILED') P
JOIN sys.indexes I
ON I.object_id = P.object_id AND I.index_id = P.index_id
WHERE I.name = 'PK_tb_Info';
Name			index_level	                page_count		        avg_page_space_used
PK_tb_Info		0				3258			        96.8682357301705		
PK_tb_Info		1				9				58.117123795404		
PK_tb_Info		2				1				1.42080553496417		

SELECT I.name,'DETAILED') P
JOIN sys.indexes I
ON I.object_id = P.object_id AND I.index_id = P.index_id
WHERE I.name = 'IX_TB_INFO';
Name			index_level	                 page_count		        avg_page_space_used
IX_TB_INFO		0				66				99.9414133926365	
IX_TB_INFO		1				1				10.575735112429	

logical reads 3269=3258+9+1+1(计算)
logical reads 68=66+1+1(计算)

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

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

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

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

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

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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!