TODO【聊聊MySQL】三.InnoDB的表结构和行记录结构

发表于 更新于 分类于 ， ， 本文字数： 5.5k 阅读时长 ≈ 20 分钟

一.InnoDB的表和数据

InnoDB 是怎样存储数据的，表是大概怎样的一种格式，表中的数据行又是怎样的一种格式。如果想要大概知道 B+ 索引的执行过程，这块基本是跳不过去的。当然关于 表 和 数据行 的东西特别多，没办法涵盖所有，所以我只能将我学的，大概知道的重要部分取出来说一说。

这里先大概说下后面才说的 索引 问题，B+索引 其实利用了表中的 行记录头（可以想象是 HTTP 请求的请求头），然后进行数据结构组织实现快速检索的一种结构。所以不用说啦，行记录头 一些必要的属性，基本要知道的，也可以优化数据库的占用空间。

Innodb 存储引擎表在创建的时候，如果没有显示定义主键（Primary Key），则会通过以下的规则创建主键：

  1. 如果表中有非空的唯一索引，则该列为主键；
  2. 如果 1 不满足，则在你看不见的地方（行头）创建一个 6 字节大小的指针。

我先从宏观的表空间进入，到段、区，到页，到行，那么这样看完行的时候，可以直接感受下一篇讲索引使用的行中的信息，也算是有一定的逻辑顺序吧。

他们的大致关系是：

二.表空间

2.1 表的物理存储

数据、表、数据库文件

至于要找到表结构的文件，我们至少需要先知道数据文件存在哪个地方，所以我们可以直接通过 SQL 语句进行查询：

1
2
3
4
5
6
7
mysql> SHOW variables like 'datadir';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ |
+---------------+-----------------+
1 row in set (0.00 sec)

OK，结果返回，我们的数据存储在 /var/lib/mysql/ 中，所以我们现在进去瞅一瞅是什么东西。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
[root@localhost ~]# cd /var/lib/mysql/
[root@localhost mysql]# ll
总用量 122952
-rw-r-----. 1 mysql mysql       56 9月  22 23:54 auto.cnf
-rw-------. 1 mysql mysql     1679 9月  22 23:54 ca-key.pem
-rw-r--r--. 1 mysql mysql     1107 9月  22 23:54 ca.pem
-rw-r--r--. 1 mysql mysql     1107 9月  22 23:54 client-cert.pem
-rw-------. 1 mysql mysql     1679 9月  22 23:54 client-key.pem
drwxr-x---. 2 mysql mysql       20 9月  23 00:00 demo
-rw-r-----. 1 mysql mysql      424 9月  22 23:54 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 9月  22 23:54 ibdata1
-rw-r-----. 1 mysql mysql 50331648 9月  22 23:54 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 9月  22 23:54 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 9月  22 23:54 ibtmp1
drwxr-x---. 2 mysql mysql     4096 9月  22 23:54 mysql
srwxrwxrwx. 1 mysql mysql        0 9月  22 23:54 mysql.sock
-rw-------. 1 mysql mysql        5 9月  22 23:54 mysql.sock.lock
drwxr-x---. 2 mysql mysql     8192 9月  22 23:54 performance_schema
-rw-------. 1 mysql mysql     1675 9月  22 23:54 private_key.pem
-rw-r--r--. 1 mysql mysql      451 9月  22 23:54 public_key.pem
-rw-r--r--. 1 mysql mysql     1107 9月  22 23:54 server-cert.pem
-rw-------. 1 mysql mysql     1675 9月  22 23:54 server-key.pem
drwxr-x---. 2 mysql mysql     8192 9月  22 23:54 sys

为了能够继续说下去，我需要建立一些数据库以及表来做演示：

1
2
3
4
5
6
mysql> create database innodb_study;
Query OK, 1 row affected (0.01 sec)
mysql> use innodb_study;
Database changed
mysql> create table table_file_user (id int, name varchar(10));
Query OK, 0 rows affected (0.03 sec)

