mysql> show create procedure pro_addusers\G
*************************** 1. row ***************************
Procedure: pro_addusers
sql_mode:
Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `pro_addusers`(userconut int)
begin
set @i = 0;
repeat set @i = @i + 1;
insert into users values (@var:=replace(uuid(),'-',''),left(@var,10),@var,'abcdefghijglmn');
until @i = userconut end repeat;
end
1 row in set (0.00 sec)
mysql> show create procedure pro_addusers\G
*************************** 1. row ***************************
Procedure: pro_addusers
sql_mode:
Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `pro_addusers`(userconut int)
begin
set autocommit=0;
set @i = 0;
repeat set @i = @i + 1;
insert into users values (@var:=replace(uuid(),'-',''),left(@var,10),@var,'abcdefghijglmn');
if @i%1000=0 then commit;
end if;
until @i = userconut end repeat;
end
1 row in set (0.00 sec)
上面两个存储过程,向同一个INNODB表插入同样的数据量,时间差两个数量级。
插入10万条记录,前者10分钟,后者1秒钟。
所以对INNODB表批量处理数据的时候,最好不要用自动提交。
*************************** 1. row ***************************
Procedure: pro_addusers
sql_mode:
Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `pro_addusers`(userconut int)
begin
set @i = 0;
repeat set @i = @i + 1;
insert into users values (@var:=replace(uuid(),'-',''),left(@var,10),@var,'abcdefghijglmn');
until @i = userconut end repeat;
end
1 row in set (0.00 sec)
mysql> show create procedure pro_addusers\G
*************************** 1. row ***************************
Procedure: pro_addusers
sql_mode:
Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `pro_addusers`(userconut int)
begin
set autocommit=0;
set @i = 0;
repeat set @i = @i + 1;
insert into users values (@var:=replace(uuid(),'-',''),left(@var,10),@var,'abcdefghijglmn');
if @i%1000=0 then commit;
end if;
until @i = userconut end repeat;
end
1 row in set (0.00 sec)
上面两个存储过程,向同一个INNODB表插入同样的数据量,时间差两个数量级。
插入10万条记录,前者10分钟,后者1秒钟。
所以对INNODB表批量处理数据的时候,最好不要用自动提交。
转载于:https://blog.51cto.com/yueliangdao0608/81614