mysql

MySQL 系列 数据存储

剖析MySQL InnoDB 数据存储的结构

Posted by lichao modified on January 12, 2024

表是关于特定实体的数据集合，也是关系型数据库模型的核心。

表中所有数据都被逻辑地存放在一个空间中，称为表空间。表空间由段、区、页组成。InnoDB 存储引擎的逻辑存储结构如图:

逻辑存储结构

表结构定义

在MySQL8.0版本以前，表结构是存在以.frm为后缀的文件里。而MySQL8.0版本，则已经允许把表结构定义放在系统数据表中了。

表空间

表数据既可以存在共享表空间里，也可以是单独的文件。这个行为是由参数innodb_file_per_table控制：

  • OFF:表的数据放在系统共享表空间，也就是跟数据字典放在一起；
  • ON:每个InnoDB表数据存储在一个以 .ibd为后缀的文件中；

从MySQL5.6.6版本开始，它的默认值就是ON了。独立表空间只存放数据、索引和插入缓存bitmap页。其他的数据，如undo log(回滚信息)、插入缓存索引页、系统事务信息、二次写缓存等还是存放在共享表空间中。

共享表空间

共享表空间可以由很多个文件组成，一个表可以跨多个文件存在，所以其大小限制不再是文件大小的限制，而是其自身的限制。从 InnoDB 的官方文档中可以看到，其表空间的最大限制为 64TB，也就是说，InnoDB 的单表限制基本上也在 64TB 左右了，当然这个大小是包括这个表的所有索引等其他相关数据。

共享表空间优点: 表空间可以分成多个文件存放到各个磁盘，所以表也就可以分成多个文件存放在磁盘上，表的大小不受磁盘大小的限制。

共享表空间缺点: -所有的数据和索引存放到一个文件，虽然可以把一个大文件分成多个小文件，但是多个表及索引在表空间中混合存储，当数据量非常大的时候，表做了大量删除操作后表空间中将会有大量的空隙，特别是对于统计分析，对于经常删除操作的这类应用最不适合用共享表空间。 -共享表空间分配后不能回缩：当出现临时建索引或是创建一个临时表的操作表空间扩大后，就是删除相关的表也没办法回缩那部分空间了，进行数据库的冷备很慢；

独立表空间

每个表都有自已独立的表空间文件，每个表的数据和索引都会存在自已的表空间中，可以实现单表在不同的数据库中移动。 独立表空间只存储该表的数据、索引、插入缓存 bitmap 等信息，其余信息还是存入默认的表空间中

独立表空间优点: -一个表单独存储为一个文件更容易管理，可以通过drop table;命令，系统就会直接删除这个文件。而如果是放在共享表空间中，即使表删掉了，空间也是不会回收的。 -删除大量数据后可以通过alter table TableName engine=innodb;命令重建表回缩不用的空间。 -对于使 innodb_plugin 的 Innodb 使用 turncate table 也会使空间收缩。

独立表空间缺点: -如果单表单表占用空间过大，存储空间不足，只能从操作系统层面思考解决方法。

表空间存储结构

段

表空间由各个段组成:

  • 数据段：即B+树的叶子节点
  • 索引段：即B+树的非索引节点
  • 回滚段

区

由连续页组成的空间，在任何情况下每个区的大小都是1MB。

在默认情况下，InnoDB存储引擎每个数据页的大小默认是16KB，即一个区中一共有64个连续的页。 在启动参数innodb_file_per_table后，在每个段开始时，先用32个页大小的碎片页来存放数据，在使用完这些页之后才是64个连续页的申请。

页

InnoDB数据是按数据页为单位来读写的。也就是说，当需要读一条记录的时候，并不是将这个记录本身从磁盘读出来，而是以页为单位，将其整体读入内存。在InnoDB中，每个数据页的大小默认是16KB，可通过innodb_page_size将页的大小设置为 4K、8K、16K。 数据页

InnoDB 数据页由以下 7 个部分组成：

  • file header：文件头部，页的一些通用信息
  • page header：页面头部，数据页专有的一些信息
  • Infimum + Supremum：最小记录和最大记录，两条虚拟的行记录
  • user records：用户记录，实际存储的行记录内容
  • free space：空闲空间，页中尚未使用的空间
  • page directory：页面目录，页中的某些记录的相对位置
  • file trailer：文件尾部，校验页是否完整

其中file header、page header、file trailer的大小是固定的，分别为38、56、8字节，这些空间用来标记该页的一些信息。user records、free spare、page directory这些部分为实际的行记录存储空间，因此大小是动态的

行

InnoDB 存储引擎是面向列的，也就是数据是按行进行存放的。每个页允存放的行记录有硬性规定，最多允许存放 16KB/2-200 = 7992 行的记录。

记录是以行的形式存储的，这些记录在磁盘上的存放方式也被称为行格式或者记录格式。页中保存着表中一行行的数据，数据库实例的作用之一就是读取页中存放的行记录。

