InnoDB表空间类型及特征

InnoDB表空间：可以存储一个或多个InnoDB表和相关索引的数据文件。

1.数据表空间

①系统表空间

内容：存储元数据和缓冲区，包括

• InnoDB数据字典：表、索引和列元数据
• 更改缓冲区：对二级索引页面的更改
• 双写缓冲区：确保崩溃安全写入

特征：

• 这是一个单一的逻辑存储区，包含数据目录中的一个或多个文件
• 通常命名，ibdata1、ibdata2、…
• 每个文件可以是常规文件或裸设备分区（由应用程序直接对它进行读写操作，I/O效率更高）

相关：

• innodb_data_file_path选项配置磁盘上InnoDB系统表空间文件的大小和物理位置
  • 默认值：ibdata1:12M:autoextend (文件名:大小:自动扩展)
• 如果系统表空间中有一组文件，则只有该组中的最后一个文件可以自动扩展，其他文件大小固定
• innodb_file_per_table选项指定MySQL是在系统表空间中还是在单独的.ibd文件中存储新表数据和索引.

②单表文件表空间

内容：包含来自单个表的数据和索引

特征：

• 为数据库目录中的表 命名的.ibd文件
• 默认启用
• 表压缩（不能在同一个通用或系统表空间中混合压缩和未压缩的表）
• 空间回收（使用TRUNCATE）：InnoDB删除并重新创建截断的单表文件表空间，将可用空间释放回文件系统

示例：

• 在当前数据库的目录中创建fpt_table.ibd文件

CREATE TABLE fpt_table(a INT PRIMARY KEY, b CHAR(4));

• 使用显式TABLESPACE子句的示例 :

CREATE TABLE fpt_table(a INT PRIMARY KEY, b CHAR(4))
TABLESPACE=innodb_file_per_table;

• 即使innodb_file_per_table为OFF，也会创建一个file-per-table表空间

③通用表空间

内容：包含来自多个表的数据和索引

特征：

• 创建表空间时 命名的.ibd文件
• 不属于任何特定的数据库
• 删除大量数据的语句的文件系统开销更少（如DROP TABLE或TRUNCATE TABLE）
• 消耗更少的内存来存储表空间元数据

示例：

• 在数据目录中创建myts_data1.ibd文件

CREATE TABLESPACE myts ADD DATAFILE 'myts_data1.ibd';

• 通过指定TABLESPACE子句将新表放置在通用表空间中

CREATE TABLE gen_table(a INT PRIMARY KEY, b CHAR(4)) TABLESPACE=myts;

• 使用ALTER TABLE将表移动到通用表空间

ALTER TABLE fpt_table TABLESPACE=myts;

定位在数据目录外的表空间

• 将数据集与其他数据物理分离
• 将一些数据放在比一般数据更快的设备上

示例：

• 创建表时使用DATA DIRECTORY子句 ：

CREATE TABLE ext_table(a INT PRIMARY KEY, b CHAR(4))
DATA DIRECTORY='/datadir2';

2.还原表空间

• 默认情况下，还原日志驻留在两个还原表空间中
  • 拥有两个还原表空间会减少每个表空间的最大大小
  • 默认情况下，它们驻留在MySQL数据目录中
    • 通过设置innodb_undo_directory选项可更改它们的位置
• 还原日志具有与标准数据不同的I/O模式
  • 这些模式使它们非常适合存储在SSD上
• 设置innodb_rollback_segments选项以更改分配给每个还原表空间的回滚段数量
  • 默认值 128 是允许的最大数量

3.临时表空间

何时创建临时表：

• 由MySQL在复杂查询中执行一些操作时
  • 内部表使用MEMORY存储引擎
  • 无法直接控制它何时执行此操作
• 由用户存储用户数据以供在会话中使用时

相关：

• 临时表空间文件的相对路径、名称、大小和属性取决于innodb_temp_data_file_path的值。
• 如果未设置值，则所有临时文件都在innodb_data_home_dir目录中名为ibtmp1的12MB自动扩展数据文件中创建。

其他相关

• 数据库中可以混合使用表空间类型
  • 一些表使用单表文件表空间
  • 一些使用通用表空间的表
• 为通用表空间指定数据文件时使用相对或绝对路径：
  • 相对路径使用数据目录作为它们的根目录
  • 绝对路径可以引用任何有效的文件系统目标
  • 如，使用绝对路径：

CREATE TABLESPACE ext_ts ADD DATAFILE '/datadir2/ext_ts_data1.ibd';