大家好，我是程序员强子。

日常开发中，我们很少关注 MySQL 的底层细节：

• InnoDB 是如何存储数据的？

• Buffer Pool 和 Change Buffer 又是如何优化性能的？

• SQL 执行要经过哪些环节？

今天，强子就带着大家走进 MySQL 的底层世界 ~

数据存储机制

特点

InnoDB 的存储结构按 从大到小 分为 5 个层级

表空间（Tablespace）→ 段（Segment）→ 区（Extent）→ 页（Page）→ 行（Row）

核心就上层负责 逻辑隔离与资源管理，下层负责 物理存储与 IO 交互

比如表空间隔离不同表的数据，页则作为内存与磁盘的交互单元

最终实现 按需存储、高效读写

接下来，跟着强子的脚步，来拆解每个层次~

表空间

表空间（Tablespace）,是所有数据（表数据、索引、元数据等）的 总仓库

分为两类核心类型：

• 系统表空间（System Tablespace）MySQL 默认的共享表空间，对应磁盘文件 ibdata1（可多文件）存储 InnoDB 数据字典、回滚日志（Undo Log）、临时表数据等核心元数据；

• 独立表空间（File-per-table Tablespace）MySQL 5.6 及以上默认启用（通过 innodb_file_per_table=ON 控制）每张表对应一个独立的 ibd 文件（如 user.ibd），表数据和索引单独存储

这样设计有什么好处？

• 数据隔离：独立表空间下，单表的增删改查、备份迁移不影响其他表；

• 元数据管理：系统表空间统一维护 InnoDB 的 “数据字典”（记录表结构、列类型等元信息）

有没有实战案例？

100G 大表 order 需要迁移，对比 独立表空间 和 共享表空间：

• 若用独立表空间：直接拷贝 order.ibd 文件，配合 ALTER TABLE order DISCARD TABLESPACE/IMPORT TABLESPACE 命令，10 分钟内可完成迁移；

• 若用系统表空间：需备份整个 ibdata1 文件（可能几百 G），迁移效率极低，且恢复时易影响其他表。

所以，生产环境必须启用独立表空间

还有，ibdata1 文件 有扩容后无法收缩的特点！

• 系统表空间的 ibdata1 文件默认 自动扩容，但扩容后无法收缩

• 即使删除数据，空间也不会释放

• 若 ibdata1 超过 50G，会导致 MySQL 启动变慢、磁盘 IO 负载升高

• 此时需通过 “导出数据→重建实例→导入数据” 的方式收缩表空间

段

段（Segment）用于区分 数据 与 索引 的存储

段类型有主要有两类，还有一些其他的类型：

• 数据段（Data Segment）存储表的真实业务数据；本质是聚簇索引的叶子节点段聚簇索引的叶子节点直接包含完整行数据，数据段就是聚簇索引叶子节点的存储空间

• 索引段（Index Segment）存储索引的非叶子节点（以及二级索引的叶子节点），用于实现索引的快速查找逻辑二级索引的叶子节点也存于索引段，这是因为二级索引叶子节点不存实际数据，只存主键值

还有其他类型的段：

• 回滚段 InnoDB 中专门存储 Undo Log（撤销日志） 的段，默认位于共享表空间（ibdata1）也可通过配置 innodb_undo_tablespaces 设置为独立表空间每个回滚段由多个 Undo 页组成

• 临时段是 InnoDB 为临时表、临时数据操作分配的段，存储于临时表空间（ibtmp1）,独立于普通表空间，仅用于存放临时数据显式临时表存储: 用户通过 CREATE TEMPORARY TABLE 创建的临时表隐式临时数据处理: SQL 执行过程中产生的临时数据（如 ORDER BY 排序、GROUP BY 分组、DISTINCT 去重、子查询等），会先存入临时段，处理完成后销毁

• 缓冲段专门存储 Change Buffer（插入缓冲） 数据

段没有固定大小，会随数据量动态扩展，且扩展的最小单位是 “区”

来来来，详细了解一下 区

