MySQL Storage Engine 簡介

[ Storage Engine 簡介 ]：

MySQL對於資料的處理運用了各種不同的儲存技術，有存於檔案中，也有存於記憶體中等，

而這些技術我們稱之為MySQL的「Storage Engine」。

由於不同的Storage Engine使用不同的儲存技術，所以在資料的處理上，也有效能、速度、安全性等不同的差異，

所以如何挑選正確的Storage Engine也成為使用MySQL中的一大重要環節。

例如，必須儲存大量的資料，或者能快速處理臨時性資料，又或者需要使用Transaction機制等，

這些不同的需求，都會因為選擇各種不同的Storage Engine而有非常大的差異。




[ Storage Engine 種類 ]：

想知道MySQL中有幾種Storage Engine可供選擇使用時，

可以在命令列下指令「mysql> SHOW ENGINES;」後，

會出現一表格分別列出此版MySQL的各種Storage Engine，

表格會列出三項訊息分別為「Engine」(名稱)、「Support」(是否支援)、「Comment」(備註)。


Storage Engine約有以下幾種MyISAM、ISAM、MEMORY(HEAP)、InnoDB、BerkeleyDB(BDB)、

BLACKHOLE、ARCHIVE、CSV、NDBCLUSTER、EXAMPLE、FEDERATIED等，

有些Storage Engine並不常被使用（因為各Storage Engine各有不同的特性），

或是被後來推出的Storage Engine所取代；

然而較常被一般大眾所使用的Storage Engine大約有ISAM、MyISAM、MEMORY(HEAP)、InnoDB、BerkeleyDB(BDB)

[註：不同版本的MySQL所支援的Storage Engine會有所差異]。

以下分別介紹常用的Storage Engine的特性：


　ISAM (MySQL v5.0不支援)：

ISAM在當初設計時就清楚的了解資料庫被查詢的次數遠大於被更新的次數，

所以ISAM在查詢資料的讀取時間相當的快速，而且並不佔用大量的記憶體和儲存資源，

但是ISAM的缺點在於它不支援Transaction，也不能容錯，

所以萬一遇上硬碟毀損時，資料將無法恢復，

所以相對的，若ISAM儲存著重要的資料時，那就必須經常執行備份動作以確保資料安全。


　MyISAM：

MyISAM是MySQL針對ISAM所不及的地方加以改善，所發展出來的Storage Engine。

MyISAM除了提供ISAM裡所沒有的索引和欄位管理的功能，MyISAM也提供一種Table鎖定的機制，

以確保同時間多筆資料的修改與新增的資料正確性。

但必需經常執行OPTIMIZE TABLE指令，以釋放所浪費掉的空間。


　HEAP (MEMORY)：

HEAP為一種駐留在記憶體裡的臨時Table。

也因為HEAP是駐留在記憶體裡，所以在資料的存取上比ISAM和MyISAM都還要快。

但是HEAP對於資料的儲存是不穩定的，因為記憶體中的資料會因各種例外因素或因關機而消失，

所以在關機之前沒有進行資料保存，那麼所有的資料都會遺失。

而在資料進行刪除動作時，HEAP也不會浪費太多的空間。

HEAP對於使用SELECT指令來查詢資料時具有非常高的效能。

另請注意由於HEAP是駐留在記憶體裡，

所以在資料使用完畢後，應進行Table的Drop動作，以確保記憶體保有足夠的空間。


　InnoDB和Berkley DB (MySQL v5.0不支援)：

InnoDB和Berkley DB（BDB）這兩種Storage Engine都支援Transaction的理處，

而兩者的差異在於資料的鎖定機制上，InnoDB支援到row-level，而BerkleyDB支援page-level；

並且InnoDB支援Foreign Key的使用。儘管要比ISAM和MyISAM Storage Engine慢很多，

但透過配置檔的設定，可稍稍改善InnoDB在執行上的速度。




[ Storage Engine 的選擇 ]：

每一種Storage Engine具有各種不同的特性與效能，

所以必須了解每一種Storage Engine所提供的各種核心功能。

