本文是关于如何分析MySQL内存存储引擎的。边肖觉得很实用，所以分享给大家学习。希望你看完这篇文章能有所收获。让我们和边肖一起看看。
序
需求来源于项目中的MemCached需求，一开始想用MemCached(官网：)，但是这个在Linux下广泛使用的开源软件，并没有Windows版本的官方支持。后来看到博客公园用的是NorthScale Memcached Server(官网：)，好像是共享收费，又犹豫了。其实项目中的需求很简单，我想用。Net Cache自己实现。然而，稳定性难以评估，开发和维护成本似乎过高。不可能，我的SQL内存存储已经成为唯一的选择，因为几乎不需要写代码。
官方文件(翻译自内存存储引擎5.5版)
内存存储引擎将表数据存储在内存中。内存取代了以前的堆作为首选，但同时，堆在向后兼容方面仍然受到支持。
内存存储引擎功能：
Memory 与 MySQL Cluster的比较
想要部署内存引擎的开发人员会考虑MySQL集群是否是更好的选择。请参考以下内存引擎的使用场景和功能：
它可以像会话或缓存一样方便地操作和管理。
充分发挥内存引擎的特点：高速低延迟。
或者只读访问模式(不适合频繁写入)。
但内存表的性能受限于单线程的执行效率和写入时的表锁开销，限制了内存表在高负载下的可扩展性，尤其是混合写入操作的并发处理。此外，服务器重新启动后，内存表中的数据将会丢失。
MySQL Cluster支持与Memory引擎相同的功能，并提供更高的性能，并且具有Memory不支持的许多其他功能：
行锁机制更好地支持多线程和多用户并发。
更好地支持混合读写语句和扩展。
您可以选择磁盘存储介质来永久存储数据。
无共享和分布式体系结构保证没有单点故障和99.999%的可用性。
数据自动分布在每个节点中，应用程序开发人员不需要考虑分区或碎片解决方案。
支持内存中不支持的可变长度数据类型(包括BLOB和TEXT)。
有关MySQL集群和内存引擎之间比较的更多详细信息，您可以查看MySQL集群：的扩展网络服务，这是MySQL内存存储引擎的替代产品。本白皮书包括这两种技术的性能研究，并指导您一步一步地了解如何将内存用户迁移到MySQL集群。
每个内存表都与一个磁盘文件相关联。文件名以表名和扩展名开头。frm表示它存储的表定义。要明确您需要一个内存表，请使用“引擎”选项指定：
CREATE TABLE t(I INT)ENGINEER=MEMORY；
正如它们的名称所示，Memory表存储在内存中，默认情况下使用散列索引。这使得它们对于创建临时表非常快速和有用。但是，当服务器关闭时，存储在内存表中的所有数据都会丢失。因为表的定义存储在。frm文件，表本身继续存在，当服务器重新启动时，它们是空的。
此示例显示了如何创建、使用和删除内存表：
创建表测试引擎=内存；
从日志表中选择ip，按ip分组进行汇总(下载)；
选择计数(ip)，AVG(向下)从测试；
降表试验；
内存表具有以下特征：
给内存表的空间是以小块分配的。对表插入使用100%动态散列。不需要溢出区域或额外的外键空间。空闲列表没有额外的空间要求。删除的行被放在链表中，并在向表中插入新数据时重新使用。内存表也没有通常与删除和插入哈希表相关的问题。
MEMORY表每个表最多可以有64个索引，每个索引有16列，最大键长度为3072字节。
内存存储引擎支持HASH和BTREE索引。您可以通过添加USING子句为给定的索引指定一个或另一个，如下所示：
创建表格查找
(id INT，INDEX US
ING 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表在所有客户端之间共享，就像其它任何非临时表。
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 的值重置。)
服务器需要足够内存来维持所有在同一时间使用的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：
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设置一个合理值。
Memory存储引擎官方：http://forums.mysql.com/list.?92
性能测试
分别测试比较了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();
www.2cto.com DataTableInsertAndSelect();
}
}//end class
}
.Net Cache读写性能毫无疑问大大领先于引擎
InnoDB写入耗时大概是MyIsam和Memory的5倍左右，它的行锁机制必然决定了写入时的更多性能开销，而它的强项在于多线程的并发处理，而本测试未能体现其优势。
三种数据库引擎在SELECT性能上差不多，Memory稍占优，同样高并发下的比较有待进一步测试
以上就是如何进行MySQL Memory 存储引擎的浅析，小编相信有部分知识点可能是我们日常工作会见到或用到的。希望你能通过这篇文章学到更多知识。更多详情敬请关注行业资讯频道。
内容来源网络，如有侵权，联系删除，本文地址：https://www.230890.com/zhan/153559.html