内存表Memory

内存表的数据组织结构

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

InnoDB引擎把数据放在主键索引树上，其他索引上保存的是主键id。称为索引组织表(Index Organizied Table)。
Memory引擎采用的是把数据以数组的方式单独存放，索引上保存数据位置的数据组织形式。称为堆组织表(Heap Organizied Table)。

其他不同：

InnoDB表的数据总是有序存放的，而内存表的数据是按照写入顺序存放的；(内存表的主键索引是哈希索引，如果执行范围查询，是用不上主键索引的，需要走全表扫描)
当数据文件有空洞的时候，InnoDB表在插入新数据的时候，为了保证数据有序性，只能在固定的位置写入新值，而内存表找到空位就可以插入新值；(内存表，每个数据行被删除以后，空出的这个位置都可以被接下来要插入的数据复用)
数据位置发生变化的时候，InnoDB表只需要修改主键索引，而内存表需要修改所有索引；
InnoDB表用主键索引查询时需要走一次索引查找，用普通索引查询的时候，需要走两次索引查找。而内存表没有这个区别，所有索引的“地位”都是相同的；
InnoDB支持变长数据累心，不同记录的长度可能不同；内存表不支持Blob和Text字段，并且即使定义了varchar(N)，实际也当作char(N)，也就是固定长度字符串来存储，因此内存表的每行数据长度相同。

hash索引和B-Tree索引

内存表也是支持B-tree索引的。跟InnoDB的b+树索引组织形式类似。在id列上创建一个B-Tree索引：

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

执行select * from t1 where id<5的时候，优化器会选择B-Tree索引，返回结果是0到4。使用force index强行使用主键id索引select * from t1 force index(primary) where id<5，id=0这一行就不一定在结果集的第一个了(与插入顺序有关)。

内存表的优势是速度快，其中一个原因是Memory引擎支持hash索引；更重要的原因是，内存表的所有数据都保存在内存，内存的读写速度总是比磁盘块。

由于1锁粒度问题；2数据持久化问题。不建议在生产环境上使用内存表。

内存表的锁

内存表不支持行锁，只支持表锁。一张表只要有更新，就会堵住其他所有在这个表上的读写操作。这里的表锁与MDL锁不同，但都是表级的锁。

sessionA	sessionB	sessionC
update t1 set id=sleep(50) where id=1;
	select * from t1 where id=2; (wait 50s)
		show processlist;

在这个执行序列里，sessionA的update语句要执行50秒，在这个语句执行期间sessionB的查询会进入锁等待状态。

id	user	host	db	command	time	state	info
1	root	localhost:xxxxx	test	query	3	user sleep	update t1 set id=sleep(50) where id=1
2	root	localhost:yyyyy	test	query	1	waiting for table level lock	select * from t1 where id=2
3	root	localhost:zzzzz	test	query	0	starting	show processlist

跟行锁比起来，表锁对并发访问的支持不够好。内存表的锁粒度问题，决定了它在处理并发事务的时候，性能也不会好。

数据持久性问题

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

M-S架构

业务正常访问主库；
备库硬件升级，备库重启，内存表t1内容被清空；
备库重启后，客户端发送一条update语句，修改表t1的数据行，备库的应用线程报错“找不到要更新的行”。

导致主备同步停止。这时发生主备切换的话，客户端会看到，表t1的数据“丢失”了。在右proxy的架构里，默认主备切换的逻辑是由数据库系统自己维护的，对客户端来说，就是“网络断开，重连之后，发现内存表数据丢失了”。

由于MySQL直到重启之后，内存表的数据会丢失。担心主库重启之后，出现主备不一致，MySQL在实现上：在数据库重启之后，往binlog里面写入一行delete from t1。

双M结构

在备库重启的时候，备库binlog里的delete语句就会传到主库，然后把主库内存表的内容删除。客户端在使用的时候发现，主库的内存表数据突然被清空了。

Memory与InnoDB选择

内存表并不适合在生产环境上作为普通数据表使用。普通内存表都用InnoDB表来代替：

如果表更新量很大，那么并发度是一个很重要的参考指标，InnoDB支持行锁，并发度比内存表好；
能放到内存表的数据量都不大。如果考虑的是读的性能，一个QPS很高并且数据量不大的表，即使是使用InnoDB，数据也是都会缓存在InnoDB Buffer Pool里的，使用InnoDB表的读性能也不会差。

在数据量可控，不会耗费过多内存的情况下，可以考虑使用内存临时表。MySQL-用户临时表简析

内存临时表可以无视内存表的两个不足：

临时表不会被其他线程访问，没有并发性的问题；
临时表重启后也是需要删除的，清空数据这个问题不存在；
备库的临时表也不会影响主库的用户线程。

示例：MySQL-join语句优化

1
2
3

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

相比于InnoDB表，使用内存表不需要写磁盘，往表temp_t里写数据的速度更快；
索引b使用hash索引，查找的速度比B-Tree索引快；
临时表数据只有2000行，占用的内存有限。

-- 将临时表temp_t改成内存临时表，并且在字段b上创建一个hash索引。(如果是范围查找，依然需要创建b-tree索引)  
create temporary 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);  
-- 不论是导入数据的时间，还是执行join的时间，使用内存Memory临时表的速度都比使用InnoDB临时表要更快些。