mysql系列2—InnoDB数据存储方式

背景

本文将深入探讨InnoDB的底层存储机制，包括行格式、页结构、页目录以及表空间等核心概念。通过全面了解这些基础概念，有助于把握MySQL的存储架构，也为后续深入讨论MySQL的索引原理和查询优化策略奠定了基础。

1.行格式

mysql中数据以行为单位进行存储，将行数据、行的描述数据(可变字段长度、空值等)、隐藏字段等放在一起组成一个单位进行存储。MySQL支持四种行格式，它们根据存储格式、字段溢出处理策略、是否启用压缩等因素进行区分。

1.1 行格式介绍

[1] Redundant

占用的空间最多且易导致内存严重碎片化，是效率最低的行格式。已不再推荐使用Redundant，mysql为了与旧版本保持兼容性而保留。

[2] Compact

Compact的每行记录由三个部分组成，如下图所示:

(1) 额外信息:
存放可变长字段实际长度列表、空值列表、记录头信息。
当数据库表中存在VARCHAR、VARBINARY、TEXT、BLOB等可变类型时，需要记录这些字段的实际长度；当数据库表中的字段允许为空时，需要记录对应字段是否为空；可变长字段列表、可为空字段列表中约定以字段顺序逆序排列。
其中，记录头由固定的5字节组成, 包括以下字段:
a) delete_flag: 标记该记录是否被删除;
b) min_rec_flag: B+树的每层非叶子节点中最小的目录项记录都会添加该标记;
c) n_owned: 一个数据页中的记录分为若个个组；每个组的最后一个记录通过n_owned记录当前组有多少条记录，其他记录n_owned为0;
d) heap_no: 当前记录在当前数据页的相对位置;
e) record_type: 记录类型：0-普通记录，1-B+树飞叶子节点的目录项记录，2-Infimum记录，3-Supremum记录;
f) next_record: 下一条记录的相对位置。

(2) 隐藏字段
mysql为每条记录生成三个隐藏字段db_row_id和db_trx_id和db_roll_ptr;
db_row_id: Innodb为每条记录生成的一个隐藏的自增主键，可用于唯一标识记录行; 当数据库表中没有主键索引和非空的唯一索引时，db_row_id数据将被用作建立聚簇索引。
db_trx_id：记录插入或者最近一次修改当前记录的事务ID；
db_roll_ptr: 回滚指针，指向上一个版本的快照数据，多个版本之间通过db_roll_ptr形成版本链，用于事务回滚；

(3) 实际数据: 实际每列存储的数据信息。
实际存放用户数据的地方，额外信息与隐藏字段是为了保证mysql的基本功能而引入的。

[3] Dynamic

InnoDB默认的行格式，与Compact结构一致；区别在于处理字段长度溢出时的策略不同。
数据溢出指text、blob、长varchar字段存放较大长度的数据。当数据溢出发生时，Compact将前768个字节存储在当前页，其他数据存储在溢出页，并通过指针指向溢出页；Dynamic将所有字节保存在溢出页，仅保存指向该地址的20字节长度的指针。

[4] Compressed

Compressed行格式在Dynamic行格式的基础上增加了压缩功能，能够进一步减少存储空间的需求，适用于存储大量数据的场景。虽然可以减少存储空间，但是在大量更新或查询操作下，压缩和解压缩过程可能会消耗服务器的CPU资源，从而影响mysql整体性能。
因此，除非场景特殊，一般不设置为Compressed；mysql也禁止将Compressed设置为默认的行格式。

1.2 变量查询与设置

[1] 查询数据库默认行格式
通过innodb_default_row_format变量查询mysql默认支持的行格式:

mysql> SHOW VARIABLES LIKE 'innodb_default_row_format';
+---------------------------+---------+
| Variable_name             | Value   |
+---------------------------+---------+
| innodb_default_row_format | dynamic |
+---------------------------+---------+
1 row in set (0.00 sec)

mysql8汇总默认支持的行格式为DYNAMIC.

