数据库极客时间-mysql存储引擎
Source
Edit
History

InnoDB 和 Memory 存储引擎的区别

InnoDB 存储引擎的数据表会放在主键索引树上，主键索引是 B+ 树。在执行 select * from table 时，会按照叶子节点从左到右扫描，因此扫描出来的数据按照主键的排列是有序的。

Memory 存储引擎的数据和索引是分开的，内存表的数据部分是以数组的方式单独存放，而主键 id 索引中，存的是每个数据的位置。主键 id 是 hash 索引，索引上的 key 并不是有序的。

因此，我们可知，我们罗列一下这两种存储引擎的区别：

  • InnoDB 和 Memory 存储引擎的数据组织方式不同。InnoDB 引擎把数据放在主键索引上，其他索引上保存的是主键 id。这种方式，也称为 “索引组织表”。Memory 引擎采用的是把数据单独存放，索引上保存数据位置的数据组织形式。也称为 “堆组织表”。
  • InnoDB 表的数据总是有序存放的，而 Memory 表的数据就是按照写入顺序存放的
  • 当数据文件有空洞时，InnoDB 表在插入新数据的时候，为了保证数据有序性，只能在固定的位置写入新值。而 Memory 表找到空位就可以插入新值
  • 数据位置发生变化的时候，InnoDB 表只需要修改主键索引，其他索引都是通过回表的方式用主键索引定位数据的。而 Memory 表需要修改该表的每个索引，不同的索引都是独立的。
  • InnoDB 表用主键索引查询时需要走一次索引查找；用普通索引查询时需要走两次索引查找。而内存表没有这个区别，所有索引都是独立的，“地位”都是相同的。
  • InnoDB 支持变长数据类型，不同记录的长度可能不同；Memory 表不支持 Blob 和 Text 字段，并且即使定义了 varchar(N)，实际上也会被当作 char(N)，也就是固定长度字符串来存储，因此内存表的每行数据长度相同。
  • 在进行范围查询时，Memory 表是用不上哈希索引的，需要走全表扫描。而 InnoDB 表采用的 B+ 树索引结构则可以很好的进行范围查询

让 Memory 表支持 B+ 树索引。比如在表中的某一列创建一个 B+ 树索引。如下 SQL 语句

1
alter table t add index btree_index using btree(id);

那么，此时表的数据组织形式就变成了如下：

可以看到，Memory 表会增加一个 B+ 树索引。因此可以给此表带来 B+ 树索引的优点，比如上范围查询等。

一、Memory 表的问题

不建议在生产环境使用 Memory 表，虽然 Memory 表的数据都保存在内存中，读写速度总比磁盘快。但是由于锁粒度问题、数据持久化问题，导致效果并没有那么好。

1. Memory 表的锁

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

和行锁比起来，表锁对并发访问的支持不够好。所以，Memory 表的锁粒度问题，决定了他在处理并发事务时，性能也不会太好。

2. 数据持久性问题

数据放在内存中，当数据库重启时，所有的 Memory 表都会被清空。在高可用架构中，Memory 表被清空这个问题很严重。如下：

  • 在M-S架构中，使用 Memory 表存在的问题：

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

  • 在双 M 架构中，使用 Memory 表存在的问题：

由于 Mysql 知道重启后，Memory 表的数据会丢失，所以担心主库重启之后，出现主备不一致，MySQL 会在数据库重启后，往 binlog 中写入一行 delete from table 语句。在备库重启时，备库 binlog 中的 delete 语句就会传到主库，然后把主库内存表的内容删除。这样从客户端的角度来看，主库的内存表数据会出现突然被清空的现象。

3. 性能再谈

如上，我们发现 Memory 表在生产环境不能用的原因。那么我们再来谈谈 Memory 表和 InnoDB 表的执行效率对比：

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

4. 小结

Memory 存储引擎，由于重启会丢数据，如果一个备库重启，会导致主备同步线程停止；如果主库和这个备库是“双主” 架构，还可能导致主库的内存表数据被删除。因此在生产环境不建议使用 Memory 表。