测试目的:验证总记录数下,每次提交多条记录,与一条记录提交一次,性能的差异。
测试步骤:
1、创建测试表:
create table tcommittable(
t_id int(10),
t_name varchar(20),
PRIMARY KEY(t_id)
);
2、分批次提交事务过程:
drop procedure if exists tcommit;
delimiter $$
create procedure tcommit()
begin
declare i int default 1;
select now();
while i < 10000 do
insert into tcommittable(t_id, t_name)
values (i, 'a'||i)
on duplicate key update t_name = 'b'||i;
if i % 3000 = 0 then
commit;
end if;
set i = i + 1;
end while;
commit;
select now();
end$$
delimiter ;
call tcommit;
3、一条条提交过程:
drop procedure if exists tcommit;
delimiter $$
create procedure tcommit()
begin
declare i int default 1;
select now();
while i < 10000 do
insert into tcommittable(t_id, t_name)
values (i, 'a'||i)
on duplicate key update t_name = 'b'||i;
commit;
set i = i + 1;
end while;
select now();
end$$
delimiter ;
call tcommit;
测试结果:
分批次:时间: 5.760s
每条记录提交:时间: 25.265s