关闭

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

7879人阅读 评论(5) 收藏 举报
分类:

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

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


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

 

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

 

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

 

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

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


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


1、建表,插入数据

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、查看采用聚集索引和非聚集索引后,查询的资源消耗

--输出详细的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))


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



那么如果表没有聚集索引,也没有非聚集索引,效率又会怎么样呢?
--删除主键,也就删除了聚集索引
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,是可以查到,看下面的代码:

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种方式都是扫描,那为什么非聚集索引效率最高?

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


看下面的代码,就明白了:

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
*/

聚集索引的叶子节点的页数是201650,而非聚集索引的 叶子节点的页数是4538,差了近50倍,而在没有索引的时候,采用表扫描时,叶子节点的页数是201650,与聚集索引一样。


效率的差异不仅在与逻辑读取次数,因为逻辑读取效率本身是很高的,是直接在内存中读取的,但SQL Server的代码需要扫描内存中的数据201650次,也就是循环201650次,可想而知,cpu的使用率会暴涨,会严重影响SQL Server处理正常的请求。


假设这些要读取的页面不在内存中,那问题就大了,需要把硬盘上的数据读到内存,关键是要读201650页,而通过索引只需要读取4538次,效率的差距就会更大。


另外,实验中只是200多万条数据,如果实际生产环境中有2亿条记录呢?到时候,效率的差距会从几十倍上升到几百倍、几千倍。

 

5、那是不是只要是非聚集索引,都能提高select count(*) from查询的效率吗?

这个问题是由下面的网友提出的问题,而想到的一个问题。

如果按照v列来建索引,而v列的数据类型是varchar(600),所以这个新建的索引,占用的页数肯定是非常多的,应该仅次于聚集索引的201650页,那么完成索引扫描的开销肯定大于,按vid列建立的非聚集索引,而vid的数据类型是int。

所以,不是只要是非聚集索引,就能提高查询效率的。

 

总结一下:

执行select count(*) from查询的时候,要进行扫描,有人可能会说,扫描性能很差呀,还能提高性能?那么,难道用索引查找吗?这样性能只会更差。

这里想说的是,没有最好的技术,只有最适合的技术,要想提高这种select count(*) from查询的性能,那就只能用扫描。

这里,要提高效率的关键,就是减少要扫描的页数,而按照占用字节数少的字段,来建立非聚集索引,那么这个非聚集索引所占用的页数,远远少于聚集索引、按占用字节数较多的列建立的非聚集索引,所占用的页数,这样就能提高性能了。


最后,有两个关于索引的帖子,不错:


两个问题:1,(聚集或者非聚集的)索引页会不会出现也拆分;2,非聚集索引存储时又没排序:

http://bbs.csdn.net/topics/390594730


继续:非聚集索引行在存储索引键时确实是排序了的,用事实说话,理论+实践:

http://bbs.csdn.net/topics/390595949

2
0
查看评论

select count(*) 速度慢的原因主要有什么?

情景:一个表里面数据有2400多行,执行select count(*)竟然需要8秒多; 把这个表的索引删除重建之后再执行select count(*)还是要花费8秒多。 重建了一个结构一模一样的表,把同样数据导入之后,对新表执行count(*)就只需要几十ms, 想来想去造成速度慢的原因可能是:这点...
  • zhanwanhui
  • zhanwanhui
  • 2017-07-25 10:54
  • 1165

关于SQL中count的效率

COUNT(*)与COUNT(COL)网上搜索了下,发现各种说法都有:比如认为COUNT(COL)比COUNT(*)快的;认为COUNT(*)比COUNT(COL)快的;还有朋友很搞笑的说到这个其实是看人品的。在不加WHERE限制条件的情况下,COUNT(*)与COUNT(COL)基本可以认为是等价...
  • jianglei421
  • jianglei421
  • 2010-05-28 14:04
  • 6716

SQL性能调优实践——SELECT COUNT

最近想深入学习SQL,在网上搜索到一些SQL 优化的资料要么是张冠李戴,Oracle 优化的资料硬是弄成啦MS SQL 优化的资料,而且被很多人转载,收藏,有些要么有些含糊不清,好像是那么回事,也没经过验证,实践出真知!下面是我对SELECT COUNT(*)...
  • kerrycode
  • kerrycode
  • 2010-07-16 11:02
  • 2052

关于mysql处理百万级以上的数据时如何提高其查询速度的方法

原文:http://www.2cto.com/database/201306/222839.html 最近一段时间由于工作需要,开始关注针对Mysql数据库的select查询语句的相关优化方法。          由于在参与的实际项...
  • u013810758
  • u013810758
  • 2014-06-16 14:28
  • 4876

select count(1) from user where id=2

这里的count(1)中的1不是 一个字段,这句sql语句是表示有多少符合条件的行,另外,括号里面,你填1,2,3.....这些数字得到的结果都是一样的
  • small__children
  • small__children
  • 2017-06-08 16:43
  • 382

SQL数据库如何加快查询速度?

<br />SQL数据库如何加快查询速度?  <br /> 1、升级硬件   <br /> 2、根据查询条件,建立索引,优化索引、优化访问方式,限制结果集的数据量。  <br /> 3、扩大服务器的内存&#...
  • jking1989
  • jking1989
  • 2011-05-18 21:20
  • 12469

select count(id) from table 返回值

环境说明:       测试数据库为Mysql数据库,其他数据库未知       测试语言为Java       数据库操作使用dbutils包...
  • lygapp
  • lygapp
  • 2014-09-18 15:17
  • 2055

如何提高MySQL查询速度

1、选取最适用的字段属性 MySQL 可以很好的支持大数据量的存取,但是一般说来,数据库中的表越小,在它上面执行的查询也就会越快。因此,在创建表的时候,为了获得更好的性能,我们可以将表中字段的宽度设得尽可能小。例如,在定义邮政编码这个字段时,如果将其设置为CHAR(255),显然给数...
  • allen_fan_11
  • allen_fan_11
  • 2013-10-29 20:37
  • 1771

mysql存储过程中使用select count(*) into 变量名 from +表+ where条件的用法

select count(*) into v_count from dual where userid=2;此语句的意思就是根据where条件查询dual表,得到的行数存入变量v_count中(给变量赋值) 只能在存储过程中编写这样的语句?如果在mysql的sql语句中编写不知道会不会报错呢
  • jaryle
  • jaryle
  • 2017-07-21 16:56
  • 959

Hive SQL 语义分析:select count(*) from tableName

从客户端提交一个 Hive SQL  到 Driver 提交 MapReduce Job,有一个对SQL进行词法分析和语义分析的过程,下面以 select count(*) from tableName 来描述其过程。 一、词法分析 使用ANTLR分析SQL,生成语法树,每个节点是一个&#...
  • Xiaos_hui
  • Xiaos_hui
  • 2013-09-06 02:01
  • 2547
    个人资料
    • 访问:526371次
    • 积分:9035
    • 等级:
    • 排名:第2474名
    • 原创:362篇
    • 转载:14篇
    • 译文:1篇
    • 评论:137条
    博客专栏
    最新评论