区

区（Extent）是 InnoDB 中连续页的集合，是段扩展的 最小单位，默认配置下：

• 区大小固定为 1MB；

• 因 InnoDB 默认页大小为 16KB，故 1 个区包含 1MB / 16KB = 64 个连续页

实战案例有哪些？

电商项目的 product 表（存储商品信息，高频查询库存字段），当某类商品（如 “手机”）成为热点时：

• 为 product 表的数据段分配多个连续区，热点商品的数据集中存储在这些区中；

• 预读机制（Read Ahead）会一次性将整个区（1MB）的数据加载到 Buffer Pool 中；

• 后续查询该类商品时，直接从内存读取，无需频繁**访问磁盘 **

• InnoDB 对区分配 有策略： 区翻倍分配

什么是 区翻倍分配 策略？

• 表刚创建时，数据段仅分配 1 个区；

• 当数据量达到区容量的 50% 时，下次分配 2 个区；

• 再满则分配 4 个区、8 个区…… 以此类推，直到单次分配 64 个区后稳定

这种策略能避免 小数据量时频繁分配区 的性能开销，同时适配大数据量的扩展需求

页

页（Page）InnoDB 磁盘操作的最小单位

无论读还是写，都必须整页操作

默认页大小为 16KB,可通过 innodb_page_size 参数,修改为 4KB、8KB、32KB 等

核心页类型包括：

• 数据页（Data Page）：存储表数据，是 B + 树的 叶子节点

• 索引页（Index Page）：存储索引条目，是 B + 树的 非叶子节点

• 日志页（Log Page）：存储 redo log、undo log 等日志数据

实战案例是什么？

我们平常说的避免全表扫描，本质就是避免 加载过多数据页

• 假设 user 表有 100 万行数据，每行约 1KB，1 个数据页可存 16 行，全表扫描需加载 100 万 / 16 ≈ 62500 个页，需发起数万次磁盘 IO；

• 若走主键查询（select * from user where id=1001）：B + 树索引只需加载 3 个页（根节点→子节点→叶子节点数据页），仅 3 次 IO，效率相差万倍

行

行是 InnoDB 存储的最小逻辑单位，对应数据表中的一条记录

InnoDB 支持多种行格式，核心格式有

• Compact：MySQL 5.6 默认格式，对短字段紧凑存储，长字段（超过一定长度）会将部分数据存到 “溢出页”；

• Dynamic：MySQL 5.7 及以上默认格式，长字段（如 TEXT、BLOB）的所有数据都存到 “溢出页”，数据页仅保留 20 字节的指针

溢出页是什么？

专门用于存放 超长字段（如 TEXT、BLOB、超长 VARCHAR）数据的独立页面

原数据页里只保留 20 字节的指针，这个指针相当于 地址标签，记录着溢出页的位置

有没有可能一条数据太大导致占用超过 16k(超过一页)？

InnoDB 有硬性规定：单行数据的实际存储大小不能超过页大小的一半（约 8KB）

目的是保证一个数据页至少能容纳两行数据，避免存储结构崩溃

如果一行数据包含多个 TEXT/BLOB、超长 VARCHAR，确实会触发 行溢出

但 InnoDB 通过之前提到的溢出页机制+行拆分逻辑来解决。

在 段中 提到 Change Buffer ，和 区中提到 Buffer Pool 到底 是啥来的？来跟强子仔细研究一下~~

Change Buffer

定义

内存中一块临时存储区域

专门缓存对非唯一二级索引的 插入 / 更新 / 删除 操作

不直接刷盘，等合适时机一次性合并到磁盘索引页

作用

• 把修改非唯一二级索引时的零散随机 I/O，转化为批量顺序 I/O，大幅提升写入性能

• 比如批量插入数据、高频更新非唯一索引列时效果明显

解决的核心问题

二级索引的索引页在磁盘上分布零散（非顺序）

若修改时索引页不在内存（Buffer Pool），需频繁从磁盘读取索引页（随机 I/O 效率极低）

