程序博客网 > windows wordpress

sybase中的聚簇索引与count（*）的关系

来源：互联网 发布：windows wordpress 编辑：程序博客网 时间：2024/06/05 19:11

----------------------------------------------------------------------------

---- 本文为andkylee个人原创，请在尊重作者劳动成果的前提下进行转载；

---- 转载务必注明原始出处 : http://blog.csdn.net/andkylee

---- 关键字： sybase 聚簇索引 非聚簇索引 查询计划 行数  count

----------------------------------------------------------------------------

 

在sybase表上建立聚集索引可以提高键列的检索速度。这是索引的主要功能所在。

可是，聚集索引对于统计表上的行数count（*）有没有改善呢？ 答案是否定的。

 

请看我下面的测试代码！

 

建立一张临时表test3

 

[c-sharp] view plaincopy
  1. create table test3(id int not null,name varchar(30) null)   

 

向表中插入测试数据

 

 

[c-sharp] view plaincopy
  1. insert into test3    
  2. select 1,'liu'    
  3. go    
  4. insert into test3    
  5. select 2,'zhang'    
  6. go    
  7. insert into test3    
  8. select 3,'wang'    
  9. go    
  10. insert into test3    
  11. select 4,'li'    
  12. go    
  13. 循环插入    
  14. insert into test4  
  15. select count(*)+id,name from test4  
  16. go 18  
  17. 1> select count(*) from  test4  
  18. 2> go  
  19.  -----------  
  20.       524288  
  21. (1 row affected)  
  22. 循环插入了524288条记录!  

 

打开查询计划和统计查询计划时间的选项

 

[c-sharp] view plaincopy
  1. set showplan on   
  2. go  
  3. set statistics time on   
  4. go  

 

表上没有加任何索引的情况下。

 

select count(*) from test4 的查询计划为:

[c-sharp] view plaincopy
  1. 1> select count(*) from test4  
  2. 2> go  
  3. QUERY PLAN FOR STATEMENT 1 (at line 1).  
  4.     STEP 1  
  5.         The type of query is SELECT.  
  6.         2 operator(s) under root  
  7.        |ROOT:EMIT Operator (VA = 2)  
  8.        |  
  9.        |   |SCALAR AGGREGATE Operator (VA = 1)  
  10.        |   |  Evaluate Ungrouped COUNT AGGREGATE.  
  11.        |   |  
  12.        |   |   |SCAN Operator (VA = 0)  
  13.        |   |   |  FROM TABLE  
  14.        |   |   |  test4  
  15.        |   |   |  Table Scan.  
  16.        |   |   |  Forward Scan.  
  17.        |   |   |  Positioning at start of table.  
  18.        |   |   |  Using I/O Size 32 Kbytes for data pages.  
  19.        |   |   |  With MRU Buffer Replacement Strategy for data pages.  
  20. Parse and Compile Time 0.  
  21. Adaptive Server cpu time: 0 ms.  
  22.  -----------  
  23.       524288  
  24. Execution Time 1.  
  25. Adaptive Server cpu time: 100 ms.  Adaptive Server elapsed time: 156 ms.  
  26. (1 row affected)  


select count(1) from test4 的查询计划为:

 

[c-sharp] view plaincopy
  1. 1> select count(1) from test4  
  2. 2> go  
  3. QUERY PLAN FOR STATEMENT 1 (at line 1).  
  4.     STEP 1  
  5.         The type of query is SELECT.  
  6.         2 operator(s) under root  
  7.        |ROOT:EMIT Operator (VA = 2)  
  8.        |  
  9.        |   |SCALAR AGGREGATE Operator (VA = 1)  
  10.        |   |  Evaluate Ungrouped COUNT AGGREGATE.  
  11.        |   |  
  12.        |   |   |SCAN Operator (VA = 0)  
  13.        |   |   |  FROM TABLE  
  14.        |   |   |  test4  
  15.        |   |   |  Table Scan.  
  16.        |   |   |  Forward Scan.  
  17.        |   |   |  Positioning at start of table.  
  18.        |   |   |  Using I/O Size 32 Kbytes for data pages.  
  19.        |   |   |  With MRU Buffer Replacement Strategy for data pages.  
  20. Parse and Compile Time 0.  
  21. Adaptive Server cpu time: 0 ms.  
  22.  -----------  
  23.       524288  
  24. Execution Time 1.  
  25. Adaptive Server cpu time: 100 ms.  Adaptive Server elapsed time: 220 ms.  

 

