开启关闭innodb_file_per_table及创建表时的数据文件
环境信息
- MySQL 5.7.19
- Debian GNU/Linux 8
先上结论
- 当开启
innodb_file_per_table时，会在独立表空间中创建表。独立表空间，即 file-per-table 表空间。- 每个普通表对应的独立表空间数据文件路径是
{datadir}/{databaseName}/{tableName}.ibd。 - 每个分区表会对应多个数据文件，每个分区的数据文件路径是
{datadir}/{databaseName}/{tableName}#P#{partitionName}.ibd
- 每个普通表对应的独立表空间数据文件路径是
- 当关闭
innodb_file_per_table时，会在系统表空间中创建表。系统表空间对应的默认数据文件是{datadir}/ibdata1
innodb_file_per_table
innodb_file_per_table 是 InnoDB 存储引擎的系统变量之一。
↑ InnoDB Startup Options and System Variables 跳转 click here
- 当启用
innodb_file_per_table时，会在独立表空间中创建表。 - 当禁用
innodb_file_per_table时，会在系统表空间中创建表。
★ 查询： 我们可以用以下命令，查询当前 file-per-table 独立表空间是否开启：
SHOW VARIABLES LIKE 'innodb_file_per_table'
☆ 在MySQL5.6及更高版本中，innodb_file_per_table 是默认开启的。
★ 修改： 我们还可以即时修改 file-per-table 的运行时状态：
SET GLOBAL innodb_file_per_table=OFF;
SET GLOBAL innodb_file_per_table=ON;
1.系统表空间数据文件路径
Reference: The System Tablespace 跳转 click here
系统表空间可以有一个或多个数据文件。默认情况下，在数据目录中创建一个名为ibdata1的系统表空间数据文件。系统表空间数据文件的大小和数量由 innodb_data_file_path启动选项定义。
SHOW VARIABLES LIKE 'innodb_data%'
当指定
autoextend属性时，数据文件的大小会根据需要的空间自动增加。innodb_autoextend_increment变量控制增量大小，单位为兆字节（MB）。Learn More
刚才还提到数据目录，因此我们查看一下当前数据目录。
SHOW VARIABLES LIKE 'datadir'
因此，我的MySQL系统表空间的路径是/var/lib/mysql/ibdata1。
★ 创建一个分区表: 当 innodb_file_per_table 关闭时，如果创建一个分区表，不会生成单独的数据文件。例如：
CREATE TABLE t2 (i INT) ENGINE = INNODB PARTITION BY KEY (i) PARTITIONS 4;
我们查看一下 /var/lib/mysql/testdb 的文件:
cd /var/lib/mysql/testdb
ls -al | grep t2
仅产生了一个 .frm 文件，没有生成 .ibd 数据文件。此时，该表还是创建到了共享空间数据文件/var/lib/mysql/ibdata1中。
2.独立表空间数据文件路径
Reference: File-Per-Table Tablespaces 跳转 click here
★ 创建一个普通表: 当 innodb_file_per_table 开启时，如果创建一个普通表，就创建一个路径为 /数据目录/数据库名称/表名称.ibd 的数据文件。例如：
USE testdb;
CREATE TABLE t1 (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100)
) ENGINE = InnoDB;
我们查看一下 /var/lib/mysql/testdb 的文件:
cd /var/lib/mysql/testdb
ls -al
我们发现有以下两个文件：
.ibd是 file-per-table 表空间数据文件。.frm保存的是表的元信息，例如表的定义等。尽管每个InnoDB表都有一个.frm文件，但InnoDB在系统表空间中维护自己的表元数据。Learn More
★ 创建一个分区表: 当 innodb_file_per_table 开启时，如果创建一个分区表，就创建一个路径为 /数据目录/数据库名称/表名称.ibd 的数据文件。例如：
USE testdb;
CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT NOT NULL,
store_id INT NOT NULL
)
PARTITION BY RANGE (store_id) (
PARTITION p0 VALUES LESS THAN (6),
PARTITION p1 VALUES LESS THAN (11),
PARTITION p2 VALUES LESS THAN (16),
PARTITION p3 VALUES LESS THAN (21)
);
我们查看一下 /var/lib/mysql/testdb 的文件:
cd /var/lib/mysql/testdb
ls -al
我们发现有以下文件：
表名称#P#分区名称.ibd是分区表空间数据文件，每一个分区对应一个文件。.frm保存的是表的元信息，例如表的定义等。
innodb_autoextend_increment变量定义了增量大小，用于在自动扩展系统表空间文件。当该文件写满时，扩展该文件的大小。
但是，它不适用于独立表空间数据文件，无论innodb_autoextend_increment设置如何，这些文件都是自动扩展的。每个表的初始文件表空间扩展是少量的，之后扩展以4MB的增量出现。
参考文档
《MySQL技术内幕 InnoDB存储引擎 第2版》
《MySQL 5.7 Reference》 跳转 click here
