MySQL高级讲座篇之：数据页的物理存储：行数据、索引与控制信息的布局。

各位观众老爷，大家好！我是今天的主讲人，咱们今天聊点硬核的——MySQL数据页的物理存储！这玩意儿听起来玄乎，但其实就是MySQL存数据的地方，理解了它，你就能更好地理解MySQL的性能优化，以后面试也能唬住面试官！

咱们今天主要讲三个方面：行数据、索引、控制信息，看看它们在数据页里是怎么排列组合的。

一、数据页的结构：一个房间里的秘密

你可以把数据页想象成MySQL存放数据的房间。这个房间不是空荡荡的，里面有各种各样的东西，摆放得井井有条。这个房间的标准大小是16KB。

这个“房间”主要分为以下几个部分：

区域名称	大小（字节）	说明
File Header	38	文件头，记录页的类型、校验和等信息。
Page Header	56	页头，记录页的状态信息，比如页中记录的数量、空闲空间等。
Infimum + Supremum Records	26	两个虚拟记录，分别表示页中最小和最大的记录。
User Records	变长	实际存储的行数据。
Free Space	变长	空闲空间，用于存储新插入的记录。
Page Directory	变长	页目录，用于加速在页中查找记录的速度，类似于书的目录。
File Trailer	8	文件尾，包含页的校验和和LSN（Log Sequence Number），用于保证数据的一致性。

别被这些名词吓到，咱们一个个来解释。

1. File Header (文件头)：房间的身份证

File Header 就像这个房间的身份证，记录了房间的类型、校验和等等重要信息。它包含以下内容：

• FIL_PAGE_SPACE_OR_CHKSUM：页的校验和。MySQL会定期计算页的校验和，如果和存储的校验和不一致，说明页可能损坏了。
• FIL_PAGE_OFFSET：页的编号。
• FIL_PAGE_PREV和FIL_PAGE_NEXT：分别指向前一个和后一个页的编号。这些指针将多个页连接成一个双向链表，方便遍历。
• FIL_PAGE_TYPE：页的类型，比如索引页、数据页等。

2. Page Header (页头)：房间的管理信息

Page Header 记录了这个房间的管理信息，比如房间里有多少人（记录）、还有多少空位（空闲空间）等等。它包含以下内容：

• PAGE_N_DIR_SLOTS：Page Directory 中有多少个槽位（slot）。
• PAGE_HEAP_TOP：堆顶指针，指向空闲空间的起始位置。
• PAGE_N_HEAP：堆中记录的数量。
• PAGE_FREE：指向被删除记录组成的垃圾链表的头节点。
• PAGE_GARBAGE：页中被删除记录占用的总字节数。
• PAGE_LAST_INSERT：指向最后插入的记录。
• PAGE_DIRECTION：记录插入的方向，是左边还是右边。
• PAGE_N_RECS：页中记录的数量（包括最小和最大记录）。
• PAGE_MAX_TRX_ID：修改当前页的最大事务ID。
• PAGE_LSN：页最后被修改的LSN。

3. Infimum + Supremum Records (最小和最大记录)：房间的门卫

这两个记录是虚拟的，分别表示页中最小和最大的记录。它们的作用是方便查找记录，充当“门卫”的角色。

• Infimum Record：比页中任何记录都小。
• Supremum Record：比页中任何记录都大。

4. User Records (用户记录)：房间里的住户

User Records 就是实际存储的行数据。每一行数据都包含以下信息：

• record_type：记录类型，比如普通记录、最小记录、最大记录等。
• next_record：指向下一条记录的指针。
• heap_no：记录在堆中的编号。
• deleted_flag：删除标记，如果记录被删除，这个标记会被设置为1。
• min_rec_flag：最小记录标记，如果记录是最小记录，这个标记会被设置为1。
• n_owned：Page Directory 中有多少个槽位指向该记录。
• data：实际的行数据。

5. Free Space (空闲空间)：房间的空地

Free Space 就是空闲的空间，用于存储新插入的记录。

6. Page Directory (页目录)：房间的索引

Page Directory 类似于书的目录，用于加速在页中查找记录的速度。它将页中的记录分成若干组，每个组包含若干条记录，每个组的最后一条记录的地址会被存储在 Page Directory 中。

当我们要在页中查找一条记录时，首先通过二分查找在 Page Directory 中找到包含该记录的组，然后再在组中顺序查找。这样可以大大减少查找的范围，提高查找效率。

7. File Trailer (文件尾)：房间的防伪标签

File Trailer 包含页的校验和和LSN，用于保证数据的一致性。

• FIL_PAGE_SPACE_OR_CHKSUM：页的校验和。
• FIL_PAGE_LSN：页最后被修改的LSN。