可以看出，count（*） 和count（1） 的执行计划是相同的。都执行了表扫描。

 

 

由于表上没有任何索引可供使用，select count(id) 和 select count(name) 都是执行了表扫描。

[c-sharp] view plaincopy
  1. 1> select count(id) from test4  
  2. 2> go  
  3. QUERY PLAN FOR STATEMENT 1 (at line 1).  
  4.     STEP 1  
  5.         The type of query is SELECT.  
  6.         2 operator(s) under root  
  7.        |ROOT:EMIT Operator (VA = 2)  
  8.        |  
  9.        |   |SCALAR AGGREGATE Operator (VA = 1)  
  10.        |   |  Evaluate Ungrouped COUNT AGGREGATE.  
  11.        |   |  
  12.        |   |   |SCAN Operator (VA = 0)  
  13.        |   |   |  FROM TABLE  
  14.        |   |   |  test4  
  15.        |   |   |  Table Scan.  
  16.        |   |   |  Forward Scan.  
  17.        |   |   |  Positioning at start of table.  
  18.        |   |   |  Using I/O Size 32 Kbytes for data pages.  
  19.        |   |   |  With MRU Buffer Replacement Strategy for data pages.  
  20. Parse and Compile Time 0.  
  21. Adaptive Server cpu time: 0 ms.  
  22.  -----------  
  23.       524288  
  24. Execution Time 1.  
  25. Adaptive Server cpu time: 100 ms.  Adaptive Server elapsed time: 140 ms.  
  26. (1 row affected)  
  27. 1> select count(name) from test4  
  28. 2> go  
  29. QUERY PLAN FOR STATEMENT 1 (at line 1).  
  30.     STEP 1  
  31.         The type of query is SELECT.  
  32.         2 operator(s) under root  
  33.        |ROOT:EMIT Operator (VA = 2)  
  34.        |  
  35.        |   |SCALAR AGGREGATE Operator (VA = 1)  
  36.        |   |  Evaluate Ungrouped COUNT AGGREGATE.  
  37.        |   |  
  38.        |   |   |SCAN Operator (VA = 0)  
  39.        |   |   |  FROM TABLE  
  40.        |   |   |  test4  
  41.        |   |   |  Table Scan.  
  42.        |   |   |  Forward Scan.  
  43.        |   |   |  Positioning at start of table.  
  44.        |   |   |  Using I/O Size 32 Kbytes for data pages.  
  45.        |   |   |  With MRU Buffer Replacement Strategy for data pages.  
  46. Parse and Compile Time 0.  
  47. Adaptive Server cpu time: 0 ms.  
  48.  -----------  
  49.       524288  
  50. Execution Time 1.  
  51. Adaptive Server cpu time: 100 ms.  Adaptive Server elapsed time: 236 ms.  
  52. (1 row affected)  
  53. 1>  

 

 

下面考虑加入主键（聚集索引）pk_test4_id

[c-sharp] view plaincopy
  1. alter table test4 add constraint pk_test4_id primary key (id)  
  2. go  

 

再次执行select count(*) from test4 和 select count(1) from test4

 

