MySQL Memory 存儲(chǔ)引擎淺析
更新時(shí)間：2011年12月01日 00:09:09 作者：
需求源自項(xiàng)目中的MemCache需求，開(kāi)始想用MemCached（官方站點(diǎn)：http://memcached.org/ ），但這個(gè)在Linux下面應(yīng)用廣泛的開(kāi)源軟件無(wú)官方支持的Windows版本
后來(lái)看到博客園在用NorthScale Memcached Server（官方站點(diǎn)：http://www.couchbase.com/products-and-services/memcached），貌似共享收費(fèi)，又猶豫了。其實(shí)項(xiàng)目里的需求很簡(jiǎn)單，也想自己用.Net Cache來(lái)實(shí)現(xiàn)，但穩(wěn)定性難以評(píng)估，開(kāi)發(fā)維護(hù)成本又似乎太大，沒(méi)辦法，My SQL Memory Storage成了唯一選擇，因?yàn)閹缀醪辉趺葱枰帉?xiě)代碼。
先看官方手冊(cè)，然后寫(xiě)了個(gè)簡(jiǎn)單的性能測(cè)試。因?yàn)楣俜阶钚碌奈臋n都是英文版的，所以譯了5.5版本 MySQL Memory Storage章節(jié)。
官方文檔（譯自5.5版本的The Memory Storage Engine）
Memory存儲(chǔ)引擎將表的數(shù)據(jù)存放在內(nèi)存中。Memory替代以前的Heap成為首選項(xiàng)，但同時(shí)向下兼容，Heap仍被支持。
Memory存儲(chǔ)引擎特性：
Memory 與 MySQL Cluster的比較
希望部署內(nèi)存引擎的開(kāi)發(fā)者們會(huì)考慮MySQL Cluster是否是更好的選擇，參考如下Memory引擎的使用場(chǎng)景及特點(diǎn)：
能像會(huì)話(huà)（Session）或緩存（Caching）一樣方便操作和管理。
充分發(fā)揮內(nèi)存引擎的特點(diǎn)：高速度，低延遲。
只讀或讀為主的訪(fǎng)問(wèn)模式（不適合頻繁寫(xiě)）。
但是內(nèi)存表的性能受制于單線(xiàn)程的執(zhí)行效率和寫(xiě)操作時(shí)的表鎖開(kāi)銷(xiāo)，這就限制了內(nèi)存表高負(fù)載時(shí)的擴(kuò)展性，特別是混合寫(xiě)操作的并發(fā)處理。此外，內(nèi)存表中的數(shù)據(jù)在服務(wù)器重啟后會(huì)丟失。
MySQL Cluster（集群）支持與Memory引擎同樣的功能并且提供更高的性能，同時(shí)擁有Memory不支持的更多其它功能：
行鎖機(jī)制更好的支持多線(xiàn)程多用戶(hù)并發(fā)。
更好的支持讀寫(xiě)混合語(yǔ)句以及擴(kuò)展。
可選擇磁盤(pán)存儲(chǔ)介質(zhì)永久保存數(shù)據(jù)。
Shared-nothing和分布式架構(gòu)保證無(wú)單點(diǎn)故障，99.999% 可用性。
數(shù)據(jù)自動(dòng)分布在各個(gè)節(jié)點(diǎn)，應(yīng)用開(kāi)發(fā)者無(wú)需考慮分區(qū)或分片解決方案。
支持MEMORY中不支持的變長(zhǎng)數(shù)據(jù)類(lèi)型（包括BLOB 和 TEXT）。
關(guān)于MySQL集群與Memory引擎更多細(xì)節(jié)方面的比較，可以查看Scaling Web Services with MySQL Cluster: An Alternative to the MySQL Memory Storage Engine，該白皮書(shū)包括了這兩種技術(shù)的性能研究，并一步步指導(dǎo)你如何將Memory用戶(hù)遷移到MySQL集群。
每個(gè)Memory表和一個(gè)磁盤(pán)文件關(guān)聯(lián)起來(lái)。文件名由表的名字開(kāi)始，并且由一個(gè).frm的擴(kuò)展名來(lái)指明它存儲(chǔ)的表定義。要明確指出你想要一個(gè)Memory表，可使用ENGINE選項(xiàng)來(lái)指定：
CREATE TABLE t (i INT) ENGINE = MEMORY;
如它們名字所指明的，Memory表被存儲(chǔ)在內(nèi)存中，且默認(rèn)使用哈希索引。這使得它們非?？?，并且對(duì)創(chuàng)建臨時(shí)表非常有用?？墒?，當(dāng)服務(wù)器關(guān)閉之時(shí)，所有存儲(chǔ)在Memory表里的數(shù)據(jù)被丟失。因?yàn)楸淼亩x被存在磁盤(pán)上的.frm文件中，所以表自身繼續(xù)存在，在服務(wù)器重啟動(dòng)時(shí)它們是空的。
這個(gè)例子顯示你如何可以創(chuàng)建，使用并刪除一個(gè)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表的空間被以小塊來(lái)分配。表對(duì)插入使用100%動(dòng)態(tài)哈希來(lái)。不需要溢出區(qū)或額外鍵空間。自由列表無(wú)額外的空間需求。已刪除的行被放在一個(gè)以鏈接的列表里，并且在你往表里插入新數(shù)據(jù)之時(shí)被重新使用。Memory表也沒(méi)有通常與在哈希表中刪除加插入相關(guān)的問(wèn)題。
MEMORY表可以有多達(dá)每個(gè)表64個(gè)索引，每個(gè)索引16列，以及3072字節(jié)的最大鍵長(zhǎng)度。
MEMORY存儲(chǔ)引擎支持HASH和BTREE索引。你可以通過(guò)添加一個(gè)如下所示的USING子句為給定的索引指定一個(gè)或另一個(gè)：
CREATE TABLE lookup
(id INT, INDEX USING HASH (id))
ENGINE = MEMORY;
CREATE TABLE lookup
(id INT, INDEX USING BTREE (id))
ENGINE = MEMORY;
如果一個(gè)MEMORY 表的哈希索引鍵高度重復(fù) (許多索引條目包含相同的值)，與索引鍵相關(guān)的更新以及所有的刪除將會(huì)明顯變慢。 重復(fù)度與速度成正比，此時(shí)你可以使用BTREE 索引來(lái)避免這個(gè)問(wèn)題。
MEMORY表能夠使用非唯一鍵。（對(duì)哈希索引的實(shí)現(xiàn)，這是一個(gè)不常用的功能）
對(duì)可包含NULL值的列的索引
MEMORY表使用固定的記錄長(zhǎng)度格式，像VARCHAR這樣的可變長(zhǎng)度類(lèi)型將轉(zhuǎn)換為固定長(zhǎng)度類(lèi)型在MEMORY表中存儲(chǔ)。
MEMORY不能包含BLOB或TEXT列.
MEMORY支持AUTO_INCREMENT列
MEMORY表支持INSERT DELAYED
非臨時(shí)的MEMORY表在所有客戶(hù)端之間共享，就像其它任何非臨時(shí)表。
MEMORY表內(nèi)容存儲(chǔ)在內(nèi)存中，它會(huì)作為動(dòng)態(tài)查詢(xún)隊(duì)列創(chuàng)建內(nèi)部臨時(shí)表的共享介質(zhì)，但是兩個(gè)類(lèi)型表的不同在于MEMORY表不會(huì)遇到存儲(chǔ)轉(zhuǎn)換，而內(nèi)部表則會(huì)：
1、MEMORY表不會(huì)轉(zhuǎn)換為磁盤(pán)表，而內(nèi)部臨時(shí)表如果太大會(huì)自動(dòng)轉(zhuǎn)換為磁盤(pán)表。
2、MEMORY表最大值受系統(tǒng)變量 max_heap_table_size 限制，默認(rèn)為16MB，要改變MEMORY表大小限制，需要改變max_heap_table_size 的值。該值在 CREATE TABLE 時(shí)生效并伴隨表的生命周期，(當(dāng)你使用 ALTER TABLE 或 TRUNCATE TABLE命令時(shí)，表的最大限制將改變，或重啟MYSQL服務(wù)時(shí), 所有已存在的MEMORY表的最大限制將使用max_heap_table_size 的值重置。)
服務(wù)器需要足夠內(nèi)存來(lái)維持所有在同一時(shí)間使用的MEMORY表。
如果刪除行，內(nèi)存表不會(huì)回收內(nèi)存，只有整張表全部刪除的時(shí)候，才進(jìn)行內(nèi)存回收。同時(shí)只有在同一張表中插入新行時(shí)才會(huì)使用之前刪除行的內(nèi)存空間。 要釋放已刪除行所占用的內(nèi)存空間，可以使用ALTER TABLE ENGINE=MEMORY對(duì)表進(jìn)行強(qiáng)制重建。當(dāng)內(nèi)容過(guò)期要釋放整張內(nèi)存表，可以執(zhí)行DELETE 或 TRUNCATE TABLE清除所有行，或者使用DROP TABLE刪除表。
當(dāng)MySQL服務(wù)器啟動(dòng)時(shí)，如果你想填充MEMORY表，你可以使用--init-file選項(xiàng)。例如，你可以把INSERT INTO ... SELECT 或LOAD DATA INFILE這樣的語(yǔ)句放入這個(gè)文件中以便從持久穩(wěn)固的的數(shù)據(jù)源裝載表。
如果你正使用復(fù)制，當(dāng)主服務(wù)器被關(guān)閉且重啟動(dòng)之時(shí)，主服務(wù)器的MEMORY表變空?？墒菑姆?wù)器意識(shí)不到這些表已經(jīng)變空，所以如果你從它們選擇數(shù)據(jù)，它就返回過(guò)時(shí)的內(nèi)容。自從服務(wù)器啟動(dòng)后，當(dāng)一個(gè)MEMORY表在主服務(wù)器上第一次被使用之時(shí)，一個(gè)DELETE FROM語(yǔ)句被自動(dòng)寫(xiě)進(jìn)主服務(wù)器的二進(jìn)制日志，因此再次讓從服務(wù)器與主服務(wù)器同步。注意，即使使用這個(gè)策略，在主服務(wù)器的重啟和它第一次使用該表之間的間隔中，從服務(wù)器仍舊在表中有過(guò)時(shí)數(shù)據(jù)?？墒牵绻闶褂?-init-file選項(xiàng)于主服務(wù)器啟動(dòng)之時(shí)在其上推行MEMORY表。它確保這個(gè)時(shí)間間隔為零。
在MEMORY表中，一行需要的內(nèi)存使用下列公式計(jì)算：
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因子，它使得行的長(zhǎng)度為char指針大小的確切倍數(shù)。sizeof(char*)在32位機(jī)器上是4，在64位機(jī)器上是8。
如前所述，系統(tǒng)變量 max_heap_table_size 用于設(shè)置內(nèi)存表的大小上限。要控制單個(gè)表的最大值，需要在創(chuàng)建表之前設(shè)置會(huì)話(huà)變量。(不要設(shè)置全局max_heap_table_size 的值，除非你打算所有客戶(hù)端創(chuàng)建的內(nèi)存表都使用這個(gè)值)
下面的例子創(chuàng)建了兩張內(nèi)存表，它們的大小限制分別為 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) */
如果服務(wù)重啟，兩張表的大小限制會(huì)使用全局的max_heap_table_size值復(fù)原。
你也可以通過(guò)CREATE TABLE 的MAX_ROWS選項(xiàng)設(shè)置表的最大行數(shù)，但max_heap_table_size的優(yōu)先級(jí)高于MAX_ROWS，當(dāng)兩者同時(shí)存在時(shí)為了最大兼容，你需要將max_heap_table_size設(shè)置一個(gè)合理值。
分別測(cè)試比較了MySQL的InnoDB、MyIsam、Memory三種引擎與.Net DataTable的Insert以及Select性能（柱狀圖體現(xiàn)了其消耗時(shí)間，單位百納秒，innodb_flush_log_at_trx_commit參數(shù)配置為1，每次測(cè)試重啟了MySQL以避免Query Cache），大至結(jié)果如下：
/******************************************************
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;
測(cè)試代碼：
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
}
總結(jié)
.Net Cache讀寫(xiě)性能毫無(wú)疑問(wèn)大大領(lǐng)先于數(shù)據(jù)庫(kù)引擎
InnoDB寫(xiě)入耗時(shí)大概是MyIsam和Memory的5倍左右，它的行鎖機(jī)制必然決定了寫(xiě)入時(shí)的更多性能開(kāi)銷(xiāo)，而它的強(qiáng)項(xiàng)在于多線(xiàn)程的并發(fā)處理，而本測(cè)試未能體現(xiàn)其優(yōu)勢(shì)。
三種數(shù)據(jù)庫(kù)引擎在SELECT性能上差不多，Memory稍占優(yōu)，同樣高并發(fā)下的比較有待進(jìn)一步測(cè)試。
先看官方手冊(cè)，然后寫(xiě)了個(gè)簡(jiǎn)單的性能測(cè)試。因?yàn)楣俜阶钚碌奈臋n都是英文版的，所以譯了5.5版本 MySQL Memory Storage章節(jié)。
官方文檔（譯自5.5版本的The Memory Storage Engine）
Memory存儲(chǔ)引擎將表的數(shù)據(jù)存放在內(nèi)存中。Memory替代以前的Heap成為首選項(xiàng)，但同時(shí)向下兼容，Heap仍被支持。
Memory存儲(chǔ)引擎特性：
| 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 |
希望部署內(nèi)存引擎的開(kāi)發(fā)者們會(huì)考慮MySQL Cluster是否是更好的選擇，參考如下Memory引擎的使用場(chǎng)景及特點(diǎn)：
能像會(huì)話(huà)（Session）或緩存（Caching）一樣方便操作和管理。
充分發(fā)揮內(nèi)存引擎的特點(diǎn)：高速度，低延遲。
只讀或讀為主的訪(fǎng)問(wèn)模式（不適合頻繁寫(xiě)）。
但是內(nèi)存表的性能受制于單線(xiàn)程的執(zhí)行效率和寫(xiě)操作時(shí)的表鎖開(kāi)銷(xiāo)，這就限制了內(nèi)存表高負(fù)載時(shí)的擴(kuò)展性，特別是混合寫(xiě)操作的并發(fā)處理。此外，內(nèi)存表中的數(shù)據(jù)在服務(wù)器重啟后會(huì)丟失。
MySQL Cluster（集群）支持與Memory引擎同樣的功能并且提供更高的性能，同時(shí)擁有Memory不支持的更多其它功能：
行鎖機(jī)制更好的支持多線(xiàn)程多用戶(hù)并發(fā)。
更好的支持讀寫(xiě)混合語(yǔ)句以及擴(kuò)展。
可選擇磁盤(pán)存儲(chǔ)介質(zhì)永久保存數(shù)據(jù)。
Shared-nothing和分布式架構(gòu)保證無(wú)單點(diǎn)故障，99.999% 可用性。
數(shù)據(jù)自動(dòng)分布在各個(gè)節(jié)點(diǎn)，應(yīng)用開(kāi)發(fā)者無(wú)需考慮分區(qū)或分片解決方案。
支持MEMORY中不支持的變長(zhǎng)數(shù)據(jù)類(lèi)型（包括BLOB 和 TEXT）。
關(guān)于MySQL集群與Memory引擎更多細(xì)節(jié)方面的比較，可以查看Scaling Web Services with MySQL Cluster: An Alternative to the MySQL Memory Storage Engine，該白皮書(shū)包括了這兩種技術(shù)的性能研究，并一步步指導(dǎo)你如何將Memory用戶(hù)遷移到MySQL集群。
每個(gè)Memory表和一個(gè)磁盤(pán)文件關(guān)聯(lián)起來(lái)。文件名由表的名字開(kāi)始，并且由一個(gè).frm的擴(kuò)展名來(lái)指明它存儲(chǔ)的表定義。要明確指出你想要一個(gè)Memory表，可使用ENGINE選項(xiàng)來(lái)指定：
CREATE TABLE t (i INT) ENGINE = MEMORY;
如它們名字所指明的，Memory表被存儲(chǔ)在內(nèi)存中，且默認(rèn)使用哈希索引。這使得它們非?？?，并且對(duì)創(chuàng)建臨時(shí)表非常有用?？墒?，當(dāng)服務(wù)器關(guān)閉之時(shí)，所有存儲(chǔ)在Memory表里的數(shù)據(jù)被丟失。因?yàn)楸淼亩x被存在磁盤(pán)上的.frm文件中，所以表自身繼續(xù)存在，在服務(wù)器重啟動(dòng)時(shí)它們是空的。
這個(gè)例子顯示你如何可以創(chuàng)建，使用并刪除一個(gè)Memory表：
復(fù)制代碼 代碼如下:
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表的空間被以小塊來(lái)分配。表對(duì)插入使用100%動(dòng)態(tài)哈希來(lái)。不需要溢出區(qū)或額外鍵空間。自由列表無(wú)額外的空間需求。已刪除的行被放在一個(gè)以鏈接的列表里，并且在你往表里插入新數(shù)據(jù)之時(shí)被重新使用。Memory表也沒(méi)有通常與在哈希表中刪除加插入相關(guān)的問(wèn)題。
MEMORY表可以有多達(dá)每個(gè)表64個(gè)索引，每個(gè)索引16列，以及3072字節(jié)的最大鍵長(zhǎng)度。
MEMORY存儲(chǔ)引擎支持HASH和BTREE索引。你可以通過(guò)添加一個(gè)如下所示的USING子句為給定的索引指定一個(gè)或另一個(gè)：
復(fù)制代碼 代碼如下:
CREATE TABLE lookup
(id INT, INDEX USING HASH (id))
ENGINE = MEMORY;
CREATE TABLE lookup
(id INT, INDEX USING BTREE (id))
ENGINE = MEMORY;
如果一個(gè)MEMORY 表的哈希索引鍵高度重復(fù) (許多索引條目包含相同的值)，與索引鍵相關(guān)的更新以及所有的刪除將會(huì)明顯變慢。 重復(fù)度與速度成正比，此時(shí)你可以使用BTREE 索引來(lái)避免這個(gè)問(wèn)題。
MEMORY表能夠使用非唯一鍵。（對(duì)哈希索引的實(shí)現(xiàn)，這是一個(gè)不常用的功能）
對(duì)可包含NULL值的列的索引
MEMORY表使用固定的記錄長(zhǎng)度格式，像VARCHAR這樣的可變長(zhǎng)度類(lèi)型將轉(zhuǎn)換為固定長(zhǎng)度類(lèi)型在MEMORY表中存儲(chǔ)。
MEMORY不能包含BLOB或TEXT列.
MEMORY支持AUTO_INCREMENT列
MEMORY表支持INSERT DELAYED
非臨時(shí)的MEMORY表在所有客戶(hù)端之間共享，就像其它任何非臨時(shí)表。
MEMORY表內(nèi)容存儲(chǔ)在內(nèi)存中，它會(huì)作為動(dòng)態(tài)查詢(xún)隊(duì)列創(chuàng)建內(nèi)部臨時(shí)表的共享介質(zhì)，但是兩個(gè)類(lèi)型表的不同在于MEMORY表不會(huì)遇到存儲(chǔ)轉(zhuǎn)換，而內(nèi)部表則會(huì)：
1、MEMORY表不會(huì)轉(zhuǎn)換為磁盤(pán)表，而內(nèi)部臨時(shí)表如果太大會(huì)自動(dòng)轉(zhuǎn)換為磁盤(pán)表。
2、MEMORY表最大值受系統(tǒng)變量 max_heap_table_size 限制，默認(rèn)為16MB，要改變MEMORY表大小限制，需要改變max_heap_table_size 的值。該值在 CREATE TABLE 時(shí)生效并伴隨表的生命周期，(當(dāng)你使用 ALTER TABLE 或 TRUNCATE TABLE命令時(shí)，表的最大限制將改變，或重啟MYSQL服務(wù)時(shí), 所有已存在的MEMORY表的最大限制將使用max_heap_table_size 的值重置。)
服務(wù)器需要足夠內(nèi)存來(lái)維持所有在同一時(shí)間使用的MEMORY表。
如果刪除行，內(nèi)存表不會(huì)回收內(nèi)存，只有整張表全部刪除的時(shí)候，才進(jìn)行內(nèi)存回收。同時(shí)只有在同一張表中插入新行時(shí)才會(huì)使用之前刪除行的內(nèi)存空間。 要釋放已刪除行所占用的內(nèi)存空間，可以使用ALTER TABLE ENGINE=MEMORY對(duì)表進(jìn)行強(qiáng)制重建。當(dāng)內(nèi)容過(guò)期要釋放整張內(nèi)存表，可以執(zhí)行DELETE 或 TRUNCATE TABLE清除所有行，或者使用DROP TABLE刪除表。
當(dāng)MySQL服務(wù)器啟動(dòng)時(shí)，如果你想填充MEMORY表，你可以使用--init-file選項(xiàng)。例如，你可以把INSERT INTO ... SELECT 或LOAD DATA INFILE這樣的語(yǔ)句放入這個(gè)文件中以便從持久穩(wěn)固的的數(shù)據(jù)源裝載表。
如果你正使用復(fù)制，當(dāng)主服務(wù)器被關(guān)閉且重啟動(dòng)之時(shí)，主服務(wù)器的MEMORY表變空?？墒菑姆?wù)器意識(shí)不到這些表已經(jīng)變空，所以如果你從它們選擇數(shù)據(jù)，它就返回過(guò)時(shí)的內(nèi)容。自從服務(wù)器啟動(dòng)后，當(dāng)一個(gè)MEMORY表在主服務(wù)器上第一次被使用之時(shí)，一個(gè)DELETE FROM語(yǔ)句被自動(dòng)寫(xiě)進(jìn)主服務(wù)器的二進(jìn)制日志，因此再次讓從服務(wù)器與主服務(wù)器同步。注意，即使使用這個(gè)策略，在主服務(wù)器的重啟和它第一次使用該表之間的間隔中，從服務(wù)器仍舊在表中有過(guò)時(shí)數(shù)據(jù)?？墒牵绻闶褂?-init-file選項(xiàng)于主服務(wù)器啟動(dòng)之時(shí)在其上推行MEMORY表。它確保這個(gè)時(shí)間間隔為零。
在MEMORY表中，一行需要的內(nèi)存使用下列公式計(jì)算：
復(fù)制代碼 代碼如下:
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因子，它使得行的長(zhǎng)度為char指針大小的確切倍數(shù)。sizeof(char*)在32位機(jī)器上是4，在64位機(jī)器上是8。
如前所述，系統(tǒng)變量 max_heap_table_size 用于設(shè)置內(nèi)存表的大小上限。要控制單個(gè)表的最大值，需要在創(chuàng)建表之前設(shè)置會(huì)話(huà)變量。(不要設(shè)置全局max_heap_table_size 的值，除非你打算所有客戶(hù)端創(chuàng)建的內(nèi)存表都使用這個(gè)值)
下面的例子創(chuàng)建了兩張內(nèi)存表，它們的大小限制分別為 1MB 和 2MB：
復(fù)制代碼 代碼如下:
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) */
如果服務(wù)重啟，兩張表的大小限制會(huì)使用全局的max_heap_table_size值復(fù)原。
你也可以通過(guò)CREATE TABLE 的MAX_ROWS選項(xiàng)設(shè)置表的最大行數(shù)，但max_heap_table_size的優(yōu)先級(jí)高于MAX_ROWS，當(dāng)兩者同時(shí)存在時(shí)為了最大兼容，你需要將max_heap_table_size設(shè)置一個(gè)合理值。
Memory存儲(chǔ)引擎官方論壇： http://forums.mysql.com/list.php?92
性能測(cè)試分別測(cè)試比較了MySQL的InnoDB、MyIsam、Memory三種引擎與.Net DataTable的Insert以及Select性能（柱狀圖體現(xiàn)了其消耗時(shí)間，單位百納秒，innodb_flush_log_at_trx_commit參數(shù)配置為1，每次測(cè)試重啟了MySQL以避免Query Cache），大至結(jié)果如下：
寫(xiě)入10000條記錄比較。
讀取1000條記錄比較。
測(cè)試腳本：
復(fù)制代碼 代碼如下:
/******************************************************
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;
測(cè)試代碼：
復(fù)制代碼 代碼如下:
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
}
總結(jié)
.Net Cache讀寫(xiě)性能毫無(wú)疑問(wèn)大大領(lǐng)先于數(shù)據(jù)庫(kù)引擎
InnoDB寫(xiě)入耗時(shí)大概是MyIsam和Memory的5倍左右，它的行鎖機(jī)制必然決定了寫(xiě)入時(shí)的更多性能開(kāi)銷(xiāo)，而它的強(qiáng)項(xiàng)在于多線(xiàn)程的并發(fā)處理，而本測(cè)試未能體現(xiàn)其優(yōu)勢(shì)。
三種數(shù)據(jù)庫(kù)引擎在SELECT性能上差不多，Memory稍占優(yōu)，同樣高并發(fā)下的比較有待進(jìn)一步測(cè)試。
相關(guān)文章
這篇文章主要介紹了Mysql中被鎖住的表查詢(xún)以及如何解鎖的相關(guān)資料,這些方法可以幫助你釋放鎖并恢復(fù)表的正常使用,文中通過(guò)代碼介紹的非常詳細(xì),需要的朋友可以參考下2025-03-03
隨著時(shí)間的推移，表里面的數(shù)據(jù)越來(lái)越多，表數(shù)據(jù)文件越來(lái)越大，數(shù)據(jù)庫(kù)占用的空間自然也逐漸增長(zhǎng)，本文主要介紹了MySQL表空間回收，感興趣的可以了解一下2021-09-09
利用mysql的inet_aton()和inet_ntoa()函數(shù)存儲(chǔ)IP地址的方法分享
當(dāng)前很多應(yīng)用都適用字符串char(15)來(lái)存儲(chǔ)IP地址（占用16個(gè)字節(jié)），利用inet_aton()和inet_ntoa()函數(shù)，來(lái)存儲(chǔ)IP地址效率很高，適用unsigned int 就可以滿(mǎn)足需求，不需要使用bigint，只需要4個(gè)字節(jié)，節(jié)省存儲(chǔ)空間，同時(shí)效率也高很多2012-03-03
在大數(shù)據(jù)情況下MySQL的一種簡(jiǎn)單分頁(yè)優(yōu)化方法
這篇文章主要介紹了在大數(shù)據(jù)情況下MySQL的一種簡(jiǎn)單分頁(yè)優(yōu)化方法,分頁(yè)優(yōu)化是MySQL優(yōu)化的常用手段之一,需要的朋友可以參考下2015-05-05
解析sql語(yǔ)句中l(wèi)eft_join、inner_join中的on與where的區(qū)別
以下是對(duì)在sql語(yǔ)句中l(wèi)eft_join、inner_join中的on與where的區(qū)別進(jìn)行了詳細(xì)的分析介紹，需要的朋友可以參考下2013-07-07
MySQL安裝提示"請(qǐng)鍵入NET HELPMSG 3534以獲得更多的幫助"的解決辦法
這篇文章主要介紹了MySQL安裝提示"請(qǐng)鍵入NET HELPMSG 3534以獲得更多的幫助"的解決辦法2017-03-03
MySQL中安裝樣本數(shù)據(jù)庫(kù)Sakila過(guò)程分享
這篇文章主要介紹了MySQL中安裝樣本數(shù)據(jù)庫(kù)Sakila過(guò)程分享,Sakila數(shù)據(jù)庫(kù)主要用來(lái)做一些基本的操作以及壓力測(cè)試等,需要的朋友可以參考下2014-10-10
21條MySQL優(yōu)化建議（經(jīng)驗(yàn)總結(jié)）
這篇文章主要介紹了21條MySQL優(yōu)化建議,均來(lái)自個(gè)人的實(shí)戰(zhàn)經(jīng)驗(yàn)總結(jié),需要的朋友可以參考下2014-07-07
88秒插入1000萬(wàn)條數(shù)據(jù)到MySQL數(shù)據(jù)庫(kù)表的操作方法
這篇文章主要介紹了88秒插入1000萬(wàn)條數(shù)據(jù)到MySQL數(shù)據(jù)庫(kù)表的操作方法，首先給大家說(shuō)下我用到的數(shù)據(jù)庫(kù)表為mysql數(shù)據(jù)庫(kù)5.7版本的。具體操作方法大家跟隨小編一起通過(guò)本文學(xué)習(xí)吧2018-11-11
庫(kù)名表名大小寫(xiě)問(wèn)題與sqlserver兼容的啟動(dòng)配置方法
庫(kù)名表名大小寫(xiě)問(wèn)題與sqlserver兼容的啟動(dòng)配置方法，需要的朋友可以參考下。2010-12-12