mysql常用儲存引擎(InnoDB、MyISAM、MEMORY、MERGE、ARCHIVE)介紹與如何選擇

一：前言

    資料庫儲存引擎是資料庫底層軟體組織，資料庫管理系統（DBMS）使用資料引擎進行建立、查詢、更新和刪除資料。不同的儲存引擎提供不同的儲存機制、索引技巧、鎖定水平等功能，使用不同的儲存引擎，還可以 獲得特定的功能。現在許多不同的資料庫管理系統都支援多種不同的資料引擎。MySql的核心就是儲存引擎。

     什麼是儲存引擎？

    MySQL中的資料用各種不同的技術儲存在檔案(或者記憶體)中。這些技術中的每一種技術都使用不同的儲存機制、索引技巧、鎖定水平並且最終提供廣泛的不同的功能和能力。通過選擇不同的技術，你能夠獲得額外的速度或者功能，從而改善你的應用的整體功能。

    例如，如果你在研究大量的臨時資料，你也許需要使用記憶體MySQL儲存引擎。記憶體儲存引擎能夠在記憶體中儲存所有的表格資料。又或者，你也許需要一個支援事務處理的資料庫(以確保事務處理不成功時資料的回退能力)。

    這些不同的技術以及配套的相關功能在 MySQL中被稱作儲存引擎(也稱作表型別)。 MySQL預設配置了許多不同的儲存引擎，可以預先設定或者在MySQL伺服器中啟用。你可以選擇適用於伺服器、資料庫和表格的儲存引擎，以便在選擇如何儲存你的資訊、如何檢索這些資訊以及你需要你的資料結合什麼效能和功能的時候為你提供最大的靈活性。

二：儲存引擎檢視相關

 -- 檢視資料庫支援的儲存引擎

show engines;

    得到的結果為下圖（Support列的值表示某種引擎是否能使用：YES表示可以使用、NO表示不能使用、DEFAULT表示該引擎為當前預設的儲存引擎 。）：

-- 檢視mysql資料庫預設引擎

show variables like '%storage_engine%';

    得到結果為下圖，可以看到我的mysql資料庫預設使用的是InnoDB儲存引擎：

-- 檢視錶建立語句，可以獲得表的建立資訊，引擎、預設字元、約束等

show create table zd_grade;

    執行上面語句獲得該表的預設資料：

CREATE TABLE `zd_grade` (
  `ID` char(8) NOT NULL DEFAULT '' COMMENT '學號',
  `Course_ID` char(8) NOT NULL DEFAULT '' COMMENT '課程號',
  `C_Grade` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '中方分數',
  `F_Grade` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '外方分數',
  `Exam_Time` date NOT NULL DEFAULT '0000-00-00' COMMENT '考試日期',
  `Semister` varchar(255) NOT NULL DEFAULT '' COMMENT '學期',
  `Grade_Type` char(2) NOT NULL DEFAULT '' COMMENT '成績型別',
  PRIMARY KEY (`ID`,`Course_ID`,`Grade_Type`),
  KEY `index_grade_id` (`ID`) USING HASH
) ENGINE=InnoDB DEFAULT CHARSET=utf8

    在MySQL中，不需要在整個伺服器中使用同一種儲存引擎，針對具體的要求，可以對每一個表使用不同的儲存引擎。 下面來看一下其中幾種常用的引擎（InnoDB、MyISAM、MEMORY、MERGE、ARCHIVE）。

三：幾種常用的引擎（InnoDB、MyISAM、MEMORY、MERGE、ARCHIVE）

InnoDB儲存引擎

     InnoDB是事務型資料庫的首選引擎，支援事務安全表（ACID），支援行鎖定和外來鍵，上圖也看到了，InnoDB是預設的MySQL引擎。

     InnoDB 採用MVCC（多版本併發控制）來支援高併發，並實現了四個標準的隔離級別。其預設級別是REPEATABLE READ（可重複讀），並且通過間隙鎖（next-key locking）策略防止幻讀的出現。間隙鎖是的 InnoDB 不僅僅鎖定查詢涉及的行，還會對索引中的間隙進行鎖定，以防止幻影行的插入。

    InnoDB 表是基於聚簇索引建立的。InnoDB 的索引結構和 MySQL 的其他儲存引擎有很大不同，聚簇索引對主鍵查詢有很高的效能。不過它的二級索引（secondary index，非主鍵索引）中必須包含主鍵列，所以如果主鍵很大的話，其他的所有索引都會很大。因此，若表上的索引較多的話，主鍵應當儘可能的小。

    InnoDB不建立目錄，使用InnoDB時，MySQL將在MySQL資料目錄下建立一個名為ibdata1的10MB大小的自動擴充套件資料檔案，以及兩個名為ib_logfile0和ib_logfile1的5MB大小的日誌檔案。

