[验证]INNODB批处理的运用事务与否效率

时间：2009-07-16 来源：hb_li_520

Mysql Version: mysql> select version();
+----------------+
| version()      |
+----------------+
| 5.4.1-beta-log |
+----------------+
1 row in set (0.00 sec) 看到杨涛同学的这篇文章，试了一下。 DROP TABLE IF EXISTS `test`.`users`;
CREATE TABLE `test`.`users` (
`uuid_id` varchar(46) CHARACTER SET latin1 DEFAULT NULL,
`uuid_left` varchar(46) CHARACTER SET latin1 DEFAULT NULL,
`uuid_all` varchar(46) CHARACTER SET latin1 DEFAULT NULL,
`other` varchar(46) CHARACTER SET latin1 DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC; 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表批量处理数据的时候，最好不要用自动提交。 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 但测试结果第一个需要超长时间等待！ mysql> call pro_adduser(100000);
Query OK, 1 row affected (56 min 28.77 sec)
测试第二个需要测得的结果跟上面的差不多?(如果不关闭 set autocommit=off;) mysql> call pro_adduser(100000);
Query OK, 0 rows affected (20.75 sec) 这样才OK!