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

那天晚上我正准备下班，产品小哥突然冲过来跟我说一句： “东哥，那个搜索老是转圈圈，用户要骂娘了你知道吧。”

我心里一紧：坏了，又是我上周随手写的那个where name like '%xxx%'，八成是它干的好事……

我远程一看监控，CPU 正产，哦不对，是“正常”飙高，慢查询日志里一大片：

SELECT id, name, phone

FROM user

WHERE name LIKE '%张三%'

LIMIT 20;

典型“正义的模糊查询”写法，对吧，谁年轻时候没写过两句这种玩意。 问题是，数据一多，它就不讲武德了——全表扫，索引直接废掉。

你们知道 MySQL 的逻辑吧，B+树索引最怕前面那个%，一上来就说“我啥也不知道，你全给我扫一遍”，上百万行那就是地狱级 for 循环。

我当时就跟产品说： “这个…从今晚起，我们要跟like '%xxx%'分手了。”

先说一个很容易被忽略的点哈： 有些场景根本不用中间模糊，真的是“你以为要模糊，其实是前缀”。

比如手机号后四位，那确实没办法，只能中间匹配。 但像“搜索公司前缀”、“订单号开头几位”，大部分都是前缀匹配，用这个就行：

SELECT id, name

FROM user

WHERE name LIKE '张%'   -- 注意没有前面的 %

LIMIT 20;

前缀匹配是能老老实实走索引的，这个性能完全不是一个量级。 所以第一条生存法则：能前缀就别全模糊，你不是每次都要“%女明星%”那种搜法。

但今天不是来讲怎么少用，而是—— 真遇到必须“包含”搜索的时候，怎么优雅甩掉like '%xxx%'。

我自己现在最常用的是全文索引，特别是 MySQL 8 之后，好用太多了。

当时那个问题，我们的表大概是这样：

CREATE TABLE article (

  id BIGINT PRIMARY KEY AUTO_INCREMENT,

  title VARCHAR(200),

  content TEXT,

  create_time DATETIME

) ENGINE=InnoDB;

以前我肯定一拍大腿就是：

SELECT id, title

FROM article

WHERE title LIKE '%MySQL%' OR content LIKE '%MySQL%'

LIMIT 20;

然后被 DBA 扔过来一张慢查询截图给我看

后来我改成全文索引，几句 SQL 搞定：

ALTER TABLE article

  ADD FULLTEXT idx_title_content(title, content)

  WITH PARSER ngram;  -- 中文用 ngram 分词

然后查询变这样：

SELECT id, title,

     MATCH(title, content) AGAINST (? IN BOOLEAN MODE) AS score

FROM article

WHERE MATCH(title, content) AGAINST (? IN BOOLEAN MODE)

ORDER BY score DESC

LIMIT 20;

Java 这边就很简单了，我那会儿用的是最原始的 JDBC，顺手贴个大概的写法：

public List<Article> search(String keyword) {

  String sql = "SELECT id, title " +

               "FROM article " +

               "WHERE MATCH(title, content) AGAINST (? IN BOOLEAN MODE) " +

               "ORDER BY id DESC LIMIT 20";

  try (Connection conn = dataSource.getConnection();

       PreparedStatement ps = conn.prepareStatement(sql)) {

      // 前面加个 + 是必须包含，后面加 * 做前缀匹配，效果比你裸 like 香多了

      ps.setString(1, "+" + keyword + "*");

      try (ResultSet rs = ps.executeQuery()) {

          List<Article> list = new ArrayList<>();

          while (rs.next()) {

              Article a = new Article();

              a.setId(rs.getLong("id"));

              a.setTitle(rs.getString("title"));

              list.add(a);

          }

          return list;

      }

  } catch (SQLException e) {

      throw new RuntimeException("search error", e);

  }

}

你看，这里面没有一个like '%xxx%'，但是“模糊”的效果其实更好： 还能按相关度排个序，不像like只能“谁先插入谁先出”。