[c-sharp] view plaincopy
  1. 1> select count(*) from test4  
  2. 2> go  
  3. QUERY PLAN FOR STATEMENT 1 (at line 1).  
  4.     STEP 1  
  5.         The type of query is SELECT.  
  6.         2 operator(s) under root  
  7.        |ROOT:EMIT Operator (VA = 2)  
  8.        |  
  9.        |   |SCALAR AGGREGATE Operator (VA = 1)  
  10.        |   |  Evaluate Ungrouped COUNT AGGREGATE.  
  11.        |   |  
  12.        |   |   |SCAN Operator (VA = 0)  
  13.        |   |   |  FROM TABLE  
  14.        |   |   |  test4  
  15.        |   |   |  Table Scan.  
  16.        |   |   |  Forward Scan.  
  17.        |   |   |  Positioning at start of table.  
  18.        |   |   |  Using I/O Size 32 Kbytes for data pages.  
  19.        |   |   |  With MRU Buffer Replacement Strategy for data pages.  
  20. Parse and Compile Time 0.  
  21. Adaptive Server cpu time: 0 ms.  
  22.  -----------  
  23.       524288  
  24. Execution Time 2.  
  25. Adaptive Server cpu time: 200 ms.  Adaptive Server elapsed time: 736 ms.  
  26. (1 row affected)  
  27. 1> select count(1) from test4  
  28. 2> go  
  29. QUERY PLAN FOR STATEMENT 1 (at line 1).  
  30.     STEP 1  
  31.         The type of query is SELECT.  
  32.         2 operator(s) under root  
  33.        |ROOT:EMIT Operator (VA = 2)  
  34.        |  
  35.        |   |SCALAR AGGREGATE Operator (VA = 1)  
  36.        |   |  Evaluate Ungrouped COUNT AGGREGATE.  
  37.        |   |  
  38.        |   |   |SCAN Operator (VA = 0)  
  39.        |   |   |  FROM TABLE  
  40.        |   |   |  test4  
  41.        |   |   |  Table Scan.  
  42.        |   |   |  Forward Scan.  
  43.        |   |   |  Positioning at start of table.  
  44.        |   |   |  Using I/O Size 32 Kbytes for data pages.  
  45.        |   |   |  With MRU Buffer Replacement Strategy for data pages.  
  46. Parse and Compile Time 0.  
  47. Adaptive Server cpu time: 0 ms.  
  48.  -----------  
  49.       524288  
  50. Execution Time 0.  
  51. Adaptive Server cpu time: 0 ms.  Adaptive Server elapsed time: 500 ms.  
  52. (1 row affected)  
  53. 1>  

 

由上可以看出，聚集索引对于select count(*) 几乎没有扫描影响。堆表和聚集索引表上的count是没有什么区别的，甚至于聚集索引表上的IO还要多2（这是因为多了两个聚集索引的数据块造成的）。其实聚集索引并没有单独的保留所有索引列的信息，而只是将表中的行的物理顺序按照聚集索引列的顺序整理了一下，因此对聚集索 引的扫描和对堆表的扫描是一样的，没有什么本质上的区别。

 

 

添加id列上的非聚集索引idx_test4_id