好了，创建了一个 innodb_study 的数据库以及一个 table_file_user 的表，现在我们重新来看看数据文件里面多了些什么东西：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
[root@localhost mysql]# ll
总用量 122952
-rw-r-----. 1 mysql mysql       56 9月  22 23:54 auto.cnf
-rw-------. 1 mysql mysql     1679 9月  22 23:54 ca-key.pem
-rw-r--r--. 1 mysql mysql     1107 9月  22 23:54 ca.pem
-rw-r--r--. 1 mysql mysql     1107 9月  22 23:54 client-cert.pem
-rw-------. 1 mysql mysql     1679 9月  22 23:54 client-key.pem
-rw-r-----. 1 mysql mysql      424 9月  22 23:54 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 10月  9 22:44 ibdata1
-rw-r-----. 1 mysql mysql 50331648 10月  9 22:44 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 9月  22 23:54 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 10月  9 22:42 ibtmp1
drwxr-x---. 2 mysql mysql       74 10月  9 22:44 innodb_study # 多了这个数据库文件夹
drwxr-x---. 2 mysql mysql     4096 9月  22 23:54 mysql
srwxrwxrwx. 1 mysql mysql        0 9月  22 23:54 mysql.sock
-rw-------. 1 mysql mysql        5 9月  22 23:54 mysql.sock.lock
drwxr-x---. 2 mysql mysql     8192 9月  22 23:54 performance_schema
-rw-------. 1 mysql mysql     1675 9月  22 23:54 private_key.pem
-rw-r--r--. 1 mysql mysql      451 9月  22 23:54 public_key.pem
-rw-r--r--. 1 mysql mysql     1107 9月  22 23:54 server-cert.pem
-rw-------. 1 mysql mysql     1675 9月  22 23:54 server-key.pem
drwxr-x---. 2 mysql mysql     8192 9月  22 23:54 sys

好了进去 innodb_study 瞅瞅有哪些东西：

1
2
3
4
5
[root@localhost innodb_study]# ll
总用量 112
-rw-r-----. 1 mysql mysql    65 10月  9 22:43 db.opt
-rw-r-----. 1 mysql mysql  8586 10月  9 22:44 table_file_user.frm
-rw-r-----. 1 mysql mysql 98304 10月  9 22:44 table_file_user.ibd
  1. db.opt 猜一下，应该是数据库的设定，比如字符集啊，使用的存储引擎啊等等；
  2. table_file_user.frm 则是我创建的用户表的结构文件了；
  3. table_file_user.ibd 是 InnoDB 存储引擎的数据库文件；

首先我们先来看看 db.opt 文件：

1
2
3
[root@localhost innodb_study]# cat db.opt
default-character-set=latin1
default-collation=latin1_swedish_ci

可以看到，我刚刚在创建这个数据库的时候，一切用的是默认的，所以默认的字符集以及排序规则都是 latin1 相关的。

latin1百科

table_file_user.frm 则无法直接使用 cat 进行查看，需要通过 MySQL管理工具集 MySQL Utilities 的方式进行查看，不过在这里我就不演示了。

table_file_user.ibd 则就是数据库文件了，存储的是数据库数据，后面再说。

表空间设置参数

针对 table_file_user.ibd 有个参数需要提一下 innodb_file_per_table

1
2
3
4
5
mysql> SHOW variables like 'innodb_file_per_table'\G;
*************************** 1. row ***************************
Variable_name: innodb_file_per_table
        Value: ON
1 row in set (0.01 sec)

什么意思咧，就是每个表的数据都存储在独立的表空间，5.7 以前默认是 off 的，后面的版本就默认开启了。如果是 off 的话，所有表的数据都会记录在一个文件里面。但是即使开启了独立表空间的参数，也只是记录表的数据、索引、插入缓冲等信息，其他的回滚日志还是会存储在公共的空间中的。

定义表空间文件大小位置

可以通过定义 my.cnf 中的配置，来定义存储的表空间文件：

1
2
[mysqld]
innodb_data_file_path = /db/ibdata1:2000M;/db/ibdata2:2000M:autoextend

