为什么不能只用 LIKE '%xxx%'（先讲痛点）

你可能试过 select * from t where col like '%foo%';，小表上还行，但数据量一大就炸——因为这个模式无法走索引（前置通配符），MySQL 会全表扫描。即使把模糊词拆成多个 col like '%a%' or col like '%b%'，还是扫。结论：LIKE 只能在数据量很小或扫描可接受时作为补充方案，不适合作为主方案。

那我们需要两步走：先靠能走索引的方式把候选控制在几百到几千条，然后在客户端/应用层用更精准的相似度算法（如编辑距离）排序并返回前 N 条。

方案一：MySQL FULLTEXT + ngram（适合中文/无空格语言或近似词模糊）

思路很直观：把列建立 FULLTEXT 索引，使用 ngram 分词器（MySQL 自带的 ngram parser）来生成固定长度的 token（常用 2-3），这样对短语中间匹配、部分匹配都能有倒排索引支持。

建表与索引示例：

-- 需要 MySQL 支持 ngram parser（MySQL 5.7/8.0 常见）

CREATE TABLE article (

  id BIGINT PRIMARY KEY AUTO_INCREMENT,

  title VARCHAR(255),

  content TEXT,

  FULLTEXT KEY ft_content (content) WITH PARSER ngram

) ENGINE=InnoDB;

重要的 MySQL 参数（运维要注意）：

• ngram_token_size：控制 ngram 长度（2 或 3 常用）。如果你主要是中文，3 gram 常见；英文场景下 3 也常用，但对短词会有问题。

• innodb_ft_min_token_size / ft_min_word_len：fulltext 的最小词长度，默认可能是 3 或 4，要和你的 ngram 配合调整。

查询时用 MATCH ... AGAINST：

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

FROM article

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

ORDER BY score DESC

LIMIT 100;

Java 调用示例（jdbc）：

String q = "搜索关键词";

String sql = "SELECT id, title, MATCH(content) AGAINST(? IN BOOLEAN MODE) AS score " +

             "FROM article WHERE MATCH(content) AGAINST(? IN BOOLEAN MODE) " +

             "ORDER BY score DESC LIMIT 200";

try (PreparedStatement ps = conn.prepareStatement(sql)) {

    ps.setString(1, q);

    ps.setString(2, q);

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

        while (rs.next()) {

            // 收集候选，后面在应用层做精排

        }

    }

}

优点：能利用倒排索引，实现中间匹配（比 LIKE '%x%' 好很多），对中文效果尤其好。缺点：对错字纠正（拼写错误）不如编辑距离直接，也受 stopword/min-word-length 影响，配置调优必做。

方案二：生成 n-gram/tri-gram 的“倒排表”（自建轻量索引表）——最大可控性

如果你不能/不想依赖 MySQL 的 FULLTEXT 或需要更精细的控制（比如想做 3-gram + 权重 + 频次），可以把 n-gram 预生成并存成一张辅助表：

思路：把内容分成若干 n-gram（如三元组），每个 ngram 插到 token_index(token, doc_id) 表并对 token 建索引。查询时把用户查询也拆成 ngram，查出包含这些 token 的 doc_id（或计数相同 token 数），把出现次数高的放前面。

建表示例：

CREATE TABLE token_index (

  token VARCHAR(16) NOT NULL,

  doc_id BIGINT NOT NULL,

  PRIMARY KEY (token, doc_id),

  KEY idx_token (token)

) ENGINE=InnoDB;

构建 token 的伪代码（Java）：

String content = "今天天气不错";

List<String> trigrams = makeNgram(content, 3); // 自己实现

// 批量插入 token_index (token, doc_id)

查询流程：

1. 把 query 拆 trigram

2. select doc_id, count(*) as hits from token_index where token in (?,?,?) group by doc_id order by hits desc limit 500;

3. 拿到 doc_id 列表，去主表拉出完整内容，最后在 Java 做 Levenshtein / 余弦等精排

这种方案优点是高度可控，缺点是额外存储和维护（写入/更新时需要维护 token_index）。

方案三：前缀索引 + 多字段拆词（适合短词、autocomplete 场景）

如果你的模糊匹配主要是“以某个词开头”或自动补全（autocomplete），可以把词拆成 token 并对 token 建普通 BTREE 索引或 prefix 索引，然后使用 WHERE token LIKE 'prefix%'，这能走索引。

实现思路：为每条文本预生成“词前缀”并存储到 prefix_index(prefix, doc_id) 表。例如“programming” -> p, pr, pro, prog... 但注意这会爆表（若不节制）。通常只做首 3–8 个前缀。

方案四：把精确度放到应用层（Java）——候选集 + 编辑距离重排

上面任何一种方案都会先返回一个“候选集”（比如 100–2000 条）。真正的“模糊”任务（拼写纠正、打字错误、近似词）通常在应用层用更强的相似度算法来做：

