mysql autocommit对myisam,innodb的性能影响

前段时间把数据库的部分myisam表转变成了innodb了,感觉慢了好多。我知道autocommit对innodb性能有一定的影响，但不知道影响有这么大。如何关闭autocommit，请参考mysql禁用autocommit,以及遇到的问题，为了解决这个问题，我做了一些测试，包括autocommit对myisam,innodb影响。

 

一，测试autocommit对myisam的影响

1,准备测试表和数据

查看 复制 打印 ?
  1. mysql>  CREATE TABLE `test_test` (     //测试表   
  2.  ->   `id` int(11) NOT NULL auto_increment,   
  3.  ->   `num` int(11) NOT NULL default '0',   
  4.  ->    PRIMARY KEY  (`id`)   
  5.  ->  ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;   
  6. Query OK, 0 rows affected (0.00 sec)   
  7.   
  8. mysql> delimiter ||   
  9. mysql> create procedure p_test(pa int(11))   
  10.  ->  begin   
  11.  ->   
  12.  ->   declare max_num int(11) default 100000;   
  13.  ->   declare i int default 0;   
  14.  ->   declare rand_num int;   
  15.  ->   
  16.  ->   select count(id) into max_num from test_test;   
  17.  ->   
  18.  ->   while i < pa do  
  19.  ->           if max_num < 100000 then   
  20.  ->                   select cast(rand()*100 as unsigned) into rand_num;   
  21.  ->                   insert into test_test(num)values(rand_num);   
  22.  ->           end if;   
  23.  ->           set i = i +1;   
  24.  ->   end while;   
  25.  ->  end||   
  26. Query OK, 0 rows affected (0.03 sec  
mysql>  CREATE TABLE `test_test` (     //测试表
 ->   `id` int(11) NOT NULL auto_increment,
 ->   `num` int(11) NOT NULL default '0',
 ->    PRIMARY KEY  (`id`)
 ->  ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ||
mysql> create procedure p_test(pa int(11))
 ->  begin
 ->
 ->   declare max_num int(11) default 100000;
 ->   declare i int default 0;
 ->   declare rand_num int;
 ->
 ->   select count(id) into max_num from test_test;
 ->
 ->   while i < pa do
 ->           if max_num < 100000 then
 ->                   select cast(rand()*100 as unsigned) into rand_num;
 ->                   insert into test_test(num)values(rand_num);
 ->           end if;
 ->           set i = i +1;
 ->   end while;
 ->  end||
Query OK, 0 rows affected (0.03 sec

2,测试autocommit开启的情况

查看 复制 打印 ?
  1. mysql> call p_test(100000)||        //插入10000条数据   
  2. Query OK, 1 row affected (0.86 sec)   
  3.   
  4. mysql> truncate table test_test;     //清空表   
  5. Query OK, 0 rows affected (0.00 sec)   
  6.   
  7. mysql> optimize table test_test;    //优化一下表，收回资源，确保测试的公平性  
mysql> call p_test(100000)||        //插入10000条数据
Query OK, 1 row affected (0.86 sec)

mysql> truncate table test_test;     //清空表
Query OK, 0 rows affected (0.00 sec)

mysql> optimize table test_test;    //优化一下表，收回资源，确保测试的公平性

这样我连续做了三次测试，平均一下插入10000的数据差不多要0.86秒。关于optimize来优化表，请参考optimize table在优化mysql时很重要

3,autocommit关闭的情况下

查看 复制 打印 ?
  1. mysql> call p_test(100000)||        //插入10000条数据   
  2. Query OK, 1 row affected (0.83 sec)   
  3.   
  4. mysql> commit;   
  5. Query OK, 0 rows affected (0.00 sec)   
  6.   
  7. mysql> truncate table test_test;     //清空表   
  8. Query OK, 0 rows affected (0.00 sec)   
  9.   
  10. mysql> commit;   
  11. Query OK, 0 rows affected (0.00 sec)   
  12.   
  13. mysql> optimize table test_test;    //优化一下表，收回资源，确保测试的公平性  
mysql> call p_test(100000)||        //插入10000条数据
Query OK, 1 row affected (0.83 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> truncate table test_test;     //清空表
Query OK, 0 rows affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> optimize table test_test;    //优化一下表，收回资源，确保测试的公平性

这样我连续做了三次测试，平均一下插入10000的数据差不多要0.83秒。为了使init_connect='SET autocommit=0'启作用，我是换了个用户测试的。如果在执行储存过程的时候遇到这样的问题，

ERROR 1370 (42000): execute command denied to user 'mysql'@'localhost' for routine 'test.p_test'

解决办法是：grant execute on procedure p_test to 'mysql'@localhost;

由上面的测试数据我们可以看出，autocommit对myisam没有多大的影响。

二，测试autocommit对innodb的影响

1,测试autocommit开启的情况

查看 复制 打印 ?
  1. mysql> alter table test_test type=innodb;          //将表改为innodb   
  2. Query OK, 0 rows affected, 1 warning (0.02 sec)   
  3. Records: 0  Duplicates: 0  Warnings: 1   
  4.   
  5. mysql> call p_test(10000);                       //插入数据   
  6. Query OK, 1 row affected (16.32 sec)   
  7.   
  8. mysql> truncate table test_test;                //删除数据   
  9. Query OK, 0 rows affected (0.02 sec)  
mysql> alter table test_test type=innodb;          //将表改为innodb
Query OK, 0 rows affected, 1 warning (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> call p_test(10000);                       //插入数据
Query OK, 1 row affected (16.32 sec)

mysql> truncate table test_test;                //删除数据
Query OK, 0 rows affected (0.02 sec)

我也做了3次测试，都是在16点几秒。myisam插入10000条数据，都不到一秒，而innodb要十几秒，差了20多倍，太杯具了。

2,测试autocommit关闭的情况

查看 复制 打印 ?
  1. mysql> call p_test(10000);                       //插入数据   
  2. Query OK, 1 row affected (0.61 sec)   
  3.   
  4. mysql> commit;                                  //提交   
  5. Query OK, 0 rows affected (0.02 sec)   
  6.   
  7. mysql> truncate table test_test;                //删除数据   
  8. Query OK, 0 rows affected (0.00 sec)   
  9.   
  10. mysql> commit;                                  //提交   
  11. Query OK, 0 rows affected (0.00 sec)  
mysql> call p_test(10000);                       //插入数据
Query OK, 1 row affected (0.61 sec)

mysql> commit;                                  //提交
Query OK, 0 rows affected (0.02 sec)

mysql> truncate table test_test;                //删除数据
Query OK, 0 rows affected (0.00 sec)

mysql> commit;                                  //提交
Query OK, 0 rows affected (0.00 sec)

我也测试了3次，第一次测试的时候，我以为我只插入了1000条，不然怎么会差距这么大呢。我又测试了二次，测试用时差不多，都是在0.6秒左右。autocommit对innodb的影响太大了，差了快30倍。我汗

所以我的建议是把mysql的autocommit自动提交功能关闭，这样可以提高mysql的性能，特别是innodb表比较多的情况下，不是提高一点点。如果关闭了autocommit，不要忘了commit。不然mysql服务器挂掉了，或者重起了，数据就丢失了。