区别：
MySQL有多种存储引擎，MyISAM和InnoDB是其中常用的两种。这里介绍关于这两种引擎的一些基本概念(非深入介绍)。
MyISAM是MySQL的默认存储引擎(后面的版本不是了)，基于传统的ISAM类型，支持全文搜索，但不是事务安全的，而且不支持外键。每张MyISAM表存放在三个文件中：frm 文件存放表格定义；数据文件是MYD (MYData)；索引文件是MYI (MYIndex)。
InnoDB是事务型引擎，支持回滚、崩溃恢复能力、多版本并发控制、ACID事务，支持行级锁定(InnoDB表的行锁不是绝对的，如果在执行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表，如like操作时的SQL语句)，以及提供与Oracle类型一致的不加锁读取方式。InnoDB存储它的表和索引在一个表空间中，表空间可以包含数个文件。
主要区别：
MyISAM是非事务安全型的，而InnoDB是事务安全型的。MyISAM锁的粒度是表级，而InnoDB支持行级锁定。MyISAM支持全文类型索引，而InnoDB不支持全文索引。MyISAM相对简单，所以在效率上要优于InnoDB，小型应用可以考虑使用MyISAM。MyISAM表是保存成文件的形式，在跨平台的数据转移中使用MyISAM存储会省去不少的麻烦。InnoDB表比MyISAM表更安全，可以在保证数据不会丢失的情况下，切换非事务表到事务表(alter table tablename type=innodb)。
应用场景：
MyISAM管理非事务表。它提供高速存储和检索，以及全文搜索能力。如果应用中需要执行大量的SELECT查询，那么MyISAM是更好的选择。InnoDB用于事务处理应用程序，具有众多特性，包括ACID事务支持。如果应用中需要执行大量的INSERT或UPDATE操作，则应该使用InnoDB，这样可以提高多用户并发操作的性能。
新：https://blog.csdn.net/bandaoyu/article/details/83312179
常用命令：
(1)查看表的存储类型(三种)：
- show create table tablename
- show table status from dbname where name=tablename
- mysqlshow -u user -p password --status dbname tablename
(2)修改表的存储引擎：
- alter table tablename type=InnoDB
(3)启动mysql数据库的命令行中添加以下参数使新发布的表都默认使用事务：
- --default-table-type=InnoDB
(4)临时改变默认表类型：
- set table_type=InnoDB
- show variables like 'table_type'
性能：
Mysql数据库Innodb与MyISAM的性能对比测试
(原文：http://blog.csdn.net/ghosc/article/details/7230952)
由于近期有个项目对系统性能要求很高，技术选型上由于种种原因已经确定使用Mysql数据库，接下来就是要确定到底使用哪种存储引擎。我们的应用是典型的写多读少，写入内容为也很短，对系统的稳定性要求很高。所以存储引擎肯定就定在广泛使用的Innodb和MyISAM之中了。
至于两者的比较网上也有很多，但是毕竟这个事情也不复杂，决定还是自己来做，去验证一下在我们的场景下谁更优。
测试的版本是mysql Ver 14.14 Distrib 5.1.49, for debian-linux-gnu (i686)，使用的是Innodb plugin 1.0.8(官方称比built-in版本性能更好)和默认的MyISAM。
测试机器是我的笔记本，配置如下：Intel 酷睿2双核 P8600，2G*2 DDR3 1066内存，320G硬盘5400转。
测试一：数据插入性能测试，这里我分别对innodb_flush_log_at_trx_commit参数(innodb_flush_log_at_trx_commit对性能的影响见文章后附录1)打开和关闭都测了了一下，每次测试都是运行40s，表中数字都是实际插入条数。
MyISAM Innodb (打开) Innodb (关闭)
单线程，逐个插入 120000 60000 60000
4线程，逐个插入 40000*4 20000*4 40000*4
单线程，批量100条/次插入 3600*100 800*100 3000*100
单线程，批量200条/次插入 1800*200 400*200 1600*200
可以发现批量插入的性能远高于单条插入，但是一次批量的大小对性能影响不大。每条记录是否都刷新日志的参数对innodb性能的影响巨大。总体上来说，MyISAM性能更优一点。这里有一点需要注意，在插入测试过程中，我对系统资源进行了监控，发现MyISAM对系统资源占用很低，但是Innodb对磁盘占用却很高，应该是对事务控制多了很多需要记录的日志。
测试二：数据读取性能测试。每次随机读取1000条记录，反复进行读取。
MyISAM Innodb
单线程，200次读取 5.7s 16.7s
4线程，200次读取 12s 40.8s
可以看出MyISAM的读取性能非常恐怖，性能差距在3倍的样子。
以上两个测试发现MyISAM在无事务的需求下几乎完胜，但是要知道它是表锁，Innodb是行锁，那么在并发读写同时存在的情况下，那结果会是怎么样呢？！
测试三：两个线程并发写入，2个线程并发读取。
MyISAM Innodb
逐个插入 写入40s：10000*2 读取200次*2：14s 写入40s：60000*2 读取200次*2：50s
批量100条/次插入 写入40s：1000*100*2 读取200次*2：10s 写入40s：1500*100*2 读取200次*2：50s
这下立刻显示出Innodb在并发情况下强劲的性能，几乎没有什么性能衰减。而MyISAM单条插入速度变得非常慢，批量插入也下降了40%性能。
总结一下，在写多读少的应用中还是Innodb插入性能更稳定(行级锁)，在并发情况下也能基本，如果是对读取速度要求比较快的应用还是选MyISAM。
另外提一下，这里需要用到对Innodb的热备份，除了用Master-Slave的方式，还可以选用XtraBackup这个开源软件。
对于支持事务的InnoDB类型的标，影响速度的主要原因是AUTOCOMMIT默认设置是打开的，而且程序没有显式调用BEGIN 开始事务，导致每插入一条都自动Commit，严重影响了速度。可以在执行sql前调用begin，多条sql形成一个事物(即使autocommit打 开也可以)，将大大提高性能。http://www.ha97.com/4170.html
这么慢的速度实在等不下去了，于是把innodb_flush_log_at_trx_commit参数调整为2.哈，好家伙，CPU占用马上跳到了70%左右，果然变快了。而且我的数据一会儿就插入完成了。于是深刻地明白了，以前在innodb_flush_log_at_trx_commit为1的时候，大多数时间CPU都在等待日志的读写。
下面是该参数的三个可选值的说明。
innodb_flush_log_at_trx_commit (这个很管用)
抱怨Innodb比MyISAM慢 100倍？那么你大概是忘了调整这个值。默认值1的意思是每一次事务提交或事务外的指令都需要把日志写入(flush)硬盘，这是很费时的。特别是使用电池供电缓存(Battery backed up cache)时。设成2对于很多运用，特别是从MyISAM表转过来的是可以的，它的意思是不写入硬盘而是写入系统缓存。日志仍然会每秒flush到硬盘，所以你一般不会丢失超过1-2秒的更新。设成0会更快一点，但安全方面比较差，即使MySQL挂了也可能会丢失事务的数据。而值2只会在整个操作系统挂了时才可能丢数据。
附录2：
mysql autocommit对myisam,innodb的性能影响
前段时间把数据库的部分myisam表转变成了innodb了,感觉慢了好多。我知道autocommit对innodb性能有一定的影响，但不知道影响有这么大。如何关闭autocommit，请参考mysql禁用autocommit,以及遇到的问题，为了解决这个问题，我做了一些测试，包括autocommit对myisam,innodb影响。
一，测试autocommit对myisam的影响
1,准备测试表和数据
- 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
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 sec2,测试autocommit开启的情况
- 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; //优化一下表，收回资源，确保测试的公平性
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关闭的情况下
- 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; //优化一下表，收回资源，确保测试的公平性
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开启的情况
- 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)
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关闭的情况
- 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)
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服务器挂掉了，或者重起了，数据就丢失了。
转载请注明
作者:海底苍鹰
地址:http://blog.51yip.com/mysql/1231.html
扫一扫
164
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
