如何优化MySQL中的LIKE模糊查询_通过前缀索引或全文索引提升匹配速度-mysql教程-PHP中文网

like 'abc%' 能用索引是因为b+树支持前缀匹配，而'%abc'不能因索引无反向排序；需建反向字段加索引或改用全文索引等方案。

如何优化mysql中的like模糊查询_通过前缀索引或全文索引提升匹配速度

LIKE 查询慢，不是因为写法错，而是没对齐索引能力。前缀匹配（LIKE 'abc%'）能走 B+ 树索引，后缀或全模糊（LIKE '%abc'、LIKE '%abc%'）默认不走，得换路子。

为什么 `LIKE 'xxx%'` 能用索引，而 `LIKE '%xxx'` 不能

MySQL 的 B+ 树索引按字典序存储字符串，从左到右逐字符比对。LIKE 'john%' 可以定位到 'john' 开头的索引块范围，直接跳过无关分支；但 LIKE '%john' 要找所有以 'john' 结尾的值，索引里没有“反向排序”，无法快速定位——除非你显式建反向索引。

验证方式：执行 EXPLAIN SELECT * FROM users WHERE name LIKE 'john%';，看 type 是否为 range，key 是否显示索引名
注意：即使写了 LIKE 'john%'，如果列上没索引，照样全表扫描
字符集影响很大：若列是 utf8mb4_unicode_ci，索引效率可能低于 utf8mb4_bin，尤其含大量重音/大小写变体时

前缀索引不是万能解，慎用 `INDEX(column(n))`

当字段很长（如 varchar(500)），直接建完整列索引开销大、内存占用高，于是有人建前缀索引：CREATE INDEX idx_title ON articles(title(20));。但这只对前 20 字符有效，LIKE 'abcdef...%' 若超过 20 位，索引就失效了。

MySQL

编写正确的MySQL查询，避免字符集、索引和锁方面的常见陷阱。

下载

前缀长度必须覆盖绝大多数查询模式的前缀长度，可用 SELECT COUNT(DISTINCT LEFT(title, 20)) / COUNT(*) FROM articles; 算选择性，> 0.9 才算合格
前缀索引无法用于 ORDER BY 或 GROUP BY，除非语句只涉及前缀部分
LIKE 'abc%' 能用前缀索引，但 LIKE '%abc%' 依然不能——前缀索引不改变“开头不可知”这个根本限制

全文索引（`FULLTEXT`）适合自然语言搜索，不是所有模糊场景都适用

MATCH(col) AGAINST('word') 是语义级匹配，会自动忽略停用词、做词干还原（如 “running” → “run”），且要求最小词长（默认 4），'a'、'to'、'in' 这类词查不到。

仅支持 MyISAM 和 InnoDB 表，但 InnoDB 的全文索引对事务有额外开销，高并发写入时需评估
不支持通配符前缀： AGAINST('joh*') 只在布尔模式下生效（AGAINST('+joh*' IN BOOLEAN MODE)），且 * 必须在末尾
如果业务要精确匹配子串（比如查日志里的 'ERROR: timeout'），全文索引反而漏结果，此时 LOCATE() 或 INSTR() 更可靠

后缀匹配（`LIKE '%abc'`）的低成本补救方案

真要查结尾，又不想改表结构或引入 ES，可以加一个反向字段 + 普通索引：

新增列 name_reversed VARCHAR(100)，写入时同步存 REVERSE(name)
建索引：CREATE INDEX idx_name_rev ON users(name_reversed);
查询改写：SELECT * FROM users WHERE name_reversed LIKE REVERSE('abc') + '%';
注意：REVERSE() 函数在 WHERE 中可走索引，但不能和计算列混用（如 WHERE REVERSE(name) LIKE 'cba%' 不走索引，必须预存）

这个方案上线成本低、无外部依赖，但多占一份存储，且每次 INSERT/UPDATE 都要多一次字符串反转操作——对写频次高的表，得权衡。