mysql 批量建表_mysql 如何實作循環批量插入？

背景

前幾天在MySql上做分頁時，看到有博文說使用 limit 0,10 方式分頁會有丢資料問題，有人又說不會，于是想自己測試一下。測試時沒有資料，便安裝了一個MySql，建了張表，在建了個while循環批量插入10W條測試資料的時候，執行時間之長無法忍受，便查資料找批量插入優化方法，這裡做個筆記。

資料結構

尋思着分頁時标準列分主鍵列、索引列、普通列3種場景，是以，測試表需要包含這3種場景，建表文法如下：

drop table if exists `test`.`t_model`;

Create table `test`.`t_model`(
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '自增主鍵',
`uid` bigint COMMENT '業務主鍵',
`modelid` varchar(50) COMMENT '字元主鍵',
`modelname` varchar(50) COMMENT '名稱',
`desc` varchar(50) COMMENT '描述',
primary key (`id`),
UNIQUE index `uid_unique` (`uid`),
key `modelid_index` (`modelid`) USING BTREE
) ENGINE=InnoDB charset=utf8 collate=utf8_bin;
           

為了友善操作，插入操作使用存儲過程通過while循環插入有序資料，未驗證其他操作方式或循環方式的性能。

執行過程

1、使用最簡單的方式直接循環單條插入1W條，文法如下：

drop procedure if exists my_procedure;
delimiter //
create procedure my_procedure()
begin
DECLARE n int DEFAULT 1;
WHILE n < 10001 DO
insert into t_model (uid,modelid,modelname,`desc`) value (n,CONCAT('id20170831',n),CONCAT('name',n),'desc');
set n = n + 1;
END WHILE;
end
//
delimiter ;
           

插入1W條資料，執行時間大概在6m7s，按照這個速度，要插入1000W級資料，估計要跑幾天。

2、于是，構思加個事務送出，是否能加快點性能呢？測試每1000條就commit一下，文法如下：

delimiter //
create procedure u_head_and_low_pro()
begin
DECLARE n int DEFAULT 17541;
WHILE n < 10001 DO
insert into t_model (uid,modelid,modelname,`desc`) value (n,CONCAT('id20170831',n),CONCAT('name',n),'desc');
set n = n + 1;
if n % 1000 = 0
then
commit;
end if;
END WHILE;
end
//
delimiter ;
           

執行時間 6 min 16 sec，與不加commit執行差别不大，看來，這種方式做批量插入，性能是很低的。

3、使用存儲過程生成批量插入語句執行批量插入插入1W條，文法如下：

drop procedure IF EXISTS u_head_and_low_pro;
delimiter $$
create procedure u_head_and_low_pro()
begin
DECLARE n int DEFAULT 1;
set @exesql = 'insert into t_model (uid,modelid,modelname,`desc`) values ';
set @exedata = '';
WHILE n < 10001 DO
set @exedata = concat(@exedata,"(",n,",","'id20170831",n,"','","name",n,"','","desc'",")");
if n % 1000 = 0
then
set @exesql = concat(@exesql,@exedata,";");
prepare stmt from @exesql;
execute stmt;
DEALLOCATE prepare stmt;
commit;
set @exesql = 'insert into t_model (uid,modelid,modelname,`desc`) values ';
set @exedata = "";
else
set @exedata = concat(@exedata,',');
end if;
set n = n + 1;
END WHILE;
end;$$
delimiter ;
           

執行時間 3.308s。

總結

批量插入時，使用insert的values批量方式插入，執行速度大大提升。

精彩推薦

實戰MySQL多表關聯查詢技巧

常用的MySQL圖形化管理工具

MySQL的四種事務隔離級别，看這篇就會了！

MySQL常見面試題

原文連結：https://www.mscto.com/mysql/66163.html

如果感覺推送内容不錯，不妨右下角點個在看，感謝支援！