[2] 修改数据库默认行格式
通过设置innodb_default_row_format变量值:

SET GLOBAL innodb_default_row_format = DYNAMIC;

通过配置文件修改:

#/etc/my.cnf
innodb_default_row_format=Redundant

修改后，重启mysql生效。

注意：COMPRESSED不能被设置为默认值。

[3] 建表时指定行格式
建表时通过ROW_FORMAT指定行格式，案例如下所示:

    CREATE TABLE t_test (
        id INT AUTO_INCREMENT PRIMARY KEY
    ) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;

此时，t_test被指定为DYNAMIC格式；若不指定，将使用mysql的默认行格式。

[4] 修改表的行格式

ALTER TABLE test ROW_FORMAT=COMPACT;

[5] 查看表的行格式
通过show table status from test like 't_test';可以查看表的行格式，也可通过查询information_schema.tables表的Row_format字段：

mysql> SELECT Row_format FROM information_schema.tables WHERE table_schema = 'test' AND table_name = 't_test';
+------------+
| ROW_FORMAT |
+------------+
| Dynamic    |
+------------+
1 row in set (0.01 sec)

2.页和页目录

在第一章中已经介绍了数据存储的基本单位(数据行)的存储格式，本章将介绍存储数据行的容器，即页。为减少IO次数以提高查询效率，mysql要求内存与磁盘交换的单位是一个页，大小默认为16K。页如下图所示由8个部分组成，本章节将依次介绍这写组成部分:

2.1 数据部分：User Records和Free Space

User Records区域用于存放行记录，行之间通过next_record形成链表。Free Space表示空闲区域，初始分配数据页时，没有数据记录，Free Space占据数据页绝大部分空间；随着数据页中记录的添加，User Records会逐渐占据Free Space区域。

2.2 上确界和下确界: Supremum和Infimum

在每个数据页中引入两个隐藏记录，下确界和上确界分别作为链表的首部和尾部。当有数据插入时，如果当前数据页可以存放新数据，数据行插入在下确界和上确界之间；否则在下一页存储。

2.3 页分组: Page Directory

当从数据页中检索记录行时，可以从Infimum出发，遍历记录行链表。一个数据页16K，且绝大部分空间用于存储数据行，因此数据页中的数据行数量较为巨大，遍历搜索效率较低。
mysql通过引入了页目录概念，解决上述问题。多个连续的数据行组成一个页目录，mysql规定:
(1) Infimum记录所在分组只有有一条记录，是Infimum本身;
(2) supremum所在分组记录数在1~8;
(3) 其他中间分组在4~8条;
(4)每个分组的最后一条记录的n_owned列记录所在分组的数量，其他记录n_owned为0;
随着数据的插入，分组的变化过程如下图所示:

数据页的初始状态:
没有用户记录行，只有两个分组，分别包含Infimum记录和supremum记录；此时Page Directory中保存两个地址slot1和slot2，分别指向Infimum记录和supremum记录，二者都是分组的最后一条记录，且所在分组记录数都是1，所以n_owned=1.

插入4条记录时:

数据根据主键判断，插入在Infimum记录和supremum记录之间；此时形成两个组。slot1和slot2分别指向两个组(页目录)。slot2对应的组中存在5条记录，因此supremum的n_owned=5.

再次插入4条记录时:

由于页目录的记录数在4~8，所以需要裂解为两个组；此时形成三个组，分别对应page Directory中的slot1和slot2和slot3三个指针。Infimum所在分组始终只有一个记录，n_owned=1; slot2执行Record4的地址，Record4记录的n_owned标记为4；supremum所在组有5个记录行，n_owned标记为5.

当理解了页目录的动态变化后，可以考虑一下mysql为什么这么设计:
由于数据行按序排列(主键递增)，因此页目录的最后一个记录为组内最大值; 每个页目录的最大行记录(最后一条记录)的地址固定为两个字节，保存在Page Directory中。此时，查询数据无需从Infimum节点开始遍历。可根据Page Directory中各个组的最大记录值快速定位出行记录属于哪个组，然后在组内查找(每个组作为8条记录，可以直接遍历)。