我们可以这样定义由两个文件组成表空间，后面的文件容量大小限定表示文件限制大小，而 ibdata2 增长到 2000M 的时候还可以自动增长。

2.1 表的逻辑存储

接下来简单说下上图中，表空间的下级几个东西：

段

表空间由很多个段组成，段又有数据段、索引段、回滚段等等。因为 InnoDB 存储数据本质上就是利用索引树进行存储的，所以数据段即是 B+树 的叶节点，而索引段即为非叶子节点。

区

每个区由 64个页 组成，每页大小 16KB，因此每个区是 1MB。针对需要插入大数据段的操作，InnoDB 每次至多申请 4个区。

我们启用 innodb_file_per_table 参数的时候，创建表的时候即段的开始，InnoDB 先使用 32个页 的碎片页来存放数据，当这些数据存储完了才会申请上面说的 64个页。

具体其他创建的时候怎么创建的细节，因为对我们使用数据库的时候帮助不是很大，这里就不再阐述（可以参考《MySQL技术内幕：InnoDB存储引擎》的 4.2.3 小节）

页

页太重要了，请允许我现在马上提取到大标题来讲！

行

行和页一样，太重要了，现在也一样马上提取到大标题来讲！

三. InnoDB的数据页

页是 MySQL InnoDB 与服务器硬盘打交道的单位，当我们需要查询数据的时候，数据库每次至少加载 一个页 的数据到内存进行查找，而在前面说的刷新脏数据的时候，每次至少刷新 一个页 的数据，而页在 区 中说的，是 16KB。所以交互不会太小导致频繁 IO 也不会因为太大导致读取太慢。

页不仅有数据页，所有的页包含有：

  1. 数据页（B-tree Node）；
  2. Undo页（Undo Log Page）；
  3. 系统页（System Page）；
  4. 事务数据页（Transaction system Page）；
  5. 插入缓冲位图页（Insert Buffer Bitmap）；
  6. 插入缓冲空闲列表页（Insert Buffer Free List）；
  7. 未压缩的二进制大对象页（Uncompress BLOB Page）；
  8. 压缩的二进制大对象页（Compressed BLOB Page）。

第四节数据页的结构和第五节数据行的结构应该是并行了解，也就是两者共同构成搜索数据的基础，阅读建议是数据页格式先有个大概的概念，然后数据行，再两个并起来聊聊数据查询的内部原理.

四. 数据页的结构

这是一个页的结构：

image-20191120152749956

OK，现在我们知道了，InnoDB 数据页 一共由 7 个部分组成。其中 User Records 数据行 存储的是我们用户的数据，他会按照 Table 指定的 Row Format 行格式 的规定进行存储。然而当一个页没有数据页格式的时候，是不存在 User Records 空间的，也就是说 Free Space 将会占领图中 User Records 那部分的空间。

好了，我们先摊开来每部分，大致的说一下是什么功能：

4.1 FileHeader 文件头信息

页头信息主要包含以下几个部分：

名称 大小（字节数） 主要作用
FILE_PAGE_SPACE_OR_CHKSUM 4 4.0.14 之前存放在哪个表空间
后面的版本存储 checksum 值
FILE_PAGE_OFFSET 4 表空间中页的页号
FILE_PAGE_PREV 4 上一页
FILE_PAGE_NEXT 4 下一页
FILE_PAGE_LSN 8 最后修改的日志序号
Log Swquence Number
FILE_PAGE_TYPE 2 页的类型：在后面给出
FILE_PAGE_FILE_FLUSH_LSN 8 代表文件被更新到该指定的LSN值
FILE_PAGE_ARCH_LOG_NO_OR_SPACE_ID 4 属于哪个表空间

在页头信息里面有个 FILE_PAGE_TYPE，它主要有以下几种类型：

  1. FILE_PAGE_INDEX ：B+树 叶节点
  2. FILE_PAGE_UNDO_LOG ：Undo Log 页
  3. FILE_PAGE_INODE ：索引节点
  4. FILE_PAGE_IBUF_FREE_LIST ：Insert Buffer 空闲列表
  5. FILE_PAGE_TYPE_ALLOCATED ：最新分配的页
  6. FILE_PAGE_BITMAP ：Insert Buffer 位图
  7. FILE_PAGE_SYS ：系统页
  8. FILE_PAGE_TRX_SYS ：事务系统数据
  9. FILE_PAGE_FSP_HDR ：File Space Header
  10. FILE_PAGE_XDES ：拓展描述页
  11. FILE_PAGE_BLOB ：BLOB 页

