如何抑制索引的使用

如何抑制索引的使用,通俗点说就是如何给数据库挖坑,很多人主观上不会有挖坑的想法,但事实上正在做挖坑的事,就如抑制索引这个坑,创建索引就是为了在某些情况下提高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查询是否使用索引,跟上面这些写法没有绝对的关系,优化器会综合很多因素得出一个代价,然后确定执行计划,以上这些只是诸多因素中的一部分,但是,如果存在这些写法,一般是不会使用索引,所以应该尽量避免。




评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值