MySQL - InnoDB vs MyISAM 引擎的区别

总结

InnoDB 支持事务；MyISAM 不支持事务
InnoDB 支持行级锁；MyISAM 支持表级锁
InnoDB 是聚集索引，数据文件是和主索引绑在一起的，必须要有主键（MySQL没有主键怎么办?会自动生成主键么？自动生成的主键有什么问题？）
MyISAM 是非聚集索引，数据文件是分离的，主索引/辅助索引保存的只是数据文件的地址
主索引 vs 辅助索引
- InnoDB的主索引本身就是数据文件，因此主索引的data域保存的是数据文件本身；辅助索引的data域保存的是主键。
- MyISAM的主索引和辅助索引在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。他们的data域保存的都只是数据文件的地址
其他差异：
- InnoDB 支持 MVCC(多版本并发控制)；MyISAM 不支持
- InnoDB 支持外键，MyISAM 不支持
- MySQL 5.6 以前的版本，InnoDB 不支持全文索引，MyISAM 支持；MySQL 5.6 及以后的版本，MyISAM 和 InnoDB 存储引擎均支持全文索引
- InnoDB 不保存表的总行数，执行 select count(*) from table 时需要全表扫描；MyISAM 用一个变量保存表的总行数，查总行数速度很快

InnoDB vs MyISAM

1. MyISAM保存总行数 vs InnoDB不保存总行数

问题：一张表，里面有 ID 自增主键，当 insert 了 17 条记录之后，删除了第 15,16,17 条记录，（只剩下1，2，3....13，14这些数据）再把 Mysql 重启，再 insert 一条记录，这条记录的 ID 是 18 还是 15 ？

(1)如果表的类型是 MyISAM，那么是 18。因为 MyISAM 表会把自增主键的最大 ID 记录到数据文件里，重启 MySQL 自增主键的最大ID 也不会丢失
(2)如果表的类型是 InnoDB，那么是 15。 InnoDB 表只是把自增主键的最大 ID 记录到内存中，所以重启数据库或者是对表进行OPTIMIZE 操作，都会导致最大 ID 丢失。会从数据库当前的ID继续往后自增。

2. MyISAM非聚集索引 vs InnoDB聚集索引

InnoDB 是聚集索引，数据文件是和索引绑在一起的，必须要有主键，通过主键索引效率很高。（扩展：MySQL没有主键怎么办?会自动生成主键么？自动生成的主键有什么问题？）

MyISAM 是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针，主键索引和辅助索引是独立的。

3. 主索引 vs 辅助索引

InnoDB

主索引本身就是数据文件，因此主索引的data域保存的是数据文件本身；
辅助索引的data域保存的是主键。辅助索引需要两次查询，先查询到主键，再通过主键查询到数据。主键太大，其他索引也会很大；

数据：col1是主键

定义在Col1上的一个主索引：

定义在Col3上的一个辅助索引：

MyISAM

主索引和辅助索引在结构上没有任何区别。只是主索引要求key是唯一的，而辅助索引的key可以重复。
他们的data域保存的都只是数据文件的地址

如果我们在Col1上建立一个主索引，则此索引的结构如下图所示

如果我们在Col2上建立一个辅助索引，则此索引的结构如下图所示：

4. MyISAM表锁 vs InnoDB行锁

MyISAM: 表锁，即使操作一条记录也会锁住整个表，不适合高并发的操作

InnoDB: InnoDB采用MVCC(Multi-Version Concurrency Control 多版本并发控制)来支持高并发，一种行级锁的变种。MVCC是通过保存数据在某一个时间点的快照来实现的，也就是说无论事务执行多久，每个事务看到的数据都是一致的。InnoDB的MVCC，是通过在每行记录后面保存两个隐藏的列来实现，这两个列一个保存了行的创建时间，一个保存了行的过期时间(或删除时间)，当然，并非存储的是时间，而是系统版本号。每开启一个事务，版本号都会递增，事务开始时刻的系统版本号会作为事务的版本号。

注：MVCC只在REPEATABLE和READ COMMITTED两个隔离级别下才能正常工作。