复合索引性能问题初探-mysql教程

在《品悟性能优化》一书，4.4.3章节里介绍了复合索引的两个特点：前缀性，可选性。何为前缀性，该书阐述为排除skip scan index的情况（索引前置列的取不多，如性别），约束条件如果不包含复合索引的第一列，则该复合索引不会被用到；何为可选性，该书阐述为字

在《品悟性能优化》一书，4.4.3章节里介绍了复合索引的两个特点：前缀性，可选性。何为前缀性，该书阐述为排除skip scan index的情况（索引前置列的取&＃20540;不多，如性别），约束条件如果不包含复合索引的第一列，则该复合索引不会被用到；何为可选性，该书阐述为字段&＃20540;越多，可选性越强，定位记录越少，查询效率越高。即查询返回记录少的列应该放在复合索引的前面。

而在《收获不止oracle》一书，5.2.1.9章节里也介绍了复合索引的两个特点：①在等&＃20540;查询的情况下，复合索引的列无论那一列在前，性能都一样。②复合索引的两列，当一列是范围查询，一列是等&＃20540;查询的情况下，等&＃20540;查询列在前，范围查询列在后，这样的索引才是高效的。

根据上述对复合索引两位作者的不同见解之处，我们通过测试，辨别下实事的真相。测试环境是11.2.0.3单实例，oracle linux 5.4

SQL> create table t as select * from dba_objects;

Table created.

SQL> select count(*) from t;

COUNT(*)
----------
109971

SQL> select count(distinct object_type) from t;

COUNT(DISTINCTOBJECT_TYPE)
--------------------------
45

SQL> select count(distinct object_id) from t;

COUNT(DISTINCTOBJECT_ID)
------------------------
109971

SQL> create index ind_t_obj_id on t(object_id,object_type);

Index created.

SQL> create index ind_t_obj_ty on t(object_type,object_id);

Index created.

SQL> select /*&＃43; index(t,ind_t_obj_ty) */ * from T where object_id=5585 and object_type='TABLE';

Execution Plan
----------------------------------------------------------
Plan hash value: 2583045626

--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 2277 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 11 | 2277 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_T_OBJ_TY | 2 | | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("OBJECT_TYPE"='TABLE' AND "OBJECT_ID"=5585)

Note
-----
- dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
1622 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> select /*&＃43; index(t,ind_t_obj_id) */ * from T where object_id=5585 and object_type='TABLE';

Execution Plan
----------------------------------------------------------
Plan hash value: 607336433

--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 2277 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 11 | 2277 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_T_OBJ_ID | 2 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("OBJECT_ID"=5585 AND "OBJECT_TYPE"='TABLE')

Note
-----
- dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
1622 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

我们这里看到等&＃20540;查询结果是不一样的，证实了《品悟》一书的可选性是正确的。

SQL> select /*&＃43; index(t,ind_t_obj_ty) */ * from T where object_id > 20 and object_id <2000 and object_type='TABLE';

488 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2583045626

--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 912 | 184K| 49 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 912 | 184K| 49 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_T_OBJ_TY | 912 | | 6 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("OBJECT_TYPE"='TABLE' AND "OBJECT_ID">20 AND "OBJECT_ID"<2000)

Note
-----
- dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
85 consistent gets
0 physical reads
0 redo size
51220 bytes sent via SQL*Net to client
872 bytes received via SQL*Net from client
34 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
488 rows processed

SQL> select /*&＃43; index(t,ind_t_obj_id) */ * from T where object_id > 20 and object_id <2000 and object_type='TABLE';

488 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 607336433

--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 912 | 184K| 11 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 912 | 184K| 11 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_T_OBJ_ID | 9 | | 10 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("OBJECT_ID">20 AND "OBJECT_TYPE"='TABLE' AND "OBJECT_ID"<2000)
filter("OBJECT_TYPE"='TABLE')

Note
-----
- dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
87 consistent gets
0 physical reads
0 redo size
51220 bytes sent via SQL*Net to client
872 bytes received via SQL*Net from client
34 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
488 rows processed

结果，不言而喻。以上两个sql在不用hint的时候，CBO都会自动选择走IND_T_OBJ_ID。所以，《收获不止oracle》一书的结论有待商榷（如果不是我测试有问题的话）。