索引失效的常见场景

明明建了索引，EXPLAIN 却是 type=ALL，或者 key 为 NULL——这是线上排查慢 SQL 时最常见的困惑。索引失效并不是"MySQL 有 bug"，而是 B+ 树的有序性被破坏、或者优化器算完成本后主动放弃了索引。这篇文章按成因归类 9 个场景，每个都给出可复现的 SQL 和改写方法。基准 MySQL 8.0 + InnoDB，涉及 5.7 差异处会单独标注。

一、准备实验环境

CREATE DATABASE IF NOT EXISTS fail_demo DEFAULT CHARACTER SET utf8mb4;
USE fail_demo;

CREATE TABLE t_order (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  order_no    VARCHAR(32)     NOT NULL,
  user_id     BIGINT UNSIGNED NOT NULL,
  phone       VARCHAR(20)     NOT NULL,
  status      TINYINT         NOT NULL DEFAULT 0,
  amount      DECIMAL(12,2)   NOT NULL,
  created_at  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_order_no (order_no),
  KEY idx_phone (phone),
  KEY idx_user_status (user_id, status),
  KEY idx_created (created_at)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

SET SESSION cte_max_recursion_depth = 10000000;

INSERT INTO t_order (order_no, user_id, phone, status, amount, created_at)
WITH RECURSIVE seq AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1 FROM seq WHERE n < 200000
)
SELECT CONCAT('NO', LPAD(n, 10, '0')),
       n % 5000,
       CONCAT('138', LPAD(n % 100000000, 8, '0')),
       n % 4,
       ROUND(RAND() * 2000, 2),
       DATE_ADD('2026-01-01', INTERVAL (n % 280) DAY)
FROM seq;

ANALYZE TABLE t_order;

后面每条 SQL 都可以直接用 EXPLAIN 验证，关注 type、key、rows、filtered、Extra 五个字段。

二、场景一：对索引列做函数或运算

-- 失效
EXPLAIN SELECT * FROM t_order WHERE DATE(created_at) = '2026-05-01';
-- type=ALL, key=NULL

-- 有效
EXPLAIN SELECT * FROM t_order
WHERE created_at >= '2026-05-01' AND created_at < '2026-05-02';
-- type=range, key=idx_created

B+ 树按列原值排序，DATE(created_at) 是一个新值，树上的顺序对它没有意义，只能逐行计算。同理 WHERE amount + 100 > 500 也会失效，应改为 amount > 400。

三、场景二：隐式类型转换

phone 是 VARCHAR，用数字去查：

-- 失效：字符串列与数字比较，MySQL 把列转成数字
EXPLAIN SELECT * FROM t_order WHERE phone = 13800000001;
-- type=ALL, key=NULL

-- 有效
EXPLAIN SELECT * FROM t_order WHERE phone = '13800000001';
-- type=ref, key=idx_phone

原因是 MySQL 的转换规则：两个操作数类型不同时，把字符串转成数字。转换发生在列上，等价于 CAST(phone AS UNSIGNED) = 13800000001，索引失效。

反过来，INT 列与字符串比较 WHERE user_id = '100' 不会失效，因为此时是把常量转成数字，列本身没变。但仍有额外开销，并且可能导致 '100abc' 这类脏数据被匹配，规范上应始终传匹配类型。

注意：这个坑在 MyBatis 中极易出现。#{phone} 传 String 是安全的；用 ${} 拼接或 Java 侧传了 Long，就会触发隐式转换。

四、场景三：违反最左前缀

联合索引 idx_user_status (user_id, status)：

EXPLAIN SELECT * FROM t_order WHERE status = 1;                  -- type=ALL
EXPLAIN SELECT * FROM t_order WHERE user_id = 100;               -- type=ref
EXPLAIN SELECT * FROM t_order WHERE user_id = 100 AND status=1;  -- type=ref

status 单独查询没有可用的前缀。8.0 引入了 Skip Scan（跳过扫描），在 user_id 基数很低时可能用上联合索引，但这是优化器的启发式行为，不能当作设计依据。

五、场景四：范围条件截断后续列

-- 假设有索引 (user_id, status, amount)
ALTER TABLE t_order ADD KEY idx_u_s_a (user_id, status, amount);

-- status 用了范围，amount 无法用于定位
EXPLAIN SELECT * FROM t_order
WHERE user_id = 100 AND status > 1 AND amount = 500;

status > 1 之后，amount 在索引上不再全局有序，只能靠 ICP 在回表前过滤。如果 amount 的等值条件很关键，应考虑调整索引顺序为 (user_id, amount, status)，取决于实际查询分布。

六、场景五：LIKE 以通配符开头

EXPLAIN SELECT * FROM t_order WHERE order_no LIKE '%000123';  -- type=ALL
EXPLAIN SELECT * FROM t_order WHERE order_no LIKE 'NO000123%'; -- type=range

% 在左边时无法确定扫描起点，只能逐行做字符串匹配。这是所有失效场景里最难救的一类，因为业务确实可能只需要后缀匹配（例如按订单号尾号查询）。