二、行数据：住户的信息

行数据是数据页中最核心的部分，它存储了实际的行信息。MySQL的行数据存储格式是变长的，这意味着不同的行可以占用不同的空间。

MySQL支持多种行格式，常见的有Compact、Redundant、Dynamic和Compressed。不同的行格式在存储效率和功能上有所差异。

咱们以Compact行格式为例，看看行数据是如何存储的。Compact行格式主要包含以下几个部分：

• 记录头信息（Record Header）：占用5个字节，存储记录的元数据，比如记录类型、删除标记、下一条记录的指针等等。
• 变长字段长度列表：如果行中有变长字段（比如VARCHAR、TEXT等），那么这个列表会存储每个变长字段的实际长度。
• NULL值列表：如果行中有允许为NULL的字段，那么这个列表会存储每个字段是否为NULL的标记。
• 实际数据：存储实际的列数据。

举个栗子：

假设我们有一张表user，包含以下字段：

• id INT NOT NULL PRIMARY KEY
• name VARCHAR(20) NOT NULL
• age INT NULL
• email VARCHAR(50) NULL

现在插入一条数据：

INSERT INTO user (id, name, age, email) VALUES (1, '张三', 25, '[email protected]');

那么这条数据在Compact行格式下的存储结构可能是这样的：

1. 记录头信息（Record Header）：5个字节，假设值为0x00 0x01 0x02 0x03 0x04
2. 变长字段长度列表：name字段长度为6（’张三’占6个字节，UTF-8编码），email字段长度为19（’[email protected]’占19个字节）。假设存储为0x06 0x13。
3. NULL值列表：age和email字段都允许为NULL，但这里都不为NULL，所以假设存储为0x00。
4. 实际数据：
   • id：1，假设存储为0x00 0x00 0x00 0x01
   • name：’张三’，假设存储为0xE5 0xBC 0xA0 0xE4 0xB8 0x89
   • age：25，假设存储为0x00 0x00 0x00 0x19
   • email：’[email protected]’，假设存储为0x7A 0x68 0x61 0x6E 0x67 0x73 0x61 0x6E 0x40 0x65 0x78 0x61 0x6D 0x70 0x6C 0x65 0x2E 0x63 0x6F 0x6D

代码模拟：

虽然我们不能直接看到MySQL内部的存储细节，但我们可以用Python模拟一下Compact行格式的存储过程：

def compact_row_format(data):
    """
    模拟Compact行格式的存储
    """
    record_header = b'x00x01x02x03x04'  # 记录头信息
    var_length_list = b''  # 变长字段长度列表
    null_list = b''  # NULL值列表
    actual_data = b''  # 实际数据

    # 假设数据格式为：{'id': int, 'name': str, 'age': int, 'email': str}
    id_val = data['id'].to_bytes(4, byteorder='big')
    name_val = data['name'].encode('utf-8')
    age_val = data['age'].to_bytes(4, byteorder='big') if data['age'] is not None else None
    email_val = data['email'].encode('utf-8') if data['email'] is not None else None

    # 处理变长字段
    var_length_list += len(name_val).to_bytes(1, byteorder='big')
    if email_val is not None:
        var_length_list += len(email_val).to_bytes(1, byteorder='big')
    else:
        var_length_list += (0).to_bytes(1, byteorder='big') # 如果email是NULL，长度为0

    # 处理NULL值
    if data['age'] is None and data['email'] is None:
        null_list = b'x03' # age和email都是NULL，用0x03表示
    elif data['age'] is None:
        null_list = b'x01' # age是NULL，用0x01表示
    elif data['email'] is None:
        null_list = b'x02' # email是NULL，用0x02表示
    else:
        null_list = b'x00' # 都不是NULL，用0x00表示

    # 拼接实际数据
    actual_data += id_val
    actual_data += name_val
    if age_val is not None:
        actual_data += age_val
    if email_val is not None:
        actual_data += email_val

    # 拼接所有部分
    row_data = record_header + var_length_list + null_list + actual_data
    return row_data

# 测试
data = {'id': 1, 'name': '张三', 'age': 25, 'email': '[email protected]'}
row_data = compact_row_format(data)
print(row_data)

这段代码只是一个简化版的模拟，实际的MySQL存储过程要复杂得多。但它可以帮助你理解Compact行格式的基本原理。

三、索引：房间的地图

索引是MySQL中用于加速查询的关键。它类似于一本书的目录，可以帮助我们快速找到需要的数据。

MySQL的索引有很多种，常见的有B-Tree索引、哈希索引、全文索引等等。其中，B-Tree索引是最常用的索引类型。

