MySQL模糊查询再也用不着like+%了！

哎我跟你说…刚才在工位那边趴着想事情，旁边小李在那儿敲键盘啪啪响，我脑子突然冒出来一个事儿，就是那个…MySQL 模糊查询啊，我们不是老 love 用那个like '%xxx%'嘛，对吧？结果昨天晚上十一点多我在公司楼下抽烟的时候被人问懵了，他说“东哥有不用 like 的办法吗？性能还更好？”我当场一口烟差点呛死。

我说你等下…这个事儿我之前踩过坑的，就是那个—怎么讲呢—like 加前缀百分号基本废索引嘛，这大家都知道的，你一写%abc就是全表扫，SQL 优化器都帮不了你，尤其我们那种百万级数据量的库，一查就卡半天。

不过我前天改一个老项目的时候发现个挺香的玩法，不用 like，也不用%，性能嗖地一下上去了，我给你们说说。

呃我想想啊…我当时的场景是这么个情况：有个字段name，我们业务想干的就是搜索包含关键字的用户名字。过去写法就是：

select * from user where name like '%doge%';

这个东西在测试库跑挺爽，到了线上直接拖垮实例，业务那边还以为我们 Java 代码哪里又写循环了。

那天排查的时候，我顺手把 SQL 扔到 explain 一看，全表扫描 10 万行，直接秒懂。

结果我想到之前同事安利过一个叫全文索引（FULLTEXT）的东西，平时没空玩，我就试了下，没想到卧槽有点用。

不过你们别急，我继续说，当时我在工位上敲着敲着，那个接口叫什么来着…哦对对是 MySQL 8 之后全文索引支持中文了嘛，我就直接加了个索引：

ALTER TABLE user ADD FULLTEXT(name);

然后搜关键字就不写 like 了，换成：

select * from user where match(name) against('doge' IN NATURAL LANGUAGE MODE);

我跟你讲，速度一下子上来了，从 800ms 掉到十几毫秒，我当时心态直接舒坦得不行，小李还在旁边说“你怎么突然笑了像个傻子”。

后来我们那个产品经理过来催需求，我顺嘴跟他说“以后别想着让我再写 like 百分号了，这玩意性能杀手”。然后我继续改代码的时候又被另一个点吸引了，就是那个…咋说呢，全文索引的匹配方式其实比 like 强多了，比如你可以用 boolean 模式：

select * from user

where match(name) against('+doge -cat' IN BOOLEAN MODE);

这种写法意思就是必须包含 doge，不能有 cat，你像用 like 根本搞不了这种组合。

哦对了我那会儿还顺便写了个小 Demo 给测试那边确认下，他们当时问我：“东哥那中文能不能搜啊？比如搜‘程序员’？”

我测了下，MySQL 8 之后已经 OK 了，只不过分词不那么精细，你们要更准一点就得上 ngram 分词的全文索引：

ALTER TABLE user

ADD FULLTEXT INDEX ft_name(name)

WITH PARSER ngram;

这个效果好太多了，中文词语切得比较像样，之前用 like%程序员%一搜慢得像石头。

哎我现在想想那个晚上真是累得够呛，我在地铁上还在想有没有别招，比如我们有些项目其实用了 Elasticsearch，那就是另外一套世界了，比全文索引还强。但是吧，一般业务不愿意多搞个 ES，嫌麻烦，就全文索引这个办法，迁成本小，速度提升大，不香嘛。

哦靠我又跑题了，对了有人问我：全文索引是不是也会扫全表？不是哈，它会走全文索引，你 explain 一下能看到type = fulltext，比 table scan 强太多了。我那天为了确认，还写了段 Java 做压测大概这样：

String sql = "select * from user where match(name) against(? in natural language mode)";

PreparedStatement ps = conn.prepareStatement(sql);

ps.setString(1, keyword);

ResultSet rs = ps.executeQuery();

跑了几万次都挺稳。

哎我手机响了等一下……好了继续说，总结个屁，总之你们要记住一句话：

能不用like '%xxx%'就别用，用了就等着全表扫描拖库。

要模糊搜索？三种选择：

业务轻 MySQL FULLTEXT（推荐）

数据多且结构复杂 ES

真没办法 最起码写成xxx%还能走前缀索引

我想继续说点别的，不过我先去拿杯水喉咙都快冒烟了，回头再聊…