MySQL InnoDB与MyISAM引擎区别

MyISAM引擎

MyISAM 是 MySQL 的默认数据库引擎（5.5 版之前），由早期的 ISAM（Indexed Sequential Access Method：有索引的顺序访问方法）所改良。虽然性能极佳，但却有一个缺点：不支持事务处理（transaction）。不过，在这几年的发展下，MySQL 也导入了 InnoDB（另一种数据库引擎），以强化参考完整性与并发违规处理机制，后来就逐渐取代 MyISAM。

InnoDB引擎

InnoDB，是 MySQL 的数据库引擎之一，为 MySQL AB 发布 binary 的标准之一。InnoDB 由 Innobase Oy 公司所开发，2006 年五月时由甲骨文公司并购。与传统的 ISAM 与 MyISAM 相比，InnoDB 的最大特色就是支持了 ACID 兼容的事务（Transaction）功能，类似于 PostgreSQL。目前 InnoDB 采用双轨制授权，一是 GPL 授权，另一是专有软件授权。

MyISAM与InnoDB区别

存储结构

MyISAM： 每个 MyISAM 在磁盘上存储成三个文件。第一个文件的名字以表的名字开始，扩展名指出文件类型。 .frm 文件存储表定义。数据文件的扩展名为 .MYD (MYData)。索引文件的扩展名是 .MYI (MYIndex)。

InnoDB： 所有的表都保存在同一个数据文件中（也可能是多个文件，或者是独立的表空间文件），InnoDB 表的大小只受限于操作系统文件的大小，一般为 2GB。

存储空间

MyISAM： 可被压缩，存储空间较小。支持三种不同的存储格式：静态表(默认，但是注意数据末尾不能有空格，会被去掉)、动态表、压缩表。

InnoDB： 需要更多的内存和存储，它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。

可移植性、备份及恢复

MyISAM： 数据是以文件的形式存储，所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作。

InnoDB： 免费的方案可以是拷贝数据文件、备份 binlog，或者用 mysqldump，在数据量达到几十 G 的时候就相对痛苦了。

事务支持

MyISAM： 强调的是性能，每次查询具有原子性，其执行数度比 InnoDB 类型更快，但是不提供事务支持。

InnoDB： 提供事务支持事务，外部键等高级数据库功能。 具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。

AUTO_INCREMENT

MyISAM： 可以和其他字段一起建立联合索引。引擎的自动增长列必须是索引，如果是组合索引，自动增长可以不是第一列，他可以根据前面几列进行排序后递增。

InnoDB： InnoDB 中必须包含只有该字段的索引。引擎的自动增长列必须是索引，如果是组合索引也必须是组合索引的第一列。

表锁差异

MyISAM： 只支持表级锁，用户在操作 MyISAM 表时，select，update，delete，insert 语句都会给表自动加锁，如果加锁以后的表满足 insert 并发的情况下，可以在表的尾部插入新的数据。

InnoDB： 支持事务和行级锁，是 InnoDB 的最大特色。行锁大幅度提高了多用户并发操作的新能。但是 InnoDB 的行锁，只是在 WHERE 的主键是有效的，非主键的 WHERE 都会锁全表的。

全文索引

MyISAM： 支持 FULLTEXT 类型的全文索引。

InnoDB： 不支持 FULLTEXT 类型的全文索引，但是 InnoDB 可以使用 sphinx 插件支持全文索引，并且效果更好。

表主键

MyISAM： 允许没有任何索引和主键的表存在，索引都是保存行的地址。

InnoDB： 如果没有设定主键或者非空唯一索引，就会自动生成一个6字节的主键(用户不可见)，数据是主索引的一部分，附加索引保存的是主索引的值的数据列。

表的具体行数

MyISAM： 保存有表的总行数，如果 select count() from table; 会直接取出出该值。

InnoDB： 没有保存表的总行数，如果使用 select count() from table；就会遍历整个表，消耗相当大，但是在加了 wehre 条件后，MyISAM 和 InnoDB 处理的方式都一样。

CURD操作

MyISAM： 如果执行大量的 SELECT，MyISAM 是更好的选择。

