【MySQL精通之路】InnoDB磁盘I/O和文件空间管理(11)

主博客：

【MySQL精通之路】InnoDB存储引擎-CSDN博客

目录

1.InnoDB磁盘I/O

1.1 预读

1.2 双写缓冲区

2.文件空间管理

2.1 Pages, Extents, Segments, and Tablespaces（很重要）

2.2 配置保留文件段页面的百分比

2.3 页与表行的关系

3.InnoDB检查点

4.对表进行碎片整理

5.TRUNCATE TABLE回收磁盘空间

---

作为DBA，您必须管理磁盘I/O以防止I/O子系统饱和，并管理磁盘空间以避免填满存储设备。

ACID设计模型需要一定数量的I/O，这些I/O看起来可能是多余的，但有助于确保数据的可靠性。

在这些限制条件下，InnoDB试图优化数据库工作和磁盘文件的组织，以最大限度地减少磁盘I/O量。

有时，I/O操作会推迟到数据库闲时，或者直到所有东西都需要保持一致状态，例如在快速关闭后的数据库重新启动期间。

本节讨论了默认类型MySQL表（也称为InnoDB表）的I/O和磁盘空间的主要注意事项：

控制用于提高查询性能的后台I/O量。

启用或禁用以牺牲额外I/O为代价提供额外耐用性的功能。

将表组织成许多小文件、几个大文件或两者的组合。

平衡redolog日志文件的大小与日志文件变满时发生的I/O活动。

如何重新组织表以获得最佳查询性能。

---

1.InnoDB磁盘I/O

InnoDB在可能的情况下使用异步磁盘I/O，通过创建多个线程来处理I/O操作，同时允许其他数据库操作在I/O仍在进行时继续执行。

在Linux和Windows平台上，InnoDB使用可用的操作系统和库函数来执行“本地”异步I/O。

在其他平台上，InnoDB仍然使用I/O线程，但线程实际上可能会等待I/O请求完成；

这种技术被称为“模拟”异步I/O。

1.1 预读

如果InnoDB能够确定很有可能很快就需要数据，它会执行预读操作，将数据放入缓冲池，以便在内存中可用。对连续数据发出几个大的读取请求可能比发出几个分散的小请求更有效。InnoDB中有两种预读启发法：

在顺序预读中，如果InnoDB注意到对表空间中某个段的访问模式是顺序的，它会提前向I/O系统发布一批数据库页面的读取。

在随机预读中，如果InnoDB注意到表空间中的某个区域似乎正在被完全读取到缓冲池中，它会将剩余的读取发布到I/O系统。

有关配置启发式预读的信息，请参阅“配置InnoDB缓冲池预取（预读）”。

【MySQL精通之路】InnoDB配置(8)-缓存池配置-CSDN博客

1.2 双写缓冲区

InnoDB使用了一种新颖的文件刷新技术，该技术涉及一种称为doublewrite缓冲区的结构，在大多数情况下默认启用该结构（InnoDB_doublewrite=ON）。

它为意外退出或停电后的恢复增加了安全性，并通过减少对fsync（）操作的需求来提高大多数Unix的性能。

在将页面写入数据文件之前，InnoDB首先将它们写入一个称为双写缓冲区的存储区域。

只有在对双写缓冲区的写入和刷新完成后，InnoDB才会将页面写入数据文件中的正确位置。

如果在页面写入过程中出现操作系统、存储子系统或意外的mysqld进程退出（导致页面撕裂），InnoDB稍后可以在恢复期间从doublewrite缓冲区中找到页面的良好副本。

有关双写缓冲区的更多信息，请参阅“双写缓冲”。

【MySQL精通之路】InnoDB(6)-磁盘结构(4)-双写缓冲区-CSDN博客

2.文件空间管理

使用innodb_data_file_path配置选项在配置文件中定义的数据文件形成innodb系统表空间。

这些文件在逻辑上连接起来，形成系统表空间。

没有使用分段。您不能定义表在系统表空间中的分配位置。在新创建的系统表空间中，InnoDB从第一个数据文件开始分配空间。

