[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

ACID设计模型需要预留一定量的  I/O，来保证数据的可靠性。在一些限制下， 在InnoDB中，优化数据库工作和磁盘文件的组织，以最大程度地减少磁盘I / O。有时，I / O会推迟到数据库空闲，或者直到所有内容都需要进入一致状态为止，例如在快速关闭后重新启动数据库期间。

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

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

启用或禁用可提供额外持久性的功能，但需要付出额外的I / O代价。

将表组织成许多小文件，一些大文件或两者的组合。

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

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

InnoDB有时使用异步磁盘  I/O，方法是创建多个线程来处理   I/O操作，同时可以进行其他数据库操作。在Linux和Windows平台上，InnoDB使用可用的OS和库函数来执行 “native” 异步  I/O。在其他平台上，InnoDB仍然使用  I/O线程，但是这些线程实际上可能会等待  I/O 请求完成。该技术称为“模拟” 异步I / O。

如果InnoDB可以确定即将有大的数据请求，它将执行预读操作，将数据加载到缓冲池，以便可以在内存中使用。对持续的、大的读取请求要比个较小的、散布的请求更为有效。对于innodb有以下两种预读：

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

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

有关配置预读启发式方法的信息，请参见 第14.8.3.4节“配置InnoDB缓冲池预取（预读）”。

InnoDB使用一种新的文件刷新技术，该技术涉及一种称为doublewrite缓冲区的结构 ，在大多数情况下（innodb_doublewrite=ON）默认启用。它为意外退出或停电后的恢复增加了安全性，并通过减少fsync()操作需求来提高了大多数Unix类型的性能。

在将页面InnoDB 写到数据文件之前，首先将它们写到称为doublewrite缓冲区的连续表空间区域中。仅在完成对双InnoDB 写缓冲区的写入和刷新之后，才将页面写入数据文件中。如果在页面写入过程中存在操作系统，存储或意外的 mysqld进程退出（导致页面损坏的 情况），InnoDB则稍后可以在恢复期间从doublewrite缓冲区中找到该页面的未损坏副本。

如果系统表空间文件（“ ibdata文件”）位于支持原子写的Fusion-io设备上，则自动禁用双写缓冲，并且将Fusion-io原子写用于所有数据文件。由于doublewrite缓冲区设置是全局的，因此对于非Fusion-io硬件上驻留的数据文件，还将禁用doublewrite缓冲。此功能仅在Fusion-io硬件上受支持，并且仅在Linux上的Fusion-io NVMFS中启用。要充分利用此功能， 建议innodb_flush_method设置 O_DIRECT为。

您使用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组成 。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。

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

紧凑和冗余行格式

当将可变长度列选择用于外部页外存储时，InnoDB将前768个字节本地存储在该行中，其余部分从外部存储到溢出页中。每个此类列都有其自己的溢出页面列表。768字节的前缀附带一个20字节的值，该值存储列的真实长度，并指向溢出列表，该值的其余部分存储在溢出列表中。请参见 第14.11节“ InnoDB行格式”。

动态和压缩行格式

如果将可变长度列选择用于外部页外存储，则InnoDB在该行中本地存储一个20字节的指针，其余部分在外部存储到溢出页中。请参见第14.11节“ InnoDB行格式”。

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

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

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

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

随机插入二级索引或从二级索引中删除可能导致索引碎片化。碎片意味着磁盘上索引页的物理排序与页上记录的索引排序连续，或者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）或共享表空间中的表，会将表空间中未使用的空间块保留下来。

MySQL 数据库

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。