（点击上方公众号，可快速关注）
来源：十字螺丝钉
blog.chinaunix.net/uid-23284114-id-5775352.html
如有好文章投稿，请点击 → 这里了解详情
同事咨询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结尾。
特性
内存表的DDL操作
创建临时表
CREATE TABLE t (i INT) ENGINE = MEMORY;
基于非内存表创建内存表，并将数据拉入到内存表中
mysql> CREATE TABLE test ENGINE=MEMORY
-> SELECT ip,SUM(downloads) AS down
-> FROM log_table GROUP BY ip;
mysql> SELECT COUNT(ip),AVG(down) FROM test;
mysql> DROP TABLE test;
内存表的最大受限于 max_heap_table_size 参数，默认为16MB。根据场景需自己调整该参数。
索引
MEMORY存储引擎支持HASH和BTREE索引。
CREATE TABLE lookup
(id INT, INDEX USING HASH (id))
ENGINE = MEMORY;
CREATE TABLE lookup
(id INT, INDEX USING BTREE (id))
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
mysql> SET max_heap_table_size = 1024*1024;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE t1 (id INT, UNIQUE(id)) ENGINE = MEMORY;
Query OK, 0 rows affected (0.01 sec)
mysql> SET max_heap_table_size = 1024*1024*2;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE t2 (id INT, UNIQUE(id)) ENGINE = MEMORY;
Query OK, 0 rows affected (0.00 sec)
服务器重启后，每个表的尺寸都变成了全局 max_heap_table_size 变量的值大小。
压测结果
通过sysbench压测，InnoDB 写性能完爆MEMORY引擎，MEMORY的读性能略高于InnoDB。InnoDB的综合性能更好，足够满足日常使用。考虑到MEMORY引擎的雷点太多，建议不使用MEMORY引擎。
看完本文有收获？请转发分享给更多人
关注「数据库开发」，提升 DB 技能
