MySQL InnoDB File-Per-Table表空间

InnoDB File-Per-Table表空间
过去，所有InnoDB表和索引都存储在系统表空间中。这种整体的方法针对的是完全专用于数据库处理的机器，通过精心规划的数据增长，分配给MySQL的任何磁盘存储永远不会被用于其他用途。InnoDB的file-per-table表空间特性提供了一个更灵活的替代方案，每个InnoDB表及其索引都存储在一个单独的.ibd数据文件中。每个这样的.ibd数据文件代表一个单独的表空间。这个特性是由innodb_file_per_table配置选项控制的，在MySQL 5.6.6及更高版本中默认启用。

file-per-table表空间的优点
.当truncate或drop存储在file-per-table表空间中的表时，可以回收磁盘空间。truncate或drop存储在共享系统表空间中的表会在系统表空间数据文件(ibdata文件)内部创建空闲空间，这些空间只能用于新的InnoDB数据。

.在存储在file-per-table表空间文件中的表上运行TRUNCATE TABLE操作会更快。

.您可以将特定的表存储在单独的存储设备上，以实现I/O优化、空间管理或备份目的。在以前的版本中，您必须将整个数据库目录移动到其他驱动器，并在MySQL数据目录中创建符号链接。在MySQL 5.6.6及更高版本中，你可以使用
create table… data directory=absolute_path_to_directory。

.你可以运行OPTIMIZE TABLE来压缩或重新创建一个file-per-table表空间。当你运行一个OPTIMIZE TABLE时，InnoDB会创建一个新的.ibd文件，该文件带有一个临时名称，只使用存储实际数据所需的空间。当优化完成后，InnoDB会删除旧的.ibd文件，并用新文件替换它。如果以前的.ibd文件显著增长，但实际数据只占其大小的一部分，那么运行OPTIMIZE TABLE可以回收未使用的空间。

.你可以移动单个InnoDB表，而不是整个数据库

.你可以将InnoDB表从一个MySQL实例复制到另一个实例(称为可迁移表空间特性)。

.在file-per-table表空间中创建的表使用Barracuda文件格式。Barracuda文件格式支持压缩和动态行格式等特性

.可以使用动态行格式为具有大型BLOB或TEXT列的表启用更高效的存储。

.file-per-table表空间可以在发生损坏、服务器无法重启或备份和二进制日志不可用时提高成功恢复的机会并节省时间。

.在复制或备份表时，file-per-table表空间可以方便地报告每个表的状态。

.可以在文件系统级别监控表大小，而不需要访问MySQL。

.当innodb_flush_method设置为O_DIRECT时，普通的Linux文件系统不允许并发写入单个文件。因此，使用file-per-table表空间和innodb_flush_method可能会提高性能。

.系统表空间存储数据字典和undo日志，受InnoDB表空间大小限制。使用file-per_table表空间，每个表都有自己的表空间，这为增长提供了空间。

file-per-table表空间的潜在缺点
.使用file-per-table表空间，每个表可能有未使用的空间，这些空间只能由同一表的行使用。如果管理不当，可能会造成空间的浪费。

.fsync操作必须在每个打开的表上运行，而不是单个文件上。因为每个文件都有一个单独的fsync操作，所以对多个表的写操作不能合并成一个单独的I/O操作。这可能需要InnoDB执行更多的fsync操作。

.mysqld必须为每个表保留一个打开的文件句柄，如果在file-per-table表空间中有很多表，这可能会影响性能。

.使用了更多的文件描述符

.innodb_file_per_table在MySQL 5.6.6及更高版本中是默认启用的。如果向后兼容MySQL 5.5或5.1是一个问题，你可以考虑禁用它。禁用innodb_file_per_table功能可以防止在ALTER TABLE重新创建InnoDB表(ALGORITHM=COPY)时，阻止alter table将InnoDB表从系统表空间移动到单独的.ibd文件中。

例如，当重构InnoDB表的聚集索引时，表会使用innodb_file_per_table的当前设置重新创建。此行为在添加或删除InnoDB二级索引时不适用。当不重建表而创建二级索引时，无论当前的innodb_file_per_table设置是什么，索引都被存储在与表数据相同的文件中。此行为也不适用于使用CREATE TABLE…TABLESPACE或ALTER TABLE …TABLESPACE语法添加到系统表空间中的表。这些表不受innodb_file_per_table设置的影响。

.如果许多表都在增长，可能会出现更多的碎片，这可能会影响DROP TABLE和表扫描性能。但是，在管理碎片时，将文件放在它们自己的表空间中可以提高性能。

.在删除file-per-table表空间时会扫描缓冲池，对于大小为几十gb的缓冲池来说，这可能需要几秒钟的时间。扫描是用一个宽的内部锁执行的，这可能会延迟其他操作。系统表空间中的表不受影响。

.innodb_autoextend_increment变量定义了自动扩展的共享表空间文件满时的扩展大小(以MB为单位)，但不适用于file-per-table表空间文件，不管是否设置了innodb_autoextend_increment，这些文件都是自动扩展的。最初的扩展是少量的，之后扩展以4MB的增量出现。

启用与禁用file-per-table表空间
innodb_file_per_table选项默认是启用的。

为了在启动时设置innodb_file_per_table选项，可以在启动服务时使用–innodb_file_per_table命令行选项或者在my.cnf文件中[mysqld]部分增加以下一行内容:

[mysqld]
innodb_file_per_table=1
你也可以在服务器运行时动态设置innodb_file_per_table:

