MySQL存儲引擎

常用存儲引擎的對比：

1.MyISAM

MyISAM不支持事務、也不支持外鍵，其優(yōu)勢是訪問的速度快，對事務完整性沒有要求或者以 SELECT、INSERT 為主的應用基本上都可以使用這個引擎來創(chuàng)建表。

• 存儲文件：每個MyISAM在磁盤上存儲成3個文件，其文件名都和表名相同，但擴展名分別是：
  • .frm（存儲表定義）
  • .MYD（MYData，存儲數(shù)據(jù)）
  • .MYI（MYIndex，存儲索引）
• 存儲格式
  • 靜態(tài)（固定長度）表：
    • 默認格式，非變長字段；
    • 優(yōu)點是存儲迅速。容易緩存，出現(xiàn)故障容易修復
    • 缺點是占用的空間通常比動態(tài)表多
    • 靜態(tài)表的數(shù)據(jù)在存儲時會按照列的寬度定義補足空格，但是在應用訪問的時候并不會得到這些空格，這些空格在返回給應用之前已經(jīng)去掉。需要注意的是：如果需要保存的內(nèi)容后面本來就帶有空格，那么在返回結(jié)果的時候也會被去掉。
  • 動態(tài)表：動態(tài)表中包含變長字段，記錄不是固定長度的，這樣存儲的優(yōu)點是占用的空間相對較少，但是頻繁地更新和刪除記錄會產(chǎn)生碎片，需要定期執(zhí)行OPTIMIZE TABLE語句或myisamchk-r命令來改善性能，并且在出現(xiàn)故障時恢復相對比較困難。
  • 壓縮表：由myisampack工具創(chuàng)建，占據(jù)非常小的磁盤空間。因為每個記錄是被單獨壓縮的，所以只有非常小的訪問開支。

2.InnoDB

InnoDB存儲引擎提供了具有提交、回滾和崩潰恢復能力的事務安全。但是對比MyISAM的存儲引擎，InnoDB寫的處理效率差一些，并且會占用更多的磁盤空間以保留數(shù)據(jù)和索引。

• 自動增長列
  • 對于InnoDB表，自動增長列必須是索引。如果是組合索引，也必須是組合索引的第一列，但是對于MyISAM 表，自動增長列可以是組合索引的其他列，這樣插入記錄后，自動增長列是按照組合索引的前面幾列進行排序后遞增的。
• 外鍵約束
  • MySQL支持外鍵的存儲引擎只有InnoDB，在創(chuàng)建外鍵的時候，要求父表必須有對應的索引，子表在創(chuàng)建外鍵的時候也會自動創(chuàng)建對應的索引。
• 存儲方式
  • 共享存儲方式：這種方式創(chuàng)建的表的表結(jié)構(gòu)保存在.frm文件中，數(shù)據(jù)和索引保存在innodb_data_home_dir和innodb_data_file_path定義的表空間中，可以是多個文件。
  • 多表空間存儲：這種方式創(chuàng)建的表的表結(jié)構(gòu)仍然保存在.frm文件中，但是每個表的數(shù)據(jù)和索引單獨保存在.ibd中。如果是個分區(qū)表，則每個分區(qū)對應單獨的.ibd 文件，文件名是“表名+分區(qū)名”，可以在創(chuàng)建分區(qū)的時候指定每個分區(qū)的數(shù)據(jù)文件的位置，以此來將表的IO均勻分布在多個磁盤上。

3.MEMORY

MEMORY存儲引擎使用存在于內(nèi)存中的內(nèi)容來創(chuàng)建表。每個MEMORY表只實際對應一個磁盤文件，格式是.frm。MEMORY類型的表訪問非常地快，因為它的數(shù)據(jù)是放在內(nèi)存中的，并且默認使用HASH索引，但是一旦服務關(guān)閉，表中的數(shù)據(jù)就會丟失掉。

