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

来源：这里教程网时间：2026-02-28 20:39:45 作者：

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 的往往不是单一配置，而是缓冲池 + 查询路径 + 刷盘策略三者叠加失效。比如即使