首页 > 默认 > ZT:批判“通过反转键索引优化like操作”这个错误的观点

ZT:批判“通过反转键索引优化like操作”这个错误的观点

2013年6月14日 14号 发表评论 阅读评论

今天在看了一些索引的知识,在网上看到一个文章 感觉不错 拿出来分享一下。 内容如下:

首先我们来看看最常见的B树索引,B树索引适用于几乎所有的场合,也是系统中使用最为广泛的索引形式。实际上我们所说的普通索引,反转键索引、降序索引、函数索引等都是B树结构的,其物理存储结构是完全相同的。与之相对应的位图索引是完全不同的存储结构,位图索引不是树状结构,没有枝节点,只有叶节点。对于B树索引的操作可以进行索引唯一性扫描、索引范围扫描、快速索引全扫描和索引全扫描,而对于位图索引的访问方式只有一种,就是索引全扫描。在使用位图索引的时候,只有对索引完全扫描一遍,才能找到所有的所需要的行。

从昨天的知识点我们知道,索引是一个树状的结构,组织形式是一颗扩展了的B树,和普通B树不同的是,这棵B树的所有叶节点上有一条双向链,称为叶节点链。这条双向链是根据索引键值的大小进行排序的。这条双向链的存在十分关键,这是实现索引范围扫描的最关键的技术。当进行索引范围扫描时,首先通过B树的定位算法,从根开始,找到范围扫描起始键值的位置,然后从这个位置开始,通过叶节点链按照升序或者降序的方式扫描相关的叶节点,直到找到超出范围扫描范围的键值为止。

最为普通的索引是按照键值升序排列的,索引树的右面的枝叶的键值总比左边的大。而如果我们设计了降序索引,那么情况正好倒过来,索引左面的枝叶的键值总比右面的大。

函数索引是一种特殊的B树索引,引入函数索引的目的是解决那些在使用过程中,必须在字段上做函数运算的情况。一般情况下,我们在编程时都会建议开发人员不要在WHERE条件中的表字段上使用函数,因为这样我们无法为其设计索引。不过事实上,我们无法杜绝这样的函数的存在。比如说,我们必须从某个人字段取第二和第三位进行比较:WHERE substr(id,2,3)='ID',如果这样的查询条件放弃函数的话,程序员的处理将十分复杂。函数索引为这种情况提供了很好的帮助,如果这个查询条件使用索引效果较好的话,我们可以在ID字段上创建一个以函数substr(id,2,3)为键的B树索引。事实上,在绝大多数应用系统中,函数索引都是不可避免的,不过不幸的是,在我做过的优化项目中,我基本上没有看到过用户在使用这种索引。

接下来我们来讨论一下反转键索引(reverse key index),这是一种十分著名的索引,反转键索引是在存储键值的时候,先将键值进行翻转。比如'1234'存储在索引中的键值是'4321'。设计反转键索引的目的是解决索引的热块冲突问题。索引块出现热块冲突是在性能优化时经常会碰到的问题,比如一个主键是通过sequence生 成的,那么主键索引就可能成为热块。这种情况下,如果我们确定针对主键的查询不存在或者很少有索引范围扫描,那么我们可以考虑使用反转键索引来解决主键的 热块冲突问题。反转键索引解决索引热块冲突的原理很简单,就是通过键值的反转,打乱索引数据块中的数据组织,从而将热点数据分散到不同的索引数据块中。

不过除了解决热块冲突的问题外,DBA界还流传着反转键索引可以解决 like '%abc'无法随用索引的问题。粗想起来,还确实是这么回事,like '%abc'这样的条件,由于通配符在第一个字符,因此这样的查询条件,无法进行索引范围扫描,因此一般情况下使用全表扫描比较合适。不过在某些情况下,如果表十分巨大,这种全表扫描成本太高,如果能使用索引就好了。而反转键索引正好在存储键值的时候是反转过来的,1abc,2abc在索引键里的存储为abc1,abc2,这种情况下,做就可以通过范围扫描将符合条件的记录找出来吗?这个解释似乎是很合理,我也曾经被这个理论所蒙蔽过一段时间,直到有一天我自己做了一个实验,才发现问题远非那么简单。下面我们来回顾一下这个实验。首先我们创建测试表:

DROP TABLE TINDEX;

CREATE TABLE TINDEX as SELECT DISTINCT OBJECT_NAME,OBJECT_ID,OBJECT_TYPE,CREATED,STATUS,TEMPORARY,TIMESTAMP,'ABCDEFGHIJKLMNOPQRSTUVWXYZ1234456' abc

from dba_objects;

INSERT INTO TINDEX SELECT DISTINCT OBJECT_NAME,OBJECT_ID,OBJECT_TYPE,CREATED,STATUS,TEMPORARY,TIMESTAMP,'ABCDEFGHIJKLMNOPQRSTUVWXYZ1234456' abc

from dba_objects;

create index  idx_tindex_name on tindex(object_name ) reverse;

然后做一次表分析:

exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'scott', tabname=>'tindex', estimate_percent=>30, -

cascade=>true, degree=>2);

