MySQL底层的存储结构

微信搜索『coder-home』，或者扫一扫右侧的二维码，关注『程序猿集锦』。 了解更多干货分享，还有各类视频教程。 扫描它，带走我。

写在前面的话

你有没有想过这样一个问题：我们的数据在MySQL中是如何存放的？它是以什么样的组织方式存放在我们磁盘中的？

我们知道，数据是存放在表里面的，在表里面是一行一行存在的。那么这一行一行的数据怎么样在磁盘中存放的呢？表又是如何在磁盘上存放的？读完下面的文章，你就会对这个问题整体的认识。

InnoDB的存储结构

数据是放在表空间tablesapce中的，而表空间是段segment组成的，段又是由区extent组成的，区又是由页page组成的。page里面放的就是一行一行的数据。这样就组成了MySQL中innodb的存储结构。如下图所示：

Tablespace

tablespace就是我们平时所说的表空间。它是一个物理概念，对应到磁盘上，就是一个个数据文件。例如在我的MySQL的安装目录下面有一个名称为feng的数据库，该数据库下的表空间如下所示：

root@test:/var/lib/mysql/feng# pwd
/var/lib/mysql/feng	--------------------->这是我的数据库目录，数据名称为：feng
root@test:/var/lib/mysql/feng# ls -lstr
total 1464
  4 -rw-r----- 1 mysql mysql     67 Dec  6 14:24 db.opt
 12 -rw-r----- 1 mysql mysql   8674 Dec 24 10:51 t_innodb.frm # 表结构定义文件
 96 -rw-r----- 1 mysql mysql  98304 Dec 24 10:54 t_innodb.ibd # 表空间文件，里面存放数据和索引
 12 -rw-r----- 1 mysql mysql   8674 Dec 24 10:51 t_myisam.frm # 表结构定义文件
  4 -rw-r----- 1 mysql mysql   2048 Dec 24 10:54 t_myisam.MYI # 表索引文件
  4 -rw-r----- 1 mysql mysql    168 Dec 24 10:54 t_myisam.MYD # 表空间文件，里面存数据
root@test:/var/lib/mysql/feng#

从上面我们可以看出innodb存储引擎的表空间和myisam存储引擎的表空间，有一点不一样：innodb存储引擎的表空间对应的数据文件和索引是放在一个文件中的，而myisam存储引擎的表对应的数据文件和索引文件是两个分开的数据文件，这也是innodb表又称为IOT，索引组织表的一个原因，它的数据和索引是存放在一个数据文件中的。

这里对应的一个个数据文件.ibd和.MYD结尾的文件就是一个个表空间。我们可以看出这里面是一个表对应一个表空间。不同的表他们的表空间是分开的。并不像Oracle那样多个表共享一个表空间数据文件。其实在MySQL中也有和Oracle类似的存储方式，多个表共享一个表空间文件。这个是通参数innodb_file_per_table来控制的。

如下是查看MySQL中当前表空间文件是否独立的方式，这个参数是从MySQL5.6之后的版本才支持的，在5.6之前的版本中，是不支持独立表空间设置的，和Oracle一样多个表共享一个表空间数据文件。

mysql> show variables like 'innodb_file_per_table'; /* 当该参数为ON时，表示每一个表单独一个表空间文件；如果为OFF，表示多个表共享一个表空间文件。 */
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.07 sec)

mysql>

常见的表空间

我们经常遇到的表空间可以参考MySQLInnodb存储引擎的存储架构图：

从图中我们可以看到我们经常遇到的表空间有如下几类：

• System Tablespace：系统表空间，对应到磁盘上面的数据文件就是/var/lib/mysql/ibdata1，如下：

  root@test:/var/lib/mysql# ls -lstr ibdata*
  77824 -rw-r----- 1 mysql mysql 79691776 Dec 28 14:32 ibdata1
  root@test:/var/lib/mysql#
  

