通过非聚集索引让select count(*) from 的查询速度提高几十倍、甚至千倍

最新推荐文章于 2024-09-26 22:35:38 发布

菜鸟学编程

最新推荐文章于 2024-09-26 22:35:38 发布

阅读量1.5k

点赞数

分类专栏： # MSSQL

MSSQL 专栏收录该内容

21 篇文章 3 订阅

订阅专栏

通过非聚集索引，可以显著提升count(*)查询的性能。

有的人可能会说，这个count(*)能用上索引吗，这个count(*)应该是通过表扫描来一个一个的统计，索引有用吗？

不错，一般的查询，如果用索引查找，也就是用Index Seek了，查询就会很快。

之所以快，是由于查询所需要访问的数据只占整个表的很小一部分，如果访问的数据多了，那反而不如通过表扫描来的更快，因为扫描用的是顺序IO，效率更高，比运用随机IO访问大量数据的效率高很多。

相应的，如果只需要访问少量数据，那么索引查找的效率远高于表扫描，因为通过随机IO来访问少量数据的效率远高于通过顺序IO来访问少量数据，之所以扫描的效率较低是由于扫描访问了很多不需要的数据。

那么，通过非聚集索引，提升select count(*) from 的查询速度的本质在于，非聚集索引所占空间的大小往往，远小于聚集索引或堆表所占用的空间大小；

同样的，表中占用较少字节的字段的非聚集索引，对于速度的提升效果，也要远大于，占用较多字节的字段的非聚集索引，因为占用字节少，那么索引占用的空间也少，同样是扫描，只需要更少的时间，对硬盘的访问次数也更少，那么速度就会更快了。

下面通过一个实验，来说明非聚集索引为什么能提高count（*）的查询速度。

1、建表，插入数据

[sql]view plaincopy 
   
 if OBJECT_ID('test') is not null   
    drop table test   
 go   
    
 create table test   
 (   
 id int identity(1,1),   
 vid int ,   
 v varchar(600),   
 constraint pk_test_id primary key (id)   
 )   
 go   
    
    
    
 insert into test(vid,v)   
 select 1,REPLICATE('a',600) union all   
 select 2,REPLICATE('b',600) union all   
 select 3,REPLICATE('c',600) union all   
 select 4,REPLICATE('d',600) union all   
 select 5,REPLICATE('e',600) union all   
 select 6,REPLICATE('f',600) union all   
 select 7,REPLICATE('g',600) union all   
 select 8,REPLICATE('h',600) union all   
 select 9,REPLICATE('i',600) union all   
 select 10,REPLICATE('j',600)   
 go   
    
    
 --select POWER(2,18) * 10   
 --2621440条数据   
 begin tran   
     insert into test(vid,v)   
     select vid,v   
     from test   
 commit   
 go 18   
    
    
 --建立非聚集索引   
 create index idx_test_vid on test(vid)  

2、查看采用聚集索引和非聚集索引后，查询的资源消耗

[sql]view plaincopy 
   
 --输出详细的IO和时间(cpu、流逝的时间)上的开销信息   
 set statistics io on   
 set statistics time on   
    
    
 /* 采用聚集索引   
    
 SQL Server 分析和编译时间:    
    CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。   
    
 (1 行受影响)   
 表 'test'。扫描计数 5，逻辑读取 206147 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。   
    
  SQL Server 执行时间:   
    CPU 时间 = 921 毫秒，占用时间 = 277 毫秒。   
 */   
 select COUNT(*)   
 from test with(index (pk_test_id))   
    
    
    
 /*采用非聚集索引   
    
 SQL Server 分析和编译时间:    
    CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。   
    
 (1 行受影响)   
 表 'test'。扫描计数 5，逻辑读取 4608 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。   
    
  SQL Server 执行时间:   
    CPU 时间 = 327 毫秒，占用时间 = 137 毫秒。   
 */   
 select count(*)   
 from test with(index (idx_test_vid))  

另外，下图的两个语句一起执行时的执行计划：

那么如果表没有聚集索引，也没有非聚集索引，效率又会怎么样呢？

