SQL 中索引失效的 15 种高频场景及对应优化写法_网络技术

发布时间 - 2026-01-30 00:00:00 点击率：次

索引失效的五大主因：①对索引列使用函数或表达式；②LIKE以%开头；③隐式类型转换；④联合索引未遵循最左前缀原则；⑤统计信息陈旧、选择性差或返回行数过多。

只要在 WHERE 条件里对索引列做了计算、类型转换或调用函数，MySQL/PostgreSQL 等主流引擎基本都会放弃走索引。比如 WHERE YEAR(create_time) = 2025，即使 create_time 有索引，也会全表扫描。

✅ 正确写法：改用范围查询，WHERE create_time >= '2025-01-01' AND create_time
✅ 或建函数索引（MySQL 8.0+ / PG）：CREATE INDEX idx_year ON t1 (YEAR(create_time))，但注意这仅对特定函数有效
⚠️ 常见陷阱：WHERE UPPER(name) = 'ABC'、WHERE age + 1 > 30、WHERE DATE(update_time) = '2025-01-01' 全部失效

LIKE 的左侧带 %（如 LIKE '%abc'）会导致索引无法做最左前缀匹配，只能回表或全扫。只有 LIKE 'abc%' 才可能命中 B+ 树的有序结构。

✅ 优化方向一：前置固定字符，LIKE 'abc%' 可走索引；若业务允许，把模糊逻辑后置（如搜索“张”姓用户，用 LIKE '张%'）
✅ 优化方向二：用全文索引（FULLTEXT）或外部搜索引擎（Elasticsearch）处理前后模糊场景
⚠️ 注意：LIKE '_abc'（下划线单字符）同样不走索引；COLLATE 不匹配时（如字段是 utf8mb4_0900_as_cs，查询却用默认校对），也可能导致索引跳过

当 WHERE 条件中索引列与传入值类型不一致，数据库会自动转换——但转换动作常施加在索引列上，使其无法使用索引。典型如字符串字段存数字（user_id VARCHAR(32)），却写成 WHERE user_id = 123。

✅ 查看执行计划确认：EXPLAIN SELECT ... 中 type 为 ALL 或 index，且 key 为空，大概率存在隐式转换
✅ 统一类型：字符串字段就用引号，WHERE user_id = '123'；数字字段别存成字符串
⚠️ MySQL 特别敏感：WHERE status = '1' 对 TINYINT 字段可能走索引，但 WHERE status = 1 对 VARCHAR 字段必然不走——因为优化器把列转成了数字，破坏了索引有序性

联合索引 (a, b, c) 本质是先按 a 排序，a 相同再按 b，以此类推。跳过最左列（如只查 b = ? 或 c = ?）就无法定位数据块起始位置。

✅ 能用上的条件组合：a = ?、a = ? AND b = ?、a = ? AND b = ? AND c = ?、a = ? AND b IN (?, ?)（c 可范围）、a BETWEEN ? AND ? AND b = ?
✅ 部分可用：a = ? AND c = ? —— c 不走索引，但 a 还能过滤；而 b = ? AND c = ? 完全失效
⚠️ 注意：ORDER BY 也受此约束。若要 ORDER BY b, c 走索引，必须带上 WHERE a = ?，否则排序仍需 filesort

索引失效不是黑箱，本质是优化器判断「走索引比全扫更贵」。真正容易被忽略的是统计信息陈旧（ANALYZE TABLE 没跑）、索引选择性差（如 gender 只有男/女）、或者查询返回大量行（超过约 20% 行数时，MySQL 倾向直接全表扫描）。这些不会报错，但会让前面所有优化白费。