like 'abc%'能走索引，因b+树按字典序存储，该模式等价于name >= 'abc' and name

LIKE 'abc%' 为什么能走索引

因为 MySQL 的 B+ 树索引是按字典序存储的完整字段值，LIKE 'abc%' 等价于一个范围查询：name >= 'abc' AND name 。优化器能直接定位到第一个以 <code>'abc' 开头的索引项，然后向右顺序遍历，跳过大量无关数据。

这不是“模糊匹配被优化了”，而是它根本没做模糊匹配——它只是做了高效的前缀范围扫描。

EXPLAIN 显示 type=ALL 就说明没走索引

常见错误现象：EXPLAIN SELECT * FROM users WHERE username LIKE 'admin%'; 返回 type: ALL、key: NULL，说明索引完全失效。可能原因包括：

• username 字段上没建索引，或建的是 INDEX(username(5)) 但查询用的是 LIKE 'administrator%'（前缀超长）
• WHERE 中用了函数，比如 LOWER(username) LIKE 'admin%' —— 这会让所有索引失效
• 联合索引顺序不对，例如索引是 (status, username)，但查询只用了 username LIKE 'a%'，无法命中
• 表太小（如 20% 总行数），优化器主动放弃索引

前缀索引长度怎么选才不白建

对 VARCHAR(255) 这类长字段，直接 INDEX(username) 不仅浪费空间，还可能因索引页过大降低效率。必须用前缀索引，但长度不能拍脑袋定：

MySQL

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

下载

• 太短（如 username(3)）：区分度低，大量用户名都以 'zha' 开头，索引失去过滤能力
• 太长（如 username(50)）：索引体积膨胀，写入变慢，且超出 InnoDB 单索引键 3072 字节限制时会被截断
• 正确做法：用 SELECT COUNT(DISTINCT LEFT(username, N)) / COUNT(*) FROM users; 测区分度，选第一个结果 > 0.95 的 N

注意：username(10) 是指前 10 个字符，不是 10 字节；utf8mb4 下一个中文占 4 字节，别误算成能存 10 个汉字。

覆盖索引能省掉回表，但得按需加字段

即使 username LIKE 'li%' 走了索引，如果查的是 SELECT id, username, email, created_at，InnoDB 仍要根据主键回表取 email 和 created_at，I/O 成倍增加。

解决方法是建覆盖索引：

• CREATE INDEX idx_username_cover ON users (username, email, created_at);
• 必须把 username 放最左——它是 WHERE 条件的驱动列
• 别无脑堆字段：加 status、is_deleted 等低区分度字段，会让索引变宽、写入变慢，且可能触发单索引长度限制

真正容易被忽略的是：前缀索引本身不能用于 ORDER BY username 或 GROUP BY username，因为截断后无法保证全字段有序。