34.Memory引擎 – Heaven.Blog

首先很直观的进行看一下两者的区别

假设有两张表t1 t2,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

可以很明显的看出,内存表中,t1返回的结果0在最后一行,t2是在第一行

出现这个问题,可以看两个引擎的主键索引

InnoDB采用的是主键索引树,是B+树的数据类型,所以数据组织为

主键索引上的值是有序的,执行select * 的时候,就会按照叶子节点来进行从左到右的扫描

从InnoDB不同,Memory引擎是数据和索引分开的

内存表上的数据以一种数组的方式去存放,在主键索引中

InnoDB引擎把数据放在主键索引上,其他索引上保存的是主键id,称为索引组织表

Memeory引擎是把数据单独放,索引上保存数据位置的数据组织形式,称为堆组织表

两个引擎不同:

InnoDB表的数据是有序的,内存表示按照写入顺序存放的

数据文件有空洞的时候,InnoDB表在插入新数据的时候,为了保证数据的有序性,只能在固定的位置写入新的值,内存表找到空位置就能插入

数据位置发生变化的时候,InnoDB只需要修改主键索引,内存表需要修改所有索引

InnoDB表用主键索引查询的时候需要走一次索引查找,普通索引查询的时候,走两边,内存表没有区别,所有索引都是一次

InnoDB支持可以变长度的数据类型,但是内存表不支持,只能每行数据长度相同

内存表的特性决定了其可以在数据被删除了之后,将空出来的位置插入新的值复用

delete from t1 where id=5;

insert into t1 values(10,10);

select * from t1;

id=10出现在id=4之后,也就是原来5的位置

但是因此,在执行范围搜索的时候,无法正常使用主键索引,需要走全表扫描

为此我们需要维护一个真正的主键索引

一个B-Tree索引

当然,MySQL可以给这个内存表,创建一个B-Tree索引

SQL语句如下,alter table t1 add index a_btree_index using btree (id)

这就创建出了一个名为 a_btree_index

整体的内存表如上了

这样的话,如果对这个内存表执行

select * from t1 where id<5;

因为用上了B+树,那么就会返回

如果强行使用主键索引,还是会出现 select * from t1 force index(primary) where id<5;

这是强制使用了内存表的主键

当然,内存表快的原因还有就是Memory引擎支持hash索引,而且读写都在内存中,速度快,但是仍然不建议在生产环境中使用内存表

因为其,对于锁的粒度支持不好,影响了并发

数据的持久化做的不好

1.内存表的锁

内存表不支持行锁,只支持表锁,一个表上有更新,就需要堵住整个表

执行如下的语句

sessionA的update语句需要执行50秒,在此期间,sessionB处于锁等待的状态.所以,Memory引擎并不适合处理并发事务

数据持久化

数据放在内存中,是增加了读写速度,但是异常重启后,所有的内存表都会被清空

我们来看下内存表对应不同高可用架构下的异常问题

查看下面的执行顺序

业务正常访问主库

备库硬件升级,备库重启,内存表t1清空

备库重启后,客户端发送一个update语句,修改了表t1的数据行,导致备库找不到这个内存表

导致主备同步停止,如果客户端是连接备库进行读数据的话,会发现表t1的数据丢失了

当然MySQL预知了这种可能性,为了降低危险,会在数据库重启后,在binlog中插入一个行DELETE FROM t1;

如果是双M结构

在备库重启后,会发送一个delete语句给主库,告知主库会将内容删除,这时候就会发现,主库的内存表数据被清空了

于是我们不建议使用内存表

因为如果数据量大的情况下,InnoDB支持行锁,并发度更高

而且对于性能,由于有InnoDB Buffer Pool的存在,使用InnoDB的性能并不会差到哪里

但是,除了,某个事务内的内存临时表,在数据量可控的情况下,可以使用内存临时表

内存临时表不会被其他的线程访问,没有并发的问题

内存临时表在session断开后就需要删除了

备库的临时表不会影响到主库的用户线程

也可以那之前的join语句来表示Memory的优化,创建一个Memory的临时表

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);

改写为:

create temporyary 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);

这里使用了内存引擎,是因为

InnoDB比起来,内存表插入数据更快

内存表维护了一个hash索引,比B+树的查找更快

临时表的数据只有2000行,占用内存有限

如果主备系统中存在着内存表,怎么避免内存表丢数据

如果主库不能修改,那么可以考虑在备库上修改为InnoDB,对于每个内存表,都设置为set sql_log_bin=off

alter table tbl_name engine=innodb

发表评论 取消回复

发表评论取消回复