MySQL的存储引擎 —— InnoDB和MyIsAM

欢迎指正。

MySQL 作为现在互联网行业使用最广的数据库之一，最近几天刚好看到两者的区别，就想了解下数据库的存储引擎。MySQL的主流数据存储引擎有 InnoDB 和 MyIsAM 两种。

InnoDB存储引擎

InnoDB 是 MySQL 的默认事务性引擎，它被设计用来处理大量短期事务，短期事务大部分情况是正常提交的，很少会回滚。InnoDB采用 MVCC(多版本并发控制)来支持高并发，并实现四个标准的隔离级别。其默认级别是 REPEATABLE READ(可重复读)，并且通过间隙锁策略防止出现幻读的出现。

InnoDB 引擎是基于聚簇索引建立的。聚索簇引对主键查询有很高的性能。

MVCC

可以认为是行级锁的一个变种，但是在很多情况下避免了加锁的操作，因此开销更低。实现思路大体都是非阻塞的读，写操作只锁定必要的行。

每行数据都存在一个版本，每次数据更新时都更新该版本
修改时Copy出当前版本随意修改，每个事务之间无干扰
保存时比较版本号，如果成功（commit），则覆盖原记录；失败则放弃copy（rollback）

就是每行都有版本号，保存时根据版本号决定是否成功，挺像乐观锁的，而 Innodb 的实现方式是：

事务以排他锁的形式修改原始数据
把修改前的数据存放于undo log，通过回滚指针与主数据关联
修改成功（commit）啥都不做，失败则恢复undo log中的数据（rollback）

理想的MVCC很难实现。如果 Transaciton1 执行理想的 MVCC，修改 Row1 成功，而修改 Row2 失败，此时需要回滚 Row1，但因为 Row1 没有被锁定，其数据可能又被 Transaction2 所修改，如果此时回滚 Row1 的内容，则会破坏 Transaction2 的修改结果，导致 Transaction2 违反 ACID。

InnoDB 行格式

InnoDB 的设计者为我们提供了4中不同类型的行格式分别是 Compact、Redundant、Dynamic 和 Compressed 行格式。

VARCHAR(M)溢出

varchar 类型字段占用的字节长度不能超过 65535。但是在实际中，varchar 类型的列，其实需要占用3部分储存空间：

真实数据
真实数据占用字节的长度
NULL 值标识，如果该列有 NOT NULL 属性则可以没有这部分储存空间

如果该VARCHAR类型的列没有NOT NULL属性，那最多只能存储65532个字节的数据，因为真实数据的长度可能占用2个字节，NULL值标识需要占用1个字节。

如果VARCHAR类型的列有NOT NULL属性，那最多只能存储65533个字节的数据，因为真实数据的长度可能占用2个字节，不需要NULL值标识。

MyIsAM 存储引擎

MyIsAM 存储引擎提供了大量特性，包括全文索引、压缩、空间函数等，但是它不支持事务和行级锁，这是和InnoDB存储引擎的区别之一。对于只读数据，或者表比较小、可以忍受修复操作，则可以使用MyIsAM引擎。

MyIsAM 特性

加锁和并发：MyIsAM 对整张表加锁，而不是针对行。读数据会对需要读到的所有表加共享锁，写入是则对表加排他锁。但是在表有读取查询的时候，也可以往表里插入新数据。
修复：手动或者自动执行检查和修复操作。
索引特性：可以针对长字段，基于其前500个字符创建索引。MyIsAM 也支持全文索引，这是一种基于分词创建的索引。
延迟更新索引键：创建 MyISAM表的时候，如果指定了 DELAY KEY WRITE选项，在每次修改执行完成时，不会立刻将修改的索引数据写入磁盘，而是会写到内存中的键缓冲区(in-memory key buffer)，只有在清理键缓冲区或者关闭表的时候才会将对应的索引块写入到磁盘。

InnoDB和MyIsAM的区别

下面的表格简单的列举了两者的区别：

	InnoDB	MyIsAM
事务	支持	不支持
锁	MVCC	表锁
外键	支持	不支持
存储空间	存储空间需要高速缓存，较大	可压缩
使用场景	有一定量的insert和update	大量select
是否支持全文查询	5.6之后就支持了	支持

下面的内容是别人公众号里看了的。

关于count(*)

MyISAM 会直接存储总行数，InnoDB则不会，需要按行扫描。对于select count(*) from t; 如果数据量大，MyISAM 会瞬间返回，而InnoDB则会一行行扫描。

常见坑：只有查询全表的总行数，MyISAM才会直接返回结果，当加了where条件后，两种存储引擎的处理方式类似。

关于全文索引

MyISAM 支持全文索引，InnoDB5.6 之前不支持全文索引。

关于事务

MyISAM不支持事务，InnoDB支持事务。

关于外键

知识点：MyISAM不支持外键，InnoDB支持外键。

实践：不管哪种存储引擎，在数据量大并发量大的情况下，都不应该使用外键，而建议由应用程序保证完整性。

关于行锁与表锁

知识点：MyISAM只支持表锁，InnoDB可以支持行锁。

分析：MyISAM：执行读写SQL语句时，会对表加锁，所以数据量大，并发量高时，性能会急剧下降。
InnoDB：细粒度行锁，在数据量大，并发量高时，性能比较优异。

实践：网上常说，select+insert的业务用MyISAM，因为MyISAM在文件尾部顺序增加记录速度极快。楼主的建议是，绝大部分业务是混合读写，只要数据量和并发量较大，一律使用InnoDB。

常见坑：InnoDB的行锁是实现在索引上的，而不是锁在物理行记录上。潜台词是，如果访问没有命中索引，也无法使用行锁，将要退化为表锁。

蒋先森のBlog