MySQL 的核心知识

专门开一个文章来讲一下 MySQL 的知识。

存储结构

逻辑存储结构

从逻辑层面看，MySQL 的数据组织如下：

数据库 (Database)：数据库的逻辑容器，一个数据库可以包含多个表
表 (Table)：由行和列组成的二维结构，同一类数据的集合
行 (Row)：表中的一个记录，代表一条完整的数据
字段 (Column)：表中的列，定义数据的属性和类型

┌─────────────────────────────────────┐
│           Database                  │
├─────────────────────────────────────┤
│  ┌─────────────────────────────┐   │
│  │         Table: users        │   │
│  ├────┬───────┬──────┬────────┤   │
│  │ id │ name  │ age  │ email  │   │
│  ├────┼───────┼──────┼────────┤   │
│  │ 1  │ Tom   │ 25   │ a@b..  │ ← Row (行)
│  │ 2  │ Alice │ 30   │ c@d..  │   │
│  └────┴───────┴──────┴────────┘   │
│        ↑              ↑            │
│     Column       Column            │
│    (字段)        (字段)            │
└─────────────────────────────────────┘

物理存储结构

尽管 MySQL 有逻辑上的结构，数据库，表，行，字段。但是具体在磁盘中怎么存储呢？

以 InnoDB 物理存储结构为例，整个层级关系如下：

Tablespace (表空间)
    │
    ├── Segment (段) ── 逻辑分组
    │     │
    │     ├── Leaf Node Segment (叶子节点段)
    │     │     │
    │     │     └── Extent (区) ── 64 × Page
    │     │           │
    │     │           └── Page (页) ── 16KB，最小单位
    │     │
    │     └── Non-Leaf Node Segment (非叶子节点段)
    │           │
    │           └── Extent ── Page
    │
    └── Segment (另一个索引的段)

页 page (默认 16 KB)

最小的存储单位。所有数据、索引最终都存储在页中。默认大小 16 KB。

页的内部结构：

┌─────────────────────────────────────────────────────────┐
│                    Page (16KB)                          │
├─────────────────────────────────────────────────────────┤
│ Page Header (38 bytes)                                 │
│   - 页面序号、所属表空间                               │
│   - 上一个/下一个页面指针                              │
│   - 空闲空间起始位置                                   │
│   - 已用空间、记录数                                   │
│   - 删除链表指针                                       │
├─────────────────────────────────────────────────────────┤
│ Infimum Record (最小记录)                              │
│   - 虚拟记录，表示页面中最小值                        │
├─────────────────────────────────────────────────────────┤
│ Supremum Record (最大记录)                             │
│   - 虚拟记录，表示页面中最大值                        │
├─────────────────────────────────────────────────────────┤
│ User Records (用户记录)                                │
│   - 实际存放的行数据                                   │
│   - 链表形式存储（通过 next_record 指针串联）        │
├─────────────────────────────────────────────────────────┤
│ Free Space (空闲空间)                                  │
│   - 未使用的空间，新记录在此分配                       │
├─────────────────────────────────────────────────────────┤
│ Page Directory (页目录)                                │
│   - 槽数组，指向各组最后一条记录                      │
│   - 用于二分查找加速                                   │
├─────────────────────────────────────────────────────────┤
│ Page Trailer (8 bytes)                                 │
│   - 页面校验和                                         │
│   - 页面LSN                                           │
└─────────────────────────────────────────────────────────┘

Page Header (页头) - 38 字节

存储页面的管理信息：

字段	大小	含义
SPACE	4 bytes	表空间 ID
PAGE_NO	4 bytes	页号
PAGE_N_DIR_SLOTS	2 bytes	页目录槽数量
PAGE_HEAP_TOP	2 bytes	空闲空间起始位置
PAGE_N_HEAP	2 bytes	堆中记录数
PAGE_FREE	2 bytes	第一个空闲记录指针
PAGE_GARBAGE	2 bytes	已删除记录占用的空间
PAGE_MAX_TRX_ID	8 bytes	最大事务 ID
PAGE_LEVEL	2 bytes	B+树层级（0=叶子）
PAGE_INDEX_ID	8 bytes	所属索引 ID

Infimum + Supremum Records（虚拟记录）

这是两个虚拟记录，不是真正的用户数据：

┌─────────────────────────────────────────────────────────┐
│ Infimum (最小记录)                                      │
│   - 固定 13 bytes                                       │
│   - next_record → 指向页面中第一条真实记录              │
│   - 始终是页面中"最小"的记录                           │
├─────────────────────────────────────────────────────────┤
│ User Record 1: id=1, name="Tom" ...                     │
│ User Record 2: id=5, name="Alice" ...                  │
│ User Record 3: id=8, name="Bob" ...                    │
├─────────────────────────────────────────────────────────┤
│ Supremum (最大记录)                                     │
│   - 固定 13 bytes                                       │
│   - next_record = 0 (没有后续记录)                     │
│   - 始终是页面中"最大"的记录                           │
└─────────────────────────────────────────────────────────┘