• 编辑距离（Levenshtein）：衡量两个字符串最少编辑步数。

• Jaro–Winkler：对人名等效果好。

• 余弦相似度 / 词向量：适合长文本、语义相似性（需要特征化）。

• n-gram 重叠计数：简单且快（尤其配合 token_index）。

Java 上你可以直接用 Apache Commons Text 的 LevenshteinDistance 或自己实现一个高性能版（记得短路优化、阈值剪枝）。

示例：先从 MySQL 拿候选，再在 Java 里过滤和排序：

// 假设 docList 是 SQL 返回的候选

List<Result> finalList = docList.stream()

    .map(d -> {

        int dist = LevenshteinDistance.getDefaultInstance().apply(query, d.getContent());

        double score = computeScore(dist, d); // 自定义：结合 MATCH score 与 dist

        d.setScore(score);

        return d;

    })

    .filter(d -> d.getScore() > threshold)

    .sorted(Comparator.comparingDouble(Result::getScore).reversed())

    .limit(20)

    .collect(Collectors.toList());

要点：别对完整表做 Levenshtein，先筛后排是关键。

实战小贴士（遇到的坑和调优）

1. 中文处理：MySQL 内置 ngram parser 很适合中文场景；也可以用外部分词到 token_index 再建立索引。注意停止词和最小长度设置（ngram_token_size、ft_min_word_len）。

2. 最小化候选集：目标是把候选集控制在几百到几千条，应用层精排才快。用 FULLTEXT/token_index/前缀索引来做到这一点。

3. 短词问题：短关键词（比如 1–2 个字）会生成很多命中，考虑对短词采取更严格的规则或要求最短长度。

4. 更新成本：如果数据频繁写入，要考虑 token_index 的维护成本（事务写入、批更新）。

5. 停止词/噪音词：调整 stopword 表，避免“的/了/and”等词污染结果。

6. 并发与缓存：对热查询加缓存（Redis）或把高频搜索做预计算。

7. 配置修改后需重建索引：改 ngram_token_size 或 ft_min_word_len 后，必须重建 FULLTEXT 索引。

完整的示例流程（从建表到 Java 查询 + 精排）

1. 用 ngram fulltext 建索引（见上文 SQL）。

2. 查询候选（MATCH ... AGAINST 限制 LIMIT 200）。

3. 在 Java 中计算 Levenshtein（或 JaroWinkler）并重排返回 topN。

关键 Java 代码片段：

// 1. 获取候选

String sql = "SELECT id, title, content, MATCH(content) AGAINST(? IN BOOLEAN MODE) AS score " +

             "FROM article WHERE MATCH(content) AGAINST(? IN BOOLEAN MODE) " +

             "ORDER BY score DESC LIMIT 300";

List<Candidate> cand = new ArrayList<>();

try (PreparedStatement ps = conn.prepareStatement(sql)) {

    ps.setString(1, query);

    ps.setString(2, query);

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

        while (rs.next()) {

            cand.add(new Candidate(rs.getLong("id"),

                                   rs.getString("title"),

                                   rs.getString("content"),

                                   rs.getDouble("score")));

        }

    }

}

// 2. 精排（Levenshtein + 原始 score 结合）

LevenshteinDistance ld = LevenshteinDistance.getDefaultInstance();

List<Candidate> ranked = cand.stream()

    .map(c -> {

        int dist = ld.apply(query, c.getContent());

        // 一个简单的融合函数：越小距离越高分，且保留原 match score

        double sim = Math.max(0, 1.0 - (double)dist / Math.max(query.length(), 1));

        c.setFinalScore(0.6 * c.getScore() + 0.4 * sim);

        return c;

    })

    .sorted(Comparator.comparingDouble(Candidate::getFinalScore).reversed())

    .limit(20)

    .collect(Collectors.toList());

什么时候选哪种方案（简单决策树）

• 目标是“全文模糊检索 + 中文支持” → 优先尝试 MySQL FULLTEXT + ngram。

• 需要强控制与可解释性（比如你想准确统计 ngram 匹配数）→ 用自建 token_index。

• 场景是 autocomplete（前缀匹配）→ 用前缀索引或拆词加 BTREE。

• 需要拼写纠正 / 处理错别字 → 候选 + Java 层 Levenshtein/JaroWinkler 精排。

结语

• 先做 PoC：在小量数据（几十万行）上试 ngram 与 token_index 两种方式，观察召回和延迟。

• 监控查询耗时、IO 和索引大小，写入频率高时评估 token_index 的写入成本。

• 真实场景往往得混用：全文索引做常见查询，token_index 做精确近似或高性能场景；所有结果再在 Java 做最后的打分与去重。

-END-

原文来源：https://mp.weixin.qq.com/s/7Fv9Y7zs4ngsP_4e7XiT6g