ASE查询速度问题

[willbeike]

请教各位：
我有一个表table_a，上面有2个字段column_a与column_b，不是主键，都建了索引(普通索引)，（表table_a数据库120万左右、字段数30左右）。
我查询时如果单独用其中的一个来查询速度都很快，但当我把两个字段一起作为条件查询时起码慢了10倍左右正常来说两个条件一起应该过滤掉更多的行，速度更快才是，但缺出现这种情况，但也不可能在column_a与column_b上建一个结合索引，因为可能还有3个条件、4个条件等情况出现，望各位达人帮忙看看是什么原因。谢谢

[Tigersong]

不确定你的查询脚本具体是什么样子! 不过你可以查看相应的查询计划, 看有没有用道索引! 出现这种情况, 一般来说你在第二种条件下没有用到索引导致

[willbeike]

我就在sybase的查询分析器里写的查询语句，很简单的
以下是三个查询语句的执行计划
(1)测试一：SQL语句：select count(column_a) from table_a where column_a = 'cfk' (返回数值371942 ；耗时 < 1S)

QUERY PLAN FOR STATEMENT 1 (at line 1).




STEP 1
The type of query is SET OPTION ON.




QUERY PLAN FOR STATEMENT 2 (at line 2).




STEP 1
The type of query is SET STATISTICS ON.




QUERY PLAN FOR STATEMENT 3 (at line 5).




STEP 1
The type of query is SELECT.
Evaluate Ungrouped COUNT AGGREGATE.


FROM TABLE
table_a
Nested iteration.
Index : I_table_a_column_a
Forward scan.
Positioning by key.
Index contains all needed columns. Base table will not be read.
Keys are:
column_a ASC
Using I/O Size 4 Kbytes for index leaf pages.
With MRU Buffer Replacement Strategy for index leaf pages.


STEP 2
The type of query is SELECT.


Total writes for this command: 0
Table: table_a scan count 1, logical reads: (regular=1380 apf=0 total=1380), physical reads: (regular=476 apf=1098 total=1574), apf IOs used=904
Total writes for this command: 5
(1 row affected)



(2)测试二：SQL语句：select count(column_a) from table_a where column_b = 'ZC' (返回数值61162 ；耗时 < 1S)
QUERY PLAN FOR STATEMENT 1 (at line 1).




STEP 1
The type of query is SET OPTION ON.




QUERY PLAN FOR STATEMENT 2 (at line 2).




STEP 1
The type of query is SET STATISTICS ON.




QUERY PLAN FOR STATEMENT 3 (at line 5).




STEP 1
The type of query is SELECT.
Evaluate Ungrouped COUNT AGGREGATE.


FROM TABLE
table_a
Nested iteration.
Index : I_table_a_column_b
Forward scan.
Positioning by key.
Index contains all needed columns. Base table will not be read.
Keys are:
column_b ASC
Using I/O Size 4 Kbytes for index leaf pages.
With LRU Buffer Replacement Strategy for index leaf pages.


STEP 2
The type of query is SELECT.


Total writes for this command: 0
Total writes for this command: 0
Table: table_a scan count 1, logical reads: (regular=215 apf=0 total=215), physical reads: (regular=204 apf=11 total=215), apf IOs used=11
Total writes for this command: 0
(1 row affected)


(3)测试三：SQL语句：select count(column_a) from table_a where column_a = 'cfk' and column_b = 'ZC' (返回数值 2062 ；耗时 14-16S)
QUERY PLAN FOR STATEMENT 1 (at line 1).




STEP 1
The type of query is SET OPTION ON.




QUERY PLAN FOR STATEMENT 2 (at line 2).




STEP 1
The type of query is SET STATISTICS ON.




QUERY PLAN FOR STATEMENT 3 (at line 5).




STEP 1
The type of query is SELECT.
Evaluate Ungrouped COUNT AGGREGATE.


FROM TABLE
table_a
Nested iteration.
Index : I_table_a_column_b
Forward scan.
Positioning by key.
Keys are:
column_b ASC
Using I/O Size 4 Kbytes for index leaf pages.
With MRU Buffer Replacement Strategy for index leaf pages.
Using I/O Size 4 Kbytes for data pages.
With MRU Buffer Replacement Strategy for data pages.


STEP 2
The type of query is SELECT.


Total writes for this command: 0
Total writes for this command: 0
Table: table_a scan count 1, logical reads: (regular=9499 apf=5 total=9504), physical reads: (regular=7633 apf=2060 total=9693), apf IOs used=1856
Total writes for this command: 1
(1 row affected)

[Tigersong]

对于前面2种
Index contains all needed columns. Base table will not be read.
此时查询不需要一层一层遍历,因为索引页包含了所有条件列

对于第三种则需要根据条件扫描相应的下一级甚至数据页,所以...

[willbeike]

还要再看看

[willbeike]

我怎么给不了分的?