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

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

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

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

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

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

 

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

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

 

请看我下面的测试代码！

 

建立一张临时表test3

 

create table test3(id int not null,name varchar(30) null)

 

向表中插入测试数据

 

 

insert into test3 select 1,'liu' go insert into test3 select 2,'zhang' go insert into test3 select 3,'wang' go insert into test3 select 4,'li' go 循环插入 insert into test4 select count(*)+id,name from test4 go 18 1> select count(*) from test4 2> go ----------- 524288 (1 row affected) 循环插入了524288条记录!

 

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

 

set showplan on go set statistics time on go

 

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

 

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

1> select count(*) from test4 2> go QUERY 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 | | | test4 | | | Table Scan. | | | Forward Scan. | | | Positioning at start of table. | | | Using I/O Size 32 Kbytes for data pages. | | | With MRU Buffer Replacement Strategy for data pages. Parse and Compile Time 0. Adaptive Server cpu time: 0 ms. ----------- 524288 Execution Time 1. Adaptive Server cpu time: 100 ms. Adaptive Server elapsed time: 156 ms. (1 row affected)

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

 

1> select count(1) from test4 2> go QUERY 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 | | | test4 | | | Table Scan. | | | Forward Scan. | | | Positioning at start of table. | | | Using I/O Size 32 Kbytes for data pages. | | | With MRU Buffer Replacement Strategy for data pages. Parse and Compile Time 0. Adaptive Server cpu time: 0 ms. ----------- 524288 Execution Time 1. Adaptive Server cpu time: 100 ms. Adaptive Server elapsed time: 220 ms.

 

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

 

 

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

1> select count(id) from test4 2> go QUERY 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 | | | test4 | | | Table Scan. | | | Forward Scan. | | | Positioning at start of table. | | | Using I/O Size 32 Kbytes for data pages. | | | With MRU Buffer Replacement Strategy for data pages. Parse and Compile Time 0. Adaptive Server cpu time: 0 ms. ----------- 524288 Execution Time 1. Adaptive Server cpu time: 100 ms. Adaptive Server elapsed time: 140 ms. (1 row affected) 1> select count(name) from test4 2> go QUERY 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 | | | test4 | | | Table Scan. | | | Forward Scan. | | | Positioning at start of table. | | | Using I/O Size 32 Kbytes for data pages. | | | With MRU Buffer Replacement Strategy for data pages. Parse and Compile Time 0. Adaptive Server cpu time: 0 ms. ----------- 524288 Execution Time 1. Adaptive Server cpu time: 100 ms. Adaptive Server elapsed time: 236 ms. (1 row affected) 1>

 

 

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

alter table test4 add constraint pk_test4_id primary key (id) go

 

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

 

1> select count(*) from test4 2> go QUERY 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 | | | test4 | | | Table Scan. | | | Forward Scan. | | | Positioning at start of table. | | | Using I/O Size 32 Kbytes for data pages. | | | With MRU Buffer Replacement Strategy for data pages. Parse and Compile Time 0. Adaptive Server cpu time: 0 ms. ----------- 524288 Execution Time 2. Adaptive Server cpu time: 200 ms. Adaptive Server elapsed time: 736 ms. (1 row affected) 1> select count(1) from test4 2> go QUERY 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 | | | test4 | | | Table Scan. | | | Forward Scan. | | | Positioning at start of table. | | | Using I/O Size 32 Kbytes for data pages. | | | With MRU Buffer Replacement Strategy for data pages. Parse and Compile Time 0. Adaptive Server cpu time: 0 ms. ----------- 524288 Execution Time 0. Adaptive Server cpu time: 0 ms. Adaptive Server elapsed time: 500 ms. (1 row affected) 1>

 

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

 

 

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

1> create index idx_test4_id on test4(id) 2> go 1> sp_help test4 2> go Name Owner Object_type Create_date ----- ----- ----------- ------------------- test4 dbo user table Feb 25 2010 3:44PM (1 row affected) Column_name Type Length Prec Scale Nulls Default_name Rule_name Access_Rule_name Computed_Column_object Identity ----------- ------- ------ ---- ----- ----- ------------ --------- ---------------- ---------------------- ---------- id int 4 NULL NULL 0 NULL NULL NULL NULL 0 name varchar 30 NULL NULL 1 NULL NULL NULL NULL 0 Object has the following indexes index_name index_keys index_description index_max_rows_per_page index_fillfactor index_reservepagegap index_created index_local ------------ ---------- ----------------- ----------------------- ---------------- -------------------- ------------------- ------------ pk_test4_id id clustered, unique 0 0 0 Feb 25 2010 4:04PM Global Index idx_test4_id id nonclustered 0 0 0 Feb 25 2010 4:52PM Global Index (2 rows affected) index_ptn_name index_ptn_seg ----------------------- ------------- pk_test4_id_1399673003 default idx_test4_id_1399673003 default (2 rows affected) No defined keys for this object. name type partition_type partitions partition_keys ----- ---------- -------------- ---------- -------------- test4 base table roundrobin 1 NULL (1 row affected) partition_name partition_id pages row_count segment create_date ---------------- ------------ ----- --------- ------- ------------------- test4_1399673003 1399673003 2132 524288 default Feb 25 2010 4:04PM Partition_Conditions -------------------- NULL Avg_pages Max_pages Min_pages Ratio(Max/Avg) Ratio(Min/Avg) ----------- ----------- ----------- --------------------------- --------------------------- 2132 2132 2132 1.000000 1.000000 Lock scheme Allpages The attribute 'exp_row_size' is not applicable to tables with allpages lock scheme. The attribute 'concurrency_opt_threshold' is not applicable to tables with allpages lock scheme. exp_row_size reservepagegap fillfactor max_rows_per_page identity_gap ascinserts ------------ -------------- ---------- ----------------- ------------ ----------- 0 0 0 0 0 0 (1 row affected) concurrency_opt_threshold optimistic_index_lock dealloc_first_txtpg ------------------------- --------------------- ------------------- 0 0 0 (return status = 0)

 

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

1> select count(*) from test4 2> go QUERY 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 | | | test4 | | | Index : idx_test4_id | | | Forward Scan. | | | Positioning at index start. | | | Index contains all needed columns. Base table will not be read. | | | Using I/O Size 32 Kbytes for index leaf pages. | | | With MRU Buffer Replacement Strategy for index leaf pages. Parse and Compile Time 0. Adaptive Server cpu time: 0 ms. ----------- 524288 Execution Time 0. Adaptive Server cpu time: 0 ms. Adaptive Server elapsed time: 703 ms. (1 row affected) 1> select count(1) from test4 2> go QUERY 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 | | | test4 | | | Index : idx_test4_id | | | Forward Scan. | | | Positioning at index start. | | | Index contains all needed columns. Base table will not be read. | | | Using I/O Size 32 Kbytes for index leaf pages. | | | With MRU Buffer Replacement Strategy for index leaf pages. Parse and Compile Time 0. Adaptive Server cpu time: 0 ms. ----------- 524288 Execution Time 1. Adaptive Server cpu time: 100 ms. Adaptive Server elapsed time: 93 ms. (1 row affecte

 

 

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

 

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

 

 

结论：

count(*)和count(1)执行的效率是完全一样的。
如果是对特定的列做count的话建立这个列的非聚集索引能对count有很大的帮助。