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

一.InnoDB的表和数据

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

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

Innodb 存储引擎表在创建的时候,如果没有显示定义主键(Primary Key),则会通过以下的规则创建主键:

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

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

他们的大致关系是:

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

二.表空间

2.1 表的物理存储

数据、表、数据库文件

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

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

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

[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

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

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 的表,现在我们重新来看看数据文件里面多了些什么东西:

[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 瞅瞅有哪些东西:

[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.ibdInnoDB 存储引擎的数据库文件;

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

[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

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 中的配置,来定义存储的表空间文件:

[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)。

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

四. 数据页的结构

这是一个页的结构:

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

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_INDEXB+树 叶节点
  2. FILE_PAGE_UNDO_LOGUndo Log
  3. FILE_PAGE_INODE :索引节点
  4. FILE_PAGE_IBUF_FREE_LISTInsert Buffer 空闲列表
  5. FILE_PAGE_TYPE_ALLOCATED :最新分配的页
  6. FILE_PAGE_BITMAPInsert 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_BLOBBLOB

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 慢慢减小,直到这个页塞满了数据,就开启下一页。

想了想,弄个动图演示一下:

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

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 DynamicCompressed

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

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

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

5.1 额外信息

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

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

5.1.1 可变长字段的长度列表

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

怎么说呢,举个例子来做吧:

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 个数据来玩玩:

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);
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 的长度是 8addr 的长度是 9 他们通过十六进制表示分别是:0×08 0×09 ,然后,可变长字段存储是逆序的,所有,存储在可变长字段列表里面的数据就是 0908

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

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

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

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

所以,如果这个长度他需要记录 小于等于 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字节。好累,弄个伪代码吧:

/** 占用的最大字节数,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 的数据,他的 addrsex 都为 NULL

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

所以从最后一位开始,对应列中第一个可以为 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 这个隐藏列来作为主键(为啥一定要主键?后面索引会说说的)

所以记录应该是这样的:

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

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 的功能做铺垫。

点赞