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

林译川

2026-09-10 • 👁 0人浏览过

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 压力最大。 php版微信js-sdk支付接口类 php版微信js-sdk支付接口类下载允许丢失最多 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)

看系统调用热点，而不是只改参数。

相关文章