mysql创建批量数据 mysql批量添加数据sql语句

转载

mob64ca13f446df 2024-03-11 06:47:43

文章标签 mysql创建批量数据 mysql sql 数据库有序插入 文章分类 MySQL 数据库

MySQL实战：批量SQL插入性能优化

1. 使用一条SQL语句插入多条记录
2. 在事务中进行插入处理
3. 数据有序插入
4. 性能综合测试结果

在一些数据量较大的系统中，经常会面临的问题就是查询效率低下，数据入库时间长，特别是报表系统，每天的数据导入花费的时间可能会长达几个小时。因此优化数据库插入性能就变得很重要了。

经过对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 ('2','USERID_2','CONTENT_2',2), ('3','USERID_3','CONTENT_3',3);

修改后的插入操作能够提高程序的插入效率。第二种SQL执行效率高的主要原因是合并后日志量（MySQL的binlog和innodb的事务日志）减少了，降低日志刷盘的数据量和频率，从而提高效率。通过合并SQL语句，同时也能减少SQL语句解析的次数，减少网络传输的IO。

2. 在事务中进行插入处理

把插入SQL修改成：

start transaction;
insert into `insert_table` (`datetime`, `uid`, `content`, `type`)
values ('4','USERID_4','CONTENT_4',4);
insert into `insert_table` (`datetime`, `uid`, `content`, `type`)
values ('6','USERID_6','CONTENT_6',6);
commit;

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

3. 数据有序插入

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

insert into `insert_table` (`datetime`, `uid`, `content`, `type`)
values ('6','USERID_6','CONTENT_6',1);
insert into `insert_table` (`datetime`, `uid`, `content`, `type`)
values ('5','USERID_5','CONTENT_5',0);
insert into `insert_table` (`datetime`, `uid`, `content`, `type`)
values ('7','USERID_7','CONTENT_7',1);

修改为：

insert into `insert_table` (`datetime`, `uid`, `content`, `type`)
values ('5','USERID_5','CONTENT_5',0);
insert into `insert_table` (`datetime`, `uid`, `content`, `type`)
values ('6','USERID_6','CONTENT_6',1);
insert into `insert_table` (`datetime`, `uid`, `content`, `type`)
values ('7','USERID_7','CONTENT_7',1);

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

4. 性能综合测试结果

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

注意