LIKE '%abc'一定不走索引,因B+树仅支持左前缀匹配,无法定位扫描起点,只能全表扫描;FORCE INDEX无效,是索引结构硬限制。

因为 B+ 树索引无法从任意后缀反向定位扫描起点,这不是配置问题,而是数据结构硬限制。
LIKE '%abc' 为什么一定走不了索引
B+ 树叶子节点按字段值字典序严格排序,查找必须能“跳到第一个可能命中项”。LIKE 'abc%'可以快速定位到第一个 'abc' 开头的记录,然后向右顺序遍历;但 LIKE '%abc' 要求“结尾为 abc”,而索引里没有按结尾排序的结构,所有匹配项散落在不同分支,优化器只能选择全表扫描。
验证方式极简单:EXPLAIN SELECT * FROM users WHERE name LIKE '%明';,只要看到 key 为 NULL、type 为 ALL,就确认索引完全没用上。
- 复合索引(如
(status, name))救不了name LIKE '%abc'——该字段在索引中完全不可用 -
REGEXP 'abc$'同样不走索引,正则和LIKE一样,都不支持后缀索引 - 隐式转换会雪上加霜:比如
name是utf8mb4_0900_as_cs,但查询字面量没声明 collation,MySQL 可能放弃索引甚至返回空结果
哪些 LIKE 形式真能触发索引
能否走索引,只看通配符是否出现在最左侧不可控位置:
-
LIKE '张%'✅type=range,前缀确定,可定位起点 -
LIKE '张_明'✅ 下划线只占一位,不影响前缀连续性 -
LIKE '%张%'❌ 两端模糊,无起点可言 -
LIKE '张%明'⚠️ 仅'张%'部分参与索引过滤,“%明”不加速
注意:LIKE 右边必须是字面量或参数化变量(如 ? / @kw),不能是表达式(如 CONCAT('%', @kw)),否则 MySQL 无法在优化阶段判定模式是否可索引。
安装、修复、更新或打包一个基于 Python 和 PyMySQL 实现的 Windows `mysql` 命令。适用于用户希望在 PowerShell/CMD 中输入 `mysql` 时重定向到 Python 实现、需要交互式输入 MySQL 连接信息、需要同步到当前用户本地 bin 目录,或需要将该能力封装成可公开复用的 Codex skill。
为什么加了索引、写了 'abc%',EXPLAIN 却显示 Using where
这往往意味着 MySQL 把 LIKE 'abc%' 当成了纯字符串匹配,而非可下推的范围条件。常见原因包括:
- 字段定义为
CHAR(20),而值存的是'abc'(实际占位'abc '),LIKE 'abc%'在 collation 比较时无法与索引项对齐,优化器放弃 ICP - 查询字面量未声明 collation,例如字段是
utf8mb4_0900_as_cs,但写的是WHERE name LIKE 'Abc%',大小写不一致导致无法做前缀剪枝 - 联合索引为
(status, name),但查询只写了WHERE name LIKE 'abc%'——违反最左前缀原则 -
optimizer_switch中index_condition_pushdown被设为off,或者索引不是 BTREE 类型
关键判断依据是 EXPLAIN 的 Extra 字段:Using index condition 才表示 ICP 生效;Using where 说明条件是在 Server 层过滤的。
真正绕不过去时,有哪些替代方案
当业务强依赖后缀或中间匹配,又不能接受全表扫描时,得换思路:
- 全文索引(
FULLTEXT)适合自然语言搜索,但不支持精确后缀匹配 - 反转存储:新增一个
name_reversed字段存倒序字符串,建普通索引,查name_reversed LIKE 'cba%'等价于原字段后缀匹配 - 生成列 + 索引:MySQL 5.7+ 支持
GENERATED COLUMN,比如suffix3 VARCHAR(3) AS (SUBSTR(name, -3)) STORED,再对它建索引 - 外部搜索引擎(Elasticsearch / Meilisearch):适合高并发、复杂模糊场景,但引入运维复杂度
最容易被忽略的一点:即使你改成了 LIKE 'abc%',如果字段平均长度只有 8,却定义为 VARCHAR(255),建前缀索引 INDEX (name(10)) 才是更优解——体积小、写入快、缓存友好。