• Undo Tablespace：回滚表空间，默认这个空间是和系统表空间共用一个表空间的，它不会单独存在，和ibdata1系统表空间文件存在一起。但是在MySQL5.6版本以后，支持单独配置回滚表空间了。可以为其单独配置，使用参数innodb_undo_tablespaces来配置使用几个回滚表空间。如果安装MySQL的时候没有配置回滚表空间，那么查询的结果如下：

  mysql> show variables like '%undo%';
  +--------------------------+------------+
  | Variable_name            | Value      |
  +--------------------------+------------+
  | innodb_max_undo_log_size | 1073741824 |
  | innodb_undo_directory    | ./         |
  | innodb_undo_log_truncate | OFF        |
  | innodb_undo_logs         | 128        |
  | innodb_undo_tablespaces  | 0          |
  +--------------------------+------------+
  5 rows in set (0.02 sec)
  

  从提升MySQL性能的角度上来看，为了减少磁盘I/O的竞争，所以建议把回滚表空间和系统表空间分开存放，不让回滚表空间和系统表空间共用同一个数据表空间文件：ibdata1，可以使用参数innodb_undo_tablespaces参数配置回滚表空间的数据文件的数目。配置参数在/etc/mysql/my.cnf配置文件中如下：

  [mysqld]
  # 回滚表空间的配置
  innodb_max_undo_log_size = 100M
  innodb_undo_log_truncate = ON
  innodb_undo_logs = 128
  innodb_undo_tablespaces = 4
  

  配置后的结果在MySQL的命令行中查看如下：

  mysql> show variables like '%undo%';
  +--------------------------+-----------+
  | Variable_name            | Value     |
  +--------------------------+-----------+
  | innodb_max_undo_log_size | 104857600 |
  | innodb_undo_directory    | ./        |
  | innodb_undo_log_truncate | ON        |
  | innodb_undo_logs         | 128       |
  | innodb_undo_tablespaces  | 4         |
  +--------------------------+-----------+
  5 rows in set (0.01 sec)
  

  配置后可以查看到对应的回滚表空间的数据文件已经存在/var/lib/mysql/undo*，如下所示：

  root@test:/var/lib/mysql# ls -lstr undo*
  10240 -rw-r----- 1 mysql mysql 10485760 Dec 28 15:45 undo002
  10240 -rw-r----- 1 mysql mysql 10485760 Dec 28 15:45 undo001
  10240 -rw-r----- 1 mysql mysql 10485760 Dec 28 15:45 undo004
  10240 -rw-r----- 1 mysql mysql 10485760 Dec 28 15:45 undo003
  root@test:/var/lib/mysql#
  

  更多关于回滚表空间的问题参考：https://dev.mysql.com/doc/refman/5.7/en/innodb-undo-tablespaces.html

• Redo Log Tablespace：日志表空间，对应到磁盘上面的数据文件就是/var/lib/mysql/ib_logfile*，如下：

  root@test:/var/lib/mysql# ls -lstr ib_logfile*
  49152 -rw-r----- 1 mysql mysql 50331648 Dec  6 14:15 ib_logfile1
  49152 -rw-r----- 1 mysql mysql 50331648 Dec 28 14:32 ib_logfile0
  root@test:/var/lib/mysql#
  

• Temporary Tablespace：临时表空间，对应到磁盘上面的数据文件就是/var/lib/mysql/ibtmp1，如下：

  root@test:/var/lib/mysql# ls -lstr ibtmp*
  12288 -rw-r----- 1 mysql mysql 12582912 Dec 28 14:32 ibtmp1
  root@test:/var/lib/mysql#
  

• General Tablespace：一般表空间，就是平时我们用于存储自己业务表中的数据用的表空间文件。这里需要注意的是目前很少使用这种以便的表空间了，因为它是多张表共用一个数据表空间文件，如果数据量比较大的情况下经导致这个表空间数据文件会很大，导致备份、迁移、恢复等动作都很困难。尤其是当其中某一个表的数据损坏而引起所有的表数据都不可访问的情况。所以，推荐使用下面的独立表空间文件。

• File-Pre-Table Tablespace：它和上的General Tablespace的功能一样，就是用来存储我们的业务数据的表空间。但是它和上面的General Tablespace有一点不同，顾名思义，它是每一个表对应一个数据表空间文件，这样可以提高数据文件并发时的磁盘I/O，同时可以避免因为数据表被损坏导致的所有数据表都不可用的情况。在恢复的时候，备份的时候，都很方便。该功能开启的参数为：innodb_file_per_table=on。这也是目前MySQL5.7版本中默认的参数值。

Segment

