MySQL 存储引擎 | MyISAM 与 InnoDB

---

概念

MyISAM 是 MySQL 的默认数据库引擎（5.5版之前），但因为不支持事务处理而被 InnoDB 替代。

然而事物都是有两面性的，InnoDB 支持事务处理也会带来一些问题：

• 当操作完全兼容 ACID 时，InnoDB 会自动合并多个连接，但每次有事务产生时，仍至少须写入硬盘一次，因此对于某些硬盘或磁盘阵列，会造成 每秒200次 的事务处理上限。

• 若希望达到更高的性能且保持事务的完整性，就必使用磁盘缓存与电池备援。当然 InnoDB 也提供数种对性能冲击较低的模式，但相对的也会降低事务的完整性。

---

innodb引擎的4大特性

插入缓冲(insert buffer)：

为解决非聚集索引的写性能问题（插入或更新）而生。

对非聚集索引的插入或更新操作，不是每一次都直接插入到索引页中，而是先判断插入的非聚集索引页是否在缓冲池中：

• 若在则直接插入；
• 不在则先放入到一个 Insert Buffer 对象中。

此时看似数据库这个非聚集索引已经插入到了叶子节点，然而实际上只是存放在另一个位置。然后再以一定的频率进行 Insert Buffer 与辅助索引叶子节点的 merge 操作，此时通常能将多个插入合并到一个操作中，这就大大提高了非聚集索引插入的性能。

二次写(double write)：

数据库宕机可能会引起部分写失效（partial page write），解决方法有两种：

• 数据库宕机，物理文件完好无损，是可以通过 redo log 进行崩溃恢复（ACID中的持久性）。
• 数据库宕机，物理文件由于宕机发生损坏，这时就无法通过 redo log 进行数据恢复了, 而只能通过 double write 解决问题。

二次写分为三步：

1. 对缓冲池脏页进行刷新时，先将脏页复制到内存的 double write buffer ；
2. 第一次写： 将 double write buffer 分批次写入到共享表空间中，因为共享表空间是连续存储所以很快。
3. 第二次写： 将 double write buffer 中数据写入到各个表空间文件中，而此时的写入则是离散的，因为各个表空间可能在磁盘的不同位置，因此写入速度较慢。

PS：参数 skip_innodb_doublewrite 可以禁用 double write 功能，但不推荐这样做。对于需要提供数据高可靠性的主服务器，任何时候都应该确保开启 double write 功能。

自适应哈希索引(ahi)：

InnoDB存储引擎 会监控对表上各个索引页的查询，如果它观察到建立哈希索引可以带来速度提升，则会自行建立哈希索引，这也就是 自适应哈希索引。 即会自动根据 访问频率和模式 来为 热点数据 建立哈希索引。

哈希索引是数据库自身自动创建并使用的，人工无法对其进行干预。

预读(read ahead)：

为了提高磁盘操作性能，当前的数据库系统都采用 异步IO 的方式来处理磁盘操作，InnoDB 也是如此。

同步阻塞： 每进行一次IO操作，需要等待此次操作结束才能继续接下来的操作。

但是如果一条 SQL语句 需要扫描多个索引页，也就是需要进行多次 IO操作(扫描一个页就是一次IO请求) 。同步阻塞会扫描一个页并等待其完成再进行下一个页扫描，也就是等待一个IO请求完成后再发送下一个IO请求，效率很低。

异步非阻塞（AIO）： 用户可以在发出一个 IO请求 后不必等待其完成，而是可以立即发出下一个 IO请求 。

---

索引结构

我们在MySQL 索引 ：哈希索引、B+树索引、全文索引中介绍过聚集索引和非聚集索引，现在结合两个存储引擎深入研究一下。

两者都使用 B+树 作为索引结构，但实现方式却截然不同：

• 主键索引： InnoDB 的数据文件本身就是索引文件；MyISAM 主键索引的叶节点存的是数据地址。
• 辅助索引： InnoDB 的辅助索引 data域 存储相应记录主键的值而不是地址； MyISAM 辅助索引的叶节点存的还是数据地址。

InnoDB

InnoDB 是聚集索引：

