14.4.4 InnoDB File-Per-Table Tablespaces

14.4.4 InnoDB File-Per-Table Tablespaces

14.4.4.1 Enabling and Disabling File-Per-Table Tablespaces

从历史上看, 所有的InnoDB 表和Indexes 是存储在system 表空间。

这个整体的方法是针对机器完全致力于数据库处理, 精心策划的数据增长,

任何磁盘存储分配给MySQL 不需要用于其他的目的。

InnoDB 的file-per-table tablespace提供一个更加灵活的选择,

每个InnoDB 表和它的indexes 是单独存储在.ibd 数据文件。

每个.idb 数据文件代表一个单独的表空间。这个功能是通过 innodb_file_per_table 配置选项控制,

在5.6.6和更高的版本默认启用

File-Per-Table Tablespaces 的优势:

1.你可以回收磁盘空间当truncate或者drop 一个表以 file-per-table tablepace 方式存储。

truncate或者drop 表存储在system tablespace 创建的空闲的空间初始在system tablespace 数据文件

(iddata files)，只能用于新的InnoDB data.

TRUNCATE TABLE 操作是更快的当运行在以file-per-table tablepaces方式存储。

3.你可以存储单独的表在单独的存储系统,用于I/O优化,空间管理,或者备份目的。

在以前的版本中, 你需要移动整个数据库目录到其他驱动器,创建软连接 在mysql 数据目录,

在8.12.4章节描述, “Using Symbolic Links”.

在MySQL 5.6.6和更高的版本, 你可以指定每个表的位置使用 语句 CREATE TABLE … DATA DIRECTORY =

absolute_path_to_directory,

你可以

运行 OPTIMIZE TABLE 来压缩或者重新创建一个 file-per-table tablespace.

当你运行 一个OPTIMIZE TABLE, InnoDB 创建一个新的.ibd 文件 使用一个临时文件名,

只使用需要的空间来存储实际的数据。当优化完成后,如果先前的.ibd 文件增长明显,但实际数据只占其体积的一小部分

,

OPTIMIZE TABLE

运行可以回收不使用的空间。

你可以删除单个InnoDB 表相比整个数据库

你可以复制单个InnoDB 表从一个MySQL 实例到另外一个(被称为表空间传输特性)

以 file-per-table tablespaces 创建的表使用Barracuda file format.

Barracuda file 格式启用特性比如压缩和动态的行格式。

表创建在 system tablespace 不能使用那些特性。利用这些特性对于一个存在的表,启用innodb_file_per_table

setting 运行

ALTER TABLE t ENGINE=INNODB to 放置表在一个file-per-table tablespace.

在转换表之前, refer to Section 14.5.4, “Converting Tables from MyISAM to InnoDB”.

你可以让更高效的存储引擎用于BLOB或者TEXT列的表使用动态行格式:

File-per-table tablespaces会改善提高 成功恢复和节约时间当一个腐败发生,

当一个server 不能被重启,或者当备份和binary logs 不可用

你可以备份或者恢复单个表快速的 使用MySQL 企业备份产品,

而无需中断其他InnoDB表的使用。这是有意的如果你有表需要不经常的备份或者不同的备份调度

File-per-table tablespaces是方便的用于每个表的状态报告 当复制或者备份表

你可以监控表大小在文件系统层面,不需要访问Mysql

常见的Linux 文件系统不允许并发写一个单独的文件 当innodb_flush_method 是设置为O_DIRECT

mysql> show variables like ‘%innodb_flush_method%’;

+———————+——-+

| Variable_name | Value |

| innodb_flush_method | |

1 row in set (0.00 sec)

因此, 有可能的性能改进当使用 file-per-table tablespaces 。

system tablespaces 存储数据目录和unod logs,有64TB 大小限制。

通过比较,每个 file-per-table tablespace 有一个64TB大小的限制,

这为你提供了增长的空间 。

File-Per-Table Tablespaces潜在的缺点:

file-per-table tablespaces,每个表可能有没使用的空间,这只能被相同表的记录使用,如果不妥善管理，这可能会

导致浪费的空间。

fsync 操作必须运行在每个打开的表 相比一个单位的文件,因为这里有一个单独的fsync 操作对于每个文件,

写操作在多个表不能被合并成一个单独的I/O操作。 这个可能需要InnoDB 来执行一个fsync操作的较高的数量

mysqld必须保持一个表打开文件句柄,这个可能会影响性能如果你有大量的表 以 file-per-table tablespaces.

更多的文件描述符被使用

innodb_file_per_table 默认是在MySQL 5.6.6或者更高版本启用, 你可以考虑禁用

如果MySQL 5.5或者5.1是向后兼容是一个问题。禁用 innodb_file_per_table 防止ALTER TABLE 移动InnoDB

从一个system tablespace 到一个单独的.ibd 文件

例如, 当重组 InnoDB 表的 clustered index, 表被重建使用当前的设置 innodb_file_per_table

这种行为不适用于当增加或者删除InnoDB 的 secondary indexes.

当一个secondary index 是被创建没有rebuild表, index 是存储在相同的文件 作为表数据

如果很多表在增长,这个潜在的有很多的碎片 会阻碍DROP TABLE和表扫描性能。

然而,当碎片化被管理, 在自己表空间的文件可以改善性能。

buffer pool 被扫描 当删除一个 file-per-table tablespace,这可能需要几秒用于Buffer pools

mysql> show variables like ‘%innodb_autoextend_increment%’;

+—————————–+——-+

| Variable_name | Value |

| innodb_autoextend_increment | 64 |

innodb_autoextend_increment 变量, 它定义了增量大小(单位MB) 用于增长大小 共享表空间文件的自动增长, 当表空

间快满的时候,

不应用于file-per-table tablespace files, file-per-table tablespace files是自动增长的 有

innodb_autoextend_increment设置

14.4.4.1 Enabling and Disabling File-Per-Table Tablespaces 启用和禁用 File-Per-Table Tablespaces

innodb_file_per_table 选项在MySQL 5.6.6是默认启用的

设置innodb_file_per_table 选择在启动的时候, 启动serve 带上–innodb_file_per_table 命令行选项,

或者增加到[mysqld] 章节在my.cnf文件里

[mysqld]

innodb_file_per_table=1

You can also set innodb_file_per_table dynamically, while the server is running:

SET GLOBAL innodb_file_per_table=1;

当innodb_file_per_table 启用时, 你可以存储InnoDB 表在一个tbl_name.ibd文件，不像MyISAM 存储引擎,

有自己独立的tbl_name.MYD和tbl_name.MYI 文件用于indexes和data.

InnoDB 存储数据和Indexes 一起在.ibd文件。

如果你禁用 innodb_file_per_table 在你启动选择和重启server, 或者disable 通过SET GLOBAL 命令,

InnoDB 创建新的表 在system 表空间里。

你也可以读和写任何的InnoDB 表, 无论file-per-table设置

移动一个表从system表空间到它自己的表空间,改变innodb_file_per_table设置然后重建表

ALTER TABLE table_name ENGINE=InnoDB;

注意:

InnoDB 总是需要system 表空间因为它放置它的内部数据字典和Undo logs,

.ibd 文件不是有效的 对于InnoDB来操作。