4.2 PageHeader 页头信息

如果说 FileHeader 是用来记录文件的信息，那 PageHeader 就是用来记录当前数据页的状态信息了。

同样也是一堆 Key：

名称 大小（字节） 主要作用
PAGE_N_DIR_SLOTS 2 Page Directory 页目录中的槽数
PAGE_HEAP_TOP 2 堆中第一个记录的指针
PAGE_N_HEAP 2 堆中记录数
PAGE_FREE 2 空闲列表首指针
PAGE_GARBAGE 2 已删除的字节数
PAGE_LAST_INSERT 2 最后插入的位置
PAGE_DIRECTION 2 插入数据的方向
PAGE_N_DIRECTION 2 一个方向连续插入记录的数量
PAGE_N_RECS 2 该页记录数
PAGE_MAX_TRX_ID 8 当前页最大事务ID
PAGE_LEVEL 2 索引树中的位置 0x00代表叶节点
PAGE_INDEX_ID 8 当前页属于哪个索引的id
PAGE_BTR_SEG_LEAF 10 叶子节点中文件段首指针位置（B+的Root页中定义）
PAGE_BTR_SEG_TOP 10 非叶子节点中文件段首指针位置（B+的Root页中定义）

4.3 Infimum 和 Supremum 最值记录

Infimum 记录比当前页最小主键还要小的“值”，Supremum 记录比当前页最大值还要大的“值”。

值我打了双引号，并不是说他就是一个值，而是一个规定：一个页中，Infimum 记录的下一条记录就是本页中最小的记录，而本页中最大的记录的下一条记录就是 Supremum 记录 。 （这里的下一条 next_record 的字段是 Row Format 规定的头，后面讲行信息的头部消息的时候我会重新说一下这句的）

4.4 User Records 用户记录 和 Free Space 空闲空间

这两个放在一起说是有原因的，因为一个空的页，是没有 User Records 的，全部都是 Free Space。慢慢的插入数据的时候，User Records 慢慢增大，Free Space 慢慢减小，直到这个页塞满了数据，就开启下一页。

想了想，弄个动图演示一下：

4.5 Page Director 页目录

页目录 Page Director 存储了很多槽，用来存储最大最小记录的槽，InnoDB 就是通过这些槽来提高查询的速度的，下面会详细说。

4.6 File Trailer 页尾部

校验页数据用的地方，这个页有两个部分：

  1. 页的校验和

与 FileHeader 中的 FILE_PAGE_SPACE_OR_CHKSUM 值相对应，因为 FileHeader 在页的头部，被首先写入硬盘，当写入硬盘的过程中发现错误导致页不完整的时候，头部和尾部的值就会出现不相同的情况，这时候就需要根据日志来重新做这个页。

  1. 被最后修改时对应的日志序列位置（LSN）

也是用来做校验的，后面会说到。

五. 数据行

我们知道了，一个数据页是 16kb 的大小，而数据页中有一个很重要的部分就是 User Record 区域就是用来存放我们的数据的地方。我们所插入的数据，InnoDB 会根据规则，转变成一定的 行格式 插入到数据页中去，行格式除了存储我们生产的数据以外，还存储着一些额外的信息，用于加快查数据的速度（当然删和改也都以来着查，新增也会以来查来确定主要的位置，所以查是一个数据库中至关重要的功能）。

MySQL 发展到现在，已经有好几种 数据行 的格式了，分别有：Compact Redundant Dynamic 和 Compressed。

Redundant 是为了解决与之前版本的兼容性问题而存在的，剩下的 Dynamic 和 Compressed 本质上是 Compact 格式，只不过在处理溢出页的时候不同，只要说了 Compact 就相当于说了三种，至于什么是溢出页，在下面会详细说到。