目前InnoDB存储引擎主要有4种不同类型的行格式，分别是Compact、Redundant、Dynamic 和 Compressed行格式。

Compact行格式

Compact行记录的设计目标是高效的存储数据（一个页中存储的行数据越多，其性能就越高），格式如下：

存储概览

  1. 变长字段长度列表: 按照列的顺序逆序放置变长字段长度，长度存储最大不超过2字节（varcahr类型的最大长度限制为65535，mediumtext\longtext类型的长度放在了溢出区了）；值为NULL的变长字段不用存储占用字节数。
  2. NULL标识位：该标识的每个二进制位指示了该行数据中对应字段是否为NULL值，有则用1； 存储概览
  3. 记录头信息：固定占用5字节（40位）；
    • 预留位1：暂未使用
    • 预留位2：暂未使用
    • delete_mask：标记该记录是否被删除，占用1个二进制位，值为0的时候代表记录并没有被删除，为1的时候代表记录被删除掉了。
    • min_rec_mask：B+ 树的每层非叶子节点中的最小记录都会添加该标记
    • n_owned：表示当前记录组拥有的记录数
    • heap_no：表示当前记录在记录堆的位置信息
    • record_type：表示当前记录的类型，0 表示普通记录，1 表示 B+ 树非叶子节点记录，2 表示最小记录，3 表示最大记录
    • next_record：表示下一条记录的相对位置
  4. 列数据：实际存储每列的数据，NULL不占该部分任何空间，即NULL仅占有NULL标志位，实际存储不占任何空间；
  5. 隐藏列：事物ID列(transaction_id)和回滚指针列(roll_pointer)，分别为6字节和7字节的大小，若InnoDB表没有定义主键，每行还未增加一个6字节的row_id列；

innodb 存储引擎在页内部是通过一种链表的结构来串联各个行记录的；

Redundant 行格式

Redundant 行格式是MySQL5.0 之前用的一种行格式，也就是说它已经过时了。该行格式结构如图所示： redundant

  1. 字段长度偏移列表：该条记录中所有列（包括隐藏列）的长度信息都按照逆序存储到字段长度偏移列表。它是采用两个相邻数值的差值来计算各个列值的长度。
    • 将列对应的偏移量值的第一个比特位作为是否为 NULL 的依据，该比特位也可以被称之为 NULL 比特位。也就是说在解析一条记录的某个列时，首先看一下该列对应的偏移量的 NULL 比特位是不是为 1，如果为 1，那么该列的值就是 NULL，否则不是 NULL。
  2. Redundant 行格式的记录头信息占用 6 字节，48 个二进制位，这些二进制位代表的意思如下：
    • 预留位1：暂未使用
    • 预留位2：暂未使用
    • delete_mask：标记该记录是否被删除
    • min_rec_mask：B+ 树的每层非叶子节点中的最小记录都会添加该标记
    • n_owned：表示当前记录组拥有的记录数
    • heap_no：表示当前记录在记录堆的位置信息
    • n_field：表示记录中列的数量
    • 1byte_offs_flag：标记字段长度偏移列表中每个列对应的偏移量是使用 1 字节还是 2 字节表示的
      • 当记录的真实数据占用的字节数不大于 127（十六进制 0x7F，二进制 01111111）时，每个列对应的偏移量占用 1 个字节。
      • 当记录的真实数据占用的字节数大于 127，但不大于 32767（十六进制 0x7FFF，二进制 0111111111111111）时，每个列对应的偏移量占用 2 个字节。
      • 记录的真实数据大于 32767 的情况，记录已经存放到了溢出页中，在本页中只保留前768 个字节和 20 个字节的溢出页面地址（当然这 20 个字节中还记录了一些别的信息）。因为字段长度偏移列表处只需要记录每个列在本页面中的偏移就好了，所以每个列使用 2 个字节来存储偏移量就够了。
    • next_record：表示下一条记录的相对位置

行溢出数据

对于 VARCHAR(M) 类型的列最多可以占用 65535 个字节。其中的 M 代表该类型最多存储的字符数量，如果使用 ascii 字符集的话，一个字符就代表一个字节，看看 VARCHAR(65535) 是否可用：

1
2
3
4
5

mysql> CREATE TABLE varchar_size_demo(
    ->     c VARCHAR(65535)
    -> ) CHARSET=ascii ROW_FORMAT=Compact;
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
mysql>

从报错信息里可以看出，MySQL 对一条记录占用的最大存储空间是有限制的，除了 BLOB 或者 TEXT 类型的列之外，其他所有的列（不包括隐藏列和记录头信息）占用的字节长度加起来不能超过 65535 个字节。所以 MySQL 服务器建议把存储类型改为 TEXT 或者 BLOB 的类型。这个 65535 个字节除了列本身的数据之外，还包括一些其他的数据（storage overhead），比如说为了存储一个 VARCHAR(M) 类型的列，其实需要占用 3 部分存储空间：

  • 真实数据
  • 真实数据占用字节的长度
  • NULL 值标识，如果该列有 NOT NULL 属性则可以没有这部分存储空间

