B+树层高、页分裂合并、explain、排序临时表优化
Eddie一、B+ 树高度计算（高频）
1. 核心前提
MySQL InnoDB 一页默认大小 16KB
-
非叶子节点：只存索引键 + 子页指针，数据极小
-
叶子节点：存完整索引数据（主键+行数据/二级索引主键）
2. 通用计算公式
B+树节点存储数量 = 页大小 / 单条数据大小
树高度层数 = 非叶子节点层数 + 1（叶子层）
3. 行业标准估算（背诵版）
-
主键为 BIGINT（8字节）+ 指针6字节，单条非叶子数据约14字节
-
单页非叶子节点可存：
16KB / 14B ≈ 1170个索引条目 -
叶子节点一条数据约20字节，单页可存
16KB / 20B ≈ 800条数据
高度3层B+树存储上限：1170 * 1170 * 800 ≈ 10亿数据
4. 结论
日常业务数据表，B+树高度基本都是 2~3 层，查询磁盘IO仅2-3次，效率极高。
二、页分裂与页合并（InnoDB 核心机制）
1. 页分裂（新增/更新数据触发）
触发条件
InnoDB 数据页填充率达到15/16，页空间不足，无法写入新数据。
- InnoDB 预留了 1/16 空闲空间 做原地更新、小幅插入。
- 很多业务是更新原有行、少量追加，不用立刻分裂页；
留一点富余，能直接在当前页原地写，省去页分裂的 IO 开销。
- 等到塞满到 15/16，剩余空闲已经不够存放新行、也不够行更新扩容，没空间了，只能强制页分裂。
执行流程
-
申请一块新的空白数据页；
-
将原页后半部分数据迁移到新页；
-
在原页写入新数据；
-
更新父节点索引指针，关联新旧两个数据页。
弊端
消耗IO资源、产生索引碎片、降低查询性能。
规避方案
-
主键使用自增主键，数据顺序写入，几乎不会触发页分裂；
-
禁止使用 UUID、随机字符串当主键；
-
大批量插入数据时，有序批量导入。
-
自增主键：
新数据主键永远最大，永远往当前最后一个数据页末尾追加。
只有当最后一页真的塞满 15/16 了，才新开一个页，只是分页，不需要挪动老数据、不需要中间拆分。
→ 这叫页扩容，不是页分裂。
-
无序主键（UUID / 雪花随机 / 字符串）：
新主键随机，可能插到现有页中间位置。
插到中间 → 当前页空间不够 → 必须把后半截数据搬走拆分 → 触发真正页分裂，大量挪动数据、产生碎片。
2. 页合并（删除数据触发）
触发条件
删除数据后，相邻两个数据页的填充率均低于 1/2。
执行流程
-
将两个未满的数据页数据合并到同一个数据页；
-
释放空闲的数据页；
-
更新上层索引指针。
作用
回收磁盘空间、减少索引页数、提升索引查询效率。
三、explain 全字段核心详解（高频）
重点吃透：type、key、rows、Extra 四大核心字段
1. Type（查询类型，优先级从差到优）
-
ALL：全表扫描，最差，无可用索引，必须优化
-
index：全索引扫描，遍历整个索引树，仅优于全表扫描
-
range：范围查询（in、between、>、<、like 前缀匹配），基础合格
-
ref：非唯一索引等值匹配，日常最常用、性能优秀
-
eq_ref：唯一索引/主键等值匹配，每条数据一一对应，性能极佳
-
const：常量匹配，主键/唯一索引等值查询，系统最优级别
-
system：系统表查询，最高级别，业务几乎遇不到
业务标准：日常 SQL 至少保证 range/ref，核心接口必须 eq_ref/const，禁止 ALL/index
2. Key（本次查询真正使用的索引）
-
NULL：未使用任何索引
-
索引名：本次查询命中的有效索引
-
注意：possible_key 是可选索引，key 是实际生效索引，以 key 字段为准
3. Rows（扫描行数）
MySQL 优化器预估需要扫描的数据行数，数值越小性能越好。
核心意义：直接反映索引筛选能力，rows 过高代表索引筛选度差，存在优化空间。
4. Extra（额外信息，优化核心依据）
-
Using index：覆盖索引，无需回表，性能优秀（最优标识）
-
Using where：服务层过滤数据，索引未完全筛选数据
-
Using filesort：文件排序，严重性能问题，需要优化
-
Using temporary：创建临时表，严重性能问题，需要优化
-
Using join buffer：使用连接缓冲区，关联查询无索引
四、Using Filesort / Using Temporary 成因 + 全套优化方案
1. Using Filesort（文件排序）
成因
排序字段没有建立索引，MySQL 无法使用索引排序，只能在内存/磁盘中手动排序。
出现场景：单独 order by、多字段排序、排序字段非索引字段、索引失效后的排序
优化方案
-
给 order by 字段建立单列索引；
-
多字段排序建立联合索引，严格遵循 where 条件在前、order by 在后 的最左前缀原则；
-
避免 order by 字段参与函数运算、隐式类型转换，防止索引失效；
-
减少排序数据量，优先通过 where 条件过滤大量数据。
2. Using Temporary（临时表）
成因
分组、去重、关联查询无法通过索引完成，MySQL 需要创建临时表存储中间数据。
高频出现场景：group by、distinct、多表join、聚合查询
优化方案
-
为 where + group by + order by 建立联合索引，让筛选、分组、排序全程走索引；
-
避免对大字段、不确定字段进行 distinct 去重；
-
优先过滤数据，缩小分组范围，先 where 后 group by；
-
拆分复杂SQL，避免单条SQL多聚合、多关联导致临时表生成；
-
禁止在 group by、distinct 字段上使用函数。
3. 关键总结（重点）
Using filesort、Using temporary 属于硬性性能缺陷，生产环境绝对不允许出现。
核心优化思路：让筛选、分组、排序全部命中索引，避免MySQL进行额外的文件排序和临时表创建。