Mysql索引B+树、回表、覆盖、最左前缀
Eddie一、B+树索引底层原理
1. B+树结构
B+树分为三层：根节点、分支节点、叶子节点。
-
分支节点/根节点：只存储索引键+子节点指针，不存储行数据，仅用于路由寻址；
-
叶子节点：存储索引数据、主键值，所有真实数据都在叶子节点；
-
所有叶子节点通过双向链表有序串联，范围查询、排序查询极其高效。
2. MySQL 为什么选用 B+ 树？
-
二叉树/红黑树：树层高，大数据量磁盘IO次数多，查询不稳定；
-
B树：数据分散在所有节点，查询随机性大，IO次数更多；
-
B+树：层高极低、IO次数最少、叶子节点链表有序，精准查询+范围查询性能最优，完美适配磁盘IO场景。
二、聚簇索引 & 二级索引
1. 聚簇索引（主键索引）
-
InnoDB 默认自带，一张表有且仅有一个；
-
叶子节点存储整行完整数据；
-
主键查询最快，无需回表；
-
无主键时，MySQL 自动选用唯一索引作为聚簇索引，无唯一索引则自动生成隐藏 rowid。
2. 二级索引（辅助索引）
-
用户手动创建的普通索引、唯一索引、联合索引都属于二级索引；
-
叶子节点只存储 索引列 + 主键值，不存储完整行数据；
-
查询非索引字段时，必须通过主键回表查询聚簇索引获取数据。
3. 两者核心区别
聚簇索引存整行数据、唯一、查询最快；二级索引只存索引+主键，需要回表，用于辅助检索。
三、回表查询、覆盖索引、ICP索引下推
1. 回表查询
定义：使用二级索引查询时，索引无法包含所有查询字段，需要拿着主键再次查询聚簇索引获取完整数据的过程。
流程：二级索引检索 → 获取主键 → 走聚簇索引 → 返回完整数据
弊端：多一次索引IO查询，高并发场景性能损耗大。
2. 覆盖索引
定义：查询所需的所有字段，全部包含在二级索引中，无需回表。
优点：消除回表IO、性能最优、高频优化手段。
识别方式：explain 执行计划 Extra 字段显示 Using index。
业务场景：列表查询、分页查询、只查询固定少数字段场景。
3. ICP 索引条件下推（MySQL5.6+）
原理：将原本 Server 层做的索引字段过滤逻辑，下推到 存储引擎层 过滤。
作用：在索引层面提前过滤无效数据，大幅减少回表次数和数据传输量。
无ICP：引擎层查所有匹配索引数据，全部返回Server层过滤，大量无效回表；
有ICP：引擎层直接过滤不满足条件数据，只回表有效数据。
四、最左前缀原则
适用范围：仅针对联合索引
规则：联合索引按照创建顺序排序存储，查询必须匹配最左侧连续字段才能命中索引。
核心口诀：左断全断、右断没事。
-
命中最左连续字段：正常走索引；
-
跳过左侧字段、中间字段断裂：后面所有索引字段全部失效；
-
左侧等值、右侧范围查询：范围之后字段失效。
五、联合索引如何建（企业标准规范）
四大优先级（面试必背）
-
等值优先：等值查询字段放前面，范围查询放后面；
-
高频优先：业务查询频次最高的字段前置；
-
区分度优先：离散度高、重复数据少的字段优先；
-
短字段优先：字段长度越小，索引占用空间越小、加载越快。
避坑规则：联合索引字段不超过3个、避免频繁更新字段建索引、避免低区分度字段建索引。
六、索引失效十大场景（全覆盖）
-
索引字段使用函数、运算（substr、date_format、加减乘除）；
-
隐式类型转换：字符串字段不加引号，自动转为数值，索引失效；
-
or 查询，前后字段未全部建立索引；
-
like 左模糊/全模糊（%xxx、%xxx%），只有右模糊 xxx% 走索引；
-
联合索引范围查询后置断裂：左侧等值、右侧范围，范围后字段失效；
-
使用 != <> not in not exists 不等值查询；
-
order by、group by 未遵循最左前缀，导致索引失效产生文件排序；
-
对索引字段做 is null / is not null 判断，低区分度直接失效；
-
单表数据量极小，MySQL 优化器认为全表扫描更快，主动放弃索引；
-
事务隔离级别、MVCC 快照机制导致索引匹配异常失效。
七、慢查询优化五步法
第一步：开启慢查询日志
配置 slow_query_log=1，设置 long_query_time 阈值（生产0.5s~1s），自动捕获线上耗时SQL。
第二步：explain 分析执行计划
重点观察 type、key、rows、Extra，定位全表扫描、索引失效、文件排序、临时表问题。
第三步：索引优化
修复失效索引、新建联合索引、优化索引顺序、优先使用覆盖索引，减少回表。
第四步：SQL语句优化
精简关联表、减少子查询、避免函数运算、优化where条件，规避索引失效场景。
第五步：验证与监控
对比优化前后耗时、QPS、数据库CPU，持续监控线上SQL性能，形成优化闭环。