为了避免在系统表空间中存储所有表和索引时出现的问题，可以启用innodb_file_per_table配置选项（默认选项），该选项将每个新创建的表存储在一个单独的表空间文件中（扩展名为.ibd）。对于以这种方式存储的表，磁盘文件中的碎片较少，当表被截断时，空间会返回到操作系统，而不是由InnoDB在系统表空间中保留。

有关更多信息，请参阅“FPT表空间”。

也可以将表存储在通用表空间中。通用表空间是使用CREATE TABLESPACE语法创建的共享表空间。它们可以在MySQL数据目录之外创建，能够容纳多个表，并支持所有行格式的表。

有关更多信息，请参阅“通用表空间”。

2.1 Pages, Extents, Segments, and Tablespaces（很重要）

页、区、段和表空间

        每个表空间都由数据库页面组成。

        MySQL实例中的每个表空间都具有相同的页面大小。

        默认情况下，所有表空间的页面大小为16KB；

        您可以在创建MySQL实例时通过指定innodb_page_size选项将页面大小减少到8KB或4KB。您也可以将页面大小增加到32KB或64KB。

有关更多信息，请参阅innodb_page_size文档。

        对于大小不超过16KB的页面（64个连续的16KB页面、128个8KB页面或256个4KB页面），页被分组为大小为1MB的扩展区。

        对于32KB的页面大小，一个区大小为2MB。对于64KB的页面大小，一个区大小为4MB。表空间内的“文件”在InnoDB中被称为段（Segments）。（这些段与回滚段不同，回滚段实际上包含许多表空间段。）

        当一个段在表空间内增长时，InnoDB会一次性为其分配前32个页。之后，InnoDB开始将整个区分配给该段。InnoDB一次最多可以向一个段添加4个区，以确保数据的良好顺序性。

InnoDB中为每个索引分配了两个段。一个用于B+树的非叶节点，另一个用于叶节点。在磁盘上保持叶节点连续可以实现更好的顺序I/O操作，因为这些叶节点包含实际的表数据。

表空间中的一些页面包含其他页面的Bitmap位图，因此InnoDB表空间中一些区不能作为一个整体分配给段，而只能作为单独的页面。

当您通过发出SHOW TABLE STATUS语句来请求表空间中的可用区时，InnoDB会报告表空间中绝对空闲的区。InnoDB总是保留一些区用于清理和其他内部目的；这些保留的区不包括在可用区中。

当您从表中删除数据时，InnoDB会收缩相应的B树索引。释放的空间是否可供其他用户使用取决于删除模式是否将单个页或区释放到表空间。

删除表或从表中删除所有行可以保证将空间释放给其他用户，但请记住，删除的行仅通过purge操作进行物理删除，清除操作在事务回滚或一致性读取不再需要这些行后自动发生。（参见“InnoDB多版本”。）

2.2 配置保留文件段页面的百分比

innodb_segment_reserve_factor变量是MySQL 8.0.26中引入的一个高级功能，它允许定义保留为空页的表空间文件段页面的百分比。

为将来的增长保留一定比例的页面，以便可以连续分配B树中的页面。修改保留页面百分比的能力允许对InnoDB进行微调，以解决数据碎片或存储空间使用效率低下的问题。

该设置适用于FPT表空间和通用表空间。

innodb_segment_reserve_factor默认设置为12.5%，与之前MySQL版本中保留的页面百分比相同。

innodb_segment_reserve_factor变量是动态的，可以使用SET语句进行配置。例如

mysql> SET GLOBAL innodb_segment_reserve_factor=10;

2.3 页与表行的关系

对于4KB、8KB、16KB和32KB innodb_page_size设置，最大行长度略小于数据库页大小的一半。

例如

对于默认的16KB InnoDB页大小，最大行长度略小于8KB。

对于64KB的innodb_page_size设置，最大行长度略小于16KB。

如果一行不超过最大行长度，则所有行都存储在页面的本地。

如果一行超过最大行长，则会选择可变长度列用于外部页外存储，直到该行符合最大行长限制。

可变长度列的外部页存储因行格式而异：