段（Segment）由一个或多个区组成，区在文件系统是一个连续分配的空间（在 InnoDB 中是连续的 64 个页），不过在段中不要求区与区之间是相邻的。段是数据库中的分配单位，不同类型的数据库对象以不同的段形式存在。

Table表和Segment段之间的关系如下：

• 表是逻辑概念，段是物理存储概念。
• 一张普通的表，对应一个段。
• 一张表也可以有多个段，比如分区表，一个分区一个段。
• 多张表也可以共享一个段，比如簇表，多个簇表共享一个段。
  • 什么是簇表？待更新。
• 通常情况下，创建一个表会创建一个段，但是：表的创建，并不意味着一定会创建一个段，比如临时表的创建就不会创建段。
• 建立其他的数据库对象也会创建段，比如：视图、索引对应着视图段、索引段。

Extent

在 InnoDB 存储引擎中，一个区块分配 64 个连续的页。因为 InnoDB 中的页大小默认是 16KB，所以一个区的大小是 64*16KB=1MB。在任何情况下每个区大小都为1MB，为了保证页的连续性，InnoDB存储引擎每次从磁盘一次申请4-5个区。默认情况下，InnoDB存储引擎的页大小为16KB，即一个区中有64个连续的页。

Page

Page页是InnoDB存储引擎磁盘管理的最小单位，每个页默认16KB：16384Byte = 16KB，可以使用如下命令在MySQL中进行查看。

mysql> show variables like 'innodb_page_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| innodb_page_size | 16384 |
+------------------+-------+
1 row in set (0.02 sec)

在MySQL5.6之前的版本，这个参数是不支持动态修改的，如果想要修改，只能自己修改源码编辑才可以。

而在5.6版本之后，参数innodb_page_size已经支持动态的配置，支持4KB、8KB、16KB(默认值)、32KB、64KB。但是这个配置也仅仅是在数据库安装好之后初始化之前自行配置，当有数据已经存在之后，这个参数是不能修改的。除非把数据通过mysqldump导出来，重新初始化一个新的数据库环境，然后修改参数之后，把导出来的数据再次再导入进去。

page页再细粒度的划分，可以分为如下几种结构：

下面分别介绍一下page页中各个组成部分的含义。

• File Header：文件头信息，比较重要的信息有FIL_PAGE_PREV记录上一个page页和FIL_PAGE_NEXT下一个page页的位置信息，通过这两个信息，可以让所有的page页面组成一个双向链表：
  

  关于文件头File Header更为详细的内容参考如下图：
  

• Page Header：记录本页存储记录的状态信息，比如本页记录数量，槽数量，详细的信息参考下图：
  

• Infimun + Supermum Records：最小行与最大行记录，是虚拟记录，标记该page页中，存储的id最大的行和id最小的行记录。具体可以参考如下图的结构：
  

• User Records：用户真正的数据存储区域，这里真正存放用户的行数据，它占据了整个page页的大部分空间。以单链表的形式存储一条条行记录。如下图所示，他们在物理上不一定是有序的，可能刚开始是有序的，但是随着增删改的操作可能就无序了，但是在逻辑上是有序的：
  

  一个page页中的多行记录，再结合多个page页，就形成如下的存储结构：页与页直接是双向链表，页内的行记录直接是单向链表。如下所示：page页中的每一个箭头可以理解为一行数据。
  

  基于上面的图，当我们要查询某一行记录的时候，是通过下面的过程来查找的。

  1. 通过根节点开始遍历一个索引的B+树，通过各层非叶子节点达到底层的叶子节点的数据页（Page），这个Page内部存放的都是叶子节点
  2. 在Page内部从“Infimum”节点开始遍历单链表（遍历一般会被优化），如果找到键则返回。
  3. 如果遍历到了“Supremum”，说明当前Page里没有合适的键，这时借助Page页内部的next page指针，跳转到下一个page继续从“Infmum”开始逐个查找。

• Free Space：存数据空间中尚未使用的区域，该页中剩余的空间，用于存放后续插入的数据。