作用：

作为页内记录的边界标记
所有用户记录都夹在 Infimum 和 Supremum 之间
便于二分查找

Page Directory (页目录)

作用： 用于二分查找，快速定位记录

┌─────────────────────────────────────────────────────────┐
│ 记录在页中的存储顺序（链表）：                          │
│                                                         │
│ Infimum → Record1 → Record2 → Record3 → ... → Supremum│
│                                                         │
│  每 4-8 条记录为一组，组内最后一条记录地址存入页目录    │
└─────────────────────────────────────────────────────────┘

页目录结构：
┌─────────────────────────────────────────────────────────┐
│ Slot 0 │ Slot 1 │ Slot 2 │ Slot 3 │ ... │ Slot N     │
│ 0x??   │ 0x??   │ 0x??   │ 0x??   │     │ 0x??       │
│ 指向   │ 指向   │ 指向   │ 指向   │     │ 指向       │
│ Rec0   │ Rec3   │ Rec6   │ Rec9   │     │ Supremum   │
└─────────────────────────────────────────────────────────┘

Page Trailer (页尾) - 8 字节

┌─────────────────────────────────────────────────────────┐
│ Page Trailer (8 bytes)                                 │
├─────────────────────────────────────────────────────────┤
│ 4 bytes: 校验和 (Checksum)                             │
│ 4 bytes: LSN (Log Sequence Number)                    │
└─────────────────────────────────────────────────────────┘

字段	作用
Checksum	页完整性校验，检测页是否损坏
LSN	页面最后修改的日志序列号

页内记录存储示意：

┌─────────────────────────────────────────────────────────┐
│ 真实记录示例 (id=5, name="Tom", age=25, email="...")      │
├─────────────────────────────────────────────────────────┤
│ [record header - 5 bytes]                               │
│   - record_type: 0 (普通记录) / 1 (B+树节点)              │
│   - next_record: 下一条记录的偏移量                        │
│   - record_length                                       │
├─────────────────────────────────────────────────────────┤
│ [列数据]                                                 │
│   name: 1 byte(长度) + 3 bytes("Tom")                    │
│   email: 1 byte(长度) + 12 bytes                         │
│   age: 4 bytes (25)                                     │
│   id: 4 bytes (5)                                       │
├─────────────────────────────────────────────────────────┤
│ [NULL 标志位]                                            │
└─────────────────────────────────────────────────────────┘

在 MySQL 中，根据实际的使用情况分为多种类型：

Data Page 数据页，存储行数据
Index Page B+树索引页
Undo Page undo 日志页
System Page 系统页
Blob Page（存储大数据，一页存不下的情况）

区 extent

物理连续的单位，由 64 个页组成，大小为 1MB。

┌───────────────────────────────────┐
│         Extent (1MB)              │
├────────┬────────┬────────┬────────┤
│ Page 0 │ Page 1 │  ...   │ Page 63│
│ 16KB   │ 16KB   │        │  16KB  │
└────────┴────────┴────────┴────────┘

特点：

区是物理连续的 64 个页
分配空间时的最小单位
保证磁盘 I/O 效率（顺序读写）

段 segment

逻辑概念，一个索引对应两个段：

Leaf Node Segment：叶子节点段，存储实际数据
Non-Leaf Node Segment：非叶子节点段，存储索引键值和指针

CREATE TABLE users (
    id INT PRIMARY KEY,      -- 主键索引
    age INT,
    INDEX idx_age (age),     -- 普通索引
) ENGINE=InnoDB;

表空间分布：
├── Segment: 主键索引
│     ├── Leaf Segment → 存储完整行数据
│     └── Non-Leaf Segment → B+树结构
│
└── Segment: idx_age
      ├── Leaf Segment → (age, id指针)
      └── Non-Leaf Segment → B+树结构

表空间 tablespace

表空间是一个逻辑概念，它是 InnoDB 存储引擎管理数据的逻辑容器。

表空间包含什么：

内容	说明
数据	表的行数据（叶子节点）
索引	索引的 B+树结构（非叶子节点）
元数据	表结构定义（主要在系统表空间）
事务信息	Undo log、Change buffer 等

类比理解：

表空间 类似于 操作系统的"文件系统"

操作系统：
  文件系统 → 目录 → 文件 → 数据块(4KB)

InnoDB：
  表空间   → 段    → 页    → 数据(16KB)

表空间 vs 物理文件：

表空间是逻辑概念：从 InnoDB 角度看到的是一个逻辑存储单元
物理上对应文件：通过具体文件来落盘

