网站建设的词,阳山做网站,网站管理怎么做,办网Memory存储引擎
Hi#xff0c;我是阿昌#xff0c;今天学习记录的是关于Memory存储引擎的内容。
两个 group by 语句都用了 order by null#xff0c;为什么使用内存临时表得到的语句结果里#xff0c;0 这个值在最后一行#xff1b;
而使用磁盘临时表得到的结果里我是阿昌今天学习记录的是关于Memory存储引擎的内容。
两个 group by 语句都用了 order by null为什么使用内存临时表得到的语句结果里0 这个值在最后一行
而使用磁盘临时表得到的结果里0 这个值在第一行 一、内存表的数据组织结构
假设有以下的两张表 t1 和 t2其中表 t1 使用 Memory 引擎 表 t2 使用 InnoDB 引擎。
create table t1(id int primary key, c int) engineMemory;
create table t2(id int primary key, c int) engineinnodb;
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 在第一行。
出现这个区别的原因要从这两个引擎的主键索引的组织方式说起。
表 t2 用的是 InnoDB 引擎它的主键索引 id 的组织方式
InnoDB 表的数据就放在主键索引树上主键索引是 B 树。
所以表 t2 的数据组织方式如下图所示 主键索引上的值是有序存储的。在执行 select * 的时候就会按照叶子节点从左到右扫描所以得到的结果里0 就出现在第一行。 与 InnoDB 引擎不同Memory 引擎的数据和索引是分开的。
来看一下表 t1 中的数据内容。 可以看到内存表的数据部分以数组的方式单独存放而主键 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 id5;
insert into t1 values(10,10);
select * from t1;就会看到返回结果里id10 这一行出现在 id4 之后也就是原来 id5 这行数据的位置。
需要指出的是表 t1 的这个主键索引是哈希索引因此如果执行范围查询比如
select * from t1 where id5;是用不上主键索引的需要走全表扫描。 二、hash 索引和 B-Tree 索引
那如果要让内存表支持范围扫描应该怎么办呢
实际上内存表也是支持 B-Tree 索引的。在 id 列上创建一个 B-Tree 索引SQL 语句可以这么写
alter table t1 add index a_btree_index using btree (id);这时表 t1 的数据组织形式就变成了这样 这跟 InnoDB 的 b 树索引组织形式类似。
作为对比可以看一下这下面这两个语句的输出 可以看到执行 select * from t1 where id5 的时候优化器会选择 B-Tree 索引所以返回结果是 0 到 4。
使用 force index 强行使用主键 id 这个索引id0 这一行就在结果集的最末尾了。
内存表的优势是速度快其中的一个原因就是 Memory 引擎支持 hash 索引。
当然更重要的原因是内存表的所有数据都保存在内存而内存的读写速度总是比磁盘快。 为什么不建议在生产环境上使用内存表。这里的原因主要包括两个方面
锁粒度问题数据持久化问题。 三、内存表的锁
我们先来说说内存表的锁粒度问题。内存表不支持行锁只支持表锁。
因此一张表只要有更新就会堵住其他所有在这个表上的读写操作。
需要注意的是这里的表锁跟之前介绍过的 MDL 锁不同但都是表级的锁。
模拟一下内存表的表级锁。 在这个执行序列里session A 的 update 语句要执行 50 秒在这个语句执行期间 session B 的查询会进入锁等待状态。
session C 的 show processlist 结果输出如下 跟行锁比起来表锁对并发访问的支持不够好。
所以内存表的锁粒度问题决定了它在处理并发事务的时候性能也不会太好。 四、数据持久性问题
数据放在内存中是内存表的优势但也是一个劣势。
因为数据库重启的时候所有的内存表都会被清空。
如果数据库异常重启内存表被清空也就清空了不会有什么问题啊。
但是在高可用架构下内存表的这个特点简直可以当做 bug 来看待了。
为什么这么说呢先看看 M-S 架构下使用内存表存在的问题。 来看一下下面这个时序
业务正常访问主库备库硬件升级备库重启内存表 t1 内容被清空备库重启后客户端发送一条 update 语句修改表 t1 的数据行这时备库应用线程就会报错“找不到要更新的行”。
这样就会导致主备同步停止。当然如果这时候发生主备切换的话客户端会看到表 t1 的数据“丢失”了。
在图 8 中这种有 proxy 的架构里大家默认主备切换的逻辑是由数据库系统自己维护的。
这样对客户端来说就是“网络断开重连之后发现内存表数据丢失了”。
可能说这还好啊毕竟主备发生切换连接会断开业务端能够感知到异常。 但是接下来内存表的这个特性就会让使用现象显得更“诡异”了。
由于 MySQL 知道重启之后内存表的数据会丢失。
所以担心主库重启之后出现主备不一致MySQL 在实现上做了这样一件事儿
在数据库重启之后往 binlog 里面写入一行 DELETE FROM t1。
如果使用是如图 9 所示的双 M 结构的话 在备库重启的时候备库 binlog 里的 delete 语句就会传到主库然后把主库内存表的内容删除。这样你在使用的时候就会发现主库的内存表数据突然被清空了。
基于上面的分析内存表并不适合在生产环境上作为普通数据表使用。 但是内存表执行速度快呀。
这个问题其实可以这么分析
如果你的表更新量大那么并发度是一个很重要的参考指标InnoDB 支持行锁并发度比内存表好能放到内存表的数据量都不大。如果你考虑的是读的性能一个读 QPS 很高并且数据量不大的表即使是使用 InnoDB数据也是都会缓存在 InnoDB Buffer Pool 里的。因此使用 InnoDB 表的读性能也不会差。
所以建议你把普通内存表都用 InnoDB 表来代替。
但是有一个场景却是例外的。 在数据量可控不会耗费过多内存的情况下可以考虑使用内存表。
内存临时表刚好可以无视内存表的两个不足主要是下面的三个原因
临时表不会被其他线程访问没有并发性的问题临时表重启后也是需要删除的清空数据这个问题不存在备库的临时表也不会影响主库的用户线程。
再看一下Join语句优化的例子当时建议的是创建一个 InnoDB 临时表使用的语句序列是
create temporary table temp_t(id int primary key, a int, b int, index(b))engineinnodb;
insert into temp_t select * from t2 where b1 and b2000;
select * from t1 join temp_t on (t1.btemp_t.b);这里使用内存临时表的效果更好原因有三个
相比于 InnoDB 表使用内存表不需要写磁盘往表 temp_t 的写数据的速度更快索引 b 使用 hash 索引查找的速度比 B-Tree 索引快临时表数据只有 2000 行占用的内存有限。
因此将临时表 temp_t 改成内存临时表并且在字段 b 上创建一个 hash 索引。
create temporary table temp_t(id int primary key, a int, b int, index (b))enginememory;
insert into temp_t select * from t2 where b1 and b2000;
select * from t1 join temp_t on (t1.btemp_t.b);可以看到不论是导入数据的时间还是执行 join 的时间使用内存临时表的速度都比使用 InnoDB 临时表要更快一些。 五、总结
由于重启会丢数据如果一个备库重启会导致主备同步线程停止如果主库跟这个备库是双 M 架构还可能导致主库的内存表数据被删掉。
因此在生产上不建议你使用普通内存表。如果是 DBA可以在建表的审核系统中增加这类规则要求业务改用 InnoDB 表。InnoDB 表性能还不错而且数据安全也有保障。而内存表由于不支持行锁更新语句会阻塞查询性能也未必就如想象中那么好。
基于内存表的特性还分析了它的一个适用场景就是内存临时表。
内存表支持 hash 索引这个特性利用起来对复杂查询的加速效果还是很不错的。 假设刚刚接手的一个数据库上真的发现了一个内存表。 备库重启之后肯定是会导致备库的内存表数据被清空进而导致主备同步停止。 这时最好的做法是将它修改成 InnoDB 引擎表。 假设当时的业务场景暂时不允许修改引擎可以加上什么自动化逻辑来避免主备同步停止呢 在备库上设置跳过主库内存表的同步避免因为同步主库内存表的错误而影响了其他表的同步进度。