mysql> SET GLOBAL innodb_file_per_table=1;
Query OK, 0 rows affected (0.00 sec)
启用innodb_file_per_table时，可以将InnoDB表存储在tbl_name.ibd文件。不像MyISAM存储引擎，它有单独的tbl_name.MYD和tbl_name.MYI文件用于索引和数据，InnoDB将数据和索引一起存储在一个.ibd文件中。仍然像往常一样创建tbl_name.frm文件。

如果在启动选项中禁用innodb_file_per_table并重启服务器或者使用set global命令来禁用它，除非你显式的使用create table … tablespace选项将表存放在file-per-table表空间或通用表空间否则innodb将在系统表空间创建新表。

你总是可以读取和写入任何InnoDB表，不管file-per-table设置情况。

如果要将表从系统表空间移动到自己的表空间，需要修改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设置的影响。要将这些表从系统表空间移动到file-per-table表空间，必须使用ALTER TABLE…TABLESPACE语法。

InnoDB总是需要系统表空间，因为它把它的内部数据字典和undo日志放在那里。.ibd文件不够InnoDB操作。

当一个表从系统表空间移到它自己的.ibd文件时，组成系统表空间的数据文件保持相同的大小。InnoDB表以前占用的空间可以被新的InnoDB数据重用，但是不会被操作系统回收使用。当将较大的InnoDB表移出系统表空间(磁盘空间有限)时，你可能更喜欢启用innodb_file_per_table并使用mysqldump命令重新创建整个实例。如上所述，使用CREATE TABLE…TABLESPACE或者ALTER TABLE…表空间语法不受innodb_file_per_table设置的影响。这些桌子必须单独移动。

在数据目录外创建逐file-per-table表空间
要在MySQL数据目录之外的特定位置创建一个新的InnoDB file-per-table表空间，使用有data directory = absolute_path_to_directory子句的CREATE TABLE语句的来实现。

提前计划位置，因为您不能在ALTER TABLE语句中使用DATA DIRECTORY子句。您指定的目录可以位于具有特定性能或容量特征的另一个存储设备上，例如快速SSD或大容量HDD。

在目标目录中，MySQL创建一个与数据库名称对应的子目录，在该子目录中为新表创建一个.ibd文件。在MySQL DATADIR目录下的数据库目录中，MySQL创建了一个table_name.Isl文件包含表路径名。.isl文件被MySQL视为一个符号链接。(InnoDB表不支持使用实际的符号链接。)

下面的例子演示了如何在MySQL数据目录外创建一个file-per-table表空间。它显示了在指定目录中创建的.ibd，以及在MySQL数据目录下的数据库目录中创建的.isl。

mysql> use mysql
Database changed
mysql> show variables like ‘innodb_file_per_table’;
±----------------------±------+
| Variable_name | Value |
±----------------------±------+
| innodb_file_per_table | ON |
±----------------------±------+
1 row in set (0.01 sec)
mysql> create table t_cs(c1 int primary key) data directory=’/data’;
Query OK, 0 rows affected (0.22 sec)
[root@localhost mysql]# pwd
/data/mysql
[root@localhost mysql]# ls -lrt
总用量 96
-rw-r-----. 1 mysql mysql 98304 3月 8 16:09 t_cs.ibd
[root@localhost mysql]# ls -lrt t_cs*
-rw-r-----. 1 mysql mysql 8556 3月 8 16:09 t_cs.frm
-rw-r-----. 1 mysql mysql 20 3月 8 16:09 t_cs.isl
你也可以使用CREATE TABLE…TABLESPACE与DATA DIRECTORY子句结合，在MySQL数据目录之外创建一个file-per-table表空间。为此，你必须指定innodb_file_per_table作为表空间名。

mysql> create table t_cs_3(c1 int primary key) tablespace=innodb_file_per_table data directory=’/data’;
Query OK, 0 rows affected (0.28 sec)
[root@localhost mysql]# ls -lrt t_cs_2*
-rw-r-----. 1 mysql mysql 98304 3月 8 16:14 t_cs_2.ibd
[root@localhost mysql]# ls -lrt t_cs_2*
-rw-r-----. 1 mysql mysql 8556 3月 8 16:14 t_cs_2.frm
-rw-r-----. 1 mysql mysql 22 3月 8 16:14 t_cs_2.isl
使用这个方法时，你不需要启用innodb_file_per_table。

使用说明:
.MySQL最初保存的.ibd文件是打开的，防止您卸载设备，但如果服务器繁忙，可能最终会关闭表。小心不要在MySQL运行时意外地卸载外部设备，或者在设备断开连接时启动MySQL。当关联的.ibd文件丢失时，试图访问表会导致严重错误，需要重新启动服务器。

如果.ibd文件仍然不在预期的路径上，服务器重启可能会失败。在本例中，手动删除数据库目录中的table_name.isl文件重新启动后，执行DROPTABLE命令删除.frm文件，并从数据字典中删除该表的信息。

.在将表存放在NFS挂载的卷上之前，请查看在使用NFS和MySQL中列出的潜在问题。

.如果您使用LVM快照、文件复制或其他基于文件的机制来备份.ibd文件，请始终使用FLUSH TABLES…FOR EXPORT语句，以确保在备份发生之前将缓存在内存中的所有更改刷新到磁盘。

.DATA DIRECTORY子句是一种支持替代符号链接的方法，符号链接一直存在问题，从来没有被支持用于单独的InnoDB表。