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

本文主要是介绍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)
BEGINDECLARE i INT DEFAULT 0;DECLARE new_name TEXT DEFAULT '';WHILE i < count DOSET 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)
BEGINDECLARE i INT DEFAULT 0;DECLARE new_names TEXT DEFAULT '';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, '"),');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);