写在前面
什么是内存表呢?存储引擎为memory的表就是内存表,所以也是MySQL普通表的一种,只是因为数据在内存中,所以我们将其称之为内存表。本文就一起来看下吧!
1:内存表的组织结构
在分析内存表的组织结构之前,我们先来看如下的输出结果:
mysql> create table t1(id int primary key, c int) engine=Memory;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t1 values(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(0,0);
Query OK, 10 rows affected (0.00 sec)
Records: 10 Duplicates: 0 Warnings: 0
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)
可以看到输出的结果是和数据插入顺序一致的,这和其使用数组来存储数据有关系,insert数据的过程其实就是向数组中添加元素的过程,而select的过程其实就是从数组中查询数据的过程,如下图:
另外其主键索引使用的是哈希 结构,可以参考下图:
我们知道InnoDB的数据保存在主键索引上,这种组织数据的方式我们称之为索引组织表(Index Organized Table),而这里分析的Memory存储引擎是将数据单独存放的(存放在数组中)
,索引上保存的是数组下标,这种数据组织的方式我们称之为堆组织表(Heap Organized Table)。
当有新数据存入的时候,memory存储引擎会将数据放到数组中第一个空缺的位置,我们可以通过如下操作验证:
mysql> delete from t1 where id=4;
Query OK, 1 row affected (0.00 sec)
mysql> insert into t1 values(111,1111);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t1;
+-----+------+
| id | c |
+-----+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 111 | 1111 |
| 5 | 5 |
| 6 | 6 |
| 7 | 7 |
| 8 | 8 |
| 9 | 9 |
| 0 | 0 |
+-----+------+
10 rows in set (0.00 sec)
可以看到(111,111)
已经占据了被删除的(4,4)
的位置。另外因为hash索引是无序的,所以当我们执行范围查询时会走全表扫描,此时如果想要避免全表扫描的话也可以使用btree索引,接下来一起看下。
2:内存表使用B-Tree索引
我们可以通过alter语句给memory存储引擎的主键在hash索引之外再增加一个B-Tree索引,如下:
mysql> show indexes from t1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t1 | 0 | PRIMARY | 1 | id | NULL | 10 | NULL | NULL | | HASH | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)
mysql> alter table t1 add index id_btree_idx using btree (id);
Query OK, 10 rows affected (0.00 sec)
Records: 10 Duplicates: 0 Warnings: 0
mysql> show indexes from t1;
+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t1 | 0 | PRIMARY | 1 | id | NULL | 10 | NULL | NULL | | HASH | | |
| t1 | 1 | id_btree_idx | 1 | id | A | NULL | NULL | NULL | | BTREE | | |
+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
此时id主键就存在2个索引,一个是默认的hash索引,还有一个我们新加的btree索引,结构可能如下图:
可以看到这两个索引的名字分别是PRIMARY,id_btree_idx,当我们使用前者查询数据时就是通过遍历数据数组的方式,因此是和数组存储数组的方式保持一致的,而如果是使用btree索引id_btree_idx输出的结果id就是有序的了,如下:
mysql> select * from t1 force index(primary) where id>5;
+-----+------+
| id | c |
+-----+------+
| 111 | 1111 |
| 6 | 6 |
| 7 | 7 |
| 8 | 8 |
| 9 | 9 |
+-----+------+
5 rows in set (0.00 sec)
mysql> select * from t1 force index(id_btree_idx) where id>5;
+-----+------+
| id | c |
+-----+------+
| 6 | 6 |
| 7 | 7 |
| 8 | 8 |
| 9 | 9 |
| 111 | 1111 |
+-----+------+
5 rows in set (0.00 sec)
3:业务中可以使用内存表吗
先说结论,不要使用,因为内存表存在如下的2个问题:
1:锁粒度问题,仅支持表锁
2:数据不可持久化,重启数据丢失
先来看下第一个问题,首先会话A执行如下语句:
mysql> update t1 set id=sleep(50) where id=1;
会阻塞50秒。会话B执行如下查询:
mysql> select * from t1 where id=2;
被会话A的表锁阻塞中
会话C查看线程状态:
mysql> show processlist;
+----+------+-----------+------+---------+------+------------------------------+---------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+------------------------------+---------------------------------------+
| 4 | root | localhost | test | Query | 38 | User sleep | update t1 set id=sleep(50) where id=1 |
| 7 | root | localhost | test | Query | 16 | Waiting for table level lock | select * from t1 where id=2 |
| 8 | root | localhost | NULL | Query | 0 | starting | show processlist |
+----+------+-----------+------+---------+------+------------------------------+---------------------------------------+
3 rows in set (0.00 sec)
可以看到其中的Waiting for table level lock
说明是在等待表锁。
但是内存表就没有用武之地了吗?自然业务中实际存在的表不要使用,但是如果是程序中需要用到临时表 且是用来执行查询的场景是则是可以使用的,因为毕竟hash索引以及基于内存的数据查询效率还是很优秀的。
写在后面
参考文章列表: