mysql并发写入性能低怎么办_mysql写入优化思路

INSERT 慢主因是锁和事务机制：未走索引、间隙锁、事务未提交导致锁等待；批量插入应多值合并、禁用自动提交、调优 innodb_flush_log_at_trx_commit=2 等参数。

为什么 INSERT 会慢？先看锁和事务在干什么

并发写入卡顿，80% 不是磁盘或 CPU 瓶颈，而是 INSERT 被隐式锁住：InnoDB 默认走行级锁，但若没走索引、或插入间隙（gap lock）、或事务未及时提交，就会触发锁等待甚至死锁。尤其批量插入时，每条 INSERT 单独提交，等于反复加锁 / 刷日志 / 刷脏页。

• 用 SHOW ENGINE INNODB STATUSG 查 TRANSACTIONS 段，看是否有大量 LOCK WAIT 或 waiting for table metadata lock
• 检查插入语句是否命中主键或唯一索引——没索引的 INSERT …… SELECT 或 REPLACE INTO 容易退化为表级扫描 + 锁
• 确认事务隔离级别：REPEATABLE READ 下 gap lock 更激进，高并发插入相同范围值时极易冲突；可临时试 READ COMMITTED（需业务能接受幻读）

批量写入必须用 INSERT INTO …… VALUES (……), (……), (……)

单条 INSERT 和多值批量插入的性能差 5–20 倍，核心在于减少网络往返、日志刷盘次数和锁获取频次。但要注意分批大小——太大触发内存溢出或锁升级，太小失去批量意义。

• 单次批量控制在 1000 行以内（innodb_log_file_size 和 max_allowed_packet 也限制上限）
• 避免拼接超长 SQL：Python 用 executemany()，Java 用 addBatch()，让驱动自动拆包
• 禁用自动提交：SET autocommit = 0，所有批量插入完成后 COMMIT —— 但注意事务不能太久，否则阻塞 MVCC 清理

LOAD DATA INFILE 是最快路径，但权限和路径常被忽略

比批量 INSERT 快 5–10 倍，本质是服务端直接读文件、跳过 SQL 解析层。但它对环境要求苛刻，不是“一写就快”。

• 必须确保 MySQL 配置开启：secure_file_priv 不为空（查 SELECT @@secure_file_priv），且你的 CSV 文件放在该目录下
• 客户端执行 LOAD DATA LOCAL INFILE 需显式启用（MySQL 8.0+ 默认关闭），连接时加参数 --local-infile=1，并确认服务端 local_infile=ON
• 字段分隔符、NULL 处理要严格匹配：FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'，否则整批失败

写入瓶颈真在磁盘？先调这几个关键参数

即使 SQL 和事务优化到位，innodb_flush_log_at_trx_commit、sync_binlog、innodb_buffer_pool_size 这三个参数不调，SSD 也跑不满。

• innodb_flush_log_at_trx_commit = 2：日志刷到 OS 缓存而非磁盘，崩溃可能丢 1 秒数据，但写入吞吐翻倍（线上可接受，金融 类除外）
• sync_binlog = 0 或 1000：避免每次事务都强制刷 binlog，异步刷更稳；若用 GTID 或主从强一致，至少设为 1000
• innodb_buffer_pool_size 至少占物理内存 70%，否则频繁刷脏页 + 磁盘 IO 成瓶颈

SET GLOBAL innodb_flush_log_at_trx_commit = 2; SET GLOBAL sync_binlog = 1000; -- 注意：buffer_pool_size 需重启生效，不可动态改

真正卡住的地方，往往不是你怎么写 SQL，而是你没意识到 MySQL 正在等磁盘落盘、等 binlog 同步、等 buffer pool 挤出旧页——这些开关不开，再好的语句也白搭。