有人就会问了： “东哥，全文索引我试了，中午搜中文一堆搜不出来，是不是你骗我？”

这个锅…一半算你，一半算默认配置。 MySQL 有停用词、有最小词长，英文默认小于 3 个字符直接忽略，中文不用 ngram 也基本废掉。

我那次就是忘了加WITH PARSER ngram，然后产品搜“手机”啥都没有，搜“手机壳子”就出来一堆，他以为我搞了什么 AI 猜词功能，气得我想把 parser 配置打印给他看。

再说一个比全文索引更“邪门”的玩法。 有些业务不是长文本，就是一个人名、品牌名这种短字符串，要支持“包含搜索”，但又嫌全文索引太重。

那你可以搞一点“小算发”：

比如我们要搜用户姓名“包含”关键字，其实可以拆成“拼音索引”。

大致表这样：

ALTER TABLE user

  ADD COLUMN name_pinyin VARCHAR(100),

  ADD COLUMN name_pinyin_abbr VARCHAR(50),

  ADD INDEX idx_name_pinyin (name_pinyin),

  ADD INDEX idx_name_pinyin_abbr (name_pinyin_abbr);

然后写个 Java 方法，把“张三丰”转成：zhangsanfeng和zsf，插入的时候顺带填一下字段：

public void saveUser(User user) {

  user.setNamePinyin(toPinyin(user.getName()));         // zhangsanfeng

  user.setNamePinyinAbbr(toPinyinAbbr(user.getName())); // zsf

  userMapper.insert(user);

}

搜索的时候，前端传一个关键字，比如 “zs” 或 “zhang”，你就可以：

SELECT id, name

FROM user

WHERE name_pinyin LIKE CONCAT(?, '%')

 OR name_pinyin_abbr LIKE CONCAT(?, '%')

LIMIT 20;

注意这里是前缀模糊哈，索引能用上，所以从 MySQL 的角度看，我是一个“好孩子”。 从用户的角度看，他输入“zs”，照样能搜到“张三丰”，体验还行。

这其实就是把“中间模糊”的需求，变成了“多个字段的前缀模糊”。 你表结构稍微脏一点，数据库就轻松一点，鱼和熊掌选一个。

还有一种骚操作，是用“反转字符串”撑一撑。 比如你要按后缀匹配邮箱：'%@gmail.com'这种。

你可以在表里加一个反转字段：

ALTER TABLE user

  ADD COLUMN email_rev VARCHAR(200),

  ADD INDEX idx_email_rev (email_rev);

Java 写入的时候顺手反一把：

user.setEmailRev(new StringBuilder(user.getEmail()).reverse().toString());

查询的时候，你要查'%@gmail.com'，其实就是查邮箱反过来以'moc.liamg@'开头：

SELECT id, email

FROM user

WHERE email_rev LIKE CONCAT(?, '%')

LIMIT 20;

这个技巧我第一次跟同事说的时候，他愣了三秒：“你这算不算黑魔法？” 我说：不算，叫“为了让索引好做，我们可以适当出卖一点灵魂”。

当然还有终极方案： 需求说要“拼音模糊 + 同义词 + 智能纠错 + 推荐热搜 + 高亮”… 你就别再为难 MySQL 了，扔给 ES 或者其他专门的搜索引擎才是正道。

数据库负责“存和准”，搜索负责“算和帅”，各司其职。 你硬要用like '%xxx%'把两边的活儿都干完，最后 CPU 嗓子喊哑了，分页还不准，图啥呢。

所以我后来统一跟团队说一句话：

真要用like，前面不能加%； 真要加%，就先想想能不能改成别的方案； 三个%连起来的 SQL，先别提代码评审。

说到这我手机又响了，另外一个群里有人 @我说“东哥，我这条 SQL explain 一下怎么是 ALL”…… 算了我先去救火，你们先把手上那几个%收一收，回头别说是我没提醒过你。