mysql中的磁盘I/O性能问题与优化方法

InnoDB 缓冲池过小是磁盘 I / O 瓶颈主因，需设为内存 50%–75% 且为实例数整数倍；慢查询缺索引、排序 / 临时表溢出磁盘、刷盘策略不当（如 innodb_flush_log_at_trx_commit=1+sync_binlog=1）均加剧 I /O。

MySQL 的 innodb_buffer_pool_size 设置过小导致频繁刷盘

这是最常见的磁盘 I/O 瓶颈根源：InnoDB 缓冲池太小，无法缓存热数据和索引页，大量 SELECT 和 UPDATE 操作被迫读写磁盘。观察 SHOW ENGINE INNODB STATUS 中的 Buffer pool hit rate，低于 99% 就值得警惕。

• 生产环境建议设为物理内存的 50%–75%，但不超过 innodb_buffer_pool_instances × 1GB（避免单实例锁争用）
• 动态调整需 MySQL 5.7+，执行 SET GLOBAL innodb_buffer_pool_size = 12884901888;（12G），注意该值必须是 innodb_buffer_pool_instances 的整数倍
• 若启用了 innodb_buffer_pool_dump_at_shutdown 和 innodb_buffer_pool_load_at_startup，可加速冷启动后的缓存预热，减少首波 I/O 峰值

慢查询引发的随机 I/O 放大效应

没有索引的 WHERE 条件或 ORDER BY 字段，会让 MySQL 执行全表扫描或文件排序（Using filesort），触发大量随机磁盘读取——机械 硬盘 尤其敏感。可通过 slow_query_log + pt-query-digest 定位真实 I/O 开销高的语句。

• 检查执行计划中是否出现 type: ALL 或 Extra: Using temporary; Using filesort
• 对高频查询的 WHERE、JOIN、ORDER BY 字段组合建立复合索引，避免冗余索引拖慢写入
• 用 EXPLAIN FORMAT=JSON 查看 rows_examined_per_scan 和 disk_reads（MySQL 8.0.22+）字段，直观看 I/O 成本

innodb_flush_log_at_trx_commit 与 sync_binlog 的权衡取舍

这两个参数共同决定事务持久化时的刷盘行为，直接影响写入吞吐和崩溃安全性。默认值 innodb_flush_log_at_trx_commit = 1 + sync_binlog = 1 最安全，但每事务强制两次 fsync，I/O 压力最大。

• 允许丢失最多 1 秒事务时，可设为 innodb_flush_log_at_trx_commit = 2（log buffer 写 OS cache，每秒刷盘）+ sync_binlog = 1000（每 1000 次事务 sync 一次 binlog）
• SSD 环境下，innodb_flush_method = O_DIRECT 可绕过 OS page cache，避免双重缓存，降低内存压力和 swap 风险
• 禁用 innodb_doublewrite 能减 I/O，但极端情况下可能引发页损坏，仅限压测或日志型只写库

临时表和排序区溢出到磁盘

当 sort_buffer_size、join_buffer_size 或 tmp_table_size / max_heap_table_size 设置过小，MySQL 会把本可在内存完成的操作转为磁盘临时表（Created_tmp_disk_tables 指标飙升），触发大量顺序 I/O。

• 监控状态变量：SHOW GLOBAL STATUS LIKE 'Created_tmp%';，若 Created_tmp_disk_tables / Created_tmp_tables > 5%，说明内存不足
• 按连接粒度调优更稳妥：在慢查询前加 SET SESSION sort_buffer_size = 4194304;（4M），避免全局设置浪费内存
• 用 EXPLAIN 观察 Extra 是否含 Using temporary；对 GROUP BY 字段加索引，常能消除临时表

SELECT    VARIABLE_NAME,    VARIABLE_VALUE  FROM performance_schema.global_status  WHERE VARIABLE_NAME IN ('Innodb_buffer_pool_read_requests',    'Innodb_buffer_pool_reads',    'Created_tmp_disk_tables',    'Sort_merge_passes');

真正卡住 I/O 的往往不是单一配置，而是缓冲池 + 查询路径 + 刷盘策略三者叠加失效。比如即使 innodb_buffer_pool_size 足够，一个没走索引的 GROUP BY 仍会生成磁盘临时表，再叠加 sync_binlog = 1，I/O 请求就成倍放大。调优时得盯着 perf top -p $(pgrep mysqld) 看系统调用 热点，而不是只改参数。