InnoDB 表空间

合集 - MySQL(18)

1.【汇总】 InnoDB 存储引擎2023-08-252.InnoDB 的介绍2023-08-143.Innodb 与 ACID 模型2023-03-224.MySQL事务执行过程2023-03-285.InnoDB 多版本控制2023-03-246.InnoDB 存储引擎架构2023-08-147.InnoDB 内存架构2023-03-278.InnoDB 磁盘架构2023-08-25

9.InnoDB 表空间2023-08-17

10.InnoDB 双写缓冲区2023-08-1711.MySQL服务器日志2023-03-2812.InnoDB 的锁2023-08-1813.InnoDB 的事务模型2023-08-2314.InnoDB 中不同 SQL 语句设置的锁2023-08-2415.InnoDB 中的死锁2023-08-2416.InnoDB 磁盘 I/O 和 文件空间管理2023-08-2517.MySQL 查询执行计划2024-01-0518.【MySQL优化】索引失效的场景2023-09-14

收起

目录

• Tablespace
  • System Tablespace
    • File-Per-Table Tablespaces
    • File-Per-Table Tablespace Data Files
    • 优缺点
      • 优点
      • 缺点
  • General Tablespaces
    • 通用表空间的行格式

Tablespace

System Tablespace

系统表空间（system tablespace）是更改缓冲区（change buffer）的存储区域。如果表不是在每个表文件（file-per-table）或通用表空间（general tablespace）中创建的，而是在系统表空间创建的，系统表空间中还可能包含表和索引数据。

系统表空间可以有一个或多个数据文件。默认情况下，会在数据目录中创建一个名为 ibdata1 的系统表空间数据文件，系统表空间数据文件的大小和数量由 innodb_data_file_path 启动选项定义的。

File-Per-Table Tablespaces

A file-per-table tablespace contains data and indexes for a single InnoDB table, and is stored on the file system in a single data file.

每个表文件表空间（file-per-table tablespace）包含单个 InnoDB 表的数据和索引，并存储在文件系统上的单个数据文件中。

File-Per-Table Tablespace Data Files

A file-per-table tablespace is created in an *.ibd data file in a schema directory under the MySQL data directory. The *.ibd file is named for the table (${table_name}.ibd).

例如，当我们创建一个 db_larry.t_user_info_1 表后，就会在对应的目录下面，生成一个表数据文件：

$ sudo ls -la /var/lib/mysql/db_larry
-rw-r----- 1 mysql mysql 114688 Aug 16 16:40 t_user_info_1.ibd

优缺点

与共享表空间（例如，系统表空间或通用表空间）相比，file-per-table 表空间具有以下优点和缺点。

优点

• 在 file-per-table 表空间中创建的表，发生截断或删除后，相应的磁盘空间将返回给操作系统；

  截断或删除保存在共享表空间中的表，会在共享表空间的数据文件中创建可用空间，该空间只能用于数据 InnoDB。换句话说，在表被截断或删除后，共享表空间数据文件的大小不会缩小。

• 对驻留在共享表空间中的表进行表复制ALTER TABLE 操作会增加该表空间占用的磁盘空间量；

  此类操作，可能需要与表中的数据加上索引一样多的额外空间。该空间不会像 file-per-table 表空间那样释放回操作系统。

• TRUNCATE TABLE 语句，在 file-per-table 表空间中的表中执行时，性能会更好；

• file-per-table 表空间的数据文件，可以创建在独立的存储设备上，这样，就可以实现 I/O 优化、空间管理或备份目的；

• 可以从另一个 MySQL 实例，导入在 file-per-table 表空间中的表；

• 在 file-per-table 表空间中创建的表，支持 DYNAMIC、COMPRESSED 行格式，而系统表空间不支持这些功能；

• 当发生数据损坏、备份或二进制日志不可用或 MySQL 服务器实例无法重新启动时，存储在各个表空间数据文件中的表，可以节省时间并提高成功恢复的机会；

