SQL使用模糊查询like的优化

SQL使用模糊查询like的优化 SQL使用模糊查询like’%ABC’和like’%ABC%’的优化 &#1…

大家好,又见面了,我是你们的朋友全栈君。

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使用模糊查询like的优化

改写后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执行计划是索引全扫描加索引回表操作:

SQL使用模糊查询like的优化

优化原理

用索引全扫描取代表的全扫描。因为索引全扫描的代价是全表扫描的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群,学习最实用的数据库技术。

SQL使用模糊查询like的优化SQL使用模糊查询like的优化

ico_mailme_02.png DBA笔试面试讲解 欢迎与我联系

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

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。

发布者:全栈程序员-用户IM,转载请注明出处:https://javaforall.cn/143734.html原文链接:https://javaforall.cn

【正版授权,激活自己账号】: Jetbrains全家桶Ide使用,1年售后保障,每天仅需1毛

【官方授权 正版激活】: 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...

(0)


相关推荐

  • vim保存并退出有几种方法_ubuntu vim保存退出命令

    vim保存并退出有几种方法_ubuntu vim保存退出命令vim基础保存退出命令vimxxx.file输入文件内容;ins按键,切换插入和增加按ESC,左下角就可以进行输入:w保存但不退出:wq保存并退出:q退出:q!强制退出,不保存:e!放弃所有修改,从上次保存文件开始再编辑命令历史…

  • Word2vec原理浅析及gensim中word2vec使用

    Word2vec原理浅析及gensim中word2vec使用本文转载于以下博客链接:Word2vec原理浅析:https://blog.csdn.net/u010700066/article/details/83070102;gensim中word2vec使用:https://www.jianshu.com/p/b779f8219f74如有冒犯,还望谅解!Word2vec原理浅析及tensorflow实现Word2Vec是由Google的M…

  • 我的世界怎么设置传送点指令_我的世界手机版领地指令

    我的世界怎么设置传送点指令_我的世界手机版领地指令今天小编为玩家们带来了我的世界服务器领地指令_我的世界地皮指令大全,希望对玩家们有所帮助,还不了解的玩家快来看看吧。圈地指令用木棍(各个服务器不一样,绝大部分默认是木锄)左击一个点,右击一个点(两点内为你想圈的长宽高,对角,一个高点,一个低点。)然后输查询大小,在输入创建领地。查询区域大小/resselectsize创建领地/rescreate名字移除领地/resremove名字领地转赠/resg…

  • Java拖拽排序工具类「建议收藏」

    Java拖拽排序工具类「建议收藏」packagecom.ciih.jwt.util.sort;importjava.lang.reflect.Field;importjava.util.Collections;importjava.util.List;/***拖拽排序工具:此工具将传入的list重新排序后返回,使用者只需要将list重新存入数据库即可完成排序.*<>*拖拽排序必然牵扯到两个元素,被拖拽的元素和被挤压的元素.排序方式就存在两种,一种是两个元素进行交换位置,一种是一个元素拖到.

  • eclipse 导入maven工程 在libraries 没有maven Dependencies「建议收藏」

    eclipse 导入maven工程 在libraries 没有maven Dependencies「建议收藏」导入maven工程的时候所有的依赖包下载不下来,最后而且在工程的buildPath====&gt;javaBuildPath====&gt;libraries中没有mavenDependencies,显示为org.eclipse.ide.MAVEN2_CLASSPATH_CONTAINER解决方法:1..classPath文件缺失&lt;classpathentrykind=…

  • mysql 往表中某个字段的字符串后追加字符串[通俗易懂]

    mysql 往表中某个字段的字符串后追加字符串

发表回复

您的电子邮箱地址不会被公开。

关注全栈程序员社区公众号