LIKE ‘abc%’ 与 LIKE ‘%abc%’ 的索引使用差异与全文索引替代

LIKE ‘abc%’ 能走索引，因为B+树索引支持左前缀匹配，优化器可下推范围扫描；而LIKE ‘%abc%’ 无法利用普通B+树索引，需全表扫描，EXPLaiN常显示type=ALL或key=NULL。

LIKE ‘abc%’ 能走索引，但 LIKE ‘%abc%’ 不能

mysql 或 postgresql 中，LIKE 'abc%' 可以利用前缀索引（B+ 树索引）快速定位，因为匹配模式是“从开头固定”，优化器能下推索引范围扫描；而 LIKE '%abc%' 必须逐行扫描字段内容，无法用普通 B+ 树索引加速——索引项是按完整值排序的，不支持中间或结尾模糊匹配。

常见错误现象：EXPLAIN 显示 type=ALL 或 key=NULL，哪怕字段上有索引，也完全没被用上。

• 只有左前缀通配（'abc%'）、全等（'abc'）或带范围的左前缀（'abc%' 配合 WHERE col > 'ab' AND col ）才可能走索引
• LIKE 'abc_%' 同样可用索引，下划线只占一位不影响前缀判断
• 如果字段用了函数（如 UPPER(col) LIKE 'ABC%'），即使模式左对齐，索引也会失效

全文索引不是万能替代，它解决的是语义检索，不是通配符匹配

FULLTEXT 索引（MySQL）或 tsvector + tsquery（PostgreSQL）适合“找包含‘数据库’‘优化’这些词的文档”，但对 LIKE '%abc%' 这类子串匹配无直接帮助——全文索引不建子串倒排，也不保留原始字符位置。

使用场景错配的典型表现：建了 FULLTEXT 索引后仍写 WHERE MATCH(col) AGAINST('abc' IN NATURAL LANGUAGE MODE)，结果查不到 'xabcx'，因为全文索引默认按词切分，且 'abc' 可能被当停用词或长度不足而忽略。

• MySQL 全文索引默认最小词长为 4（ft_min_word_len=4），'abc' 直接被丢弃
• PostgreSQL 的 to_tsvector() 默认用 english 配置，会词干化、去停用词，'running' → 'run'，但不会保留 'un' 或 'ning' 子串
• 若硬要拿全文索引做子串查，得用 pg_trgm（PostgreSQL）或 ngram 插件（MySQL 8.0+），它们建的是三元组/二元组倒排，和标准全文索引不是一回事

真正替代 LIKE ‘%abc%’ 的实用方案

子串搜索没有银弹，选方案得看数据量、更新频率和精度要求。小表可忍着 LIKE '%abc%' 扫描；大表必须换思路。

• 用 pg_trgm（PostgreSQL）：启用扩展后在字段上建 gin 索引，col % 'abc' 或 col LIKE '%abc%' 就能走索引，响应快但索引体积大、写入略慢
• MySQL 8.0+ 可用 ngram 全文解析器：建 FULLTEXT 索引时指定 WITH PARSER ngram，然后用 MATCH(col) AGAINST('abc' IN NATURAL LANGUAGE MODE) 查 n-gram 子串，但只支持等长切分（如 n=2，则 ‘abc’ → ‘ab’,’bc’）
• 应用层预计算：对需高频子串搜索的字段，额外维护一个关联表，存所有长度 ≥3 的连续子串 + 原记录 ID，用普通索引加速——适合变更不频繁的场景

容易被忽略的细节：大小写、字符集与正则代价

即便加了 pg_trgm 或 ngram，WHERE col LIKE '%Abc%' 仍可能不走索引，因为索引是按字段实际值构建的，而 LIKE 默认大小写敏感（取决于 collation）。更隐蔽的问题是字符集：UTF8MB4 下 emoji 或某些生僻字会让 ngram 切分错位。

• PostgreSQL 的 pg_trgm 支持 ILIKE，但索引本身不自动适配大小写，需确保字段用 text_pattern_ops 或统一转小写建表达式索引
• MySQL ngram 解析器对多字节字符按字节切分，不是按 Unicode 字符，'好abc'（UTF8MB4）可能被切成 '好a'、'abc' 等非预期组合
• 别轻易换成 regexp：MySQL 的 REGEXP 和 PostgreSQL 的 ~ 都无法利用任何索引，性能通常比 LIKE '%abc%' 更差

索引能不能用，不取决于你写了什么操作符，而取决于优化器能否把查询条件映射到索引结构的有序性上。子串搜索天然破坏有序性，所以所有“能加速”的方案，本质都是用空间换时间，或是换一种索引结构来重建局部有序。