KEEL · 龙骨 · A CURRICULUM FOR THE AI ERA

03 · 索引失效与反模式全集 — keel 龙骨

这一章回答：索引"建了但没用"时，按什么清单逐条对照排查。

这一章回答：索引"建了但没用"时，按什么清单逐条对照排查。

先修课列了 5 条不该做的事（函数、隐式转换、LIKE '%x'、过多索引、SELECT *）。真实排查里失效原因远不止这些，而且有些失效是间歇性的——同一条 SQL，参数一变就不走索引了。这一章给出可对照排查的完整清单。

一、索引列被"加工"：函数、运算与隐式转换

-- ❌ 索引列套函数
WHERE DATE(created_at) = '2026-01-01'
-- ✅ 改成范围
WHERE created_at >= '2026-01-01' AND created_at < '2026-01-02'

-- ❌ 索引列参与运算
WHERE amount * 100 > 5000
-- ✅ 把运算移到常量侧
WHERE amount > 50

-- ❌ 隐式类型转换：user_id 是 VARCHAR，传了数字
WHERE user_id = 13800138000
-- ✅ 传字符串
WHERE user_id = '13800138000'

隐式转换的方向很重要：

字符串列 vs 数字常量 → 列被转成数字 → 索引失效（上面第三个例子）
数字列 vs 字符串常量 → 常量被转成数字 → 索引仍然可用

判断方法：EXPLAIN 的 ref 列出现 func，或 Extra 出现 Using where 且 rows 很大。更直接的办法是看 warnings：

EXPLAIN EXTENDED SELECT ...; SHOW WARNINGS;   -- 能看到改写后的 SQL

二、字符集与排序规则不匹配

跨表 JOIN 或子查询时，如果关联列字符集/排序规则不同，被驱动表的索引会失效：

-- orders.user_no 是 utf8mb4_general_ci，users.user_no 是 utf8mb4_0900_ai_ci
SELECT * FROM orders o JOIN users u ON o.user_no = u.user_no;
-- 结果：orders 可以走索引，users 走全表（因为比较前要转换）

排查：

SHOW CREATE TABLE orders\G
SHOW CREATE TABLE users\G
-- 对比关联列的 CHARACTER SET 与 COLLATE

这是最难发现的一类，因为单看 SQL 完全正常。EXPLAIN 里的线索是被驱动表 type=ALL 且 rows 是整表行数。修法是统一字符集（改表属于 DDL，走第 04 章的变更流程）。

三、范围条件之后的列全部失效

-- 索引 (a, b, c)
WHERE a = 1 AND b > 10 AND c = 5
-- b 是范围 → c 无法用于定位，只能作为过滤（key_len 停在 b）

这不是"失效"而是索引的固有行为：B+ 树在 b 这一层已经不再有序，无法继续用 c 定位。

应对：

把等值列（c）提到范围列（b）之前，即索引改成 (a, c, b)；
或者接受 c 走索引下推（8.0 默认开启，Extra: Using index condition）。

四、`OR`、`NOT IN`、`!=` 与 `IS NULL`

-- ❌ OR 连接不同列的条件，常常导致放弃索引
WHERE tenant_id = 42 OR status = 'PAID'
-- ✅ 拆成 UNION ALL（各自走自己的索引）
SELECT ... WHERE tenant_id = 42
UNION ALL
SELECT ... WHERE status = 'PAID' AND tenant_id <> 42;

-- != / NOT IN / NOT EXISTS：通常无法有效利用索引（结果集本身就是"绝大多数"）
WHERE status != 'DONE'      -- 如果 95% 都是 DONE，其实还很划算；反之等于全表

-- IS NULL / IS NOT NULL：InnoDB 索引是包含 NULL 的，单看这一点不会失效
WHERE deleted_at IS NULL    -- 可以走索引（前提是它在索引里且是前导等值条件）

⚠️ 不等于类查询的判断标准不是"能不能走索引"，而是"结果集占比"。结果集超过全表 20~30% 时，优化器放弃索引是对的——回表的随机 IO 比顺序全表更贵。这时候不要硬改，应该改需求（加时间范围、加必填过滤条件）。

五、排序与分组导致的失效

-- 索引 (tenant_id, created_at)
WHERE tenant_id = 42 ORDER BY created_at DESC        -- ✅ 索引有序，无 filesort
WHERE tenant_id = 42 ORDER BY amount DESC            -- ❌ 排序列不在索引中
WHERE tenant_id = 42 ORDER BY created_at ASC, id DESC -- ❌ 方向不一致（8.0 支持降序索引可解）
WHERE tenant_id > 42 ORDER BY created_at             -- ❌ 前导列是范围，索引对排序无序

