内存表的数据组织结构
建表:
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 -- Memory引擎;
SELECT * FROM t2 ; -- Innodb引擎
结果:
可以看到,内存表 t1 的返回结果里面 0 在最后一行,而 InnoDB 表 t2 的返回结果里 0 在第一行。
表t2使用都InnoDB 引擎,InnoDB 表的数据就放在主键索引树上,主键索引是 B+ 树,数据组织方式如下:
主键索引上的值是有序存储的。在执行 select * 的时候,就会按照叶子节点从左到右扫描,所以得到的结果里,0 就出现在第一行。
与 InnoDB 引擎不同,Memory 引擎的数据和索引是分开的,表t1的数据组织(图3):
内存表的数据部分以数组的方式单独存放,而主键 id 索引里,存的是每个数据的位置。主键 id 是 hash 索引,可以看到索引上的 key 并不是有序的。
在内存表 t1 中,执行 select * 的时候,走的是全表扫描,也就是顺序扫描这个数组。因此,0 就是最后一个被读到,并放入结果集的数据。
可见,InnoDB 和 Memory 引擎的数据组织方式是不同的:
- InnoDB 引擎把数据放在主键索引上,其他索引上保存的是主键 id。这种方式,我们称之为索引组织表(Index Organizied Table)。
- 而 Memory 引擎采用的是把数据单独存放,索引上保存数据位置的数据组织形式,我们称之为堆组织表(Heap Organizied Table)。
从中我们可以看出,这两个引擎的一些典型不同:
- InnoDB 表的数据总是有序存放的,而内存表的数据就是按照写入顺序存放的;.
- 当数据文件有空洞的时候,InnoDB 表在插入新数据的时候,为了保证数据有序性,只能在固定的位置写入新值,而内存表找到空位就可以插入新值;
- 数据位置发生变化的时候,InnoDB 表只需要修改主键索引,而内存表需要修改所有索引;
- InnoDB 表用主键索引查询时需要走一次索引查找,用普通索引查询的时候,需要走两次索引查找。而内存表没有这个区别,所有索引的“地位”都是相同的。
- InnoDB 支持变长数据类型,不同记录的长度可能不同;内存表不支持 Blob 和 Text 字段,并且即使定义了 varchar(N),实际也当作 char(N),也就是固定长度字符串来存储,因此内存表的每行数据长度相同。
由于内存表的这些特性,每个数据行被删除以后,空出的这个位置都可以被接下来要插入的数据复用。比如,如果要在表 t1 中执行:
delete from t1 where id=5;
insert into t1 values(10,10);
select * from t1;
就会看到返回结果里,id=10 这一行出现在 id=4 之后,也就是原来 id=5 这行数据的位置。需要指出的是,表 t1 的这个主键索引是哈希索引,因此如果执行范围查询,比如
select * from t1 where id<5;
是用不上主键索引的,需要走全表扫描,explain如下:
innodb的t2是这样的:
EXPLAIN select * from t2 where id<5;
hash 索引和 B-Tree 索引
实际上,内存表也是支 B-Tree 索引的。在 id 列上创建一个 B-Tree 索引,SQL 语句可以这么写:
alter table t1 add index a_btree_index using btree (id);
此时表t1的数据组织形式就变成:
使用 B-Tree 和 hash 索引查询返回结果对比:
SELECT * FROM t1 WHERE id <5;
SELECT * FROM t1 FORCE INDEX(PRIMARY) WHERE id <5;
可以看到,执行 select * from t1 where id<5
的时候,优化器会选择 B-Tree 索引,所以返回结果是 0 到 4。 使用 force index 强行使用主键 id 这个索引,id=0 这一行就在结果集的最末尾了,数据组织就是上面的图3。
内存表的优势是速度快,其中的一个原因就是 Memory 引擎支持 hash 索引。当然,更重要的原因是,内存表的所有数据都保存在内存,而内存的读写速度总是比磁盘快。
内存表的缺点
内存表的锁
内存表不支持行锁,只支持表锁。因此,一张表只要有更新,就会堵住其他所有在这个表上的读写操作。
内存表的表锁复现步骤:
在这个执行序列里,sessionA都update语句要执行50秒,在这个语句执行期间sessionB的查询会进入锁等待状态,sessionC的show processlist 如下:
跟行锁比起来,表锁对并发访问的支持不够好。所以,内存表的锁粒度问题,决定了它在处理并发事务的时候,性能也不会太好。
数据持久性问题
数据放在内存中,是内存表的优势,但也是一个劣势。因为,数据库重启的时候,所有的内存表都会被清空。
M-S 基本架构:
有业务时序:
- 业务正常访问主库;
- 备库硬件升级,备库重启,内存表 t1 内容被清空;
- 备库重启后,客户端发送一条 update 语句,修改表 t1 的数据行,这时备库应用线程就会报错“找不到要更新的行”。
这样就会导致主备同步停止。当然,如果这时候发生主备切换的话,客户端会看到,表 t1 的数据“丢失”了。
由于 MySQL 知道重启之后,内存表的数据会丢失。所以,担心主库重启之后,出现主备不一致,MySQL 在实现上做了这样一件事儿:在数据库重启之后,往 binlog 里面写入一行 DELETE FROM t1。
双 M 结构:
在备库重启的时候,备库 binlog 里的 delete 语句就会传到主库,然后把主库内存表的内容删除。这样你在使用的时候就会发现,主库的内存表数据突然被清空了。
小结
-
由于重启会丢数据,如果一个备库重启,会导致主备同步线程停止;如果主库跟这个备库是双 M 架构,还可能导致主库的内存表数据被删掉。
-
能放到内存表的数据量都不大。如果你考虑的是读的性能,一个读 QPS 很高并且数据量不大的表,即使是使用 InnoDB,数据也是都会缓存在 InnoDB Buffer Pool 里的。因此,使用 InnoDB 表的读性能也不会差。建议你把普通内存表都用 InnoDB 表来代替。