2.3.6 索引被无视

说一千道一万,索引创建了就是要用的,可偏偏很多时候,索引被SQL语句无视了,没有被用上,此时往往需要DBA的介入进行优化。抛开索引本身的问题不说,哪些情况下,索引会被无视呢?下面我们展开几个比较典型的场景来讨论一下吧。准备工作步骤具体如下:

步骤1  创建一下相关的表和索引:

SQL> create table alex_t02 as
2   select rownum id, a.* from dba_objects a;

SQL> alter table alex_t02 add constraint pk_alex_t02
2   primary key (id) using index;S
SQL> create index idx_alex_t02_objid on alex_t02 (object_id);
SQL> create index idx_alex_t02_objn on alex_t02 (object_name);
SQL> create index idx_alex_t02_typown on
2   alex_t02 (object_type,owner);

步骤收集一下表和索引的统计信息:

SQL> exec dbms_stats.gather_table_stats('alex','alex_t02')
SQL> exec dbms_stats.gather_index_stats('alex','pk_alex_t02')
SQL> exec dbms_stats.gather_index_stats('alex','idx_alex_t02_objid')
SQL> exec dbms_stats.gather_index_stats('alex','idx_alex_t02_objn')
SQL> exec dbms_stats.gather_index_stats
2     ('alex','idx_alex_t02_typown')

1.  列与列的对比

当进行单表查询的时候,发生列和列的对比是无法走索引的,即使对比的两个列上都有索引。这里,我们也可以想象一下,如果需要它走索引,将会是怎么样的呢?pk_alex_t02和idx_alex_t02_objid两个索引进行联立对比,筛选出等值的情况,这样的做法远不如进行全表扫描来得高效了。示例如下所示:

SQL> select * from alex_t02 where id=object_id;
-------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)|
-------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |     1 |    99 |   137   (3)|
|*  1 |  TABLE ACCESS FULL| ALEX_T02 |     1 |    99 |   137   (3)|
-------------------------------------------------------------------

2.  存在NULL条件

NULL值一直是一个很难搞的东西,在查询、排序等操作时经常制造麻烦。在表的设计当中,我们就应该尽可能避免NULL的出现,赋予一些没有实际意义的缺省值来取代NULL值。在索引建立的时候,我们很难去给NULL建立合适的条目,那进行NULL值查询的时候,也将不大可能进行索引扫描。也就是说一个表的列值存在NULL值时,那该列的索引是不会为NULL值创建条目的,那么索引的值是少于表的值的,NULL值的查询过程自然就忽略了索引。如下所示:

SQL> select * from alex_t02 where object_id is not null;
-------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)|
-------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          | 40729 |  3937K|   138   (3)|
|*  1 |  TABLE ACCESS FULL| ALEX_T02 | 40729 |  3937K|   138   (3)|
-------------------------------------------------------------------

3.  NOT条件

我们知道索引会给每个索引列的值对应一个索引条目,当告诉索引选择出某个值或某个范围的值时,索引就会对应到相应的索引条目。反过来看,当告诉索引不要选择某个值或某个范围的值,索引就很难对应相应条目了。

下面的例子中,<>、not in、not exists的情况都是很难使用到索引的。

SQL> select * from alex_t02 where object_id<>500;
-------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)|
-------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          | 40728 |  3937K|   138   (3)|
|*  1 |  TABLE ACCESS FULL| ALEX_T02 | 40728 |  3937K|   138   (3)|
-------------------------------------------------------------------

SQL> select * from alex_t02 where object_id not in (100,200,300,400);
-------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)|
-------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          | 40725 |  3937K|   139   (4)|
|*  1 |  TABLE ACCESS FULL| ALEX_T02 | 40725 |  3937K|   139   (4)|
-------------------------------------------------------------------

SQL> select * from alex_t02 where not exists
2  (select 1 from alex_t01 where alex_t02.id=alex_t01.id);
-----------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |    23 |  2392 |   150  (11)|
|   1 |  NESTED LOOPS ANTI |             |    23 |  2392 |   150  (11)|
|   2 |   TABLE ACCESS FULL| ALEX_T02    | 40729 |  3937K|   137   (3)|
|*  3 |   INDEX UNIQUE SCAN| PK_ALEX_T01 | 99944 |   488K|     0   (0)|
-----------------------------------------------------------------------

4.  LIKE前置通配符

