好得很程序员自学网

<tfoot draggable='sEl'></tfoot>

sql count效率

sql count效率

 

浅谈count(1),count(*)和count（column_name）

　　　最近看到群里有位仁兄，问到关于count(column_name)和count(*),还有count(1)效率和不同点的问题，我记得，在很久之前提到过关于这块的问题，很多人对怎么用这三个统计都模糊不清的，所以，今天抽个空，自己做个实验，测试测试这种情况，我测试的思路是从执行效率上和输出的数据量这两方面。

　　如果有不到之处，还敬请拍砖！！

 --  建立测试环境 
 create   table   test_a
(
a   int  
)

  declare   @max   int  , @rc   int 
 set   @max   =  10000000 
 set   @rc   =  1

 
 while   @rc  <=  @rc 
 begin 
 insert   into   test_a
  select   @max 
 set   @rc  =  @rc  +  1 
 end 
 --  数据量太大，插的时候太慢了,所以只插了8034568条数据，大约半小时，嘻嘻

 

 --  没有NULL值得情况下，有NULL值得情况我会在后面再讨论，现在情况为表为单列的且没有索引的情况 

 declare   @time_first   datetime 
 declare   @time_end      datetime 
 declare   @sql      varchar ( 20  )

  select   @time_first   =   GETDATE  ()
  select   COUNT ( 1 )  from   test_a
  select   @time_end  =  GETDATE  ()
  --  print @time_first 
 set   @sql  =   DATEDIFF (MILLISECOND, @time_first , @time_end  )
  print   @sql 

 set   @time_first   =  GETDATE  ()
  select   COUNT (a)  from   test_a
  set   @time_end  =  GETDATE  ()
  set   @sql  =   DATEDIFF (MILLISECOND, @time_first , @time_end  )
  print   @sql 

 set   @time_first   =  GETDATE  ()
  select   COUNT ( * )  from   test_a
  set   @time_end  =  GETDATE  ()
  set   @sql  =   DATEDIFF (MILLISECOND, @time_first , @time_end  )
  print   @sql 
 /*  
在没有索引的情况下，我多次执行，得到如下结果(这边就不一一列举)：
第一次：
-----------
8034568

(1 行受影响)

366

-----------
8034568

(1 行受影响)

803

-----------
8034568

(1 行受影响)

400

第二次：
-----------
8034568

(1 行受影响)

360

-----------
8034568

(1 行受影响)

820

-----------
8034568

(1 行受影响)

396

--第三次：

-----------
8034568

(1 行受影响)

386

-----------
8034568

(1 行受影响)

816

-----------
8034568

(1 行受影响)

370

  */

　　为了避免偶然性，我再本机上多次执行了，从得出的结果上很容易得出来，count(column_name)从时间上来看的话，是最慢的，count(1)和count(*)时间上是差不多的。
--我们看看他们各自的执行计划，如图一：


　　由上面的图片可以看的出来，在没有索引的情况下他们的执行计划是一样的。
　　我们再来看看磁盘盒CPU的开销情况。

 set   statistics  time  on  
 go 
 select   COUNT ( 1 )  from   test_a
  select   COUNT (a)  from   test_a
  select   COUNT ( * )  from   test_a
  set   statistics  time  off 
 go 
 /*  
表 'test_a'。扫描计数 3，逻辑读取 13572 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

SQL Server 执行时间:
CPU 时间 = 688 毫秒，占用时间 = 344 毫秒。

-----------
8034568

(1 行受影响)

表 'test_a'。扫描计数 3，逻辑读取 13572 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

SQL Server 执行时间:
CPU 时间 = 1562 毫秒，占用时间 = 774 毫秒。

-----------
8034568

(1 行受影响)

表 'test_a'。扫描计数 3，逻辑读取 13572 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

SQL Server 执行时间:
CPU 时间 = 688 毫秒，占用时间 = 347 毫秒。
  */

　　从上可以看的出来，在没有索引的情况下，他们的逻辑读取和物理读取是一样的，但在CPU的输入输出上不同，所以，可以得出以下结论，在相同条件下，如果表只有一列，且没有索引的情况下，count(column_name)的开销是最大的，而count(1)和count(*)区别不是很大，这种区别在将近900W的数据量的情况下是可以忽略的。

　　下面我们讨论讨论如果有索引的情况下是怎么样的。

