MySQL資料庫引擎介紹、區別、建立和效能測試的深入分析

資料庫引擎介紹

MySQL資料庫引擎取決於MySQL在安裝的時候是如何被編譯的。要添加一個新的引擎，就必須重新編譯MYSQL。在預設情況下，MYSQL支援三個引擎：ISAM、MYISAM和HEAP。另外兩種類型INNODB和BERKLEY（BDB），也常常可以使用。如果技術高超，還可以使用MySQL+API自己做一個引擎。下面介紹幾種資料庫引擎：

ISAM：ISAM是一個定義明確且曆經時間考驗的資料表格管理方法，它在設計之時就考慮到 資料庫被查詢的次數要遠大於更新的次數。因此，ISAM執行讀取操作的速度很快，而且不佔用大量的記憶體和儲存資源。ISAM的兩個主要不足之處在於，它不 支援交易處理，也不能夠容錯：如果你的硬碟崩潰了，那麼資料檔案就無法恢複了。如果你正在把ISAM用在關鍵任務應用程式裡，那就必須經常備份你所有的實 時資料，通過其複製特性，MYSQL能夠支援這樣的備份應用程式。
MyISAM：MyISAM是MySQL的ISAM擴充格式和預設的資料庫引擎。除了提供ISAM裡所沒有的索引和欄位管理的大量功能，MyISAM還使用一種表格鎖定的機制，來最佳化多個並發的讀寫操作，其代價是你需要經常運行OPTIMIZE TABLE命令，來恢複被更新機制所浪費的空間。MyISAM還有一些有用的擴充，例如用來修複資料庫檔案的MyISAMCHK工具和用來恢複浪費空間的 MyISAMPACK工具。MYISAM強調了快速讀取操作，這可能就是為什麼MySQL受到了WEB開發如此青睞的主要原因：在WEB開發中你所進行的大量資料操作都是讀取操作。所以，大多數虛擬機器主機供應商和INTERNET平台供應商只允許使用MYISAM格式。MyISAM格式的一個重要缺陷就是不能在表損壞後恢複資料。
HEAP：HEAP允許只駐留在記憶體裡的暫存資料表格。駐留在記憶體裡讓HEAP要比ISAM和MYISAM都快，但是它所管理的資料是不穩定的，而且如果在關機之前沒有進行儲存，那麼所有的資料都會丟失。在資料行被刪除的時候，HEAP也不會浪費大量的空間。HEAP表格在你需要使用SELECT運算式來選擇和操控資料的時候非常有用。要記住，在用完表格之後就刪除表格。
InnoDB：InnoDB資料庫引擎都是造就MySQL靈活性的技術的直接產品，這項技術就是MYSQL+API。在使用MYSQL的時候，你所面對的每一個挑戰幾乎都源於ISAM和MyISAM資料庫引擎不支援交易處理（transaction process）也不支援外來鍵。儘管要比ISAM和 MyISAM引擎慢很多，但是InnoDB包括了對交易處理和外來鍵的支援，這兩點都是前兩個引擎所沒有的。如前所述，如果你的設計需要這些特性中的一者 或者兩者，那你就要被迫使用後兩個引擎中的一個了。
如果感覺自己的確技術高超，你還能夠使用MySQL+API來建立自己的資料庫引擎。這個API為你提供了操作欄位、記錄、表格、資料庫、串連、安全帳號的功能，以及建立諸如MySQL這樣DBMS所需要的所有其他無數功能。深入講解API已經超出了本文的範圍，但是你需要瞭解MySQL+API的存在及其可交換引擎背後的技術，這一點是很重要的。估計這個外掛程式式資料庫引擎的模型甚至能夠被用來為MySQL建立本地的XML提供器（XML provider）。（任何讀到本文的MySQL+API開發人員可以把這一點當作是個要求。）
MyISAM與InnoDB的區別
　　InnoDB和MyISAM是許多人在使用MySQL時最常用的兩個表類型，這兩個表類型各有優劣，視具體應用而定。基本的差別為：MyISAM類型不支援交易處理等進階處理，而InnoDB類型支援。MyISAM類型的表強調的是效能，其執行數度比InnoDB類型更快，但是不提供事務支援，而InnoDB提供事務支援已經外部鍵等進階資料庫功能。

