IT评测·应用市场-qidao123.com技术社区

标题: 第三十七讲：都说InnoDB好，那还要不要使用Memory引擎？ [打印本页]

作者: 魏晓东 时间: 2024-11-22 23:22
标题: 第三十七讲：都说InnoDB好，那还要不要使用Memory引擎？
第三十七讲：都说InnoDB好，那还要不要使用Memory引擎？

简概

引言

我在上一篇文章末尾留给你的问题是：两个 group by 语句都用了 order by null，为什么使用内存临时表得到的语句结果里，0 这个值在最后一行；而使用磁盘临时表得到的结果里，0 这个值在第一行？
今天我们就来看看，出现这个问题的缘故原由吧。
内存表的数据组织结构

为了便于分析，我来把这个问题简化一下，假设有以下的两张表 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。

图 1 两个查询结果 -0 的位置可以看到，内存表 t1 的返回结果里面 0 在最后一行，而 InnoDB 表 t2 的返回结果里 0 在第一行。
出现这个区别的缘故原由，要从这两个引擎的主键索引的组织方式说起。
表 t2 用的是 InnoDB 引擎，它的主键索引 id 的组织方式，你已经很熟悉了：InnoDB 表的数据就放在主键索引树上，主键索引是 B+ 树。
所以表 t2 的数据组织方式如下图所示：

图 2 表 t2 的数据组织主键索引上的值是有序存储的。在执行 select * 的时候，就会按照叶子节点从左到右扫描，所以得到的结果里，0 就出现在第一行。
与 InnoDB 引擎不同，Memory 引擎的数据和索引是分开的。我们来看一下表 t1 中的数据内容。

图 3 表 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)，也就是固定长度字符串来存储，因此内存表的每行数据长度相同。

注意第三点
所有索引是指，该表的所有索引，而innodb表只必要修改主键索引表就行，因为其他的索引是使用回表的方式，用主键索引定位具体数据的。
这里数据位置指的是innodb引擎中数据在磁盘上的存储位置大概Memory引擎中数据在内存中的存储位置。inndob的数据是放在主键索引上的，如果其存储位置发生变化，则主键索引必要改动。但是其二级索引生存的是主键id，故不必要进行改动。Memory中数据发生位置改动，则对应主键索引必要改动，对应的其他索引，好比下文中所讲的B-tree索引由于生存的也是其存储位置，所以也必要改动。

由于内存表的这些特性，每个数据行被删除以后，空出的这个位置都可以被接下来要插入的数据复用。好比，如果要在表 t1 中执行：

delete from t1 where id=5;
insert into t1 values(10,10);
select * from t1;

复制代码

就会看到返回结果里，id=10 这一行出现在 id=4 之后，也就是原来 id=5 这行数据的位置。必要指出的是，表 t1 的这个主键索引是哈希索引，因此如果执行范围查询，好比
[code]select * from t1 where id

欢迎光临 IT评测·应用市场-qidao123.com技术社区 (https://dis.qidao123.com/)