mysql如何优化模糊查询 %关键字持续创作，加速成长！这是我参与「掘金日新计划 · 10 月更文挑战」的第24天，点击

持续创作，加速成长！这是我参与「掘金日新计划 · 10 月更文挑战」的第24天，点击查看活动详情

经常在工作的时候会遇得到索引使用不当或者是模糊查询时间比较慢的问题。在使用msyql进行模糊查询的时候，很自然的会用到like语句，通常情况下，在数据量小的时候，不容易看出查询的效率，但在数据量达到百万级，千万级的时候，查询的效率就很容易显现出来。

一、模糊查询like一般语法

一般情况下模糊查询的语法为：

SELECT `column` FROM `table` WHERE `field` like '%keyword%';//其中field已创建索引

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

对比下面的写法：

SELECT `column` FROM `table` WHERE `field` like '%keyword%';//其中field已创建索引

用explain解释看到，SQL语句使用了索引，搜索的效率大大的提高了。但是"keywork%"并不合适所有的模糊查询，不可避免会用到'%keyword'。

二、试试LOCATE方法

那么如何解决这个问题呢？我们可以试试LOCATE。

LOCATE(substr,str)  返回substr在字符串str 的第一个出现的位置

LOCATE(substr,str,pos) 返回子符串substr在字符串str，从pos处开始的第一次出现的位置

SELECT LOCATE('bar',`foobarbar`); 
###返回4

SELECT LOCATE('bar',`foobarbar`,5);
###返回7

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

使用like，还是使用locate position高效的前提条件是查询的字段上面已经建立起了索引。

五、考虑使用全文索引

创建全文索引语法如下：

CREATE FULLTEXT INDEX full_index_name ON table_name(col_name);

使用全文索引

MySQL 数据库支持全文检索的查询，全文索引只能在 InnoDB 或 MyISAM 的表上使用，并且只能用于创建 char,varchar,text 类型的列。其语法如下：

MATCH(col1,col2,...) AGAINST(expr[search_modifier])
search_modifier:
{
    IN NATURAL LANGUAGE MODE
    | IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION
    | IN BOOLEAN MODE
    | WITH QUERY EXPANSION
}

全文搜索使用 MATCH() AGAINST()语法进行，其中，MATCH()采用逗号分隔的列表，命名要搜索的列。AGAINST()接收一个要搜索的字符串，以及一个要执行的搜索类型的可选修饰符。全文检索分为三种类型：自然语言搜索、布尔搜索、查询扩展搜索。

比如查询查询 title,body 列中包含 'MySQL' 关键字的行数量。上述语句还可以这样写：

SELECT
    count(*) AS count 
FROM
    `fts_articles` 
WHERE
    MATCH ( title, body ) AGAINST ( 'MySQL' );

还可以这么写：

SELECT
    count(IF(MATCH ( title, body ) 
    against ( 'MySQL' ), 1, NULL )) AS count 
FROM
    `fts_articles`;

六、删除修改全文索引

1、直接删除全文索引语法如下：

DROP INDEX full_idx_name ON db_name.table_name;

2、使用 alter table 删除全文索引语法如下：

ALTER TABLE db_name.table_name DROP INDEX full_idx_name;

七、考虑创建匹配字段的反转字段

新增一列，存储该字段的反转。比如原字段是abcd，取反存储为dcba，查询%bcd改成查dcb%。

where reverse(字段) like reverse('%关键字')

reverse函数索引将索引数据反转，SQL中的where条件中将值反转，这样就将前%改为后%。

1、创建反转索引

2、反转字段模糊匹配

mysql如何优化模糊查询 %关键字