PDF大白话说Java面试题 — 03-Mysql篇第18题a 和 b 是联合索引SELECT * FROM table WHERE a 1 AND b 2是否可以使用到索引回答核心考点大厂面试要求深入理解联合索引的最左前缀原则、范围查询对后续列的影响、索引条件下推ICP机制并能结合执行计划和MySQL版本差异进行分析。面试官常追问“MySQL 5.6前后有什么区别”、“如何优化这条SQL”1. 核心结论可以使用索引但只能部分命中版本a 1b 2说明MySQL 5.6 之前✅ 索引范围扫描❌ 无法使用b条件在Server层过滤需回表所有a1的记录MySQL 5.6 及之后✅ 索引范围扫描✅ ICP过滤b条件在存储引擎层通过ICP过滤减少回表关键点a 1是范围查询可以利用联合索引(a, b)进行索引范围扫描b 2无法用于索引范围查找因为范围a破坏了后续列的有序性但MySQL 5.6的**索引条件下推ICP**可以在索引扫描时用b列提前过滤减少回表次数2. 原理解析2.1 为什么b 2无法用于索引范围查找联合索引(a, b)在B树中的排序规则先按a排序a相同时按b排序当WHERE a 1 AND b 2执行时定位到a 1的下一条记录a 1的起始位置在a 1的范围内b的值是无序的因为a不同时b不保证顺序因此无法用二分查找直接定位到b 2的位置示意图索引(a,b)的B树结构 (1, 2) (1, 5) (2, 1) (2, 3) ← a1的范围 (3, 2) ← b2的记录但位置不连续 (3, 4) (4, 2) ← b2的记录在a 1的范围内b 2的记录分散在(2,?)、(3,2)、(4,2)等位置无法一次性定位。2.2 MySQL 5.6的ICP优化无ICP5.6之前索引扫描找到所有a 1的记录假设1000条每条记录都回表1000次回表Server层过滤b 2假设最终10条有ICP5.6及之后索引扫描找到a 1的记录在索引层面直接判断b 2不满足的直接跳过只有满足b 2的记录才回表10次回表效果回表次数从1000次降到10次性能提升100倍。3. 执行计划验证MySQL 5.6环境EXPLAINSELECT*FROMtableWHEREa1ANDb2;字段值解读typerange范围扫描keyidx_a_b使用了联合索引ExtraUsing index condition使用了ICPb条件在索引层过滤rows~1000估算扫描行数a1的记录数filtered~10%经过b2过滤后的比例MySQL 5.6之前EXPLAINSELECT*FROMtableWHEREa1ANDb2;字段值解读typerange范围扫描keyidx_a_b使用了联合索引ExtraUsing whereb条件在Server层过滤rows~1000扫描1000行filtered~10%同样10%但回表已经发生Extra字段关键区别Using index condition→ ICP生效b条件在存储引擎层过滤Using where→ b条件在Server层过滤回表更多4. 优化方案问题SELECT *需要回表即使有ICP回表次数仍等于满足条件的行数。方案一创建覆盖索引最佳-- 假设表中只有 a, b, c, d 四个字段CREATEINDEXidx_a_b_coveringONtable(a,b,c,d);-- 查询改为覆盖索引SELECTa,b,c,dFROMtableWHEREa1ANDb2;-- 如果SELECT包含的所有字段都在索引中Extra会显示Using index无回表注意覆盖索引要求索引包含查询所需的所有字段。SELECT *几乎无法被覆盖除非全表字段都在索引中。方案二改写查询条件-- 原查询范围等值SELECT*FROMtableWHEREa1ANDb2;-- 如果不能改索引尝试将a的范围条件转为等值条件如果业务允许SELECT*FROMtableWHEREaIN(2,3,4,5,...)ANDb2;-- IN条件可以走索引查找不破坏b的有序性方案三调整联合索引顺序-- 将等值查询的列放在前面CREATEINDEXidx_b_aONtable(b,a);-- 查询变为SELECT*FROMtableWHEREb2ANDa1;-- 先用b2精确定位等值再在b2的范围内扫描a1-- 此时两个条件都能充分利用索引对比索引顺序查询条件索引使用情况(a, b)a 1 AND b 2a范围b用ICP过滤(b, a)b 2 AND a 1b等值定位a范围扫描哪个更好(b, a)能完整利用索引的排序特性通常比(a, b)ICP更高效推荐将等值查询列放在联合索引最左边范围查询列放右边5. 边界情况分析情况一a 1 匹配的数据量很大如50%数据方案扫描行数回表行数I/O类型使用索引总行数×50%满足b2的行数索引扫描(顺序)回表(随机)全表扫描总行数总行数全表顺序扫描优化器可能选择全表扫描因为大量回表随机I/O成本可能高于全表顺序扫描。情况二区分度极低如a只有1,2两个值-- a只有1和2SELECT*FROMtableWHEREa1ANDb2;-- 相当于WHERE a 2 AND b 2全是精确匹配-- 索引(a,b)能完全命中此时应检查执行计划可能需要ANALYZE TABLE更新统计信息。6. 版本对比表对比项MySQL 5.5及之前MySQL 5.6a 1使用索引✅ 范围扫描✅ 范围扫描b 2使用索引❌ 无法使用✅ ICP过滤回表次数 a1的行数 满足a1且b2的行数EXPLAIN ExtraUsing whereUsing index condition性能较差显著提升7. 索引条件vs覆盖索引技术原理是否需要回表EXPLAIN标识ICP索引扫描时提前过滤仍需回表符合条件的行Using index condition覆盖索引索引包含所有查询列不回表Using index两者结合覆盖索引ICP不回表Using indexICP仍可生效但无需标识注意如果查询是覆盖索引ICP可能不再显示Using index condition因为根本不需要回表。8. 实战建议优先调整索引顺序将等值查询列b 2放在联合索引最左边范围查询列a 1放在右边 → 索引(b, a)考虑覆盖索引如果SELECT字段固定且不多创建包含所有查询字段的索引升级MySQL版本5.6的ICP对这类查询优化显著监控执行计划定期检查Extra字段是否出现Using index condition或Using where评估索引选择性如果a 1匹配大量数据优化器可能选择全表扫描9. 总结对比表场景索引(a,b)索引(b,a)覆盖索引(a,b,cols)说明SELECT * WHERE a1 AND b2部分命中a范围 ICP完全命中覆盖索引ICP推荐(b,a)或覆盖索引SELECT * WHERE a1 AND b2完全命中完全命中覆盖索引两者都能完全命中SELECT a,b WHERE a1 AND b2部分命中ICP完全命中覆盖索引无回表最佳覆盖索引面试官想要的满分总结联合索引(a, b)下WHERE a 1 AND b 2可以使用索引但只能部分命中。原理解析a 1是范围查询可以利用索引进行范围扫描b 2无法用于索引范围查找因为a范围后b无序MySQL 5.6通过索引条件下推(ICP)在索引扫描时直接判断b 2提前过滤减少回表次数版本差异5.6之前先回表再过滤回表次数所有a1的行数5.6之后先过滤再回表回表次数最终满足条件的行数优化方案调整索引顺序(b, a)将等值列放左、范围列放右能完全利用索引创建覆盖索引如果查询字段固定建覆盖索引避免回表改写查询将范围查询改为IN等值列表如业务允许一句话范围查询a1后b2不能索引查找但MySQL 5.6可通过ICP在索引层过滤减少回表最佳实践是索引顺序改为(b, a)。觉得对您有帮助麻烦点点关注啦您的关注是我创作的最大动力~