MySQL批量插入优化

布满荆棘的人生 2022-02-13 04:09 367阅读 0赞

前面介绍了MySQL批量插入可以通过存储过程的方式来实现，这里介绍批量插入100W记录，并做一个优化。

建表语句：create\_table.sql

drop table if exists xx_user;
    create table xx_user(
    id int primary key auto_increment,
    name varchar(20),
    age int);

直接插入：proc.sql

delimiter //
    drop procedure if exists add_user;
    create procedure add_user()
    begin
    declare i int default 0;
    set i=0;
    while i<=1000000 do 
      set i=i+1;
      insert into xx_user(name,age) values (concat('user-',i),20);
    end while;
    end;
    //
    delimiter ;

默认情况下，直接调用生成的存储过程，批量插入100w条记录，耗时如下：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZlaW5pZmk_size_16_color_FFFFFF_t_70][]

这种循环单条记录插入的方式建议不要直接操作，非常耗时。

多values插入：batch.sql

drop procedure if exists batch_insert;
    delimiter //
    create procedure batch_insert()
    begin
        declare i int;
        set i = 0;
        set @sqlstr='insert into xx_user(name,age) values ';
        while i<=1000000 do
          set i=i+1;
          set @sqlstr=concat(@sqlstr,'(concat(''user-'',',i,'),18)');
          if mod(i,5000)=0 then
             prepare stmt from @sqlstr;
             execute stmt;
             deallocate prepare stmt;
             set @sqlstr='insert into xx_user(name,age) values ';
          else
             set @sqlstr=concat(@sqlstr,',');
          end if;
       end while;
    end;
    //
    delimiter ;

这个是利用了多个values批量插入的办法，速度明显要高于第一种循环单条记录插入的办法，如下图所示，时间缩短到接近90秒：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZlaW5pZmk_size_16_color_FFFFFF_t_70 1][]

优化参数：

set global bulk\_insert\_buffer\_size=104857600;  
set session autocommit=off;  
set session unique\_checks=off;

当使用以上优化之后，第一个循环插入单条记录的办法，速度提升的惊人，效果如下，时间接近60秒：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZlaW5pZmk_size_16_color_FFFFFF_t_70 2][]

当我们利用优化后的环境进行多values批量插入测试，发现速度并没有明显的改变。如下所示：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZlaW5pZmk_size_16_color_FFFFFF_t_70 3][]

这个速度接近于90秒，和不做参数修改时差不多。

以上两个插入对比，如果不修改参数的情况下，多values批量插入的办法比普通循环插入的效率要高很多，如果更改了环境变量参数，那么普通循环插入单条记录的效率提升非常快，甚至超过了多values批量插入的效率。

一般而言，如果修改了autocommit，unique\_checks为off，那么需要在批量插入之后，将变量值修改回来。比如：

set session autocommit=on;  
set session unique\_checks=on;

根据很多建议修改bulk\_insert\_buffer\_size大小，默认是8m即8388608，修改为100m，即104857600，无论是循环插入单条记录，还是多values插入，均没有明显的提升效率，所以这里并没有特别的介绍。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZlaW5pZmk_size_16_color_FFFFFF_t_70]: /images/20220213/bcfc1e7a91e544dab92fe93e9ad29228.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZlaW5pZmk_size_16_color_FFFFFF_t_70 1]: /images/20220213/58a00541559c43fab14fd91f2d9fcd04.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZlaW5pZmk_size_16_color_FFFFFF_t_70 2]: /images/20220213/41fd15a97f9944a5af7c5b6a0da7e379.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZlaW5pZmk_size_16_color_FFFFFF_t_70 3]: /images/20220213/70f86645b8d24891a2ad6e825652d262.png