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 |
+—————————–+——-+
1 row in set (0.00 sec)
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设置然后重建表
SET GLOBAL innodb_file_per_table=1;
ALTER TABLE table_name ENGINE=InnoDB;
注意:
InnoDB 总是需要system 表空间因为它放置它的内部数据字典和Undo logs,
.ibd 文件不是有效的 对于InnoDB来操作。