MySQL数据库存储引擎MyISAM和InnoDB的对比详解

http://www.mysqlops.com/2011/12/09/myisam%E5%92%8Cinnodb%E5%AF%B9%E6%AF%94%E8%AF%A6%E8%A7%A3.html 之前Eugene兄已经写过两篇关于myisam转innodb引擎的文章，我今天把myisam和innodb引擎的差别做了一个整理，目的是让大家更深的理解两个引擎的差别之处

http://www.mysqlops.com/2011/12/09/myisam%E5%92%8Cinnodb%E5%AF%B9%E6%AF%94%E8%AF%A6%E8%A7%A3.html

之前Eugene兄已经写过两篇关于myisam转innodb引擎的文章，我今天把myisam和innodb引擎的差别做了一个整理，目的是让大家更深的理解两个引擎的差别之处，让大家在项目初期选择数据库引擎或者中期改变引擎的时候少走弯路。

一、简单介绍
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();
              ?>