InnoDB支持事务,MyISAM不支持事务;对于InnoDB,每一条SQL语句都默认封装成事务,自动提交,这样会影响速度,所以最好把多条SQL语句放在begin和commit之间,组成一个事务;
InnoDB支持外键(两个表之间,外键一是一棵索引B+树,键和索引表示同一个意思),MyISAM不支持外键;对一个包含外键的InnoDB表转为MyISAM会失败;
InnoDB和MyISAM的索引都是使用B+树结构,但是:InnoDB使用的是 “聚集索引(clustered index) + 辅助索引(secondary index)”,其中聚集索引的叶子结点存储的是数据本身,数据文件直接存储在主键索引这棵B+树上,辅助索引的叶子节点存储的是主键的值,需要通过回表到主键B+树上才能找到数据,需要两次索引;MyISAM是非聚集索引,数据文件和索引是完全分离开的,所有索引树的叶子结点存储的都是 数据文件的地址指针。
也正是由于上述原因,InnoDB必须要有唯一索引(有主键索引最好,如果没有主键索引就使用第一个NOT NULL的唯一索引做聚簇索引,如果这个也没有,InnoDB就使用一个隐藏的row_id用来做聚簇索引,以存储数据文件),而MyISAM可以没有索引。
InnoDB不保存表的具体行数,执行 select count(*) from table
时需要全表扫描;而MyISAM使用一个变量保存了整个表的行数,执行上述语句时只需要读出该变量的值即可,速度很快。
InnoDB之所以不保存 count(*) 的值是因为: 由于InnoDB的事务特性,在同一时刻表中的行数对于不同的事务而言是不一样的(快照读),因此count统计会计算对于 当前事务 而言可以统计到的行数,而不是将总行数保存起来。
InnoDB不支持全文索引,MyISAM支持全文索引。(5.7 版本之后的InnoDB也开始支持全文索引了)
InnoDB支持 行级锁(默认)、表级锁;MyISAM只支持表级锁。
注意InnoDB的行级锁是实现在索引上的,而不是锁在物理行记录上,也就是说如果访问没有命中索引,也无法使用行级锁,将使用表级锁。
InnoDB的存储文件有 frm、idb: frm是表定义文件, ibd 是数据文件;
MyISAM的存储文件有 frm、myd、myi: frm是表定义文件, myd 是数据文件, myi 是索引文件。
如需要则必须选择InnoDB,如不需要可以考虑MyISAM;
由于InnoDB采用的是行级锁,所以对数据库 “写” 操作能够支持更高的并发度,因此:当表中绝大多数的操作都是 读 操作时,可以考虑使用MyISAM;如果既有读也有写,请使用InnoDB。(例如 )
InnoDB在系统崩溃后恢复起来更容易(采用redo log + binlog “两阶段提交”的方式,redo log是循环写,当宕机后,redo log中存储的就是还没有来得及刷盘的数据),而MyISAM在系统崩溃后回复起来更困难(只有binlog,binlog是追加写,当宕机恢复后,不知道哪些数据写了磁盘,哪些数据还没写磁盘);
redo log是InnoDB独有的,binlog是MySQL server层的,所有的存储引擎都可以使用;
MySQL 5.5 版本之后,InnoDB取代了MyISAM成为了系统的默认存储引擎,说明其优势有目共睹;如果你不知道用什么,那就用InnoDB,至少不会差。