InnoDB主要特性有：

1. InnoDB給MySQL提供了具有提交、回滾和崩潰恢復能力的事物安全（ACID相容）儲存引擎。InnoDB鎖定在行級並且也在SELECT語句中提供一個類似Oracle的非鎖定讀。這些功能增加了多使用者部署和效能。在SQL查詢中，可以自由地將InnoDB型別的表和其他MySQL的表型別混合起來，甚至在同一個查詢中也可以混合
2. InnoDB是為處理巨大資料量的最大效能設計。它的CPU效率可能是任何其他基於磁碟的關係型資料庫引擎鎖不能匹敵的
3. InnoDB儲存引擎完全與MySQL伺服器整合，InnoDB儲存引擎為在主記憶體中快取資料和索引而維持它自己的緩衝池。InnoDB將它的表和索引在一個邏輯表空間中，表空間可以包含數個檔案（或原始磁碟檔案）。這與MyISAM表不同，比如在MyISAM表中每個表被存放在分離的檔案中。InnoDB表可以是任何尺寸，即使在檔案尺寸被限制為2GB的作業系統上
4. InnoDB支援外來鍵完整性約束，儲存表中的資料時，每張表的儲存都按主鍵順序存放，如果沒有顯示在表定義時指定主鍵，InnoDB會為每一行生成一個6位元組的ROWID，並以此作為主鍵
5. InnoDB被用在眾多需要高效能的大型資料庫站點上

MyISAM儲存引擎

     MyISAM基於ISAM儲存引擎，並對其進行擴充套件。它是在Web、資料倉儲和其他應用環境下最常使用的儲存引擎之一。MyISAM擁有較高的插入、查詢速度，但不支援事務。

     MyISAM表格可以被壓縮，而且它們支援全文搜尋。不支援事務，而且也不支援外來鍵。如果事物回滾將造成不完全回滾，不具有原子性。在進行updata時進行表鎖，併發量相對較小。如果執行大量的SELECT，MyISAM是更好的選擇。

    MyISAM的索引和資料是分開的，並且索引是有壓縮的，記憶體使用率就對應提高了不少。能載入更多索引，而Innodb是索引和資料是緊密捆綁的，沒有使用壓縮從而會造成Innodb比MyISAM體積龐大。

    MyISAM快取在記憶體的是索引，不是資料。而InnoDB快取在記憶體的是資料，相對來說，伺服器記憶體越大，InnoDB發揮的優勢越大。

MyISAM主要特性有：

1. 大檔案（達到63位檔案長度）在支援大檔案的檔案系統和作業系統上被支援
2. 當把刪除和更新及插入操作混合使用的時候，動態尺寸的行產生更少碎片。這要通過合併相鄰被刪除的塊，以及若下一個塊被刪除，就擴充套件到下一塊自動完成
3. 每個MyISAM表最大索引數是64，這可以通過重新編譯來改變。每個索引最大的列數是16
4. 最大的鍵長度是1000位元組，這也可以通過編譯來改變，對於鍵長度超過250位元組的情況，一個超過1024位元組的鍵將被用上
5. BLOB和TEXT列可以被索引
6. NULL被允許在索引的列中，這個值佔每個鍵的0~1個位元組
7. 所有數字鍵值以高位元組優先被儲存以允許一個更高的索引壓縮
8. 每個MyISAM型別的表都有一個AUTO_INCREMENT的內部列，當INSERT和UPDATE操作的時候該列被更新，同時AUTO_INCREMENT列將被重新整理。所以說，MyISAM型別表的AUTO_INCREMENT列更新比InnoDB型別的AUTO_INCREMENT更快
9. 可以把資料檔案和索引檔案放在不同目錄
10. 每個字元列可以有不同的字符集
11. 有VARCHAR的表可以固定或動態記錄長度
12. VARCHAR和CHAR列可以多達64KB

    使用MyISAM引擎建立資料庫，將產生3個檔案。檔案的名字以表名字開始，副檔名之處檔案型別：frm檔案儲存表定義、資料檔案的副檔名為.MYD（MYData）、索引檔案的副檔名時.MYI（MYIndex）

