导语：
“同事的批量写入脚本跑 2 小时，我的代码只需 12 分钟！” 本文揭秘京东、美团都在用的 MySQL 写入优化方案，文末送【批量写入压测脚本】，关注+转发抽奖送《MySQL 内核优化手册》！

痛点共鸣

"INSERT 语句写满 3 屏，跑批任务等到天荒地老？"
先看触目惊心的数据对比（真实压测）：

优化前：10万条数据写入耗时 182 秒

优化后：同数据量仅需 9.7 秒

️核心优化方案（附代码）

1. 一箭三雕：事务+批处理+参数调优（80% 场景适用）

java

// 错误示范：逐条提交
for (User user : userList) {
    stmt.executeUpdate("INSERT INTO users VALUES (...)");
}

// 正确姿势：批处理+事务
Connection conn = dataSource.getConnection();
conn.setAutoCommit(false); // 关闭自动提交
PreparedStatement ps = conn.prepareStatement("INSERT ...");

for (int i=0; i<100000; i++) {
    ps.setString(1, "name"+i);
    ps.addBatch();
    
    if (i % 5000 == 0) { // 每5000条提交一次
        ps.executeBatch();
        conn.commit(); 
        ps.clearBatch();
    }
}

参数调优：

sql

innodb_buffer_pool_size = 80%内存  # 缓冲池
innodb_flush_log_at_trx_commit = 2 # 事务提交策略
bulk_insert_buffer_size = 256M     # 批量插入缓存

2. 核弹级提速：LOAD DATA INFILE（提速 10 倍+）

sql

-- 将CSV导入MySQL（秒级完成百万数据）
LOAD DATA INFILE '/tmp/users.csv' 
INTO TABLE users 
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '
';

性能对比：

方式	10万条耗时	100万条耗时
逐条INSERT	182s	1800s+
LOAD DATA	4.2s	38s

高阶技巧

3. 空间换时间：内存磁盘混合架构

sql

-- 使用内存临时表中转
CREATE TEMPORARY TABLE tmp_users (...) ENGINE=MEMORY;
-- 批量插入临时表
INSERT INTO tmp_users ...;
-- 一次性导入主表
INSERT INTO users SELECT * FROM tmp_users;

4. 并行写入黑科技（Python示例）

python

from concurrent.futures import ThreadPoolExecutor

def batch_insert(chunk):
    # 每个线程处理1万条数据
    with connection.cursor() as cursor:
        cursor.executemany(sql, chunk)

with ThreadPoolExecutor(max_workers=8) as executor:
    # 将10万数据拆分为10个chunk
    executor.map(batch_insert, [chunk1, chunk2,...])

避坑指南

• 索引陷阱：写入前禁用非必要索引，完成后重建
  ALTER TABLE users DISABLE KEYS; ... ENABLE KEYS;
• 日志深渊：关闭binlog（SET sql_log_bin=0）或使用ROW模式
• 连接池误区：HikariCP 配置不当引发雪崩（附配置模板）

实战压测报告

使用 sysbench 对比不同方案的 TPS（Transactions/sec）：

方案	TPS	资源消耗
逐条写入	62	CPU 8%
批量+事务	5400	CPU 31%
LOAD DATA	21000	CPU 68%