SQL使用模糊查询like ‘%ABC’ 和 like ‘%ABC%’的优化 

编辑手记:
 今天继续学习SQL优化的技巧
。
感谢刘永甫专家，
本文授权转自“老虎刘谈SQL优化”。

一般情况下，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’);

我们看执行计划：

改写后SQL走了索引。

二、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执行计划是索引全扫描加索引回表操作：

优化原理

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

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

About Me

………………………………………………………………………………………………………………. ● 本文转载自老虎刘谈SQL优化，感谢刘永甫专家的奉献 ● 本文在itpub（http://blog.itpub.net/26736162）、博客园（http://www.cnblogs.com/lhrbest）和个人微信公众号（xiaomaimiaolhr）上有同步更新 ● 本文itpub地址：http://blog.itpub.net/26736162/abstract/1/ ● 本文博客园地址：http://www.cnblogs.com/lhrbest ● 本文pdf版及小麦苗云盘地址：http://blog.itpub.net/26736162/viewspace-1624453/ ● 数据库笔试面试题库及解答：http://blog.itpub.net/26736162/viewspace-2134706/ ● QQ群：230161599     微信群：私聊 ● 联系我请加QQ好友(646634621)，注明添加缘由 ● 于 2017-05-09 09:00 ~ 2017-05-30 22:00 在魔都完成 ● 文章内容来源于小麦苗的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解 ● 版权所有，欢迎分享本文，转载请保留出处 ………………………………………………………………………………………………………………. 拿起手机使用微信客户端扫描下边的左边图片来关注小麦苗的微信公众号：xiaomaimiaolhr，扫描右边的二维码加入小麦苗的QQ群，学习最实用的数据库技术。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26736162/viewspace-2139039/，如需转载，请注明出处，否则将追究法律责任。