使用存储过程向数据库批量插入数据

最新推荐文章于 2024-04-19 15:44:00 发布

weixin_33850890

最新推荐文章于 2024-04-19 15:44:00 发布

阅读量389

点赞数

文章标签：数据库 python

原文链接：https://my.oschina.net/pingjiangyetan/blog/519726

版权

2019独角兽企业重金招聘Python工程师标准>>>

<一> 前言

最近有个需求，需要向数据库并发批量插入数据。

最开始使用存储过程，类似这样的：

 delimiter // 
 CREATE PROCEDURE load_part_tab()
 begin
     declare v int default 0;
     while v < 8000000
     do
         insert into part_tab
         values (v,'testing partitions',adddate('1995-01-01',(rand(v)*36520) mod 3652));
          set v = v + 1;
     end while;
 end
 //
 delimiter ;

这是单条多次插入。时间消耗完全不能忍，2万条记录，耗时近半个小时。

后改为使用存储过程，批量插入，10个并发，每个插入2万条记录，共耗时119s。

查阅了网上的一些资料，先整理如下。我自己的代码，因为公司业务问题就不公布。

<二>过程

2.1 建立数据库表：

CREATE TABLE song ( 
          id int(11) NOT NULL AUTO_INCREMENT COMMENT 'Autoincreament element', 
          name text NOT NULL, 
          datetime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
          rank int(11) NOT NULL, 
          PRIMARY KEY (id) 
) ENGINE=MyISAM AUTO_INCREMENT=8102001 DEFAULT CHARSET=gbk

2.2 创建处理的存储过程：

DELIMITER //
DROP PROCEDURE IF EXISTS sp_insert_batch;
CREATE  PROCEDURE sp_insert_batch(IN number int(11))
BEGIN
  declare i int(11);
  set i = 1;
  WHILE i <= number DO
    if mod(i,2000)=1 then
       set @sqltext =concat('(''',concat('t',i),''',''',now(),''',',ceil(10*rand()),')');
    elseif mod(i,2000)=0 then
       set @sqltext=concat(@sqltext,',(''',concat('t',i),''',''',now(),''',',ceil(10*rand()),')');
       set @sqltext=concat('insert into song (name,datetime,rank) values',@sqltext);
       prepare stmt from @sqltext;
       execute stmt;
       DEALLOCATE PREPARE stmt;
       set @sqltext='';
    else 
       set @sqltext=concat(@sqltext,',(''',concat('t',i),''',''',now(),''',',ceil(10*rand()),')');
    end if;
    set i = i + 1;
  END WHILE;
  if @sqltext<>'' then
     set @sqltext=concat('insert into song (name,datetime,rank) values',@sqltext);
     prepare stmt from @sqltext;
     execute stmt;
     DEALLOCATE PREPARE stmt;
     set @sqltext='';
  end if;
END //
DELIMITER ;

解释一下：2000 条一个批次插入，通过拼接外面的values，然后使用 insert into values (),(),... 这样批量插入。

3. 使用 mysqlslap 测试如：

mysqlslap -uroot -p --concurrency=10,20,30,40,50,60 --engine=innodb --create-schema='scmtg_dev' --query='call sp_insert_batch(2000);'

4.测试结果：

每个客户端插入2万条记录，10个、20个、30个、40个(当然这个测试量是极小的)，走势如下图：

<三> 分析

批量插入的效率明显是要远高于单条多次插入，这个毋庸置疑。单条多次插入，一个客户端2万条需要近半个小时；而批量插入 10个客户端，每个2万条，才120s。
存储过程，sql 的拼接还是比较麻烦的。要多测试，避免错误。

转载于:https://my.oschina.net/pingjiangyetan/blog/519726