【MySQL】 索引核心知识点：索引下推、索引失效、联合索引、使用规范
大家好，我是程序员二叉。
简介
索引是 MySQL 数据库优化查询性能的核心手段，合理使用索引能大幅提升 SQL 执行效率，反之则会出现索引失效、查询缓慢等问题。
本文一次性讲透：索引下推原理、索引失效定义、全部失效场景、like 模糊查询规则、隐式转换失效原因、select * 弊端、联合索引设计原则、不适合建索引的场景，一套搞定 MySQL 索引面试+实战。欢迎点赞收藏关注。
一、索引下推（ICP）
1. 原理
InnoDB 存储引擎在查询时，优先利用索引内字段过滤WHERE条件，不再直接根据索引主键逐条回表，仅将过滤后符合条件的数据进行回表查询。
2. 作用
- 大幅减少回表次数，降低磁盘 IO 开销
- 提升查询效率，在联合索引 + 范围查询场景优化效果最明显
二、索引失效概述
1. 什么是索引失效
原本可以使用索引的 SQL，MySQL 优化器判定索引效率更低，放弃索引转而执行全表扫描，查询性能急剧下降。
2. 常见索引失效场景
- 索引列参与数学运算、函数调用
- 字段发生隐式类型转换
- 使用
!=、<>、NOT IN、NOT EXISTS OR左右条件存在无索引字段- 联合索引违背最左前缀原则
- 模糊查询
LIKE以%开头 ORDER BY排序字段不在索引中，或与索引顺序不一致- 字段区分度极低（如性别、状态），优化器主动放弃索引
- 使用
IS NULL/IS NOT NULL（多数场景失效） IN后数据量过大，也会触发索引失效
三、模糊查询 LIKE 索引使用规则
LIKE 关键词%：走索引，前缀匹配符合索引有序特性LIKE %关键词/LIKE %关键词%：索引失效，无法利用索引有序结构
四、字段隐式转换导致索引失效的原因
MySQL 遵循低精度向高精度自动转换规则：
当索引字段为字符串类型，查询条件传入数字，数据库会等价转换为：WHERE CAST(索引列 AS 数值类型) = 条件值
本质：索引列被函数包裹，索引结构被破坏，最终导致索引失效。
五、为什么不建议使用 SELECT *
- 读取全部字段，加载大量无用数据，增加磁盘 IO 与网络传输开销
- 无法使用覆盖索引，必须额外回表查询，丧失索引优化能力
- 数据表字段变更时，极易引发程序报错，维护性差
- 结果集数据量大，占用更多网络带宽
最佳实践：只查询业务所需字段。
六、联合索引创建顺序原则
核心排序规则（优先级从高到低）：
- 经常作为查询条件的字段放左侧（满足最左前缀）
- 区分度高的字段靠前，过滤数据效果更好
- 字段长度小的字段靠前，减小索引体积，提升缓存命中率
总结口诀：高频在前、高区分度在前、短字段在前。
七、不适合建立索引的场景
- 数据表数据量极小（几百条以内），全表扫描效率高于索引
- 字段区分度极低：性别、布尔状态、固定枚举值等
- 表频繁执行增删改（DML）：索引会额外增加数据维护成本
- 字段极少出现在
WHERE、ORDER BY、GROUP BY中 - 大文本类型字段：
TEXT、BLOB、超长VARCHAR，索引体积过大 - 业务以批量更新、全表操作为主，索引收益远低于维护开销
八、全文总结
本文汇总了 MySQL 索引面试必考全集：
- 索引下推：先在索引层完成数据过滤，减少回表 IO，对范围查询优化效果显著。
- 索引失效：核心原因是索引有序性被破坏、索引列被函数 / 运算包裹，或是优化器判定全表扫描更高效。
- 模糊查询：仅后缀匹配 like xxx% 可走索引，左模糊、全模糊均会索引失效。
- 隐式类型转换：数据库自动转换数据类型，导致索引列被函数包裹，是高频失效场景。
- select *：生产环境不推荐使用，会错失覆盖索引优势，额外增加数据传输与 IO 压力。
- 联合索引：遵循「高频、高区分、短字段」排序规则，同时严格遵守最左前缀原则。
- 索引设计禁忌：小表、低区分度字段、频繁 DML 表、大文本字段，都不建议建立索引。
熟练掌握以上规则，可以解决90% 的 MySQL 慢查询与索引面试问题。