----------------------------------------------------------------------------
---- 本文为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有很大的帮助。
--------------------------------
实际情况中,还有时间作为条件进行检索的,这时还会扫描全表,我这将时间列也做索引后,效果明显。
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> go
QUERY 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 numberListHistory
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
| | | numberListHistory
| | | Index : idx_NLH
| | | Forward Scan.
| | | Positioning at index start.
| | | Index contains all needed columns. Base table will not be
read.
| | | 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> go
QUERY 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> go
QUERY 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 be
read.
| | | | Keys are:
| | | | generateTime ASC
| | | | Using I/O Size 64 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.
-----------
26379303
Execution Time 39.
Adaptive Server cpu time: 3900 ms. Adaptive Server elapsed time: 4193 ms.
(1 row affected)
1>