MEMORY儲存引擎

    使用MySQL Memory儲存引擎的出發點是速度。為得到最快的響應時間，採用的邏輯儲存介質是系統記憶體。雖然在記憶體中儲存表資料確實會提供很高的效能，但當mysqld守護程式崩潰時，所有的Memory資料都會丟失。獲得速度的同時也帶來了一些缺陷。它要求儲存在Memory資料表裡的資料使用的是長度不變的格式，這意味著不能使用BLOB和TEXT這樣的長度可變的資料型別，VARCHAR是一種長度可變的型別，但因為它在MySQL內部當做長度固定不變的CHAR型別，所以可以使用。MEMORY主要特性有：

1. MEMORY表的每個表可以有多達32個索引，每個索引16列，以及500位元組的最大鍵長度
2. MEMORY儲存引擎執行HASH和BTREE縮影
3. 可以在一個MEMORY表中有非唯一鍵值
4. MEMORY表使用一個固定的記錄長度格式
5. MEMORY不支援BLOB或TEXT列
6. MEMORY支援AUTO_INCREMENT列和對可包含NULL值的列的索引
7. MEMORY表在所由客戶端之間共享（就像其他任何非TEMPORARY表）
8. MEMORY表記憶體被儲存在記憶體中，記憶體是MEMORY表和伺服器在查詢處理時的空閒中，建立的內部表共享
9. 當不再需要MEMORY表的內容時，要釋放被MEMORY表使用的記憶體，應該執行DELETE FROM或TRUNCATE TABLE，或者刪除整個表（使用DROP TABLE）

MERGE儲存引擎

    MERGE儲存引擎是一組MyISAM表的組合，這些MyISAM表結構必須完全相同，儘管其使用不如其它引擎突出，但是在某些情況下非常有用。說白了，Merge表就是幾個相同MyISAM表的聚合器；Merge表中並沒有資料，對Merge型別的表可以進行查詢、更新、刪除操作，這些操作實際上是對內部的MyISAM表進行操作。

    主要應用於伺服器日誌這種資訊，一般常用的儲存策略是將資料分成很多表，每個名稱與特定的時間端相關。例如：可以用12個相同的表來儲存伺服器日誌資料，每個表用對應各個月份的名字來命名。當有必要基於所有12個日誌表的資料來生成報表，這意味著需要編寫並更新多表查詢，以反映這些表中的資訊。與其編寫這些可能出現錯誤的查詢，不如將這些表合併起來使用一條查詢，之後再刪除Merge表，而不影響原來的資料，刪除Merge表只是刪除Merge表的定義，對內部的表沒有任何影響。

    例如：設你有幾個日誌資料表，他們內容分別是這幾年來每一年的日誌記錄項，他們的定義都是下面這樣，YY代表年份：

 

CREATE TABLE log_YY
(
    dt DATETIME NOT NULL,
    info VARCHAR(100) NOT NULL,
    INDEX (dt)
) ENGINE = MyISAM;

    假設日誌資料表的當前集合包括 log_2004、log_2005、log_2006、log_2007 ，而你可以建立一個如下所示的MERGE資料表把他們歸攏為一個邏輯單元：

 

CREATE TABLE log_merge
(
    dt DATETIME NOT NULL,
    info VARCHAR(100) NOT NULL,
    INDEX(dt)
) ENGINE = MERGE UNION = (log_2004, log_2005, log_2006, log_2007);

     ENGINE選項的值必須是MERGE，UNION選項列出了將被收錄在這個MERGE資料表離得各有關資料表。把這個MERGE建立出來後，就可以像對待任何其他資料表那樣查詢它，只是每一次查詢都將同時作用與構成它的每一個成員資料表 。

    下面這個查詢可以讓我們知道上述幾個日誌資料表的資料行的總數：

SELECT COUNT（*） FROM log_merge;

 下面這個查詢用來確定在這幾年裡每年各有多少日誌記錄項：

SELECT YEAR(dt) AS y, COUNT(*) AS entries FROM log_merge GROUP BY y;

    除了便於同時引用多個資料表而無需發出多條查詢，MERGE資料表還提供了以下一些便利。

• MERGE資料表可以用來建立一個尺寸超過各個MyISAM資料表所允許的最大長度邏輯單元

