MySQL存储过程批量插入过慢求助：压力测试场景下插入效率优化

嘿，我完全懂你现在的头疼——用单条INSERT循环插数据，100行就要5秒，那要插2亿多行简直不敢想！咱们来把这个效率提上去，几个关键优化点就能帮你解决问题：

1. 改用批量插入（最核心的优化） #

每次只插一行的话，MySQL要处理大量的事务提交、日志写入和网络交互（如果是远程连接的话），开销极大。改成一次插几百甚至几千行，能把效率提升几十上百倍。

比如把你的存储过程改成批量插入的版本，每次攒1000行再提交：

DELIMITER //
CREATE PROCEDURE myproc()
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE batch_size INT DEFAULT 1000; -- 可以根据服务器性能调整，比如2000、5000
    DECLARE total_rows INT DEFAULT 237692004;
    
    -- 关闭自动提交，避免每次插入都触发事务提交
    SET autocommit = 0;
    -- 禁用外键检查（如果你的表有外键约束的话）
    SET FOREIGN_KEY_CHECKS = 0;
    -- 如果表有索引，建议先删除，插完数据再重建（索引会大幅拖慢插入速度）
    -- DROP INDEX idx_field ON ok;

    WHILE i <= total_rows DO
        -- 构建批量插入的SQL语句
        SET @insert_sql = 'INSERT INTO ok (field) VALUES ';
        -- 循环拼接当前批次的所有值
        WHILE i <= total_rows AND i < (i + batch_size) DO
            -- 这里替换成你需要的随机数据，比如生成随机字符串用UUID()，随机数用RAND()
            SET @insert_sql = CONCAT(@insert_sql, '(UUID()),');
            SET i = i + 1;
        END WHILE;
        -- 去掉最后一个多余的逗号
        SET @insert_sql = LEFT(@insert_sql, LENGTH(@insert_sql) - 1);
        -- 执行批量插入
        PREPARE stmt FROM @insert_sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        -- 每批次提交一次事务，避免事务过大占用资源
        COMMIT;
    END WHILE;

    -- 恢复外键检查和自动提交
    SET FOREIGN_KEY_CHECKS = 1;
    SET autocommit = 1;
    -- 重建之前删除的索引
    -- CREATE INDEX idx_field ON ok (field);
END //
DELIMITER ;

2. 调整MySQL配置参数（针对InnoDB） #

如果用的是InnoDB引擎（现在大部分都是），调整几个核心参数能显著提升写入性能：

• innodb_buffer_pool_size：尽量设大，比如服务器内存的50%-70%，让更多数据在内存中处理，减少磁盘IO。
• innodb_log_file_size：增大日志文件大小，比如设为1G-2G，减少日志切换的频率。
• innodb_flush_log_at_trx_commit：如果不需要严格的ACID一致性，设为2，这样日志每秒刷新一次，而不是每次事务都刷新，能大幅提升写入速度。
• innodb_write_io_threads：增加写IO线程数，比如设为8，提升并行写入能力。

3. 其他小技巧 #

• 避免触发器：如果表上有触发器，插入的时候会额外执行逻辑，先暂时禁用触发器，插完再恢复。
• 使用LOAD DATA INFILE：如果能生成CSV格式的随机数据文件，用LOAD DATA INFILE插入的速度会比INSERT快得多，这是MySQL最快的批量导入方式。
• 分表插入：如果数据量实在太大（比如2亿行），可以考虑把数据分到多个表中，比如按范围分表，并行插入，最后再合并或者直接用分表做测试。

这些优化组合起来，插入速度至少能提升几十倍，甚至上百倍，你可以根据自己的服务器情况调整参数和批次大小~

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