一文了解MySql存储引擎

一、前言

这里只讲解常见的InnoDB和MyISAM存储引擎，其他存储引擎，例如Memory、Merge、Archive、Federated、CSV、BLACKHOLE此处不涉及。

二、两者区别

为了读者能够带着疑问去阅读本文章，从InnoDB和MyISAM两者的区别（优缺点）出发，一步一步分析两者在区别上造成差异的原因，从而使读者能够更容易牢记存储引擎的知识点。

2.1 MyISAM

• 优点：

  • 占用空间小
  • 访问速度块（适合大量查询和插入操作）
  • 此处不讲：可以配合锁，实现操作系统下的复制备份， 数据紧凑存储

• 缺点

  • 不支持事务（无法满足完整性和并发性）
  • 表级锁（并发性差）
  • 数据库崩溃之后无法安全恢复（没有事务版本日志文件回滚——无多版本并发控制）
  • 不支持外键

2.2 InnoDB

• 优点：

  • 支持事务（四种隔离级别满足不同的场景需求）
  • 数据库崩溃之后可以恢复（具有事务版本日志文件——多版本并发控制前提)
  • 有自带的查询数据缓冲区
  • 支持外键

• 缺点

  • 读写的效率比较差
  • 占用空间比较大

2.3 小结

从事务、外键、日志文件角度分析，MyISAM能够做到查询插入快， 占用空间小（InnoDB相反）。下面从优缺点提炼两者存储引擎设计上的不同。

三、事务

3.1 基本概念

数据库的事务（Transaction） 是一种机制、一个操作序列，包含了一组数据库操作命令。事务把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库命令要么都执行，要么都不执行，因此事务是一个不可分割的工作逻辑单元。

事务具有以下特性：

• 原子性

事务是一个完整的操作。事务的各元素是不可分的（原子的）。事务中的所有元素必须作为一个整体提交或回滚。如果事务中的任何元素失败，则整个事务将失 败。

• 一致性

当事务完成时，数据必须处于一致状态。例如银行的钱，不管有多个事务在进行更改操作，总数是不变的。

• 隔离性

对数据进行修改的所有并发事务是彼此隔离的，这表明事务必须是独立的，它不应以任何方式依赖于或影响其他事务

• 持久性

事务的持久性指不管系统是否发生了故障，事务处理的结果都是永久的。

3.2 隔离级别

场景：当有两个事务A, B在同时进行的时候。

• 读未提交

A进行了多次的select操作，可能会读到B在提交前、提交后或者回滚的数据，造成脏读，幻读，两者暗示着数据不可重复读。性能比好不了多少，一般不用。

• 读已提交

解决脏读的情况，虽然A事务读取的时候不会读到B事务处理期间的数据，也就是说，A读到一条数据（B=1）,那么在A的事务中，B永远等于1。但是由于插入操作不在读已提交的 范围，所以A可能读到两条数据，出现幻读。企业常用隔离级别。

• 可重复读

解决幻读的情况，通过多版本并发控制实现（不用加锁，性能较好）。InnoDB默级别。

• 可串行化

解决幻读的情况，强制事务排序（性能较差）。

3.3 多版本并发控制（MVCC）

InnoDB采用乐观锁的方式实现，解决一致性读的问题。此处的一致性，指的是当前读只读取当前事务修改数据或者当前事务之前的数据。

多版本的实现原理是，InnoDB在每行数据隐式的添加了三个字段，分别是：

• 隐藏行id

当没有自定义主键时，默认将该隐藏id建立聚簇索引，建立索引结构（这里为什么说建立结构，可以查看四、索引结构。

• 事务id

记录的是事务的版本信息，有序，保证一致性读的时候在当前事务之前。（对比当前事务版本id与最新事务版本id的大小）。

• 回滚指针

指向Undo log的行日志，也就是每行数据的历史数据（结合版本事务id，查找不同事务的数据），也是灾难性恢复的来源。

图片来源： MySQL 的多版本并发控制 (MVCC)

四、索引结构

此处只讲解B+树的索引结构，不设计哈希索引和全文索引。B+树是一种多路平衡查找树，只有在叶子节点才存储对应数据，并且叶子节点使用链表的方式链接起来（方便用于范围查找）。由于InnoDB和MyISAM都可以使用B+树，但是具体实现方面二者都有不同，首先从两者存储的物理文件讲起。

4.1 物理存储

MyISAM总共有三个物理文件存储数据库表信息，一个文件存储表数据定义，一个文件存储数据，另一个文件存储索引信息。而InnoDB也有三个文件，一个存储表数据信息，两外两个存储日志信息（重做日志文件——redo log，注意和前面讲到的undo log作区分，前者是事务前的，后者是事务后的）。所以两个存储引擎的区别在于，MyISAM用三个文件存储表、数据和索引信息，InnoDB用一个文件存储表、数据和索引信息。这是如何做到的呢？

4.1 叶子节点差异

MyISAM存储引擎B+树中的叶子节点存储的仅仅是指向表数据的物理地址，而InnoDB存储的是表数据（这里得看是聚簇索引还是普通索引，看下一节内容），所以在相同表结构前提下，理论上来说，前者的B+树会比后者的小。

4.2 聚簇索引和普通索引

带着上一个小结的疑问，什么是聚簇索引，什么是非聚簇索引（普通索引）。详细可看这篇文章——【MySQL】聚集索引和非聚集索引

聚簇索引通常以唯一主键来创建（可以不唯一，不建议），叶子节点存储的是该数据整行字段的信息（在物理存储上连续）。非聚簇索引只存储的聚簇索引列（通常是主键）和索引列数据，所以mysql主键不推荐使用复杂的序列，而推荐使用自增整型的原因。

总的来说，它俩的本质区别在于是表记录的排列顺序和与索引的排列顺序是否一致。

五、总结

以上便是关于存储引擎的全部内容，如有错误，欢迎指正！