2.4 Page Header

Page Header是数据页专有的一些信息，它占用固定的56个字节, 包含以下字段：
(1) PAGE_N_DIR_SLOTS: Page Directory中的Slot数量;
(2) PAGE_HEAP_TOP: 堆中第一个记录的地址;
(3) PAGE_N_HEAP: 堆中的记录数, 包括Infimum和Supermum记录和已标记为删除的记录;
(4) PAGE_NRECS: 用户实际的记录数，不包括Infimum和Supermum，也不包括Infimum和Supermum记录;
(5) PAGE_FREE: 可用空间(free space)的地址;
(6) PAGE_GARBAGE: 已删除记录的字节数;
(7) PAGE_LAST_INSERT: 最后插入记录的位置;
(8) PAGE_DIRECTION和PAGE_N_DIRECTION: 分别表示最后插入的方向和一个方向连续插入记录的数量。

2.5 File Header

File Header是页的通用信息部分，对于所有类型的页都是相同的。File Header用于描述当前页的元信息，它占用固定的38字节，包含以下字段:
(1) FIL_PAGE_OFFSET: 页号，页的唯一ID;
(2) FIL_PAGE_PREV和FIL_PAGE_NEXT：分别表示上一个和下一个数据页的页号，使得数据页之间形成双向链表;

(3) FIL_PAGE_TYPE: 数据页的类型，包括：数据页，索引页，undolog页等;
(4) FIL_PAGE_ARCH_LOG_NO_OR_SPACE_ID: 页所属的表空间;
(5) FIL_PAGE_FILE_FLUSH_LSN: 仅在系统表空间的一个页中定义，代表文件至少被刷新到了对应的LSN值(独立表空间默认为0);
(6) FIL_PAGE_LSN：页面被最后修改时对应的日志序列位置;
(7) FIL_PAGE_SPACE_OR_CHKSUM：页的校验和(checksum值), FIL_PAGE_LSN和FIL_PAGE_SPACE_OR_CHKSUM被用于与File Tailer进行比较，以确认当前页是否完整。

2.6 File Tailer

mysql在内存中修改数据页，之后刷入到磁盘中。刷盘过程遇到故障(如断电)可能导致数据页未完全写入磁盘。为确保页的完整性，mysql分别在页的首部和尾部添加了File Header和File Tailer, 通过比较二者可以判断当前数据页是否完整。
File Trailer包含一个FIL_PAGE_END_LSN字段，占8字节: 前4字节表示该页的checksum值，后4字节表示FIL_PAGE_LSN; 两者需要与File Header中的FIL_PAGE_SPACE_OR_CHKSUM和FIL_PAGE_LSN值进行比较，匹配则满足一致性。

3.表空间

mysql中数据以页为单位进行存储，不同类型的页存放不同类型的数据；如FILE_PAGE_INDEX存放数据页，FILE_PAGE_UNDO_LOG存放undo日志页。页需要以文件为载体存储在磁盘上，表空间是对这个文件的抽象；即表空间可以理解为页的载体，类似书是页的载体。mysql中共存在两种类型的表空间，用户表空间和系统表空间。
系统表空间只能有一个，存放系统相关的信息以及作为公用的表空间。独立表空间可以有多个，存放用户数据。
本章将依此介绍表空间结构和两种表空间。

3.1 表空间结构

一方面，由于表空间的页较多，合理高效的管理方式是对其划分为更小单位；另一方面，连续读的效率要高于随机读。因此，在表空间中引入了区和组的概念。
一个页的大小是16K，物理上连续的64个页作为一个区，即1个区1M。当数据量较大时，内存分配可以区为单位，或者分配连续的多个区，相对以页为单位大大提高效率。进一步，每256个区划分为一个组，即一个组256M。下图较为形象地展示表空间存储页的结构。

