<返回更多

MySQL底层的存储结构

2020-12-30    
加入收藏

写在前面的话

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

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

 

InnoDB的存储结构

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

MySQL底层的存储结构

 

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存储引擎的存储架构图：

MySQL底层的存储结构

innodb-architecture.png

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

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#
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
[mysqld]
# 回滚表空间的配置
innodb_max_undo_log_size = 100M
innodb_undo_log_truncate = ON
innodb_undo_logs = 128
innodb_undo_tablespaces = 4
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)
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#
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#
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#

 

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页再细粒度的划分，可以分为如下几种结构：

MySQL底层的存储结构

mysql innodb page structure.jpg

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

MySQL底层的存储结构

page双向链表.png

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

MySQL底层的存储结构

 

MySQL底层的存储结构

 

MySQL底层的存储结构

page infimum and supremum.jpg

MySQL底层的存储结构

page内数据行存储的方式.png

MySQL底层的存储结构

page页和页之间的关系.png

MySQL底层的存储结构

 

什么是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)
声明：本站部分内容来自互联网,如有版权侵犯或其他问题请与我们联系，我们将立即删除或处理。
▍相关推荐
更多资讯 >>>
Copyright @ 简易百科 V 2.0 京ICP备14042104号