MySQL之内存表

写在前面

什么是内存表呢？存储引擎为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)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

可以看到输出的结果是和数据插入顺序一致的，这和其使用数组来存储数据有关系，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)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

可以看到(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      |         |               |
+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

此时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)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

3：业务中可以使用内存表吗

先说结论，不要使用，因为内存表存在如下的2个问题：

1:锁粒度问题，仅支持表锁
2:数据不可持久化，重启数据丢失 
1
2

先来看下第一个问题，首先会话A执行如下语句：

mysql> update t1 set id=sleep(50) where id=1;
1

会阻塞50秒。会话B执行如下查询：

mysql> select * from t1 where id=2;
被会话A的表锁阻塞中
1
2

会话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)
1
2
3
4
5
6
7
8
9

可以看到其中的Waiting for table level lock说明是在等待表锁。

但是内存表就没有用武之地了吗？自然业务中实际存在的表不要使用，但是如果是程序中需要用到临时表 且是用来执行查询的场景是则是可以使用的，因为毕竟hash索引以及基于内存的数据查询效率还是很优秀的。

写在后面

参考文章列表：

MySQL之临时表 。

数据结构之哈希算法 。