mysql memory 引擎_MySQL Memory 存储引擎浅析

前言

需求源自项目中的MemCache需求，開始想用MemCached(官方网站：http://memcached.org/ )，但这个在Linux以下应用广泛的开源软件无官方支持的Windows版本号。后来看到博客园在用NorthScale Memcached Server(官方网站：http://www.couchbase.com/products-and-services/memcached)。貌似共享收费，又犹豫了。

事实上项目里的需求非常easy，也想自己用.Net

Cache来实现，但稳定性难以评估，开发维护成本又似乎太大，没办法。My SQL Memory Storage成了唯一选择，由于差点儿不怎么须要编写代码。

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

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

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

Memory存储引擎特性：

Storage limits

RAM

Transactions

No

Locking granularity

Table

MVCC

No

Geospatial data type support

No

Geospatial indexing support

No

B-tree indexes

Yes

Hash indexes

Yes

Full-text search indexes

No

Clustered indexes

No

Data caches

N/A

Index caches

N/A

Compressed data

No

Encrypted data

Yes

Cluster database support

No

Replication support

Yes

Foreign key support

No

Backup / point-in-time recoveryc

Yes

Query cache support

Yes

Update statistics for data dictionary

Yes

Memory 与 MySQL Cluster的比較

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

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

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

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

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

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选项来指定：

CREATE TABLE t (i INT) ENGINE = MEMORY;

如它们名字所指明的，Memory表被存储在内存中。且默认使用哈希索引。这使得它们很快，而且对创建暂时表很实用。但是。当server关闭之时，全部存储在Memory表里的数据被丢失。

由于表的定义被存在磁盘上的.frm文件里，所以表自身继续存在。在server重新启动动时它们是空的。

这个样例显示你怎样能够创建。使用并删除一个Memory表：

CREATE TABLE test ENGINE=MEMORY;

SELECT ip,SUM(downloads) AS down FROM log_table GROUP BY ip;

SELECT COUNT(ip),AVG(down) FROM test;

DROP TABLE test;

MEMORY表有下列特征：

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

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

MEMORY存储引擎支持HASH和BTREE索引。你能够通过加入一个例如以下所看到的的USING子句为给定的索引指定一个或还有一个：

CREATE TABLE lookup

(id INT, INDEX USING HASH (id))

ENGINE = MEMORY;

CREATE TABLE lookup

(id INT, INDEX USING BTREE (id))

ENGINE = MEMORY;

假设一个MEMORY 表的哈希索引键高度反复 (很多索引条目包括同样的值)。与索引键相关的更新以及全部的删除将会明显变慢。 反复度与速度成正比，此时你可以使用BTREE 索引来避免这个问题。

MEMORY表可以使用非唯一键。

(对哈希索引的实现，这是一个不经常使用的功能)

对可包括NULL值的列的索引

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

MEMORY不能包括BLOB或TEXT列.

MEMORY支持AUTO_INCREMENT列

MEMORY表支持INSERT DELAYED

非暂时的MEMORY表在全部client之间共享。就像其他不论什么非暂时表。

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

1、MEMORY表不会转换为磁盘表，而内部暂时表假设太大会自己主动转换为磁盘表。

2、MEMORY表最大值受系统变量 max_heap_table_size 限制，默觉得16MB，要改变MEMORY表限制大小，须要改变max_heap_table_size 的值。该值在 CREATE TABLE 时生效并伴随表的生命周期，(当你使用 ALTER TABLE 或 TRUNCATE TABLE命令时，表的最大限制将改变。或重新启动MYSQL服务时, 全部已存在的MEMORY表的最大限制将使用max_heap_table_size 的值重置。)

server须要足够内存来维持全部在同一时间使用的MEMORY表。

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

当MySQLserver启动时。假设你想填充MEMORY表，你能够使用--init-file选项。比如。你能够把INSERT INTO ... SELECT 或LOAD DATA INFILE这种语句放入这个文件里以便从持久稳固的的数据源装载表。

假设你正使用复制，当主server被关闭且重新启动动之时，主server的MEMORY表变空。但是从server意识不到这些表已经变空。所以假设你从它们选择数据。它就返回过时的内容。

自从server启动后。当一个MEMORY表在主server上第一次被使用之时，一个DELETE FROM语句被自己主动写进主server的二进制日志，因此再次让从server与主server同步。

注意，即使使用这个策略，在主server的重新启动和它第一次使用该表之间的间隔中，从server仍旧在表中有过时数据。但是，假设你使用--init-file选项于主server启动之时在其上推行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 的值。除非你打算全部client创建的内存表都使用这个值)

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

