MySQL MEMORY儲存引擎

MEMORY儲存引擎特性

MEMORY儲存引擎 VS MySQL Cluster

MEMORY儲存引擎的使用場景及特點：

• 涉及會話管理、快取操作等非關鍵性資料
• 發揮記憶體儲存引擎的特性：快速訪問、低延遲
• 只讀或已讀為主的訪問模式(非頻繁寫)

MySQL Cluster(叢集)支援與MEMORY引擎同樣的功能並提供更高的效能，並提供MEMORY不支援的更多其他特性：

• 行級鎖支援多執行緒多使用者併發
• 支援讀寫混合語句的擴充套件
• 可選擇磁碟介質永久儲存資料
• Shared-nothing及分散式架構防止單點故障，實現高可用性
• 資料自動分佈在各個節點上，不需考分割槽或分片解決方案
• 支援MEMORY中不支援的變長資料型別（包含BLOB 和 TEXT）

MEMORY表效能特點

記憶體效能被單一執行緒執行及表級鎖開銷所制約，當負載增加特別是包含大量寫操作時，這將成為效能的瓶頸。儘管是完全使用記憶體訪問，但是在業務繁忙的系統，他們並不見得比InnoDB表更快。在一般查詢系統，或讀/寫工作負載，表級鎖粒度將大大降低併發訪問。

MEMORY表的特性

• 每一個記憶體表都將在磁碟上生成一個檔案用於儲存表定義（不包含資料），副檔名為tbl_ame.frm
  給MEMORY表的空間都以小塊來分配。表使用時100%動態雜湊來插入，不需要溢位區或額外的鍵空間。自由列表無額外的空間需求。刪除的記錄會被放入連結串列內並會在有新記錄插入時重新使用，MEMORY表也沒有通常雜湊表中刪除插入相關的問題。
• MEMORY表使用固定長度的行儲存格式，可變長度型別如Varchar使用固定長度
• MEMORY表不能包含BLOB 或 TEXT列
• MEMORY表支援AUTO_INCREMENT列
• 像其他非臨時表一樣，非臨時記憶體表也可多Session共享

MEMORY表DDL操作

建立表時使用ENGINE=MEMORY,MEMORY表大小由max_heap_table_size控制，該值預設為16MB

mysql> CREATE TABLE t (i INT) ENGINE=MEMORY;

mysql> CREATE TABLE test_memory ENGINE= MEMORY as select * from test;

mysql> DROP TABLE test_memory;

MEMORY表支援HASH與BTREE索引，MEMORY表最大支援64個索引，每個索引16列，最大鍵長度為3072位元組
MEMORY表中如果Hash索引包含大量重複的鍵(多個索引項包含相同值)，更新或刪除表中鍵值會非常緩慢，減慢程度與重複度成正比，可使用BTREE索引避免此問題;
MEMORY表支援非唯一鍵（一般雜湊索引不能實現的特性;
索引列可包含NULL值

mysql> CREATE TABLE lookup
        (id INT, INDEX USING HASH (id))
       ENGINE = MEMORY;

mysql> CREATE TABLE lookup
        (id INT, INDEX USING BTREE (id))
       ENGINE = MEMORY;

MEMORY表 與 TEMPORARY表

兩者均可將資料放入記憶體，當臨時表資料超過tmp_table_size時，自動轉化為磁碟表，效能急劇下降
MEMORY表資料滿之後，會提示錯誤，不會進行轉化。

MEMORY表與複製

當主庫關閉或重啟時，主庫上的MEMORY表資料清空,會造成主從資料不一致。
為同步主備資料MEMORY表，當主庫啟動後第一次使用記憶體表時，會在主庫的binlog中寫入一條DELETE語句，來刪除從庫中的資料。當主庫重啟及第一次使用記憶體表間，從庫的資料依舊不是最新的資料，為避免該情況，主庫啟動時使用--init-file重置記憶體表。
可以將INSERT INTO ... SELECT 或 LOAD DATA INFILE等初始化語句放入該檔案中作為MEMORY表
持久的資料來源。
MEMORY也支援INSERT DELAYED來匯入資料供會話併發訪問。

MEMORY表記憶體管理方式

伺服器需要足夠的記憶體來執行所有正在使用的記憶體表
當刪除部分記錄時，記憶體不會立即被回收；只有全部記錄都被刪除時，記憶體才會被回收。之前用於儲存刪除記錄的記憶體空間會被該表的新增記錄使用。
收回記憶體表使用空間的方式執行DELETE、TRUNCATE或DROP TABLE可完全釋放該表佔用的記憶體
使用ALTER TABLE ENGINE=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的值，除非你希望所有session建立的記憶體表都使用這個值)

mysql> SET max_heap_table_size = 1024*1024;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE t1 (id INT, UNIQUE(id)) ENGINE = MEMORY;
Query OK, 0 rows affected (0.01 sec)

mysql> SET max_heap_table_size = 1024*1024*2;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE t2 (id INT, UNIQUE(id)) ENGINE = MEMORY;
Query OK, 0 rows affected (0.00 sec)

當服務重啟後，兩個表都將使用全域性max_heap_table_size
在建立MEMORY表時，也可指定MAX_ROW來限制儲存的最大記錄，該選項仍然作為限制表最大尺寸的約束，但不會讓表增長超過max_heap_table_size的值，需要將MAX_ROW與max_heap_table_size均合理設定。

參見
http://dev.mysql.com/doc/refman/5.6/en/memory-storage-engine.html
http://www.nowamagic.net/librarys/veda/detail/1405

整理自網路

svoid
2014-09-16