#MySQL IO 负载优化

#InnoDB IO 系统简介

MySQL 的 IO 性能受多种因素影响，包括硬件存储介质类型、软件层的数据库内核架构，以及具体 SQL 语句读取或修改的数据量。

InnoDB 利用操作系统提供的异步 IO 接口，实现了一个用于异步读写数据页、日志和 undo 日志的 IO 子系统。当 SQL 查询请求的数据页不在缓冲池中时，需要进行物理 IO 操作，从底层存储读取或写入数据。

• 对于读取数据页，InnoDB IO 系统采用同步 IO。同步 IO 调用底层的读取接口进行数据读取。对于写入数据页，InnoDB IO 系统采用异步 IO；例如，后台线程异步将脏页刷新到存储。

• 除了对普通数据文件的读写 IO 操作外，写入 redo/undo 日志、写入 binlog 日志、排序临时表以及 DDL 重建表空间等操作也会产生大量 IO。

为了优化磁盘 IO，通常可以通过增加缓冲池大小、调整刷新方式、配置操作系统的刷新阈值，以及使用 MySQL 8.0 提供的 fdatasync 替代 fsync 等方式进行优化。具体优化方法请参考 MySQL 5.7 官方文档 或 MySQL 8.0 官方文档 中的优化章节。

#高 IO 负载的优化建议

#高吞吐写入

建议使用适合高 IOPS（每秒输入输出操作次数）的固态硬盘，这可以有效缓解高 IO 问题。

innodb_log_file_size 控制 InnoDB redo 日志文件的大小。如果该参数值过小，会导致日志文件频繁切换和刷新，增加 IO 操作次数，影响写入性能。适当增大该参数可以减少日志文件切换频率，将多次日志缓冲区刷新合并为一次磁盘 IO 操作，从而提升写入性能。

此外，建议降低读写频率或优化与脏页刷新相关的参数，以解决高 IO 问题。与脏页刷新相关的参数如下表所示。

#临时表产生的大量 IO

进入 MySQL 容器后，可以使用以下命令查看临时文件目录大小。

ls -lh /var/lib/mysql/#innodb_temp

如果临时目录较大，可能是由于 SQL 排序或去重较慢，导致生成了较大的临时表。此时需要优化慢 SQL 查询，减少临时表的使用，从而避免临时表写入带来的 IO 增加。

#读取冷数据产生的大量 IO

可以通过以下 SQL 命令查询缓冲命中率。

select hit_rate from information_schema.INNODB_BUFFER_POOL_STATS;

当 SQL 查询或修改的数据不在内存缓冲池中时，必须从磁盘读取。如果读取数据量非常大，可能导致磁盘读写吞吐量非常高。为避免这种情况，应尽量减少全表扫描，例如避免使用 select * from large_table 之类的语句，以减少对缓冲池的污染。

针对具体业务场景，可以考虑重新设计缓存策略或升级实例规格以提升系统性能。

#DDL 语句带来的 IO 负担

执行 DDL 语句时，可能会发生重建表空间、全表数据扫描、创建索引排序等操作。新表产生的脏页需要刷新，导致大量 IO 吞吐。此外，删除大表时，drop table 操作可能引起 IO 波动。为避免影响业务，应将这些操作安排在业务低峰期执行。

#大事务写入 Binlog 产生重 IO

建议将大事务拆分为多个小事务，避免写入大量 binlog 文件，从而降低 IO 吞吐量。例如，删除大量行的 delete SQL 语句可以拆分为多个较小的 delete 语句执行。这样可以减小每个事务产生的 binlog 文件大小，降低磁盘刷新频率，提高性能。此外，优化业务逻辑也有助于避免大事务的产生。