SET max_heap_table_size = 1024*1024;

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

CREATE TABLE t1 (id INT, UNIQUE(id)) ENGINE = MEMORY;

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

SET max_heap_table_size = 1024*1024*2;

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

CREATE TABLE t2 (id INT, 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设置一个合理值。

性能測试

分别測试比較了MySQL的InnoDB、MyIsam、Memory三种引擎与.Net DataTable的Insert以及Select性能(柱状图体现了其消耗时间，单位百纳秒。innodb_flush_log_at_trx_commit參数配置为1，每次測试重新启动了MySQL以避免Query Cache)。大至结果例如以下：

写入10000条记录比較。

读取1000条记录比較。

測试脚本：

/******************************************************

MYSQL STORAGE ENGINE TEST

http://wu-jian.cnblogs.com/

2011-11-29

******************************************************/

CREATE DATABASE IF NOT EXISTS test

CHARACTER SET 'utf8'

COLLATE 'utf8_general_ci';

USE test;

/******************************************************

1.INNODB

******************************************************/

DROP TABLE IF EXISTS test_innodb;

CREATE TABLE IF NOT EXISTS test_innodb (

id INT UNSIGNED AUTO_INCREMENT COMMENT 'PK',

obj CHAR(255) NOT NULL DEFAULT '' COMMENT 'OBJECT',

PRIMARY KEY (id)

) ENGINE=INNODB;

/******************************************************

2.MYISAM

******************************************************/

DROP TABLE IF EXISTS test_myisam;

CREATE TABLE IF NOT EXISTS test_myisam (

id INT UNSIGNED AUTO_INCREMENT COMMENT 'PK',

obj CHAR(255) NOT NULL DEFAULT '' COMMENT 'OBJECT',

PRIMARY KEY (id)

) ENGINE=MYISAM;

/******************************************************

1.MEMORY

******************************************************/

DROP TABLE IF EXISTS test_memory;

CREATE TABLE IF NOT EXISTS test_memory (

id INT UNSIGNED AUTO_INCREMENT COMMENT 'PK',

obj CHAR(255) NOT NULL DEFAULT '' COMMENT 'OBJECT',

PRIMARY KEY (id)

) ENGINE=MEMORY;

測试代码：

using System;

using System.Data;

using MySql.Data.MySqlClient;

namespace MySqlEngineTest

{

class Program

{

const string OBJ = "The MEMORY storage engine creates tables with contents that are stored in memory. Formerly, these were known as HEAP tables. MEMORY is the preferred term, although HEAP remains supported for backward compatibility.";

const string SQL_CONN = "Data Source=127.0.0.1;Port=3308;User ID=root;Password=root;DataBase=test;Allow Zero Datetime=true;Charset=utf8;pooling=true;";

const int LOOP_TOTAL = 10000;

const int LOOP_BEGIN = 8000;

const int LOOP_END = 9000;

#region Database Functions

public static bool DB_InnoDBInsert(string obj)

{

string commandText = "INSERT INTO test_innodb (obj) VALUES (?

obj)";

MySqlParameter[] parameters = {

new MySqlParameter("?

obj", MySqlDbType.VarChar, 255)

};

parameters[0].Value = obj;

if (DBUtility.MySqlHelper.ExecuteNonQuery(SQL_CONN, CommandType.Text, commandText, parameters) > 0)

return true;

else

return false;

}

public static string DB_InnoDBSelect(int id)

{

string commandText = "SELECT obj FROM test_innodb WHERE id = ?id";

MySqlParameter[] parameters = {

new MySqlParameter("?id", MySqlDbType.Int32)

};

parameters[0].Value = id;

return DBUtility.MySqlHelper.ExecuteScalar(SQL_CONN, CommandType.Text, commandText, parameters).ToString();

}

public static bool DB_MyIsamInsert(string obj)

{

string commandText = "INSERT INTO test_myisam (obj) VALUES (?obj)";

MySqlParameter[] parameters = {

new MySqlParameter("?obj", MySqlDbType.VarChar, 255)

};

parameters[0].Value = obj;

if (DBUtility.MySqlHelper.ExecuteNonQuery(SQL_CONN, CommandType.Text, commandText, parameters) > 0)

return true;

else

return false;

}

public static string DB_MyIsamSelect(int id)

{

string commandText = "SELECT obj FROM test_myisam WHERE id = ?id";

MySqlParameter[] parameters = {

new MySqlParameter("?id", MySqlDbType.Int32)

};

parameters[0].Value = id;

return DBUtility.MySqlHelper.ExecuteScalar(SQL_CONN, CommandType.Text, commandText, parameters).ToString();

}

public static bool DB_MemoryInsert(string obj)

{

string commandText = "INSERT INTO test_memory (obj) VALUES (?obj)";

MySqlParameter[] parameters = {

new MySqlParameter("?

obj", MySqlDbType.VarChar, 255)

};

parameters[0].Value = obj;

if (DBUtility.MySqlHelper.ExecuteNonQuery(SQL_CONN, CommandType.Text, commandText, parameters) > 0)

return true;

else

return false;

}

public static string DB_MemorySelect(int id)

{

string commandText = "SELECT obj FROM test_memory WHERE id = ?id";

MySqlParameter[] parameters = {

new MySqlParameter("?id", MySqlDbType.Int32)

};

parameters[0].Value = id;

return DBUtility.MySqlHelper.ExecuteScalar(SQL_CONN, CommandType.Text, commandText, parameters).ToString();

}

#endregion

#region Test Functions InnoDB

static void InnoDBInsert()

{

long begin = DateTime.Now.Ticks;

for (int i = 0; i < LOOP_TOTAL; i++)

{

DB_InnoDBInsert(OBJ);

}

Console.WriteLine("InnoDB Insert Result: {0}", DateTime.Now.Ticks - begin);

}

static void InnoDBSelect()

{

long begin = DateTime.Now.Ticks;

for (int i = LOOP_BEGIN; i < LOOP_END; i++)

{

DB_InnoDBSelect(i);

}

Console.WriteLine("InnoDB SELECT Result: {0}", DateTime.Now.Ticks - begin);

}

static void MyIsamInsert()

{

long begin = DateTime.Now.Ticks;

for (int i = 0; i < LOOP_TOTAL; i++)

{

DB_MyIsamInsert(OBJ);

}

Console.WriteLine("MyIsam Insert Result: {0}", DateTime.Now.Ticks - begin);

}

static void MyIsamSelect()

{

long begin = DateTime.Now.Ticks;

for (int i = LOOP_BEGIN; i < LOOP_END; i++)

{

DB_MyIsamSelect(i);

}

Console.WriteLine("MyIsam SELECT Result: {0}", DateTime.Now.Ticks - begin);

}

static void MemoryInsert()

{

long begin = DateTime.Now.Ticks;

for (int i = 0; i < LOOP_TOTAL; i++)

{

DB_MemoryInsert(OBJ);

}

Console.WriteLine("Memory Insert Result: {0}", DateTime.Now.Ticks - begin);

}

static void MemorySelect()

{

long begin = DateTime.Now.Ticks;

for (int i = LOOP_BEGIN; i < LOOP_END; i++)

{

DB_MemorySelect(i);

}

Console.WriteLine("Memory SELECT Result: {0}", DateTime.Now.Ticks - begin);

}

static void DataTableInsertAndSelect()

{

//Insert DataTable dt = new DataTable();

dt.Columns.Add("id", Type.GetType("System.Int32"));

dt.Columns["id"].AutoIncrement = true;

dt.Columns.Add("obj", Type.GetType("System.String"));

DataRow dr = null;

long begin = DateTime.Now.Ticks;

for (int i = 0; i < LOOP_TOTAL; i++)

{

dr = null;

dr = dt.NewRow();

dr["obj"] = OBJ;

dt.Rows.Add(dr);

}

Console.WriteLine("DataTable Insert Result: {0}", DateTime.Now.Ticks - begin);

//Select long begin1 = DateTime.Now.Ticks;

for (int i = LOOP_BEGIN; i < LOOP_END; i++)

{

dt.Select("id =" + i);

}

Console.WriteLine("DataTable Select Result: {0}", DateTime.Now.Ticks - begin1);

}

#endregion

static void Main(string[] args)

{

InnoDBInsert();

InnoDBSelect();

//restart mysql to avoid query cache

MyIsamInsert();

MyIsamSelect();

//restart mysql to avoid query cache

MemoryInsert();

MemorySelect();

DataTableInsertAndSelect();

}

}//end class}

总结

.Net Cache读写性能毫无疑问大大率先于数据库引擎

InnoDB写入耗时大概是MyIsam和Memory的5倍左右。它的行锁机制必定决定了写入时的很多其它性能开销，而它的强项在于多线程的并发处理，而本測试未能体现其优势。

三种数据库引擎在SELECT性能上差点儿相同，Memory稍占优，相同高并发下的比較有待进一步測试。