似乎一切都准备好了,下面我们来测试一下反转键索引是否真的能够解决like语句的问题:

SQL> set autotrace traceonly

select *  froM tindex where object_name like '%TINDEX';

SQL>

Execution Plan

----------------------------------------------------------

Plan hash value: 2264840918

----------------------------------------------------------------------------

| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |        |  5297 |   553K|   103   (3)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| TINDEX |  5297 |   553K|   103   (3)| 00:00:01 |

----------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("OBJECT_NAME" LIKE '%TINDEX')

Statistics

----------------------------------------------------------

1  recursive calls

0  db block gets

1717  consistent gets

0  physical reads

0  redo size

939  bytes sent via SQL*Net to client

400  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

2  rows processed

似乎索引并没有被自动使用,我们使用hint强制索引看看:

SQL> select /*+ INDEX(TINDEX idx_tindex_name ) */ *  froM tindex where object_name like '%TINDEX';

Execution Plan

----------------------------------------------------------

Plan hash value: 2021627753

-----------------------------------------------------------------------------------------------

| Id  | Operation                   | Name            | Rows  | Bytes | Cost (%CPU)| Time     |

-----------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |                 |  5297 |   553K|  5548   (1)| 00:00:54 |

|   1 |  TABLE ACCESS BY INDEX ROWID| TINDEX          |  5297 |   553K|  5548   (1)| 00:00:54 |

|*  2 |   INDEX FULL SCAN           | IDX_TINDEX_NAME |  5287 |       |   520   (1)| 00:00:05 |

-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - filter("OBJECT_NAME" LIKE '%TINDEX')

Statistics

----------------------------------------------------------

1  recursive calls

0  db block gets

520  consistent gets

0  physical reads

0  redo size

939  bytes sent via SQL*Net to client

400  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

2  rows processed

虽然使用了索引,但是扫描方式是全索引扫描,而不是我们期待的索引范围扫描。看样子反转键索引并不能解决这个问题,我以前是被忽悠了。于是我继续做实验:

SQL> create index idx_tindex_func on tindex(reverse(object_name));

Index created.

SQL> exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'scott', tabname=>'tindex', estimate_percent=>30, -

> cascade=>true, degree=>2);

PL/SQL procedure successfully completed.

SQL> select *  froM tindex where reverse(object_name) like 'XEDNIT%';

Execution Plan

----------------------------------------------------------

Plan hash value: 1286384425

-----------------------------------------------------------------------------------------------

| Id  | Operation                   | Name            | Rows  | Bytes | Cost (%CPU)| Time     |

-----------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |                 |     4 |   436 |     7   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| TINDEX          |     4 |   436 |     7   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IDX_TINDEX_FUNC |     4 |       |     3   (0)| 00:00:01 |

-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access(REVERSE("OBJECT_NAME") LIKE 'XEDNIT%')

filter(REVERSE("OBJECT_NAME") LIKE 'XEDNIT%')

Statistics

----------------------------------------------------------

1  recursive calls

0  db block gets

6  consistent gets

0  physical reads

0  redo size

939  bytes sent via SQL*Net to client

400  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

2  rows processed

这才是我们所需要的效果,通过reverse函数,然后将%TINDEX反转为XEDNIT%,才真正的解决了这个问题,这个SQL的开销是原SQL的几百分之一。实际上这种解决方案只能在修改应用的前提下实现,不如使用反转键索引这么简单,而且用途广泛。不过我们也终于通过实验纠正了一个错误的,流传甚广的误解。我在网上通过google查阅了大量的关于此话题的英文资料,终于明白了这个误解的来源,最初的时候,网上确实有一篇文章,介绍使用reverse函数解决这个问题,后来这篇文章在被转载的时候

SELECT *

FROM customer

WHERE Cust_Name LIKE '%Vilas%'

修改为:

SELECT *

FROM customer

WHERE reverse(Cust_Name) LIKE '%saliV%';

被错误的写成了:

SELECT *

FROM customer

WHERE Cust_Name LIKE '%saliV%';

以此版本为蓝本,终于引发了通过反转键索引优化like操作这个错误的观点。由此可见,网络上的知识,不经过自己验证就全盘吸收是多么危险。

不过以Oracle反转键索引的存储结构,确实具备对Like条件做范围扫描的基础,只不过这样的扫描,和以往Oracle提供的任何一种索引扫描技术都不相同,是一种全新的索引扫描方式。也许在Oracle 12或者13里,真的会出现类似的功能呢,Oracle的美工新版本给人带来的惊喜一直都是出乎大多数人的意外的。

 

 

关于作者:

昵称:14号
档案信息:梦里不知身是客,一响贪欢!
联系方式:你可以通过lysmsz@163.com联系作者
点击查看14号发表过的所有文章...
本文永久链接: http://blog.retailsolution.cn/archives/3401

 

 

对本文的评价:

1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
Loading ... Loading ...

 

 

分类: 默认 标签:
  1. 本文目前尚无任何评论.
  1. 本文目前尚无任何 trackbacks 和 pingbacks.
您必须在 登录 后才能发布评论.