如何選擇mysql儲存引擎

標籤：索引   外掛程式   儲存   崩潰恢複   隊列   hive   業務   not   系統負載   

一、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環境十分適合。

如何選擇mysql儲存引擎