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

发表评论

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

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

请看我下面的测试代码！

建立一张临时表test3 

1

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

向表中插入测试数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

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条记录!

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

1
2
3
4

set showplan on
go
set statistics time on
go

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

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53

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

1
2

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

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53

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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64

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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56

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 affected)

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

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

结论：

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

————————————————————————————————————
——— 本文为andkylee个人原创，请在尊重作者劳动成果的前提下进行转载；
——— 转载务必注明原始出处 : http://www.dbainfo.net
——— 关键字： sybase 聚簇索引 非聚簇索引 查询计划 行数  count
————————————————————————————————————

  • 本文链接地址：http://www.sybrepair.com/sybase-relation-between-clustered-indexes-and-count.htm；
  • 本文为dbainfo个人原创，请在尊重作者劳动成果的前提下进行转载；
  • 转载务必注明原始出处 : Sybase数据库技术，数据库恢复专家；
  • 对《sybase中的聚簇索引与count（*）的关系》有何疑问或见解，请在本文下方发表；
  • 对网站还有其他问题或建议，请提交在留言板，谢谢！
    • 标签: ,
    目前还没有任何评论.
    :wink: :twisted: :roll: :oops: :mrgreen: :lol: :idea: :evil: :cry: :arrow: :?: :-| :-x :-o :-P :-D :-? :) :( :!: 8-O 8)