Rumah >pangkalan data >tutorial mysql >详解MySQL批量SQL插入的性能优化
mysql教程栏目介绍批量SQL插入
推荐(免费):mysql教程
对于一些数据量较大的系统,数据库面临的问题除了查询效率低下,还有就是数据入库时间长。特别像报表系统,每天花费在数据导入上的时间可能会长达几个小时或十几个小时之久。因此,优化数据库插入性能是很有意义的。
一条SQL语句插入多条数据
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) VALUES ('0', 'userid_0', 'content_0', 0);INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) VALUES ('1', 'userid_1', 'content_1', 1);
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) VALUES ('0', 'userid_0', 'content_0', 0), ('1', 'userid_1', 'content_1', 1);
测试对比数据,分别是单条数据的导入与转换成一条SQL语句进行导入。
在事务中进行插入处理
START TRANSACTION;INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) VALUES ('0', 'userid_0', 'content_0', 0);INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) VALUES ('1', 'userid_1', 'content_1', 1);...COMMIT;
测试对比数据,分笔试不适用事务和使用事务操作
数据有序插入
数据有序的插入是插入记录在主键上的有序排序
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) VALUES ('1', 'userid_1', 'content_1', 1);INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) VALUES ('0', 'userid_0', 'content_0', 0);INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) VALUES ('2', 'userid_2', 'content_2',2);
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) VALUES ('0', 'userid_0', 'content_0', 0);INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) VALUES ('1', 'userid_1', 'content_1', 1);INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) VALUES ('2', 'userid_2', 'content_2',2);
参照InnoDB使用的B+tree索引,如果每次插入记录都在索引的最后面,索引的定位效率很高,并且对索引调整较少;如果插入的记录在索引中间,需要B+tree进行分裂合并等处理,会消耗比较多计算资源,并且插入记录的索引定位效率会下降,数据量较大时会有频繁的磁盘操作。
测试对比数据,随机数据与顺序数据的性能对比
先删除索引,插入完成后重建索引
性能综合测试
注意事项
SQL语句是有长度限制,在进行数据合并在同一SQL中务必不能超过SQL长度限制,通过max_allowed_packet
配置可以修改,默认1M
,测试时可以修改为8M
。
事务需要控制大小,事物太大可能影响执行的效率。MySQL有innodb_log_buffer_size
配置项,超过这个值会把innodb的数据刷到磁盘中,这时,效率会有所下降。所以较好的做法是,在数据达到这个值前执行事务提交。
Atas ialah kandungan terperinci 详解MySQL批量SQL插入的性能优化. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!