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

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

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

关于一些数据量较大的系统，数据库面临的问题除了查询效力低下，还有就是数据入库时间长。特殊像报表系统，每天花费在数据导入上的时间大概会长达几个小时或十几个小时之久。因此，优化数据库插入机能是很成心义的。

引荐：《mysql教程》

经过对MySQL InnoDB的一些机能测试，发明一些可以提高insert效力的办法，供大家参照参照。

1、一条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语句停止导入，离别测试1百、1千、1万条数据记载。

2、在事务中停止插入处置。

把插入修改成：

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内部会创立一个事务，在事务内才停止真正插入处置操纵。通过使用事务可以减少创立事务的耗损，所有插入都在施行后才停止提交操纵。

这里也供给了测试对照，离别是不使用事务与使用事务在记载数为1百、1千、1万的状况。

3、数据有序插入。

数据有序的插入是指插入记载在主键上是有序摆列，例如datetime是记载的主键：

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

下面供给随机数据与次序数据的机能对照，离别是记载为1百、1千、1万、10万、100万。

从测试结果来看，该优化办法的机能有所提高，但是提高并不是很明显。

4、机能综合测试

这里供给了同时使用上面三种办法停止INSERT效力优化的测试。

从测试结果可以看到，合并数据+事务的办法在较小数据量时，机能提高是很明显的，数据量较大时（1千万以上），机能会急剧下落，这是由于此时数据量超越了innodb_buffer的容量，每次定位索引触及较多的磁盘读写操纵，机能下落较快。而使用合并数据+事务+有序数据的方式在数据量到达千万级以上展现照旧是良好，在数据量较大时，有序数据索引定位较为利便，不需要频繁对磁盘停止读写操纵，所以可以保持较高的机能。

留意事项：

1、SQL语句是有长度限制，在停止数据合并在统一SQL中务必不克不及超越SQL长度限制，通过max_allowed_packet配置可以修改，默许是1M，测试时修改为8M。

2、事务需要操纵大小，事务太大大概会影响施行的效力。MySQL有innodb_log_buffer_size配置项，超越这个值会把innodb的数据刷到磁盘中，这时，效力会有所下落。所以比力好的做法是，在数据到达这个这个值前停止事务提交。

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

打赏