mysql并发写多读少怎么处理_mysql性能调优建议

写多读少场景下innodb_buffer_pool_size应设为物理内存60%~70%，过小导致频繁I/O和等待，过大引发OOM；需结合热数据窗口、索引及undo空间综合估算。

写多读少场景下 innodb_buffer_pool_size 设置不合理会放大锁竞争

mysql 在写多读少时，大量 INSERT、UPDATE 会频繁刷脏页、触发 log_file_size 切换、加剧 buffer pool LRU 链表争用。如果 innodb_buffer_pool_size 过小（比如仅占物理内存 30%），会导致频繁磁盘 I/O 和 Buffer pool wait free 等待；过大（如 >80%）又可能引发系统 OOM 或 swap。建议按「写入吞吐量 × 平均行大小 × 2~3 倍热数据窗口」估算，例如每秒写入 5000 行、平均 200 字节，则热数据约 3MB/s，保留 10 分钟窗口即需 ≥1.8GB，再叠加索引和 undo 空间，设为物理内存的 60%~70% 更稳妥。

高并发 INSERT 用 INSERT … ON DUPLICATE KEY UPDATE 要小心唯一索引锁范围

在写多场景中，用 INSERT ... ON DUPLICATE KEY UPDATE 替代先 select 再 INSERT/UPDATE 可减少一次网络往返，但若表有非主键的 UNIQUE 索引，InnoDB 会对冲突值所在间隙加 next-key lock，极易造成锁等待甚至死锁。特别是当业务用时间戳或递增 ID 做唯一索引时，多个事务集中插入相邻值，会锁住大片间隙。

• 确认唯一约束是否真有必要：能用主键代替就不用额外 UNIQUE 索引
• 改用 INSERT IGNORE + 应用层重试，避免锁升级
• 对高频写入字段（如 create_time）不建 UNIQUE 索引
• 监控 Innodb_row_lock_waits 和 Innodb_row_lock_time_avg，突增说明锁问题已暴露

binlog_format = ROW + sync_binlog = 1 是写性能瓶颈关键开关

写多场景下，sync_binlog = 1 保证崩溃安全，但每次事务都刷盘，极大拖慢 INSERT 吞吐；而 binlog_format = ROW 虽然复制精确，但大事务会产生巨量 binlog 日志，进一步加重 I/O 压力。两者叠加常是 show processlist 中大量 Writing to binlog 状态的根源。

• 若允许主从秒级延迟，可设 sync_binlog = 100（每 100 个事务刷一次）
• 确认业务没用到 STATEMENT 特有函数（如 NOW()、UUID()），否则不能随意切回 STATEMENT
• 对批量导入类操作，临时设 SET session binlog_format = STATEMENT，避免生成冗余 ROW 日志
• 检查 max_binlog_size 是否过小（默认 1GB），频繁切换也会增加开销

不要忽略 auto_increment_lock_mode=2 的实际效果

MySQL 5.6+ 默认 auto_increment_lock_mode = 1（consecutive），但在高并发 INSERT 下仍会对 auto_inc 锁加表级互斥，成为瓶颈。设为 2（interleaved）后，InnoDB 不再预分配连续 ID，而是按需分配，彻底消除该锁。虽然 ID 不再严格递增（中间可能跳号），但对绝大多数写多业务完全可接受。

SET GLOBAL auto_increment_lock_mode = 2;

注意：此参数必须在实例启动前配置进 my.cnf 才能持久生效；运行时设置仅对后续连接有效，且要求表引擎为 InnoDB、主键为 BIGINT 或 INT 类型。

真正卡住写入的往往不是 SQL 写法本身，而是这些看似“安全默认”的配置在写多压力下集体失守。调参不是堆内存或关日志，而是理解每个开关在 WAL、锁、缓存三者间的实际权衡点。