MySQL存儲引擎（表類型）的差別和選擇

• • 1. MyISAM
  • 2. InnoDB
  • 3. MEMORY
  • 4. MERGE
  • 5. TokuDB
  • 6. 如何選擇

1. MyISAM

MyISAM是MySQL預設的存儲引擎，不支援事務、也不支援外鍵，其優勢是通路速度快，對事務完整性沒有要求或者以SELECT、INSECT為主的應用基本上都可以使用這個引擎來建立表

每個MyISAM在磁盤上存儲成3個檔案，其檔案名都和表名相同，但擴充名分别是：.frm（存儲表定義）；.MYD（存儲資料）；.MYI（存儲索引）

MyISAM的表支援3種不同的存儲格式，分别是靜态表、動态表和壓縮表

1. 靜态表是預設的存儲格式，表中的字段都是非變長字段，存儲非常迅速，容易緩存，出現故障容易恢複；但是占用空間通常比動态表多。靜态表在存儲時慧按照列的寬度定義補足空格，但是在應用通路時并不會得到這些空格，這些空格在傳回給應用之前已經去掉。是以，當需要儲存的内容後面本來就帶有空格時，在傳回結果的時候也會被去掉，開發時需要注意（内容前的空格不會被去掉）
2. 動态表包含變長字段，占用的空間相對較少，但是頻繁更新和删除記錄會産生碎片，需要定期執行optimize table語句或myisamchk-r指令來改善性能，并且在出現故障時恢複相對比較困難
3. 壓縮表由myisampack工具建立，占據非常小的空間。因為每個記錄是被單獨壓縮的，是以隻有非常小的通路開支

2. InnoDB

InnoDB存儲引擎提供了具有送出、復原和崩潰恢複能力的事務安全。但是對比MyISAM，InnoDB寫的處理效率差一些，并且會占用更多的磁盤空間以保留資料和索引。下面是InnoDB在使用過程中不用于其他存儲引擎的特點

1. 自動增長列

   InnoDB的自動增長列可以手工插入，但是插入的值如果是空或是0，則實際插入的将是自動增長後的值。并且，自動增長列必須是索引；如果是組合索引，也必須是組合索引的第一列，但是對于MyISAM表，自動增長列可以是組合索引的其他列，插入記錄後自動增長列是按照組合索引的前面幾列進行排序後遞增的

2. 外鍵限制

   MySQL支援外鍵限制的存儲引擎隻有InnoDB，在建立外鍵的時候，要求父表必須有對應的索引，子表在建立外鍵的時候也會自動建立對應的索引。因為在插入或删除時的外鍵限制，必須先删除子表的内容再删除父表，必須先插入父表的内容再插入子表，在導入多個表的資料時，可以通過“SET FOREIGN_KEY_CHECKS = 0;”來暫時關閉外鍵限制來加快處理的速度，執行完成之後，再執行“SET FOREIGN_KEY_CHECKS = 1;”語句改回原狀态

3. 存儲方式

   1） 共享表空間存儲

   2） 多表空間存儲

3. MEMORY

使用存在于記憶體中的内容來建立表，每個MEMORY表隻實際對應一個磁盤檔案，格式是.frm。MEMORY類型的表通路非常快，因為它的資料是放在記憶體中（沒有寫入到磁盤），并且預設使用HASH索引，但是一旦服務關閉，表中的資料就會丢失掉

伺服器需要足夠記憶體來維持所有在同一時間使用的MEMORY表，當不在需要MEMORY表的内容時，要釋放掉表使用的記憶體

每個MEMORY表中可以放置的資料量的大小受到系統變量的限制（初始值是16MB，可以根據需要加大）

MEMORY主要用于那些内容變化不頻繁的代碼表，或者作為統計操作的中間結果表，便于高效地對中間結果進行分析并得到最終的統計結果

4. MERGE

MERGE存儲引擎是一組MyISAM表的組合，這些MyISAM表必須結構完全相同，MERGE表本身并沒有資料，對MERGE類型的表可以進行查詢、更新、删除操作，這些操作實際上是對内部的MyISAM表進行的。對于MERGE類型表的插入操作，是通過INSERT_METHOD子句定義插入的表，可以是FIRST/LAST表示相應的插入到第一個表或者最後一個表，不定義這個子句或者定義未NO，表示不能對這個MERGE表執行插入操作

可以對MERGE表進行DROP操作，這個操作隻是删除MERGE的定義，對内部的表沒有任何影響

5. TokuDB

前面4種都是MySQL自帶的存儲引擎，除此還有一些常見的第三方引擎，比如列式存儲引擎Infobright、高寫性能高壓縮的TokuDB

TokuDB是一個高性能、支援事務處理的MySQL和MariaDB的存儲引擎，具有高拓展性、高壓縮率、高效的寫入性能，支援大多是DDL操作

• 使用Fractal樹索引保證高效的插入性能
• 優秀的壓縮特性，比InnoDB高近10倍
• Hot Schema Changes特性支援線上建立索引和添加、删除屬性列等DDL操作
• 使用Bulk Loader達到快速加載大量資料
• 提供了主從延遲消除技術
• 支援ACID和MVCC

适用場景：

• 日志資料，因為日志通常插入頻繁且存儲量大
• 曆史資料，通常不會再有寫入操作，可以利用TokuDB的高壓縮特性進行存儲
• 線上DDL較頻繁的場景，使用TokuDB可以大大增加系統的可用性

6. 如何選擇

• MyISAM：如果應用是以讀操作和插入操作為主，隻是很少的更新和删除操作，并且對事務的完整性、并發行要求不是很高，選擇MyISAM
• InnoDB：如果應用對事務的完整性有比較高的要求，在并發條件下要求資料的一緻性，資料操作除了插入和查詢以外，還包括很多的更新、删除操作，選擇InnoDB，對于類似計費系統或者财務系統等對資料準确性要求比較高的系統，InnoDB是合适的選擇
• MEMORY：對表的大小有限制，太大的表無法緩存在記憶體中，其次要確定表的資料可以恢複。通常用于更新不太頻繁的小表，用以得到通路結果
• MERGE：可以突破對單個MyISAM表大小的限制，并且通過不同的表分布在多個磁盤上，可以有效地改善MERGE表的通路效率，這對于諸如資料倉儲等VLDB環境十分合适