MySQL的存储引擎之MEMORY

2020年3月1日
| 没有评论
摘要：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列。

© 2020, morinson. 版权所有. 欢迎转载，但请保留作者及出处。

MySQL
| Tags: , ,