MEMORY 類型的存儲引擎主要用于那些內(nèi)容變化不頻繁的代碼表，或者作為統(tǒng)計操作的中間結(jié)果表，便于高效地對中間結(jié)果進行分析并得到最終的統(tǒng)計結(jié)果。

4.MERGE

MERGE存儲引擎是一組MyISAM表的組合，這些MyISAM表必須結(jié)構(gòu)完全相同，MERGE表本身并沒有數(shù)據(jù)，對MERGE類型的表可以進行查詢、更新、刪除操作，這些操作實際上是對內(nèi)部的MyISAM表進行的。對于MERGE類型表的插入操作，是通過INSERT_METHOD子句定義插入的表，可以有3個不同的值，使用FIRST或LAST值使得插入操作被相應地作用在第一或最后一個表上，不定義這個子句或者定義為NO，表示不能對這個MERGE表執(zhí)行插入操作。

可以對MERGE表進行DROP操作，這個操作只是刪除MERGE的定義，對內(nèi)部的表沒有任何的影響。

MERGE表在磁盤上保留兩個文件，文件名以表的名字開始，一個.frm文件存儲表定義，另一個.MRG文件包含組合表的信息，包括MERGE表由哪些表組成、插入新的數(shù)據(jù)時的依據(jù)。可以通過修改.MRG文件來修改MERGE表，但是修改后要通過FLUSH TABLES刷新。

創(chuàng)建表使用MERGE：

CREATE TABLE tableName(
    ...
)engine=merge union=(table01,table02) ...;

下面是幾種常用存儲引擎的適用環(huán)境:

• MyISAM：默認的MySQL插件式存儲引擎。如果應用是以讀操作和插入操作為主，只有很少的更新和刪除操作，并且對事務的完整性、并發(fā)性要求不是很高，那么選擇這個存儲引擎是非常適合的。MyISAM是在Web、數(shù)據(jù)倉儲和其他應用環(huán)境下最常使用的存儲引擎之一。
• InnoDB：用于事務處理應用程序，支持外鍵。如果應用對事務的完整性有比較高的要求，在并發(fā)條件下要求數(shù)據(jù)的一致性，數(shù)據(jù)操作除了插入和查詢以外，還包括很多的更新、刪除操作，那么InnoDB存儲引擎應該是比較合適的選擇。InnoDB存儲引擎除了有效地降低由于刪除和更新導致的鎖定，還可以確保事務的完整提交（Commit）和回滾（Rollback），對于類似計費系統(tǒng)或者財務系統(tǒng)等對數(shù)據(jù)準確性要求比較高的系統(tǒng)，InnoDB都是合適的選擇。
• MEMORY：將所有數(shù)據(jù)保存在 RAM 中，在需要快速定位記錄和其他類似數(shù)據(jù)的環(huán)境下，可提供極快的訪問。MEMORY 的缺陷是對表的大小有限制，太大的表無法緩存在內(nèi)存中，其次是要確保表的數(shù)據(jù)可以恢復，數(shù)據(jù)庫異常終止后表中的數(shù)據(jù)是可以恢復的。MEMORY表通常用于更新不太頻繁的小表，用以快速得到訪問結(jié)果。
• MERGE：用于將一系列等同的MyISAM表以邏輯方式組合在一起，并作為一個對象引用它們。MERGE表的優(yōu)點在于可以突破對單個MyISAM表大小的限制，并且通過將不同的表分布在多個磁盤上，可以有效地改善MERGE表的訪問效率。這對于諸如數(shù)據(jù)倉儲等VLDB環(huán)境十分適合。

注意：以上只是按照實施經(jīng)驗提出的關(guān)于存儲引擎選擇的一些建議，但是不同應用的特點是千差萬別的，選擇使用哪種存儲引擎才是最佳方案也不是絕對的，這需要根據(jù)用戶各自的應用進行測試，從而得到最適合自己的結(jié)果。

參考書目：

• 《深入淺出MySQL：數(shù)據(jù)庫開發(fā)、優(yōu)化與管理維護》