InnoDB 视角（逻辑）          操作系统视角（物理）
┌─────────────────┐          ┌─────────────────┐
│  表空间 A        │          │  users.ibd      │  ← 独立表空间文件
│  (包含表+索引)    │  ←─────→ │  (100MB)        │
└─────────────────┘          └─────────────────┘

┌─────────────────┐          ┌─────────────────┐
│  系统表空间       │          │  ibdata1        │  ← 系统表空间文件
│  (系统元数据)     │  ←─────→ │  (可配置大小)   │
└─────────────────┘          └─────────────────┘

表空间内部结构：

以一个独立的表空间 users.ibd 为例：

┌─────────────────────────────────────────────────────────┐
│ users.ibd 表空间                                        │
├─────────────────────────────────────────────────────────┤
│ FSP Header (表空间头)                                    │
│   - 空闲页数量、已分配页数量                                │
│   - 区描述符数组起始位置                                   │
├─────────────────────────────────────────────────────────┤
│ XDES (区描述信息)                                         │
│   - 每个区的状态 (FREE/FULL/碎片)                          │
│   - 属于哪个 Segment                                      │
├─────────────────────────────────────────────────────────┤
│ INODE (段描述信息)                                       │
│   - 每个 Segment 的信息                                  │
│   - 指向的 Extent 列表                                  │
├─────────────────────────────────────────────────────────┤
│ 数据页/索引页                                           │
│   - 主键索引 B+树的节点                                 │
│   - 二级索引 B+树的节点                                 │
└─────────────────────────────────────────────────────────┘

所有信息都存储在页里

无论是用户数据、管理信息还是索引，最终都存放在页中：

表空间内部实际上就是一堆页：

┌─────────────────────────────────────────────────────────┐
│ 表空间 (users.ibd)                                      │
├─────────────────────────────────────────────────────────┤
│ Page 0: FSP Header + XDES (表空间和区的管理信息)       │
│ Page 1: INODE (段的管理信息)                           │
│ Page 2: INODE                                          │
│ Page 3: 主键索引 Root (B+树根节点)                      │
│ Page 4: 主键索引非叶子节点                              │
│ Page 5-10: 主键索引叶子节点 (存实际行数据)              │
│ Page 11: idx_age 索引 Root                             │
│ Page 12-15: idx_age 索引叶子节点                       │
│ ...                                                    │
└─────────────────────────────────────────────────────────┘

特殊页类型：

页类型	存储内容
FSP_HDR	表空间头信息 + 区描述数组
XDES	区的描述信息（属于哪个Segment）
INODE	段的描述信息（指向哪些区）
INDEX	索引节点（数据/索引）

页是 InnoDB 的最小存储单位，无论是：

用户数据（行）
索引数据（B+树节点）
管理数据（FSP、XDES、INODE）

全部都存放在页里面，页再组成区，区再组成段，段组成表空间。

表空间的类型：

类型	文件	说明
System Tablespace	ibdata1	系统表空间，存储 undo、change buffer 等
File-Per-Tablespace	db_name/table.ibd	独立表空间（MySQL 5.6.6+默认）
Undo Tablespace	undo_001, undo_002	独立 undo 表空间
Temporary Tablespace	ibtmp1	临时表空间

具体示例：一个表和一个索引是怎么存的

以示例表为例：

CREATE TABLE users (
    id INT PRIMARY KEY,      -- 主键索引
    name VARCHAR(50),
    age INT,
    email VARCHAR(100),
    INDEX idx_age (age),     -- 普通索引
) ENGINE=InnoDB;

主键索引的物理存储：

                    [B+树索引结构]

                      ┌─────────┐
                      │  Root   │  ← 非叶子节点（索引页）
                      │  Page   │
                      └────┬────┘
                           │
              ┌────────────┼────────────┐
         ┌────▼────┐  ┌────▼────┐  ┌────▼────┐
         │  Page   │  │  Page   │  │  Page   │
         └────┬────┘  └────┬────┘  └────┬────┘
              │            │            │
       ┌──────┴──────┐    ...    ┌──────┴──────┐
       │  Leaf Pages │           │  Leaf Pages │ ← 存储完整行数据
       └─────────────┘           └─────────────┘

普通索引 (idx_age) 的物理存储：

Leaf Page 内容 (索引页):
┌─────────────────────────────────────┐
│ age=18, id=15   │ 主键id=15 的指针   │
│ age=20, id=3    │ 主键id=3 的指针    │
│ age=22, id=88   │ 主键id=88 的指针   │
│ age=25, id=5    │ 主键id=5 的指针    │
└─────────────────────────────────────┘
        ↓ 跟随指针
┌─────────────────────────────────────┐
│ 数据页: 完整行数据                     │
│ id=5, name="Tom", age=25, email=... │
└─────────────────────────────────────┘

关键点：

