【MySQL】 索引核心知识点：索引下推、索引失效、联合索引、使用规范

大家好我是程序员二叉。简介索引是 MySQL 数据库优化查询性能的核心手段合理使用索引能大幅提升 SQL 执行效率反之则会出现索引失效、查询缓慢等问题。本文一次性讲透索引下推原理、索引失效定义、全部失效场景、like 模糊查询规则、隐式转换失效原因、select * 弊端、联合索引设计原则、不适合建索引的场景一套搞定 MySQL 索引面试实战。欢迎点赞收藏关注。一、索引下推ICP1. 原理InnoDB 存储引擎在查询时优先利用索引内字段过滤WHERE条件不再直接根据索引主键逐条回表仅将过滤后符合条件的数据进行回表查询。2. 作用大幅减少回表次数降低磁盘 IO 开销提升查询效率在联合索引 范围查询场景优化效果最明显二、索引失效概述1. 什么是索引失效原本可以使用索引的 SQLMySQL 优化器判定索引效率更低放弃索引转而执行全表扫描查询性能急剧下降。2. 常见索引失效场景索引列参与数学运算、函数调用字段发生隐式类型转换使用!、、NOT IN、NOT EXISTSOR左右条件存在无索引字段联合索引违背最左前缀原则模糊查询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 慢查询与索引面试问题。