Boo's Blog

Stay foolish, Stay hungry

本文是基于 极客时间——MySQL 实战 45 讲 整理的学习笔记,仅供学习参考,请勿用于商业用途,如若侵权,请联系并删除。

课程重点:

  • 了解 InnoDB 与 Memory 引擎的区别
  • 了解 Memory 引擎的优缺点

内存表的数据组织结构

为了便于分析,我来把这个问题简化一下,假设有以下的两张表 t1 和 t2,其中表 t1 使用 Memory 引擎, 表 t2 使用 InnoDB 引擎。

1
2
3
4
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);

然后,分别执行 select * from t1 和 select * from t2

可以看到,内存表 t1 的返回结果里面 0 在最后一行,而 InnoDB 表 t2 的返回结果里 0 在第一行。

出现这个区别的原因,要从这两个引擎的主键索引的组织方式说起。

表 t2 用的是 InnoDB 引擎,它的主键索引 id 的组织方式,你已经很熟悉了:InnoDB 表的数据就放在主键索引树上,主键索引是 B+ 树。所以表 t2 的数据组织方式如下图所示:

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

与 InnoDB 引擎不同,Memory 引擎的数据和索引是分开的。再来看一下表 t1 中的数据内容。


可以看到,内存表的数据部分以数组的方式单独存放,而主键 id 索引里,存的是每个数据的位置。主键 id 是 hash 索引,可以看到索引上的 key 并不是有序的。

在内存表 t1 中,当我执行 select * 的时候,走的是全表扫描,也就是顺序扫描这个数组。因此,0 就是最后一个被读到,并放入结果集的数据。

可见,InnoDB 和 Memory 引擎的数据组织方式是不同的:

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

从中我们可以看出,这两个引擎的一些典型不同:

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

由于内存表的这些特性,每个数据行被删除以后,空出的这个位置都可以被接下来要插入的数据复用。比如,如果要在表 t1 中执行:

1
2
3
delete from t1 where id=5;
insert into t1 values(10,10);
select * from t1;

就会看到返回结果里,id=10 这一行出现在 id=4 之后,也就是原来 id=5 这行数据的位置。

需要指出的是,表 t1 的这个主键索引是哈希索引,因此如果执行范围查询,比如:

1
select * from t1 where id<5;

是用不上主键索引的,需要走全表扫描。你可以借此再回顾下第 4 篇文章的内容。那如果要让内存表支持范围扫描,应该怎么办呢 ?

hash 索引和 B-Tree 索引

实际上,内存表也是支 B-Tree 索引的。在 id 列上创建一个 B-Tree 索引,SQL 语句可以这么写:

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

这时,表 t1 的数据组织形式就变成了这样:


新增的这个 B-Tree 索引你看着就眼熟了,这跟 InnoDB 的 b+ 树索引组织形式类似。

作为对比,你可以看一下这下面这两个语句的输出:

可以看到,执行 select * from t1 where id<5 的时候,优化器会选择 B-Tree 索引,所以返回结果是 0 到 4。 使用 force index 强行使用主键 id 这个索引,id=0 这一行就在结果集的最末尾了。

其实,一般在我们的印象中,内存表的优势是速度快,其中的一个原因就是 Memory 引擎支持 hash 索引。当然,更重要的原因是,内存表的所有数据都保存在内存,而内存的读写速度总是比磁盘快。

但是,接下来我要跟你说明,为什么我不建议你在生产环境上使用内存表。这里的原因主要包括两个方面:

  1. 锁粒度问题;
  2. 数据持久化问题。

内存表的锁

我们先来说说内存表的锁粒度问题。

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

需要注意的是,这里的表锁跟之前我们介绍过的 MDL 锁不同,但都是表级的锁。接下来,我通过下面这个场景,跟你模拟一下内存表的表级锁。

在这个执行序列里,session A 的 update 语句要执行 50 秒,在这个语句执行期间 session B 的查询会进入锁等待状态。session C 的 show processlist 结果输出如下:

跟行锁比起来,表锁对并发访问的支持不够好。所以,内存表的锁粒度问题,决定了它在处理并发事务的时候,性能也不会太好。

数据持久性问题

接下来,我们再看看数据持久性的问题。

数据放在内存中,是内存表的优势,但也是一个劣势。因为,数据库重启的时候,所有的内存表都会被清空。

内存表并不适合在生产环境上作为普通数据表使用。

有同学会说,但是内存表执行速度快呀。这个问题,其实你可以这么分析:

  1. 如果你的表更新量大,那么并发度是一个很重要的参考指标,InnoDB 支持行锁,并发度比内存表好;
  2. 能放到内存表的数据量都不大。如果你考虑的是读的性能,一个读 QPS 很高并且数据量不大的表,即使是使用 InnoDB,数据也是都会缓存在 InnoDB Buffer Pool 里的。因此,使用 InnoDB 表的读性能也不会差。

所以,建议把普通内存表都用 InnoDB 表来代替。但是,有一个场景却是例外的。

这个场景就是,我们在第 35 和 36 篇说到的用户临时表。在数据量可控,不会耗费过多内存的情况下,你可以考虑使用内存表。

内存临时表刚好可以无视内存表的两个不足,主要是下面的三个原因:

  1. 临时表不会被其他线程访问,没有并发性的问题;
  2. 临时表重启后也是需要删除的,清空数据这个问题不存在;
  3. 备库的临时表也不会影响主库的用户线程。

现在,我们回过头再看一下第 35 篇 join 语句优化的例子,当时我建议的是创建一个 InnoDB 临时表,使用的语句序列是:

1
2
3
create temporary table temp_t(id int primary key, a int, b int, index(b))engine=innodb;
insert into temp_t select * from t2 where b>=1 and b<=2000;
select * from t1 join temp_t on (t1.b=temp_t.b);

了解了内存表的特性,你就知道了, 其实这里使用内存临时表的效果更好,原因有三个:

  1. 相比于 InnoDB 表,使用内存表不需要写磁盘,往表 temp_t 的写数据的速度更快;
  2. 索引 b 使用 hash 索引,查找的速度比 B-Tree 索引快;
  3. 临时表数据只有 2000 行,占用的内存有限。

因此,你可以对第 35 篇文章的语句序列做一个改写,将临时表 t1 改成内存临时表,并且在字段 b 上创建一个 hash 索引。

1
2
3
create temporary table temp_t(id int primary key, a int, b int, index (b))engine=memory;
insert into temp_t select * from t2 where b>=1 and b<=2000;
select * from t1 join temp_t on (t1.b=temp_t.b);

总结

  • 在生产上,我不建议你使用普通内存表
  • 而内存表由于不支持行锁(只支持表锁),更新语句会阻塞查询

评论