MySQL 数据库入门到大牛，索引失效的几种情况
MySQL 数据库入门到大牛，索引失效的几种情况
一、个人总结
- 1、AND 连接的列，有联合索引，单列索引失效。
- WHERE name = ‘JetLi’ and age = 25。
- 如果建立了 (name, age) 联合索引，通常不需要再单独建立 name 单列索引，否则会造成冗余，增加写入成本。
- 2、搜索条件没有使用联合索引最左边的列，联合索引失效。
- WHERE age = 25。
- name 和 age 有联合索引，联合索引失效。
- 3、对列进行运算、函数、类型转换会导致索引失效。
- WHERE name = 123 （name 是字符串类型，123 是数字）。
- 4、范围条件右边的列，索引失效。
- WHERE age > 25 AND name = ‘JetLi’。
- age 和 name 有联合索引。联合索引失效。
- 建议把范围查询的字段放在联合索引的最后，即 name 和 age。
- 5、不等于条件，索引失效。
- WHERE age <> ‘25’。
- age 是不等于条件，age 单列索引失效。
- 6、is not null，索引失效。
- WHERE age is not null。
- age 单列索引失效。
- 7、通配符开头，索引失效。
- WHERE name like ‘%Jet’。
- name 单列索引失效。
- 8、OR 前后存在没有索引的列，索引失效。
- WHERE name = ‘JetLi’ OR age = 25。
- name 有单列索引，age 没有单列索引，name 上的单列索引失效。
- 9、数据库和表字符集不一致，索引失效。
- 两个字符集不一致的表连接查询
- 10、InnoDB 存储引擎，如果使用主键自增，而我们插入数据的主键忽大忽小，索引失效。
- 这个不算索引失效，但是这种情况也会影响效率。
二、AI 验证优化
你的总结已经涵盖了 MySQL 索引失效的绝大多数核心场景，非常棒！特别是对联合索引最左前缀原则、函数操作以及类型转换的总结非常准确。
1、 完善后的“索引失效清单”
为了方便你记忆，我把我们讨论的内容整理成一个表格：
| 失效场景 | 典型 SQL 示例 | 核心原因 | 优化建议 |
|---|---|---|---|
| 最左前缀原则 | WHERE age=25(联合索引为(name,age)) | 跳过了联合索引最左列 | 调整查询条件或索引顺序 |
| 索引列计算/函数 | WHERE YEAR(create_time) = 2023 | 破坏了索引有序性 | 将计算移到等号右边 |
| 类型转换 | WHERE phone=138...(phone为字符串) | 隐式转换导致全表扫描 | 保持数据类型一致 |
| 左模糊查询 | WHERE name LIKE '%Li' | B+树无法反向匹配 | 改为右模糊LIKE 'Li%' |
| OR 条件陷阱 | WHERE indexed=1 OR not_indexed=2 | 无索引列导致全表扫描 | 使用UNION拆分查询 |
| 范围查询阻断 | WHERE a>1 AND b=2(联合索引(a,b)) | 范围查询后索引断裂 | 将范围字段放在联合索引末尾 |
| 不等于操作 | WHERE status != 'active' | 需扫描大部分数据 | 尽量用IN列举代替 |
| IS NOT NULL | WHERE name IS NOT NULL | 优化器认为全表扫描更快 | 字段设置为NOT NULL默认空值 |
| 字符集不一致 | JOIN时两边字符集不同 | 需要做隐式转换 | 统一表和字段的字符集 |
2、 总结
你的总结已经非常全面了，以上补充主要是为了严谨性。
最后给你一个排查索引问题的终极建议：
永远不要靠猜！使用 EXPLAIN 或 EXPLAIN FORMAT=JSON 命令来查看执行计划。
- 看 type 列：如果是 ALL 就是全表扫描（索引失效）；如果是 ref/range/const 说明用了索引。
- 看 key 列：到底用了哪个索引。
- 看 Extra 列：是否有 Using where; Using filesort 或 Using temporary，这些也是性能杀手。
希望这些建议能帮你的总结更加完美！