MySQL模糊查询like优化方案

索引失效的解决方案

　　在MySQL中，模糊查询肯定要使用LIKE关键字，然后再加 %，是代表前模糊还是后模糊。数据量小的情况下，不容易看出查询的效率，但是数据量达到百万级，千万级甚至更高的时候，查询的效率就很容易显现出来了。

项	索引是否失效	解决方法
col_name LIKE 'ABC%'	索引有效
col_name LIKE '%ABC'	索引失效，使用全表扫描	翻转函数+like前模糊查询+建立翻转函数索引=走翻转函数索引
col_name LIKE '%ABC%'	索引失效，使用全表扫描	locate, position, instr, find_in_set等方法

col_name like '%ABC'

本语句不走索引：

SELECT object_name from t1 WHERE object_name LIKE '%ABC';

解决方法：

CREATE INDEX idx_t1_objectname2 ON t1(reverse(object_name));
SELECT object_name FROM t1 WHERE REVERSE(object_name) LIKE REVERSE('%ABC');

col_name like '%ABC%'

有三种情况：

1.ABC在字符串中位置不固定，可以通过改写SQL进行优化。（速度稍快于LIKE，但都不走索引）

函数	作用	示例/说明
LOCATE(substr,str)	返回 substr 在 str 中第一次出现的位置，如果 substr 在 str 中不存在，返回值为 0 。 如果str是字段，则查找字段内容，如果str不是字段，则将str作为要查的库。	SELECT LOCATE('xbar',`foobar`); #返回0 #若`foobar`是字段，则去此字段查询，而不是”foobar”字符串 SELECT LOCATE('bar',`foobarbar`); #返回4 SELECT `column` FROM `table` WHERE LOCATE('keyword', `field`)>0
LOCATE(substr,str, pos)	返回 substr 在 str 第pos个位置后第一次出现的位置，如果 substr 在 str 中不存在，返回值为0。	SELECT LOCATE('bar',`foobarbar`,5); #返回7
POSITION('substr' IN `field`)	功能跟locate一样	SELECT `col` FROM `tb1` WHERE POSITION('keyword' IN `filed`) SELECT `column` FROM `table` WHERE POSITION('keyword' IN `filed`)
FIND_IN_SET(str,strlist)	查询字段(strlist)中包含(str)的结果，返回记录（没有则返回null） str 要查询的字符串 strlist 字段名 参数以”,”分隔 如 (1,2,6,8,10,22)	SELECT FIND_IN_SET('b', 'a,b,c,d'); #返回2 SELECT name,role FROM `user` WHERE find_in_set('2',role) find_in_set()和like的区别： 主要的区别就是like是广泛的模糊查询，而 find_in_set() 是精确匹配，并且字段值之间用‘,'分开。 SELECT userid,username,userrole 角色 FROM `user` WHERE userrole LIKE '%2%'; SELECT userid,username,userrole 角色 FROM `user` WHERE find_in_set('2',userrole)

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

先创建substr函数索引，再使用like ‘ABC%’。

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

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%';

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

在执行的时候，执行计划显示，消耗值，io值，cpu值均非常大，原因是like后面前模糊查询导致索引失效，进行全表扫描。

解决方法：这种只有前模糊的sql可以改造如下写法

SELECT COUNT(c.c_ply_no) AS COUNT
FROM Policy_Data_All c, Item_Data_All i
WHERE c.c_ply_no = i.c_ply_no
AND REVERSE(i.C_LCN_NO) LIKE REVERSE('%245')

优化原理

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

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

如果in子查询返回的记录数较多，两种SQL的性能区别就不是很明显了。