每表文件表空间
历史上,InnoDB
表存储在 系统表空间中。这种整体方法针对的是专用于数据库处理的计算机,该计算机具有经过精心计划的数据增长,而分配给MySQL的任何磁盘存储都不会出于其他目的。该文件的每个表的表空间特征提供了一种更灵活的选择,其中,每个InnoDB
表被存储在其自己的表空间的数据文件(.ibd
文件)。此功能由innodb_file_per_table
配置选项控制 ,默认情况下已启用。
好处
-
截断或删除每个表文件表空间中存储的表时,可以回收磁盘空间。截断或删除存储在共享 系统表空间中的表会在系统表空间数据文件(ibdata文件)内部创建可用空间,该空间只能用于新
InnoDB
数据。同样,
ALTER TABLE
对驻留在共享表空间中的表进行表复制操作会增加表空间使用的空间量。此类操作可能需要与表中的数据加索引一样多的额外空间。表复制ALTER TABLE
操作所需的额外空间 不会像每表文件表空间那样释放回操作系统。 -
TRUNCATE TABLE
在每个表文件表空间中存储的表上运行时, 该操作更快。 -
您可以使用语法指定每个表的位置,以将特定表存储在单独的存储设备上,以实现I / O优化,空间管理或备份目的 ,如第15.6.3.6节“在数据目录之外创建表空间”所述”。
CREATE TABLE ... DATA DIRECTORY =
absolute_path_to_directory
-
您可以运行
OPTIMIZE TABLE
来压缩或重新创建每表文件表空间。运行时OPTIMIZE TABLE
, 仅使用存储实际数据所需的空间InnoDB
创建一个.ibd
具有临时名称的新 文件。优化完成后,请InnoDB
删除旧.ibd
文件,然后将其替换为新文件。如果先前的.ibd
文件显着增长,但是实际数据仅占其大小的一部分,则运行OPTIMIZE TABLE
可以回收未使用的空间。 -
您可以移动单个
InnoDB
表而不是整个数据库。 -
您可以将单个
InnoDB
表从一个MySQL实例复制到另一个(称为可 传输表空间功能)。 -
您可以使用动态行格式为具有大表
BLOB
或TEXT
列的表启用更有效的存储 。 -
每表文件表空间可以增加成功恢复的机会,并在发生损坏,无法重新启动服务器或备份和二进制日志不可用时节省时间。
-
您可以使用MySQL企业备份产品快速备份或还原单个表,而不会中断其他
InnoDB
表的使用。如果您的表需要较少的备份频率或以不同的备份时间表进行备份,这将很有用。有关详细信息,请参见进行部分备份。 -
每表文件表空间在复制或备份表时便于按表状态报告。
-
您可以在文件系统级别监视表大小,而无需访问MySQL。
-
当通用的Linux文件系统不允许同时写一个文件
innodb_flush_method
设置为O_DIRECT
。因此,当结合使用每表文件表空间和时,可能会提高性能innodb_flush_method
。 -
系统表空间存储数据字典和撤消日志,并且其大小受
InnoDB
表空间大小限制。请参见 第15.6.1.6节“ InnoDB表的限制”。对于每个表文件表空间,每个表都有自己的表空间,这为增长提供了空间。
潜在的缺点
-
使用每表文件表空间,每个表可能具有未使用的空间,该空间只能由同一表的行使用。如果管理不当,可能会导致空间浪费。
-
fsync
操作必须在每个打开的表上而不是单个文件上运行。因为fsync
每个文件都有单独的操作,所以不能将对多个表的写操作合并为一个I / O操作。这可能需要InnoDB
执行更高数量的fsync
操作。 -
mysqld必须为每个表保留一个打开的文件句柄,如果每个表文件表空间中有多个表,则可能会影响性能。
-
使用更多的文件描述符。
-
innodb_file_per_table
在MySQL 5.6和更高版本中默认启用。如果需要考虑与MySQL早期版本的向后兼容性,则可以考虑禁用它。 -
如果越来越多的表正在增长,则可能会出现更多碎片,这可能会妨碍
DROP TABLE
表扫描性能。但是,在管理碎片时,将文件放在自己的表空间中可以提高性能。 -
删除每表文件表空间时,将扫描缓冲池,对于大小为数十GB的缓冲池,这可能需要花费几秒钟的时间。使用宽泛的内部锁定执行扫描,这可能会延迟其他操作。系统表空间中的表不受影响。
-
该
innodb_autoextend_increment
变量定义增量大小(以MB为单位),用于在自动扩展共享表空间文件已满时扩展其大小,该 变量不适用于每表文件表空间文件,无论innodb_autoextend_increment
设置如何,该文件均会自动扩展 。初始扩展名不多,之后扩展名以4MB为增量。
innodb_file_per_table
默认情况下启用 该选项。
要innodb_file_per_table
在启动时设置该 选项,请使用--innodb_file_per_table
命令行选项启动服务器 ,或将此行添加到以下 [mysqld]
部分 my.cnf
:
[mysqld]
innodb_file_per_table=1
您还可以innodb_file_per_table
在服务器运行时动态设置 :
mysql> SET GLOBAL innodb_file_per_table=1;
与innodb_file_per_table
启用,可以存储InnoDB
在一个表
文件。与tbl_name
.ibdMyISAM
存储引擎不同,存储引擎具有 用于索引和数据的单独 文件
和 tbl_name
.MYD
文件,tbl_name
.MYIInnoDB
将数据和索引一起存储在单个 .ibd
文件中。
如果禁用 innodb_file_per_table
启动选项并重新启动服务器,或者使用SET GLOBAL
命令禁用服务器,请 InnoDB
在系统表空间内创建新表,除非已使用该CREATE TABLE ... TABLESPACE
选项将表显式放置在每表文件表空间或常规表空间中 。
InnoDB
无论每表文件设置如何, 您始终可以读写任何表。
要将表从系统表空间移至其自己的表空间,请更改 innodb_file_per_table
设置并重建表:
mysql> SET GLOBAL innodb_file_per_table=1;
mysql> ALTER TABLE table_name ENGINE=InnoDB;
使用CREATE TABLE ... TABLESPACE
或 ALTER TABLE ... TABLESPACE
语法添加到系统表空间的表 不受此innodb_file_per_table
设置的影响 。要将这些表从系统表空间移至每个表文件表空间,必须使用ALTER TABLE ... TABLESPACE
语法将其显式移动 。
InnoDB
始终需要系统表空间,因为它会将其内部数据字典放在其中 并撤消日志。这些 .ibd
文件不足以进行 InnoDB
操作。
将表从系统表空间移到其自己的.ibd
文件时,构成系统表空间的数据文件将保持相同大小。以前由表占用的空间可以重新用于新 InnoDB
数据,但不能回收供操作系统使用。将大型 InnoDB
表移出磁盘空间有限的系统表空间时,您可能更喜欢innodb_file_per_table
使用mysqldump命令启用 并重新创建整个实例 。如上所述,使用CREATE TABLE ... TABLESPACE
或 ALTER TABLE ... TABLESPACE
语法添加到系统表空间的表 不受 innodb_file_per_table
设置。这些表必须单独移动。