MySQL MEMORY引擎及性能比对

数据库 MySQL
MEMORY存储引擎创建的表数据只能保存在内存。MySQL宕机、硬件故障或者意外掉电,都会造成MEMORY引擎表丢失数据。所以,MEMORY表中的数据来源于其他表(可落盘永久保存)用于只读适用,或者用于临时工作起到数据周转。

同事咨询MySQL MEMORY引擎的细节,能否满足需求。没有太多了解,这里做个系统总结。

MEMORY存储引擎创建的表数据只能保存在内存。

MySQL宕机、硬件故障或者意外掉电,都会造成MEMORY引擎表丢失数据。所以,MEMORY表中的数据来源于其他表(可落盘永久保存)用于只读适用,或者用于临时工作起到数据周转。

MEMORY 存储引擎特性

 

 

 

 

[a] Implemented in the server (via encryption functions). Data-at-rest tablespace encryption is available in MySQL 5.7 and later.

[b] Implemented in the server, rather than in the storage engine.

[c] Implemented in the server, rather than in the storage engine.

来源: https://dev.mysql.com/doc/refman/5.6/en/memory-storage-engine.html

何时使用MEMORY

  • 临时使用、不重要的数据,例如网站的会话管理和缓存。可接受数据丢失。
  • 发挥其访问快、低延迟特性
  • 只读或者大部分是读操作,不适合大量写操作。

性能特性

内存表受限于单线程执行和表级锁引起的争用。在负载增加时限制了可扩展性,特别是写操作。

尽管内存表在内存中处理,在繁忙的服务网上,并不一定比InnoDB表更快,如一般查询或者读写场景。特别是,多个会话执行update操作会造成性能低下。

根据不同的SQL查询,需要创建内存表的默认hash索引(基于唯一键的单个值查询),或者B-tree索引(等值查询、不等值查询或者范围查询)。

内存表的物理特性

每张内存表会在硬盘上创建一个文件,用于保存表结构(没有数据)。文件名为以内存表名开始,.frm结尾。

特性

  • 内存表的空间使用小块(small block)。表使用100%动态hash插入。已删除的行放到空闲列表中,下次插入新数据会被使用。
  • 使用定长的行存储格式。变长类型存入内存表需转换为定长。
  • 不支持BLOB、TEXT类型的列。
  • 内存表支持自增列(AUTO_INCREMENT)
  • 不能在多个会话上共享临时表。

内存表的DDL操作

创建临时表

  1. CREATE TABLE t (i INT) ENGINE = MEMORY; 

基于非内存表创建内存表,并将数据拉入到内存表中

  1. mysql> CREATE TABLE test ENGINE=MEMORY 
  2.  
  3. -> SELECT ip,SUM(downloads) AS down 
  4.  
  5. -> FROM log_table GROUP BY ip; 
  6.  
  7. mysql> SELECT COUNT(ip),AVG(down) FROM test; 
  8.  
  9. mysql> DROP TABLE test; 

 

内存表的最大受限于 max_heap_table_size 参数,默认为16MB。根据场景需自己调整该参数。

索引

MEMORY存储引擎支持HASH和BTREE索引。

  1. CREATE TABLE lookup 
  2.     (id INTINDEX USING HASH (id)) 
  3.     ENGINE = MEMORY; 
  4. CREATE TABLE lookup 
  5.     (id INTINDEX USING BTREE (id)) 
  6.     ENGINE = MEMORY; 

 

每张内存表可创建64个索引,每个索引最大支持16个列,一个key的长度最大值为3072bytes。

如果一个内存表hash索引的键值有很高的重复度,更新键值、删除操作速度都会显著降低。这种速度下降的程度与键值重复度成正比。您可以使用BTREE索引来规避这个问题。

内存表可以有非唯一键(这是hash索引不常用的功能)。

索引列中可包含NULL值。

加载数据

MySQL启动时,加入--init-file选项,将下列命令加入到这个文件中,保证启动后内存表中有数据。

  • INSERT INTO ... SELECT
  • LOAD DATA INFILE

内存表和复制(Replication)

服务器重启会导致内存表数据丢失。如果是主库,从库没有意识到主库表中数据已被情况,所以在从库你看到的是过期数据。