　　在原来的环境情况下，增加一个列，和一个索引。

 --  增加一个标识列 
 alter   table   test_a
  add  id  int   identity ( 1 , 1  )
  --  建立一个聚集索引 
 create   clustered   index  index_id  on  test_a(id)



 declare   @time_first   datetime 
 declare   @time_end      datetime 
 declare   @sql      varchar ( 20  )

  select   @time_first   =   GETDATE  ()
  select   COUNT ( 1 )  from   test_a
  select   @time_end  =  GETDATE  ()
  --  print @time_first 
 set   @sql  =   DATEDIFF (MILLISECOND, @time_first , @time_end  )
  print  N '  执行count(1)所花费的时间：  '  +   @sql 


 set   @time_first   =  GETDATE  ()
  select   COUNT (ID)  from   test_a
  set   @time_end  =  GETDATE  ()
  set   @sql  =   DATEDIFF (MILLISECOND, @time_first , @time_end  )
  print  N '  执行count(ID)所花费的时间(其中ID为索引列)：  '  +  @sql 

 set   @time_first   =  GETDATE  ()
  select   COUNT (ID)  from   test_a
  set   @time_end  =  GETDATE  ()
  set   @sql  =   DATEDIFF (MILLISECOND, @time_first , @time_end  )
  print  N '  执行count(a)所花费的时间(其中a为非索引列)：  '  +  @sql 

 set   @time_first   =  GETDATE  ()
  select   COUNT ( * )  from   test_a
  set   @time_end  =  GETDATE  ()
  set   @sql  =   DATEDIFF (MILLISECOND, @time_first , @time_end  )
  print  N '  执行count(*)：  '  +  @sql 

 /*  
第一次：
-----------
8034568

(1 行受影响)

执行count(1)所花费的时间：353

-----------
8034568

(1 行受影响)

执行count(ID)所花费的时间(其中ID为索引列)：406

-----------
8034568

(1 行受影响)

执行count(a)所花费的时间(其中a为非索引列)：350

-----------
8034568

(1 行受影响)

执行count(*)所花费的时间：370

第二次：
-----------
8034568

(1 行受影响)

执行count(1)所花费的时间：376

-----------
8034568

(1 行受影响)

执行count(ID)所花费的时间(其中ID为索引列)：380

-----------
8034568

(1 行受影响)

执行count(a)所花费的时间(其中a为非索引列)：353

-----------
8034568

(1 行受影响)

执行count(*)：360

第三次：
-----------
8034568

(1 行受影响)

执行count(1)所花费的时间：360

-----------
8034568

(1 行受影响)

执行count(ID)所花费的时间(其中ID为索引列)：403

-----------
8034568

(1 行受影响)

执行count(a)所花费的时间(其中a为非索引列)：360

-----------
8034568

(1 行受影响)

执行count(*)：360
  */
 


     




　　很奇怪，为什么加了索引之后反而比没有加索引的快呢？我们在来看看他们各自的执行计划。


 


　　四个的执行计划是一样的，但是与之前的是有区别的，加了索引之后，无论你是否是指定索引列还是非索引列，它都默认的走索引列。


　　我们再看看磁盘以及CPU的开销情况。




     


 set   statistics  time  on   
 go 
 select   COUNT ( 1 )  from   test_a
  select   COUNT (ID)  from   test_a
  select   COUNT (a)  from   test_a
  select   COUNT ( * )  from   test_a
  set   statistics  time  off 
 go 
 /*  
SQL Server 分析和编译时间: 
   CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。

-----------
8034568

(1 行受影响)

表 'test_a'。扫描计数 3，逻辑读取 17021 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

 SQL Server 执行时间:
   CPU 时间 = 702 毫秒，占用时间 = 348 毫秒。

-----------
8034568

(1 行受影响)

表 'test_a'。扫描计数 3，逻辑读取 17021 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

 SQL Server 执行时间:
   CPU 时间 = 720 毫秒，占用时间 = 403 毫秒。

-----------
8034568

(1 行受影响)

表 'test_a'。扫描计数 3，逻辑读取 17021 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

 SQL Server 执行时间:
   CPU 时间 = 1624 毫秒，占用时间 = 825 毫秒。

-----------
8034568

(1 行受影响)

表 'test_a'。扫描计数 3，逻辑读取 17021 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

 SQL Server 执行时间:
   CPU 时间 = 782 毫秒，占用时间 = 394 毫秒。
  */ 


     




　　从上面的磁盘和CPU的开销来看，如果是多列有索引的情况下，并非count(索引列)最快，反而最慢，当然其中也不含有null统计的情况。



　　下面我们来讨论，有NULL值得情况下，数据量的问题。




     


 --  数据准备，执行如下语句： 
 update   test_a
  set  a =  null 
 where  id  %  5  =  0 

 select   count ( * )  from   test_a
  /*  
-----------
8034568

(1 行受影响)
  */ 
 select   COUNT ( * )  from   test_a
  where  A  is   null 
 /*  
--为空的数据为
-----------
1606913

(1 行受影响)

  */ 


     




　　数据环境出来了，我们来做个统计。




     


 --  我们执行以下语句 
 select   COUNT ( 1 )  from   test_a
  select   COUNT ( * )  from   test_a
  select   COUNT (id)  from   test_a
  select   COUNT (a)  from   test_a

  /*  
-----------
8034568

(1 行受影响)


-----------
8034568

(1 行受影响)


-----------
8034568

(1 行受影响)


-----------
6427655
警告: 聚合或其他 SET 操作消除了 Null 值。

(1 行受影响)
  */ 


     




　　我们不忙分析，请看下面的执行，能更加加固我们对count()的用法的意思。




     


 --  我们在原来的基础上执行以下语句，建立环境。 
 begin   tran 
 drop   index  test_a.index_id --   删除索引 

 alter   table   test_a
  drop   column  id             --  删除列 
 commit 


     






     


 --  我们再次执行上面的查询语句： 
 select   COUNT ( 1 )  from   test_a
  select   COUNT ( * )  from   test_a
  --  select COUNT(id) from test_a 
 select   COUNT (a)  from   test_a

  /*  
-----------
8034568

(1 行受影响)


-----------
8034568

(1 行受影响)


-----------
6427655
警告: 聚合或其他 SET 操作消除了 Null 值。

(1 行受影响)
  */ 


     




　　到此为止，我们应该清晰的明白了count()的用法了吧。


　　以上仅一家之言，如有不对的地方请各位指教，若有转载，请注明出处。












作者： Leo_wl 

　　　　
出处： http://www.cnblogs.com/Leo_wl/ 

　　　　
本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，否则保留追究法律责任的权利。 

 版权信息 




        


      
查看更多关于sql count效率的详细内容...

        

          
声明：本文来自网络，不代表【好得很程序员自学网】立场，转载请注明出处：http://www.haodehen.cn/did47046

        

      

        

           
         阅读：43次 
	

    


    

      
上一篇： 第一个MVC4 Web应用程序 

      
 下一篇：通过网页进行 iOS 应用内部分发 

    

    
    

      
相关资讯