MySQL存储结构

理解 MySQL 索引之前，必须先理解 InnoDB 怎么组织数据。很多性能问题看起来是 SQL 问题，本质上是数据页、B+Tree、回表、页分裂、Buffer Pool 共同作用的结果。

一句话理解：

InnoDB 不是把数据随便一行一行堆在磁盘上，而是把数据组织成页，再用 B+Tree 把页连接起来，让查询尽量少读页。

为什么要学存储结构

现象	背后的存储结构原因
主键查询很快	聚簇索引的叶子节点直接保存整行数据
普通索引查询有时慢	二级索引找到主键后还要回表
范围查询适合 B+Tree	叶子节点有序并通过链表连接
随机 UUID 主键写入慢	容易插入到 B+Tree 中间，引发页分裂
`select *` 可能更慢	可能导致更多回表和更多页读取

如果不理解存储结构，很容易只背结论：主键递增、少用 select *、建联合索引。但你会不知道为什么这样做，也不知道例外情况怎么判断。

InnoDB 层级结构

mermaid

flowchart TD
    A["表空间 Tablespace<br/>表和索引的整体存储空间"] --> B["段 Segment<br/>按用途管理数据"]
    B --> C["区 Extent<br/>连续页的集合"]
    C --> D["页 Page<br/>默认 16KB，磁盘读写基本单位"]
    D --> E["行 Record<br/>真正的业务记录"]

页 Page

页是 InnoDB 读写磁盘的基本单位，默认通常是 16KB。即使你只查一行，底层也可能把这一行所在的整页加载到 Buffer Pool。

为什么不用“行”作为最小 IO 单位：

磁盘按块读写，一行一行读效率很低。
一页里通常有多行，读一页后可能命中后续查询。
B+Tree 的节点本身也以页为单位组织。

如果一页能放更多索引项，B+Tree 的高度就更低，查询需要的 IO 次数就更少。

行 Record

InnoDB 行里不只有业务字段，还会有隐藏字段。常见隐藏信息包括：

隐藏信息	作用
`trx_id`	最近修改这行的事务 ID，用于 MVCC 可见性判断
`roll_pointer`	指向 undo log 中的旧版本
隐藏 row_id	没有主键和唯一非空索引时，InnoDB 生成隐藏行 ID

这些隐藏字段解释了为什么 MVCC 能找到旧版本，也解释了为什么表最好自己设计主键。

B+Tree 为什么适合数据库

mermaid

flowchart TD
    A["根节点<br/>索引键 + 页指针"] --> B["内部节点<br/>索引键 + 页指针"]
    A --> C["内部节点<br/>索引键 + 页指针"]
    B --> D["叶子页 1<br/>有序记录"]
    B --> E["叶子页 2<br/>有序记录"]
    C --> F["叶子页 3<br/>有序记录"]
    C --> G["叶子页 4<br/>有序记录"]
    D <--> E
    E <--> F
    F <--> G

B+Tree 的关键优势：

特点	为什么有用
非叶子节点只保存 key 和指针	单页能放更多目录项，树更矮
叶子节点按 key 有序	范围查询、排序更高效
叶子节点之间有链表	找到范围起点后可以顺序扫描
查询路径稳定	大多数查询从根到叶子，IO 次数可控

为什么不用普通二叉树：

二叉树每个节点分叉少，数据量大时树会很高。
树越高，磁盘随机 IO 次数越多。
数据库更关心减少磁盘 IO，而不是单纯比较次数。

为什么不用 Hash 做所有索引：

Hash 适合等值查询，但不适合范围查询。
Hash 无序，无法天然支持 order by。
数据库业务里范围查询、排序、分页很常见。

聚簇索引

InnoDB 表的数据按主键组织。主键索引的叶子节点保存完整行数据，这种索引叫聚簇索引。

mermaid

flowchart TD
    A["主键 id=1001"] --> B["聚簇索引 B+Tree"]
    B --> C["叶子节点"]
    C --> D["整行数据<br/>id / name / status / created_at ..."]

一个表只能有一个聚簇索引，因为真实数据只能按一种顺序组织。

主键查询为什么快：

sql

select *
from user_profile
where id = 1001;

通过主键 B+Tree 找到叶子节点后，叶子节点就是整行数据，不需要再查一次。

二级索引和回表

普通索引也叫二级索引。二级索引的叶子节点通常保存“索引列 + 主键值”，不直接保存完整行。

mermaid

flowchart TD
    A["按普通索引 username 查询"] --> B["二级索引叶子节点<br/>username + id"]
    B --> C["拿到主键 id"]
    C --> D["回到聚簇索引"]
    D --> E["读取完整行数据"]

这个“再回到主键索引查整行”的过程叫回表。

准备表：

sql

create table user_profile (
  id bigint primary key auto_increment,
  username varchar(50) not null,
  email varchar(100) not null,
  status tinyint not null,
  created_at datetime not null,
  key idx_username (username)
) engine = InnoDB default charset = utf8mb4;

