[转]Like模糊查询优化

在使用mysql进行模糊查询的时候，很自容的会用到 like 语句，通常情况下，在数量小的时候，不容易看出查询效率，但在数据量达到百万级，千万级的时候，查询的效率就很容易显现出来。这个时候查询的效率就显得很重要！

模糊查询

一般情况下like模糊查询的写法为（field已建立索引）

SELECT `column` FROM `table` WHERE `field` like `%keyword%`

上面的语句用explain解释来看，SQL语句并未用到索引，而且是全表索引，如果在数据量超大的时候，可想而知最后的效率会是怎么样

对比下面的写法：

SELECT `column` FROM `table` WHERE `field` like 'keyword%'; 

这样的写法用explain解释看到，SQL语句使用了索引，搜索的效率大大的提高了！

但是有的时候，我们在做模糊查询的时候，并非要想查询的关键词都在开头，所以如果不是特别的要求，"keywork%"并不合适所有的模糊查询

一张表大概40万左右的数据，用like模糊查询title字段，很慢，title字段已经建立了索引，mysql 对 someTitle% 这样的模糊查询在有索引的前提下是很快的。
所以下面这两台sql语句差别就很大了
sql1 = "...... title like someTitle%" (话费0.001秒)
sql2 = "...... title like %someTitle%" (话费0.8秒)
这两句的效率相差了800倍，这很可观啊。
所以我有个想法：在不用分词的方法的前提下，把存储的title字段，加一个特别的前缀，比如"im_prefix"，比如一条记录的title="我是标题党"，那么存储的时候就存储为"im_prefix我是标题党"。
这样一来，我们要模糊查找"标题党"这个关键词的时候，就把sql写成这样：
$sql1 = "...... title like im_prefix%标题党%" (花费0.001秒)，前台显示数据的时候，自然把取到的title过滤掉"im_prefix"这个前缀了

考虑用其他的方法

LOCATE（'substr',str,pos）方法

SELECT LOCATE('xbar',`foobar`);   返回0 
SELECT LOCATE('bar',`foobarbar`);  返回4
SELECT LOCATE('bar',`foobarbar`,**5**); 返回7

备注：返回 substr 在 str 中第一次出现的位置，如果 substr 在 str 中不存在，返回值为 0 。如果pos存在，返回 substr 在 str 第pos个位置后第一次出现的位置，如果 substr 在 str 中不存在，返回值为0。

SELECT `column` FROM `table` WHERE LOCATE('keyword', `field`)>0 

备注：keyword是要搜索的内容，field为被匹配的字段，查询出所有存在keyword的数据

POSITION('substr' IN field)方法

position可以看做是locate的别名，功能跟locate一样

SELECT `column` FROM `table` WHERE POSITION('keyword' IN `filed`)

INSTR(str,'substr')方法

SELECT `column` FROM `table` WHERE INSTR(`field`, 'keyword' )>0

还有一个函数FIND_IN_SET

FIND_IN_SET(str1,str2):

返回str2中str1所在的位置索引，其中str2必须以","分割开。

SELECT * FROM `person` WHERE FIND_IN_SET('apply',`name`);

image.png

参考：http://java.isture.com/db/mysql/optimize/like模糊查询优化.html