Day69【概念解析】InnoDB System Tablespace

type

status

date

slug

summary

整理定义

英文名称：InnoDB System Tablespace

中文名称：InnoDB 系统表空间

What is InnoDB System Tablespace?

InnoDB存储引擎使用一个或多个数据文件（即ibdata文件）来存储与InnoDB相关对象的元数据，以及变更缓冲区和双写缓冲区的存储区域。如果表是在系统表空间中创建的，而不是在文件每表或通用表空间中，那么这些文件也可能包含InnoDB表和索引的数据。系统表空间中的数据和元数据适用于MySQL实例中的所有数据库。

在MySQL 5.6.7之前，默认情况下所有InnoDB表和索引都存储在系统表空间内，这经常导致这个文件变得非常大。由于系统表空间永远不会缩小，如果加载了大量的临时数据然后删除，可能会出现存储问题。在MySQL 8.0中，默认模式是文件每表模式，其中每个表及其关联的索引都存储在单独的.ibd文件中。这种默认设置使得使用依赖于DYNAMIC和COMPRESSED行格式的InnoDB特性变得更加容易，例如表压缩、高效存储非页面列和大索引键前缀。

将所有表数据保留在系统表空间或分散在单独的.ibd文件中，对于存储管理有着不同的影响。MySQL Enterprise Backup产品可能会备份一小组大文件，或许多小文件。在有成千上万个表的系统中，处理成千上万个.ibd文件的文件系统操作可能会导致瓶颈。

在MySQL 5.7.6中，InnoDB引入了通用表空间，它们也由.ibd文件表示。通用表空间是使用CREATE TABLESPACE语法创建的共享表空间。它们可以在数据目录之外创建，能够容纳多个表，并支持所有行格式的表。

复述展开

系统表空间在MySQL 5.7和MySQL 8.0版本之间的区别

InnoDB的系统表空间在MySQL 5.7和MySQL 8.0版本之间的主要区别主要体现在默认的表存储方式和对某些特性的支持上。以下是一些关键的差异：

文件每表模式（File-Per-Table Mode）:

MySQL 5.7: 在这个版本中，MySQL开始默认使用文件每表模式。这意味着新创建的InnoDB表和索引会被存储在各自独立的.ibd文件中，而不是在共享的系统表空间中。这有助于避免系统表空间无限增长的问题，并且使得表空间管理更加灵活。

MySQL 8.0: 这个版本继续使用文件每表模式作为默认设置，并且进一步增强了对这种模式的支持，例如通过改进DML操作的性能和提供更多的在线DDL操作。

撤销日志（Undo Log）的存储:

MySQL 5.7: 在这个版本中，撤销日志可以存储在系统表空间中，也可以选择配置为存储在独立的撤销表空间中。

MySQL 8.0: 提供了更多的灵活性来配置撤销日志，包括能够创建多个撤销表空间，以及更好地管理和配置撤销日志。

表空间管理:

MySQL 5.7: 引入了通用表空间的概念，允许用户创建可以包含多个表的共享表空间，这些表空间可以使用所有行格式。

MySQL 8.0: 继续支持通用表空间，并且在表空间管理方面提供了更多的改进和新特性，如表空间加密等。

行格式（Row Formats）:

MySQL 5.7: 支持DYNAMIC和COMPRESSED等行格式，这些格式可以在文件每表模式下使用。

MySQL 8.0: 进一步增强了对这些行格式的支持，特别是在表压缩和大索引键前缀方面。

性能和可扩展性:

MySQL 5.7: 对InnoDB进行了多项性能优化，包括对多核处理器的优化。

MySQL 8.0: 引入了更多的性能改进，如对InnoDB的undo日志、redo日志和Purge操作的优化，以及对数据字典的重构，这些都是为了提高大型数据库的可扩展性和性能。

数据字典:

MySQL 5.7: 数据字典仍然部分存储在系统表空间中，这意味着一些元数据是与表数据混合在一起的。

MySQL 8.0: 引入了一个全新的数据字典架构，它是事务性的，并且完全存储在自己的表空间中。这个改变提高了元数据操作的可靠性和效率，并且简化了系统表空间的结构。

表空间加密:

MySQL 5.7: 提供了对InnoDB表空间的透明加密支持，但这是一个相对较新的特性。

MySQL 8.0: 加密功能得到了增强，包括对redo日志和undo日志的加密，以及更细粒度的加密选项。

信息模式和性能模式:

MySQL 5.7: 提供了丰富的信息模式和性能模式视图，帮助用户监控和优化数据库性能。

MySQL 8.0: 在这方面进行了进一步的增强，提供了更多的监控和诊断能力。

DDL操作:

MySQL 5.7: 支持在线DDL操作，这意味着可以在不中断服务的情况下进行表结构的修改。

MySQL 8.0: 在线DDL操作得到了极大的扩展，支持更多类型的表结构变更，减少了需要重建表的操作类型。

分区表:

MySQL 5.7: 支持表的分区和子分区，但有一些限制。

MySQL 8.0: 对分区表的支持得到了显著改进，包括对分区表的外键支持和更多的分区算法。

这些变化反映了MySQL在版本迭代中对InnoDB存储引擎的持续优化和改进，旨在提高性能、可扩展性和易用性。MySQL 8.0的许多新特性和改进都是基于在5.7版本中的经验和用户反馈。

