深入MySQL存儲引擎比較的詳解

2024-07-24 13:01:33

字體：大中小

來源：轉載

供稿：網友

MyISAM是MySQL的默認存儲引擎。MyISAM不支持事務、也不支持外鍵，但其訪問速度快，對事務完整性沒有要求。

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

MEMORY存儲引擎使用存在內存中的內容來創建表。每個MEMORY表只實際對應一個磁盤文件。MEMORY類型的表訪問非常得快，因為它的數據是放在內存中的，并且默認使用HASH索引。但是一旦服務關閉，表中的數據就會丟失掉。

MERGE存儲引擎是一組MyISAM表的組合，這些MyISAM表必須結構完全相同。MERGE表本身沒有數據，對MERGE類型的表進行查詢、更新、刪除的操作，就是對內部的MyISAM表進行的。

MyISAM表還支持3中不同的存儲格式：
1 靜態表
2 動態表
3 壓縮表
靜態表是默認的存儲格式，靜態表中的字段都是非變長的字段，優點是：存儲非常迅速，容易緩存，出現故障容易恢復；缺點是：占用的空間通常比動態表多。（注意：在存儲時，列的寬度不足時，用空格補足，當時在訪問的時候并不會得到這些空格）
動態表的字段是變長的，優點是：占用的空間相對較少，但是頻繁地更新刪除記錄會產生碎片，需要定期改善性能，并且出現故障的時候恢復相對比較困難。
壓縮表占用磁盤空間小，每個記錄是被單獨壓縮的，所以只有非常小的訪問開支。

MySQL支持外鍵存儲引擎只有InnoDB，在創建外鍵的時候，要求附表必須有對應的索引，子表在創建外鍵的時候也會自動創建對應的索引。
InnoDB存儲方式為兩種：1 使用共享表空間存儲 2 使用多表空間

MEMORY類型的存儲引擎主要用于那些內容變化不頻繁的代碼表，或者作為統計操作的中間結果表，便于高效地堆中間結果進行分析并得到最終的統計結果。對MEMORY存儲引擎的表進行更新操作要謹慎，因為數據并沒有實際寫入到磁盤中，所以一定要對下次重新啟動服務后如何獲得這些修改后的數據有所考慮。

MERGE用于將一系列等同的MyISAM表以邏輯方式組合在一起，并作為一個對象引用它。MERGE表的優點在于可以突破對單個MyISAM表大小的限制，通過將不同的表分布在多個磁盤上，可以有效的改善MERGE表的訪問效率。

為了適應各種不同的運行環境，MYSQL提供了多種不同的存儲引擎（Storage Engine ）,在應用程序開發這個層面上，開發者可以根據不同的需求選擇適合的Storage Engine 方案，更為靈活的是，你可以根據每張表將要存儲數據的特點，選擇不同的Storage Engine,也就是說，在一個MYSQL數據庫中，可以混合使用多種不同的Storage Engine

首先小瞥一下MySQL的體系結構，在最高抽象層度下，可以用Garlan & Shaw的分層結構體系來表示（左）

深入MySQL存儲引擎比較的詳解

其中應用層為所有RDBMS用戶提供用戶接口，邏輯層包括了所有核心功能的實現，物理層則負責將數據存儲在硬件設備上。

圖中右側更為具體的描述了邏輯層的組成，查詢處理子系統、事務管理子系統、恢復管理子系統和存儲管理子系統共同組成了MySQL的邏輯層。相信Storage Engine的位置是在Storage Management處，既Storage Engine屬于Storage Management子系統的一部分

為了讓思路更清晰一些，下面給出一幅比較全面的體系結構圖（或更確切的說是流程圖，只是忽略了反饋）

深入MySQL存儲引擎比較的詳解

上面三幅圖來自于一篇非官方（不保證百分百的正確）的MySQL體系結構的報告，與《High Performance MySQL》一書中給出的MySQL大體結構（下圖,基本對應于Logic Layer，從第一幅圖右側可以看出MySQL logic layer同樣遵從分層體系結構）還是比較吻合的。

深入MySQL存儲引擎比較的詳解

連接上圖中第二層和第三層之間的接口是并不針對任何存儲引擎的單一API,.大概由20個基本的類似“啟動事務，返回結果集”等函數組成。存儲引擎并不處理SQL，相互之間也不通信，它們的任務只是簡單的響應高層傳來的請求。

存儲引擎各自的一些特點

上面提到的四種存儲引擎都有各自適用的環境，這取決于它們獨有的一些特征。主要體現在性能、事務、并發控制、參照完整性、緩存、故障恢復，備份及回存等幾個方面

目前比較普及的存儲引擎是MyISAM和InnoDB.而MyISAM又是絕大部分Web應用的首選。MyISAM與InnoDB的主要的不同點在于性能和事務控制上。

MyISAM是早期ISAM(Indexed Sequential Access Method,我現在用的MySQL5.0已經不支持ISAM了）的擴展實現，ISAM被設計為適合處理讀頻率遠大于寫頻率這樣一種情況，因此ISAM以及后來的MyISAM都沒有考慮對事物的支持，排除了TPM，不需要事務記錄，ISAM的查詢效率相當可觀，而且內存占用很少。MyISAM在繼承了這類優點的同時，與時俱進的提供了大量實用的新特性和相關工具。例如考慮到并發控制，提供了表級鎖，雖然MyISAM本身不支持容錯，但可以通過myisamchk進行故障恢復。而且由于MyISAM是每張表使用各自獨立的存儲文件（MYD數據文件和MYI索引文件），使得備份及恢復十分方便（拷貝覆蓋即可），而且還支持在線恢復。

所以如果你的應用是不需要事務，處理的只是基本的CRUD操作，那么MyISAM是不二選擇

InnoDB被設計成適用于高并發讀寫的情況.使用MVCC(Multi-Version Concurrency Control)以及行級鎖來提供遵從ACID的事務支持。InnoDB支持外鍵參照完整性，具備故障恢復能力。另外 InnoDB的性能其實還是不錯的，特別是在處理大數據量的情況下,用官方的話說就是： InnoDB的CPU效率是其他基于磁盤的關系數據庫存儲引擎所不能比的。不過InnoDB的備份恢復要麻煩一點，除非你使用了4.1以后版本提供的Mulit-tablespace支持，因為InnoDB和MyISAM不同，他的數據文件并不是獨立對應于每張表的。而是使用的共享表空間，簡單的拷貝覆蓋方法對他不適用，必須在停掉MYSQL后對進行數據恢復。使用Per-Table Tablespacesd，使其每張表對應一個獨立的表空間文件，則情況要簡單很多。

一般來說，如果需要事務支持，并且有較高的并發讀寫頻率，InnoDB是不錯的選擇。要是并發讀寫頻率不高的話，其實可以考慮BDB,但由于在MySQL5.1及其以后版本中，將不再提供BDB支持。這個選項也就沒有了

至于Heap和BDB（Berkeley DB），相對來說，普及率不如前兩種，但在有些情況下，還是挺適用的

Heap存儲引擎就是將數據存儲在內存中，由于沒有磁盤I./O的等待，速度極快。但由于是內存存儲引擎，所做的任何修改在服務器重啟后都將消失。

Heap挺適合做測試的時候使用

BDB是MySQL第一款事務安全的存儲引擎。在Berkeley DB database library的基礎上建立，同樣是事務安全的，但BDB的普及率顯然不及InnoDB，因為大多數在MySQL中尋找支持事務的存儲引擎的同時也在找支持MVCC或是行級鎖定存儲引擎，而BDB只支持Page-level Lock。