有了InnoDB,Memory存储引擎还有意义吗?(上)

简介: 两个group by 语句都用了order by null,为什么使用内存临时表得到的语句结果里,0这个值在最后一行;而使用磁盘临时表得到的结果里,0这个值在第一行?

两个group by 语句都用了order by null,为什么使用内存临时表得到的语句结果里,0这个值在最后一行;而使用磁盘临时表得到的结果里,0这个值在第一行?

内存表的数据组织结构

  • 示例表

1.png

插入测试数据。分别查询

mysql> select *
    -> from t1;
+----+------+
| id | c    |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
|  4 |    4 |
|  5 |    5 |
|  6 |    6 |
|  7 |    7 |
|  8 |    8 |
|  9 |    9 |
|  0 |    0 |
+----+------+
10 rows in set (0.00 sec)
mysql> select *
    -> from t2;
+----+------+
| id | c    |
+----+------+
|  0 |    0 |
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
|  4 |    4 |
|  5 |    5 |
|  6 |    6 |
|  7 |    7 |
|  8 |    8 |
|  9 |    9 |
+----+------+
10 rows in set (0.00 sec)

可见:

  • 内存表的返回结果里面0在最后一行
  • InnoDB表的返回结果里0在第一行

主键索引的组织方式

  • InnoDB表的数据就放在主键索引树:t2的数据组织

image.png

主键索引上的值有序存储。执行select *时,就会按照叶子节点从左到右扫描,所以0在第一行。

  • Memory引擎的数据和索引是分开的:t1中的数据组织

image.png

内存表的数据以数组存放,而主键id索引里,存的是每个数据的位置。主键id是hash索引,所以索引的key并非有序。


在内存表中,执行select *时,是全表扫描:顺序扫描该数组。因此,0就是最后一个被读到,并放入结果集的数据。


所以InnoDB、Memory数据组织方式不同:


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


  1. InnoDB表的数据总是有序存放的,而内存表数据按写入顺序存放
  2. 当数据文件有空洞时,InnoDB表在插入新数据时,为保证有序性,只能在固定位置写入新值,内存表找到空位即可插入新值
  3. 数据位置发生变化时,InnoDB表只需修改主键索引,而内存表要修改所有索引
  4. InnoDB表用主键索引查询时需走一次索引查找,用普通索引查询时,走两次索引查找。而内存表的所 有索引的“地位”相同
  5. InnoDB支持变长数据类型,不同记录的长度可能不同;内存表不支持Blob、Text字段。即使定义了varchar(N),实际也当作char(N),即固定长度字符串,因此内存表的每行数据长度相同。


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

1.png

可见,id=10这行出现在id=4之后,即原来id=5这行数据位置。

t1的这个主键索引是哈希索引,因此若执行范围查询:

select * from t1 where id<5;

用不上主键索引的,而走全表扫描。

若要让内存表支持范围扫描,应该怎么办呢 ?

hash索引和B-Tree索引

内存表其实也支持B-Tree索引。在id列上创建一个B-Tree索引,SQL语句可以这么写:

image.png

此时t1的数据组织形式:

1.png

这就类似InnoDB的b+树索引了。

目录
相关文章
|
4天前
|
存储 SQL 关系型数据库
Mysql系列-4.Mysql存储引擎-InnoDB(下)
Mysql系列-4.Mysql存储引擎-InnoDB
50 0
|
4天前
|
存储 缓存 关系型数据库
MySQL - 存储引擎MyISAM和Innodb
MySQL - 存储引擎MyISAM和Innodb
|
4天前
|
存储 SQL 关系型数据库
MySQL存储引擎之MyISAM和InnoDB
MySQL存储引擎之MyISAM和InnoDB
51 0
|
4天前
|
存储 SQL 关系型数据库
Mysql专栏 - mysql、innodb存储引擎、binlog的工作流程
Mysql专栏 - mysql、innodb存储引擎、binlog的工作流程
85 0
|
4天前
|
存储 关系型数据库 MySQL
MySQL存储引擎 InnoDB、MyISAM、Memory存储引擎的特点与区别
MySQL存储引擎 InnoDB、MyISAM、Memory存储引擎的特点与区别
61 0
|
4天前
|
存储 缓存 关系型数据库
Mysql系列-4.Mysql存储引擎-InnoDB(上)
Mysql系列-4.Mysql存储引擎-InnoDB
43 0
|
4天前
|
存储 SQL 关系型数据库
二十、MySQL技术体系之InnoDB存储引擎的索引
二十、MySQL技术体系之InnoDB存储引擎的索引
60 0
|
4天前
|
存储 缓存 关系型数据库
MySQL技术内幕:InnoDB存储引擎
MySQL技术内幕:InnoDB存储引擎
|
4天前
|
存储 关系型数据库 MySQL
⑩【MySQL】存储引擎详解, InnoDB、MyISAM、Memory。
⑩【MySQL】存储引擎详解, InnoDB、MyISAM、Memory。
41 0
|
4天前
|
存储 关系型数据库 MySQL
MySQL引擎对决:深入解析MyISAM和InnoDB的区别
MySQL引擎对决:深入解析MyISAM和InnoDB的区别
38 0