MEMORY存储引擎
Memory存储的表就是内存表。实际的数据存储在内存中,磁盘中只有表结构定义文件。
Mysql服务单独给memory存储引擎的表分配内存空间,而且表一直持有这些内存空间(即使删除数据也不释放内存),直到有drop、alter、create等重建对象才能释放内存。
注:内存的占用空间由max_heap_table_size参数控制,默认16M。
当心!当Mysql服务关闭时,数据会丢失。
建立Memory存储引擎的表
建立一个基于Memory存储引擎的表table_memory:
mysql> create table table_memory(id int) engine=memory;
Query OK, 0 rows affected (0.02 sec)
查看下table_memory表的占用空间大小(Max_data_length字段):
空间大小:16777216/1024/1024=16M
mysql> show table status like'table_memory';
| Name | Engine | Version | Row_format|Rows|Avg_row_length|Data_length| Max_data_length
| table_memory | MEMORY | 10 | Fixed | 0 | 8 | 0 | 16777216
……
查看下table_memory表的数据文件:
注:只有一个.frm的表结构定义文件
[mysql@localhost test]$ ll
-rw-rw---- 1 mysql mysql 8556 Sep 2 14:00 table_memory.frm
max_heap_table_size参数
更改max_heap_table_size参数(更改为1M和4M),再建立两个基于Memory存储引擎的表table_memory_1M、table_memory_4M,测试一下占用空间的大小:
注:更改的max_heap_table_size参数为会话级,而非全局级
mysql> set max_heap_table_size=1024*1024;
Query OK, 0 rows affected (0.01 sec)
mysql> create table table_memory_1M(idint) engine=memory;
Query OK, 0 rows affected (0.05 sec)
mysql> set max_heap_table_size=1024*1024*4;
Query OK, 0 rows affected (0.00 sec)
mysql> create table table_memory_4M(idint) engine=memory;
Query OK, 0 rows affected (0.01 sec)
查看占用空间大小:分别是16M、1M、4M
mysql> show table status like'table_memory%';
| Name | Engine | Version|Row_format | Rows |Avg_row_length | Data_length | Max_data_length
| table_memory | MEMORY | 10 | Fixed | 0 | 8 | 0 | 16777216
| table_memory_1M | MEMORY | 10 | Fixed | 0 | 8 | 0 | 1048576
| table_memory_4M | MEMORY | 10 | Fixed | 0 | 8 | 0 | 4194304
……
重启Mysql服务,数据和空间的变化
向table_memory表中插入一条数据:
mysql> insert into table_memory values(11);
Query OK, 1 row affected (0.00 sec)
mysql> select * from table_memory;
+------+
| id |
+------+
| 11 |
+------+
1 row in set (0.00 sec)
重启Mysql服务:
[mysql@localhost test]$ service mysqld restart;
Shutting down MySQL.... SUCCESS!
rm: cannot remove '/var/lock/subsys/mysql':Permission denied
Starting MySQL... SUCCESS!
再次查询table_memory表,数据丢失:
mysql> select * from table_memory;
Empty set (0.00 sec)
再次查看之前改过不同内存空间大小的三张表的状态,都是16M了:
mysql> show table status like'table_memory%';
| Name | Engine | Version|Row_format | Rows |Avg_row_length | Data_length | Max_data_length
| table_memory | MEMORY | 10 | Fixed | 0 | 8 | 0 | 16777216
| table_memory_1M | MEMORY | 10 | Fixed | 0 | 8 | 0 | 16777216
| table_memory_4M | MEMORY | 10 | Fixed | 0 | 8 | 0 | 16777216
……
注:Mysql服务重启后,所有表会自动继承全局的max_heap_table_size参数的值,所以都会变成16M