MySql插入大量数据解决方案

一、循环插入

循环插入是比较常见的方式,即通过sql语句一条一条向数据库添加数据。

1、示例

INSERT INTO table_name ( field1, field2,...fieldN ) VALUES( value1, value2,...valueN );

2、优点

  • 方便简单

  • 适用于单条或者少量数据插入的场景

3、缺点

  • 对于大量数据插入的场景,效率慢

  • 频繁的连接数据库,系统开销大,数据库服务压力较大

4、总结

循环插入不适用于大量数据插入的场景。

 

二、一条sql语句插入多条数据

对于大量数据插入的场景,可以将要插入的数据通过一条sql语句完成。

1、示例

INSERT INTO `table_name` (`id`, `name`)  VALUES ('2', 'xiaohua');

将上边两条插入语句合并成一条:

INSERT INTO `table_name` (`id`, `name`) VALUES ('1', 'xiaoming'), ('2', 'xiaohua');

2、优点

  • 减少SQL语句解析的次数

  • 减少数据库连接的I/O开销

  • 如果使用的是innodb数据库引擎,语句合并后,减少了MySql的binlog日志等,降低了日志刷盘的数据量和频率

3、测试结果 

MySql插入大量数据解决方案

4、总结

批量插入要比一条条插入效率高很多。但是如果批量插入的数据量太大,MySQL可能报错:

Packets larger than max_allowed_packet are not allowed 

这是由于MySQL数据库有一个系统参数max_allowed_packet,其默认值为1048576(1M),可以查看并修改max_allowed_packet的值来解决:

(1)查看max_allowed_packet     

show VARIABLES like '%max_allowed_packet%';

如果没有查到,如下图,说明没有设置,需要设置max_allowed_packet

MySql插入大量数据解决方案

(2)设置max_allowed_packet

  修改此参数的方法是在mysql文件夹找到my.ini文件,在my.ini文件[mysqld]中添加一行:max_allowed_packet=16777216 重启MySQL,这样将可以导入不大于16M的数据了,当然这数值可以根据需要作调整。

(3)修改max_allowed_packet

  如果已经设置了max_allowed_packet,修改的语句如下: set global max_allowed_packet = 1024*1024*1024 

 

三、在事务中进行插入处理

1、示例 

START TRANSACTION;
INSERT INTO `t_test1` (`content`) VALUES ('xiaoming');
INSERT INTO `t_test1` (`content`) VALUES ('xiaohua');
COMMIT;

2、测试结果

3、总结

使用事务可以提高数据的插入效率。因为进行一个INSERT操作时,MySQL内部会建立一个事务,在事务内才进行真正插入处理操作。通过使用事务可以减少创建事务的消耗,所有插入都在执行后才进行提交操作

 

四、数据有序插入

数据有序的插入是指插入记录在主键上是有序排列,例如:

INSERT INTO `insert_table` (`id`, `name`)  VALUES ('1', 'xiaoming');
INSERT INTO `insert_table` (`id`, `name`)  VALUES ('3', 'xiaoming');
INSERT INTO `insert_table` (`id`, `name`)  VALUES ('2', 'xiaoming');

修改成:

INSERT INTO `insert_table` (`id`, `name`)  VALUES ('1', 'xiaoming');
INSERT INTO `insert_table` (`id`, `name`)  VALUES ('2', 'xiaoming');
INSERT INTO `insert_table` (`id`, `name`)  VALUES ('3', 'xiaoming');

2、测试结果

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

3、总结

由于数据库插入时,需要维护索引数据,无序的记录会增大维护索引的成本。我们可以参照InnoDB使用的B+tree索引,如果每次插入记录都在索引的最后面,索引的定位效率很高,并且对索引调整较小;如果插入的记录在索引中间,需要B+tree进行分裂合并等处理,会消耗比较多计算资源,并且插入记录的索引定位效率会下降,数据量较大时会有频繁的磁盘操作。 

 

五、使用存储过程

1、建表

 
 
CREATE TABLE `t_test`( `id` BIGINT(20) not null AUTO_INCREMENT, `content` VARCHAR(255) DEFAULT null, PRIMARY KEY(`id`)) ENGINE=MYISAM DEFAULT CHARSET=utf8;

这里选择数据库引擎是MYISAM。

2、建立存储过程

因为要插入100万条数据,不可能通过一条条的insert去做,这里采用存储过程:

delimiter$$DROP PROCEDURE IF EXISTS proc_batch_insert;CREATE PROCEDURE proc_batch_insert()BEGINDECLARE i INT;SET i=1;WHILE i<1000000 DO INSERT INTO t_test(`content`)VALUES(SUBSTRING(MD5(RAND()) FROM 1 FOR 6));SET i=i+1;END WHILE;END$$delimiter;

调用存储过程:

CALL proc_batch_insert;

从下图可以看出插入100万条数据需要2284.025s,大约38分钟。

上边演示了插入100万条数据,使用的数据库引擎是MYISAM。

3、变更数据库引擎

删除上面插入的100万条数据:可参考删除大量数据。将MYISAM改为INNODB:(具体可参考如何查看并修改数据库引擎)

ALTER TABLE t_test ENGINE=INNODB;

重新调用插入100万数据的存储过程:

从上面看出插入大量数据时,采用MYISAM或者INNODB相差不大。 

 

六、性能综合测试

 

1、注意事项

  1. SQL语句是有长度限制,在进行数据合并在同一SQL中务必不能超过SQL长度限制,通过max_allowed_packet配置可以修改,默认是1M,测试时修改为8M。

  2. 事务需要控制大小,事务太大可能会影响执行的效率。MySQL有innodb_log_buffer_size配置项,超过这个值会把innodb的数据刷到磁盘中,这时,效率会有所下降。所以比较好的做法是,在数据达到这个这个值前进行事务提交。

2、总结

从测试结果可以看到,合并数据+事务的方法在较小数据量时,性能提高是很明显的,数据量较大时(1千万以上),性能会急剧下降,这是由于此时数据量超过了innodb_buffer的容量,每次定位索引涉及较多的磁盘读写操作,性能下降较快。而使用合并数据+事务+有序数据的方式在数据量达到千万级以上表现依旧是良好,在数据量较大时,有序数据索引定位较为方便,不需要频繁对磁盘进行读写操作,所以可以维持较高的性能。

更多技术请关注

MySql插入大量数据解决方案》来自互联网,仅为收藏学习,如侵权请联系删除。本文URL:http://www.hashtobe.com/477.html