mysql memory优点_MySQL Memory存储引擎：优势及性能测试

【IT168技术】需求源自项目中的MemCache需求，开始想用MemCached(官方站点：http://memcached.org/ )，但这个在Linux下面应用广泛的开源软件无官方支持的Windows版本。后来看到博客园在用NorthScale Memcached Server(官方站点：http://www.couchbase.com/products-and-services/memcached)，貌似共享收费，又犹豫了。其实项目里的需求很简单，也想自己用.Net Cache来实现，但稳定性难以评估，开发维护成本又似乎太大，没办法，My SQL Memory Storage成了唯一选择，因为几乎不怎么需要编写代码。

先看官方手册，然后写了个简单的性能测试。因为官方最新的文档都是英文版的，所以译了5.5版本 MySQL Memory Storage章节。

官方文档(译自5.5版本的The Memory Storage Engine)

Memory存储引擎将表的数据存放在内存中。Memory替代以前的Heap成为首选项，但同时向下兼容，Heap仍被支持。

Memory存储引擎特性：

Memory 与 MySQL Cluster的比较

希望部署内存引擎的开发者们会考虑MySQL Cluster是否是更好的选择，参考如下Memory引擎的使用场景及特点：

能像会话(Session)或缓存(Caching)一样方便操作和管理。

充分发挥内存引擎的特点：高速度，低延迟。

只读或读为主的访问模式(不适合频繁写)。

但是内存表的性能受制于单线程的执行效率和写操作时的表锁开销，这就限制了内存表高负载时的扩展性，特别是混合写操作的并发处理。此外，内存表中的数据在服务器重启后会丢失。

MySQL Cluster(集群)支持与Memory引擎同样的功能并且提供更高的性能，同时拥有Memory不支持的更多其它功能：

行锁机制更好的支持多线程多用户并发。

更好的支持读写混合语句以及扩展。

可选择磁盘存储介质永久保存数据。

Shared-nothing和分布式架构保证无单点故障，99.999% 可用性。

数据自动分布在各个节点，应用开发者无需考虑分区或分片解决方案。

支持MEMORY中不支持的变长数据类型(包括BLOB 和 TEXT)。

关于MySQL集群与Memory引擎更多细节方面的比较，可以查看Scaling Web Services with MySQL Cluster: An Alternative to the MySQL Memory Storage Engine，该白皮书包括了这两种技术的性能研究，并一步步指导你如何将Memory用户迁移到MySQL集群。

每个Memory表和一个磁盘文件关联起来。文件名由表的名字开始，并且由一个.frm的扩展名来指明它存储的表定义。要明确指出你想要一个Memory表，可使用ENGINE选项来指定：

CREATETABLEt (iINT) ENGINE=MEMORY;

如它们名字所指明的，Memory表被存储在内存中，且默认使用哈希索引。这使得它们非常快，并且对创建临时表非常有用。可是，当服务器关闭之时，所有存储在Memory表里的数据被丢失。因为表的定义被存在磁盘上的.frm文件中，所以表自身继续存在，在服务器重启动时它们是空的。

这个例子显示你如何可以创建，使用并删除一个Memory表：

CREATETABLEtest ENGINE=MEMORY;SELECTip,SUM(downloads)ASdownFROMlog_tableGROUPBYip;SELECTCOUNT(ip),AVG(down)FROMtest;DROPTABLEtest;

MEMORY表有下列特征：

给Memory表的空间被以小块来分配。表对插入使用100%动态哈希来。不需要溢出区或额外键空间。自由列表无额外的空间需求。已删除的行被放在一个以链接的列表里，并且在你往表里插入新数据之时被重新使用。Memory表也没有通常与在哈希表中删除加插入相关的问题。

MEMORY表可以有多达每个表64个索引，每个索引16列，以及3072字节的最大键长度。

MEMORY存储引擎支持HASH和BTREE索引。你可以通过添加一个如下所示的USING子句为给定的索引指定一个或另一个：

CREATETABLElookup(idINT,INDEXUSING HASH (id))ENGINE=MEMORY;CREATETABLElookup(idINT,INDEXUSING BTREE (id))ENGINE=MEMORY;

如果一个MEMORY 表的哈希索引键高度重复 (许多索引条目包含相同的值)，与索引键相关的更新以及所有的删除将会明显变慢。 重复度与速度成正比，此时你可以使用BTREE 索引来避免这个问题。

MEMORY表能够使用非唯一键。(对哈希索引的实现，这是一个不常用的功能)

对可包含NULL值的列的索引

MEMORY表使用固定的记录长度格式，像VARCHAR这样的可变长度类型将转换为固定长度类型在MEMORY表中存储。

MEMORY不能包含BLOB或TEXT列.MEMORY支持AUTO_INCREMENT列

MEMORY表支持INSERT DELAYED非临时的

MEMORY表在所有客户端之间共享，就像其它任何非临时表。

MEMORY表内容存储在内存中，它会作为动态查询队列创建内部临时表的共享介质，但是两个类型表的不同在于MEMORY表不会遇到存储转换，而内部表则会：

MEMORY表不会转换为磁盘表，而内部临时表如果太大会自动转换为磁盘表。

MEMORY表最大值受系统变量 max_heap_table_size 限制，默认为16MB，要改变MEMORY表大小限制，需要改变max_heap_table_size 的值。该值在 CREATE TABLE 时生效并伴随表的生命周期，(当你使用 ALTER TABLE 或 TRUNCATE TABLE命令时，表的最大限制将改变，或重启MYSQL服务时, 所有已存在的MEMORY表的最大限制将使用max_heap_table_size 的值重置。)

服务器需要足够内存来维持所有在同一时间使用的MEMORY表。

如果删除行，内存表不会回收内存，只有整张表全部删除的时候，才进行内存回收。同时只有在同一张表中插入新行时才会使用之前删除行的内存空间。 要释放已删除行所占用的内存空间，可以使用ALTER TABLE ENGINE=MEMORY对表进行强制重建。当内容过期要释放整张内存表，可以执行DELETE 或 TRUNCATE TABLE清除所有行，或者使用DROP TABLE删除表。

当MySQL服务器启动时，如果你想填充MEMORY表，你可以使用--init-file选项。例如，你可以把INSERT INTO ... SELECT 或LOAD DATA INFILE这样的语句放入这个文件中以便从持久稳固的的数据源装载表。

如果你正使用复制，当主服务器被关闭且重启动之时，主服务器的MEMORY表变空。可是从服务器意识不到这些表已经变空，所以如果你从它们选择数据，它就返回过时的内容。自从服务器启动后，当一个MEMORY表在主服务器上第一次被使用之时，一个DELETE FROM语句被自动写进主服务器的二进制日志，因此再次让从服务器与主服务器同步。注意，即使使用这个策略，在主服务器的重启和它第一次使用该表之间的间隔中，从服务器仍旧在表中有过时数据。可是，如果你使用--init-file选项于主服务器启动之时在其上推行MEMORY表。它确保这个时间间隔为零。

在MEMORY表中，一行需要的内存使用下列公式计算：

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 的值，除非你打算所有客户端创建的内存表都使用这个值)

下面的例子创建了两张内存表，它们的大小限制分别为 1MB 和 2MB：

SETmax_heap_table_size=1024*1024;

/*Query OK, 0 rows affected (0.00 sec)*/

CREATETABLEt1 (idINT,UNIQUE(id)) ENGINE=MEMORY;

/*Query OK, 0 rows affected (0.01 sec)*/

SETmax_heap_table_size=1024*1024*2;

/*Query OK, 0 rows affected (0.00 sec)*/

CREATETABLEt2 (idINT,UNIQUE(id)) ENGINE=MEMORY;

/*Query OK, 0 rows affected (0.00 sec)*/

如果服务重启，两张表的大小限制会使用全局的max_heap_table_size值复原。

你也可以通过CREATE TABLE 的MAX_ROWS选项设置表的最大行数，但max_heap_table_size的优先级高于MAX_ROWS，当两者同时存在时为了最大兼容，你需要将max_heap_table_size设置一个合理值。

Memory存储引擎官方论坛： http://forums.mysql.com/list.php?92