以下是一些細節和具體實現的差別：
1.InnoDB不支援FULLTEXT類型的索引。
2.InnoDB 中不儲存表的具體行數，也就是說，執行select count(*) fromtable時，InnoDB要掃描一遍整個表來計算有多少行，但是MyISAM只要簡單的讀出儲存好的行數即可。注意的是，當count(*)語句包含where條件時，兩種表的操作是一樣的。
3.對於AUTO_INCREMENT類型的欄位，InnoDB中必須包含只有該欄位的索引，但是在MyISAM表中，可以和其他欄位一起建立聯合索引。
4.DELETE FROM table時，InnoDB不會重建立立表，而是一行一行的刪除。
5.LOAD TABLE FROMMASTER操作對InnoDB是不起作用的，解決方案是首先把InnoDB表改成MyISAM表，匯入資料後再改成InnoDB表，但是對於使用的額外的InnoDB特性(例如外鍵)的表不適用。

另外，InnoDB表的行鎖也不是絕對的，假如在執行一個SQL語句時MySQL不能確定要掃描的範圍，InnoDB表同樣會鎖全表，例如updatetable set num=1 where name like “a%”
兩種類型最主要的差別就是Innodb支援交易處理與外鍵和行級鎖.而MyISAM不支援.所以MyISAM往往就容易被人認為只適合在小項目中使用。
我作為使用MySQL的使用者角度出發，Innodb和MyISAM都是比較喜歡的，但是從我目前營運的資料庫平台要達到需求：99.9%的穩定性，方便的擴充性和高可用性來說的話，MyISAM絕對是我的首選。

原因如下：
1、首先我目前平台上承載的大部分項目是讀多寫少的項目，而MyISAM的讀效能是比Innodb強不少的。
2、MyISAM的索引和資料是分開的，並且索引是有壓縮的，記憶體使用量率就對應提高了不少。能載入更多索引，而Innodb是索引和資料是緊密捆綁的，沒有使用壓縮從而會造成Innodb比MyISAM體積龐大不小。
3、從平台角度來說，經常隔1，2個月就會發生應用開發人員不小心update一個表where寫的範圍不對，導致這個表沒法正常用了，這個時候MyISAM的優越性就體現出來了，隨便從當天拷貝的壓縮包取出對應表的檔案，隨便放到一個資料庫目錄下，然後dump成sql再導回到主庫，並把對應的binlog補上。如果是Innodb，恐怕不可能有這麼快速度，別和我說讓Innodb定期用匯出xxx.sql機製備份，因為我平台上最小的一個資料庫執行個體的資料量基本都是幾十G大小。
4、從我接觸的應用邏輯來說，select count(*) 和order by是最頻繁的，大概能佔了整個sql總語句的60%以上的操作，而這種操作Innodb其實也是會鎖表的，很多人以為Innodb是行級鎖，那個只是where對它主鍵是有效，非主鍵的都會鎖全表的。
5、還有就是經常有很多應用部門需要我給他們定期某些表的資料，MyISAM的話很方便，只要發給他們對應那表的frm.MYD,MYI的檔案，讓他們自己在對應版本的資料庫啟動就行，而Innodb就需要匯出xxx.sql了，因為光給別人檔案，受字典資料檔案的影響，對方是無法使用的。
6、如果和MyISAM比insert寫操作的話，Innodb還達不到MyISAM的寫效能，如果是針對基於索引的update操作，雖然MyISAM可能會遜色Innodb,但是那麼高並發的寫，從庫能否追的上也是一個問題，還不如通過多執行個體分庫分表架構來解決。
7、如果是用MyISAM的話，merge引擎可以大大加快應用部門的開發速度，他們只要對這個merge表做一些selectcount(*)操作，非常適合大項目總量約幾億的rows某一類型(如日誌，調查統計)的業務表。
當然Innodb也不是絕對不用，用事務的項目如類比炒股項目，我就是用Innodb的，活躍使用者20多萬時候，也是很輕鬆應付了，因此我個人也是很喜歡Innodb的，只是如果從資料庫平台應用出發，我還是會首MyISAM。
另外，可能有人會說你MyISAM無法抗太多寫操作，但是我可以通過架構來彌補，說個我現有用的資料庫平台容量：主從資料總量在幾百T以上，每天十多億pv的動態網頁面，還有幾個大項目是通過資料介面方式調用未算進pv總數，(其中包括一個大項目因為初期memcached沒部署,導致單台資料庫每天處理9千萬的查詢)。而我的整體資料庫伺服器平均負載都在0.5-1左右。

