38 | 都说InnoDB好，那还要不要使用Memory引擎

前面group by语句都用了order by null，为什么使用内存临时表得到的语句结果里，0这个值在最后一行；而使用磁盘临时表得到的结果里0这个值在第一行？

内存表的数据组织结构

一下两张表，t1使用Memory引擎，t2使用InnoDB引擎。

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。

出现这种情况的原因，要从这两个引擎的主键索引的组织方式说起。

表t2是InnoDB引擎，主键索引组织方式是B+树。

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

 

和InnoDB不同，Memory引擎的数据和索引是分开的。表t1的数据内容：

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

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

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

• InnoDB引擎把数据放在主键索引上，其他索引上保存的是主键id，称之为“索引组织表”；
• Memory引擎采用的是把数据单独存放，索引上保存数据位置的数据组织形式，称之为“堆组织表”。

两个引擎的典型不同：

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

由于内存表的这些特性，每个数据行被删除以后，空出的这个位置都可以被接下来要插入的数据复用，比如：

另外需要说明的是，表t1这个主键索引是哈希索引，因此如果执行范围查询，比如：

select * from t1 where id<5;

是用不上索引的，需要走全表扫描。

如果要让内存表支持范围扫描，应该怎么办？

hash索引和B-Tree索引

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

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

这时，数据组织就成这了（跟InnoDB的b+树索引组织形式类似）：

作为对比，如下：

直接查询，优化器会使用B-Tree索引，所以返回结果是0在第一行；但是如果强制使用主键索引，返回结果里0就到了最后一行了。

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

但是一般不建议在生产环境使用内存表：

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

内存表的锁

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

数据持久性问题

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

一般来说这不是个大问题，但是在高可用架构下，问题就会扩大，甚至可以说是bug。

但是也要根据实际场景具体分析，不能一概而论。

 

上一篇：37 | 什么时候会使用内部临时表

下一篇：39 | 自增主键为什么不是连续的？