• Page Directory：页目录，页中某些记录的相对位置，用于提升查询效率。我们要在一个页中查找指定的一条记录。除了从头遍历还有更高效率的方法么？Page Directory提供了解决方案。

  InnoDB会将一个页中的所有记录划分成若干个组，每组4-8个记录。将每个组最后一个记录相对于第一个记录的地址偏移量（可以定位到真实数据记录）提取出来存放在页中一个叫做Page Directory的数组中，数组中的元素就是这些地址偏移量，也称为槽(slot)。所以Page Directory就是由槽组成的。

  所以在一个页中根据主键查找记录是很快的，步骤为：

  1. 二分法确定该记录所在的槽，并找到该槽所在分组中主键值最小的那条记录。
  2. 通过next_record属性遍历单链表找到记录

  注意：二分法，适用于数组。

  链表是顺序存取，不是随机存取，用二分查找并不能提高查找效率，因为你每次还得从第一个结点出发，找到指针LOW,HIGH,MIDDLE所指的元素，所以一般不在链表内使用二分查找。

• File Trailer：文件尾，刷盘时校验页是否完整。详细内参考下图：
  

什么是off-page

MySQL的表中存储数据的时候，数据是一行一行的存储的。这个行要落在innodb的最小存储单位：page页中。好比我们的书本中的一行一行的文字是在页中，一个页里面有很多行。MySQL中的page页，就是用来存储多个行的基本单位。

但是如果一个行特别的大，大于了16KB的大小，那么此时一个page页，就容纳不下这个行了，此时就要在用2个甚至更多的page页来存储这个行的数据，这种现象就是off-page，即行溢出，off-page是指一个表的单行的大小超过了MySQL默认的一个page页的大小。一个行，要占用多个页来存储对于这种现象，在不同的行存储格式下面会有不同的处理方式，下面会有详细的介绍。默认的方式是将多余的数据需要在overflow-page溢出页中存储。

InnoDB的文件存储格式

InnoDB存储引擎有两种文件存储格式：Antelope和Barracuda，而这两种文件存储格式下，有分别支持两种行存储格式。

1. Antelope(羚羊)：Compact(紧凑的)与Redundant(冗余的)两种行记录格式

   1. compact：在存储大的数据字段的时候，比如blob、text类型的字段，涉及到行溢出的问题。它在存储text大字段的时候，会在一个page页中存储前768个字节，后面的字节会存储在溢出页``overflow page`中。
   2. redundant：是最早的一种存储格式，相比compact要占用更多的存储空间。现在级别已经废弃。

2. Barracuda(梭鱼)：Dynamic(动态的)和Compress(压缩的)还支持compact、redundant两种。

   1. dynamic：这种行存储方式是目前MySQL5.7版本后默认的行存储格式。它在存储大字段的时候，只会在page页中存储一个指向溢出页的一个20个字节的物理指针，而不会真正的去存放大字段的内容。真正的字段内容存储在溢出页overflow page中。这种方式，针对溢出列所在的新页利用率更高，查询的效率会减少磁盘的I/O交互次数，提高查效率。

   2. compress：相比dynamic，除了基本功能和dynamic一样之外，它是把字段内容以压缩的方式存储在page页中，但是这种压缩只是在物理存储上的压缩。在需要查询对应的字段内容的时候，需要从物理的page页面中，读取到内存中的数据需要进行相应的解压缩的操作，这样就需要大量的CPU的支持，降低的数据库的TPS，影响数据库的响应时间，这是一种以时间来换取空间的思想。

      而在当前磁盘存储空间不是瓶颈的前提下，这种方式一般不被大家所认可了。因为磁盘价格也不贵，花费时间在CPU解压数据上而换取节省磁盘空间的成本。这是一种得不偿失的做法。

   注意：在Barracuda文件存储格式下，也是支持compact和redundant这两种行存储格式的，这个是为了将文件存储格式从Antelope向Barracuda慢慢过度才支持的。

查看MySQL数据库innodb存储引擎使用的文件格式和行存储格式的命令如下：

mysql> show variables like 'innodb_file%';
+--------------------------+-----------+
| Variable_name            | Value     |
+--------------------------+-----------+
| innodb_file_format       | Barracuda |
| innodb_file_format_check | ON        |
| innodb_file_format_max   | Barracuda |
| innodb_file_per_table    | ON        |
+--------------------------+-----------+
4 rows in set (0.01 sec)

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

微信搜索『coder-home』，或者扫一扫右侧的二维码，关注『程序猿集锦』。 了解更多干货分享，还有各类视频教程。 扫描它，带走我。