LIKE ‘%keyword%’ 导致全表扫描的全文索引 / ngram 替代方案_网络技术

MySQL中LIKE '%keyword%'必然全表扫描，因B+树索引无法支持左模糊匹配；全文索引对中文效果差，需ngram插件或生成列+函数索引优化。

MySQL 中 LIKE '%keyword%' 为什么必然走全表扫描

因为 LIKE 左侧带通配符（%）时，B+ 树索引无法利用最左前缀匹配规则——索引是按字典序存储的，而 %keyword% 要求从任意位置开始匹配，数据库只能逐行读取并判断，自然触发全表扫描。

即使字段上有普通 B+ 树索引，WHERE content LIKE '%error%' 也完全用不上。唯一例外是 LIKE 'prefix%' 这种右模糊，才可能走索引。

复合索引更没用：比如 (user_id, content)，content 不在最左且带左模糊，整个索引失效
函数索引（如 LOWER(content)）同样不解决左模糊问题
CHAR 和 VARCHAR 行为一致，类型不影响该限制

全文索引（FULLTEXT）在中文场景下的硬伤

MySQL 原生 FULLTEXT 索引依赖空格或标点分词，对连续中文文本基本无效——它会把整段文字当做一个“词”，导致 MATCH(content) AGAINST('日志' IN NATURAL LANGUAGE MODE) 查不到任何结果。

除非你提前用程序把中文切好词、用空格拼接再入库（比如存成 "错误日志服务启动"），否则 FULLTEXT 对中文就是摆设。

5.7+ 支持 ngram 插件，但需显式启用且只支持 IN NATURAL LANGUAGE MODE 或 IN BOOLEAN MODE，不支持 AGAINST(... WITH QUERY EXPANSION)
ngram_token_size 默认为 2，意味着“错误日志”会被拆成“错”“错误”“误”“误日”“日”“日志”“志”，粒度粗、噪音大
停用词表对中文影响极大：默认停用词包含“的”“了”“在”等高频字，一旦命中就直接忽略整词匹配

启用 ngram 并正确建全文索引的实操步骤

不是加个插件就能用，必须严格按顺序操作，漏一步都会让查询返回空结果。

先确认插件已加载：SHOW PLUGINS; 查看 ngram 是否为 ACTIVE；若没有，需在配置文件中添加 ft_parser=ngram 并重启 MySQL。

建表时指定 parser：CREATE TABLE logs (id INT, content TEXT, FULLTEXT(content) WITH PARSER ngram);
已有表需重建索引：ALTER TABLE logs DROP INDEX ft_content, ADD FULLTEXT(content) WITH PARSER ngram;（不能只 ADD FULLTEXT）
查询必须用 AGAINST，且关键词长度 ≥ ngram_token_size（默认 2 字）；查单字如 '错' 会失败
布尔模式下支持 +/-，但 + 必须紧贴关键词：AGAINST('+错误 +日志' IN BOOLEAN MODE)

比 ngram 更可控的替代方案：生成列 + 函数索引（MySQL 5.7+）

如果关键词固定、长度有限（比如只查 2–4 字的错误码或状态），用生成列预计算所有子串，再建哈希索引，性能和精度都远超 ngram。

例如提取所有长度为 3 的子串：

ALTER TABLE logs
ADD COLUMN content_3grams VARCHAR(1000) 
  GENERATED ALWAYS AS (
    CONCAT(
      IF(CHAR_LENGTH(content) >= 3, SUBSTR(content, 1, 3), ''),
      ' ',
      IF(CHAR_LENGTH(content) >= 3, SUBSTR(content, 2, 3), ''),
      ' ',
      IF(CHAR_LENGTH(content) >= 3, SUBSTR(content, 3, 3), ''),
      -- 继续扩展至你需要的最大偏移...
      ' '
    )
  ) STORED;

然后建索引：CREATE INDEX idx_3grams ON logs (content_3grams);，查时用 WHERE content_3grams LIKE '%err%' ——这时是右模糊，能走索引。

空间换时间：每多一个子串长度，存储就翻倍；建议只覆盖业务真正需要的长度（如 HTTP 状态码只做 3 字，错误码只做 4 字）
更新代价高：每次 UPDATE content 都要重算生成列，不适合高频写入场景
无法支持跨字匹配：比如“服务异常”里查“异常”，若生成列只截 2 字，就漏掉“服务”“务异”“异常”，得靠组合多个长度覆盖

ngram 的 token 边界是按字节切的，遇到 UTF-8 多字节字符（比如 emoji 或某些生僻汉字）容易截断出乱码子串；生成列方案虽然麻烦，但逻辑完全可控，上线前务必用真实语料跑一遍子串覆盖率验证。

# mysql # word # 字节 # ai # 配置文件 # 状态码 # 为什么 # red # Boolean # Error # Token # char # int # table # 数据库 # http # 关键词 # 多字 # 只做 # 能走 # 都要 # 多个 # 已有 # 错误码 # 上有 # 一遍

相关栏目：【网站优化151355 】【网络推广146373 】【网络技术251813 】【 AI营销90571 】