特性/版本	MySQL 5.7	MySQL 8.0
文件每表模式	默认启用，新表创建为独立文件	继续默认启用，性能和DDL操作得到增强
撤销日志存储	可存储在系统表空间或独立表空间	支持多个撤销表空间，更灵活的配置
通用表空间	支持，可以包含多个表	继续支持，增加了新特性如表空间加密
行格式	支持DYNAMIC和COMPRESSED等	增强对行格式的支持，改进表压缩和索引键前缀
性能和可扩展性	多项性能优化，包括多核处理器优化	进一步性能改进，优化undo/redo日志和Purge操作
数据字典	部分存储在系统表空间中	全新的事务性数据字典架构，独立存储
表空间加密	提供基本的透明加密支持	加密功能增强，包括日志加密和更细粒度选项
信息模式和性能模式	丰富的视图帮助监控和优化	增强监控和诊断能力
DDL操作	支持在线DDL操作	扩展在线DDL操作，减少重建表需求
分区表	支持分区和子分区，有限制	显著改进分区表支持，包括外键和更多算法

配置项相关

在MySQL中，系统表空间是InnoDB存储引擎的默认表空间，用于存储InnoDB表和索引的数据，除非显式地为这些表指定了其他的表空间。系统表空间的配置和使用方式可以通过以下几个方面来理解：

配置项:

innodb_data_file_path: 这个配置项用于指定系统表空间的数据文件（通常是ibdata1）的路径和大小。例如，innodb_data_file_path=ibdata1:12M:autoextend 表示系统表空间的数据文件位于MySQL数据目录中，初始大小为12MB，并且会在需要时自动扩展。

innodb_autoextend_increment: 当系统表空间的数据文件设置为自动扩展时，这个配置项指定了每次自动扩展时增加的大小，单位是MB。

innodb_file_per_table: 这个配置项控制是否为每个新的InnoDB表创建独立的表空间文件（.ibd文件）。在MySQL 5.6.6及以后的版本中，默认值为ON，意味着默认情况下每个表都会使用独立的文件。

使用方式:

创建表时指定表空间: 当创建一个新的InnoDB表时，可以通过CREATE TABLE语句中的TABLESPACE选项来指定表使用的表空间。如果没有指定，表将默认使用系统表空间或者文件每表模式下的独立表空间。

管理系统表空间的大小: 系统表空间的大小管理主要依赖于innodb_data_file_path配置项中的autoextend设置。如果系统表空间的数据文件设置为自动扩展，那么当空间不足时，它会根据innodb_autoextend_increment的设置自动增长。

迁移表到独立表空间: 如果原来的表是在系统表空间中创建的，可以通过ALTER TABLE语句将表移动到独立的表空间文件中，例如：ALTER TABLE tablename ENGINE=InnoDB FILE_PER_TABLE;。

注意事项:

系统表空间的数据文件一旦创建，其大小就不能减小，即使删除了表中的数据。要减小系统表空间的大小，需要创建新的实例并迁移数据。

Decreasing the Size of the InnoDB System Tablespace
Decreasing the size of an existing system tablespace is not supported. The only option to achieve a smaller system tablespace is to restore your data from a backup to a new MySQL instance created with the desired system tablespace size configuration.

系统表空间包含了重要的系统信息，如InnoDB的数据字典和撤销日志，因此需要定期备份，并在操作系统层面上保护这些文件不被损坏。

在MySQL 8.0中，系统表空间不再包含数据字典信息，因为数据字典已经被移到了自己的表空间中。

通过合理配置和管理系统表空间，可以确保数据库的性能和可维护性。在实际使用中，推荐开启文件每表模式，这样可以更好地管理磁盘空间，提高数据库的性能。

理解体会

首先，系统表空间的设计决定了它是InnoDB存储引擎的心脏。所有的InnoDB表和索引默认都存储在这里，除非显式地为它们指定了其他的表空间。这种设计简化了初始的数据库部署，但随着数据库的增长，它可能导致系统表空间变得庞大而难以管理。这个认识让我在设计数据库时更加注重表空间的规划，以避免未来可能出现的性能瓶颈。

其次，系统表空间的不可缩减性让我意识到了数据清理和存储优化的重要性。在实际操作中，我学会了定期归档旧数据，以减少对系统表空间的压力。同时，我也开始使用innodb_file_per_table配置，这样每个表的数据都存储在独立的文件中，这不仅有助于管理磁盘空间，还可以提高某些数据库操作的性能。

再者，系统表空间的备份和恢复也是我关注的焦点。由于系统表空间包含了重要的系统信息，任何损坏都可能导致整个数据库实例的不可用。因此，我学会了定期备份系统表空间，并且在备份策略中考虑到了不同的恢复场景。这种做法在我遇到数据丢失的危机时发挥了关键作用，帮助我成功恢复了数据。

最后，我也体会到了监控和调整系统表空间配置的重要性。通过监控系统表空间的使用情况，我可以及时发现潜在的问题，并通过调整配置来解决这些问题。例如，我可以增加数据文件的大小，或者启用自动扩展功能来应对数据增长。这些操作需要谨慎进行，因为它们会影响到数据库的性能和稳定性。