Mysql使用中的性能优化——单次插入和批量插入的性能差异

一般Mysql的客户端和服务端不在一台机器上，所以它们之间的通信需要通过网络进行。我们本次实验，希望抛开网络的影响，测试不同SQL方案在Mysql服务器上的执行效率的对比。于是我们使用“存储过程”来辅助测试。

结论

先上结论：
批量插入速度约是单次插入速度的100倍。

实验数据

单条、批量插入10000条耗时对比

上图表达了，在插入10000条数据时：

• 单条逐条插入方案耗时35.839秒；
• 一次性批量插入方案耗时0.325秒。

单条插入不同条数时耗时对比

可以看出来这个变化基本是线性的。

批量插入不同条数时耗时对比

可以看到变化呈指数型，即随着批量中每次插入的数据量增加，耗时呈指数关系变化。这意味着在一次批量操作中，插入太多条目并不是最优解。后面我们会在《Mysql使用中的性能优化——批量插入的规模对比》中看到相关数据。

测试环境

见《Mysql使用中的性能优化——搭建Mysql的监测服务》

测试脚本

DROP DATABASE IF EXISTS testdb;
CREATE DATABASE IF NOT EXISTS testdb;
USE testdb;

DROP TABLE IF EXISTS test_insert;
CREATE TABLE test_insert (
    id SERIAL PRIMARY KEY,
    name TEXT
) engine=InnoDB;

DROP PROCEDURE IF EXISTS test_insert_proc_batch;
DELIMITER //
CREATE PROCEDURE test_insert_proc_batch(IN name TEXT, IN count INT)
BEGIN
    DECLARE i INT DEFAULT 0;
		DECLARE new_name TEXT DEFAULT '';
    WHILE i < count DO
        SET new_name = CONCAT(name, i);
        INSERT INTO test_insert (name) VALUES (new_name);
        SET i = i + 1;
    END WHILE;
    COMMIT;
END //
DELIMITER ;

DROP PROCEDURE IF EXISTS test_insert_proc_batch_bulk;
DELIMITER //
CREATE PROCEDURE test_insert_proc_batch_bulk(IN name TEXT, IN count INT)
BEGIN
    DECLARE i INT DEFAULT 0;
    DECLARE new_names TEXT DEFAULT '';
    SET @sql = 'INSERT INTO test_insert (name) VALUES ';
    WHILE i < count DO
        SET new_names = CONCAT(name, i);
        SET i = i + 1;
        SET @sql = CONCAT(@sql, '("', new_names, '"),');
    END WHILE;
    SET @sql = LEFT(@sql, LENGTH(@sql) - 1);
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    COMMIT;
END //
DELIMITER ;

TRUNCATE TABLE test_insert;
CALL test_insert_proc_batch('test', 1000);
TRUNCATE TABLE test_insert;
CALL test_insert_proc_batch('test', 2000);
TRUNCATE TABLE test_insert;
CALL test_insert_proc_batch('test', 3000);
TRUNCATE TABLE test_insert;
CALL test_insert_proc_batch('test', 4000);
TRUNCATE TABLE test_insert;
CALL test_insert_proc_batch('test', 5000);
TRUNCATE TABLE test_insert;
CALL test_insert_proc_batch('test', 6000);
TRUNCATE TABLE test_insert;
CALL test_insert_proc_batch('test', 7000);
TRUNCATE TABLE test_insert;
CALL test_insert_proc_batch('test', 8000);
TRUNCATE TABLE test_insert;
CALL test_insert_proc_batch('test', 9000);
TRUNCATE TABLE test_insert;
CALL test_insert_proc_batch('test', 10000);


TRUNCATE TABLE test_insert;
CALL test_insert_proc_batch_bulk('test', 10000);
TRUNCATE TABLE test_insert;
CALL test_insert_proc_batch_bulk('test', 20000);
TRUNCATE TABLE test_insert;
CALL test_insert_proc_batch_bulk('test', 30000);
TRUNCATE TABLE test_insert;
CALL test_insert_proc_batch_bulk('test', 40000);
TRUNCATE TABLE test_insert;
CALL test_insert_proc_batch_bulk('test', 50000);
TRUNCATE TABLE test_insert;
CALL test_insert_proc_batch_bulk('test', 60000);
TRUNCATE TABLE test_insert;
CALL test_insert_proc_batch_bulk('test', 70000);
TRUNCATE TABLE test_insert;
CALL test_insert_proc_batch_bulk('test', 80000);
TRUNCATE TABLE test_insert;
CALL test_insert_proc_batch_bulk('test', 90000);
TRUNCATE TABLE test_insert;
CALL test_insert_proc_batch_bulk('test', 100000);

测试结果原始数据