如何抑制索引的使用

于 2016-12-15 14:09:43 发布
阅读量358 收藏
点赞数
CC 4.0 BY-SA版权
分类专栏： 小技 文章标签： 抑制索引
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/oraclesys10g/article/details/53669761

如何抑制索引的使用，通俗点说就是如何给数据库挖坑，很多人主观上不会有挖坑的想法，但事实上正在做挖坑的事，就如抑制索引这个坑，创建索引就是为了在某些情况下提高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、付费专栏及课程。

余额充值