可能回表：

sql

explain
select email, status
from user_profile
where username = 'tom';

因为 idx_username 只有 username 和主键 id，查询 email、status 时需要回表。

覆盖索引

如果查询需要的字段都在二级索引里，就不需要回表，这叫覆盖索引。

sql

alter table user_profile
add index idx_username_status (username, status);

explain
select username, status
from user_profile
where username = 'tom';

这时查询字段 username、status 都在 idx_username_status 中，执行计划的 Extra 可能出现 Using index。

覆盖索引为什么快：

少了一次回表。
二级索引页通常比完整数据页更小。
可以减少随机 IO。

但不要为了覆盖所有查询而无限加索引。每个索引都要占磁盘，写入和更新时也要维护。

页分裂是什么

B+Tree 的叶子页是有容量限制的。如果一个页满了，又要插入一个应该放在这个页中间的 key，就可能分裂成两个页。

mermaid

flowchart TD
    A["叶子页已满"] --> B["插入新记录"]
    B --> C{"新 key 是否追加到末尾"}
    C -- "是" --> D["尽量顺序追加"]
    C -- "否" --> E["移动部分记录到新页"]
    E --> F["更新父节点指针"]
    F --> G["产生页分裂和碎片"]

为什么推荐递增主键：

主键类型	写入特点
自增 ID	新记录大多追加到 B+Tree 右侧，页分裂较少
随机 UUID	新记录可能插入任意叶子页，页分裂更多
业务字符串	主键更长，二级索引也会膨胀

这不是说 UUID 一定不能用，而是要理解代价。分布式系统中如果需要全局唯一 ID，可以考虑雪花算法等趋势递增 ID。

Buffer Pool 和页读取

InnoDB 会把热点数据页缓存到 Buffer Pool。查询时不是每次都直接读磁盘，而是先看内存里有没有目标页。

mermaid

flowchart TD
    A["SQL 需要读取某个页"] --> B{"Buffer Pool 中是否存在"}
    B -- "存在" --> C["直接从内存读取"]
    B -- "不存在" --> D["从磁盘加载页"]
    D --> E["放入 Buffer Pool"]
    E --> C

这解释了两个现象：

同一条 SQL 第一次慢，后面可能变快，因为数据页被缓存了。
大量全表扫描可能挤掉热点页，让其他查询变慢。

存储结构如何影响 SQL 写法

SQL 写法	背后原因	建议
`select *`	可能读取更多列，导致回表和更多页读取	只查需要字段
前缀模糊 `like '%abc'`	B+Tree 无法按左侧有序查找	尽量避免，或使用全文索引/搜索引擎
随机主键	更容易页分裂	优先使用递增或趋势递增主键
返回大量行	即使命中索引，也要扫描大量叶子页	分页、限制范围、归档冷数据
过多二级索引	每次写入要维护多个 B+Tree	根据真实查询保留必要索引

SQL Demo：观察回表和覆盖索引

准备表：

sql

create table article (
  id bigint primary key auto_increment,
  user_id bigint not null,
  title varchar(100) not null,
  content text not null,
  created_at datetime not null,
  key idx_user_created (user_id, created_at)
) engine = InnoDB default charset = utf8mb4;

可能回表的查询：

sql

explain
select title, content
from article
where user_id = 1001
order by created_at desc
limit 10;

原因：idx_user_created 中没有 title 和 content，找到主键后还要回表。

覆盖索引查询：

sql

explain
select user_id, created_at
from article
where user_id = 1001
order by created_at desc
limit 10;

原因：查询字段都在索引中，可能减少回表。

小结

InnoDB 以页为基本读写单位，不是按行单独读磁盘。
B+Tree 能降低树高，并天然支持范围查询和排序。
聚簇索引叶子节点保存整行数据，主键查询路径最短。
二级索引叶子节点保存主键值，查询非索引字段可能回表。
覆盖索引能减少回表，但索引不是越多越好。
递增主键能减少页分裂，随机主键写入成本更高。

更多索引使用原则可以继续看索引知识点。

JDK 8/11/17/21 LTS演进

线程池

MySQL存储结构 ​

为什么要学存储结构 ​

InnoDB 层级结构 ​

页 Page ​

行 Record ​

B+Tree 为什么适合数据库 ​

聚簇索引 ​

二级索引和回表 ​

覆盖索引 ​

页分裂是什么 ​

Buffer Pool 和页读取 ​

存储结构如何影响 SQL 写法 ​

SQL Demo：观察回表和覆盖索引 ​

小结 ​

MySQL存储结构

为什么要学存储结构

InnoDB 层级结构

页 Page

行 Record

B+Tree 为什么适合数据库

聚簇索引

二级索引和回表

覆盖索引

页分裂是什么

Buffer Pool 和页读取

存储结构如何影响 SQL 写法

SQL Demo：观察回表和覆盖索引

小结