批量处理场景下向MySQL高效插入/更新数据的方法咨询

针对你每小时处理数千行数据的场景，逐行插入/更新确实效率低下，以下是几种更高效的方案，按易用性和效率排序：

1. 原生LOAD DATA INFILE（首选） #

这是MySQL性能最高的批量导入方式，直接读取本地或服务器文件，绕过SQL解析层，比单条INSERT快几十倍。支持直接处理更新逻辑（通过ON DUPLICATE KEY UPDATE）。

示例代码（CSV文件导入）：

-- 关闭自动提交减少事务开销
SET AUTOCOMMIT = 0;

LOAD DATA LOCAL INFILE '/path/to/cleaned_data.csv'
INTO TABLE target_table
FIELDS TERMINATED BY ','  -- 按你的文件分隔符调整
ENCLOSED BY '"'           -- 如果字段有引号包裹
LINES TERMINATED BY '\n'
IGNORE 1 LINES            -- 跳过表头（如果有）
(col1, col2, col3)        -- 映射到表字段
ON DUPLICATE KEY UPDATE   -- 处理重复主键的更新逻辑
col2 = VALUES(col2),
col3 = VALUES(col3);

COMMIT;

注意事项：

• 需要确保MySQL客户端开启LOCAL INFILE权限（连接时加--local-infile=1参数）
• 服务器端文件需放在MySQL有权限读取的路径，或用LOCAL指定本地文件

2. 批量INSERT语句 #

将多行数据合并为单条INSERT语句，减少网络往返和SQL解析次数，适合无法直接用LOAD DATA的场景（比如数据需要在内存中再处理）。

示例代码：

SET AUTOCOMMIT = 0;

INSERT INTO target_table (col1, col2, col3)
VALUES 
(1, 'val2_1', 'val3_1'),
(2, 'val2_2', 'val3_2'),
...
(1000, 'val2_1000', 'val3_1000')
ON DUPLICATE KEY UPDATE
col2 = VALUES(col2),
col3 = VALUES(col3);

COMMIT;

注意事项：

• 控制单条语句的行数（建议1000-5000行），避免超过max_allowed_packet限制
• 同样关闭自动提交提升效率

3. 临时表中转优化 #

如果目标表有大量索引或约束，直接导入会触发频繁索引更新，可先导入临时表，再同步到正式表：

-- 创建临时表（结构与正式表一致）
CREATE TEMPORARY TABLE temp_target LIKE target_table;

-- 导入数据到临时表
LOAD DATA LOCAL INFILE '/path/to/data.csv' INTO temp_target ...;

-- 同步到正式表，处理更新
INSERT INTO target_table SELECT * FROM temp_target
ON DUPLICATE KEY UPDATE
col2 = temp_target.col2,
col3 = temp_target.col3;

DROP TEMPORARY TABLE temp_target;

临时表无持久化索引，导入速度更快，还能避免正式表的锁竞争。

4. MySQL Shell并行导入工具 #

如果数据量更大或涉及多表，用MySQL Shell的util.importTable()工具，支持多线程并行导入，效率更高：

示例命令（在mysqlsh中执行）：

util.importTable('/path/to/data.csv', {
  schema: 'your_database',
  table: 'target_table',
  delimiter: ',',
  ignoreLines: 1,
  onDuplicate: 'update',  // 处理重复数据
  threads: 4              // 并行线程数，根据服务器配置调整
});

通用优化技巧 #

• 禁用非主键索引：导入前执行ALTER TABLE target_table DISABLE KEYS;，导入后执行ENABLE KEYS;，避免逐行更新索引
• 调整InnoDB参数：确保innodb_buffer_pool_size足够大（建议设为服务器内存的50%-70%），减少磁盘IO
• 关闭唯一性检查：导入前执行SET UNIQUE_CHECKS = 0;，导入后恢复，避免每次插入检查唯一约束

内容的提问来源于stack exchange，提问作者data_matata