如何抑制索引的使用,通俗点说就是如何给数据库挖坑,很多人主观上不会有挖坑的想法,但事实上正在做挖坑的事,就如抑制索引这个坑,创建索引就是为了在某些情况下提高SQL的执行效率的,最后因为一些坑,数据库该使用索引的时候却没有使用,造成系统性能低下,大家都得挨板子,下面扯一下曾经在索引上挖过的坑
只说说普通的B树索引,其他的不具代表性
使用不等于查找
比如 <>,!=,索引叶子块上存放了索引列上的数据和rowid,用来查找确定的数据效率很高,如果是不确定的数据,全表扫描效率会更高,但是,在不需要回表的情况下,不等于查询会使用索引查找数据
SQL> select * from test where object_type <> 'TABLE';
Elapsed: 00:00:00.02
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 658 | 288 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| TEST | 7 | 658 | 288 (1)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_TYPE"<>'TABLE')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1032 consistent gets
0 physical reads
0 redo size
1604 bytes sent via SQL*Net to client
524 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 object_type from test where object_type <> 'TABLE';
Elapsed: 00:00:00.02
Execution Plan
----------------------------------------------------------
Plan hash value: 3655030924
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 42 | 82 (0)| 00:00:01 |
|* 1 | INDEX FAST FULL SCAN| IDX_TEST_TYPE | 7 | 42 | 82 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_TYPE"<>'TABLE')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
503 consistent gets
0 physical reads
0 redo size
531 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
在不需要回表的情况下,oracle使用索引快速全扫描查找数据,直接在索引叶子块上就能得到数据。
使用模糊查询
比如 like '%abc%',like '%abc',这两个查询方式是无法使用索引的,前缀查询 like 'abc%'可以使用索引,同样的,不需要回表的查询会使用索引,不论哪种模糊查询。
全模糊查询
SQL> select * from test where object_name like '%TEST%';
121 rows selected.
Elapsed: 00:00:00.09
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3602 | 330K| 288 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| TEST | 3602 | 330K| 288 (1)| 00:00:04 |
--------------------------------------------------------------------------
后缀模糊查询
SQL> select * from test where object_name like '%TEST';
15 rows selected.
Elapsed: 00:00:00.13
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3602 | 330K| 288 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| TEST | 3602 | 330K| 288 (1)| 00:00:04 |
--------------------------------------------------------------------------
前缀模糊查询
SQL> select * from test where object_name like 'TEST%';
Elapsed: 00:00:00.04
Execution Plan
----------------------------------------------------------
Plan hash value: 3700989122
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 188 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 2 | 188 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_TEST_NAME | 2 | | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
不回表查询
SQL> select object_name from test where object_name like '%TEST%';
121 rows selected.
Elapsed: 00:00:00.14
Execution Plan
----------------------------------------------------------
Plan hash value: 1634768693
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3602 | 90050 | 99 (2)| 00:00:02 |
|* 1 | INDEX FAST FULL SCAN| IDX_TEST_NAME | 3602 | 90050 | 99 (2)| 00:00:02 |
--------------------------------------------------------------------------------------
使用NULL检索
比如 is null 或者 is not null,这两个都不会使用索引,B树索引是不会存储空值,所以针对空值的检索是无法使用索引,当然,不回表依然会使用索引。
SQL> select * from test where cs is not null;
Elapsed: 00:00:00.02
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 95 | 288 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| TEST | 1 | 95 | 288 (1)| 00:00:04 |
--------------------------------------------------------------------------
SQL> select * from test where cs is null;
72039 rows selected.
Elapsed: 00:00:00.40
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 72039 | 6683K| 288 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| TEST | 72039 | 6683K| 288 (1)| 00:00:04 |
--------------------------------------------------------------------------
索引列使用函数或者使用运算符
比如 to_char(),to_date(),to_number()或者使用 +,-,*,这种情况除非创建函数索引,基本上不会使用索引,就算是不回表的查询也无法使用索引
SQL> select * from test where to_number(object_id)=100;
Elapsed: 00:00:00.05
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 95 | 288 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| TEST | 1 | 95 | 288 (1)| 00:00:04 |
--------------------------------------------------------------------------
索引列上使用运算符
SQL> select * from test where object_id + 1 = 100;
Elapsed: 00:00:00.03
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 720 | 68400 | 288 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| TEST | 720 | 68400 | 288 (1)| 00:00:04 |
--------------------------------------------------------------------------
格式不匹配
这种情况发生在number型和varchar2型的匹配上最多,尤其是字符串也是数字的,执行时oracle发生隐式转换,就相当于加上了格式转换函数,自然也就无法使用索引
SQL> select * from test where cs=100;
Elapsed: 00:00:00.04
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 100 | 288 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| TEST | 1 | 100 | 288 (1)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER("CS")=100)
执行计划上面有隐式转换的提示,如果索引列是number类型,那么oracle会尝试对varchar2类型进行转换,转换失败则报错,转换没问题是可以正常使用索引
SQL> select * from test where object_id = '100';
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 1345973065
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 100 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 100 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_TEST_ID | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=100)
执行计划没有提示进行转换,我们可以试试使用 ‘0100’
SQL> select * from test where object_id = '0100';
Elapsed: 00:00:00.02
Execution Plan
----------------------------------------------------------
Plan hash value: 1345973065
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 100 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 100 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_TEST_ID | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=100)
使用‘0100’一样能查出结果,说明oracle已经做了类型转换
SQL查询是否使用索引,跟上面这些写法没有绝对的关系,优化器会综合很多因素得出一个代价,然后确定执行计划,以上这些只是诸多因素中的一部分,但是,如果存在这些写法,一般是不会使用索引,所以应该尽量避免。