4. InnoDB表单独表空间

4. InnoDB表单独表空间

从历史上看，所有InnoDB表和索引都存储在系统表空间中。这种单片方法的目标是完全专用于数据库处理的机器，精心规划的数据增长，其中任何分配给MySQL的磁盘存储都不会用于其他目的。

每张InnoDB表的单独表空间功能提供了一种更灵活的替代方案，其中每张InnoDB表及其索引都存储在一个单独的 .ibd数据文件中。每个这样的 .ibd数据文件代表一个单独的表空间。此功能由 innodb_file_per_table 配置选项，默认情况下在MySQL 5.6.6及更高版本中启用。

mysql> show variables like 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.01 sec)

4.1 File-Per-Table表空间的优点

• truncate 或删除存储在单独表空间中的表时，可以回收磁盘空间。truncate 或删除存储在共享系统表空间中的表会在系统表空间数据文件（ibdata文件）内部创建可用空间，该数据文件只能用于新 InnoDB数据。

• TRUNCATE TABLE在存储在单独表空间中的表上运行时 ，操作更快。

• 可以将特定表存储在单独的存储设备上，以进行I/O优化，空间管理或备份。在以前的版本中，您必须将整个数据库目录移动到其他驱动器并在MySQL数据目录中创建符号链接

shell> mkdir /dr1/databases/test
shell> ln -s /dr1/databases/test /path/to/datadir

在MySQL 5.6.6及更高版本中，您可以使用语法指定每个表的位置,如：

CREATE TABLE t11 (c1 INT PRIMARY KEY) DATA DIRECTORY = '/data/mysql/mysql3306';

• 可以运行OPTIMIZE TABLE以压缩或重新创建单独表空间。
  运行OPTIMIZE TABLE， InnoDB创建一个新的.ibd具有临时名称的文件，只使用存储的实际数据所需的空间。优化完成后，InnoDB删除旧.ibd文件并将其替换为新文件。如果先前的.ibd文件显着增长但实际数据仅占其大小的一部分，则运行OPTIMIZE TABLE可以回收未使用的空间。

root@localhost [test] 17:03:31>optimize table account;
+--------------+----------+----------+-------------------------------------------------------------------+
| Table        | Op       | Msg_type | Msg_text                                                          |
+--------------+----------+----------+-------------------------------------------------------------------+
| test.account | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| test.account | optimize | status   | OK                                                                |
+--------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (0.09 sec)

• 可以移动单张InnoDB表而不是整个数据库
  移动单张innodb表方法

0）source：查看源端xx表结构(show create table xx;)
1）target：create table xx (与source端一致，且表结构必须一致)
2）target：alter table xx discard tablespace;
3）source：flush table xx for export;
4）source：备份表（xx.ibd）文件至target
5）target：修改权限为mysql（如果不修改权限，导入时将会报如下错误）
ERROR 1812 (HY000): Tablespace is missing for table `move_db`.`account`.
6）target：alter table xx import tablespace;

• 可以将单张InnoDB表从一个MySQL实例复制到另一个MySQL实例（称为可 传输表空间功能）。

• 在单独表空间中创建的表使用 Barracuda文件格式。Barracuda文件格式支持 压缩 和动态行格式等功能。

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

• 在发生损坏，无法重新启动服务器或备份和二进制日志不可用时,单独表空间恢复可以提高恢复成功率以节省时间。

• 可以使用MySQL Enterprise Backup产品快速备份或还原单个表，而不会中断其他InnoDB表的使用。

• 在复制或备份表时，单独表空间便于每表状态报告。

• 可以在文件系统级别监视表大小，而无需访问MySQL。

• 当innodb_flush_method设置为 常见Linux文件系统时，不允许对单个文件进行并发写入 O_DIRECT。因此，在使用单独表空间时，可能会有性能提升。
  

• 系统表空间存储数据字典和撤消日志，并且受InnoDB表空间大小限制的限制。使用单独表空间，每个表都有自己的表空间，这为增长提供了空间。

4.2 File-Per-Table表空间的潜在缺点

• 对于单独表空间，每个表可能有未使用的空间，只能由同一个表的行使用。如果管理不当，这可能会导致空间浪费。

• fsync操作必须在每个打开的表上运行，而不是在单个文件上运行。由于fsync每个文件都有单独的操作，因此无法将多个表上的写操作组合到单个I / O操作中。这可能需要 InnoDB执行更高的fsync操作总数 。

• mysqld必须为每个表保留一个打开的文件句柄，如果在单独表空间中有许多表，这可能会影响性能。

• 使用了更多的文件描述符。

• innodb_file_per_table在MySQL 5.6.6及更高版本中默认启用。如果担心与MySQL 5.5或5.1的向后兼容性，您可以考虑禁用它。在 重新创建table（）的情况下，禁用innodb_file_per_table 会阻止ALTER TABLE将InnoDB表从系统表空间移动到单个.ibd文件。

• 如果许多表正在增长，则可能存在更多碎片，这可能会影响DROP TABLE表扫描性能。但是，在管理碎片时，在自己的表空间中放置文件可以提高性能。

• 删除单独表空间时会扫描缓冲池，对于数十GB的缓冲池，这可能需要几秒钟。使用广泛的内部锁执行扫描，这可能会延迟其他操作。系统表空间中的表不受影响。

• innodb_autoextend_increment 变量定义了增量大小（以MB为单位），用于在自动扩展共享表空间文件变满时扩展其大小，不适用于单独表空间文件，无论innodb_autoextend_increment 设置如何，这些文件都是自动扩展的 。初始扩展是少量的，之后扩展以4MB的增量发生。