• 你看一把經過壓縮的資料表包括到MERGE資料表裡。比如說，在某一年結束之後，你應該不會再往相應的日誌檔案裡新增記錄，所以你可以用myisampack工具壓縮它以節省空間，而MERGE資料表仍可以像往常那樣工作

    MERGE資料表也支援DELETE 和UPDATE操作。INSERT操作比較麻煩，因為MySQL需要知道應該把新資料行插入到哪一個成員表裡去。在MERGE資料表的定義裡可以包括一個INSERT_METHOD選項，這個選項的可取值是NO、FIRST、LAST，他們的含義依次是INSERT操作是被禁止的、新資料行將被插入到現在UNION選項裡列出的第一個資料表或最後一個資料表。比如說，以下定義將對log_merge資料表的INSERT操作被當作對log_2007資料表----它是UNION選項所列出的最後一個資料表：

 

CREATE TABLE log_merge
(
    dt DATETIME NOT NULL,
    info VARCHAR(100) NOT NULL,
    INDEX(dt)
) ENGINE = MERGE UNION = (log_2004, log_2005, log_2006, log_2007)

INSERT_METHOD = LAST;

    建立一個新的成員資料表log_2009並讓他有同樣的表結構，然後修改log_merge資料表把log_2009包括進來：

log_2009:

CREATE TABLE log_2009 LIKE log_2008;

ALTER TABLE log_merge

UNION = (log_2004, log_2005, log_2006, log_2007,log_2008,log_2009);

ARCHIVE儲存引擎

    Archive是歸檔的意思，在歸檔之後很多的高階功能就不再支援了，僅僅支援最基本的插入和查詢兩種功能。在MySQL 5.5版以前，Archive是不支援索引，但是在MySQL 5.5以後的版本中就開始支援索引了。Archive擁有很好的壓縮機制，它使用zlib壓縮庫，在記錄被請求時會實時壓縮，所以它經常被用來當做倉庫使用。

四：儲存引擎的選擇

不同的儲存引擎都有各自的特點，以適應不同的需求，如下表所示：

功  能	MYISAM	Memory	InnoDB	Archive
儲存限制	256TB	RAM	64TB	None
支援事物	No	No	Yes	No
支援全文索引	Yes	No	No	No
支援樹索引	Yes	Yes	Yes	No
支援雜湊索引	No	Yes	No	No
支援資料快取	No	N/A	Yes	No
支援外來鍵	No	No	Yes	No

1. 如果要提供提交、回滾、崩潰恢復能力的事物安全（ACID相容）能力，並要求實現併發控制，InnoDB是一個好的選擇。
2. 如果資料表主要用來插入和查詢記錄，則MyISAM引擎能提供較高的處理效率。並且，如果你的應用程式對查詢效能要求較高，就要使用MYISAM了。MYISAM索引和資料是分開的，而且其索引是壓縮的，可以更好地利用記憶體。所以它的查詢效能明顯優於INNODB。壓縮後的索引也能節約一些磁碟空間。MYISAM擁有全文索引的功能，這可以極大地優化LIKE查詢的效率。
3. 如果只有INSERT和SELECT操作，可以選擇Archive，Archive支援高併發的插入操作，但是本身不是事務安全的。Archive非常適合儲存歸檔資料，如記錄日誌資訊可以使用Archive。
4. 對日誌的一些綜合操作，通常使用的是MERGE儲存引擎。
5. 一般在以下幾種情況下使用Memory儲存引擎：

      1）目標資料較小，而且被非常頻繁地訪問。在記憶體中存放資料，所以會造成記憶體的使用，可以通過引數max_heap_table_size控制Memory表的大小，設定此引數，就可以限制Memory表的最大大小。

          2）如果資料是臨時的，而且要求必須立即可用，那麼就可以存放在記憶體表中。

          3）儲存在Memory表中的資料如果突然丟失，不會對應用服務產生實質的負面影響。

        4）如果只是臨時存放資料，資料量不大，並且不需要較高的資料安全性，可以選擇將資料儲存在記憶體中的Memory引擎，MySQL中使用該引擎作為臨時表，存放查詢的中間結果。

五：總結

    在對資料庫進行設計時，使用哪一種引擎需要靈活選擇，一個資料庫中不同表可以使用不同引擎以滿足各種效能和實際需求，使用合適的儲存引擎，將會大大提高整個資料庫的效能。

參考部落格：https://www.cnblogs.com/wcwen1990/p/6655416.html