详解MySQL大量SQL插入的机能优化-百分百源码网

详解MySQL大量SQL插入的机能优化

发布时间：01/01 来源：未知浏览：关键词：

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施行效力高的主要缘由是合并后日志量[mysql的binlog和InnoDB的事务让日志]减少了，落低日志刷盘的数据量和频率，从而提高效力。
通过合并SQL语句，同时也能减少SQL语句解析的次数，减少网络传输的IO。

测试对照数据，离别是单条数据的导入与转换成一条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操纵时，MySQL内部都会创立一个事务，在事务内才停止真正插入处置操纵。
通过使用事务减少创立事务的耗损，所有插入都在施行后才停止提交操纵

测试对照数据，分笔试不适用事务和使用事务操纵

数据有序插入

数据有序的插入是插入记载在主键上的有序排序

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停止分裂合并等处置，会耗损比力多运算资源，并且插入记载的索引定位效力会下落，数据量较大时会有频繁的磁盘操纵。

测试对照数据，随机数据与次序数据的机能对照

先删除索引，插入完成后重建索引

机能综合测试

合并数据+事务的办法在较少数据量时，机能晋升很明显，数据量较大时，机能急剧下落，这是由于此时数据量超越了innodb_buffer的容量，每次定位索引触及较多的磁盘读写操纵，机能下落较快。
合并数据+事务+有序的办法在数据量到达千万级以上展现仍然良好，在数据量较大时，有序数据索引定位较为利便，不需要频繁对磁盘停止读写操纵，可以保持较高

留意事项

SQL语句是有长度限制，在停止数据合并在统一SQL中务必不克不及超越SQL长度限制，通过max_allowed_packet配置可以修改，默许1M，测试时可以修改为8M。
事务需要操纵大小，事物太大大概影响施行的效力。MySQL有innodb_log_buffer_size配置项，超越这个值会把innodb的数据刷到磁盘中，这时，效力会有所下落。所以较好的做法是，在数据到达这个值前施行事务提交。

以上就是详解MySQL大量SQL插入的机能优化的具体内容，更多请关注百分百源码网其它相关文章！

打赏