[sql]view plaincopy 
    
 --删除主键，也就删除了聚集索引   
 alter table test   
 drop constraint pk_test_id   
    
    
 --删除非聚集索引   
 drop index idx_test_vid on test   
    
    
 /* 表扫描   
    
 SQL Server 分析和编译时间:    
    CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。   
    
  SQL Server 执行时间:   
    CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。   
 SQL Server 分析和编译时间:    
    CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。   
    
 (1 行受影响)   
 表 'test'。扫描计数 5，逻辑读取 201650 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。   
    
 (1 行受影响)   
    
  SQL Server 执行时间:   
    CPU 时间 = 765 毫秒，占用时间 = 233 毫秒。   
 SQL Server 分析和编译时间:    
    CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。   
    
  SQL Server 执行时间:   
    CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。   
 */   
 select count(*)   
 from test  

3、从上面的开销可以看出：

a、通过聚集索引来查询count(*)时，逻辑读取次数206147次，执行时间和占用时间分别是921毫秒和277毫秒，从执行计划中看出，其查询开销是96%。

b、非聚集索引的逻辑读取次数是4608次，而执行时间和占用时间是327毫秒和137毫秒，查询开销是4%。

c、表扫描的逻辑读取次数是201650次，执行时间和占用时间是765毫秒和233毫秒。

这里需要注意的是，由于两个执行计划都采用了并行计划，导致了执行时间远大于占用时间，这主要是因为执行时间算的是多个cpu时间的总和，我的笔记本电脑有4个cpu，那么921/4 大概就是230毫秒左右，也就是每个cpu花在执行上的时间大概是230毫秒左右，和277毫秒就差不多了。

从这些开销信息可以看出，非聚集索引的逻辑读取次数是聚集索引的50分之一，执行时间是聚集索引的2-3分之一左右，查询开销上是聚集索引的24分之一。

很有意思的是，表扫描的逻辑读取次数要比聚集索引的要少4497次，这个逻辑读取次数201650，是可以查到，看下面的代码：

[sql]view plaincopy 
   
 use master   
 go   
    
 --下面的数据库名称是wcc，需要改成你自己的数据库名称   
 select index_id,   
        index_type_desc,   
        alloc_unit_type_desc,   
        page_count              --页数为：201650   
 from sys.dm_db_index_physical_stats   
 (   
 db_id('wcc'),object_id('wcc.dbo.test'),0,null,'detailed'   
 )d   
    
 /*   
 index_id    index_type_desc alloc_unit_type_desc    page_count   
 0           HEAP            IN_ROW_DATA             201650   
 */  

之所以能查到，是因为全表扫描，无非就是把表中所有的页，都扫描一遍，所以扫描的次数正好是表中的页数201650.

4、那为什么非聚集索引来查询count(*) 的效率是最高的呢？

其实上面分别提到了，通过聚集索引、非聚集索引、表扫描，3种方式来查询，从执行计划可以看出来，3种方式都是扫描，那为什么非聚集索引效率最高？

其实，很简单，谁扫描的次数少，也就是扫描的页数少，那谁的效率当然就高了。

看下面的代码，就明白了：

[sql]view plaincopy 
   
 use master   
 go   
    
 --index_id为1表示聚集索引   
 select index_id,   
        index_type_desc,   
        alloc_unit_type_desc,   
        page_count                --201650   
 from sys.dm_db_index_physical_stats   
 (   
 db_id('wcc'),object_id('wcc.dbo.test'),1,null,'detailed'   
 )d   
 where index_level = 0  --只取level为0的,也就是页子级别   
    
 /*   
 index_id    index_type_desc     alloc_unit_type_desc   page_count   
 1           CLUSTERED INDEX     IN_ROW_DATA            201650   
 */   
    
    
    
 --index_id为2的,表示非聚集索引   
 select index_id,   
        index_type_desc,   
        alloc_unit_type_desc,   
        page_count               --4538   
 from sys.dm_db_index_physical_stats   
 (   
 db_id('wcc'),object_id('wcc.dbo.test'),2,null,'detailed'   
 )d   
 where index_level = 0   
    
 /*   
 index_id    index_type_desc     alloc_unit_type_desc    page_count   
 2           NONCLUSTERED INDEX  IN_ROW_DATA             4538   
 */  