如果该 VARCHAR 类型的列没有 NOT NULL 属性，那最多只能存储 65532 个字节的数据，因为真实数据的长度可能占用 2 个字节，NULL值标识需要占用 1 个字节。

如果 VARCHAR 类型的列有 NOT NULL 属性，那最多只能存储 65533 个字节的数据，因为真实数据的长度可能占用 2 个字节，不需要NULL值标识。

如果 VARCHAR(M) 类型的列使用的不是 ascii 字符集，那 M 的最大取值取决于该字符集表示一个字符最多需要的字节数。在列的值允许为 NULL 的情况下，gbk 字符集表示一个字符最多需要 2 个字节，那在该字符集下，M 的最大取值就是 32766（也就是：65532/2），也就是说最多能存储 32766 个字符；utf8 字符集表示一个字符最多需要 3 个字节，那在该字符集下，M的最大取值就是 21844，就是说最多能存储 21844（也就是：65532/3）个字符。

MySQL 中磁盘和内存交互的基本单位是页，也就是说 MySQL 是以页为基本单位来管理存储空间的，记录都会被分配到某个页中存储。而一个页的大小一般是 16KB，也就是 16384 字节，而一个 VARCHAR(M) 类型的列就最多可以存储 65533 个字节，这样就可能造成一个页存放不了一条记录的尴尬情况。

在 Compact 和 Redundant 行格式中，对于占用存储空间非常大的列，在记录的真实数据处只会存储该列的一部分数据，把剩余的数据分散存储在几个其他的页中，然后记录的真实数据处用 20 个字节存储指向这些页的地址（当然这20 个字节中还包括这些分散在其他页面中的数据的占用的字节数），从而可以找到剩余数据所在的页。假设我们有一张只有一个列的表，它的某条记录的列值很大，超过了行溢出的临界点，那么该表结构如图所示： 溢出列

Dynamic 和 Compressed 行格式

这俩行格式和 Compact 行格式挺像，只不过在处理行溢出数据时有点儿分歧，它们不会在记录的真实数据处存储字段真实数据的前 768 个字节，而是把所有的字节都存储到其他页面中，只在记录的真实数据处存储其他页面的地址，就像这样：

Dynamic

Compressed 行格式和 Dynamic 不同的一点是，Compressed 行格式会采用压缩算法对页面进行压缩，以节省空间。

行数据删除

假设要删掉R这行记录，InnoDB引擎只会把R这个记录标记为删除。之后要再插入记录时，可能会复用这个位置（只限于符合条件的数据，需要满足数据的有序性）。但是，磁盘文件的大小并不会缩小。

假如删掉了一个数据页上的所有记录，整个数据页就可以被复用了。而且当整个页从B+树里面摘掉以后，可以复用到任何位置。

  • 如果相邻的两个数据页利用率都很小，系统就会把这两个页上的数据合到其中一个页上，另外一个数据页就被标记为可复用。
  • 如果用delete命令把整个表的数据删除，所有的数据页都会被标记为可复用。但是磁盘上，文件不会变小。

如果数据是按照索引递增顺序插入的，那么索引是紧凑的。但如果数据是随机插入的，就可能造成索引的数据页分裂，这也是会造成空洞的。

另外，更新索引上的值，可以理解为删除一个旧的值，再插入一个新值，这也是会造成空洞的。

综合来说，经过大量增删改的表，都是可能是存在空洞的。如果能够把这些空洞去掉，就能达到收缩表空间的目的。而重建表，就可以达到这样的目的。

可通过重建表提高数据页的利用率。

相关命令

  • 查看配置文件的位置，mysql 实例会按一定的顺序在指定的位置进行读取配置文件：

mysql --help | grep my.cnf

  • 查看数据库中所有的参数,可以通过like来过滤参数名：
1
2

show variables    
show variables like 'XXX%'

  • 定位错误日志文件： show variables like 'log_error'

  • 修改动态参数：

1
2

set [global | session] system_var_name = expr       
set [@@global. | @@session. @@] system_var_name =expr

global和session关键字表明该参数的修改是基于当前会话还是整个实例的生命周期。

  • 查询动态参数：
1
2

get [global | session] system_var_name = expr     
get [@@global. | @@session. @@] system_var_name =expr

global和session关键字表明该参数的修改是基于当前会话还是整个实例的生命周期。

  • 开启慢查询（默认情况下，mysql并不启动慢查询日志）： show variables like 'long_query_time'

  • mysqldumpslow命令 mysqldumpslow -s al -n 10 david.log 得到执行时间最长的10条sql语句。

  • 查看二进制日志中的记录： show binlog event

  • 查看当前数据库的表空间管理类型： show variables like "innodb_file_per_table";

CATALOG
    FEATURED TAGS
    redis go mysql algorithm distribute jvm network spring java kafka mongo mq concurrent index design_pattern io rocket_mq store netty os concurrency nosql mesh etcd graph hbase kitex lang zookeeper
    FRIENDS