COMPACT和REDUNDANT行格式

当选择可变长度列用于外部页外存储时，InnoDB将前768个字节存储在行的本地，其余字节存储在溢出页的外部。每个这样的列都有自己的溢出页列表。768字节的前缀附带一个20字节的值，该值存储列的真实长度，并指向存储该值其余部分的溢出列表。

参见“InnoDB行格式”

DYNAMIC和COMPRESSED行格式

DYNAMIC和COMPRESSED当选择可变长度列作为外部页外存储时，InnoDB将一个20字节的指针本地存储在行中，其余存储到溢出页中。

参见“InnoDB行格式”

LONGBLOB和LONGTEXT列必须小于4GB，并且包括BLOB和TEXT列在内的总行长度必须小于4GB。

3.InnoDB检查点

使日志文件非常大可能会减少检查点操作期间的磁盘I/O。

将日志文件的总大小设置为与缓冲池一样大甚至更大通常是有意义的。

检查点处理的工作原理

InnoDB实现了一种称为模糊检查点的检查点机制。

InnoDB以小批量从缓冲池中刷新修改后的数据库页面。

不需要在一个批次中刷新缓冲池，因为这会在检查点过程中中断对用户SQL语句的处理。

在崩溃恢复过程中，InnoDB会查找写入日志文件的检查点标签。它知道在标签之前对数据库的所有修改都存在于数据库的磁盘映像中。然后InnoDB从检查点向前扫描日志文件，将记录的修改应用于数据库。

4.对表进行碎片整理

在二级索引中随机插入或从二级索引删除会导致索引变得碎片化。

碎片化意味着磁盘上索引页的物理顺序与页上记录的索引顺序不接近，或者在分配给索引的64页块中有许多未使用的页。

碎片化的一个症状是表占用的空间超过了它“应该”占用的空间。

这到底是多少，很难确定。所有InnoDB数据和索引都存储在B树中，它们的填充因子可能在50%到100%之间变化。

碎片化的另一个症状是，像这样的表扫描所花费的时间比“应该”花费的时间更长：

SELECT COUNT(*) FROM t WHERE non_indexed_column <> 12345;

前面的查询要求MySQL执行完整的表扫描，这是大表中最慢的查询类型。

为了加快索引扫描速度，您可以定期执行“null”ALTER TABLE操作，这会导致MySQL重新生成表：

ALTER TABLE tbl_name ENGINE=INNODB

您还可以使用ALTER TABLE tbl_name FORCE执行一个空修改操作来重新构建表。

ALTER TABLE tbl_name ENGINE=INNODB和ALTER TABLE tbl_name FORCE都使用联机DDL。

有关更多信息，请参阅“InnoDB和在线DDL”。

执行碎片整理操作的另一种方法是使用mysqldump将表转储到文本文件中，删除表，然后从转储文件中重新加载它。

如果索引中的插入始终是升序的，并且只从末尾删除记录，那么InnoDB文件空间管理算法可以保证索引中不会出现碎片。

5.TRUNCATE TABLE回收磁盘空间

        要在TRUNCATE InnoDB表时回收操作系统磁盘空间，该表必须存储在自己的.ibd文件中。

        要将表存储在其自己的.ibd文件中，必须在创建表时启用innodb_file_per_table。

        此外，被TRUNCATE的表和其他表之间不能有外键约束，否则TRUNCATE TABLE操作将失败。但是，允许在同一表中的两列之间使用外键约束。

        当表被TRUNCATE时，它会被删除并在新的.ibd文件中重新创建，释放的空间会返回到操作系统。这与TRUNCATE存储在InnoDB系统表空间内的InnoDB表（当InnoDB_file_per_table=OFF时创建的表）和存储在共享通用表空间中的表形成对比，其中只有InnoDB可以在TRUNCATE表后使用释放的空间。

        TRUNCATE表并将磁盘空间返回到操作系统的能力也意味着物理备份可以更小。TRUNCATE存储在系统表空间（innodb_file_per_table=OFF时创建的表）或通用表空间中的表会在表空间中留下未使用的区域。