38 | 何时使用除了Innodb以外的Memory引擎

一、前言

这里的前言就是上篇评论，顺便准备下初始化数据～
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);

二、内容

2.1.Innodb引擎和Memory引擎区别？

• 在索引存储上：Innodb主键索引存储的是具体的数据，这种索引存储方式成为索引组织表。而 Memory 引擎采用的是把数据单独存放，索引上保存数据位置的数据组织形式，我们称之为堆组织表。

• 数据存放顺序上：Innodb数据存放总是有序的，Memory数据存放顺序由插入顺序决定

• 空洞处理上：Innodb插入数据时考虑到数据有序性会在固定位置插入，Memory有空位置就插。

• 查找效率上：Innodb引擎主键索引和普通索引查找数据方式不同，Memory查找数据主键索引和普通索引没区别

• 数据变更效率上：数据位置发生变化的时候，InnoDB 表只需要修改主键索引，而内存表需要修改所有索引

• 字段类型上：支持变长数据类型，不同记录的长度可能不同；内存表不支持 Blob 和 Text 字段，并且即使定义了 varchar(N)，实际也当作 char(N)，也就是固定长度字符串来存储。

2.2.Memory引擎范围查找时如何用上索引？

• 可以在内存表上创建b-tree索引，方式：alter table table add index column_btree_index using btree (column);

2.3.为什么不建议你在生产环境上使用内存表？

• 内存表的锁力度不同于MDL锁，是直接锁全表（堵塞所有操作包括查询）

• 数据持久化问题。在MS架构中，备库的重启或者down机会导致内存表丢失。在主备恢复时主库发来一个表更新语句，备库就会找不到对应的表结构导致主备复制停止。最重要的是，MySQL知道主库重启之后，出现主备不一致，因此在数据库重启之后，往 binlog 里面写入一行 DELETE FROM table。如果在MM架构中，备库 binlog 里的 delete 语句就会传到主库，数据莫名删除。

2.4.基于2.3我们知道了内存表的问题，那么结论是什么？有特殊情况么，为什么？

• 把普通的内存表都换成Innodb表代替

• 用户临时表除外，1）临时表不会被其他线程访问，没有并发问题。2）临时表重启后会删除，不存在清空数据问题。3）备库的临时表不会影响主库的用户线程

三、问题

3.1.这是一个问题

内容：假设刚刚接手的一个数据库上，真的发现了一个内存表。备库重启之后肯定是会导致备库的内存表数据被清空，进而导致主备同步停止。这时，最好的做法是将它修改成 InnoDB 引擎表。假设当时的业务场景暂时不允许你修改引擎，你可以加上什么自动化逻辑，来避免主备同步停止呢？

• 我们假设的是主库暂时不能修改引擎，那么就把备库的内存表引擎先都改成 InnoDB。对于每个内存表，执行（目的：这样就能避免备库重启的时候，数据丢失的问题。）
  set sql_log_bin=off;
  alter table tbl_name engine=innodb;

• 如果由于主库异常重启，触发了 HA，这时候我们之前修改过引擎的备库变成了主库。而原来的主库变成了新备库，在新备库上把所有的内存表（这时候表里没数据）都改成 InnoDB 表。