B-Tree索引的结构类似于一棵树，每个节点包含若干个键值对。叶子节点存储了实际的行数据，而非叶子节点存储了指向子节点的指针。

索引在数据页中的存储：

• 索引页 (Index Page): B-Tree索引的非叶子节点通常存储在索引页中。索引页只存储索引键值和指向下一层索引页的指针，不存储实际的行数据。
• 数据页 (Data Page): B-Tree索引的叶子节点可以存储在数据页中（聚集索引），也可以存储指向数据页的指针（非聚集索引）。

聚集索引 (Clustered Index) vs 非聚集索引 (Secondary Index):

• 聚集索引: 也称为主键索引。 数据页本身就是按照聚集索引的顺序排列的。因此，聚集索引的叶子节点存储的是完整的行数据。一张表只能有一个聚集索引。
• 非聚集索引: 也称为二级索引。非聚集索引的叶子节点存储的是索引键值和指向聚集索引的指针（也就是主键值）。 通过非聚集索引找到主键值后，还需要再通过聚集索引才能找到完整的行数据，这个过程称为"回表"。

举个栗子：

假设我们有一张表user，包含以下字段：

• id INT NOT NULL PRIMARY KEY (聚集索引)
• name VARCHAR(20) NOT NULL (非聚集索引)
• age INT NULL
• email VARCHAR(50) NULL

如果我们创建了name字段的非聚集索引，那么索引的结构可能是这样的：

1. 索引页 (Index Page): 存储name字段的索引键值和指向下一层索引页或数据页的指针。
2. 数据页 (Data Page): 存储name字段的索引键值和对应的id值（主键值）。

当我们执行以下查询时：

SELECT * FROM user WHERE name = '张三';

MySQL会首先通过name字段的非聚集索引找到name为’张三’的记录的id值，然后再通过id字段的聚集索引找到完整的行数据。

代码模拟：

class IndexNode:
    """
    模拟索引节点
    """
    def __init__(self, keys, children=None, is_leaf=False):
        self.keys = keys  # 键值列表
        self.children = children or []  # 子节点列表
        self.is_leaf = is_leaf  # 是否是叶子节点

    def __repr__(self):
        return f"IndexNode(keys={self.keys}, is_leaf={self.is_leaf})"

def build_index(data, key_func):
    """
    模拟构建一个简单的B-Tree索引
    """
    # 为了简化，这里只构建一个两层的索引
    leaf_nodes = []
    for item in data:
        key = key_func(item)
        leaf_nodes.append(IndexNode([key, item['id']], is_leaf=True)) # 叶子节点存储键值和主键ID

    # 构建一个根节点，指向所有的叶子节点
    root_keys = [node.keys[0] for node in leaf_nodes]
    root_node = IndexNode(root_keys, children=leaf_nodes)

    return root_node

# 测试
data = [
    {'id': 1, 'name': '张三', 'age': 25, 'email': '[email protected]'},
    {'id': 2, 'name': '李四', 'age': 30, 'email': '[email protected]'},
    {'id': 3, 'name': '王五', 'age': 28, 'email': '[email protected]'},
]

# 创建name字段的索引
index = build_index(data, lambda x: x['name'])
print(index) # 打印根节点
print(index.children) # 打印叶子节点

这段代码只是一个非常简化的B-Tree索引的模拟，实际的MySQL索引要复杂得多，包括平衡、分裂、合并等操作。

四、控制信息：房间的规章制度

数据页中除了行数据和索引之外，还包含一些控制信息，用于管理数据页的状态、维护数据的一致性等等。

这些控制信息主要包括：

• 页类型 (Page Type): 标识页的类型，比如数据页、索引页、Undo页等等。
• LSN (Log Sequence Number): 日志序列号，用于保证数据的一致性。每次修改数据页时，都会记录一个LSN，用于在崩溃恢复时进行重做或回滚操作。
• 校验和 (Checksum): 用于检测数据页是否损坏。MySQL会定期计算页的校验和，如果和存储的校验和不一致，说明页可能损坏了。
• 空闲空间 (Free Space): 记录页中空闲空间的大小和位置，用于存储新插入的记录。

总结：

今天咱们聊了MySQL数据页的物理存储结构，包括行数据、索引和控制信息。理解了这些内容，你就能更好地理解MySQL的性能优化，比如如何选择合适的行格式、如何创建高效的索引等等。

数据页是MySQL存储数据的基本单元，理解它的结构对于深入理解MySQL的底层原理至关重要。希望今天的讲座能对你有所帮助！

下次有机会，咱们再聊聊MySQL的日志系统，那也是一个很有意思的话题！ 感谢大家！