开发者社区> 航空母舰> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

mysql autocommit对myisam,innodb的性能影响

简介:
+关注继续查看

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

 

一,测试autocommit对myisam的影响

1,准备测试表和数据

Java代码  收藏代码
  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(11default 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  

 2,测试autocommit开启的情况

Java代码  收藏代码
  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;    //优化一下表,收回资源,确保测试的公平性  

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

3,autocommit关闭的情况下

Java代码  收藏代码
  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;    //优化一下表,收回资源,确保测试的公平性   

这样我连续做了三次测试,平均一下插入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开启的情况

Java代码  收藏代码
  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)  

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

2,测试autocommit关闭的情况

Java代码  收藏代码
  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)  

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

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

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
手把手教你Prometheus + Granafa实现mysql 性能监测部署
数据库性能监控可以说是十分重要,能否自行搭建环境实现像阿里云或是腾讯云那样直观的展示不同维度数据的功能?答案是肯定的。下面详细说明一下安装部署过程以及过程中出现的问题,希望对你有所帮助!
0 0
Intel PAUSE指令变化如何影响MySQL的性能
x86、arm指令都很多,无论是应用程序员还是数据库内核研发大多时候都不需要对这些指令深入理解,但是 Pause 指令和数据库操作太紧密了,本文通过一次非常有趣的性能优化来引入对 Pause 指令的理解,期望可以事半功倍地搞清楚 CPU指令集是如何影响你的程序的。
0 0
ELK搭建(六):搭建mysql性能、执行效率监控平台
mysql作为市场的主流数据库,承载了大部分公司的核心业务数据,同时也是大多数业务的底层存储。 针对mysql运行情况的监控必不可少,之前我们讲解了如何搭建mysql慢日志、错误日志的监控平台。 那么本期,我们针对mysql集群、性能、各类sql语句执行情况、服务状态等指标来搭建一个可视化的监控平台,方便我们实时了解mysql资源利用率、sql执行效率、访问压力等等。
0 0
【黄啊码】MySQL入门—8、想要自己的SQL性能更上一层楼吗?MySQL视图了解一下
【黄啊码】MySQL入门—8、想要自己的SQL性能更上一层楼吗?MySQL视图了解一下
0 0
软件测试mysql面试题:使用索引查询一定能提高查询的性能吗?为什么
软件测试mysql面试题:使用索引查询一定能提高查询的性能吗?为什么
0 0
软件测试mysql面试题:简单描述mysql中,索引,主键,唯一索引,联合索引的区别,对数据库的性能有什么影响?
软件测试mysql面试题:简单描述mysql中,索引,主键,唯一索引,联合索引的区别,对数据库的性能有什么影响?
0 0
使用MySqL替代Freeswitch默认的SQLite数据库 以提高性能
使用MySqL替代Freeswitch默认的SQLite数据库 以提高性能
0 0
MySQL统计信息不准导致的性能问题
统计信息不准导致错误的执行计划,引发性能问题
0 0
+关注
文章
问答
文章排行榜
最热
最新
相关电子书
更多
阿里云 AnalyticDB MySQL版性能白皮书
立即下载
好的 MySQL 兼容可以做到什么程度
立即下载
云数据库RDS MySQL从入门到高阶
立即下载