mysql中如何优化LIKE语句的索引使用

发布日期： 2026-01-17 阅读量： 作者： P粉602998670

LIKE以通配符开头时索引完全失效，因B+树无法利用有序性定位，仅'abc%'和'ab_c'可走索引；可用前缀索引、覆盖索引或FULLTEXT索引优化，并避免隐式类型转换。

当 WHERE name LIKE '%abc' 或 WHERE name LIKE '%abc%' 这类查询出现时，MySQL 无法使用 B+ 树索引的有序性，会直接走全表扫描。这是因为索引是按字典序存储的，而前导通配符让数据库无法确定从哪个叶子节点开始查找。

如果业务必须支持模糊搜索但又不能全表扫，可以结合字段特性做针对性优化。例如用户昵称搜索，通常只关心前 20 个字符：

ALTER TABLE users ADD INDEX idx_nickname_prefix (nickname(20));

这样 WHERE nickname LIKE '张%' 就能命中该前缀索引，节省空间且提升查询效率。但要注意：

前缀长度不能太短（否则区分度低，大量回表），也不能太长（浪费索引空间、降低写入性能）
用 SELECT COUNT(DISTINCT LEFT(nickname, 20)) / COUNT(*) 估算区分度，理想值 > 0.9
覆盖索引对 LIKE 'xxx%' 有效，但若查询中包含未建索引的字段（如 SELECT id, email FROM users WHERE nickname LIKE '张%'），仍需回表

对描述类、内容类字段（如文章标题、商品详情），LIKE '%keyword%' 几乎必然慢，应改用 FULLTEXT 索引 + MATCH ... AGAINST：

ALTER TABLE articles ADD FULLTEXT(title, content);
SELECT * FROM articles WHERE MATCH(title, content) AGAINST('数据库优化' IN NATURAL LANGUAGE MODE);

注意点：

一个常被忽略的坑：LIKE 字段类型与参数类型不一致时，MySQL 可能自动转换，导致索引失效。例如：

phone VARCHAR(20) 有索引，但写成 WHERE phone LIKE 138%（数字字面量），MySQL 会把 phone 转成数字比较，索引失效
正确写法是 WHERE phone LIKE '138%'，确保两边都是字符串
同理，COLLATE 不一致（如字段是 utf8mb4_0900_as_cs，参数没指定）也可能触发隐式转换

执行前务必用 EXP

LAIN 看一眼 key 和 rows，哪怕语句看起来“应该能走索引”。很多慢查就卡在这类细节上。

随便看看