MySQL MEMORY引擎及性能比對(duì)
同事咨詢MySQL MEMORY引擎的細(xì)節(jié)，能否滿足需求。沒有太多了解，這里做個(gè)系統(tǒng)總結(jié)。
MEMORY存儲(chǔ)引擎創(chuàng)建的表數(shù)據(jù)只能保存在內(nèi)存。
MySQL宕機(jī)、硬件故障或者意外掉電，都會(huì)造成MEMORY引擎表丟失數(shù)據(jù)。所以，MEMORY表中的數(shù)據(jù)來源于其他表(可落盤永久保存)用于只讀適用，或者用于臨時(shí)工作起到數(shù)據(jù)周轉(zhuǎn)。
MEMORY 存儲(chǔ)引擎特性
[a] Implemented in the server (via encryption functions). Data-at-rest tablespace encryption is available in MySQL 5.7 and later.
[b] Implemented in the server, rather than in the storage engine.
[c] Implemented in the server, rather than in the storage engine.
來源： https://dev.mysql.com/doc/refman/5.6/en/memory-storage-engine.html
何時(shí)使用MEMORY
- 臨時(shí)使用、不重要的數(shù)據(jù)，例如網(wǎng)站的會(huì)話管理和緩存。可接受數(shù)據(jù)丟失。
- 發(fā)揮其訪問快、低延遲特性
- 只讀或者大部分是讀操作，不適合大量寫操作。
性能特性
內(nèi)存表受限于單線程執(zhí)行和表級(jí)鎖引起的爭(zhēng)用。在負(fù)載增加時(shí)限制了可擴(kuò)展性，特別是寫操作。
盡管內(nèi)存表在內(nèi)存中處理，在繁忙的服務(wù)網(wǎng)上，并不一定比InnoDB表更快，如一般查詢或者讀寫場(chǎng)景。特別是，多個(gè)會(huì)話執(zhí)行update操作會(huì)造成性能低下。
根據(jù)不同的SQL查詢，需要?jiǎng)?chuàng)建內(nèi)存表的默認(rèn)hash索引(基于唯一鍵的單個(gè)值查詢)，或者B-tree索引(等值查詢、不等值查詢或者范圍查詢)。
內(nèi)存表的物理特性
每張內(nèi)存表會(huì)在硬盤上創(chuàng)建一個(gè)文件，用于保存表結(jié)構(gòu)(沒有數(shù)據(jù))。文件名為以內(nèi)存表名開始，.frm結(jié)尾。
特性
- 內(nèi)存表的空間使用小塊(small block)。表使用100%動(dòng)態(tài)hash插入。已刪除的行放到空閑列表中，下次插入新數(shù)據(jù)會(huì)被使用。
- 使用定長(zhǎng)的行存儲(chǔ)格式。變長(zhǎng)類型存入內(nèi)存表需轉(zhuǎn)換為定長(zhǎng)。
- 不支持BLOB、TEXT類型的列。
- 內(nèi)存表支持自增列(AUTO_INCREMENT)
- 不能在多個(gè)會(huì)話上共享臨時(shí)表。
內(nèi)存表的DDL操作
創(chuàng)建臨時(shí)表
- CREATE TABLE t (i INT) ENGINE = MEMORY;
基于非內(nèi)存表創(chuàng)建內(nèi)存表，并將數(shù)據(jù)拉入到內(nèi)存表中
- mysql> CREATE TABLE test ENGINE=MEMORY
- -> SELECT ip,SUM(downloads) AS down
- -> FROM log_table GROUP BY ip;
- mysql> SELECT COUNT(ip),AVG(down) FROM test;
- mysql> DROP TABLE test;
內(nèi)存表的最大受限于 max_heap_table_size 參數(shù)，默認(rèn)為16MB。根據(jù)場(chǎng)景需自己調(diào)整該參數(shù)。
索引
MEMORY存儲(chǔ)引擎支持HASH和BTREE索引。
- CREATE TABLE lookup
- (id INT, INDEX USING HASH (id))
- ENGINE = MEMORY;
- CREATE TABLE lookup
- (id INT, INDEX USING BTREE (id))
- ENGINE = MEMORY;
每張內(nèi)存表可創(chuàng)建64個(gè)索引，每個(gè)索引最大支持16個(gè)列，一個(gè)key的長(zhǎng)度最大值為3072bytes。
如果一個(gè)內(nèi)存表hash索引的鍵值有很高的重復(fù)度，更新鍵值、刪除操作速度都會(huì)顯著降低。這種速度下降的程度與鍵值重復(fù)度成正比。您可以使用BTREE索引來規(guī)避這個(gè)問題。
內(nèi)存表可以有非唯一鍵(這是hash索引不常用的功能)。
索引列中可包含NULL值。
加載數(shù)據(jù)
MySQL啟動(dòng)時(shí)，加入--init-file選項(xiàng)，將下列命令加入到這個(gè)文件中，保證啟動(dòng)后內(nèi)存表中有數(shù)據(jù)。
- INSERT INTO ... SELECT
- LOAD DATA INFILE
內(nèi)存表和復(fù)制(Replication)
服務(wù)器重啟會(huì)導(dǎo)致內(nèi)存表數(shù)據(jù)丟失。如果是主庫，從庫沒有意識(shí)到主庫表中數(shù)據(jù)已被情況，所以在從庫你看到的是過期數(shù)據(jù)。
重啟后，主從庫如何同步內(nèi)存表數(shù)據(jù)?
當(dāng)主庫使用內(nèi)存表，主庫啟動(dòng)后，一條DELETE語句會(huì)寫入到主庫的binary log中，從庫接到命令后清空內(nèi)存表。
主庫重啟期間，從庫還是有讀取到過期數(shù)據(jù)的情況。為了避免這種情況，主庫啟動(dòng)時(shí)加入--init-file參數(shù)，這樣主庫啟動(dòng)自動(dòng)將數(shù)據(jù)加載到內(nèi)存表中。(官方文檔寫的不嚴(yán)謹(jǐn)，我認(rèn)為僅限于內(nèi)存表只讀場(chǎng)景。如果不是只讀，即使加入--init-file參數(shù)也無法保證主庫內(nèi)存表數(shù)據(jù)一致)。
管理內(nèi)存使用情況
服務(wù)器必須有足夠內(nèi)存，來滿足多張內(nèi)存表的使用。
如果從內(nèi)存表中刪除單獨(dú)的行，并不會(huì)回收內(nèi)存。當(dāng)整個(gè)內(nèi)存表刪除時(shí)，才回收內(nèi)存。同一張內(nèi)存表，之前刪除行占用的空間，會(huì)被新的行復(fù)用。執(zhí)行DELETE、TRUNCATE TABLE來釋放內(nèi)存表占用空間，如果表不在使用可使用DROP TABLE命令。釋放正在使用的內(nèi)存表占用的內(nèi)存，可以使用 ALTER TABLE XX ENGINE=MEMORY 強(qiáng)制重建表。
內(nèi)存表1行記錄占用內(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()表示一個(gè)round-up因子，其會(huì)導(dǎo)致行長(zhǎng)度等于char指針大小的精確倍數(shù)。sizeof(char*)在32位機(jī)器上是4,64位則為8。
前面提到, max_heap_table_size 系統(tǒng)變量決定了內(nèi)存表的最大尺寸。在創(chuàng)建內(nèi)存之前，可設(shè)置該變量控制每個(gè)內(nèi)存表的最大尺寸。(不建議修改全局 max_heap_table_size 的大小，否則所有會(huì)話的內(nèi)存表最大尺寸都為該值 )。
下面的例子創(chuàng)建了2個(gè)內(nèi)存表，最大尺寸為1M和2M
- 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)
服務(wù)器重啟后，每個(gè)表的尺寸都變成了全局 max_heap_table_size 變量的值大小。
壓測(cè)結(jié)果
通過sysbench壓測(cè)，InnoDB 寫性能完爆MEMORY引擎，MEMORY的讀性能略高于InnoDB。InnoDB的綜合性能更好，足夠滿足日常使用。考慮到MEMORY引擎的雷點(diǎn)太多，建議不使用MEMORY引擎。