主键索引叶子节点：存储完整行数据
二级索引叶子节点：存储索引列 + 主键值
非叶子节点：存储索引列范围 + 子节点指针
每个索引都有独立的段（叶子段 + 非叶子段）

表结构信息（元数据）存储在系统表空间（mysql.tables 等），不在表数据文件中。

行格式

行格式是指记录（行数据）在页中的存储方式。

Compact（紧凑格式）

记录结构：
┌─────────────────────────────────────────────────────────┐
│ 变长字段长度数组    │ NULL 标志位 │ 记录头 │ 列数据           │
└─────────────────────────────────────────────────────────┘
        ↓                    ↓       ↓
    偏移量数组          1 byte       5 bytes  实际数据

特点：

变长字段（VARCHAR）长度放在记录头
NULL 值不占空间，只在 NULL 标志位标记
第一个 BIT 位的值决定行格式

Dynamic（动态格式）

与 Compact 的最大区别：变长字段的存储方式

Compact 格式：
┌─────────────────────────────────────┐
│ VARCHAR(5000) │ 超过 768 字节的部分 │
│ 前 768 字节   │ 存到 Off-page      │
└─────────────────────────────────────┘

Dynamic 格式：
┌─────────────────────────────────────┐
│ VARCHAR(5000) │ 只存 20 字节的指针  │
│ (整个字段)    │ 指向溢出页          │
└─────────────────────────────────────┘

优点：

避免行碎片化
溢出数据存储更高效
5.7+ 默认格式

Compressed（压缩格式）

特点：
- 对数据进行压缩存储
- 页面级别压缩（16KB → 压缩后写入磁盘）
- 读取时自动解压

优点： 节省磁盘空间

缺点： CPU 开销（压缩/解压）

变长字段的溢出存储

当 VARCHAR/TEXT/BLOB 等大字段超过 768 字节时：

页结构示意：
┌─────────────────────────────────────────────────────────┐
│ 主键列 │ age │ name (前 768 字节) │ 溢出页指针             │
└─────────────────────────────────────────────────────────┘
                                           ↓
                                  ┌──────────────────┐
                                  │ Off-page 溢出页   │
                                  │ name (剩余部分)   │
                                  └──────────────────┘

768 字节的由来：

变长字段在 Compact/Dynamic 中最多保留 768 字节在行内
超过部分存储到独立的溢出页

配置行格式

-- 查看当前行格式
SHOW VARIABLES LIKE 'innodb_default_row_format';

-- 创建表指定行格式
CREATE TABLE t1 (
    id INT
) ROW_FORMAT=DYNAMIC;

-- 修改行格式
ALTER TABLE t1 ROW_FORMAT=COMPRESSED;

增删数据时物理结构的处理

插入数据流程

假设要插入: id=5, name="Tom", age=25

步骤：
1. 定位到应该插入的页（通过索引找到叶子节点）
2. 检查页的 Free Space 是否足够
3. 在 Free Space 中分配空间
4. 将记录写入
5. 更新 Page Directory
6. 更新 Page Header（记录数、已用空间等）

插入后页的状态：

插入前：
┌─────────────────────────────────────┐
│ Page Header                          │
├─────────────────────────────────────┤
│ User Records: [id=1] [id=3]         │
├─────────────────────────────────────┤
│ Free Space (15000 bytes)            │  ← 大部分空闲
└─────────────────────────────────────┘

插入后：
┌─────────────────────────────────────┐
│ Page Header (n_heap=4)              │
├─────────────────────────────────────┤
│ Infimum → [id=1] → [id=3] → [id=5]│  ← 链表形式
│                          ↑ new      │
├─────────────────────────────────────┤
│ Free Space (剩余)                   │
└─────────────────────────────────────┘

删除数据流程

删除不是真正删除，而是标记：

删除 id=3 的记录：

删除前：
Infimum → [id=1] → [id=3] → [id=5] → Supremum

删除后（标记为删除）：
Infimum → [id=1] ─X─→ [id=3] ─X─→ [id=5] → Supremum
               ↓ 标记为 deleted
               → 放入 Page Free List

删除记录的结构变化：

┌─────────────────────────────────────────────────────────┐
│ Page Header                                            │
│   - PAGE_FREE: 指向第一个空闲记录                     │
│   - PAGE_GARBAGE: 已删除记录占用的总空间               │
├─────────────────────────────────────────────────────────┤
│ User Records:                                           │
│   - 已删除的记录仍占用物理空间                         │
│   - 记录头的 deleted_flag = 1                          │
│   - next_record 指向下一个记录                        │
├─────────────────────────────────────────────────────────┤
│ Free Space (记录被标记删除后，空间计入空闲空间池)       │
└─────────────────────────────────────────────────────────┘

为什么不在删除时立即回收空间？

减少磁盘 I/O
保持页内记录的相对位置
后续插入可以复用这些空间

