mysql并发插入大量数据怎么处理_mysql批量写入技巧

应使用批量INSERT、显式事务、LOAD DATA INFILE及合理并发策略提升插入性能。单条INSERT开销大；批量每批≤1000行，字段顺序一致且NULL显式写出；关闭autocommit并分批COMMIT；LOAD DATA INFILE最快但需本地文件；并发时注意自增锁与主键冲突，优先用ON DUPLICATE KEY UPDATE或分布式ID。

用 INSERT INTO ... VALUES (...), (...), ... 代替单条 INSERT

单条 INSERT 每次都走完整事务流程，网络往返+日志刷盘开销大，吞吐极低。批量拼成一条语句可显著减少解析、权限校验、锁竞争次数。

• 每批控制在 1000 行以内（max_allowed_packet 限制下更稳妥）
• 避免跨事务拼太长——超长语句会卡住 binlog 写入，也可能触发 ER_NET_PACKET_TOO_LARGE
• 字段顺序必须严格一致，NULL 值要显式写 NULL，不能省略

INSERT INTO orders (user_id, amount, status, created_at) 
VALUES 
(123, 99.9, 'paid', '2025-05-20 10:00:00'),
(456, 129.5, 'pending', '2025-05-20 10:00:01'),
(789, 45.0, 'paid', '2025-05-20 10:00:02');

关闭自动提交 + 手动事务包裹多批插入

默认 autocommit=1 下每条 INSERT 都是独立事务，频繁刷 redo log 和 binlog。改成显式事务后，仅末尾一次 commit 触发刷盘。

• 执行前先 SET autocommit = 0，或用 BEGIN/START TRANSACTION
• 每 1000~5000 行 COMMIT 一次，避免事务过大导致锁表或回滚段膨胀
• 注意：长事务会阻塞 purge 线程，影响 MVCC 性能

BEGIN;
INSERT INTO logs (level, msg, ts) VALUES (...),(...),...;
INSERT INTO logs (level, msg, ts) VALUES (...),(...),...;
COMMIT;

用 LOAD DATA INFILE 替代 SQL 插入（本地文件场景）

这是 MySQL 原生最快的批量导入方式，绕过 SQL 解析层，直接读文件进 buffer pool，速度通常是批量 INSERT 的 5–20 倍。

• 文件必须在数据库服务器本地（或启用 local_infile=ON 并用 LOAD DATA LOCAL INFILE）
• 字段分隔符、行结束符需与文件严格匹配，否则静默跳过整行
• 无法触发触发器，也不走外键检查（除非显式开启 FOREIGN_KEY_CHECKS=1）

  

LOAD DATA INFILE '/var/lib/mysql-files/batch.csv'
INTO TABLE products
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(product_id, name, price, category);

并发插入时注意主键冲突和自增锁争用

多线程同时 INSERT 到同一张 InnoDB 表，若用 AUTO_INCREMENT 主键，默认的 auto_inc_lock_mode=1（interleaved）虽提升并发，但可能产生间隙不连续；而 mode=2（innodb_autoinc_lock_mode=2）在简单 INSERT 场景下无表级锁，但 INSERT ... SELECT 仍会锁表。

• 高并发写入建议用 REPLACE INTO 或 INSERT ... ON DUPLICATE KEY UPDATE 处理重复主键
• 避免用 SELECT MAX(id)+1 手动生成 ID，极易冲突且性能差
• 如果业务允许，考虑 UUID 或雪花 ID 脱离自增依赖，彻底消除争用点

真正卡住吞吐的往往不是磁盘 I/O，而是事务粒度、锁范围和网络包大小这些细节。尤其在云数据库上，max_allowed_packet 和 innodb_log_file_size 配置不对，再好的批量逻辑也会退化成单行插入。