[c-sharp] view plaincopy
  1. 1> create index idx_test4_id on test4(id)  
  2. 2> go  
  3. 1> sp_help test4  
  4. 2> go  
  5.  Name  Owner Object_type Create_date  
  6.  ----- ----- ----------- -------------------  
  7.  test4 dbo   user table  Feb 25 2010  3:44PM  
  8. (1 row affected)  
  9.  Column_name Type    Length Prec Scale Nulls Default_name Rule_name  
  10.          Access_Rule_name Computed_Column_object Identity  
  11.  ----------- ------- ------ ---- ----- ----- ------------ ---------  
  12.          ---------------- ---------------------- ----------  
  13.  id          int          4 NULL  NULL     0 NULL         NULL  
  14.          NULL             NULL                            0  
  15.  name        varchar     30 NULL  NULL     1 NULL         NULL  
  16.          NULL             NULL                            0  
  17. Object has the following indexes  
  18.  index_name   index_keys index_description index_max_rows_per_page  
  19.          index_fillfactor index_reservepagegap index_created       index_local  
  20.  ------------ ---------- ----------------- -----------------------  
  21.          ---------------- -------------------- ------------------- ------------  
  22.  pk_test4_id   id        clustered, unique                       0  
  23.                         0                    0 Feb 25 2010  4:04PM Global Index  
  24.  idx_test4_id  id        nonclustered                            0  
  25.                         0                    0 Feb 25 2010  4:52PM Global Index  
  26. (2 rows affected)  
  27.  index_ptn_name          index_ptn_seg  
  28.  ----------------------- -------------  
  29.  pk_test4_id_1399673003  default  
  30.  idx_test4_id_1399673003 default  
  31. (2 rows affected)  
  32. No defined keys for this object.  
  33.  name  type       partition_type partitions partition_keys  
  34.  ----- ---------- -------------- ---------- --------------  
  35.  test4 base table roundrobin              1 NULL  
  36. (1 row affected)  
  37.  partition_name   partition_id pages row_count segment create_date  
  38.  ---------------- ------------ ----- --------- ------- -------------------  
  39.  test4_1399673003   1399673003  2132    524288 default Feb 25 2010  4:04PM  
  40.  Partition_Conditions  
  41.  --------------------  
  42.  NULL  
  43.  Avg_pages   Max_pages   Min_pages   Ratio(Max/Avg)  
  44.          Ratio(Min/Avg)  
  45.  ----------- ----------- ----------- ---------------------------  
  46.          ---------------------------  
  47.         2132        2132        2132                    1.000000  
  48.                             1.000000  
  49. Lock scheme Allpages  
  50. The attribute 'exp_row_size' is not applicable to tables with allpages lock  
  51. scheme.  
  52. The attribute 'concurrency_opt_threshold' is not applicable to tables with  
  53. allpages lock scheme.  
  54.  exp_row_size reservepagegap fillfactor max_rows_per_page identity_gap  
  55.          ascinserts  
  56.  ------------ -------------- ---------- ----------------- ------------  
  57.          -----------  
  58.             0              0          0                 0            0  
  59.                    0  
  60. (1 row affected)  
  61.  concurrency_opt_threshold optimistic_index_lock dealloc_first_txtpg  
  62.  ------------------------- --------------------- -------------------  
  63.                          0                     0                   0  
  64. (return status = 0)  

 

此时再次执行select count(*) 和select count(1)。查询计划如下：