空闲记录链表（Free List）

PAGE_FREE 指向第一个空闲记录，如何找到第二个？

每个被删除的记录的 next_record 指针仍然有效，通过指针串联成链表：

假设删除了两条不相邻的记录：id=2 和 id=5

删除前：
┌─────────────────────────────────────────────────────────┐
│ Page Header: PAGE_FREE = NULL (没有空闲记录)           │
├─────────────────────────────────────────────────────────┤
│ Infimum → [id=1] → [id=2] → [id=3] → [id=5] → Supremum│
└─────────────────────────────────────────────────────────┘

删除后（id=2 和 id=5 被标记删除）：
┌─────────────────────────────────────────────────────────┐
│ Page Header: PAGE_FREE = 0x?? (指向 id=2 的位置)      │
├─────────────────────────────────────────────────────────┤
│ Infimum → [id=1] ─X─→ [id=2] ─X─→ [id=3] ─X─→ [id=5] │
│                     ↑             ↑                     │
│                     ↓             ↓                     │
│              next_record    next_record                │
│                 │               │                       │
│                 └───────┬───────┘                       │
│                         ↓                               │
│              空闲记录链表 (Free List)                   │
│              id=2 → id=5 → NULL                        │
└─────────────────────────────────────────────────────────┘

空闲链表的工作原理：

删除 id=2:
- id=2 记录的 next_record = 0x?? (原来指向 id=3)
- 修改为指向 id=5 的位置
- PAGE_FREE 指向 id=2

删除 id=5:
- id=5 记录的 next_record = NULL (原指向 Supremum)
- 修改为指向 NULL (链表尾部)
- id=2 记录的 next_record 修改为指向 id=5

最终形成：
PAGE_FREE → id=2 → id=5 → NULL

插入时复用空闲空间：

当插入新记录 id=4 时：

1. 查看 PAGE_FREE，指向 id=2
2. 取出 id=2 的位置
3. 检查空间是否足够
4. 如果足够，在 id=2 位置写入新记录
5. 更新 PAGE_FREE 指向 id=5
6. 新记录的 next_record 指向原来 id=2 指向的位置

完整示例：

场景：依次删除 id=2, id=5, id=8（非连续删除）

删除后页的状态：
┌─────────────────────────────────────────────────────────┐
│ Page Header:                                           │
│   PAGE_FREE = 0x100  (指向 id=2)                      │
│   PAGE_GARBAGE = 300 bytes (三个删除记录的总空间)     │
├─────────────────────────────────────────────────────────┤
│ Records:                                               │
│   id=1: next_record → id=3                            │
│   id=2: deleted=1, next_record → id=5  ─┐            │
│   id=3: next_record → id=5              │ 链表      │
│   id=5: deleted=1, next_record → id=8  ─┼─→ ...   │
│   id=8: deleted=1, next_record → NULL  ─┘            │
│   id=10: ...                                          │
└─────────────────────────────────────────────────────────┘

空闲链表: id=2 → id=5 → id=8 → NULL

页面分裂

当一个页无法插入新数据时，执行页面分裂：

分裂前（Page 已满）:
┌─────────────────────────────────────┐
│ Page 1                              │
│ Infimum → [1] → [3] → [5] → [7]   │
│                      → [9] → [11]   │
│                      (已满，无法插入) │
└─────────────────────────────────────┘

分裂后:
┌─────────────────┐    ┌─────────────────┐
│ Page 1          │    │ Page 2 (新)    │
│ [1] [3] [5]    │    │ [7] [9] [11]   │
│ (左半部分)      │    │ (右半部分)      │
└─────────────────┘    └─────────────────┘
         ↓                      ↓
    父节点更新           父节点更新
    (索引指针)          (索引指针)

分裂策略：

找到中间记录
左边一半保留原页面
右边一半移到新页面
更新父节点的索引

空间回收机制

定期清理：Purge 线程

后台 Purge 线程做的事情：
1. 真正删除已标记的记录
2. 回收 PAGE_GARBAGE 的空间
3. 可能合并相邻的碎片页

Purge 详解：

InnoDB 的删除操作采用"标记删除"策略，物理空间的真正回收由后台 Purge 线程完成。

删除记录的完整生命周期：

4. 用户执行 DELETE
   → 记录标记为 deleted_flag = 1
   → 加入 Page Free List
   → 立即返回成功

5. 事务提交后
   → 记录仍占用物理空间
   → 等待 Purge 线程清理

6. Purge 线程执行
   → 读取 Undo Log
   → 确认记录不再需要（事务已提交 + 无活跃事务需要）
   → 真正释放物理空间
   → 更新 PAGE_GARBAGE

为什么需要 Purge 线程延迟清理？

原因：MVCC 多版本并发控制

