Nemo

Nemo 关注TA

路漫漫其修远兮,吾将上下而求索。

Nemo

Nemo

关注TA

路漫漫其修远兮,吾将上下而求索。

  •  普罗旺斯
  • 负责帅就完事了
  • 写了1,493,291字

该文章投稿至Nemo社区   数据库  板块 复制链接


SQL 使用like '%ABC' 和 like '%ABC%'的优化

发布于 2017/06/07 09:56 2,493浏览 0回复 2,533

一般情况下,sql中使用col_name like 'ABC%‘的情况才能使用到col_name字段上的索引,这种情况不再赘述。

如果是col_name like '%ABC%'的情况,能否使用索引,怎样才能使用索引?

答案是:可以使用索引,但是需要改写SQL并创建reverse函数索引。

一、col_name like '%ABC'时的优化方法

Test case:

    Create table t1 as select * from dba_objects;

    Create index idx_t1_objectname1 on t1(object_name);

百分号在后面,可以使用索引:

    select object_name from t1 where object_name like ‘DBA%';

百分号在前面,不能使用索引:

    select object_name from t1 where object_name like '%LIB';

解决方法:

create index idx_t1_objectname2 on t1(reverse(object_name));

select object_name from t1 where reverse(object_name) like reverse('%LIB');

执行计划:

640

二、col_name like '%ABC%'时的优化方法

一般认为这种情况是不能使用索引的,但还是有一些优化方法可以使用。

有三种情况:

1、ABC始终从字符串开始的某个固定位置出现,可以创建函数索引进行优化

2、ABC始终从字符串结尾的某个固定位置出现,可以创建函数组合索引进行优化

3、ABC在字符串中位置不固定,可以通过改写SQL进行优化

情况1、先创建substr函数索引,再使用like ‘ABC%’。

假如ABC从字符串第五位出现:

Test Case:

create index idx_substr_t1_objname on t1 (substr(object_name,5,30));

select object_id,object_type,object_name from t1

where substr(object_name,5,30) like 'TAB%';

情况2、先创建reverse+substr组合函数索引,再使用like reverse‘%ABC’。

假如ABC从字符串倒数第五位出现:

Test Case:

Create index idx_t1_reverse2 on t1(reverse(substr(object_name,1,length(object_name)-4)));

select object_id,object_name,object_type from t1 

where reverse(substr(object_name,1,length(object_name)-4)) like reverse('%TAB_COL');

情况3、这种情况需要like的字段上存在普通索引,主要在SQL的写法上做改进。

原来的SQL是这样写的:

Select object_id,object_type,object_name from t1

where object_name like '%ABC%‘;

改写后的SQL是这样的:

Select object_id ,object_type,object_name from t1 

Where object_name in

(select object_name from t1 where object_name like ‘%ABC%’);

Test Case:

create index idx_t1_object_name on t1 (object_name);

Select object_id,object_type,object_name from t1

where object_name like '%TABCOL%';

此时SQL的执行计划是t1 表做全表扫描。

Select object_id,object_type,object_name from t1

Where object_name in

(select object_name from t1 where object_name like '%TABCOL%');

改写后的SQL执行计划是索引全扫描加索引回表操作:

6401

优化原理:

      用索引全扫描取代表的全扫描。因为索引全扫描的代价是全表扫描的1/N (即索引块数与数据块数的比例),表越大,优化效果越明显。

改写后SQL的执行计划,根据索引再回表的代价要看符合条件的记录数多少:如果in子查询返回的记录数很少,那么优化的效果就相当于效率提高了N倍;如果in子查询返回的记录数较多,两种SQL的性能区别就不是很明显了。


最后再提一个:

最常见的写法:where  a like '%xx%' or b like '%xx%' or c like '%xx%';  这种写法查询效率低,经过调查,下面的方法可以替代,并且效率高:

1、如果like的关键字相同:

where instr(nvl(a, '')||nvl(b,'')||nvl(c,''),   'xx') > 0

把要模糊查询的字段先拼接起来,拼接时需要把null转成‘’,否则只要有一个字段值是空,整个拼接的字符串都成空了, 然后用instr 函数去过滤;

2、如果like的关键字不同:

where instr(a, 'xx') > 0  or  instr(b, 'yy') > 0  or instr(c, 'zz') > 0 

经过测试,这两种方法都比like效率要高;

点赞(0)

上一个文章:Hello 深圳

下一个文章:SQL查询优化记录

点了个评