你有没有想过这样一个问题:我们的数据在MySQL中是如何存放的?它是以什么样的组织方式存放在我们磁盘中的?
我们知道,数据是存放在表里面的,在表里面是一行一行存在的。那么这一行一行的数据怎么样在磁盘中存放的呢?表又是如何在磁盘上存放的?读完下面的文章,你就会对这个问题整体的认识。
数据是放在表空间tablesapce中的,而表空间是段segment组成的,段又是由区extent组成的,区又是由页page组成的。page里面放的就是一行一行的数据。这样就组成了MySQL中innodb的存储结构。如下图所示:
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存储引擎的存储架构图:
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)由一个或多个区组成,区在文件系统是一个连续分配的空间(在 InnoDB 中是连续的 64 个页),不过在段中不要求区与区之间是相邻的。段是数据库中的分配单位,不同类型的数据库对象以不同的段形式存在。
Table表和Segment段之间的关系如下:
在 InnoDB 存储引擎中,一个区块分配 64 个连续的页。因为 InnoDB 中的页大小默认是 16KB,所以一个区的大小是 64*16KB=1MB。在任何情况下每个区大小都为1MB,为了保证页的连续性,InnoDB存储引擎每次从磁盘一次申请4-5个区。默认情况下,InnoDB存储引擎的页大小为16KB,即一个区中有64个连续的页。
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 innodb page structure.jpg
下面分别介绍一下page页中各个组成部分的含义。
page双向链表.png
关于文件头File Header更为详细的内容参考如下图:
page infimum and supremum.jpg
page内数据行存储的方式.png
page页和页之间的关系.png
MySQL的表中存储数据的时候,数据是一行一行的存储的。这个行要落在innodb的最小存储单位:page页中。好比我们的书本中的一行一行的文字是在页中,一个页里面有很多行。MySQL中的page页,就是用来存储多个行的基本单位。
但是如果一个行特别的大,大于了16KB的大小,那么此时一个page页,就容纳不下这个行了,此时就要在用2个甚至更多的page页来存储这个行的数据,这种现象就是off-page,即行溢出,off-page是指一个表的单行的大小超过了MySQL默认的一个page页的大小。一个行,要占用多个页来存储对于这种现象,在不同的行存储格式下面会有不同的处理方式,下面会有详细的介绍。默认的方式是将多余的数据需要在overflow-page溢出页中存储。
InnoDB存储引擎有两种文件存储格式:Antelope和Barracuda,而这两种文件存储格式下,有分别支持两种行存储格式。
注意:在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)