[mysql] 14.12 innodb下的 磁盘I/O与文件空间管理方式

官方文档参考：

https://dev.mysql.com/doc/refman/5.7/en/innodb-disk-management.html

14.12 InnoDB Disk I/O and File Space Management

您使用innodb_data_file_path 配置选项在配置文件中定义的文件形成InnoDB 系统表空间。这些文件在逻辑上串联在一起以组成系统表空间。没有使用的条带化。您无法定义表在系统表空间中的分配位置。在新创建的系统表空间中，InnoDB从第一个数据文件开始分配空间。

为避免将所有表和索引存储在系统表空间内所带来的问题，可以启用 innodb_file_per_table 配置选项（默认开启），该选项将每个新创建的表存储在单独的表空间文件中（扩展名为 .ibd）。对于以这种方式存储的表，磁盘文件中的碎片较少，并且当表被truncate时，该空间将释放给操作系统，而不是仍由InnoDB在系统表空间中保留。有关更多信息，请参见 第14.6.3.2节“每表文件表空间”。

您还可以将表存储在 普通表空间中。普通表空间是使用CREATE TABLESPACE 语法创建的共享表空间。它们可以在MySQL数据目录之外创建，能够容纳多个表，并支持所有行格式的表。有关更多信息，请参见 第14.6.3.3节“常规表空间”。

Pages, Extents, Segments, and Tablespaces

每个表空间由数据库pages组成 。MySQL实例中的每个表空间都具有相同的page size。默认情况下，所有表空间的page size均为16KB；可以通过innodb_page_size参数，在创建MySQL实例时将page size 改为8KB或4KB ，32KB或64KB。有关更多信息，请参阅 innodb_page_size文档。

这些页面分为大小为1MB的extents，用于最大16KB的页面（64个连续的16KB页面，128个8KB页面或256个4KB页面）。对于32KB的pages，extents大小为2MB。对于64KB的pages，extents大小为4MB。表空间内的”files”被称为 InnoDB的segments。（这些段与回滚段不同， 回滚段实际上包含许多表空间段。）

当段在表空间内增长时， InnoDB一次将前32 pages分配给它。之后，InnoDB开始将整个extents分配给该段。InnoDB 一次最多可以向一个segments 中添加4个extents，以确保数据的顺序性。

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

表空间中的某些页面包含其他页面的位图，因此表空间中的某些扩展数据InnoDB块无法整体分配给段，而只能分配给各个页面。

当您通过发出一条SHOW TABLE STATUS语句在表空间中请求可用空间​​时，请 InnoDB报告表空间中绝对可用的extents。InnoDB始终保留一定程度的清理空间和其他内部用途；这些保留的范围不包括在可用空间中。

从表中删除数据时，InnoDB 将收缩相应的B树索引。释放的空间是否可供其他用户使用取决于删除模式是否将单个page或extents释放到表空间中。删除表或删除表中的所有行可以保证将空间释放给其他用户，但请记住，删除的行仅通过perge操作才能物理删除， purge操作会在不再需要事务回滚或一致读取后的一段时间自动发生。 。（请参见 第14.3节“ InnoDB多版本”。）

页面如何与表格行相关

对于4KB，8KB，16KB和32KB的innodb_page_size设置，最大行长度略小于数据库页面的一半 。例如，对于默认的16KBInnoDB页面大小，最大行长度略小于8KB 。对于64KB页面，最大行长度略小于16KB。

如果某行未超过最大行长，则所有行都将存储在本地页面内。如果某行超出最大行长，则选择可变长度列进行外部页外存储，直到该行适合最大行长限制。可变长度列的外部页外存储因行格式而异：

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

14.12.3 InnoDB Checkpoints

增大日志文件可以减少磁盘 I/O的 检查点。通常将日志文件的总大小设置为与等于或者大于缓冲池。尽管从MySQL 5.5开始，过去大日志文件可能会使崩溃恢复花费更多时间，但是崩溃恢复的性能增强使得崩溃后可以快速启动使用大型日志文件。（严格来说，对于具有InnoDB Plugin 1.0.7和更高版本的MySQL 5.1，可以实现这种性能改进。对于MySQL 5.5，可以在默认的InnoDB存储引擎中进行此改进。）

检查点处理的工作方式

InnoDB的 检查点机制称为模糊检查点。小批量地从缓冲池中刷新已修改的数据库页面。无需单批刷新缓冲池，因为这个会在检查点过程中中断用户SQL语句的处理。 

在崩溃恢复期间， InnoDB查找写入日志文件的检查点标签。标签之前的修改都已存在于磁盘中。然后InnoDB从检查点向前扫描日志文件，将记录的修改应用于数据库。

14.12.4 Defragmenting a Table

随机插入二级索引或从二级索引中删除可能导致索引碎片化。碎片意味着磁盘上索引页的物理排序与页上记录的索引排序连续，或者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

您还可以 用来执行 “ null ”更改操作以重建表。 ALTER TABLE tbl_name FORCE

以上两者，都使用 在线DDL。有关更多信息，请参见第14.13节“ InnoDB和在线DDL”。

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

如果对索引的插入总是递增的，并且记录仅从末尾删除，则InnoDB 文件空间管理算法可确保不会发生索引中的碎片。

14.12.5 Reclaiming Disk Space with TRUNCATE TABLE

当 truncate 表去回收操作系统的磁盘空间时，该表必须存放在自己的的.ibd文件。若要将表存储在其自己的.ibd 文件中，innodb_file_per_table必须在创建表时启用。此外，被truncate 的表和其他表之间不能有 外键约束，否则 TRUNCATE TABLE操作将失败。但是，允许在同一表的两列之间使用外键约束。

truncate 表后，将其删除并在新.ibd文件中重新创建 ，并将释放的空间返回给操作系统。这与truncate 存储在 InnoDB 系统表空间 （在中创建的表innodb_file_per_table=OFF）中的表和存储在共享通用表InnoDB空间中的表（在表被truncate 后只能使用释放的空间） 相反 。

truncate 表并将磁盘空间返回给操作系统的功能还意味着 物理备份可以更小。truncate 存储在系统表空间（在时创建的表 innodb_file_per_table=OFF）或共享表空间中的表，会将表空间中未使用的空间块保留下来。