重启后,主从库如何同步内存表数据?

当主库使用内存表,主库启动后,一条DELETE语句会写入到主库的binary log中,从库接到命令后清空内存表。

主库重启期间,从库还是有读取到过期数据的情况。为了避免这种情况,主库启动时加入--init-file参数,这样主库启动自动将数据加载到内存表中。(官方文档写的不严谨,我认为仅限于内存表只读场景。如果不是只读,即使加入--init-file参数也无法保证主库内存表数据一致)。

管理内存使用情况

服务器必须有足够内存,来满足多张内存表的使用。

如果从内存表中删除单独的行,并不会回收内存。当整个内存表删除时,才回收内存。同一张内存表,之前删除行占用的空间,会被新的行复用。执行DELETE、TRUNCATE TABLE来释放内存表占用空间,如果表不在使用可使用DROP TABLE命令。释放正在使用的内存表占用的内存,可以使用 ALTER TABLE XX ENGINE=MEMORY 强制重建表。

内存表1行记录占用内存计算公式

SUM_OVER_ALL_BTREE_KEYS(max_length_of_key + sizeof(char*) * 4)

+ SUM_OVER_ALL_HASH_KEYS(sizeof(char*) * 2)

+ ALIGN(length_of_row+1, sizeof(char*))

ALIGN()表示一个round-up因子,其会导致行长度等于char指针大小的精确倍数。sizeof(char*)在32位机器上是4,64位则为8。

前面提到, max_heap_table_size 系统变量决定了内存表的最大尺寸。在创建内存之前,可设置该变量控制每个内存表的最大尺寸。(不建议修改全局 max_heap_table_size 的大小,否则所有会话的内存表最大尺寸都为该值 )。

下面的例子创建了2个内存表,最大尺寸为1M和2M

  1. mysql> SET max_heap_table_size = 1024*1024; 
  2. Query OK, 0 rows affected (0.00 sec) 
  3.  
  4. mysql> CREATE TABLE t1 (id INTUNIQUE(id)) ENGINE = MEMORY; 
  5. Query OK, 0 rows affected (0.01 sec) 
  6.  
  7. mysql> SET max_heap_table_size = 1024*1024*2; 
  8. Query OK, 0 rows affected (0.00 sec) 
  9.  
  10. mysql> CREATE TABLE t2 (id INTUNIQUE(id)) ENGINE = MEMORY; 
  11. Query OK, 0 rows affected (0.00 sec) 

 

服务器重启后,每个表的尺寸都变成了全局 max_heap_table_size 变量的值大小。

压测结果

通过sysbench压测,InnoDB 写性能完爆MEMORY引擎,MEMORY的读性能略高于InnoDB。InnoDB的综合性能更好,足够满足日常使用。考虑到MEMORY引擎的雷点太多,建议不使用MEMORY引擎。 

 

责任编辑:庞桂玉 来源: 数据库开发
相关推荐

2017-12-14 10:16:01

CaddySSLDockerNginx

2013-12-16 10:20:48

MySQL数据库

2016-12-07 10:42:57

排序算法实例

2013-04-03 10:04:36

MySQL 5.6

2011-07-06 14:18:40

Percona SerMySQL

2010-11-23 11:27:53

MySQL MyISA

2010-05-21 16:23:52

MySQL MyISA

2020-10-23 07:43:37

Log配置性能

2009-05-25 08:39:08

iPhone苹果移动OS

2009-11-10 10:32:16

UbuntuWindows 7主流性能

2011-04-15 10:26:38

JavaMVC

2013-12-18 10:27:11

OpenMP线程

2011-08-09 10:21:55

SQL Server存储过程分页

2023-11-20 10:34:09

语言

2011-05-18 14:52:04

XML

2015-03-09 10:40:44

MySQL大量数据插入

2009-07-01 18:12:18

JSP的优势性能比较

2019-06-17 16:17:03

存储MySQL主流

2009-12-04 19:28:25

FreeBSD 8.0Ubuntu 9.10性能比较

2012-12-03 10:26:51

Scala
点赞
收藏

51CTO技术栈公众号