┌─────────────────────────────────────────────────────────┐
│ 事务 A: BEGIN; SELECT * FROM users WHERE id=1;        │
│         (读取到 id=1 的历史版本)                       │
├─────────────────────────────────────────────────────────┤
│ 事务 B: DELETE FROM users WHERE id=1; COMMIT;          │
│         (标记删除 id=1)                                │
├─────────────────────────────────────────────────────────┤
│ 事务 C: SELECT * FROM users WHERE id=1;               │
│         (如果立刻物理删除，事务 C 将看不到数据)         │
└─────────────────────────────────────────────────────────┘

所以必须等所有可能读到历史版本的事务结束后，才能物理删除

Purge 线程的工作流程：

7. 扫描 Undo Log
   - 找到已提交事务产生的删除标记
   - 判断记录是否可以被物理删除

8. 判断条件
   - 事务已提交
   - 没有活跃事务的 trx_id 小于该事务的 trx_id

9. 物理删除
   - 从页中移除记录
   - 回收空间到 PAGE_GARBAGE
   - 更新 FSP Header 中的空闲页统计

配置 Purge 行为：

-- 查看 purge 配置
SHOW VARIABLES LIKE 'innodb_purge_threads';

-- 查看 purge 批量大小
SHOW VARIABLES LIKE 'innodb_purge_batch_size';

-- 手动触发 purge（极端情况）
SET GLOBAL innodb_max_purge_lag = 0;

增删改查的完整物理流程

┌─────────────────────────────────────────────────────────────┐
│                     INSERT                                  │
│  1. 定位索引 → 找到叶子节点页                              │
│  2. 检查 Free Space                                       │
│  3. 写入记录到空闲空间                                    │
│  4. 更新 Page Header                                      │
│  5. 更新 Page Directory                                   │
│  6. 如果页面满 → 分裂                                     │
└─────────────────────────────────────────────────────────────┘

┌─────────────────────────────────────────────────────────────┐
│                     DELETE                                  │
│  1. 定位记录                                              │
│  2. 标记 deleted_flag = 1                                 │
│  3. 加入 Page Free List                                   │
│  4. 更新 PAGE_GARBAGE                                     │
│  5. 后台 Purge 线程真正删除并回收空间                      │
└─────────────────────────────────────────────────────────────┘

┌─────────────────────────────────────────────────────────────┐
│                     UPDATE                                  │
│  1. DELETE (标记删除旧记录)                               │
│  2. INSERT (插入新记录)                                   │
│  → 相当于两次操作                                         │
└─────────────────────────────────────────────────────────────┘

┌─────────────────────────────────────────────────────────────┐
│                     SELECT                                  │
│  1. 定位索引 → 找到目标页                                 │
│  2. Page Directory 二分查找定位记录                       │
│  3. 读取记录返回                                          │
└─────────────────────────────────────────────────────────────┘

索引

索引是 MySQL 中用于加速数据查询的数据结构，类似于书的目录。合理的索引设计能够大幅提升查询性能。

聚簇索引 clustered index

定义： 索引的叶子节点存储完整的行数据（数据与索引在一起）。

特点：

每个表只能有一个聚簇索引
InnoDB 中，主键索引就是聚簇索引
如果没有显式主键，会选择第一个非空唯一索引作为聚簇索引
如果既没有主键也没有唯一索引，InnoDB 会自动生成一个隐藏的 ROWID 作为聚簇索引

聚簇索引结构：

                    ┌─────────┐
                    │  Root   │  ← 非叶子节点
                    │  Page   │
                    └────┬────┘
                         │
            ┌────────────┼────────────┐
            │            │            │
      ┌────▼────┐  ┌────▼────┐  ┌────▼────┐
      │  Page   │  │  Page   │  │  Page   │
      │ (非叶子) │  │ (非叶子) │  │ (非叶子) │
      └────┬────┘  └────┬────┘  └────┬────┘
           │            │            │
           └────────────┼────────────┘
                        │
              ┌────────▼────────┐
              │   Leaf Pages    │  ← 叶子节点存储完整行数据
              │  [id=1, Tom,25] │
              │  [id=3, Alice,30]│
              │  [id=5, Bob, 28] │
              └──────────────────┘

优势：

范围查询效率高（数据连续存储）
减少磁盘 I/O（一次 I/O 可以读取数据和索引）
主键查询非常快

劣势：

插入速度依赖插入顺序（顺序插入快，随机插入慢）
更新主键代价高（需要移动数据）
二级索引查询需要两次索引查找

二级索引 secondary index

定义： 非聚簇索引，叶子节点存储索引列的值和主键值。

特点：

也叫辅助索引、普通索引
一个表可以有多个二级索引
叶子节点不存储完整数据，只存储：索引列值 + 主键值