一般來說，MyISAM適合：
(1)做很多count 的計算；
(2)插入不頻繁，查詢非常頻繁；
(3)沒有事務。

InnoDB適合：
(1)可靠性要求比較高，或者要求事務；
(2)表更新和查詢都相當的頻繁，並且表鎖定的機會比較大的情況指定資料引擎的建立
讓所有的靈活性成為可能的開關是提供給ANSI SQL的MySQL擴充——TYPE參數。MySQL能夠讓你在表格這一層指定資料庫引擎，所以它們有時候也指的是table formats。下面的範例程式碼表明了如何建立分別使用MyISAM、ISAM和HEAP引擎的表格。要注意，建立每個表格的代碼是相同的，除了最後的 TYPE參數，這一參數用來指定資料引擎。

以下為引用的內容：

複製代碼 代碼如下:CREATE TABLE tblMyISAM (
id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (id),
value_a TINYINT
) TYPE=MyISAM
CREATE TABLE tblISAM (
id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (id),
value_a TINYINT
) TYPE=ISAM
CREATE TABLE tblHeap (
id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (id),
value_a TINYINT
) TYPE=Heap

你也可以使用ALTER TABLE命令，把原有的表格從一個引擎移動到另一個引擎。下面的代碼顯示了如何使用ALTER TABLE把MyISAM表格移動到InnoDB的引擎：

以下為引用的內容：

複製代碼 代碼如下:ALTER TABLE tblMyISAM CHANGE TYPE=InnoDB

MySQL用三步來實現這一目的。首先，這個表格的一個副本被建立。然後，任何輸入資料的改變都被排入隊列，同時這個副本被移動到另一個引擎。最後，任何排入隊列的資料改變都被送交到新的表格裡，而原來的表格被刪除。複製代碼 代碼如下:ALTER TABLE捷徑

如果只是想把表格從ISAM更新為MyISAM，你可以使用MySQL_convert_table_format命令，而不需要編寫ALTER TABLE運算式。

你可以使用SHOW TABLE命令（這是MySQL對ANSI標準的另一個擴充）來確定哪個引擎在管理著特定的表格。SHOW TABLE會返回一個帶有多資料列的結果集，你可以用這個結果集來查詢獲得所有類型的資訊：資料庫引擎的名稱在Type欄位裡。下面的範例程式碼說明了 SHOW TABLE的用法：

複製代碼 代碼如下:SHOW TABLE STATUS FROM tblInnoDB

你可以用SHOW CREATE TABLE [TableName]來取回SHOW TABLE能夠取回的資訊。
一般情況下，MySQL會預設提供多種儲存引擎，可以通過下面的查看:
（1）看你的MySQL現在已提供什麼儲存引擎: mysql> show engines；
（2）看你的MySQL當前預設的儲存引擎: mysql> show variables like '%storage_engine%'；
（3）你要看某個表用了什麼引擎(在顯示結果裡參數engine後面的就表示該表當前用的儲存引擎): mysql> show create table 表名；
最後，如果你想使用沒有被編譯成MySQL也沒有被啟用的引擎，那是沒有用的，MySQL不會提示這一點。而它只會給你提供一個預設格式（MyISAM）的表格。除了使用預設的表格格式外，還有辦法讓MySQL給出錯誤提示，但是就現在而言，如果不能肯定特定的資料庫引擎是否可用的話，你要使用SHOW TABLE來檢查表格格式。
更多的選擇意味著更好的效能
用於特定表格的引擎都需要重新編譯和追蹤，考慮到這種的額外複雜性，為什麼你還是想要使用非預設的資料庫引擎呢？答案很簡單：要調整資料庫來滿足你的要求。
可以肯定的是，MyISAM的確快，但是如果你的邏輯設計需要交易處理，你就可以自由使用支援交易處理的引擎。進一步講，由於MySQL能夠允許你在表格這一層應用程式資料庫引擎，所以你可以只對需要交易處理的表格來進行效能最佳化，而把不需要交易處理的表格交給更加輕便的MyISAM引擎。對於 MySQL而言，靈活性才是關鍵。