首先来看看 Compact 的数据行包含了什么鬼：

image-20191121094525787

5.1 额外信息

这部分是 InnoDB 为了方便快速查找统计而新加进去的一些记录行额外信息，主要用来标记可变长字段的长度（比如 varchar 占用多长）、哪些定义可为 NULL 的列在这条记录中出现了 NULL 以及一部分有用的记录头信息。

接下来一个一个来看这些有什么作用，为了防止标题太多层级我直接使用跟这里一样的标题。

5.1.1 可变长字段的长度列表

在 Compact 中，把表定义的可变长字段，在当前数据行中所占用的实际长度，按照 逆序 存储他们当前的长度。

怎么说呢，举个例子来做吧：

1
2
3
4
5
6
CREATE TABLE user_db(
	id int primary key,
  name varchar(30) not null,
  addr varchar(50),
  sex char(1)
) CHARSET=ascii ROW_FORMAT=COMPACT;

这个表包含一个主键、一个不为空的 name，一个可为空的 addr，还有一个不可变长可为空的 sex。字符集是 ascii（就是包含简单字母的那个），以及指定行格式是 Compact。

然后我插入 5 个数据来玩玩：

1
2
3
4
5
INSERT INTO user_db (id, name, addr, sex)
	values(1, 'goudan', null, 'b'),
	(2, 'gousheng', 'guangzhou', 'g'),
	(3, 'xiaoming', null, null),
	(4, 'adan', 'tianhe', null);
1
2
3
4
5
6
7
8
9
10
mysql> SELECT * FROM user_db;
+----+----------+-----------+------+
| id | name     | addr      | sex  |
+----+----------+-----------+------+
|  1 | goudan   | NULL      | b    |
|  2 | gousheng | guangzhou | g    |
|  3 | xiaoming | NULL      | NULL |
|  4 | adan     | tianhe    | NULL |
+----+----------+-----------+------+
4 rows in set (0.00 sec)

好，终于搞定 demo 数据。

我们回到可变长字段的长度列表来看，就拿 id = 2 的数据来看吧：

因为在这个表中 name addr 都是可变长字段，name 的长度是 8，addr 的长度是 9 他们通过十六进制表示分别是：0×08 0×09 ，然后，可变长字段存储是逆序的，所有，存储在可变长字段列表里面的数据就是 0908

这里有两个地方需要注意的是

  1. 如果这个可变长的列，他的真实值是 NULL，则他不会被记录在此列表中，也就是说在 id = 3 的记录中，可变长字段的长度列表只需要记录 name 列的长度即可；
  2. 这个长度列表可能需要 1字节 也可能因为记录不下，而开辟了 2字节 的长度来记录，怎么说呢：

我们知道 1字节 数的二进制是 8 位，然后，第一位像 Protobuf 一样用来记录后面还有没有位数，所以真实使用存储长度的是后 7 位。而 7 位最大能表示多大长度呢：

image-20191121105157176

所以，如果这个长度他需要记录 小于等于 127 的长度的时候，使用 1字节，而如果记录的值 大于 127 的时候则使用 2字节。8位 的第一位用来标记，我后面还有没有 1字节 来一起记录长度，如果是 0 则表示我当前字节已经记录完所有的了，如果是 1 则表示我后面的 1字节 也是用来记录长度的。而如果长度，他 1字节 = 255 可以存储完毕，则使用 1字节。

那怎么大概计算字段占用多少字节的长度呢：

因为上面的 user_db 使用的是 ascii 来做记录的，让我们用臭名远扬的 utf-8 来说，我们知道，MySQL 中的 utf-8 是个假的，他其实只使用了 3字节 一个字符的方法来存储。

