MySQL Memory 儲存引擎淺析

後來看到部落格園在用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儲存引擎特性：

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）一樣方便操作和管理。
充分發揮記憶體引擎的特點：高速度，低延遲。
唯讀或讀為主的訪問模式（不適合頻繁寫）。
但是記憶體表的效能受制於單線程的執行效率和寫操作時的表鎖開銷，這就限制了記憶體表高負載時的擴充性，特別是混合寫操作的並發處理。此外，記憶體表中的資料在伺服器重啟後會丟失。

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表被儲存在記憶體中，且預設使用雜湊索引。這使得它們非常快，並且對建立暫存資料表非常有用。可是，當伺服器關閉之時，所有儲存在Memory表裡的資料被丟失。因為表的定義被存在磁碟上的.frm檔案中，所以表自身繼續存在，在伺服器重啟動時它們是空的。

這個例子顯示你如何可以建立，使用並刪除一個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表在所有用戶端之間共用，就像其它任何非暫存資料表。
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.php?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();
DataTableInsertAndSelect();
}
}//end class
}

總結
.Net Cache讀寫效能毫無疑問大大領先於資料庫引擎
InnoDB寫入耗時大概是MyIsam和Memory的5倍左右，它的行鎖機制必然決定了寫入時的更多效能開銷，而它的強項在於多線程的並發處理，而本測試未能體現其優勢。
三種資料庫引擎在SELECT效能上差不多，Memory稍佔優，同樣高並發下的比較有待進一步測試。