MySQL灵魂十三问之第一问: 语法:like “%关键字%“(模糊匹配) 如何优化?

---

前言

在工作中,我们经常遇到需要前后模糊匹配查找的功能需求,数据量小的话我们直接可以用like “%关键字%” 的语法就可以。但是当数据量比较大的时候,在用这类的语法效率就非常低下了,因为前模糊匹配查找SQL内置的优化器并不能生成合适的扫描区间,必然要用到全表扫描。那么有没有一种合适的方法来优化我们的这种SQL呢?答案: 肯定是有,只需两个简单步骤看完让你的SQL执行速度提升N倍。

---

一、建立全文索引

1.1 语法如下：

ALTER TABLE 表名 ADD FULLTEXT INDEX 索引名 ( 列名 ) WITH PARSER ngram
例如:

ALTER TABLE t_ms_log ADD FULLTEXT INDEX idx_act_name ( act_name ) WITH PARSER ngram

ngram为一个语法分析器,解决的是汉语分词的问题,有点类似ES的倒排索引。

二、修改查询语句

2.1 原来的语法:

SELECT * FROM t_ms_log WHERE act_name LIKE "%进账%"

2.2 修改后的语法:

 SELECT * FROM t_ms_log WHERE MATCH(act_name) against('进账')

2.3 比较两种语法的执行时间:

 SHOW PROFILES 

1. 原语句耗时:
   

2. 修改后的语句耗时:
   

3. 查看执行计划,这里用到了全文索引fulltext,而之前的执行计划是全表扫描。
   

   总结: 全文索引用在模糊匹配查找上的速度比模糊匹配查找快了N倍。建议大家收藏以备在运维让我们解决慢SQL时的抓耳挠腮。