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

最新推荐文章于 2022-07-02 21:37:30 发布

starseeker7

最新推荐文章于 2022-07-02 21:37:30 发布

阅读量841

点赞数

CC 4.0 BY-SA版权

分类专栏： SQL技术

本文链接：https://blog.youkuaiyun.com/starseeker7/article/details/8569665

SQL技术专栏收录该内容

37 篇文章

订阅专栏

本文深入探讨了在SQL查询中，使用Count(*)函数时，聚合索引与非聚合索引在性能上的区别。通过详细分析查询过程和物理统计信息，解释了为何非聚合索引在此场景下通常具有更高的效率。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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

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

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') 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 
, 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') 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(*)时非聚合索引性能大于聚合索引性能得原因

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