在论坛里看到有人问,有三个列的复合索引,查询条件中只包含两个列是不是就不会走索引了?
经过试验,我发现两点:
1. 只要某查询条件中包含复合索引中的第一个列,该查询就会走索引,如果不包含,怎么样都不会走索引。
什么意思呢?比如说我创建个索引:
Create index idx1_test on test(c1,c2,c3);
当切仅当查询条件中包含c1时,此查询就会走idx1_test索引,否则无论如何都不会走索引。
SQL> select * from test where c1 = '1' and c2 = '2';
----------------------------------------------------------------------------------------
| Id |Operation | Name | Rows | Bytes | Cost (%CPU)|Time |
----------------------------------------------------------------------------------------
| 0 | SELECTSTATEMENT | | 1 | 48 | 0 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 48 | 0 (0)| 00:00:01 |
|* 2 | INDEX RANGESCAN | IDX_TEST | 1 | | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
SQL> select * from test where c1 = '1' and c3 = '3';
----------------------------------------------------------------------------------------
| Id |Operation | Name | Rows | Bytes | Cost (%CPU)|Time |
----------------------------------------------------------------------------------------
| 0 | SELECTSTATEMENT | | 1 | 48 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 48 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGESCAN | IDX_TEST | 1 | | 1 (0)| 00:00:01 |
SQL> select * from test where c3 = '3' and c2 = '2';
--------------------------------------------------------------------------
| Id |Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 48 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST | 1 | 48 | 3 (0)| 00:00:01 |
2. 第二点,是否走索引与查询中的条件排列顺序是无关的。无论第一列在条件中排在什么位置,只要有他,就会走索引。
SQL> select * from test where c3 = '3' and c1 = '1';
----------------------------------------------------------------------------------------
| Id |Operation | Name | Rows | Bytes | Cost (%CPU)|Time |
----------------------------------------------------------------------------------------
| 0 | SELECTSTATEMENT | | 1 | 48 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 48 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGESCAN | IDX_TEST | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 -access("C1"='1' AND "C3"='3')
filter("C3"='3')
SQL> select * from test where c1 = '1' and c3 = '3';
----------------------------------------------------------------------------------------
| Id |Operation | Name | Rows | Bytes | Cost (%CPU)|Time |
----------------------------------------------------------------------------------------
| 0 | SELECTSTATEMENT | | 1 | 48 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 48 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGESCAN | IDX_TEST | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 -access("C1"='1' AND "C3"='3') 我们可以看到,对优化器来说,这两种查询对它来说是没有区别的。
filter("C3"='3')