MySQL - InnoDB vs MyISAM 引擎的区别

阅读目录(Content)

总结
InnoDB vs MyISAM

1. MyISAM保存总行数 vs InnoDB不保存总行数
2. MyISAM非聚集索引 vs InnoDB聚集索引
3. 主索引 vs 辅助索引
4. MyISAM表锁 vs InnoDB行锁
5. “数据”在物理空间存在的位置

回到顶部(go to top)

总结

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 用一个变量保存表的总行数，查总行数速度很快

回到顶部(go to top)

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两个隔离级别下才能正常工作。