Change Buffer 直接跳过 频繁读磁盘 步骤，先缓存修改操作，避免了这个痛点

限制

• 只适用于非唯一二级索引，唯一索引需实时校验唯一性，不能缓冲；

• 聚簇索引、已在内存的索引页，不适用（聚簇索引直接操作数据页，内存索引页可直接修改）。

Buffer Pool

本质是一块从内存中划分出的缓存空间

专门用来缓存磁盘上的数据页（表数据）和索引页，以及索引变更、行数据等相关信息

核心目标是减少磁盘 I/O、提升读写性能

作用

读缓存

查询数据时，先从 Buffer Pool 找数据页 / 索引页，找到就直接返回（“缓存命中”），不用读磁盘；

没找到才从磁盘加载到 Buffer Pool，后续再查就走内存，速度提升成百上千倍。

写缓存

修改数据时，先更新 Buffer Pool 里的缓存页（标记为 “脏页”），

不立即刷到磁盘，而是由后台线程批量异步刷盘

避免频繁磁盘写，提升写入效率

数据结构

Buffer Pool 按 页（和磁盘数据页大小一致，默认 16KB）划分

每个缓存页对应磁盘上的一个数据页 / 索引页

用 最近最少使用 LRU 算法管理缓存页

把常用的页留在链表前端，不常用的页在末端

内存满时淘汰末端页，保证缓存命中率

机制

被修改但未刷到磁盘的缓存页叫 脏页

由后台线程在空闲时或满足阈值时批量刷到磁盘

SQL 各子句的执行顺序

执行顺序

FROM/JOIN

• 确定数据来源：加载 FROM 后的表，处理 JOIN（内连接 / 外连接等）关联的表

• 生成 基础数据集，包含所有关联后的行

WHERE

对第一步的基础数据集做行级过滤：只保留满足 WHERE 条件的行

此时还未分组，不能用聚合函数如 SUM ()

GROUP BY

按指定字段对过滤后的行分组：相同字段值的行被合并为一个组

后续聚合函数（如 COUNT、AVG）基于组计算

HAVING

对分组后的结果做组级过滤：只保留满足 HAVING 条件的组

可以用聚合函数，比如 HAVING AVG(salary) > 5000

SELECT

从前面处理后的结果中选择要显示的列

包括字段、聚合函数计算结果、别名等

DISTINCT

对 SELECT 选出的结果去重，去除重复行

ORDER BY

按指定列 / 表达式对结果集排序

此时可以用 SELECT 里的别名，因为 SELECT 已经执行

LIMIT/OFFSET

最后限制结果集的行数

• 如 LIMIT 10 取前 10 行

• 或跳过指定行数（OFFSET 5）

验证

SELECT dept_id, AVG(salary) AS avg_salFROM employeeWHERE dept_id > 10GROUP BY dept_idHAVING avg_sal > 5000ORDER BY avg_sal DESCLIMIT 5;

执行顺序对应：

• 先从 employee 表加载数据（FROM）；

• 过滤出 dept_id > 10 的行（WHERE）；

• 按 dept_id 分组（GROUP BY）；

• 保留平均工资 > 5000 的组（HAVING）；

• 选择 dept_id 和平均工资（SELECT，别名 avg_sal 生效）；

• 按 avg_sal 降序排序（ORDER BY）；

• 取前 5 组（LIMIT）

总结

今天我们了解了 Mysql 是怎么存储数据的，也知道了 sql 子句执行流程是怎么样的，顺便还了解了一下 Mysql 的缓存机制~

• InnoDB 靠表空间 - 段 - 区 - 页架构存储数据，搭配数据段、索引段、溢出页等解决存储难题；

• SQL 按 “FROM→WHERE→GROUP BY→SELECT→ORDER BY→LIMIT” 的固定顺序执行。

• Buffer Pool 缓存数据和索引减磁盘 I/O，Change Buffer 优化非唯一二级索引写入；

带着底层视角开发，解决问题会更精准~

熟练度刷不停，知识点吃透稳，下期接着练～