效能測試
所有的效能測試在：Micrisoft window xp sp2 ， Intel(R) Pentinum(R) M processor 1.6oGHz 1G 記憶體的電腦上測試。
測試方法：連續提交10個query， 表記錄總數：38萬 ， 時間單位 s
引擎類型MyISAMInnoDB 效能相差
count 0.00083573.01633609
查詢主鍵 0.005708 0.157427.57
查詢非主鍵 24.01 80.37 3.348
更新主鍵 0.008124 0.8183100.7
更新非主鍵 0.004141 0.02625 6.338
插入 0.004188 0.369488.21
（1）加了索引以後，對於MyISAM查詢可以加快：4 206.09733倍，對InnoDB查詢加快510.72921倍，同時對MyISAM更新速度減慢為原來的1/2，InnoDB的更新速度減慢為原來的1/30。要看情況決定是否要加索引，比如不查詢的log表，不要做任何的索引。
（2）如果你的資料量是百萬層級的，並且沒有任何的交易處理，那麼用MyISAM是效能最好的選擇。
（3）InnoDB表的大小更加的大，用MyISAM可省很多的硬碟空間。

在我們測試的這個38w的表中，表佔用空間的情況如下：

引擎類型MyISAM InnoDB
資料 53,924 KB 58,976 KB
索引 13,640 KB 21,072 KB

佔用總空間 67,564 KB 80,048 KB

另外一個176W萬記錄的表， 表佔用空間的情況如下：
引擎類型MyIsam InnorDB
資料 56,166 KB 90,736 KB
索引 67,103 KB 88,848 KB

佔用總空間 123,269 KB179,584 KB

其他
MySQL 官方對InnoDB是這樣解釋的：InnoDB給MySQL提供了具有提交、復原和崩潰恢複能力的事務安全（ACID相容）儲存引擎。InnoDB鎖定在行級並且也在SELECT語句提供一個Oracle風格一致的非鎖定讀，這些特色增加了多使用者部署和效能。沒有在InnoDB中擴大鎖定的需要，因為在InnoDB中行級鎖定適合非常小的空間。InnoDB也支援FOREIGN KEY強制。在SQL查詢中，你可以自由地將InnoDB類型的表與其它MySQL的表的類型混合起來，甚至在同一個查詢中也可以混合。
InnoDB是為處理巨大資料量時的最大效能設計，它的CPU效率可能是任何其它基於磁碟的關聯式資料庫引擎所不能匹敵的。
InnoDB儲存引擎被完全與MySQL伺服器整合，InnoDB儲存引擎為在主記憶體中快取資料和索引而維持它自己的緩衝池。InnoDB儲存它的表＆索引在一個資料表空間中，資料表空間可以包含數個檔案（或原始磁碟分割）。這與MyISAM表不同，比如在MyISAM表中每個表被存在分離的檔案中。InnoDB 表可以是任何尺寸，即使在檔案尺寸被限制為2GB的作業系統上。
InnoDB預設地被包含在MySQL二進位分發中。Windows Essentials installer使InnoDB成為Windows上MySQL的預設表。
InnoDB被用來在眾多需要高效能的大型資料庫網站上產生。著名的Internet新聞網站Slashdot.org運行在InnoDB上。 Mytrix, Inc.在InnoDB上儲存超過1TB的資料，還有一些其它網站在InnoDB上處理平均每秒800次插入/更新的.