【轉載】如何選擇MySQL存儲引擎

一、MySQL的存儲引擎

完整的引擎說明還是看官方文檔：http://dev.mysql.com/doc/refman/5.6/en/storage-engines.html

這裡介紹一些主要的引擎

1、InnoDB存儲引擎

InnoDB是MySQL的預設事務型引擎，它被設計用來處理大量的短期(short-lived)事務。除非有非常特別的原因需要使用其他的存儲引擎，否則應該優先考慮InnoDB引擎。

建議使用MySQL5.5及以後的版本，因為這個版本及以後的版本的InnoDB引擎性能更好。

MySQL4.1以後的版本中，InnoDB可以將每個表的數據和索引存放在單獨的文件中。這樣在複製備份崩潰恢復等操作中有明顯優勢。可以通過在my.cnf中增加innodb_file_per_table來開啟這個功能。如下：

1. [mysqld]  
2. innodb_file_per_table  

InnoDB採用MVCC來支持高併發，並且實現了四個標準的隔離級別。其預設級別是REPEATABLE READ(可重覆讀)，並且通過間隙鎖(next-key locking)策略防止幻讀的出現。(事務和事務隔離級別是另一個大題目，各自網補吧)。

InnoDB是基於聚簇索引建立的，聚簇索引對主鍵查詢有很高的性能。不過它的二級索引(secondary index，非主鍵索引)中必須包含主鍵列，所以如果主鍵列很大的話，其他的所有索引都會很大。因此表上的索引較多的話，主鍵應當儘可能的小。

InnoDB的存儲格式是平臺獨立的，可以將數據和索引文件從Intel平臺複製到Sun SPARC平臺或其他平臺。

InnoDB通過一些機制和工具支持真正的熱備份，MySQL的其他存儲引擎不支持熱備份。

2、MyISAM存儲引擎

MyISAM提供了大量的特性，包括全文索引、壓縮、空間函數(GIS)等，但MyISAM不支持事務和行級鎖，有一個毫無疑問的缺陷就是崩潰後無法安全恢復。

MyISAM會將表存儲在兩個文件在中：數據文件和索引文件，分別是.MYD和.MYI為擴展名。

在MySQL5.0以前，只能處理4G的數據，5.0中可以處理256T的數據。

在數據不再進行修改操作時，可以對MyISAM表進行壓縮，壓縮後可以提高讀能力，原因是減少了磁碟I/O。

3、Archive引擎

Archive存儲引擎只支持INSERT和SELECT操作，在MySQL5.1之前不支持索引。

Archive表適合日誌和數據採集類應用。

Archive引擎支持行級鎖和專用的緩存區，所以可以實現高併發的插入，但它不是一個事物型的引擎，而是一個針對高速插入和壓縮做了優化的簡單引擎。

4、Blackhole引擎

Blackhole引擎沒有實現任何存儲機制，它會丟棄所有插入的數據，不做任何保存。但伺服器會記錄Blackhole表的日誌，所以可以用於複製數據到備庫，或者簡單地記錄到日誌。但這種應用方式會碰到很多問題，因此並不推薦。

5、CSV引擎

CSV引擎可以將普通的SCV文件作為MySQL的表來處理，但不支持索引。

CSV引擎可以作為一種數據交換的機制，非常有用。

6、Federated引擎

Federated引擎是訪問其他MySQL伺服器的一個代理，儘管該引擎看起來提供了一種很好的跨伺服器的靈活性，但也經常帶來問題，因此預設是禁用的。

7、Memory引擎

如果需要快速地訪問數據，並且這些數據不會被修改，重啟以後丟失也沒有關係，那麼使用Memory表是非常有用。Memory表至少比MyISAM表要快一個數量級。

Memory表是表級鎖，因此併發寫入的性能較低。它不支持BLOB或TEXT類型的列，並且每行的長度是固定的，這可能呆滯部分記憶體的浪費。

臨時表和Memory表不是一回事。臨時表是指使用CREATE TEMPORARY TABLE語句創建的表，它可以使用任何存儲引擎，只在單個連接中可見，當連接斷開時，臨時表也將不復存在。

8、NDB集群引擎

