MYSQL性能测试

MYSQL插入

set global innodb_flush_log_at_trx_commit = 0;
set autocommit = 0;


drop procedure if exists test1;
DELIMITER //
create procedure test1(in sum int)
begin
    delete from person;
    commit;

    set @starttime = now(3);
    set @i = 0;
    while @i < sum do
        insert into person values('JW');
        set @i = @i + 1;
    end while;
    commit;

    set @endtime = now(3);
    select timediff(@endtime, @starttime);
end;
//
DELIMITER ;

drop procedure if exists test2;
DELIMITER //
create procedure test2(in sum int)
begin
    delete from person;
    commit;

    set @starttime = now(3);
    set @i = 0;
    while @i < sum do
        insert into person values('JW');
        commit;
        set @i = @i + 1;
    end while;

    set @endtime = now(3);
    select timediff(@endtime, @starttime);
end;
//
DELIMITER ;


call test1(100000);
call test1(100000);
call test1(100000);
call test2(100000);
call test2(100000);
call test2(100000);

结果1：
00:00:15.267
00:00:21.011
00:00:35.000
00:00:40.232
00:00:56.392
00:00:55.403
结果2：
00:00:19.862
00:00:24.240
00:00:28.661
00:01:21.946
00:01:03.016
00:00:39.048

MYSQL查询：

select * from person limit 0, 100000;

结果1：
100000 row(s) returned
0.000 sec / 0.188 sec
结果2：
100000 row(s) returned
0.016 sec / 0.156 sec