说明：为了进一步优化管理mysql和节省存储空间，mysql引入了段(segment)和碎片区(fragment)的概念。一般而言，开发人员只需从整体上理解mysql的存储方式，这部分细节可以不进行过度深入，有兴趣可自行研究。

3.2 独立表空间

默认情况下(innodb_file_per_table=1), 用户建表时mysql创建一个表空间与之对应；innodb_file_per_table设置为0时，建表时使用系统表空间。
以下结合案例进行说明：

mysql> show variables like 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.01 sec)

innodb_file_per_table为ON(1)，表示开启状态。

-- 创建数据库test和t_test表
create database test;
use test;
create table t_test(id int);

查询表空间，

mysql> select ts.space, ts.name, ts.space_type, tf.path from information_schema.INNODB_SYS_TABLESPACES ts Left join information_schema.INNODB_SYS_DATAFILES tf on ts.space = tf.space where name like '%t_test%';
+-------+-------------+------------+-------------------+
| space | name        | space_type | path              |
+-------+-------------+------------+-------------------+
|    30 | test/t_test | Single     | ./test/t_test.ibd |
+-------+-------------+------------+-------------------+
1 row in set (0.00 sec)

得到t_test表位于30号表空间，对应磁盘文件为/test/t_test.ibd。
进入/var/lib/mysql查看表空间文件：

root@124:/var/lib/mysql# cd test/      
root@124:/var/lib/mysql/test# ls -al | grep t_test.ibd
-rw-r----- 1 mysql mysql 98304 Dec  1 03:57 t_test.ibd

此时test/t_test表空间大小为98304，即96k, 包含6个页。

3.3 系统表空间

表空间和表空间对应文件的路径、表与表空间的从属关系、表的列类型数据和索引等等，这些用于管理数据而引入的额外信息属于系统信息, 保存在系统表空间中。以4张内部基本系统表为例：
(1) sys_tables: 表信息，包括：表名，列数，表类型，所属表空间；
(2) sys_columns: 列信息，包括：所属表ID，表的第几列，列名，类型；
(3) sys_indexes: 索引信息，包括：索引名称，索引类型，列个数；
(4) sys_fields: 索引列信息，包括：所属索引ID，索引中的第几列，列名称；
上述四张表属于系统表，用户无法直接访问。当mysql启动时，读取这些sys_表信息，填充到innodb_sys_表中，这些表位于information_schema这个schema中。

mysql> show tables like 'INNODB_SYS_%';
+---------------------------------------------+
| Tables_in_information_schema (INNODB_SYS_%) |
+---------------------------------------------+
| INNODB_SYS_DATAFILES                        |
| INNODB_SYS_VIRTUAL                          |
| INNODB_SYS_INDEXES                          |
| INNODB_SYS_TABLES                           |
| INNODB_SYS_FIELDS                           |
| INNODB_SYS_TABLESPACES                      |
| INNODB_SYS_FOREIGN_COLS                     |
| INNODB_SYS_COLUMNS                          |
| INNODB_SYS_FOREIGN                          |
| INNODB_SYS_TABLESTATS                       |
+---------------------------------------------+
10 rows in set (0.00 sec)

可通过INNODB_SYS_TABLES查看表的信息，通过INNODB_SYS_TABLESPACES查看表空间信息，案例如下所示：

再看一下innodb_data_file_path变量，保存了系统表空间的存储路径：

mysql> show variables like '%innodb_data_file_path%';
+-----------------------+------------------------+
| Variable_name         | Value                  |
+-----------------------+------------------------+
| innodb_data_file_path | ibdata1:12M:autoextend |
+-----------------------+------------------------+
1 row in set (0.00 sec)

系统表空间对应文件ibdata1，初始大小为12M，支持自动扩容。进入mysql路径下查看ibdata1文件：

root@124:/# cd /var/lib/mysql
root@124:/var/lib/mysql# ls -al | grep ibdata1
-rw-r----- 1 mysql mysql 79691776 Dec  1 03:57 ibdata1

此时大小为79691776，即76M。