MySQL批量插入200万行数据：分块插入的实现方案咨询

两种循环实现思路分析与优化 #

1. 循环生成单条批量INSERT语句（适合生成新数据） #

如果是要生成全新的测试数据或者导入外部数据，这种方式直接高效。核心逻辑是通过循环，每次构建包含10000行(value_list)的INSERT语句，直到完成200万行的插入目标。

比如用MySQL存储过程来实现的话：

DELIMITER //
CREATE PROCEDURE BatchInsertTestData()
BEGIN
    DECLARE total_target INT DEFAULT 2000000;
    DECLARE batch_size INT DEFAULT 10000;
    DECLARE inserted_count INT DEFAULT 0;
    
    WHILE inserted_count < total_target DO
        -- 替换成你实际的列和数据生成逻辑，比如随机值、自增序列等
        INSERT INTO your_target_table (col1, col2, create_time)
        SELECT 
            FLOOR(RAND() * 100000), 
            CONCAT('user_', UUID()),
            NOW()
        FROM INFORMATION_SCHEMA.TABLES 
        LIMIT batch_size; -- 借助系统表快速生成批量行
        
        SET inserted_count = inserted_count + batch_size;
        -- 可选：每10批提交一次事务，平衡性能和一致性
        -- IF MOD(inserted_count, batch_size*10) = 0 THEN COMMIT; END IF;
    END WHILE;
END //
DELIMITER ;

-- 执行存储过程
CALL BatchInsertTestData();

重要提醒：提前调整MySQL的max_allowed_packet参数（建议设为64M以上），确保单条批量INSERT语句的大小不会超出限制。

2. 从父表分批查询插入（适合数据迁移） #

如果数据来自现有父表，强烈不建议用LIMIT offset, batch_size的方式——当offset值很大时（比如到199万），MySQL会先扫描前面所有的offset行，性能会断崖式下降。更优的做法是基于**有序唯一键（比如主键ID）**来分页，每次只获取下一批数据：

DELIMITER //
CREATE PROCEDURE MigrateDataFromParent()
BEGIN
    DECLARE batch_size INT DEFAULT 10000;
    DECLARE last_processed_id INT DEFAULT 0;
    DECLARE current_batch_rows INT DEFAULT 1; -- 初始化大于0进入循环
    
    WHILE current_batch_rows > 0 DO
        INSERT INTO your_target_table (col1, col2, col3)
        SELECT p.col1, p.col2, p.col3
        FROM parent_table p
        WHERE p.id > last_processed_id -- 用主键过滤，避免扫描前置数据
        ORDER BY p.id
        LIMIT batch_size;
        
        -- 获取本次插入的行数，判断是否还有剩余数据
        SET current_batch_rows = ROW_COUNT();
        -- 更新last_processed_id为当前批次的最大ID
        SELECT MAX(id) INTO last_processed_id FROM your_target_table;
    END WHILE;
END //
DELIMITER ;

-- 执行存储过程
CALL MigrateDataFromParent();

这种方式的优势是每次查询都只扫描需要的批次数据，性能全程稳定，不会随着批次增加而变慢。

关键注意事项 #

• 事务控制：如果不需要强一致性，可以每插入几批提交一次事务，减少事务日志的压力；如果要求原子性，可以把整个循环放在一个事务里，但要注意事务过大可能导致锁表或日志溢出。
• 索引优化：插入期间可以临时关闭目标表的非必要索引，插入完成后再重建，能大幅提升插入速度。
• 资源监控：批量插入期间注意监控MySQL的CPU、内存和磁盘IO，避免资源耗尽影响其他业务。

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