MySQL批量更新大量数据耗时过长，求高效解决方案

首先明确：LOAD DATA INFILE无法直接完成现有行的更新，它的核心作用是向表中导入新记录，要实现更新必须结合其他逻辑，但我们可以通过优化流程大幅提升效率。针对你2.5亿行主表+6000万行更新数据的场景，以下是可落地的高效方案：

一、先优化临时表的导入环节 #

临时表的结构和索引直接影响后续关联更新的速度，必须和主表字段严格匹配：

1. 创建临时表时，保证id字段类型与mydb.users.id完全一致，并且给id加主键索引（利用主键的快速查找特性）
2. 导入时关闭外键检查、自动提交，减少额外开销

示例代码：

-- 关闭外键检查和自动提交
SET FOREIGN_KEY_CHECKS = 0;
SET AUTOCOMMIT = 0;

-- 创建与主表字段类型匹配的临时表，id设为主键
CREATE TEMPORARY TABLE temp_data (
    id INT UNSIGNED PRIMARY KEY, -- 替换成你实际的id类型，比如BIGINT
    new_income DECIMAL(12,2) -- 匹配users.income的类型
) ENGINE=InnoDB;

-- 导入CSV文件，注意分隔符是';'，如果有表头加IGNORE 1 ROWS
LOAD DATA INFILE '/绝对路径/data.csv'
INTO TABLE temp_data
FIELDS TERMINATED BY ';'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS; -- 若CSV第一行是表头则启用

-- 恢复外键检查并提交
SET FOREIGN_KEY_CHECKS = 1;
COMMIT;

二、分批次更新，避免一次性锁表 #

一次性更新6000万行会导致InnoDB行锁范围过大、事务日志暴涨，分批次处理能显著降低锁竞争和IO压力，推荐两种方式：

方式1：按批次数量循环更新 #

每次固定更新N行（比如10万行），直到临时表中所有匹配行处理完成：

SET @batch_size = 100000; -- 可根据服务器配置调整，比如5万或20万

REPEAT
    UPDATE mydb.users u
    INNER JOIN temp_data t ON u.id = t.id
    SET u.income = t.new_income
    LIMIT @batch_size; -- 限制单次更新行数
    
    SET @updated_rows = ROW_COUNT(); -- 获取本次更新行数
UNTIL @updated_rows = 0 END REPEAT;

方式2：按id范围分段更新 #

利用主键id的有序性，分段处理不同区间的id，更利于InnoDB的缓存和锁管理：

SET @min_id = (SELECT MIN(id) FROM temp_data);
SET @max_id = (SELECT MAX(id) FROM temp_data);
SET @step = 100000; -- 每段处理10万行
SET @current_start = @min_id;

WHILE @current_start <= @max_id DO
    UPDATE mydb.users u
    INNER JOIN temp_data t ON u.id = t.id
    SET u.income = t.new_income
    WHERE u.id BETWEEN @current_start AND @current_start + @step - 1;
    
    COMMIT; -- 每批次提交，释放锁和日志空间
    SET @current_start = @current_start + @step;
END WHILE;

三、替代方案：用INSERT ... ON DUPLICATE KEY UPDATE #

因为id是主键，可通过插入临时表数据+主键冲突时更新的方式实现，InnoDB对INSERT操作的优化通常优于关联UPDATE，速度可能更快：

-- 同样建议分批次执行，避免一次性处理过多数据
SET @batch_size = 100000;
SET @offset = 0;

REPEAT
    INSERT INTO mydb.users (id, income)
    SELECT id, new_income FROM temp_data
    LIMIT @offset, @batch_size
    ON DUPLICATE KEY UPDATE income = VALUES(income);
    
    SET @inserted_rows = ROW_COUNT();
    SET @offset = @offset + @batch_size;
UNTIL @inserted_rows = 0 END REPEAT;

四、服务器参数临时优化（可选） #

如果服务器允许临时调整配置，可进一步提升速度：

• 临时关闭binlog：SET SQL_LOG_BIN = 0;（操作完成后记得改回1，若不需要备份则可忽略）
• 增大InnoDB缓冲池：SET GLOBAL innodb_buffer_pool_size = 21474836480;（比如设为20G，根据服务器内存调整，建议是内存的70%）
• 增大日志文件和缓冲：调整innodb_log_file_size、innodb_log_buffer_size，减少刷盘频率

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