每表文件表空间 - innodb_file_per_table

每表文件表空间

历史上,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实例复制到另一个(称为可 传输表空间功能)。

  • 在每表文件表空间中创建的表支持与压缩 和动态行格式关联的功能 

  • 您可以使用动态行格式为具有大表BLOBTEXT列的表启用更有效的存储 

  • 每表文件表空间可以增加成功恢复的机会,并在发生损坏,无法重新启动服务器或备份和二进制日志不可用时节省时间。

  • 您可以使用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.ibd 文件。MyISAM存储引擎不同,存储引擎具有 用于索引和数据的单独 文件tbl_name.MYD和 tbl_name.MYI文件,InnoDB将数据和索引一起存储在单个 .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 设置。这些表必须单独移动。

业务热线电话:(+86)021-52765253 021-61172807      上海哲涛网络科技有限公司版权所有 © 2005-2018       沪ICP备06058430-1号

沪公网安备 31011302000898号

校睿宝,培训机构学员管理软件,教师工资计算软件,消课管理软件!

手机智慧课堂答题,短信答题,微信答题!

数据备份软件,文件数据备份软件,文件备份软件,B/S架构备份软件,分布式计算机备份软件!