InnoDB： 如果你的数据执行大量的 INSERT 或 UPDATE，出于性能方面的考虑，应该使用 InnoDB 表。DELETE 从性能上 InnoDB 更优，但 DELETE FROM table 时，InnoDB 不会重新建立表，而是一行一行的删除，在 InnoDB 上如果要清空保存有大量数据的表，最好使用 truncate table 这个命令。

外键

MyISAM： 不支持。

InnoDB： 支持。

索引结构

InnoDB 是 B+ 树的索引结构。InnoDB 的索引文件本身就是数据文件，即 B+Tree 的数据域存储的就是实际的数据，这种索引就是聚集索引。这个索引的 key 就是数据库的主键，因此 InnoDB 表数据文件本身就是主索引。

InnoDB 的辅助索引数据与存储的也是相应记录主键的值而不是地址，所以当用辅助索引查找时，会先根据辅助索引找到主键，再根据主键找到实际的数据。所以 InnoDB 不建议使用过长的主键，否则会使辅助索引变得很大。建议使用自增的字段作为主键，这样 B+tree 的每个节点都会被顺序的填满，而不会频繁的分裂调整，会有效提升插入数据的效率。

MyISAM 索引用的 B+tree 来存储数据，MyIASM 索引的指针指向的是键值的地址，地址存储的是数据。B+tree 的数据域存储的内容为实际数据的地址，也就是说它的索引和实际数据是分开的，这种索引就是所谓的非聚集索引。

总结

通过上述的分析，基本上可以考虑使用 InnoDB 来替代 MyISAM 引擎了，原因是 InnoDB 自身很多良好的特点，比如事务支持、存储 过程、视图、行级锁定等等，在并发很多的情况下，相信 InnoDB 的表现肯定要比 MyISAM 强很多。另外，任何一种表都不是万能的，只用恰当的针对业务类型来选择合适的表类型，才能最大的发挥 MySQL 的性能优势。如果不是很复杂的 Web 应用，非关键应用，还是可以继续考虑 MyISAM 的，这个具体情况可以自己斟酌。

InnoDB与MyISAM引擎适用场景

InnoDB

1. 经常更新的表，适合多并发的更新请求。
2. 支持事务。
3. 可以从灾难中恢复（通过 bin-log 日志等）。
4. 外键约束，只有它支持外键。
5. 支持自动增加列属性 auto_increment。

MyISAM

1. 不支持事务的设计，但是并不代表者有事务操作的项目不能使用 MyISAM 存储引擎，可以在 service 层根据自己的业务需求进行相应的控制。
2. 不支持外键。
3. 查询速度很快，MyISAM 极度强调快读读取操作。
4. 整天对表加锁的场景。
5. MyISAM 中存储了表的行数，于是 SELECT COUNT(*) FROM TABLE 时只需要直接读取保存号的值而不需要扫面全表。
6. 如果表的读操作远远多于写操作且不需要数据库事务的支持，那么 MyISAM 也是很好的选择。
7. 缺点：不能在表损坏后主动恢复数据。

如何选择存储引擎

MyISAM： 默认的 MySQL 插件式存储引擎。如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常适合的。MyISAM 是在 Web、数据仓储和其他应用环境下最常使用的存储引擎之一。

InnoDB： 用于事务处理应用程序，支持外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询以外，还包括很多的更新、删除操作，那么 InnoDB 存储引擎应该是比较合适的选择。InnoDB 存储引擎除了有效地降低由于删除和更新导致的锁定，还可以确保事务的完整提交（Commit）和回滚（Rollback），对于类似计费系统或者财务系统等对数据准确性要求比较高的系统，InnoDB 都是合适的选择。

MEMORY： 将所有数据保存在 RAM 中，在需要快速定位记录和其他类似数据的环境下，可提供极快的访问。MEMORY 的缺陷是对表的大小有限制，太大的表无法缓存在内存中，其次是要确保表的数据可以恢复，数据库异常终止后表中的数据是可以恢复的。MEMORY 表通常用于更新不太频繁的小表，用以快速得到访问结果。