MySQL---7.Memory引擎

目录

一、内存表的数据组织结构

二、hash索引和B-Tree索引

三、内存表的优缺点

---

 

一、内存表的数据组织结构

例子：表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放在最后一行，而innodb表t2查询结果中0放在第一行

原因：innodb表的数据放在主键索引树上，主键索引是B+树，当select * 时会按照叶子节点从左到右扫描，所以0在第一个；

而Memory引擎的数据和索引是分开的，内存表中的数据是以数组方式单独存放的，而主键id索引里，存放的是每个数据的位置。

主键id是hash索引，可以看到索引上的key并不是有序的，当执行select * 时，走的是全表扫描，也就是顺序扫描这个数组，因此0是最后一个被读到。

总的来说，Innodb和Memory引擎有一些典型的不同：

①innodb表的数据总是有序存放的，而内存表中的数据是按照写入顺序存放的

②当数据文件有空洞的时候，innodb表在插入新数据的时候，为了保证数据有序性，只能在固定位置上写入新值，而内存表找到空位就可以插入新值

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

④innodb表用主键索引查询时需要走一次索引查找，用普通索引查找时，需要走两次索引查找；而内存表所有索引的地位都是相同的

⑤innodb支持变长数据类型，不同记录的长度可能不同，内存表不支持Blob和Text字段，及时定义了varchar(N)，实际也当做char(N)，也就是固定长度字符串来存储，

因此内存表的每行数据长度相同

 

二、hash索引和B-Tree索引

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

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

此时，表t1的数据组织形式就变成了这样：

对比下面两个语句的输出：

当执行select * from t1 where id<5 的时候，执行器会选择B-Tree索引，所以返回结果是0到4

当使用force index强行使用主键id这个索引，id=0这一行就在结果集的最后

 

三、内存表的优缺点

1.内存表的优点

①速度快，原因是Memory引擎支持hash索引、所有数据都保存在内存

2.内存临时表的优点

六、临时表

①内存临时表不会被其他线程访问，普通内存表存在并发问题

②内存临时表重启后会被直接删除，清空数据但是空间还占用的问题不存在

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

④先比较其他引擎的临时表，完全基于内存，速度很快，且使用hash索引，查找速度快

3.内存表的缺点

①锁

内存表不支持行锁，只支持表锁。因此，一张表只要有更新，就会阻塞其他对这个表的读写操作

②持久化

数据库重启，所有内存表都会被清空

a）M-S

当备库重启时，内存表t1内存被清空，主从同步语句时，会提示找不到内存表t1

当数据库重启后，MySQL为了避免主备不一致，会往binlog里面写入一行 delete from t1;

所以当在下面这种双M的结构下，一个MySQL重启，会导致另一个MySQL删除内存表t1