#MySQL IO负载优化

#InnoDB IO系统简介

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

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

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

• 除了对正常数据文件的读取和写入IO操作外，写入重做/撤销日志、写入二进制日志、排序临时表以及DDL重建表空间等操作也会产生大量的IO。

为了优化磁盘IO，一般可以增加缓冲池的大小、调整刷新方法、配置操作系统的刷新阈值，以及使用MySQL 8.0提供的fdatasync替代fsync。具体的优化方法，请参见MySQL 5.7官方文档或MySQL 8.0官方文档中的优化部分。

#高IO负载的优化建议

#高吞吐量写入

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

innodb_log_file_size控制InnoDB重做日志文件的大小。如果参数值太小，会导致频繁的日志文件循环和刷新，增加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波动。为了避免影响业务操作，这些操作应安排在业务低峰期进行。

#大事务写入二进制日志产生重IO

建议将大事务拆分为较小的事务，以避免写入大量的二进制日志文件，从而减少IO吞吐量。例如，如果一个删除SQL语句需要删除大量行，可以将其拆分为多条较小的删除语句执行。这可以减少每个事务生成的二进制日志文件大小，并降低磁盘刷新的频率，提高性能。此外，优化业务逻辑可以帮助防止大事务的发生。