我们假设这个表只有一个列(varchar(MAXLEN))，那如果字符串的最大字节占用长度 = MAXLEN * 3，则如果最大占用字节 小于等于 255 的时候，使用一个字节来存储，如果他大于 255 则根据他是否 小于等于 127 也就是 127 / 3 = 42 个字符来看，如果小于等于127，则使用 1字节，否则使用 2字节。好累，弄个伪代码吧：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
/** 占用的最大字节数，LocalCharsetLeng是当前字符集的最大字节长度 */
let maxByteLeng = LocalCharsetLeng * MAXLEN;
/** 可变长字段列表的长度 */
let lengthOfVariableColumn = 0;
if (maxByteLeng <= 255) {
  lengthOfVariableColumn = 1;
} else {
  /** 她当前数据行存储的长度 */
  let lengthOfData = ...
  let byteLengthOfData = LocalCharsetLeng * lengthOfData;
  if (byteLengthOfData <= 127) {
    lengthOfVariableColumn = 1;
  } else {
    lengthOfVariableColumn = 2;
  }
}

5.1.2 NULL值列表

NULL值列表 也是按照列中可以为NULL的列逆序来排放的，直接上图更清晰：

我们加入要看 id = 3 的数据，他的 addr 和 sex 都为 NULL。

image-20191121151849940

所以从最后一位开始，对应列中第一个可以为 NULL 的列（被 NOT NULL 修饰的列），如果是 NULL 则为 1 否则为 0。

所以，NULL 值并不会占用存储空间。

5.1.3 记录头信息

这个可就很重要，因为他是构成索引搜索条件的基础。

记录头有以下这些头部信息，共占用 40字节 不同的字节数作用都不相同：

名称 占用大小 描述
占位符1 1 无作用
占位符2 1 无作用
delete_flag 1 标记记录是否被删除（也就是我们开发常说的逻辑删除）
min_rec_flag 1 B+树的每层非叶子节点中的最小记录都会添加该标记
n_owned 4 该记录拥有的记录数
heap_no 13 索引堆中该条记录的位置信息
record_type 3 记录类型：000普通记录 001B+树节点指针 010Infimum
011Supremum 1xx保留
next_record 16 页中玉东道下一条记录需要的偏移量

OK一起说完真实记录再来看看怎么搜索数据的吧

5.2 真实数据

真实数据的存储，都是存储有效的数据，即如果某个值没有数据，则不做存储。那他怎么知道谁是有的谁是没的，这就需要结合上面可变长字段列表、NULL值列表以及下一条记录的偏移量来确定了。

其实真实数据中，InnoDB 还会在真实数据的头部增加两个列：DB_TRX_ID DB_ROLL_PTR 分别是事务的指针以及回滚的指针（用于重做？）。如果当前的表没有定义主键的话，那么 InnoDB 还会真实数据的头部增加 ROW_ID 这个隐藏列来作为主键（为啥一定要主键？后面索引会说说的）

所以记录应该是这样的：

5.3 溢出页

针对 VARCHAR 定义的数据列最多能存储多少个数据？一个记录最大能存储 65535 个字节的数据，但是并不是真的可以存储，因为在上面看来，还需要记录变长字段以及 NULL值列表，如果被 NOT NULL 修饰，则还可以节省 1字节，所以假设我这个表只有一个列的情况下分为下面两种情况：

  1. 被 NOT NULL 修饰，存储 65533 个字节；
  2. 没有修饰，存储 65532 个字节。

那如果存储的数据超出一个页 16kb 的定义怎么办，那就需要额外的溢出页来存储数据了。

但是 Compact 格式的数据有点纠结，如果占用的字节数小于 768 字节的时候，存储在本页，如果超出了，则该页依然会记录前 768字节 的数据，然后再使用 20字节 来存储溢出页的地址以及字节数大小。

而 MySQL 5.7 使用的 InnoDB Plugin 默认的格式 Dynamic 以及新增的 Compress 格式则不同，他们只在当前记录这里记录溢出页的位置，将所有的数据都放在溢出页里面，而 Compress 还会将溢出页进行压缩处理，减少存储空间。

总结一下

主要了解了表的逻辑存储、数据页、数据行的格式。不过不用太担心没记住，后面我都会重复这里的内容来说接下来的所有事情的，毕竟这里面的一堆属性都是为后面 MySQL 的功能做铺垫。