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

一般情况下,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的性能区别就不是很明显了。

原文发布于微信公众号 - 数据和云(OraNews)

原文发表时间:2017-05-10

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏Python

索引长度过长 ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

1.发现问题   今天在修改innodb表的某个列的长度时,报如下错误: [html] view plain copy print? alter tab...

6076
来自专栏乐沙弥的世界

MongoDB 部分索引(Partial Indexes)

a、部分索引就是带有过滤条件的索引,即索引只存在与某些文档之上 b、满足过滤条件的文档在查询时,其执行计划将使用该列上的索引,否则不会被使用 c、稀疏索...

970
来自专栏武培轩的专栏

设计模式-单例模式

模式定义 确保一个类最多只有一个实例,并提供一个全局访问点。 单例模式分为饿汉式和懒汉式。 懒汉式单例模式:在类加载时不初始化。 饿汉式单例模式:在类加载时就完...

2964
来自专栏栗霖积跬步之旅

单例模式

定义:   单例模式,是一种常用的软件设计模式。在它的核心结构中只包含一个被称为单例的特殊类。通过单例模式可以保证系统中一个类只有一个实例。即一个类只有一个对象...

1939
来自专栏青青天空树

mysql-存储过程(转载)

转自(http://www.cnblogs.com/exmyth/p/3303470.html)

1252
来自专栏乐沙弥的世界

MySQL数据类型 -- 字符型

版权声明:本文为博主原创文章,欢迎扩散,扩散请务必注明出处。 https://blog.csdn.net/robinson_0612/art...

852
来自专栏Hongten

java开发_mysql中获取数据库表描述_源码下载

我们需要获取表:test_table表的描述信息,然后把描述信息插入到表:data_element_config中记录结果

1852
来自专栏xingoo, 一个梦想做发明家的程序员

Java几种单例模式的实现与利弊

多线程环境下无法保证单例效果,会多次执行 instance=new Singleton(),需要考虑到多线程

2282
来自专栏C#

C#常用的IO操作方法

public class IoHelper { /// <summary> /// 判断文件是否存在 /...

21910
来自专栏转载gongluck的CSDN博客

逆转字符串

逆转字符串——输入一个字符串,将其逆转并输出 #define _CRT_SECURE_NO_WARNINGS #include <iostream> #inc...

2854

扫码关注云+社区