一、简单介绍
1.MyISAM:默认表类型，它是基于传统的ISAM类型,ISAM是Indexed Sequential Access Method (有索引的顺序访问方法) 的缩写,它是存储记录和文件的标准方法.不是事务安全的，而且不支持外键，如果执行大量的select，insert MyISAM比较适合。
2.InnoDB:支持事务安全的引擎，支持外键、行锁、事务是他的最大特点。Innodb最初是由innobase Oy公司开发，2006年5月由oracle公司并购，目前innodb采用双授权，一个是GPL授权，一个是商业授权。如果有大量的update和insert，建议使用InnoDB,特别是针对多个并发和QPS较高的情况。
二、表锁差异
MyISAM:
myisam只支持表级锁，他的兼容性如下
| 请求模式
是否兼容
当前锁模式 | 其他用户(读锁) | 其他用户（写锁） | 当前用户 |
| 读锁 | YES | NO | YES |
| 写锁 | NO | NO | YES |
用户在操作myisam表时，select，update，delete，insert语句都会给表自动加锁，如果加锁以后的表满足insert并发的情况下，可以在表的尾部插入新的数据。也可以通过lock table命令来锁表，这样操作主要是可以模仿事务，但是消耗非常大，一般只在实验演示中使用。
InnoDB：
Innodb支持事务和行级锁，是innodb的最大特色。
事务的ACID属性：atomicity,consistent,isolation,durable。
并发事务带来的几个问题：更新丢失，脏读，不可重复读，幻读。
事务隔离级别：未提交读(Read uncommitted)，已提交读(Read committed)，可重复读(Repeatable read)，可序列化(Serializable)
四种隔离级别的比较
| 读数据一致性及并发副作用
隔离级别 | 读数据一致性 | 脏读 | 不可重复读 | 幻读 |
| 为提交读(read uncommitted) | 最低级别，不读物理上顺坏的数据 | 是 | 是 | 是 |
| 已提交读(read committed) | 语句级 | 否 | 是 | 是 |
| 可重复读(Repeatable red) | 事务级 | 否 | 否 | 是 |
| 可序列化(Serializable) | 最高级别，事务级 | 否 | 否 | 否 |
查看mysql的默认事务隔离级别“show global variables like ‘tx_isolation’; ”
Innodb的行锁模式有以下几种：共享锁，排他锁，意向共享锁(表锁)，意向排他锁(表锁)，间隙锁。
注意：当语句没有使用索引，innodb不能确定操作的行，这个时候就使用的意向锁，也就是表锁
关于死锁：
什么是死锁？当两个事务都需要获得对方持有的排他锁才能完成事务，这样就导致了循环锁等待，也就是常见的死锁类型。
解决死锁的方法：
1、 数据库参数
2、 应用中尽量约定程序读取表的顺序一样
3、 应用中处理一个表时，尽量对处理的顺序排序
4、 调整事务隔离级别（避免两个事务同时操作一行不存在的数据，容易发生死锁）
三、数据库文件差异
MyISAM：
myisam属于堆表
myisam在磁盘存储上有三个文件，每个文件名以表名开头，扩展名指出文件类型。
.frm 用于存储表的定义
.MYD 用于存放数据
.MYI 用于存放表索引
myisam表还支持三种不同的存储格式：
静态表(默认，但是注意数据末尾不能有空格，会被去掉)
动态表
压缩表
InnoDB：
innodb属于索引组织表
innodb有两种存储方式，共享表空间存储和多表空间存储
两种存储方式的表结构和myisam一样，以表名开头，扩展名是.frm。
如果使用共享表空间，那么所有表的数据文件和索引文件都保存在一个表空间里，一个表空间可以有多个文件，通过innodb_data_file_path和innodb_data_home_dir参数设置共享表空间的位置和名字，一般共享表空间的名字叫ibdata1-n。
如果使用多表空间，那么每个表都有一个表空间文件用于存储每个表的数据和索引，文件名以表名开头，以.ibd为扩展名。
四、索引差异
1、关于自动增长
myisam引擎的自动增长列必须是索引，如果是组合索引，自动增长可以不是第一列，他可以根据前面几列进行排序后递增。
innodb引擎的自动增长咧必须是索引，如果是组合索引也必须是组合索引的第一列。
2、关于主键
myisam允许没有任何索引和主键的表存在，
myisam的索引都是保存行的地址。
innodb引擎如果没有设定主键或者非空唯一索引，就会自动生成一个6字节的主键(用户不可见)
innodb的数据是主索引的一部分，附加索引保存的是主索引的值。
3、关于count()函数
myisam保存有表的总行数，如果select count(*) from table;会直接取出出该值
innodb没有保存表的总行数，如果使用select count(*) from table；就会遍历整个表，消耗相当大，但是在加了wehre 条件后，myisam和innodb处理的方式都一样。
4、全文索引
myisam支持 FULLTEXT类型的全文索引
innodb不支持FULLTEXT类型的全文索引，但是innodb可以使用sphinx插件支持全文索引，并且效果更好。（sphinx 是一个开源软件，提供多种语言的API接口，可以优化mysql的各种查询）
5、delete from table
使用这条命令时，innodb不会从新建立表，而是一条一条的删除数据，在innodb上如果要清空保存有大量数据的表，最 好不要使用这个命令。(推荐使用truncate table，不过需要用户有drop此表的权限)
6、索引保存位置
myisam的索引以表名+.MYI文件分别保存。
innodb的索引和数据一起保存在表空间里。
五、开发的注意事项
1、可以用 show create table tablename 命令看表的引擎类型。
2、对不支持事务的表做start/commit操作没有任何效果，在执行commit前已经提交。
3、可以执行以下命令来切换非事务表到事务（数据不会丢失），innodb表比myisam表更安全：alter table tablename type=innodb;或者使用 alter table tablename engine = innodb;
4、默认innodb是开启自动提交的，如果你按照myisam的使用方法来编写代码页不会存在错误，只是性能会很低。如何在编写代码时候提高数据库性能呢？
a、尽量将多个语句绑到一个事务中，进行提交，避免多次提交导致的数据库开销。
b、在一个事务获得排他锁或者意向排他锁以后，如果后面还有需要处理的sql语句，在这两条或者多条sql语句之间程序应尽量少的进行逻辑运算和处理，减少锁的时间。
c、尽量避免死锁
d、sql语句如果有where子句一定要使用索引，尽量避免获取意向排他锁。
f、针对我们自己的数据库环境，日志系统是直插入，不修改的，所以我们使用混合引擎方式，ZION_LOG_DB照旧使用myisam存储引擎，只有ZION_GAME_DB，ZION_LOGIN_DB，DAUM_BILLING使用Innodb引擎。
5、php完成事务处理的一个例子
事务处理（多sql要完成的任务看做一个事务，任何一个sql出错，整个事务都要撤销，如果都成功才去提交）
mysql使用innodb引擎才能支持事务
默认表都是自动提交的(autocommit),如果需要手工控制事务，需要做如下操作
1、关闭自动提交 //set autocommit=0;
2、start事务 //start transaction;
commit //提交
rollback //回滚
<?php
$mysqli= new mysqli("10.1.1.15","web","web","test","9188");
$mysqli->autocommit(0); //注意，在此模式下，此连接中的前一个事务回滚或者提交以后，会马上开启下一个事务。
$error=true;
$sql="update inno set sex=2 where name='andy'";
$result=$mysqli->query($sql);
if(!$result){
$error=false;
echo "sql执行失败<br>";
}else{
if($mysqli->affected_rows==0){
$error=false;
echo "数据没有改变<br>";
}else{
echo "sql sussceful";
}
}
if($error){
echo "事务成功";
$mysqli->commit();
}else{
echo "事务失败";
$mysqli->rollback();
}
$mysqli->close();
?>
转载自：http://www.mysqlops.com/2011/12/09/myisam%E5%92%8Cinnodb%E5%AF%B9%E6%AF%94%E8%AF%A6%E8%A7%A3.html
什么是MyISAM?
MyISAM是MySQL关系数据库管理系统的默认储存引擎。这种MySQL表存储结构从旧的ISAM代码扩展出许多有用的功能。在新版本的MySQL中，InnoDB引擎由于其对事务，参照完整性，以及更高的并发性等优点开始广泛的取代MyISAM。
每一个MyISAM表都对应于硬盘上的三个文件。这三个文件有一样的文件名，但是有不同的扩展名以指示其类型用途：.frm文件保存表的定义，但是这个文件并不是MyISAM引擎的一部，而是服务器的一部分；.MYD保存表的数据；.MYI是表的索引文件。
什么是InnoDB?
InnoDB是MySQL的另一个存储引擎，正成为目前MySQL AB所发行新版的标准，被包含在所有二进制安装包里。较之于其它的存储引擎它的优点是它支持兼容ACID的事务（类似于PostgreSQL),以及参数完整性（即对外键的支持）。
Oracle公司与2005年10月收购了Innobase。Innobase采用双认证授权。它使用GNU发行，也允许其它想将InnoDB结合到商业软件的团体获得授权。
目前比较普及的存储引擎是MyISAM和InnoDB.而MyISAM又是绝大部分Web应用的首选。MyISAM与InnoDB的主要的不同点在于性能和事务控制上。
MyISAM是早期ISAM(Indexed Sequential Access Method,现在用的MySQL5.0已经不支持ISAM了）的扩展实现，ISAM被设计为适合处理读频率远大于写频率这样一种情况，因此ISAM以及后来的MyISAM都没有考虑对事物的支持，排除了TPM，不需要事务记录，ISAM的查询效率相当可观，而且内存占用很少。MyISAM在继承了这类优点的同时，与时俱进的提供了大量实用的新特性和相关工具。例如考虑到并发控制，提供了表级锁，虽然MyISAM本身不支持容错，但可以通过myisamchk进行故障恢复。而且由于MyISAM是每张表使用各自独立的存储文件（MYD数据文件和MYI索引文件），使得备份及恢复十分方便（拷贝覆盖即可），而且还支持在线恢复。与其他存储引擎比较,MyISAM具有检查和修复表格的大多数工具. MyISAM表格可以被压缩,而且它们支持全文搜索.它们不是事务安全的,而且也不支持外键
所以如果你的应用是不需要事务，处理的只是基本的CRUD操作，那么MyISAM是不二选择
InnoDB被设计成适用于高并发读写的情况.使用MVCC(Multi-Version Concurrency Control)以及行级锁来提供遵从ACID的事务支持。InnoDB支持外键参照完整性，具备故障恢复能力。另外 InnoDB的性能其实还是不错的，特别是在处理大数据量的情况下,用官方的话说就是： InnoDB的CPU效率是其他基于磁盘的关系数据库存储引擎所不能比的。不过InnoDB的备份恢复要麻烦一点，除非你使用了4.1以后版本提供的Mulit-tablespace支持，因为InnoDB和MyISAM不同，他的数据文件并不是独立对应于每张表的。而是使用的共享表空间，简单的拷贝覆盖方法对他不适用，必须在停掉MYSQL后对进行数据恢复。使用Per-Table Tablespacesd，使其每张表对应一个独立的表空间文件，则情况要简单很多。它与BDB类型具有相同的特性,它们还支持外键.InnoDB表格速度很快.具有比BDB还丰富的特性,因此如果需要一个事务安全的存储引擎,建议使用它.
一般来说，如果需要事务支持，并且有较高的并发读写频率，InnoDB是不错的选择。要是并发读写频率不高的话，其实可以考虑BDB,但由于在MySQL5.1及其以后版本中，将不再提供BDB支持。这个选项也就没有了
InnoDB默认情况下的事务是打开的(set autocommit = 0)就是说每插入一条记录时候,InnoDB类型的表都会把它当作一个单独的事务来处理.所以如果我们插入了10000条记录,而且没有将事务关闭,那么InnoDB类型的表会把它当作10000个事务来处理,此时插入的总时间是很多的,这个时候一定要首先把事务关掉再插入,这样的速度就很快了
至于Heap和BDB（Berkeley DB），相对来说，普及率不如前两种，但在有些情况下，还是挺适用的
Heap存储引擎就是将数据存储在内存中，由于没有磁盘I./O的等待，速度极快。但由于是内存存储引擎，所做的任何修改在服务器重启后都将消失。
Heap挺适合做测试的时候使用
BDB是MySQL第一款事务安全的存储引擎。在Berkeley DB database library的基础上建立，同样是事务安全的，但BDB的普及率显然不及InnoDB，因为大多数在MySQL中寻找支持事务的存储引擎的同时也在找支持MVCC或是行级锁定存储引擎，而BDB只支持Page-level Lock。
附上一张《High Performance MySQL》 中的各存储引擎的特性表
| Attribute | MyISAM | Heap | BDB | InnoDB |
|---|---|---|---|---|
| Transactions | No | No | Yes | Yes |
| Lock granularity | Table | Table | Page (8 KB) | Row |
| Storage | Split files | In-memory | Single file per table | Tablespace(s) |
| Isolation levels | None | None | Read committed | All |
| Portable format | Yes | N/A | No | Yes |
| Referential integrity | No | No | No | Yes |
| Primary key with data | No | No | Yes | Yes |
| MySQL caches data records | No | Yes | Yes | Yes |
| Availability | All versions | All versions | MySQL-Max | All Versions |
转载自：http://drizzlewalk.blog.51cto.com/2203401/443266
| MyISAM | InnoDB | |
|---|---|---|
| 存储结构 | 每张表被存放在三个文件：
| 所有的表都保存在同一个数据文件中（也可能是多个文件，或者是独立的表空间文件），InnoDB表的大小只受限于操作系统文件的大小，一般为2GB |
| 存储空间 | MyISAM可被压缩，存储空间较小 | InnoDB的表需要更多的内存和存储，它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引 |
| 可移植性、备份及恢复 | 由于MyISAM的数据是以文件的形式存储，所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作 | 免费的方案可以是拷贝数据文件、备份 binlog，或者用 mysqldump，在数据量达到几十G的时候就相对痛苦了 |
| 事务安全 | 不支持每次查询具有原子性 | 支持具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表 |
| AUTO_INCREMENT | MyISAM表可以和其他字段一起建立联合索引 | InnoDB中必须包含只有该字段的索引 |
| SELECT | MyISAM更优 | |
| INSERT | InnoDB更优 | |
| UPDATE | InnoDB更优 | |
| DELETE | InnoDB更优它不会重新建立表，而是一行一行的删除 | |
| COUNT without WHERE | MyISAM更优。因为MyISAM保存了表的具体行数 | InnoDB没有保存表的具体行数，需要逐行扫描统计，就很慢了 |
| COUNT with WHERE | 一样 | 一样，InnoDB也会锁表 |
| 锁 | 只支持表锁 | 支持表锁、行锁行锁大幅度提高了多用户并发操作的新能。但是InnoDB的行锁，只是在WHERE的主键是有效的，非主键的WHERE都会锁全表的 |
| 外键 | 不支持 | 支持 |
| FULLTEXT全文索引 | 支持 | 不支持可以通过使用Sphinx从InnoDB中获得全文索引，会慢一点 |
总的来说，MyISAM和InnoDB各有优劣，各有各的使用环境。
但是InnoDB的设计目标是处理大容量数据库系统，它的CPU利用率是其它基于磁盘的关系数据库引擎所不能比的。
我觉得使用InnoDB可以应对更为复杂的情况，特别是对并发的处理要比MyISAM高效。同时结合memcache也可以缓存SELECT来减少SELECT查询，从而提高整体性能。
http://www.mysqlops.com/2011/12/09/myisam%E5%92%8Cinnodb%E5%AF%B9%E6%AF%94%E8%AF%A6%E8%A7%A3.html
http://drizzlewalk.blog.51cto.com/2203401/443266
http://www.pureweber.com/article/myisam-vs-innodb/
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
