MySQL - 表空间详解

一七令
433 阅读5分钟

共享表空间

共享表空间也叫系统表空间，用于存储数据字典、双写缓冲区（doublewrite buffer）、更改缓冲区（hange buffer）和重做日志（undo log）等。

可通过 SHOW VARIABLES LIKE 'innodb_data_file_path' 查看，若不设定则 Innodb 在初始化时候会创建一个稍大于12MB的单个自动扩展数据文件，并命名为 ibdata1。

mysql> SHOW VARIABLES LIKE 'innodb_data_file_path';
+-----------------------+------------------------+
| Variable_name         | Value                  |
+-----------------------+------------------------+
| innodb_data_file_path | ibdata1:12M:autoextend |
+-----------------------+------------------------+

共享表空间默认存放在 datadir 指定的目录下，可通过 innodb_data_home_dir 指定。如有下设置：

innodb_data_home_dir = /myibdata/
innodb_data_file_path=ibdata1:50M:autoextend

共享表空间完整的命名规范必须包括文件名、文件大小、autoextend 属性和 max 属性，且最后只能设置为： autoextend 或者 max。

其中文件大小以千字节（K）、兆字节(M)或千兆字节(G)为单位，且若以K为单位必须设定为1024的整数倍，否则将自动四舍五入到最接近的M值。

file_name:file_size[:autoextend[:max:max_file_size]]

e.g 1: 
# 指定autoextend属性后，数据文件的大小会随着空间的需要自动增加 64MB.
# 大小可由参数 innodb_autoextend_increment  控制。
innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend

e.g 2: 
# 若要指定自动扩展数据文件的最大大小，在 autoextend 属性后面增加 max 属性。
# 如下代表允许ibdata1增长到 500MB 大小。 
innodb_data_file_path=ibdata1:12M:autoextend:max:500M

若不开启独立表空间，那所有表的数据、索引等数据都存放在共享表空间中。这样做有好处也有缺点。

优点：

  1. 单表空间不受操作系统单文件大小限制，因为可以将一个表数据分布到不同的 ibddata 文件中。
  2. 方便管理，文件相对不多不会存在过多的句柄。

缺点:

  1. 磁盘空间使用效率低，如删数据后可能出现空间不能复用情况导致碎片化严重。
  2. 多表公用一个文件时并发时可能出现IO 瓶颈，所以需要频繁写入的场景并不适合共享表空间。

独立表空间

独立表空间是将InnoDB表及其索引都存储在一个单独的 you_table_name.ibd 数据文件中，并使用 you_table_name.frm 存放描述文件。

每个.ibd 文件代表一个单独的表空间。这个特性是由参数：innodb_file_per_table 配置选项控制的，在MySQL 5.6.6及更高版本中默认启用。

innodb_file_per_table=ON

独立表空间默认存放在 innodb_data_home_dir 下以具体数据库名为目录名的目录下，如在 Test 库下创建了 test 表：

drwx------  5 root  root    160 Jun 12 03:51 ./
drwxr-xr-x 16 mysql root    512 Jun 12 03:50 ../
-rw-r-----  1 root  root     67 Jun 12 03:50 db.opt
-rw-r-----  1 root  root   1966 Jun 12 03:50 test.frm
-rw-r-----  1 mysql mysql 98304 Jun 12 03:50 test.ibd

凡事有好坏，独立表空间也有着有缺点：

优点：

  1. 使用 truncate table 或 drop table 时候，可以释放、回收磁盘空间。而使用共享表空间的则仅在系统表空间数据文件(ibdata文件)内部创建空闲空间。
  2. 可以将表指定到单独的磁盘中，如将查询热点表单独存到一个磁盘中以优化查询；在如将一个大表单独享用一个独立磁盘等等。create table… data directory=absolute_path_to_directory（单独指定的磁盘路径）
  3. 当使用 optimize table 优化重建表时候，会新建一个只存放需要保留的数据的文件.ibd，并将新 .ibd 文件替换旧的 .ibd。不需要在共享表空间操作，不影响其他表的使用。
  4. 支持可迁移表空间特性，可以对单独表的迁移。如将一个 .ibd文件和.frm 复制到其他的数据库实例中就能使用。
  5. 可以在操作系统层面就能知道单表的使用大小，而不用通过mysql。
  6. 当 innodb_flush_method=O_DIRECT，Linux文件系统不允许并发写入单个文件。所以使用独立表空间就比使用共享表空间更具优势。

缺点：

  1. 当进行 fsync 时候是在独立表空间文件上进行，所以不能像使用共享表空间那样可以将多个 I/O 合并成一个进行操作。一定程度上会增加较多的 fsync 调用。
  2. mysqld 会为每一个表保留一个文件句柄，若表过多的情况下可能会影响性能。
  3. 单表大小会受操作系统单文件大小限制。

可以结合共享表空间和独立表空间优缺点和具体的业务特点选择是否开启独立表空间，当然也可以将共享表空间中的表抽离成独立表空间。

一般有两种方式可以采用，在此之前都需要开启独立表空间。

innodb_file_per_table=ON

两种方式分别是：

  1. 逻辑备份后，重启数据库后导入。
  2. 重启数据库并将需要抽离的表执行 alter table you_table_name engine=innodb，该方法只会对新表使用独立表空间，旧表还是会使用共享表空间。

临时表空间

默认情况下 innodb 会在 innodb_data_home_dir目录创建一个 12MB 的 ibtmp1 文件，用于存放查询时候产生临时表。

可以使用 innodb_temp_data_file_path选项指定临时表空间数据文件的路径、文件名和文件大小。

其设置方式和共享表空间一致，如：ibtmp1:12M:autoextend。

在使用大型临时表或广泛使用临时表的环境中，自动扩展临时表空间数据文件可能会变得很大。 大型数据文件也可能来自使用临时表的长时间运行的查询。为防止临时数据文件变得过大，可配置 innodb_temp_data_file_path选项以指定最大数据文件大小。

avatar
文章
阅读
粉丝