文章目录6. InnoDB 磁盘文件6.1 InnoDB存储引擎包含哪些磁盘文件6.1.1 什么是表空间6.2 系统表空间 - System Tablespace6.2.1 系统表空间的作用6.4.2 系统表空间文件保存在哪里6.4.3 系统表空间都有哪些可以配置的选项6.4.4 修改系统表空间配置后什么时候生效6.5 独立表空间 - File-Per-Table Tablespace6.5.1 独立表空间的作用6.5.2 独立表空间文件保存在哪里6.5.3 每个表都对应一个独立表空间吗6.5.4 独立表空间的优点和缺点6.6 撤销表空间 - Undo Tablespaces6.6.1 撤销表空间的作用6.6.1.1 什么是撤销日志6.6.2 在使用MySQL时并没有手动创建撤销表空间它是什么时候被创建的6.6.2.1 默认的撤销表空间名称和路径是什么6.6.3 可以手动创建撤销表空间吗6.6.3.1 什么时候需要手动创建撤销表空间6.6.3.2 使用自己创建的撤销表空间需要注意什么6. InnoDB 磁盘文件6.1 InnoDB存储引擎包含哪些磁盘文件InnoDB的磁盘文件主要是表空间文件和其他文件。表空间包括系统表空间独立表空间通用表空间临时表空间撤销表空间其他文件重做日志双写缓冲区6.1.1 什么是表空间表空间可以理解为MYSQL为了管理数据而设计的一种数据结构主要描述的对结构的定义表空间文件是对定义的具体实现以文件的形式存在于磁盘上以后我们说的表空间指的就是表空间文件InnoDB存储引擎的表空间包括系统表空间、独立表空间、通用表空间、临时表空间和撤销表空间6.2 系统表空间 - System Tablespace6.2.1 系统表空间的作用系统表空间存储了MySQL中所有系统表的数据也包括数据字典系统表空间也是变更缓冲区的存储区域当数据库服务器关闭时没有合并到缓冲池的二级索引修改操作被保存到系统表空间在以前的版本中系统表空间也包含双写缓冲区从MySQL 8.0.20开始双写缓冲区从系统表空间中移到单独的文件中。6.4.2 系统表空间文件保存在哪里系统表空间可以对应一个或多个数据文件默认情况下MySQL在data目录中创建一个系统表空间数据文件ibdata1。系统表空间数据文件的大小和数量由innodb_data_file_path启动选项定义。6.4.3 系统表空间都有哪些可以配置的选项可以通过innodb_data_file_path选项定义如果没有指定innodb_data_file_path的值则默认创建一个大小可以自动扩展的数据文件文件名为ibdata1初始大小12MB。mysql SHOW VARIABLES LIKE innodb_data_file_path; ----------------------------------------------- | Variable_name | Value | ----------------------------------------------- | innodb_data_file_path | ibdata1:12M:autoextend | # 默认值 ----------------------------------------------- 1 row in set (0.00 sec) mysql数据文件命名规范的完整语法包括文件名、文件大小、自动扩展属性和max属性:file_name:file_size[:autoextend[:max:max_file_size]]通过在file_size值后面指定单位K、M或G来设置文件大小单位为 千字节 、 兆字节 或 千兆字节 。如果以K为单位指定文件大小应设置为1024的倍数。否则千字节值四舍五入到最接近的兆字节 (MB) 且文件大小至少为12MB。指定多个数据文件可以使用分号;分隔。例如# mysqld节点 [mysqld] # 文件1名称为ibdata1 大小为50M # 文件2名称为ibdata2 大小为50M自动扩容 innodb_data_file_pathibdata1:50M;ibdata2:50M:autoextendautoextend和max属性只能用于最后指定的数据文件当指定autoextend属性时数据文件的大小会根据需要自动扩容默认每次增加64MB。可以通过系统变量innodb_autoextend_increment控制增量的大小如果要指定数据文件的最大容量在autoextend后面指定max属性。注意只有在明确了解限制磁盘使用的情况下才使用max属性。下面的配置允许ibdata1扩展到500MB。# mysqld节点 [mysqld] # 初始大小12M允许自动扩容最大可以扩容到500M innodb_data_file_pathibdata1:12M:autoextend:max:500M系统表空间文件默认创建在data目录下。如果指定其他的目录使用innodb_data_home_dir选项。注意修改或指定自定义目录时一定要检查或设置目录的权限让MySQL可以进行读写操作否则就会启动失败。例如要在名为myibdata的目录下创建一个系统表空间数据文件可以使用如下配置# mysqld节点 [mysqld] # 指定innodb数据目录 innodb_data_home_dir /myibdata/ # 配置系统表空间 innodb_data_file_pathibdata1:50M:autoextend注意指定innodb_data_home_dir时必须以斜杠/结尾InnoDB不会自动创建目录所以在启动服务器之前要确保指定的目录已经存在最终通过innodb_data_home_dir指定的路径与数据文件名组合起来生成完整路径。例如上面/myibdata/ibdata1拼起来。如果innodb_data_home_dir不指定默认值为./即MySQL的数据目录如果innodb_data_file_path指定一个绝对路径则不会读取innodb_data_home_dir的值系统表空间文件根据指定的绝对路径创建启动服务器之前必须确保指定的目录存在。在添加新的数据文件时不要指定现有的文件名InnoDB在启动服务器时会创建并初始化新的数据文件。总结根据实际应用场景通过配置对应的系统变量来指定数据文件的大小、名称、数量和其他属性。6.4.4 修改系统表空间配置后什么时候生效在修改系统表空间配置时先停止MySQL服务修改完成后再重新启动MySQL服务之后生效。6.5 独立表空间 - File-Per-Table Tablespace6.5.1 独立表空间的作用File-Per-Table表空间包含单个InnoDB表的数据和索引默认情况下每张表都对应一个表空间数据文件便于维护所以称为File-Per-Table Tablespace。6.5.2 独立表空间文件保存在哪里File-Per-Table表空间在data/database_name/目录下的table_name.ibd表空间数据文件中创建。.ibd文件与表同名。例如表test_db.t1的数据文件如下所示# 选择数据库 USE test_db; # 建表 CREATE TABLE t1 ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100) ) ENGINE InnoDB;6.5.3 每个表都对应一个独立表空间吗不一定默认每张表都对应一个表空间数据文件但也可以通过系统变量innodb_file_per_table[{OFF|ON}]控制开启或禁用是否为每张表生成一个独立表空间文件如果禁用会在系统表空间中创建表可以在选项文件中指定innodb_file_per_table设置也可以在运行时使用SET GLOBAL语句设置# 选项文件中的mysqld节点 [mysqld] innodb_file_per_tableOFF # 禁用强烈不建议 # 在运行时通过SET GLOBAL 设置 mysql SET GLOBAL innodb_file_per_tableON;6.5.4 独立表空间的优点和缺点优点使用TRUNCATE或DROP语句删除File-Per-Table表空间中的表后磁盘空间会返回给操作系统从而提高磁盘利用率而共享表空间比如System Tablespace则不会回收磁盘空间而且在共享表空间中这些空间只能被InnoDB表重新使用执行时TRUNCATE TABLE时性能更好恢复出厂设置可以在其他目录或单独的存储设备上创建File-Per-Table表空间文件的数据文件从而达到I/O优化、空间管理或备份的目的# 指定DATA directory子句可以在外部目录中创建表 CREATE TABLE t1 (c1 INT PRIMARY KEY) DATA DIRECTORY /external/directory;支持与DYNAMIC和COMPRESSED行格式而系统表空间不支持发生数据损坏、备份、二进制日志不可用或MySQL服务器实例无法重新启动时提高成功恢复的机会单个表容量大小限制为64TB所以可以存储更多的数据而共享表空间中的表的总容量为64TB。缺点每个表都可能有未使用的空间这些空间只能由对应的表使用如果管理不当可能会导致空间浪费当每个表都有自己的数据文件操作系统需要维护更多的文件描述符如果表非常多可能会影响性能可能会出现更多的磁盘碎片会影响DROP TABLE表扫描性能innodb_autoextend_increment系统变量定义了自动扩展共享表空间文件的增量大小但对于File-Per-Table表空间文件不起作用File-Per-Table表空间文件始终自动扩展初始大小根据表定义分配最小的空间之后以4MB为增量进行扩容。6.6 撤销表空间 - Undo Tablespaces6.6.1 撤销表空间的作用撤销表空间中包含撤销日志(Undo Log)撤销日志记录了如何撤销事务对聚集索引记录的最新更改(事务的回滚)通过对事务的回滚从而保证事务ACID特性中的原子性。6.6.1.1 什么是撤销日志介绍完撤销表空间之后将会详细详解撤销日志6.6.2 在使用MySQL时并没有手动创建撤销表空间它是什么时候被创建的MySQL初始化时会在数据目录下创建两个默认的撤销表空间数据文件名分别为undo_001和undo_002数据字典中对应undo表空间名称为innodb_undo_001和innodb_undo_002rootyudukai:/var/lib/mysql# ll # ...省略 -rw-r----- 1 mysql mysql 16777216 May 29 08:52 undo_001 -rw-r----- 1 mysql mysql 16777216 May 29 08:50 undo_002总结MySQL初始化时会在数据目录下创建两个默认的撤销表空间数据文件名分别为undo_001和undo_0026.6.2.1 默认的撤销表空间名称和路径是什么要查看撤销表空间名称和路径请查询INFORMATION_SCHEMA.FILESmysql SELECT TABLESPACE_NAME, FILE_NAME FROM INFORMATION_SCHEMA.FILES - WHERE FILE_TYPE LIKE UNDO LOG; ----------------------------- | TABLESPACE_NAME | FILE_NAME | ----------------------------- | innodb_undo_001 | ./undo_001 | # 数据字典中的名称为innodb_undo_001对应的数据文件为./undo_001 | innodb_undo_002 | ./undo_002 | # 数据字典中的名称为innodb_undo_002对应的数据文件为./undo_002 ----------------------------- 2 rows in set (0.00 sec) mysql6.6.3 可以手动创建撤销表空间吗可以通过使用CREATE UNDO TABLESPACE语句可以创建撤销表空间# 语法 CREATE UNDO TABLESPACE tablespace_name ADD DATAFILE file_name.ibu;6.6.3.1 什么时候需要手动创建撤销表空间对于长时间运行的大事务撤销日志可能会变得很大通过创建额外的撤销表空间来防止单个撤销表空间变得太大从MySQL 8.0.14开始可以在运行时使用CREATE UNDO TABLESPACE语法创建额外的撤销表空间6.6.3.2 使用自己创建的撤销表空间需要注意什么通过系统变量innodb_undo_directory指定撤销表空间的默认存放路径如果不指定默认位置为数据目录撤销表空间文件名必须以.ibu为扩展名定义undo表空间文件名时如果需要指定路径必须使用绝对路径不允许指定相对路径建议使用唯一的撤销表空间文件名避免在以后移动和复制的过程中发生文件名冲突如果指定其他路径那么路径必须在innodb_directories中定义以便MySQL扫描并识别最多支持127个undo表空间包括实例初始化时创建的两个默认表空间MySQL 8.0.23开始初始撤销表空间大小通常为16MB并根据服务器负载以[16MB, 256MB]的增量进行扩容MySQL 8.0.14之前版本额外的撤销表空间通过配置系统变量innodb_undo_tablespaces来创建取值范围[2, 127]MySQL 8.0.14开始此变量已弃用且不再可配置。
【MySQL高阶】20.InnoDB 磁盘文件
文章目录6. InnoDB 磁盘文件6.1 InnoDB存储引擎包含哪些磁盘文件6.1.1 什么是表空间6.2 系统表空间 - System Tablespace6.2.1 系统表空间的作用6.4.2 系统表空间文件保存在哪里6.4.3 系统表空间都有哪些可以配置的选项6.4.4 修改系统表空间配置后什么时候生效6.5 独立表空间 - File-Per-Table Tablespace6.5.1 独立表空间的作用6.5.2 独立表空间文件保存在哪里6.5.3 每个表都对应一个独立表空间吗6.5.4 独立表空间的优点和缺点6.6 撤销表空间 - Undo Tablespaces6.6.1 撤销表空间的作用6.6.1.1 什么是撤销日志6.6.2 在使用MySQL时并没有手动创建撤销表空间它是什么时候被创建的6.6.2.1 默认的撤销表空间名称和路径是什么6.6.3 可以手动创建撤销表空间吗6.6.3.1 什么时候需要手动创建撤销表空间6.6.3.2 使用自己创建的撤销表空间需要注意什么6. InnoDB 磁盘文件6.1 InnoDB存储引擎包含哪些磁盘文件InnoDB的磁盘文件主要是表空间文件和其他文件。表空间包括系统表空间独立表空间通用表空间临时表空间撤销表空间其他文件重做日志双写缓冲区6.1.1 什么是表空间表空间可以理解为MYSQL为了管理数据而设计的一种数据结构主要描述的对结构的定义表空间文件是对定义的具体实现以文件的形式存在于磁盘上以后我们说的表空间指的就是表空间文件InnoDB存储引擎的表空间包括系统表空间、独立表空间、通用表空间、临时表空间和撤销表空间6.2 系统表空间 - System Tablespace6.2.1 系统表空间的作用系统表空间存储了MySQL中所有系统表的数据也包括数据字典系统表空间也是变更缓冲区的存储区域当数据库服务器关闭时没有合并到缓冲池的二级索引修改操作被保存到系统表空间在以前的版本中系统表空间也包含双写缓冲区从MySQL 8.0.20开始双写缓冲区从系统表空间中移到单独的文件中。6.4.2 系统表空间文件保存在哪里系统表空间可以对应一个或多个数据文件默认情况下MySQL在data目录中创建一个系统表空间数据文件ibdata1。系统表空间数据文件的大小和数量由innodb_data_file_path启动选项定义。6.4.3 系统表空间都有哪些可以配置的选项可以通过innodb_data_file_path选项定义如果没有指定innodb_data_file_path的值则默认创建一个大小可以自动扩展的数据文件文件名为ibdata1初始大小12MB。mysql SHOW VARIABLES LIKE innodb_data_file_path; ----------------------------------------------- | Variable_name | Value | ----------------------------------------------- | innodb_data_file_path | ibdata1:12M:autoextend | # 默认值 ----------------------------------------------- 1 row in set (0.00 sec) mysql数据文件命名规范的完整语法包括文件名、文件大小、自动扩展属性和max属性:file_name:file_size[:autoextend[:max:max_file_size]]通过在file_size值后面指定单位K、M或G来设置文件大小单位为 千字节 、 兆字节 或 千兆字节 。如果以K为单位指定文件大小应设置为1024的倍数。否则千字节值四舍五入到最接近的兆字节 (MB) 且文件大小至少为12MB。指定多个数据文件可以使用分号;分隔。例如# mysqld节点 [mysqld] # 文件1名称为ibdata1 大小为50M # 文件2名称为ibdata2 大小为50M自动扩容 innodb_data_file_pathibdata1:50M;ibdata2:50M:autoextendautoextend和max属性只能用于最后指定的数据文件当指定autoextend属性时数据文件的大小会根据需要自动扩容默认每次增加64MB。可以通过系统变量innodb_autoextend_increment控制增量的大小如果要指定数据文件的最大容量在autoextend后面指定max属性。注意只有在明确了解限制磁盘使用的情况下才使用max属性。下面的配置允许ibdata1扩展到500MB。# mysqld节点 [mysqld] # 初始大小12M允许自动扩容最大可以扩容到500M innodb_data_file_pathibdata1:12M:autoextend:max:500M系统表空间文件默认创建在data目录下。如果指定其他的目录使用innodb_data_home_dir选项。注意修改或指定自定义目录时一定要检查或设置目录的权限让MySQL可以进行读写操作否则就会启动失败。例如要在名为myibdata的目录下创建一个系统表空间数据文件可以使用如下配置# mysqld节点 [mysqld] # 指定innodb数据目录 innodb_data_home_dir /myibdata/ # 配置系统表空间 innodb_data_file_pathibdata1:50M:autoextend注意指定innodb_data_home_dir时必须以斜杠/结尾InnoDB不会自动创建目录所以在启动服务器之前要确保指定的目录已经存在最终通过innodb_data_home_dir指定的路径与数据文件名组合起来生成完整路径。例如上面/myibdata/ibdata1拼起来。如果innodb_data_home_dir不指定默认值为./即MySQL的数据目录如果innodb_data_file_path指定一个绝对路径则不会读取innodb_data_home_dir的值系统表空间文件根据指定的绝对路径创建启动服务器之前必须确保指定的目录存在。在添加新的数据文件时不要指定现有的文件名InnoDB在启动服务器时会创建并初始化新的数据文件。总结根据实际应用场景通过配置对应的系统变量来指定数据文件的大小、名称、数量和其他属性。6.4.4 修改系统表空间配置后什么时候生效在修改系统表空间配置时先停止MySQL服务修改完成后再重新启动MySQL服务之后生效。6.5 独立表空间 - File-Per-Table Tablespace6.5.1 独立表空间的作用File-Per-Table表空间包含单个InnoDB表的数据和索引默认情况下每张表都对应一个表空间数据文件便于维护所以称为File-Per-Table Tablespace。6.5.2 独立表空间文件保存在哪里File-Per-Table表空间在data/database_name/目录下的table_name.ibd表空间数据文件中创建。.ibd文件与表同名。例如表test_db.t1的数据文件如下所示# 选择数据库 USE test_db; # 建表 CREATE TABLE t1 ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100) ) ENGINE InnoDB;6.5.3 每个表都对应一个独立表空间吗不一定默认每张表都对应一个表空间数据文件但也可以通过系统变量innodb_file_per_table[{OFF|ON}]控制开启或禁用是否为每张表生成一个独立表空间文件如果禁用会在系统表空间中创建表可以在选项文件中指定innodb_file_per_table设置也可以在运行时使用SET GLOBAL语句设置# 选项文件中的mysqld节点 [mysqld] innodb_file_per_tableOFF # 禁用强烈不建议 # 在运行时通过SET GLOBAL 设置 mysql SET GLOBAL innodb_file_per_tableON;6.5.4 独立表空间的优点和缺点优点使用TRUNCATE或DROP语句删除File-Per-Table表空间中的表后磁盘空间会返回给操作系统从而提高磁盘利用率而共享表空间比如System Tablespace则不会回收磁盘空间而且在共享表空间中这些空间只能被InnoDB表重新使用执行时TRUNCATE TABLE时性能更好恢复出厂设置可以在其他目录或单独的存储设备上创建File-Per-Table表空间文件的数据文件从而达到I/O优化、空间管理或备份的目的# 指定DATA directory子句可以在外部目录中创建表 CREATE TABLE t1 (c1 INT PRIMARY KEY) DATA DIRECTORY /external/directory;支持与DYNAMIC和COMPRESSED行格式而系统表空间不支持发生数据损坏、备份、二进制日志不可用或MySQL服务器实例无法重新启动时提高成功恢复的机会单个表容量大小限制为64TB所以可以存储更多的数据而共享表空间中的表的总容量为64TB。缺点每个表都可能有未使用的空间这些空间只能由对应的表使用如果管理不当可能会导致空间浪费当每个表都有自己的数据文件操作系统需要维护更多的文件描述符如果表非常多可能会影响性能可能会出现更多的磁盘碎片会影响DROP TABLE表扫描性能innodb_autoextend_increment系统变量定义了自动扩展共享表空间文件的增量大小但对于File-Per-Table表空间文件不起作用File-Per-Table表空间文件始终自动扩展初始大小根据表定义分配最小的空间之后以4MB为增量进行扩容。6.6 撤销表空间 - Undo Tablespaces6.6.1 撤销表空间的作用撤销表空间中包含撤销日志(Undo Log)撤销日志记录了如何撤销事务对聚集索引记录的最新更改(事务的回滚)通过对事务的回滚从而保证事务ACID特性中的原子性。6.6.1.1 什么是撤销日志介绍完撤销表空间之后将会详细详解撤销日志6.6.2 在使用MySQL时并没有手动创建撤销表空间它是什么时候被创建的MySQL初始化时会在数据目录下创建两个默认的撤销表空间数据文件名分别为undo_001和undo_002数据字典中对应undo表空间名称为innodb_undo_001和innodb_undo_002rootyudukai:/var/lib/mysql# ll # ...省略 -rw-r----- 1 mysql mysql 16777216 May 29 08:52 undo_001 -rw-r----- 1 mysql mysql 16777216 May 29 08:50 undo_002总结MySQL初始化时会在数据目录下创建两个默认的撤销表空间数据文件名分别为undo_001和undo_0026.6.2.1 默认的撤销表空间名称和路径是什么要查看撤销表空间名称和路径请查询INFORMATION_SCHEMA.FILESmysql SELECT TABLESPACE_NAME, FILE_NAME FROM INFORMATION_SCHEMA.FILES - WHERE FILE_TYPE LIKE UNDO LOG; ----------------------------- | TABLESPACE_NAME | FILE_NAME | ----------------------------- | innodb_undo_001 | ./undo_001 | # 数据字典中的名称为innodb_undo_001对应的数据文件为./undo_001 | innodb_undo_002 | ./undo_002 | # 数据字典中的名称为innodb_undo_002对应的数据文件为./undo_002 ----------------------------- 2 rows in set (0.00 sec) mysql6.6.3 可以手动创建撤销表空间吗可以通过使用CREATE UNDO TABLESPACE语句可以创建撤销表空间# 语法 CREATE UNDO TABLESPACE tablespace_name ADD DATAFILE file_name.ibu;6.6.3.1 什么时候需要手动创建撤销表空间对于长时间运行的大事务撤销日志可能会变得很大通过创建额外的撤销表空间来防止单个撤销表空间变得太大从MySQL 8.0.14开始可以在运行时使用CREATE UNDO TABLESPACE语法创建额外的撤销表空间6.6.3.2 使用自己创建的撤销表空间需要注意什么通过系统变量innodb_undo_directory指定撤销表空间的默认存放路径如果不指定默认位置为数据目录撤销表空间文件名必须以.ibu为扩展名定义undo表空间文件名时如果需要指定路径必须使用绝对路径不允许指定相对路径建议使用唯一的撤销表空间文件名避免在以后移动和复制的过程中发生文件名冲突如果指定其他路径那么路径必须在innodb_directories中定义以便MySQL扫描并识别最多支持127个undo表空间包括实例初始化时创建的两个默认表空间MySQL 8.0.23开始初始撤销表空间大小通常为16MB并根据服务器负载以[16MB, 256MB]的增量进行扩容MySQL 8.0.14之前版本额外的撤销表空间通过配置系统变量innodb_undo_tablespaces来创建取值范围[2, 127]MySQL 8.0.14开始此变量已弃用且不再可配置。