[c-sharp] view plaincopy
  1. 1> select count(*) from test4  
  2. 2> go  
  3. QUERY PLAN FOR STATEMENT 1 (at line 1).  
  4.     STEP 1  
  5.         The type of query is SELECT.  
  6.         2 operator(s) under root  
  7.        |ROOT:EMIT Operator (VA = 2)  
  8.        |  
  9.        |   |SCALAR AGGREGATE Operator (VA = 1)  
  10.        |   |  Evaluate Ungrouped COUNT AGGREGATE.  
  11.        |   |  
  12.        |   |   |SCAN Operator (VA = 0)  
  13.        |   |   |  FROM TABLE  
  14.        |   |   |  test4  
  15.        |   |   |  Index : idx_test4_id  
  16.        |   |   |  Forward Scan.  
  17.        |   |   |  Positioning at index start.  
  18.        |   |   |  Index contains all needed columns. Base table will not be  
  19. read.  
  20.        |   |   |  Using I/O Size 32 Kbytes for index leaf pages.  
  21.        |   |   |  With MRU Buffer Replacement Strategy for index leaf pages.  
  22. Parse and Compile Time 0.  
  23. Adaptive Server cpu time: 0 ms.  
  24.  -----------  
  25.       524288  
  26. Execution Time 0.  
  27. Adaptive Server cpu time: 0 ms.  Adaptive Server elapsed time: 703 ms.  
  28. (1 row affected)  
  29. 1> select count(1) from test4  
  30. 2> go  
  31. QUERY PLAN FOR STATEMENT 1 (at line 1).  
  32.     STEP 1  
  33.         The type of query is SELECT.  
  34.         2 operator(s) under root  
  35.        |ROOT:EMIT Operator (VA = 2)  
  36.        |  
  37.        |   |SCALAR AGGREGATE Operator (VA = 1)  
  38.        |   |  Evaluate Ungrouped COUNT AGGREGATE.  
  39.        |   |  
  40.        |   |   |SCAN Operator (VA = 0)  
  41.        |   |   |  FROM TABLE  
  42.        |   |   |  test4  
  43.        |   |   |  Index : idx_test4_id  
  44.        |   |   |  Forward Scan.  
  45.        |   |   |  Positioning at index start.  
  46.        |   |   |  Index contains all needed columns. Base table will not be  
  47. read.  
  48.        |   |   |  Using I/O Size 32 Kbytes for index leaf pages.  
  49.        |   |   |  With MRU Buffer Replacement Strategy for index leaf pages.  
  50. Parse and Compile Time 0.  
  51. Adaptive Server cpu time: 0 ms.  
  52.  -----------  
  53.       524288  
  54. Execution Time 1.  
  55. Adaptive Server cpu time: 100 ms.  Adaptive Server elapsed time: 93 ms.  
  56. (1 row affecte  

 

 

可以看出查询引擎使用了非聚集索引idx_test4_id ,执行时间明显减少。因为计算行数这个操作对于全表扫描或是非聚集索引的扫描结果是一样的，而相对来说非聚集索引的数据量是肯定会比表的数据量小很多的，同样的做一次全部扫描所花费的IO也就要少很多了。

 

select count(id) 也是利用了非聚集索引 idx_test4_id。

 

 

结论：

count(*)和count(1)执行的效率是完全一样的。

如果是对特定的列做count的话建立这个列的非聚集索引能对count有很大的帮助。



--------------------------------

实际情况中，还有时间作为条件进行检索的，这时还会扫描全表，我这将时间列也做索引后，效果明显。

Adaptive Server cpu time: 0 ms.  Adaptive Server elapsed time: 23 ms.1> select count(id) from numberListHistory where 1=1 and generateTime < getDate()2> goQUERY PLAN FOR STATEMENT 1 (at line 1).    STEP 1        The type of query is SELECT.        3 operator(s) under root       |ROOT:EMIT Operator (VA = 3)       |       |   |SCALAR AGGREGATE Operator (VA = 2)       |   |  Evaluate Ungrouped COUNT AGGREGATE.       |   |       |   |   |RESTRICT Operator (VA = 1)(5)(0)(0)(0)(0)       |   |   |       |   |   |   |SCAN Operator (VA = 0)       |   |   |   |  FROM TABLE       |   |   |   |  numberListHistory       |   |   |   |  Table Scan.       |   |   |   |  Forward Scan.       |   |   |   |  Positioning at start of table.       |   |   |   |  Using I/O Size 64 Kbytes for data pages.       |   |   |   |  With MRU Buffer Replacement Strategy for data pages.Parse and Compile Time 0.Adaptive Server cpu time: 0 ms.              -----------     26379303  Execution Time 46.Adaptive Server cpu time: 4600 ms.  Adaptive Server elapsed time: 19366 ms.(1 row affected)1> select count(id) from numberListHistory2> goQUERY PLAN FOR STATEMENT 1 (at line 1).    STEP 1        The type of query is SELECT.        2 operator(s) under root       |ROOT:EMIT Operator (VA = 2)       |       |   |SCALAR AGGREGATE Operator (VA = 1)       |   |  Evaluate Ungrouped COUNT AGGREGATE.       |   |       |   |   |SCAN Operator (VA = 0)       |   |   |  FROM TABLE       |   |   |  numberListHistory       |   |   |  Index : idx_NLH       |   |   |  Forward Scan.       |   |   |  Positioning at index start.       |   |   |  Index contains all needed columns. Base table will not beread.       |   |   |  Using I/O Size 64 Kbytes for index leaf pages.       |   |   |  With MRU Buffer Replacement Strategy for index leaf pages.Parse and Compile Time 1.Adaptive Server cpu time: 100 ms.              -----------     26379303  Execution Time 28.Adaptive Server cpu time: 2800 ms.  Adaptive Server elapsed time: 3713 ms.(1 row affected)1> create index idx_nlh_gtime on numberListHistory(generateTime)2> goQUERY PLAN FOR STATEMENT 1 (at line 1).    STEP 1        The type of query is CREATE INDEX.        TO TABLE        numberListHistory        Using I/O Size 8 Kbytes for data pages.Parse and Compile Time 0.Adaptive Server cpu time: 0 ms.The sort for numberListHistory is done in Serial Execution Time 294.Adaptive Server cpu time: 29400 ms.  Adaptive Server elapsed time: 56416 ms.1> select count(id) from numberListHistory where 1=1 and generateTime < getDate()2> goQUERY PLAN FOR STATEMENT 1 (at line 1).    STEP 1        The type of query is SELECT.        3 operator(s) under root       |ROOT:EMIT Operator (VA = 3)       |       |   |SCALAR AGGREGATE Operator (VA = 2)       |   |  Evaluate Ungrouped COUNT AGGREGATE.       |   |       |   |   |RESTRICT Operator (VA = 1)(5)(0)(0)(0)(0)       |   |   |       |   |   |   |SCAN Operator (VA = 0)       |   |   |   |  FROM TABLE       |   |   |   |  numberListHistory       |   |   |   |  Index : idx_nlh_gtime       |   |   |   |  Forward Scan.       |   |   |   |  Positioning by key.       |   |   |   |  Index contains all needed columns. Base table will not beread.       |   |   |   |  Keys are:       |   |   |   |    generateTime ASC       |   |   |   |  Using I/O Size 64 Kbytes for index leaf pages.       |   |   |   |  With MRU Buffer Replacement Strategy for index leafpages.Parse and Compile Time 0.Adaptive Server cpu time: 0 ms.              -----------     26379303  Execution Time 39.Adaptive Server cpu time: 3900 ms.  Adaptive Server elapsed time: 4193 ms.(1 row affected)1>


     

0 0
windows wordpress windows wordpress
原创粉丝点击
热门IT博客
网络电视机顶盒你存大网络电视机顶盒你存大
网页特效制作软件网页特效制作软件
知否txt书包网
阿里云免费主机开通码
惠普打印机是什么端口
linux重启vnc服务
linux内核调用shell
apache jena
手机淘宝店铺装修流程
影楼制作相册软件
手机淘宝扫码付款
mac预览图片怎么删除
网络优化seo
淘宝网开店交多少钱
海马模拟器 mac
手机照片制作软件
算法工程师英文缩写
双十一淘宝客服怎么找
linux系统卸载软件
数据结构java版 pdf
热门问题 老师的惩罚 人脸识别 我在镇武司摸鱼那些年 重生之率土为王 我在大康的咸鱼生活 盘龙之生命进化 天生仙种 凡人之先天五行 春回大明朝 姑娘不必设防，我是瞎子 恒河沙 印度恒河水 恒河猴实验 恒河猴 恒河有多脏 恒河女神 恒河源头 八恒河沙 恒河鲨 恒河矿泉水 老恒河料酒 恒河图片 印度恒河浮尸 印度恒河水有多脏 恒河水有多脏 印度恒河有多脏 印度恒河脏到什么程度 米国内裤恒河浮 恒河沙数反义词 印度恒河烧尸 哈洛的恒河猴实验 干了这碗恒河水 人女恒河浮尸 恒河沙数的意思 印度恒河与浮尸共沐浴 浮尸 房敏跨越爱的恒河 清穿之餐花饮露 恒河月 恒波股份 恒波 恒波加密软件下载 恒波三星手机报价 恒泰 恒泰证券网上营业厅 恒太 深圳工业园 恒泰证券手机版下载 恒泰掌财 恒泽女装 恒泽 新恒洁衣

程序博客网，程序员的互联网技术博客家园。csdn论坛精品 msdn技术资料都在这里