索引建了，为什么又慢了
前几篇老王的团队尝到甜头，逢表就加索引。结果一周后订单页又慢了——EXPLAIN 一看，type=ALL，索引还在，就是没人用它。索引失效不是索引坏了，是写法让它没法用。这篇盘点六类失效写法，全是我们在老王项目里真实踩过的，每条都给补救方案。
违规一：在索引列上做运算
# 失效：给列包了函数
SELECT * FROM orders WHERE DATE(create_time) = CURDATE();
# 重写成范围，索引恢复
SELECT * FROM orders
WHERE create_time >= CURDATE() AND create_time < CURDATE() + INTERVAL 1 DAY;原则一句话：运算只放常量那边，别放列这边。WHERE id + 1 = 9528 改成 WHERE id = 9527。原理：B+ 树按列的原值排序，包了函数或算术后的值在树里没有排序可言，只能全扫。确实要用函数索引时，MySQL 8.0.13+ 支持函数索引（基于表达式生成虚拟列再建索引），但多数场景改写 SQL 更干净。
违规二：隐式类型转换
# phone 是 varchar(20)，常量没带引号：索引失效
SELECT * FROM users WHERE phone = 13800001234;
# 带上引号，类型一致：ref 级
SELECT * FROM users WHERE phone = "13800001234";规则要记全：字符串列和数字比，失效——MySQL 把每行的列值转成数字再比，等价于对列做 CAST，树排序作废；反过来，数字列和字符串常量比，一般不失效（常量被转成数字），但不规范、有歧义风险，照样别写。这坑的隐蔽之处在于：开发环境数据量小，全表扫也看不出来，一上量就爆。
违规三：LIKE 左模糊
# 左模糊：树里没法定位，失效
SELECT * FROM users WHERE nickname LIKE "%陈";
# 右模糊：前缀可定位，正常走索引
SELECT * FROM users WHERE nickname LIKE "陈%";词典按开头排序，结尾匹配没法走树，和最左前缀是同一个道理。搜「包含某词」的真需求，别硬扛 LIKE——上全文索引（FULLTEXT / ngram）或者搜索引擎。左右都模糊又必须用 LIKE 时，可以配覆盖索引：全索引扫比全表扫省 I/O，算不上治好，但止血。
违规四：or 混搭与否定语义
WHERE user_id = 9527 OR amount > 100：or 的两侧只要有一侧没有索引，整条就得全表扫——or 意味着「两批结果取并集」，一批走索引一批全扫，优化器干脆全扫。补救：用 UNION ALL 拆成两条各自走索引的查询，或保证 or 两侧都有索引。NOT IN、!=、<> 这类否定条件通常也用不上索引（它们匹配的是「一段之外」的行），值集合有限时改写成 IN 正向列举。
违规五：隐式字符集与排序规则
老王踩过的最阴的一招：两张表 JOIN，关联列一边 utf8 一边 utf8mb4，或者排序规则（COLLATE）不同——MySQL 会在 JOIN 时对其中一侧做隐式转换，那一侧的索引当场下岗，数据量大的那张表全扫。补救只有根治：全库统一 utf8mb4 和一致的 COLLATE，这篇先立规矩，表设计篇再展开。
违规六：选择性太差，优化器弃用
严格说这不是失效，是优化器主动放弃：当它估算走索引的代价高于全表扫（比如命中行数占比太高，超过两三成），ALL 反而是更便宜的选择。典型如 status 只有三个值、sex 只有两值——这种列别单独建索引，塞进联合索引当辅助过滤。这类「计划与预期不符」的场景，下一篇讲优化器怎么算账时展开。
一张表收编
| 失效写法 | 原因 | 补救 |
|---|---|---|
| 列上函数 / 运算 | 破坏树的有序性 | 运算移到常量侧，或改写为范围 |
| 字符串列 = 数字 | 隐式转换，对列做 CAST | 常量补引号，类型对齐 |
| LIKE "%x" | 前缀不可定位 | 改前缀匹配 / 全文索引 / 覆盖止血 |
| or 侧无索引 | 并集需要两批都可索引 | UNION ALL 拆分 |
| NOT IN / != | 否定匹配无连续区间 | 改写为 IN 正向列举 |
| JOIN 字符集不一致 | 隐式转换一侧列 | 统一 utf8mb4 与 COLLATE |
最后一条军规：所有改写都用 EXPLAIN 验收，别信感觉。修复了所有失效写法之后，老王又遇到新问题：条件完全一样，有时走索引有时全表扫——这不是 Bug，是优化器在算账。下一篇讲它的账本：统计信息、成本估算，以及 force index 什么时候用。
咖啡凉了，记得趁热喝。
评论 (0)