• 可以使用 MySQL Enterprise Backup 快速备份或恢复在 file-per-table 表空间中创建的表，而无需中断其他 InnoDB 表的使用；

  这对于不同备份计划或需要不太频繁备份的表是有益的。

• file-per-table 表空间允许通过监视表空间数据文件的大小来监视文件系统上的表大小；

• 能提高磁盘写入的性能；

  当变量设置为 innodb_flush_method = O_DIRECT 时，常见的 Linux 文件系统不允许对单个文件（例如，共享表空间数据文件）进行并发写入。

  因此，file-per-table 表空间与此设置结合使用时，可能会提高性能。

• 表空间大小不受限制。

  共享表空间中的表的大小受到 64TB 表空间大小限制。相比之下，每一个 file-per-table 的表空间大小最大都是为 64TB，这样就就为单个表的大小增长提供了充足的空间。

缺点

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

• 多个 file-per-table 表空间的数据文件刷新时，需要多次调用 fsync。

  fsync 操作是在多个 file-per-table 数据文件上执行的，而不是在单个共享表空间数据文件上执行的。由于 fsync操作是针对每个文件的，因此无法合并多个表的写入操作，这可能会导致多次调用 fsync 操作。

• mysqld 必须为每个 file-per-table 表空间保留一个打开的文件句柄，如果 file-per-table 表空间中有大量表，这可能会影响性能。

• 当每个表都有自己的数据文件时，需要更多的文件描述符。

• DROP TABLE 可能会产生更多碎片，这可能会影响全表扫描的性能。

  但是，如果碎片得到管理，file-per-table 表空间可以提高这些操作的性能。

• 删除驻留在 file-per-table 表空间中的表时，会扫描缓冲池，对于大型缓冲池，这可能需要几秒钟的时间。扫描是通过广泛的内部锁执行的，这可能会延迟其他操作。

• 该 innodb_autoextend_increment 变量定义了自动扩展共享表空间文件变满时扩展大小的增量大小，不适用于 file-per-table 表空间文件，这些文件无论设置如何都会自动扩展 innodb_autoextend_increment 。

  初始的每表文件表空间扩展量很小，之后扩展以 4MB 为增量。

General Tablespaces

通用表空间是使用 CREATE TABLESPACE 语法创建的共享 InnoDB 表空间。

通用表空间提供以下功能：

• 与系统表空间类似，通用表空间是能够存储多个表数据的共享表空间。

• 与每个表文件的表空间 相比，通用表空间具有潜在的内存优势 。服务器在表空间的生命周期内将表空间元数据保存在内存中。与单独的每个表文件表空间中的相同数量的表相比，较少的通用表空间中的多个表消耗的表空间元数据内存更少。

• 一般表空间数据文件可以放置在相对于或独立于MySQL数据目录的目录中，这为您提供了 file-per-table表空间的许多数据文件和存储管理功能。与每表文件表空间一样，将数据文件放置在 MySQL 数据目录之外的功能允许您单独管理关键表的性能，为特定表设置 RAID 或 DRBD，或者将表绑定到特定磁盘。

• 通用表空间支持所有表行格式和相关功能。

• 该TABLESPACE选项可用于 CREATE TABLE在常规表空间、每表文件表空间或系统表空间中创建表。

• 该TABLESPACE选项可用于 ALTER TABLE在常规表空间、每表文件表空间和系统表空间之间移动表。

通用表空间的行格式

通用表空间支持所有表行格式（REDUNDANT、COMPACT、DYNAMIC、COMPRESSED），但需要注意的是，由于物理页大小不同，压缩表和未压缩表不能共存于同一个通用表空间中。

mysql> CREATE TABLESPACE `ts2` ADD DATAFILE 'ts2.ibd' FILE_BLOCK_SIZE = 8192 Engine=InnoDB;
 
mysql> CREATE TABLE t4 (c1 INT PRIMARY KEY) TABLESPACE ts2 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;