8.0 的解法（方向不一致时）：

CREATE INDEX idx_t_time ON orders (tenant_id, created_at ASC, id DESC);

GROUP BY 同理：如果分组列不构成索引前缀，会出现 Using temporary。判断是否真的需要临时表：

EXPLAIN SELECT tenant_id, COUNT(*) FROM orders GROUP BY tenant_id;
-- Extra 只有 Using index（覆盖索引）→ 不需要临时表
-- Extra 有 Using temporary → 考虑让分组列成为索引前缀

六、`LIKE`：前导通配符与可用形态

WHERE name LIKE '%abc'      -- ❌ 无法用索引定位（不知道开头）
WHERE name LIKE 'abc%'      -- ✅ 可以用索引（等价于范围查询）
WHERE name LIKE '%abc%'     -- ❌ 需要全文索引或搜索引擎

中间匹配的替代方案：全文索引（MATCH ... AGAINST）、生成列 + 索引、或外部检索（ES）。不要指望 B+ 树索引解决子串匹配。

七、间歇性失效：参数变了就不走索引

同一条 SQL，参数 A 走索引、参数 B 全表扫描——这不是 bug，是优化器基于成本的正确选择：

-- tenant_id=42 有 10 行 → 走索引
-- tenant_id=1 有 30 万行（占比 30%）→ 优化器选择全表

诊断：分别对两个参数跑 EXPLAIN；用 optimizer_trace 看 cost_info。

应对优先级：

接受它——如果全表确实更快，这是正确行为；
检查统计信息是否过期（ANALYZE TABLE）；
加直方图帮助优化器识别倾斜；
最后才是 FORCE INDEX。

八、`FORCE INDEX`：能用但要知道代价

SELECT * FROM orders FORCE INDEX (idx_tenant) WHERE ...;

代价：

锁死了优化器的选择权：数据分布变化后，这个强制可能变成负优化，且没人会注意到；
索引被删除或改名会导致 SQL 直接报错；
掩盖了真实问题（通常是统计信息或索引设计问题）。

使用原则：只在"已确认优化器持续误判 + 短期无法改索引 + 有监控"时使用，并写清失效条件与移除时间。把它当创可贴，不当治疗。

九、其它容易忽略的几条

失效情形	说明
表太小	优化器认为全表比走索引便宜（几行的配置表走全表是对的）
`SELECT *` 导致无法覆盖	索引本身没失效，但失去了覆盖索引的收益
索引列参与 `JOIN` 但类型不同	见第二节
统计信息严重过期	见第 01 章
使用了不支持索引的排序规则/表达式	如 `ORDER BY RAND()`（本来就该避免）

动手：可观察结果

产出	判断标准
一份失效场景复现集	至少复现 8 类失效，`EXPLAIN` 前后对比（`key`、`rows`、`Extra`）
隐式转换定位记录	用 `EXPLAIN EXTENDED` + `SHOW WARNINGS` 找出被转换的列
一次字符集不匹配实验	两张不同排序规则的表 JOIN，被驱动表 `type=ALL` 的现象与修法
一次间歇性失效分析	两个参数各自的执行计划 + 结论（接受 / ANALYZE / 直方图 / 改写）
`FORCE INDEX` 决策记录	写明使用理由、失效条件与计划移除时间

完成标志：面对"索引建了没用"的工单，你能按清单在 10 分钟内定位到具体类别，而不是逐个试。

故障注入

注入方式	观察
数字常量传给 VARCHAR 索引列	`ref` 是否变 `func`、`rows` 是否飙升
建两张排序规则不同的表做 JOIN	被驱动表是否 `type=ALL`
对同一 SQL 换大/小结果集参数	执行计划是否切换、`optimizer_trace` 成本差异
删除统计信息后大批量写入再查询	优化器是否做出错误选择，`ANALYZE` 后是否恢复
`WHERE a=1 AND b>10 AND c=5`	`key_len` 是否只到 b
给 `FORCE INDEX` 的索引改名	SQL 是否直接报错（验证强索引的脆弱性）

自测题

字符串列传数字常量与数字列传字符串常量，对索引的影响为什么不同？
索引 (a,b,c) 下 WHERE a=1 AND b>10 AND c=5，哪几列参与了定位？如何改进？
什么情况下"不走索引"反而是对的？给出两个判据。
跨表 JOIN 时字符集不同会导致什么？怎么发现？
FORCE INDEX 的三条代价是什么？什么条件下你才会用它？

进入 keel 阅读