mysql type=memory_MySQL的存储引擎之MEMORY

MEMORY引擎在内存中创建表。由于数据是放在内存中的，所以访问速度非常快，但是硬件问题、断电或MySQL服务关闭等，表中数据就会丢失。因此MEMORY表主要用于临时用途、从其他表中提取数据的只读缓存等场景。

在MySQL 5.7里MEMORY引擎的特性如下：

Feature

Support

B-tree indexes

Yes

Backup/point-in-time recovery (Implemented in the server, rather than in the storage engine.)

Yes

Cluster database support

No

Clustered indexes

No

Compressed data

No

Data caches

N/A

Encrypted data

Yes (Implemented in the server via encryption functions.)

Foreign key support

No

Full-text search indexes

No

Geospatial data type support

No

Geospatial indexing support

No

Hash indexes

Yes

Index caches

N/A

Locking granularity

Table

MVCC

No

Replication support (Implemented in the server, rather than in the storage engine.)

Limited (See the discussion later in this section.)

Storage limits

RAM

T-tree indexes

No

Transactions

No

Update statistics for data dictionary

Yes

下面我们通过例子来看看

root@database-one 08:15: [gftest]> CREATE TABLE testmemory(

-> `eno` int(11) NOT NULL AUTO_INCREMENT,

-> `ename` varchar(10) DEFAULT NULL,

-> `age` int(11) DEFAULT NULL,

-> `sal` decimal(10,2) DEFAULT NULL,

-> `hiredate` date DEFAULT NULL,

-> `deptno` int(2) DEFAULT NULL,

-> PRIMARY KEY (`eno`)

-> ) ENGINE=memory;

Query OK, 0 rows affected (0.46 sec)

root@database-one 08:17: [gftest]> insert into testmemory select * from emp_copy;

Query OK, 40 rows affected (0.13 sec)

Records: 40 Duplicates: 0 Warnings: 0

root@database-one 08:17: [gftest]> select count(*) from testmemory;

+----------+

| count(*) |

+----------+

| 40 |

+----------+

1 row in set (0.01 sec)

root@database-one 08:17: [gftest]> show table status like 'testmemory' G

*************************** 1. row ***************************

Name: testmemory

Engine: MEMORY

Version: 10

Row_format: Fixed

Rows: 40

Avg_row_length: 52

Data_length: 7046424

Max_data_length: 65431132

Index_length: 3019896

Data_free: 0

Auto_increment: 50

Create_time: 2020-03-01 08:17:05

Update_time: NULL

Check_time: NULL

Collation: utf8_general_ci

Checksum: NULL

Create_options:

Comment:

1 row in set (0.01 sec)

MEMORY表上也可以建索引，并且可以指定使用HASH索引还是BTREE索引，不指定时默认是HASH索引。

root@database-one 08:20: [gftest]> create index idx_testmemory_ename on testmemory(ename);

Query OK, 40 rows affected (0.11 sec)

Records: 40 Duplicates: 0 Warnings: 0

root@database-one 08:21: [gftest]> show index from testmemory G

*************************** 1. row ***************************

Table: testmemory

Non_unique: 0

Key_name: PRIMARY

Seq_in_index: 1

Column_name: eno

Collation: NULL

Cardinality: 40

Sub_part: NULL

Packed: NULL

Null:

Index_type: HASH

Comment:

Index_comment:

*************************** 2. row ***************************

Table: testmemory

Non_unique: 1

Key_name: idx_testmemory_ename

Seq_in_index: 1

Column_name: ename

Collation: NULL

Cardinality: 4

Sub_part: NULL

Packed: NULL

Null: YES

Index_type: HASH

Comment:

Index_comment:

2 rows in set (0.03 sec)

root@database-one 08:21: [gftest]> drop index idx_testmemory_ename on testmemory;

Query OK, 40 rows affected (0.00 sec)

Records: 40 Duplicates: 0 Warnings: 0

root@database-one 08:23: [gftest]> create index idx_testmemory_ename using btree on testmemory(ename);

Query OK, 40 rows affected (0.01 sec)

Records: 40 Duplicates: 0 Warnings: 0

root@database-one 08:25: [gftest]> show index from testmemory G

*************************** 1. row ***************************

Table: testmemory

Non_unique: 0

Key_name: PRIMARY

Seq_in_index: 1

Column_name: eno

Collation: NULL

Cardinality: 40

Sub_part: NULL

Packed: NULL

Null:

Index_type: HASH

Comment:

Index_comment:

*************************** 2. row ***************************

Table: testmemory

Non_unique: 1

Key_name: idx_testmemory_ename

Seq_in_index: 1

Column_name: ename

Collation: A

Cardinality: NULL

Sub_part: NULL

Packed: NULL

Null: YES

Index_type: BTREE

Comment:

Index_comment:

2 rows in set (0.01 sec)

MEMORY表的数据是在内存中，但是表定义是落在磁盘上的，永久的，可以使用drop table删除。表定义文件名以表名开头，扩展名为.frm。

[root@database-one ~]# cd /home/mysql/gftest/

[root@database-one gftest]# ls -l testmemory*

-rw-r----- 1 mysql mysql 8718 3月 1 08:23 testmemory.frm

因为MEMORY表中的数据会随着MySQL关闭释放掉，为了在MySQL启动时自动填充数据到表里，可以使用–init-file选项指定文件，在文件中用INSERT INTO … SELECT或LOAD DATA等来实现数据填充。

每个MEMORY表中可以放置的数据量最大上限，由max_heap_table_size限制，笔者环境里是96M。

root@database-one 08:44: [gftest]> show variables like 'max_heap_table_size';

+---------------------+-----------+

| Variable_name | Value |

+---------------------+-----------+

| max_heap_table_size | 100663296 |

+---------------------+-----------+

1 row in set (0.01 sec)

在创建MEMORY表的时候，也可以通过MAX_ROWS指定表的最大行数。

MEMORY表还有几个特点：

MEMORY表的空间是以小块的形式分配的。

MEMORY表使用固定长度的行存储格式。可变长度类型(如VARCHAR)也是用固定长度存储的。

MEMORY表不支持BLOB和TEXT。

MEMORY表支持AUTO_INCREMENT列。