Mysql学习笔记：存储引擎MyISAM和InnoDB的区别

在整理文章《 Mysql索引的理解：B-Tree(B-树)和B+Tree(B+树) 》时，突然对 “MyISAM和InnoDB存储引擎区别” 有了一个新的理解： 两者之间区别之间是有很大关联性的 。后面我会慢慢说明。

构成上的区别

Mysql 在设计时把表信息和数据是分开，这在 Mysql8 以前表现为 .frm(表信息元数据)以及 xxx 文件。这是为什么是 xxx 文件，因为这里是本文整理的第一个区别。

索引和数据的存储结构区别

• MyISAM 将索引和数据分别保存在 .MYI(索引) 和 .MYD(数据) 中
• InnoDB 则将索引和数据保存在了相同的文件 .ibd 或者 ibdata 中，.ibd 看似是 ibdata 的缩写，但其它含义是不一样，首先，.ibd 我用了 “.” 表示后缀的意思，但是 ibdata 并没有；其次，.ibd 是独享表空间，ibdata 是共享表空间，可通过 innodb_file_per_table 来修改。

注意：上文中突出了 Mysql 8 以前，是因为 Mysql8 已经将 .frm .opt 之类的文件放入到了事务表中进行维护，目的是很明显的。

上文说到了，MyISAM 存储引擎把索引和数据分开存放，但是在实际使用过程它俩是无法分开的，因为通过索引要能找到数据，这里是本文整理的第二个区别。

索引实现区别

• 首先，二者都是使用 B+ tree 实现的，但是在叶子结点中存储的数据是不一样的，MyISAM 存储的是数据行的地址，而 InnoDB 不一定，为什么说不一定，看下一条；
• MyISAM 索引和数据分开，所以它的索引都是存储的数据行地址，但是 InnoDB 不同，对于主键，它存储的是整个数据，而辅助索引存储的是主键ID，这里引申出下一条；
• InnoDB 必需有主键，当然，你可以说你不创建，但是引擎会默认给你加上一个 6 字节的隐藏主键， 建议开发者明确指定 InnoDB 表主键 ；
• 怎么记索引实现区别，两个词： “聚集”、“非聚集” 。

可移植性、备份及恢复

• MyISAM 数据文件可被压缩，存储空间较小。备份恢复可拷贝文件；
• InnoDB 也可以拷贝文件，但是数据量大时就麻烦;
• 都可 mysqldump 和 binlog。

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

事务及外键

• MyISAM 不支持事务和外键；
• InnoDB 支持事务和外键。

表锁差异

• MyISAM 只支持表级锁，用户在操作 MyISAM 表时，select，update，delete，insert 语句都会给表自动加锁，如果加锁以后的表满足 insert 并发的情况下，可以在表的尾部插入新的数据。
• InnoDB 支持事务和行级锁，是 InnoDB 的最大特色。行锁大幅度提高了多用户并发操作的新能。但是 InnoDB的行锁，只是在 WHERE 的主键是有效的，非主键的 WHERE 都会锁全表的。

全文索引

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