Mysql Like查询优化_mysql like 优化

mysql使用模糊查询时，如果数据量比较大，就会响应很长时间，严重影响效率。

一般的模糊查询：SELECT `column` FROM `table` WHERE `field` like '%keyword%';

即使对相应的字段建立索引也无济于事(用EXPLAN执行，根本没有触发索引，而是全表搜索)。

在网上查了一下，找到如下几种方法：

一、单个匹配符SELECT `column` FROM `table` WHERE `field` like 'keyword%';

这样写的好处是可以触发索引，提高查询效率，但只适用部分场景。

二、各种函数

1、LOCATE()函数SELECT `column` FROM `table` WHERE LOCATE('keyword', `field`)>0

2、POSTITON()函数SELECT `column` FROM `table` WHERE POSITION('keyword' IN `filed`)

3、INSTR()函数SELECT `column` FROM `table` WHERE INSTR(`field`, 'keyword' )>0

select *, INSTR(cf_ent_name, '单') from ytj_ruku_copy1 where INSTR(cf_ent_name, '单');
select *, LOCATE('2', cf_ent_name) from ytj_ruku_copy1 where LOCATE('2', cf_ent_name) > 1;
select *, POSITION('2' in cf_ent_name) from ytj_ruku_copy1 where POSITION('2' in cf_ent_name);
select * from ytj_ruku_copy1 where FIND_IN_SET('单', cf_ent_name);

实际测试，这些函数并不能提高多少效率。?

最后，根据我的具体需求，想到了一种方法(并不适用所有场景，但能解决我遇到的问题)

先交代一下需求：

根据字段`PRODUCT`(产品)、`TYPE`(类型)和`IDX`(流水号)查询`ID`(识别码)。

原本的SQL：SELECT * FROM detail WHERE `IDX` LIKE '%TEST001%' AND PRODUCT = 'Pad' AND TYPE= 'AAA'

这样便会遇到上文说到的情况，会全表查询，效率特别慢。

后来发现，在使用like时，在'%'前加一个字符串，便会触发索引，类似于：SELECT * FROM detail WHERE `IDX` LIKE 'T%EST001%'

这样查询效率会快很多。

因此，我在表中添加了一个字段`SEARCH_ID`,这个字段是由字段`PRODUCT`、`TYPE`的首字符和`IDX`组成的,例如

PRODUCT :Pad

TYPE:AAA

IDX:TEST001 =>  SEARCH_ID : P_A_TEST001。

优化后的代码如下：SELECT * FROM detail WHERE `IDX` LIKE 'P_A_%TEST001%'

这样写，会使用索引，提高查询效率。

其实不难发现，这样做就相当于把表中的数据，根据字段`PRODUCT`、`TYPE`做了分类，减少了查询数量。

这也算是一种用空间换时间的办法。

最后附上更新表的代码：UPDATE detail SET `SEARCH_ID` = CONCAT(SUBSTRING(PRODUCT ,1,1),'_',SUBSTRING(TYPE,1,1),'_',IDX)