MySQL如何快速插入大量数据

一、背景

在学习索引优化或者大数据分页的时候，需要在一张表内插入百万级的数据甚至更高，手动一条条的插入肯定是不可能，既麻烦又耗时

一开时最容易想到的是通过存储过程，可以循环的帮我们插入

#1. 准备表
create table s1(
id int,
name varchar(20),
gender char(6),
email varchar(50)
);

#2. 创建存储过程，实现批量插入记录
delimiter $$ #声明存储过程的结束符号为$$
create procedure auto_insert1()
BEGIN
    declare i int default 1;
    while(i<3000000)do
        insert into s1 values(i,'bob','male',concat('bob',i,'@163.com'));
        set i=i+1;
    end while;
END$$ #$$结束
delimiter ; #重新声明分号为结束符号

#3. 查看存储过程
show create procedure auto_insert1\G 

#4. 调用存储过程
call auto_insert1();

这种方法虽然省事，但是效率很低，，，插入300万插到天昏地暗

二、解决方案

对于一些数据量较大的系统，数据库面临的问题除了查询效率低下，还有就是数据入库时间长。特别像报表系统，可能每天花费在数据导入上的时间就会长达几个小时之久。因此，优化数据库插入性能是很有意义的。

对于Myisam类型的表来说，貌似可以使用LOAD DATA的方式批量插入

对于InnoDB来说，如何优化insert语句的插入效率呢

1.一条sql语句插入多条记录

上面那种方法就是一条sql语句插入一条记录

insert into s1 values(1,'bob','male','bob@163.com');

现在改成一条sql，插入多条数据

insert into s1 values
    (1,'bob','male','bob@163.com')，
    (2,'tony','tony','bob@163.com')；

数据对比

分析

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

2.在事务中插入处理

START TRANSACTION;
insert into s1 values(1,'bob','male','bob@163.com');
insert into s1 values(1,'bob','male','bob@163.com');
COMMIT;

数据对比

分析

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

3.数据有序插入

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

#原始数据
insert into s1 values(2,'bob','male','bob@163.com');
insert into s1 values(1,'bob','male','bob@163.com');
insert into s1 values(3,'bob','male','bob@163.com');

修改后

insert into s1 values(1,'bob','male','bob@163.com');
insert into s1 values(2,'bob','male','bob@163.com');
insert into s1 values(3,'bob','male','bob@163.com');

数据对比

分析

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

同时使用上述三种方法

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

注意事项

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

方法1：

可以编辑my.cnf来修改（windows下my.ini）,在[mysqld]段或者mysql的server配置段进行修改。

max_allowed_packet = 20M

如果找不到my.cnf可以通过，寻找

mysql --help | grep my.cnf

方法2：

在mysql中输入

set global max_allowed_packet = 2*1024*1024*10

然后重启，查看

show VARIABLES like '%max_allowed_packet%';

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

所以我最后的插入数据语句改成了

create table s1(
id int primary key auto_increment,
name varchar(20),
gender char(6),
email varchar(50)
);

delimiter $$ #声明存储过程的结束符号为$$
create procedure auto_insert1()
BEGIN
    declare i int default 1;
    while(i<300000)do
    start transaction;
        insert into s1(name,gender,email) values 
        ('t1','male',concat('bob',i,'@163.com')),
        ('t2','male',concat('bob',i,'@163.com')),
        ('t3','male',concat('bob',i,'@163.com')),
        ('t4','male',concat('bob',i,'@163.com')),
        ('t5','male',concat('bob',i,'@163.com')),
        ('t6','male',concat('bob',i,'@163.com')),
        ('t7','male',concat('bob',i,'@163.com')),
        ('t8','male',concat('bob',i,'@163.com')),
        ('t9','male',concat('bob',i,'@163.com')),
        ('t10','male',concat('bob',i,'@163.com')),
        ('t11','male',concat('bob',i,'@163.com')),
        ('t12','male',concat('bob',i,'@163.com')),
        ('t13','male',concat('bob',i,'@163.com')),
        ('t14','male',concat('bob',i,'@163.com')),
        ('t15','male',concat('bob',i,'@163.com'));
        insert into s1(name,gender,email) values 
        ('t1','male',concat('bob',i,'@163.com')),
        ('t2','male',concat('bob',i,'@163.com')),
        ('t3','male',concat('bob',i,'@163.com')),
        ('t4','male',concat('bob',i,'@163.com')),
        ('t5','male',concat('bob',i,'@163.com')),
        ('t6','male',concat('bob',i,'@163.com')),
        ('t7','male',concat('bob',i,'@163.com')),
        ('t8','male',concat('bob',i,'@163.com')),
        ('t9','male',concat('bob',i,'@163.com')),
        ('t10','male',concat('bob',i,'@163.com')),
        ('t11','male',concat('bob',i,'@163.com')),
        ('t12','male',concat('bob',i,'@163.com')),
        ('t13','male',concat('bob',i,'@163.com')),
        ('t14','male',concat('bob',i,'@163.com')),
        ('t15','male',concat('bob',i,'@163.com'));
        insert into s1(name,gender,email) values 
        ('t1','male',concat('bob',i,'@163.com')),
        ('t2','male',concat('bob',i,'@163.com')),
        ('t3','male',concat('bob',i,'@163.com')),
        ('t4','male',concat('bob',i,'@163.com')),
        ('t5','male',concat('bob',i,'@163.com')),
        ('t6','male',concat('bob',i,'@163.com')),
        ('t7','male',concat('bob',i,'@163.com')),
        ('t8','male',concat('bob',i,'@163.com')),
        ('t9','male',concat('bob',i,'@163.com')),
        ('t10','male',concat('bob',i,'@163.com')),
        ('t11','male',concat('bob',i,'@163.com')),
        ('t12','male',concat('bob',i,'@163.com')),
        ('t13','male',concat('bob',i,'@163.com')),
        ('t14','male',concat('bob',i,'@163.com')),
        ('t15','male',concat('bob',i,'@163.com'));
        insert into s1(name,gender,email) values 
        ('t1','male',concat('bob',i,'@163.com')),
        ('t2','male',concat('bob',i,'@163.com')),
        ('t3','male',concat('bob',i,'@163.com')),
        ('t4','male',concat('bob',i,'@163.com')),
        ('t5','male',concat('bob',i,'@163.com')),
        ('t6','male',concat('bob',i,'@163.com')),
        ('t7','male',concat('bob',i,'@163.com')),
        ('t8','male',concat('bob',i,'@163.com')),
        ('t9','male',concat('bob',i,'@163.com')),
        ('t10','male',concat('bob',i,'@163.com')),
        ('t11','male',concat('bob',i,'@163.com')),
        ('t12','male',concat('bob',i,'@163.com')),
        ('t13','male',concat('bob',i,'@163.com')),
        ('t14','male',concat('bob',i,'@163.com')),
        ('t15','male',concat('bob',i,'@163.com'));
    commit;
      set i=i+1;
    end while;
END$$ 
delimiter ; #重新声明分号为结束符号

#调用
call auto_insert1();

#删除
DROP PROCEDURE auto_insert1