1 案例

数据准备

create table t1(id int primary key, c int) engine=Memory;
create table t2(id int primary key, c int) engine=innodb;
insert into t1 values(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(0,0);
insert into t2 values(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(0,0);

数据查询

可以看到，内存表 t1 的返回结果里面 0 在最后一行，而 InnoDB 表 t2 的返回结果里 0 在第一行。出现这个区别的原因，主要是这两个引擎的主键索引的组织方式不同引起的。

2 索引组织方式

2.1 InnoDB

InnoDB 表的数据就放在主键索引树上，主键索引是 B+ 树。主键索引上的值是有序存储的。在执行 select * 的时候，就会按照叶子节点从左到右扫描，所以得到的结果里，0 就出现在第一行。

2.2 Memory

Memory 引擎的数据和索引是分开的。可以看到，内存表的数据部分以数组的方式单独存放，而主键 id 索引里，存的是每个数据的位置。主键 id 是 hash 索引，可以看到索引上的 key 并不是有序的。在内存表 t1 中，当我执行 select * 的时候，走的是全表扫描，也就是顺序扫描这个数组。因此，0 就是最后一个被读到，并放入结果集的数据。

2.3 异同点

InnoDB 和 Memory 引擎的数据组织方式是不同的：

InnoDB 引擎把数据放在主键索引上，其他索引上保存的是主键 id。这种方式，我们称之为索引组织表（Index Organizied Table）。
Memory 引擎采用的是把数据单独存放，索引上保存数据位置的数据组织形式，我们称之为堆组织表（Heap Organizied Table）。

3 范围查询

内存表的主键索引是哈希索引，因此如果执行范围查询是用不上主键索引的，需要走全表扫描。那如果要让内存表支持范围扫描，应该怎么办呢？实际上，内存表也是支持 B-Tree 索引（B+树）的。在 id 列上创建一个 B-Tree 索引，SQL 语句可以这么写：

alter table t1 add index a_btree_index using btree (id);

索引树如下：

内存表的优势是速度快，其中的一个原因就是 Memory 引擎支持 hash 索引。当然，更重要的原因是，内存表的所有数据都保存在内存，而内存的读写速度总是比磁盘快。但是，为什么我不建议你在生产环境上使用内存表。这里的原因主要包括两个方面：

锁粒度问题；
数据持久化问题。

3.1 锁力粒度

内存表不支持行锁，只支持表锁。因此，一张表只要有更新，就会堵住其他所有在这个表上的读写操作。

3.2 数据持久化

3.2.1 M-S

我们来看一下下面这个时序：

业务正常访问主库；
备库硬件升级，备库重启，内存表 t1 内容被清空；
备库重启后，客户端发送一条 update 语句，修改表 t1 的数据行，这时备库应用线程就会报错“找不到要更新的行”。
当然，如果这时候发生主备切换的话，客户端会看到，表 t1 的数据“丢失”了。

3.2.2 M-M

由于 MySQL 知道重启之后，内存表的数据会丢失。所以，担心主库重启之后，出现主备不一致，MySQL 在实现上做了这样一件事儿：在数据库重启之后，往 binlog 里面写入一行 DELETE FROM t1。在备库重启的时候，备库 binlog 里的 delete 语句就会传到主库，然后把主库内存表的内容删除。这样你在使用的时候就会发现，主库的内存表数据突然被清空了。

4 异同点

InnoDB 表的数据总是有序存放的，而内存表的数据就是按照写入顺序存放的；
当数据文件有空洞的时候，InnoDB 表在插入新数据的时候，为了保证数据有序性，只能在固定的位置写入新值，而内存表找到空位就可以插入新值；
数据位置发生变化的时候，InnoDB 表只需要修改主键索引，而内存表需要修改所有索引；
InnoDB 表用主键索引查询时需要走一次索引查找，用普通索引查询的时候，需要走两次索引查找。而内存表没有这个区别，所有索引的“地位”都是相同的。I
innoDB 支持变长数据类型，不同记录的长度可能不同；内存表不支持 Blob 和 Text 字段，并且即使定义了 varchar(N)，实际也当作 char(N)，也就是固定长度字符串来存储，因此内存表的每行数据长度相同。（mysql支持的常见变长数据类型：varchar、text、blob）

5 页分裂/合并

5.1 文件结构

假设你已经装好了MySQL最新的5.7版本，并且你创建了一个windmills库（schema）和wmills表。在文件目录（通常是/var/lib/mysql/）你会看到以下内容：

data/
  windmills/
      wmills.ibd
      wmills.frm

wmills.ibd的文件。这个文件由多个段（segments）组成，每个段和一个索引相关。文件的结构是不会随着数据行的删除而变化的，但段则会跟着构成它的更小一级单位——区的变化而变化。区仅存在于段内，并且每个区都是固定的1MB大小（页体积默认的情况下）。页则是区的下一级构成单位，默认体积为16KB。
wmills.frm MySQL表的定义文件，它包含了表的结构信息，例如表的列名、数据类型、索引等信息。

5.2 数据页结构

5.3 数据区

在MySQL的设定中，同一个表空间内的一组连续的数据页为一个extent（区），默认区的大小为1MB，页的大小为16KB。16*64=1024，也就是说一个区里面会有64个连续的数据页。连续的256个数据区为一组数据区。

5.4 页分裂

当你删了一行记录时，实际上记录并没有被物理删除，记录被标记（flaged）为删除并且它的空间变得允许被其他记录声明使用。

当页中删除的记录达到MERGE_THRESHOLD（默认页体积的50%），InnoDB会开始寻找最靠近的页（前或后）看看是否可以将两个页合并以优化空间使用。

5.5 页合并

页可能填充至100%，在页填满了之后，下一页会继续接管新的记录。如果下一页也满了，需要出发页分裂。InnoDB的做法是（简化版）：

创建新页
判断当前页（页#10）可以从哪里进行分裂（记录行层面）
移动记录行
重新定义页之间的关系

性能隐患

要记住在合并和分裂的过程，InnoDB会在索引树上加写锁（x-latch）。在操作频繁的系统中这可能会是个隐患。它可能会导致索引的锁争用（index latch contention）

【MySQL】存储引擎