• 数据文件和主键索引绑在一起（表数据文件本身就是按 B+Tree 组织的一个索引结构），必须要有主键，通过主键索引效率很高。主键索引的叶节点保存了完整的数据记录。
• 但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。主键不应该过大，因为主键太大，其他索引也都会很大。辅助索引的叶子节点是主键的值。
  
  图源博客

MyISAM

MyISAM 是 非聚集索引：

• 索引和数据文件是分离的，主键索引和辅助索引的叶子节点都是数据文件的地址指针。主键索引要求每个叶子节点的内容是唯一的，而辅助索引每个叶子节点的内容可以重复。
• 主键索引和辅助索引是独立的。因此不同于 InnoDB 需要先在辅助索引中查找到主键，再通过主键在主键索引中查找到具对应数据。MyISAM 可通过辅助索引快速找到所有的数据，而不需要再遍历一边主键索引，所以适用于OLAP。
  

---

区别

	myisam	innodb
索引类型	支持 B-tree、FullTex、R-tree 索引类型	支持 hash、B-tree 索引类型，可以通过使用插件 Sphinx 从 InnoDB 中获得全文索引，会慢一点。
索引	Myisam 可以不用主键	InnoDB 表必须有唯一索引（如主键）（用户没有指定的话会自己找/生产一个隐藏列 Row_id 来充当默认主键）
存储结构	每张表存放三个文件：frm：格式定义； MYD(MYData)：数据文件； MYI(MYIndex)：索引文件。	Innodb 存储文件有 frm：格式定义；ibd ：数据文件。表的大小只受限于操作系统文件的大小，一般为 2GB 。
存储空间	MyISAM 提供 压缩 与 简洁行（terse row formats），存储空间较小。	InnoDB 的表需要更多的内存和存储，在主内存中建立专用的缓冲池用于高速缓冲数据和索引，对硬盘和高速缓存的使用量较大。
读写缓存	MyISAM 没有缓存管理机制，必须依靠操作系统来管理读取与写入的缓存。	InnoDB 有读写缓存管理机制，不会将被修改的数据页立即交给操作系统。通常 InnoDB 的数据访问会比 MyISAM 更有效率。
可移植性、备份	由于 MyISAM 的数据是以文件的形式存储，所以在跨平台的数据转移中会很方便。	免费的方案可以是拷贝数据文件、备份 binlog，或者用 mysqldump，在数据量达到几十G的时候就相对痛苦了
恢复	MyISAM 遇到错误，必须完整扫描全表后才能重建索引，或修正未写入硬盘的错误。修复时间与数据量的多少成正比。	InnoDB 可借由回滚机制来恢复程序崩溃或非预期结束所造成的数据错误 ，修复时间基本都是固定的。
事务	不支持，每次查询具有原子性。	支持，具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全型表
AUTO_INCREMENT	MyISAM 表可以和其他字段一起建立联合索引	InnoDB 中必须包含只有该字段的索引
锁	只支持表级锁，select、update、delete、insert 语句都会给表自动加锁。	支持表锁、（默认）行锁，行锁大幅度提高了多用户并发操作的新能。但是 InnoDB 的行锁，只是在 WHERE 的主键是有效的，非主键的 WHERE 都会锁全表的。
外键	不支持	支持，对一个包含外键的 InnoDB表 转为 MYISAM 会失败。
快速读取行数	用一个变量保存了整个表的行数，执行 select count(*) from table 语句时只需要读出该变量即可，速度很快（注意不能加有任何 WHERE 条件）。	保存表的具体行数，执行 select count(*) from table 时需要全表扫描。

---

表级锁和行级锁

表级锁

• 优点：开销小，加锁快；
• 缺点：粒度大，发生冲突概率高，高容纳并发能力低，适合查询为主的业务。

行级锁

• 优点：粒度小，发生锁冲突的概率小，适用于高并发的频繁表修改，因此 InnoDB 高并发性能优于 MyISAM 。
• 缺点：加锁慢，系统消耗较大。索引不仅缓存自身，也缓存数据，因此 InnoDB 相比 MyISAM 需要更大的内存。