二级索引 (idx_age) 结构：

                    ┌─────────┐
                    │  Root   │
                    └────┬────┘
                         │
            ┌────────────┼────────────┐
            │            │            │
      ┌────▼────┐  ┌────▼────┐  ┌────▼────┐
      └─────────┘  └─────────┘  └─────────┘
                        │
              ┌────────▼────────┐
              │   Leaf Pages    │
              │ age=18, id=15   │
              │ age=20, id=3    │ ← 存储 (索引列, 主键)
              │ age=22, id=88   │
              │ age=25, id=5    │
              └────────┬────────┘
                       │ 根据 id=5 回表查询
                       ▼
              ┌──────────────────┐
              │  聚簇索引叶子节点 │
              │  完整行数据       │
              │ id=5, Tom, 25... │
              └──────────────────┘

回表查询：

-- 查询 age=25 的完整信息
SELECT * FROM users WHERE age = 25;

-- 执行流程：
-- 1. 在 idx_age 索引中找到 age=25 的记录
-- 2. 获取主键 id=5
-- 3. 再通过主键索引找到完整行数据
-- 4. 返回结果

索引覆盖：

如果需要查询的列正好在索引中，就不需要回表：

-- 只需要 age 和 id，不需要回表
SELECT age, id FROM users WHERE age = 25;
-- Using index (覆盖索引)

联合索引 composite index

定义： 多个列组合创建的索引。

最左前缀原则：

联合索引遵循最左前缀原则，即索引的最左边的列必须出现在查询条件中，索引才会生效。

创建联合索引：INDEX idx_name_age (name, age, email)

索引结构（按 name, age, email 排序）：

┌─────────────────────────────────────────────┐
│ name     │ age │ email      │ 主键 id       │
├─────────────────────────────────────────────┤
│ Tom      │ 18  │ a@xx.com   │ 1             │
│ Tom      │ 25  │ b@xx.com   │ 2             │
│ Tom      │ 30  │ c@xx.com   │ 3             │
│ Alice    │ 18  │ d@xx.com   │ 4             │
│ Alice    │ 22  │ e@xx.com   │ 5             │
│ Bob      │ 28  │ f@xx.com   │ 6             │
└─────────────────────────────────────────────┘

有效查询：

-- ✅ 使用 name (最左列)
WHERE name = 'Tom'

-- ✅ 使用 name + age (最左两列)
WHERE name = 'Tom' AND age = 25

-- ✅ 使用 name + age + email (全部三列)
WHERE name = 'Tom' AND age = 25 AND email = 'b@xx.com'

-- ⚠️ 使用 age + email (跳过最左列，索引失效)
WHERE age = 25 AND email = 'b@xx.com'

-- ⚠️ 使用 email (跳过前面所有列，索引失效)
WHERE email = 'b@xx.com'

索引下推（Index Condition Pushdown, ICP）：

MySQL 5.6+ 引入的优化，在索引遍历过程中过滤数据，减少回表次数。

-- 假设索引 idx_name_age (name, age)
SELECT * FROM users WHERE name = 'Tom' AND age > 20;

-- 不使用 ICP：
-- 1. 通过 name='Tom' 找到所有记录
-- 2. 回表获取完整数据
-- 3. 在服务器层过滤 age > 20

-- 使用 ICP：
-- 1. 通过 name='Tom' 找到记录
-- 2. 在索引层直接过滤 age > 20
-- 3. 只对满足条件的记录回表
-- 减少回表次数

全文索引

定义： 用于全文搜索的专用索引，适用于 LIKE '%keyword%' 类型的查询。

适用场景：

大量文本内容的搜索
英文、中文等自然语言搜索

创建全文索引：

-- 创建
ALTER TABLE articles ADD FULLTEXT(title, content);

-- 使用 MATCH AGAINST 查询
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('database' IN NATURAL LANGUAGE MODE);

全文搜索模式：

模式	说明
NATURAL LANGUAGE MODE	自然语言模式（默认）
BOOLEAN MODE	布尔模式，支持 +、- 等操作符
QUERY EXPANSION	扩展查询模式

InnoDB vs MyISAM：

MyISAM：原生支持全文索引
InnoDB：MySQL 5.6+ 开始支持全文索引

哈希索引

定义： 使用哈希表实现的索引，适用于等值查询。

特点：

记忆化查询效率高（O(1)）
不支持范围查询
不支持排序
哈希冲突需要链表或红黑树解决

哈希索引结构：

Key: "Tom"  →  Hash("Tom") = 3  →  指向 Page 3
Key: "Alice" → Hash("Alice") = 7 →  指向 Page 7

内存中哈希表：
┌────────┬─────────┐
│ Hash   │ Pointer │
├────────┼─────────┤
│ 3      │ Page 3  │  →  (Tom, row_data)
│ 7      │ Page 7  │  →  (Alice, row_data)
│ 15     │ Page 15 │  →  (John, row_data)
└────────┴─────────┘

