MySQL 插入性能調優：從每分鐘 2000 條到每秒 100 條的實戰紀錄

MySQL 插入性能調優：從每分鐘 2000 條到每秒 100 條的實戰紀錄

環境基線

• OS：CentOS 6.5
• MySQL：5.5.40
• 硬件：4 核 CPU、4 GB 內存
• 目標：寫入吞吐從 2,000 行/分 提升至 6,000 行/分（100 行/秒）

優化策略雙管齊下

1. 主機 I/O 層優化
2. MySQL 關鍵參數調整

一、主機 I/O 優化

• 獨立表空間 + 多盤分離：開啟 innodb_file_per_table=ON，將不同表的 .ibd 文件掛載到不同物理磁盤/RAID 卷，分散隨機 I/O 壓力。
• 清理干擾進程：關閉非必要的定時任務、日誌收集、備份掃描等佔用 I/O 的後台進程。

二、my.cnv 核心參數調整（逐步增大，觀察穩定性）

參數調整順序建議

1. 先調大 innodb_log_file_size（需停機：關閉實例 → 刪除舊 ib_logfile* → 啟動）。
2. 動態調整 innodb_log_buffer_size、innodb_autoextend_increment。
3. 最後修改 innodb_flush_log_at_trx_commit=0（動態生效），並確認業務可接受「極端崩潰丟失 1 秒數據」。

驗證結果

調整後，寫入穩定在 100+ 行/秒，CPU 佔用率從 80%+ 降至 30% 左右，I/O 等待時間明顯下降。

風險提示與補救

• innodb_flush_log_at_trx_commit=0 會導致 實例崩潰時丟失最近 1 秒提交的事務。
• 若業務要求嚴格 ACID（如金融核心），請保持 =1，改用批量提交、異步寫入隊列、升級 SSD/RAID 卡等手段提升吞吐。
• 定期 SHOW ENGINE INNODB STATUS 觀察 Log sequence number 推進情況，確保後台刷盤跟得上。

一句話總結： 在可接受「秒級數據風險」的業務場景下，innodb_flush_log_at_trx_commit=0 配合大 Redo Log 是提升 MySQL 寫入吞吐的「核武器級」參數組合。