写在前面说实话我见过太多人建索引跟撒胡椒面似的不管啥字段都往上怼结果查询照样慢写操作还被拖垮。去年我帮一个兄弟看慢 SQL一张表 8 个索引查询还是用不上。一问才知道他连覆盖索引四个字都没听过。今天咱就把覆盖索引和索引下推这两个利器讲透看完你再去建索引心里就有底了。文章目录一、为什么索引这么快1.1 一个直观的数字1.2 索引原理速览二、覆盖索引Covering Index2.1 什么是覆盖索引2.2 回表到底是什么2.3 实战联合索引设计实现覆盖查询2.4 完整代码示例2.5 问题与解答三、索引下推Index Condition Pushdown, ICP3.1 ICP 是什么3.2 原理对比3.3 开关控制与验证3.4 EXPLAIN 对比实验3.5 ICP 的使用条件四、联合索引设计最佳实践4.1 最左前缀原则4.2 字段顺序区分度高的放前面4.3 索引失效的常见坑4.4 索引设计 Checklist五、EXPLAIN 字段解读5.1 type 列访问类型5.2 关键字段速查5.3 完整 EXPLAIN 结果解读示例六、踩坑指南七、问题与解答八、面试高频考点汇总考点 1什么是覆盖索引怎么判断有没有触发考点 2索引下推ICP的原理和好处考点 3联合索引的最左前缀原则是什么考点 4索引失效的常见场景有哪些考点 5聚簇索引和非聚簇索引的区别回表是什么九、模拟面试官提问与参考答案场景题 1场景题 2场景题 3场景题 4场景题 5十、互动话题十一、参考资料一、为什么索引这么快1.1 一个直观的数字千万级数据表没索引查一条记录要3-5 秒加了合适的索引只要3-5 毫秒。差了一千倍。这差距就跟找书一样——没有目录你得一页一页翻有目录直接翻到对应页码。索引就是数据库的目录。1.2 索引原理速览MySQL 的 InnoDB 用B 树存索引聚簇索引叶子节点存的是完整数据行主键索引就是聚簇索引非聚簇索引叶子节点存的是主键值查完整数据需要回表聚簇索引 非聚簇索引二级索引 [10] [Alice] - 10 / \ [Bob] - 20 [5] [20] [Carol] - 10走非聚簇索引时先查到主键再拿主键去聚簇索引查整行数据——这就是回表。二、覆盖索引Covering Index2.1 什么是覆盖索引查询的所有字段都在索引里数据库不需要回表直接从索引拿数据。EXPLAIN 的 Extra 列会出现Using index。-- 假设有联合索引 INDEX idx_user (user_id, order_no, amount)SELECTorder_no,amountFROMorderWHEREuser_id10086;这条 SQL 查的三个字段user_id、order_no、amount都在索引idx_user里MySQL 扫一遍索引树就完事了一次回表都没有。2.2 回表到底是什么想象你去图书馆找书回表先从书名目录找到索书号再拿索书号去书架找实体书。跑了两次。覆盖索引你要的信息书名、作者、价格目录卡片上全有直接拿走不用去书架。每次回表都是一次随机 IO性能开销不小。覆盖索引能减少甚至避免回表尤其在数据量大的时候效果显著。2.3 实战联合索引设计实现覆盖查询需求根据user_id查order_no和amount。索引设计CREATEINDEXidx_user_order_amountONorder(user_id,order_no,amount);SQL 验证EXPLAINSELECTorder_no,amountFROMorderWHEREuser_id10086;EXPLAIN 结果解读字段值说明typeref使用索引匹配keyidx_user_order_amount使用了联合索引key_len8user_id 字段长度ExtraUsing index覆盖索引无需回表看到Using index这俩字就知道成了。2.4 完整代码示例ServicepublicclassOrderQueryService{AutowiredprivateOrderMapperorderMapper;/** * 查询用户的订单号和金额利用覆盖索引优化 */publicListOrderBriefDTOqueryUserOrderBrief(LonguserId){// 这条SQL只查索引中存在的字段触发覆盖索引returnorderMapper.selectOrderNoAndAmountByUserId(userId);}}// DTO 只包含索引中的字段dataclassOrderBriefDTO(val orderNo:String,val amount:BigDecimal)selectidselectOrderNoAndAmountByUserIdresultTypeOrderBriefDTOSELECT order_no, amount FROM order WHERE user_id #{userId}/select踩坑提醒如果查询里多加一个不在索引里的字段比如SELECT order_no, amount, status覆盖索引立马失效Extra 变成 Using where。我见过太多人索引建对了SQL 里手贱多写了一个字段性能直接打回原形。2.5 问题与解答Q1覆盖索引只适用于 SELECT 吗主要是 SELECT 场景受益最大。但 INSERT/UPDATE/DELETE 时MySQL 也要维护索引索引越多写越慢。所以覆盖索引是查询优化手段不是让你无节制建索引的理由。Q2联合索引字段顺序怎么定最左前缀原则。查询条件里用WHERE user_id ?所以user_id放最左。后面跟着的order_no, amount是为了让查询能覆盖减少回表。如果查询条件是WHERE order_no ?这个索引就用不上了。Q3COUNT(*) 能走覆盖索引吗能。比如SELECT COUNT(*) FROM order WHERE user_id 10086只要user_id有索引MySQL 直接扫索引树计数不需要回表查整行。三、索引下推Index Condition Pushdown, ICP3.1 ICP 是什么MySQL 5.6 引入的优化特性。简单说把 WHERE 条件的过滤从 Server 层下推到存储引擎层在索引扫描的时候就过滤掉不满足条件的记录减少回表次数。3.2 原理对比假设有联合索引INDEX idx (name, age)查询SELECT*FROMuserWHEREnameLIKE张%ANDage20;没有 ICPMySQL 5.5 及以前存储引擎根据name LIKE 张%找到所有姓张的记录每一条都回表查完整数据Server 层再判断age 20把不符合的扔掉有 ICPMySQL 5.6存储引擎根据name LIKE 张%找到记录直接在存储引擎层判断age 20不满足条件的直接跳过只回表符合条件的记录3.3 开关控制与验证-- 查看 ICP 是否开启SHOWVARIABLESLIKEoptimizer_switch;-- 关闭 ICP测试用SEToptimizer_switchindex_condition_pushdownoff;-- 开启 ICPSEToptimizer_switchindex_condition_pushdownon;3.4 EXPLAIN 对比实验建表和索引CREATETABLEuser(idINTPRIMARYKEYAUTO_INCREMENT,nameVARCHAR(50),ageINT,addressVARCHAR(200),INDEXidx_name_age(name,age));关闭 ICPSEToptimizer_switchindex_condition_pushdownoff;EXPLAINSELECT*FROMuserWHEREnameLIKE张%ANDage20;-- Extra: Using whereServer 层过滤开启 ICPSEToptimizer_switchindex_condition_pushdownon;EXPLAINSELECT*FROMuserWHEREnameLIKE张%ANDage20;-- Extra: Using index conditionICP 生效看到Using index condition就是 ICP 在干活。3.5 ICP 的使用条件不是所有查询都能用 ICP得满足MySQL 5.6废话查询字段包含索引列和非索引列的联合条件只能用于二级索引聚簇索引本身就有完整数据不需要不能用于子查询、存储过程部分限制场景是否触发 ICP原因WHERE name 张三 AND age 20否两个都是索引列直接走索引匹配WHERE name LIKE 张% AND age 20是name走范围age 在索引里但用不上最左匹配ICP 来补WHERE name 张三 AND address 北京是address不在索引里ICP 下推到引擎层过滤四、联合索引设计最佳实践4.1 最左前缀原则联合索引(a, b, c)查询条件必须从最左开始才能用上索引WHEREa1-- 用索引WHEREa1ANDb2-- 用索引WHEREa1ANDb2ANDc3-- 用索引WHEREb2-- 不用索引WHEREa1ANDc3-- 只用 a4.2 字段顺序区分度高的放前面-- 不好status 只有几种值区分度低INDEXidx_status_name(status,name)-- 好name 区分度高过滤能力强INDEXidx_name_status(name,status)区分度 不重复值数量 / 总记录数。越接近 1 越好。4.3 索引失效的常见坑操作示例结果前导模糊LIKE %张索引失效OR 条件WHERE a 1 OR b 2可能全表扫描函数操作WHERE YEAR(create_time) 2024索引失效隐式转换WHERE phone 13800138000字符串字段传数字索引失效计算表达式WHERE id 1 100索引失效踩坑提醒隐式类型转换这个坑我踩过。表字段是VARCHAR手机号Java 代码传了Long类型MySQL 偷偷把字段转成了数字索引直接作废。解决办法Java 传 String或者 SQL 里手动CAST。4.4 索引设计 Checklist检查项是否通过说明查询条件字段是否在最左前缀-确保索引能被用到SELECT 字段能否被索引覆盖-减少回表区分度低的字段是否放后面-提高索引过滤效率是否有函数/隐式转换导致失效-EXPLAIN 确认索引数量是否过多-一般不超过 5-6 个五、EXPLAIN 字段解读5.1 type 列访问类型性能从高到低system const eq_ref ref range index ALLtype含义场景const主键或唯一索引等值匹配WHERE id 1eq_refJOIN 中主键匹配关联表主键查询ref非唯一索引等值匹配WHERE name 张三range索引范围扫描WHERE id 100 AND id 200index全索引扫描SELECT count(*) FROM tableALL全表扫描没有索引或索引失效5.2 关键字段速查字段重点关注key实际用了哪个索引NULL 表示没用索引rows预估扫描行数越小越好ExtraUsing index覆盖索引、Using index conditionICP、Using whereServer 层过滤、Using filesort需要排序优化5.3 完整 EXPLAIN 结果解读示例EXPLAINSELECTorder_no,amountFROMorderWHEREuser_id10086ANDcreate_time2024-01-01;id: 1 select_type: SIMPLE table: order type: ref possible_keys: idx_user_time, idx_user_order_amount key: idx_user_order_amount key_len: 8 ref: const rows: 150 Extra: Using index condition; Using where解读type: ref—— 非唯一索引等值匹配还不错key: idx_user_order_amount—— 实际用了这个索引rows: 150—— 预估扫 150 行Extra: Using index condition—— ICP 生效在引擎层过滤了create_time六、踩坑指南坑 1索引不是越多越好每多一个索引INSERT/UPDATE/DELETE 就要多维护一棵树。我见过一张表 12 个索引写操作慢得像蜗牛。一般单表索引不超过 5-6 个联合索引优先于单列索引。坑 2选择性低的字段不适合单独建索引性别字段就两种值建了索引 MySQL 也不一定用优化器觉得全表扫描更快。非要建的话放在联合索引的后面。坑 3联合索引字段过多导致索引过大联合索引(a, b, c, d, e)体积可能跟表差不多大维护成本高。建议关键查询条件放前面SELECT 里需要覆盖的字段精选一下。坑 4隐式类型转换导致索引失效WHERE phone 13800138000phone 是 VARCHARMySQL 会把 phone 字段转成数字再比较索引直接失效。解决办法传 String 类型或者写WHERE phone 13800138000。七、问题与解答Q1覆盖索引和索引下推有什么区别覆盖索引解决的是要不要回表的问题——查询字段全在索引里直接返回零回表。ICP 解决的是回表次数能不能减少的问题——条件里有非索引字段在引擎层先过滤减少回表次数。两者可以叠加使用效果更好。Q2为什么有时候 EXPLAIN 显示用了索引查询还是很慢可能原因1索引虽然用了但扫了太多行rows 很大2回表次数太多没有覆盖索引3数据在磁盘上分散随机 IO 太多4锁竞争或并发高。EXPLAIN 只是执行计划还得结合慢查询日志和性能测试综合分析。Q3MySQL 一定会用覆盖索引吗不一定。优化器会权衡成本如果它觉得全表扫描更快比如数据量很小、索引选择性差就可能不用索引。可以用FORCE INDEX强制指定但一般不建议除非你很确定。八、面试高频考点汇总考点 1什么是覆盖索引怎么判断有没有触发答案查询的所有字段都在索引中不需要回表查完整记录。看 EXPLAIN 的 Extra 列出现Using index就是触发了。设计思路是把查询条件和 SELECT 字段尽量放到联合索引里。考点 2索引下推ICP的原理和好处答案MySQL 5.6 引入将 WHERE 条件的过滤从 Server 层下推到存储引擎层。好处是在索引遍历阶段就过滤掉不满足条件的记录减少回表次数。看 EXPLAIN 的 Extra 列出现Using index condition表示 ICP 生效。考点 3联合索引的最左前缀原则是什么答案联合索引(a, b, c)查询必须从最左边的字段开始按顺序使用中间不能跳过。WHERE a1 AND b2能用上索引WHERE b2用不上。原理是 B 树按最左字段排序跳过左边字段就无法二分查找。考点 4索引失效的常见场景有哪些答案1前导模糊LIKE %xx2对索引字段做函数操作3隐式类型转换4OR 条件里部分字段没索引5计算表达式WHERE id11006不等于、NOT IN部分情况。考点 5聚簇索引和非聚簇索引的区别回表是什么答案聚簇索引的叶子节点存完整数据行InnoDB 的主键索引就是聚簇索引。非聚簇索引叶子节点存主键值查完整数据需要根据主键再去聚簇索引查一次——这就是回表。覆盖索引的作用就是避免回表。九、模拟面试官提问与参考答案场景题 1面试官有一张订单表查询场景是根据 user_id 查订单号和金额你会怎么设计索引参考答案建联合索引INDEX idx_user_order_amount (user_id, order_no, amount)。user_id放最左因为查询条件用它order_no和amount放后面让查询变成覆盖索引避免回表。用 EXPLAIN 验证 Extra 列出现Using index。场景题 2面试官现在需求变了还要根据 create_time 范围筛选同时保留原来的 user_id 查询怎么办参考答案看哪种查询更频繁。如果user_id ?的等值查询最多保留(user_id, order_no, amount)create_time 的范围查询可以用 ICP 在引擎层过滤或者单独建一个(user_id, create_time)索引。如果范围查询是主要场景把create_time放到联合索引后面INDEX idx_user_time (user_id, create_time)。但要注意这样可能就覆盖不了order_no和amount了需要权衡。场景题 3面试官查询SELECT * FROM user WHERE name LIKE %张% AND age 20有索引(name, age)能走索引吗参考答案name LIKE %张%是前导模糊索引失效整个查询变成全表扫描后在 Server 层过滤。即使 age 在索引里最左前缀断了后面的字段也用不上。解决办法1用搜索引擎如 Elasticsearch处理模糊匹配2如果业务允许改成name LIKE 张%就能走索引 ICP3冗余一个拼音或分词字段做反向索引。场景题 4面试官EXPLAIN 看到 type 是 index这说明什么好还是不好参考答案type: index表示全索引扫描MySQL 把整个索引树扫了一遍。比ALL全表扫描好一点因为索引通常比表小但还是不够高效。如果是SELECT COUNT(*)或覆盖索引查询index 是正常的如果是普通查询带了 WHERE 条件还是 index说明索引没用上需要优化。场景题 5面试官一张表数据量很大读写比例 10:1索引怎么设计参考答案读多写少可以适度多建索引重点优化覆盖索引减少回表。分析慢查询日志把 TOP 10 的查询拿出来看能不能用联合索引覆盖。写操作虽然少但批量写入时索引维护成本还在所以单表索引控制在 5 个以内。另外定期用OPTIMIZE TABLE或重建索引消除碎片保持 B 树紧凑。十、互动话题你在实际项目中有没有遇到过明明建了索引但查询还是很慢的情况最后发现是什么原因是字段顺序不对、隐式转换、还是索引根本没用上评论区聊聊大家一起排坑。十一、参考资料MySQL 官方文档Index Condition Pushdown OptimizationMySQL 覆盖索引与回表 - 知乎原创不易如果觉得有帮助点个免费的赞再走吧。你的支持是我持续输出的动力。