需要区分两种前缀写法：LIKE 'NO000123%' 是前缀匹配，可以走索引；LIKE '%000123%' 是包含匹配，必然全表扫描。中间态 LIKE 'NO%123' 中，NO 部分仍然可以用于定位区间，所以能走 range，只是过滤效果有限。如果业务必须做后缀匹配，方案有：

• 反转存储一列 order_no_reverse，查询时反转条件。
• 用全文索引（FULLTEXT）或外部搜索引擎（ES）。
• 数据量不大时接受全表扫描，但要限制并发。

七、场景六：OR 连接非索引列

-- 失效：status 无独立索引
EXPLAIN SELECT * FROM t_order WHERE user_id = 100 OR status = 1;
-- type=ALL

-- 有效：两侧都有索引，走 index_merge
ALTER TABLE t_order ADD KEY idx_status (status);
EXPLAIN SELECT * FROM t_order WHERE user_id = 100 OR status = 1;
-- type=index_merge, key=idx_user_status,idx_status

OR 的任一侧无法用索引，整个条件就只能全表扫描。index_merge 虽然能用，但 Using union 的代价常常高于单索引，实践中更推荐改写为 UNION ALL。

八、场景七：优化器主动放弃索引

这是最容易被误判为"失效"的一类。索引不是免费的：走索引要先扫索引页再回表，回表是随机 I/O。当优化器估算回表行数占比过高（经验阈值约 20%~30%）时，全表扫描的顺序 I/O 反而更快。

-- status 只有 4 个值，命中 25%，优化器选全表
EXPLAIN SELECT * FROM t_order WHERE status = 1;

-- 强制走索引做对比
EXPLAIN SELECT * FROM t_order FORCE INDEX (idx_status) WHERE status = 1;

FORCE INDEX 的结果不一定更快，它只是把选择权从优化器手里拿走。正确的做法是不要给低区分度列单独建索引，或者用覆盖索引减少回表。

统计信息不准也会导致误判：

-- 查看统计信息
SHOW INDEX FROM t_order;
-- 重新采样
ANALYZE TABLE t_order;

8.0 默认 innodb_stats_persistent=ON，统计信息持久化到 mysql.innodb_index_stats；5.7 早期版本默认是 OFF，重启后统计信息可能重置，导致执行计划漂移。

九、场景八：IS NULL / != / NOT IN 的边界

EXPLAIN SELECT * FROM t_order WHERE phone IS NULL;

IS NULL 在列可为空时可以走索引（索引里存了 NULL），但很多资料说"不能"，这是过时结论。真正的问题是可空列让索引选择性变差，而且 IS NOT NULL 的命中比例往往很高，优化器可能直接选择全表扫描。

!= 和 NOT IN 通常无法使用索引，因为它们等价于"除了某值之外的全部"，命中比例往往很高：

EXPLAIN SELECT * FROM t_order WHERE status != 1;  -- type=ALL

NOT IN 还有个陷阱：子查询结果含 NULL 时整个条件恒为 NULL，返回空集。

十、场景九：字符集与排序规则不一致

JOIN 时两张表字符集不同（utf8mb4 与 utf8），关联列的排序规则不匹配，索引无法使用：

SELECT TABLE_NAME, COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'fail_demo';

-- 修复
ALTER TABLE t_order CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

8.0 默认字符集是 utf8mb4、默认排序规则是 utf8mb4_0900_ai_ci；5.7 默认是 latin1 + utf8mb4_general_ci。跨版本迁移或从 5.7 升级时，这是高频故障点。

十一、常见坑 / 最佳实践

1. 先看 EXPLAIN 再下结论。"索引失效"必须由执行计划证明，凭感觉改 SQL 容易做无用功。用 EXPLAIN ANALYZE（8.0.18+）可以看到实际行数与耗时，比估算更可靠。
2. 统一参数类型。Java 侧 Long 对应 BIGINT、String 对应 VARCHAR，MyBatis 用 #{} 占位符，避免 jdbcType 推断错误引发隐式转换。
3. 不在索引列上做任何加工。函数、运算、类型转换、排序规则转换都会破坏有序性。需要按函数结果查询就用 8.0 的函数索引。
4. 低区分度列不要单独建索引。status、type、is_deleted 这类列，要么进联合索引，要么不建。
5. 升级 5.7 → 8.0 后必须重新核对执行计划。字符集默认值、统计信息持久化、直方图、Skip Scan 都会改变优化器的选择，原本正常的 SQL 可能变慢。

十二、小结

• 索引失效的本质是两类：B+ 树有序性被破坏（函数、隐式转换、前缀通配符、最左前缀），或优化器算完成本主动放弃。
• 隐式类型转换是最隐蔽的一类，规则是"字符串转数字"，转换发生在列上就失效。
• 范围条件会截断联合索引后续列，ICP 只能减少回表，不能恢复定位能力。
• 优化器放弃索引通常发生在回表比例过高时，靠 FORCE INDEX 硬顶不是解法，改索引设计才是。
• 字符集/排序规则不一致导致的 JOIN 失效，在 5.7 升级 8.0 时尤其常见。