InnoDB 中的自适应哈希索引：

InnoDB 会自动为热点数据页建立哈希索引（自适应），无需手动创建。

-- 查看自适应哈希索引状态
SHOW ENGINE INNODB STATUS;

-- 自适应哈希索引相关信息：
-- Hash table size 2540419, used cells 123456
-- hash searches: 789012, non-hash searches: 123456

Memory 存储引擎：

Memory 引擎默认使用哈希索引，也支持 B-Tree 索引。

-- 创建哈希索引
CREATE TABLE t1 (
    id INT,
    name VARCHAR(50),
    INDEX idx_name USING HASH (name)
) ENGINE=MEMORY;

索引的数据结构

B+Tree vs B-Tree：

B-Tree（非叶子节点也存储数据）：
┌─────────────────────────────────────┐
│           B-Tree                    │
├─────────────────────────────────────┤
│    [10] [20] [30]                  │  ← 节点存储键和数据
│   /   \   \                        │
│ [5][8] [15] [35][40]              │
└─────────────────────────────────────┘

B+Tree（只有叶子节点存储数据）：
┌─────────────────────────────────────┐
│          B+Tree                     │
├─────────────────────────────────────┤
│    [10] [20] [30]                  │  ← 只存储键和指针
│   /    \    \                       │
│  [5][8] [15] [35][40] ────────    │  ← 叶子节点存储完整数据
│    \                              │
│   all data → [5][8][15][20][30][35][40]  ← 链表连接
└─────────────────────────────────────┘

B+Tree 的优势：

叶子节点存储数据，磁盘 I/O 更少
叶子节点链表连接，范围查询快
树高稳定（所有叶子节点在同一层）
查询性能稳定

索引的创建与使用

创建索引：

-- 主键索引
ALTER TABLE users ADD PRIMARY KEY (id);

-- 唯一索引
ALTER TABLE users ADD UNIQUE INDEX idx_email (email);

-- 普通索引
ALTER TABLE users ADD INDEX idx_age (age);

-- 联合索引
ALTER TABLE users ADD INDEX idx_name_age (name, age);

-- 全文索引
ALTER TABLE articles ADD FULLTEXT(title, content);

索引失效的情况：

-- ❌ 使用函数
WHERE YEAR(created_at) = 2024

-- ❌ 类型转换
WHERE name = 123  -- name 是 VARCHAR

-- ❌ 使用 LIKE %开头
WHERE name LIKE '%Tom'

-- ❌ 使用 OR（部分情况）
WHERE name = 'Tom' OR age = 25

-- ❌ 联合索引不遵循最左前缀
WHERE age = 25

-- ❌ 使用 IS NULL / IS NOT NULL
WHERE name IS NULL

EXPLAIN 分析索引使用：

EXPLAIN SELECT * FROM users WHERE age = 25;

-- 关键字段：
-- type: ALL(全表扫描) < index(索引扫描) < range(范围) < ref(索引查找)
-- key: 实际使用的索引
-- rows: 预计扫描行数
-- Extra: Using index (覆盖索引) / Using where (需要过滤)

MySQL从头到尾

MySQL 的核心知识

存储结构

逻辑存储结构

物理存储结构

页 page (默认 16 KB)

区 extent

段 segment

表空间 tablespace

具体示例：一个表和一个索引是怎么存的

行格式

Compact（紧凑格式）

Dynamic（动态格式）

Compressed（压缩格式）

变长字段的溢出存储

配置行格式

增删数据时物理结构的处理

插入数据流程

删除数据流程

空闲记录链表（Free List）

页面分裂

空间回收机制

增删改查的完整物理流程

索引

聚簇索引 clustered index

二级索引 secondary index

联合索引 composite index

全文索引

哈希索引

索引的数据结构

索引的创建与使用

Binlog

逻辑日志

记录 DDL 和 DML

用于主从复制

数据恢复

格式

Statement

Row

Mixed

UndoLog

撤销日志

记录数据历史版本

实现事务回滚

支持 MVCC

存储在 undo 表空间

RedoLog

重做日志

物理日志

崩溃恢复保障

事务持久性保证

循环写入

Checkpoint 机制

事务的隔离机制

Read uncommitted 读未提交

脏读

最低隔离级别

Read committed 读已提交

不可重复读

每次 SELECT 创建新视图

Repeatable read 可重复读

事务开始创建视图

解决不可重复读

MySQL 默认级别

Serializable 串行化

最高隔离级别

强制串行执行

性能最低

隔离级别解决的问题

脏读

不可重复读

幻读

MVCC

多版本并发控制

隐藏字段

DB_TRX_ID 事务 ID

DB_ROLL_PTR 回滚指针

DB_ROW_ID 行 ID (可选)

Read View

M_ids 活跃事务列表