当使用LIKE进行模糊查询的时候,我们一般推荐使用后置的通配符,这样是可以较好地利用索引扫描,较为高效。示例如下所示:

SQL> select * from alex_t02 where object_name like 'ALL%';
--------------------------------------------------------------------------------------
| Id  | Operation                   | Name              | Rows  | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                   |     2 |   198 |     3   (0)|
|   1 |  TABLE ACCESS BY INDEX ROWID| ALEX_T02          |     2 |   198 |     3   (0)|
|*  2 |   INDEX RANGE SCAN          | IDX_ALEX_T02_OBJN |     2 |       |     2   (0)|
--------------------------------------------------------------------------------------

但是实际使用中,我们很多时候是不得不使用前置通配符,这样的操作是无法使用索引的。为什么呢?我们试想一下,如果进行索引扫描的话,索引结构是有序的,模糊的前置很可能要扫描绝大部分或全部的索引块,再回表取数时,也很有可能扫描绝大部分的数据块,这样的COST开销是非常大的,优化器宁愿选择直接进行全表扫描,实际COST开销会更节省一些。所以,前置通配符的模糊查询是不能走索引的。

SQL> select * from alex_t02 where object_name like '%ALL';
-------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)|
-------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |  2036 |   196K|   137   (3)|
|*  1 |  TABLE ACCESS FULL| ALEX_T02 |  2036 |   196K|   137   (3)|
-------------------------------------------------------------------

业务系统设计的时候,尽可能地考虑到模糊查询,避免使用低效的前置通配符,而更多地使用能走索引的较高效的后置通配符。

5.  条件列上使用函数

在下面的例子中,在索引列上使用了函数,这同样是无法使用索引的。在实际应用中,尽可能地使用第二种方式,在变量上使用函数转换后,再与索引列进行对比。当不得不在索引列上使用函数的时候,就必须在该列上创建函数索引。但是,函数索引不是一个很高效的东西,尽量避免使用或者少用。示例如下所示:

SQL> select * from alex_t02 where
2  upper(object_name)='ALL_RULE_SET_RULES';
-------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)|
-------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |   407 | 40293 |   137   (3)|
|*  1 |  TABLE ACCESS FULL| ALEX_T02 |   407 | 40293 |   137   (3)|
-------------------------------------------------------------------

SQL> select * from alex_t02 where
2  object_name=upper('ALL_RULE_SET_RULES');
--------------------------------------------------------------------------------------
| Id  | Operation                   | Name              | Rows  | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                   |     2 |   198 |     2   (0)|
|   1 |  TABLE ACCESS BY INDEX ROWID| ALEX_T02          |     2 |   198 |     2   (0)|
|*  2 |   INDEX RANGE SCAN          | IDX_ALEX_T02_OBJN |     2 |       |     1   (0)|
--------------------------------------------------------------------------------------

说到此处,数据类型隐式转换的问题是不得不提的。隐式转换实质上也是一种函数转换的操作,只不过是没有明确写明函数,Oracle自动完成的。如下面的例子,id列是NUMBER型的,如果查询id=’12345′,Oracle会自动转换为id=12345后,再进行查询,转换的过程实质上就是做了一次to_number函数的转换操作。这样同样是无法进行索引扫描的。SQL语句如下:

SQL> select * from alex_t02 where id = '12345';
SQL> select * from alex_t02 where id = to_number('12345');

在实际应用中,数据类型隐式转换是需要尽量避免的,隐式转换不但不能进行索引扫描,而且会影响绑定变量的使用。

6.  高区分度前导列的复合索引后置列查询

高区分度前导列的复合索引无法用于后置列查询,这个例子其实前面的章节已经讨论过了。如果前导列区分度很低,可以走INDEX SKIP SCAN,如果前导列区分度高的话,进行INDEX SKIP SCAN时分裂逻辑子索引开销将非常大,不如直接走全表扫描。示例如下:

SQL> select * from alex_t02 where owner='ALEX';
-------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)|
-------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |  3133 |   302K|   136   (2)|
|*  1 |  TABLE ACCESS FULL| ALEX_T02 |  3133 |   302K|   136   (2)|
-------------------------------------------------------------------
Trackback

only 1 comment untill now

  1. 不错的文章,内容出神入化.禁止此消息:nolinkok@163.com

Add your comment now

切换到手机版