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

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

结论

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

实验数据

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

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

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

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

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

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

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

测试环境

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

测试脚本

DROPDATABASEIFEXISTS testdb;CREATEDATABASEIFNOTEXISTS testdb;USE testdb;DROPTABLEIFEXISTS test_insert;CREATETABLE test_insert (
    id SERIALPRIMARYKEY,
    name TEXT)engine=InnoDB;DROPPROCEDUREIFEXISTS test_insert_proc_batch;DELIMITER//CREATEPROCEDURE test_insert_proc_batch(IN name TEXT,IN count INT)BEGINDECLARE i INTDEFAULT0;DECLARE new_name TEXTDEFAULT'';WHILE i < count DOSET new_name = CONCAT(name, i);INSERTINTO test_insert (name)VALUES(new_name);SET i = i +1;ENDWHILE;COMMIT;END//DELIMITER;DROPPROCEDUREIFEXISTS test_insert_proc_batch_bulk;DELIMITER//CREATEPROCEDURE test_insert_proc_batch_bulk(IN name TEXT,IN count INT)BEGINDECLARE i INTDEFAULT0;DECLARE new_names TEXTDEFAULT'';SET@sql='INSERT INTO test_insert (name) VALUES ';WHILE i < count DOSET new_names = CONCAT(name, i);SET i = i +1;SET@sql= CONCAT(@sql,'("', new_names,'"),');ENDWHILE;SET@sql=LEFT(@sql, LENGTH(@sql)-1);PREPARE stmt FROM@sql;EXECUTE stmt;DEALLOCATEPREPARE stmt;COMMIT;END//DELIMITER;TRUNCATETABLE test_insert;CALL test_insert_proc_batch('test',1000);TRUNCATETABLE test_insert;CALL test_insert_proc_batch('test',2000);TRUNCATETABLE test_insert;CALL test_insert_proc_batch('test',3000);TRUNCATETABLE test_insert;CALL test_insert_proc_batch('test',4000);TRUNCATETABLE test_insert;CALL test_insert_proc_batch('test',5000);TRUNCATETABLE test_insert;CALL test_insert_proc_batch('test',6000);TRUNCATETABLE test_insert;CALL test_insert_proc_batch('test',7000);TRUNCATETABLE test_insert;CALL test_insert_proc_batch('test',8000);TRUNCATETABLE test_insert;CALL test_insert_proc_batch('test',9000);TRUNCATETABLE test_insert;CALL test_insert_proc_batch('test',10000);TRUNCATETABLE test_insert;CALL test_insert_proc_batch_bulk('test',10000);TRUNCATETABLE test_insert;CALL test_insert_proc_batch_bulk('test',20000);TRUNCATETABLE test_insert;CALL test_insert_proc_batch_bulk('test',30000);TRUNCATETABLE test_insert;CALL test_insert_proc_batch_bulk('test',40000);TRUNCATETABLE test_insert;CALL test_insert_proc_batch_bulk('test',50000);TRUNCATETABLE test_insert;CALL test_insert_proc_batch_bulk('test',60000);TRUNCATETABLE test_insert;CALL test_insert_proc_batch_bulk('test',70000);TRUNCATETABLE test_insert;CALL test_insert_proc_batch_bulk('test',80000);TRUNCATETABLE test_insert;CALL test_insert_proc_batch_bulk('test',90000);TRUNCATETABLE test_insert;CALL test_insert_proc_batch_bulk('test',100000);

测试结果原始数据