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问题。与脏页刷新相关的参数如下表所示。
| 参数名称 | 描述 |
|---|---|
| innodb_max_dirty_pages_pct | 控制InnoDB中脏页的最大比例。值越大，允许的脏页越多，但也会增加脏数据的刷新频率和时间。 |
| innodb_max_dirty_pages_pct_lwm | 控制当InnoDB中达到脏页最大比例后，何时开始刷新脏数据。值越小，InnoDB越早开始刷新脏数据，但也会增加刷新频率和时间。 |
| innodb_io_capacity | 控制InnoDB在磁盘上可以处理的IO吞吐量。值越大，InnoDB对磁盘的压力越大，但也可以加快脏数据的刷新速度。 |
| innodb_io_capacity_max | 控制InnoDB在磁盘上可以处理的最大IO吞吐量，决定脏数据刷新速度的上限。 |
| innodb_lru_scan_depth | 控制InnoDB在刷新脏页时扫描的缓冲池深度，从而影响高吞吐量写入环境中的IO负载。如果数值过大，每次刷新周期需要扫描更多页，导致更多无效的IO读取，增加整体IO负载并影响性能。如果数值过小，可能只允许InnoDB扫描和清理非常少量的脏页，这可能导致脏页无限积累和严重的IO竞争，加重IO负载。 |
临时表带来的大量IO
进入MySQL容器后，可以使用以下命令检查临时文件目录的大小。
如果临时目录很大，可能是由于SQL排序或去重速度慢，导致生成了大型临时表。在这种情况下，有必要优化缓慢的SQL查询，以减少临时表的使用，避免临时表写入带来的IO增加。
读取冷数据造成的大量IO
您可以使用以下SQL命令查询缓冲命中率。
当SQL查询或修改的数据不在内存中的缓冲池时，必须从磁盘读取。如果要读取的数据量非常大，可能导致磁盘读写吞吐量非常高。为了避免这种情况，应尽量减少全表扫描，例如避免使用类似select * from large_table的语句，以减少对缓冲池的污染。
针对具体的业务场景，可以考虑重新设计缓存策略或升级实例规格，以提升系统性能。
DDL语句造成的IO负担
执行DDL语句时，可能会发生重建表空间、扫描全表数据和创建索引排序等操作。新表生成的脏页需要刷新，导致大量的IO吞吐量。此外，当需要删除大表时，drop table操作可能会导致IO波动。为了避免影响业务操作，这些操作应安排在业务低峰期进行。
大事务写入二进制日志产生重IO
建议将大事务拆分为较小的事务，以避免写入大量的二进制日志文件，从而减少IO吞吐量。例如，如果一个删除SQL语句需要删除大量行，可以将其拆分为多条较小的删除语句执行。这可以减少每个事务生成的二进制日志文件大小，并降低磁盘刷新的频率，提高性能。此外，优化业务逻辑可以帮助防止大事务的发生。