MySQL伺服器、NDB集群存儲引擎，以及分散式的、share-nothing的、容災的、高可用的NDB資料庫的組合，被稱為MySQL集群(MySQL Cluster)。

其他第三方或社區引擎

XtraDB：是InnoDB的一個改進版本，可以作為InnoDB的一個完美的替代產品。

TokuDB：使用了一種新的叫做分形樹(Fractal Trees)的索引數據結構。

Infobright：是最有名的面向列的存儲引擎。

Groonga：是一款全文索引引擎。

OQGraph：該引擎由Open Query研發，支持圖操作(比如查找兩點之間的最短路徑)。

Q4M：該引擎在MySQL內部實現了隊列操作。

SphinxSE：該引擎為Sphinx全文索引搜索伺服器提供了SQL介面。

二、選擇合適的引擎

大部分情況下，InnoDB都是正確的選擇，可以簡單地歸納為一句話“除非需要用到某些InnoDB不具備的特性，並且沒有其他辦法可以替代，否則都應該優先選擇InnoDB引擎”。

除非萬不得已，否則建議不要混合使用多種存儲引擎，否則可能帶來一系列負責的問題，以及一些潛在的bug和邊界問題。

如果應用需要不同的存儲引擎，請先考慮以下幾個因素：

事務：

    如果應用需要事務支持，那麼InnoDB(或者XtraDB)是目前最穩定並且經過驗證的選擇。

備份：

    如果可以定期地關閉伺服器來執行備份，那麼備份的因素可以忽略。反之，如果需要線上熱備份，那麼選擇InnoDB就是基本的要求。

崩潰恢復

    MyISAM崩潰後發生損壞的概率比InnoDB要高很多，而且恢復速度也要慢。

特有的特性

    如果一個存儲引擎擁有一些關鍵的特性，同時卻又缺乏一些必要的特性，那麼有時候不得不做折中的考慮，或者在架構設計上做一些取捨。

有些查詢SQL在不同的引擎上表現不同。比較典型的是：

SELECT COUNT(*) FROM table;

對於MyISAM確實會很快，但其他的可能都不行。

三、應用舉例

1、日誌型應用

MyISAM或者Archive存儲引擎對這類應用比較合適，因為他們開銷低，而且插入速度非常快。

如果需要對記錄的日誌做分析報表，生成報表的SQL很可能會導致插入效率明顯降低，這時候該怎麼辦？

一種解決方法，是利用MySQL內置的複製方案將數據複製一份到備庫，然後在備庫上執行比較消耗時間和CPU的查詢。當然也可以在系統負載較低的時候執行報表查詢操作，但應用在不斷變化，如果依賴這個策略可能以後會導致問題。

另一種方法，在日誌記錄表的名字中包含年和月的信息，這樣可以在已經沒有插入操作的歷史表上做頻繁的查詢操作，而不會幹擾到最新的當前表上的插入操作。

2、只讀或者大部分情況下只讀的表

有些表的數據用於編製類目或者分列清單（如工作崗位），這種應用場景是典型的讀多寫少的業務。如果不介意MyISAM的崩潰恢復問題，選用MyISAM引擎是合適的。（MyISAM只將數據寫到記憶體中，然後等待操作系統定期將數據刷出到磁碟上）

3、訂單處理

涉及訂單處理，支持事務是必要的，InnoDB是訂單處理類應用的最佳選擇。

4、大數據量

如果數據增長到10TB以上的級別，可能需要建立數據倉庫。Infobright是MySQL數據倉庫最成功的方案。也有一些大資料庫不適合Infobright，卻可能適合TokuDB。

下麵是常用存儲引擎的適用環境：

1. MyISAM：預設的MySQL插件式存儲引擎，它是在Web、數據倉儲和其他應用環境下最常使用的存儲引擎之一
2. InnoDB：用於事務處理應用程式，具有眾多特性，包括ACID事務支持。
3. Memory：將所有數據保存在RAM中，在需要快速查找引用和其他類似數據的環境下，可提供極快的訪問。
4. Merge：允許MySQL DBA或開發人員將一系列等同的MyISAM表以邏輯方式組合在一起，並作為1個對象引用它們。對於諸如數據倉儲等VLDB環境十分適合。