mysql中InnoDB存储引擎的性能优化策略

innodb_buffer_pool_size是最关键调优参数，因其直接决定热数据内存驻留量，影响i/o性能；设太小导致频繁读盘，太大引发oom；建议设为物理内存50%–75%，并监控buffer pool hit rate是否持续低于99%。

为什么 innodb_buffer_pool_size 是最关键的调优参数

InnoDB 性能瓶颈绝大多数时候卡在磁盘 I/O，而 innodb_buffer_pool_size 决定了有多少热数据能常驻内存。设得太小，大量 select 和 UPDATE 都要反复读盘；设得太大，可能挤占 OS 缓存或触发系统 OOM。

• 生产环境建议设为物理内存的 50%–75%，但需预留至少 2GB 给 OS 和其他进程
• mysql 5.7+ 支持在线动态调整（SET GLOBAL innodb_buffer_pool_size = 4294967296），但仅限于 1GB 对齐的整数倍
• 监控是否命中不足：查 SHOW ENGINE INNODB STATUS 中的 Buffer pool hit rate，持续低于 99% 就该扩容

如何避免 autocommit=1 拖垮批量写入性能

默认开启的 autocommit 会让每条 INSERT/UPDATE 都触发一次 redo log 刷盘（fsync），批量导入时延迟爆炸。这不是“要不要用事务”的问题，而是“刷盘频率”问题。

• 大批量写入前显式关闭：SET autocommit = 0，然后手动 COMMIT，把多条语句合并成一次刷盘
• 配合 innodb_flush_log_at_trx_commit = 2（日志写入 OS cache 而非立即刷盘），吞吐可提升 3–5 倍，但断电有最多 1 秒事务丢失风险
• 注意：INSERT ... VALUES (...), (...), (...) 单语句多值插入，比循环单条 INSERT 快得多，且不受 autocommit 影响

innodb_file_per_table 开启后，碎片和空间回收才真正可控

关闭时所有表共用 ibdata1，即使 DROP TABLE，空间也回不给操作系统；开启后每张表独立 .ibd 文件，OPTIMIZE TABLE 或 ALTER TABLE ... ENGINE=InnoDB 才能真正释放磁盘空间。

• 新实例必须开启：SET GLOBAL innodb_file_per_table = ON（并写入配置文件）
• 老表迁移：执行 ALTER TABLE t1 ENGINE=InnoDB，会重建表并释放碎片，但期间锁表（8.0 可加 ALGORITHM=INPLACE 降低影响）
• 监控碎片率：SELECT DATA_FREE / (DATA_LENGTH + INDEX_LENGTH) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 't1'，超过 0.2 就值得优化

为什么 innodb_read_io_threads 和 innodb_write_io_threads 不宜盲目调高

这两个参数控制后台 I/O 线程数，默认各 4，看似加大能提升并发，但实际受制于磁盘队列深度、IOPS 上限和 CPU 上下文切换开销。SSD 上调到 8 可能有效，HDD 上设成 16 反而因争抢加剧延迟。

• 优先观察 SHOW ENGINE INNODB STATUS 中的 FILE I/O 段：若 pending normal aio reads/writes 长期 > 0，说明 I/O 线程确实吃紧
• linux 下检查磁盘压力：iostat -x 1，看 %util 是否持续 100%、await 是否飙升
• 调参后必须压测验证：用 sysbench --threads=32 oltp_insert 对比 QPS 和平均延迟，而不是只看理论值

SELECT    table_name,   round(((data_length + index_length) / 1024 / 1024), 2) AS size_mb,   round(data_free / 1024 / 1024, 2) AS free_mb FROM information_schema.tables  WHERE engine = 'InnoDB' AND data_free > 0 ORDER BY data_free DESC LIMIT 5;

大表的 data_free 明显偏高，就是 innodb_file_per_table + 定期 OPTIMIZE 的信号。别等磁盘告警才动手。