MySQL Memory 存儲引擎淺析（1）

需求源自項目中的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存儲引擎特性：

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條記錄比較。