MySQL 基于导入大表更新2.5亿行业务表过慢及锁表报错咨询

可行优化方案 #

核心问题定位

你遇到的ERROR 1206报错本质是InnoDB一次性处理2亿+行更新时，超大事务产生的行锁占满了缓冲池分配的锁表空间；临时表加主键导入慢是因为逐行插入时需要实时维护B+树索引，随机IO开销极高。 #

分步优化操作

1. 预处理CSV文件

先在操作系统层对CSV去重，避免后续数据库处理冗余数据，效率远高于数据库内去重：

sort -u path.csv > path_unique.csv

2. 优化临时表导入流程

不要先建索引再导数据，改为导入完成后批量构建索引，批量建索引是顺序IO，开销远低于逐行维护索引：

DROP TEMPORARY TABLE IF EXISTS tmp_import;
-- 内存足够的前提下用Memory引擎，临时表读写速度会大幅提升，内存不足用默认InnoDB也可
CREATE TEMPORARY TABLE tmp_import (name VARCHAR(255)) ENGINE=MEMORY;
-- 导入去重后的CSV，耗时和你之前不加索引导入一致，约9分钟
LOAD DATA LOCAL INFILE 'path_unique.csv' INTO TABLE tmp_import LINES TERMINATED BY '\n';
-- 导入完成后批量建主键索引，预计10分钟内即可完成
ALTER TABLE tmp_import ADD PRIMARY KEY (name);

3. 分批执行更新+插入，避免大事务

不要一次性关联全量数据执行操作，按批次处理、每批提交释放锁，彻底解决锁表报错问题，同时执行效率会大幅提升。这里推荐直接用INSERT ... ON DUPLICATE KEY UPDATE语法一步完成更新+插入逻辑，无需拆分两步操作：

-- 提前计算时间戳，避免每次循环重复计算
SET @now = UNIX_TIMESTAMP(NOW());
-- 定义批次大小，可根据服务器性能调整，1000-10000均可，性能够可以调到20000
SET @batch_size = 5000;
-- 取第一个批次的起始name
SELECT MIN(name) INTO @current_name FROM tmp_import;

WHILE @current_name IS NOT NULL DO
    -- 处理当前批次数据，存在则更新timestamp1，不存在则插入
    INSERT INTO og_table (name, timestamp1, timestamp2)
    SELECT name, @now, @now FROM tmp_import
    WHERE name >= @current_name
    ORDER BY name
    LIMIT @batch_size
    ON DUPLICATE KEY UPDATE timestamp1 = @now;
    
    -- 提交当前批次，释放锁资源
    COMMIT;
    
    -- 计算下一个批次的起始name
    SELECT MIN(name) INTO @current_name 
    FROM tmp_import 
    WHERE name > @current_name;
END WHILE;

可选辅助优化

• 如果不需要同步到从库，可以临时关闭binlog，或者调整参数innodb_flush_log_at_trx_commit=0、sync_binlog=0，操作完成后再改回原配置，可进一步提升写入速度
• 适当调大innodb_buffer_pool_size，给InnoDB更多缓存空间，能加速索引匹配和写入操作

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