MySQL存储引擎InnoDB和MyISAM对比

• 事务支持：
  • InnoDB支持事务，外键等高级数据库功能。具有事务、回滚和崩溃修复能力的事务安全型表。
  • MyISAM不支持事务，强调的是性能，每次查询具有原子性，其执行行数度比InnoDB类型更快。
• 存储结构：
  • 每个MyISAM在磁盘上存储成三个文件。文件名为所属表名，扩展名为.frm文件存储表定义。数据文件的扩展名为.MYD(MYData)。索引文件的扩展名是.MYI(MYIndex)。
  • 所有的表都保存在一个数据文件中（也可能是多个文件），InnoDB表的大小受限于操作系统同文件的大小，一般为2GB。
• 表锁差异：
  • MyISAM只支持表级锁，用户在操作MyISAM表时，增删改查都会给表自动加锁，如果加锁后的表满足insert并发的情况下，可以在表的尾部插入新的数据。
    • MyISAM并发读写时，如果等待队列中既有读请求又有写请求，默认写请求的优先级更高，即时读请求先到。所有MyISAM不适合用于有大量查询和修改并存的情况。
  • InnoDB支持事务和行级锁，但InnoDB的行锁，只是在WHERE条件是主键时有效，其他情况表现为表级锁。
• 全文索引：
  • MyISAM支持(FULLTEXT类型的)全文索引。
  • InnoDB不支持(FULLTEXT类型的)全文索引，但InnoDB可以使用sphinx插件来实现全文索引的功能。
  • 全文索引是指char、varchar和text中的每个词(停用词除外)建立倒排序索引。MyISAM的全文索引其实没啥用，因为它不支持中文分词，必须由使用者分词后加入空格再写到数据表中，而且少于4个汉字的词会和停用词一样被忽略掉。
  • MyISAM的索引和数据是分离的，并且索引是有压缩的。而InnoDB是和数据紧密捆绑的，索引没有压缩从而造成InnoDB比MyISAM体积庞大不少。
  • MyISAM天生非聚簇索引，InnoDB的诗句文件本身就是主键索引文件，这样的索引称为 聚簇索引。
• 表主键：
  • MyISAM允许没有任何索引和主键的表存在，索引就是保存行的地址。
  • 如果没有设定主键或者非空唯一索引，就会自动生成一个6字节的主键隐藏列(用户不可见)。
• 查询效率：
  • 没有where的count(*) 语句：MyISAM比InnoDB快的多。
  • 因为MyISAM内置了一个计数器，count(*)时它直接从计数器中读取，而InnoDB必须扫描全表。所以在InnoDB上执行count(*)时一般要伴随where，并且where中要包含主键(primary key / clustered index)以外的索引列(secondary index)。因为InnoDB中主键和行数据是存放在一起的，而secondary index是单独存放的。然后有个指针指向主键。而主键则主要在扫描索引同事要返回具体行数据时作用较大。