一般把這些核心功能分為四項：支援的欄位和資料類型、鎖定類型、索引、支援Transaction。


　欄位和資料類型：

大多的Storage Engine都技援通用的資料類型，例如：整數、浮點數、字完等，

但並非所有的Storage Engine都支援其他的欄位類型，例如：BLOB（二進制物件）、TEXT等，

或僅支援有限的資料大小與長度。

然而以上的限制與欄位的支援度，有可能直接影響到欲儲存的資料，或是針對搜索或建立索引時間接的產生影響，

所以在事前應該確認欲儲存的資料型態，再選擇合適的Storage Engine。


　鎖定：

Storage Engine中提供的鎖定功能讓資料庫中的資料當被查詢或更新時被鎖定給此單一處理，

在完成之前，其他處理不能修改此資料；大多數的鎖定機制主要是為了防止多個處理更新同一筆資料，

而新增資料和更新資料這兩種情況都需要鎖定，以必免多個處理同時針對同一筆資料進行更新或新增時所產生的差異，

以確保資料的正確性與安全。不同的Storage Engine所提供的鎖定層級不盡相同，

而這些鎖定層級分為三項，分別是：table-level、page-level、row-level。

MyISAM提供了table-level的鎖定層級，當有資料在更新時，啟動鎖定機制是鎖定整個Table，

而若有多個使用者，可能因為鎖定而產生延遲的影響。

支援page-level的Storage Engine有Berkeley DB，此鎖定機制是鎖定資料結構的最後8KB。

而row-level只有InnoDB支援此鎖定層級，此鎖定層級對於多使用者的情況下影響最小，

也最有效率，它只針對Table中的一列資料進行鎖定。


　建立索引：

建立索引在搜尋和恢復資料時有顯著的良好性能。

然而不同的Storage Engine建立索引的技術也不盡相同。

但其中有一些Storage Engine並不支援索引，如：FEDERATED、BLACKHOLE。


　Transaction：

Transaction機制可提供資料在更新或新增時的可靠性，

此機制是當所有對資料庫進行的所有操作完成結束後，才針對資料庫的實際資料進行更改；

反之，若過程中有任何一項操作發生錯誤或失敗，則可以取消此次的操作，

而此新增或修改並不會對於實際資料有所變更。




[ Storage Engine 的使用 ]：

MySQL中提供了多種Storage Engine讓使用者選擇使用，

而在指定Storage Engine時，可設定其中一種Storage Engine為預設儲存引擎，

當然也可以針對不同Table而各別設定不同的Storage Engine。


　Globle設置（預設儲存引擎）：

設定MySQL的預設儲存引擎，

在OS為Windows下可在MySQL的安裝目錄下的my.ini檔中設定default-storage-engine的選項，（註：在OS為Linux下為my.cnf）

即可更改新建Table時的預設Storage Engine，例如：default-storage-engine=INNODB。


或是透過MySQL的MySQL Server Instance Config Wizard進行簡單的設定。


　Per Table（各別設定）：

除了變更服務的預設儲存引擎外，也可透過Table在建立時各別指定使用不同的Storage Engine，

或是在設置後變更Storage Engine（但需注意各別Storage Engine提供的資料欄位的差異性）。

不同用途的Table指定合適的Storage Engine能提升DB性能，設定如下：


１、在CREATE TABLE的SQL後加TYPE或是ENGINE來指定儲存引擎，

例如：CREATE TABLE demo_table (ID INT) ENGINE = MyISAM。

若不清楚當前服務下有什麼儲存引擎可供使用時，可下指令「SHOW ENGINES;」，

以顯示出目前可供使用的Storage Engine。


２、若Table已建立，而需要變更Storage Engine可下ALTER語句進行儲存引擎的更改，

例如：ALTER TABLE demo_table ENGINE = INNODB。

若不清楚當前資料表使用何種儲存引擎時，可下指令「SHOW TABLE STATUS FROM schemaName」，

如：SHOW TABLE STATUS FROM demo_schema。

From: http://www.wretch.cc/blog/kanghaowu/11448209