數(shù)據(jù)庫(kù)引擎介紹

MySQL數(shù)據(jù)庫(kù)引擎取決于MySQL在安裝的時(shí)候是如何被編譯的。要添加一個(gè)新的引擎，就必須重新編譯MYSQL。在缺省情況下，MYSQL支持三個(gè)引擎：ISAM、MYISAM和HEAP。另外兩種類型INNODB和BERKLEY（BDB），也常?？梢允褂?。如果技術(shù)高超，還可以使用MySQL+API自己做一個(gè)引擎。下面介紹幾種數(shù)據(jù)庫(kù)引擎：

    ISAM：ISAM是一個(gè)定義明確且歷經(jīng)時(shí)間考驗(yàn)的數(shù)據(jù)表格管理方法，它在設(shè)計(jì)之時(shí)就考慮到 數(shù)據(jù)庫(kù)被查詢的次數(shù)要遠(yuǎn)大于更新的次數(shù)。因此，ISAM執(zhí)行讀取操作的速度很快，而且不占用大量的內(nèi)存和存儲(chǔ)資源。ISAM的兩個(gè)主要不足之處在于，它不 支持事務(wù)處理，也不能夠容錯(cuò)：如果你的硬盤崩潰了，那么數(shù)據(jù)文件就無(wú)法恢復(fù)了。如果你正在把ISAM用在關(guān)鍵任務(wù)應(yīng)用程序里，那就必須經(jīng)常備份你所有的實(shí) 時(shí)數(shù)據(jù)，通過其復(fù)制特性，MYSQL能夠支持這樣的備份應(yīng)用程序。
    MyISAM：MyISAM是MySQL的ISAM擴(kuò)展格式和缺省的數(shù)據(jù)庫(kù)引擎。除了提供ISAM里所沒有的索引和字段管理的大量功能，MyISAM還使用一種表格鎖定的機(jī)制，來(lái)優(yōu)化多個(gè)并發(fā)的讀寫操作，其代價(jià)是你需要經(jīng)常運(yùn)行OPTIMIZE TABLE命令，來(lái)恢復(fù)被更新機(jī)制所浪費(fèi)的空間。MyISAM還有一些有用的擴(kuò)展，例如用來(lái)修復(fù)數(shù)據(jù)庫(kù)文件的MyISAMCHK工具和用來(lái)恢復(fù)浪費(fèi)空間的 MyISAMPACK工具。MYISAM強(qiáng)調(diào)了快速讀取操作，這可能就是為什么MySQL受到了WEB開發(fā)如此青睞的主要原因：在WEB開發(fā)中你所進(jìn)行的大量數(shù)據(jù)操作都是讀取操作。所以，大多數(shù)虛擬主機(jī)提供商和INTERNET平臺(tái)提供商只允許使用MYISAM格式。MyISAM格式的一個(gè)重要缺陷就是不能在表?yè)p壞后恢復(fù)數(shù)據(jù)。
    HEAP：HEAP允許只駐留在內(nèi)存里的臨時(shí)表格。駐留在內(nèi)存里讓HEAP要比ISAM和MYISAM都快，但是它所管理的數(shù)據(jù)是不穩(wěn)定的，而且如果在關(guān)機(jī)之前沒有進(jìn)行保存，那么所有的數(shù)據(jù)都會(huì)丟失。在數(shù)據(jù)行被刪除的時(shí)候，HEAP也不會(huì)浪費(fèi)大量的空間。HEAP表格在你需要使用SELECT表達(dá)式來(lái)選擇和操控?cái)?shù)據(jù)的時(shí)候非常有用。要記住，在用完表格之后就刪除表格。
    InnoDB：InnoDB數(shù)據(jù)庫(kù)引擎都是造就MySQL靈活性的技術(shù)的直接產(chǎn)品，這項(xiàng)技術(shù)就是MYSQL+API。在使用MYSQL的時(shí)候，你所面對(duì)的每一個(gè)挑戰(zhàn)幾乎都源于ISAM和MyISAM數(shù)據(jù)庫(kù)引擎不支持事務(wù)處理（transaction process）也不支持外來(lái)鍵。盡管要比ISAM和 MyISAM引擎慢很多，但是InnoDB包括了對(duì)事務(wù)處理和外來(lái)鍵的支持，這兩點(diǎn)都是前兩個(gè)引擎所沒有的。如前所述，如果你的設(shè)計(jì)需要這些特性中的一者 或者兩者，那你就要被迫使用后兩個(gè)引擎中的一個(gè)了。
    如果感覺自己的確技術(shù)高超，你還能夠使用MySQL+API來(lái)創(chuàng)建自己的數(shù)據(jù)庫(kù)引擎。這個(gè)API為你提供了操作字段、記錄、表格、數(shù)據(jù)庫(kù)、連接、安全帳號(hào)的功能，以及建立諸如MySQL這樣DBMS所需要的所有其他無(wú)數(shù)功能。深入講解API已經(jīng)超出了本文的范圍，但是你需要了解MySQL+API的存在及其可交換引擎背后的技術(shù)，這一點(diǎn)是很重要的。估計(jì)這個(gè)插件式數(shù)據(jù)庫(kù)引擎的模型甚至能夠被用來(lái)為MySQL創(chuàng)建本地的XML提供器（XML provider）。（任何讀到本文的MySQL+API開發(fā)人員可以把這一點(diǎn)當(dāng)作是個(gè)要求。）
MyISAM與InnoDB的區(qū)別
　　InnoDB和MyISAM是許多人在使用MySQL時(shí)最常用的兩個(gè)表類型，這兩個(gè)表類型各有優(yōu)劣，視具體應(yīng)用而定。基本的差別為：MyISAM類型不支持事務(wù)處理等高級(jí)處理，而InnoDB類型支持。MyISAM類型的表強(qiáng)調(diào)的是性能，其執(zhí)行數(shù)度比InnoDB類型更快，但是不提供事務(wù)支持，而InnoDB提供事務(wù)支持已經(jīng)外部鍵等高級(jí)數(shù)據(jù)庫(kù)功能。

以下是一些細(xì)節(jié)和具體實(shí)現(xiàn)的差別：
1.InnoDB不支持FULLTEXT類型的索引。
2.InnoDB 中不保存表的具體行數(shù)，也就是說(shuō)，執(zhí)行select count(*) fromtable時(shí)，InnoDB要掃描一遍整個(gè)表來(lái)計(jì)算有多少行，但是MyISAM只要簡(jiǎn)單的讀出保存好的行數(shù)即可。注意的是，當(dāng)count(*)語(yǔ)句包含where條件時(shí)，兩種表的操作是一樣的。
3.對(duì)于AUTO_INCREMENT類型的字段，InnoDB中必須包含只有該字段的索引，但是在MyISAM表中，可以和其他字段一起建立聯(lián)合索引。
4.DELETE FROM table時(shí)，InnoDB不會(huì)重新建立表，而是一行一行的刪除。
5.LOAD TABLE FROMMASTER操作對(duì)InnoDB是不起作用的，解決方法是首先把InnoDB表改成MyISAM表，導(dǎo)入數(shù)據(jù)后再改成InnoDB表，但是對(duì)于使用的額外的InnoDB特性(例如外鍵)的表不適用。

另外，InnoDB表的行鎖也不是絕對(duì)的，假如在執(zhí)行一個(gè)SQL語(yǔ)句時(shí)MySQL不能確定要掃描的范圍，InnoDB表同樣會(huì)鎖全表，例如updatetable set num=1 where name like “a%”
兩種類型最主要的差別就是Innodb支持事務(wù)處理與外鍵和行級(jí)鎖.而MyISAM不支持.所以MyISAM往往就容易被人認(rèn)為只適合在小項(xiàng)目中使用。
我作為使用MySQL的用戶角度出發(fā)，Innodb和MyISAM都是比較喜歡的，但是從我目前運(yùn)維的數(shù)據(jù)庫(kù)平臺(tái)要達(dá)到需求：99.9%的穩(wěn)定性，方便的擴(kuò)展性和高可用性來(lái)說(shuō)的話，MyISAM絕對(duì)是我的首選。

原因如下：
1、首先我目前平臺(tái)上承載的大部分項(xiàng)目是讀多寫少的項(xiàng)目，而MyISAM的讀性能是比Innodb強(qiáng)不少的。
2、MyISAM的索引和數(shù)據(jù)是分開的，并且索引是有壓縮的，內(nèi)存使用率就對(duì)應(yīng)提高了不少。能加載更多索引，而Innodb是索引和數(shù)據(jù)是緊密捆綁的，沒有使用壓縮從而會(huì)造成Innodb比MyISAM體積龐大不小。
3、從平臺(tái)角度來(lái)說(shuō)，經(jīng)常隔1，2個(gè)月就會(huì)發(fā)生應(yīng)用開發(fā)人員不小心update一個(gè)表where寫的范圍不對(duì)，導(dǎo)致這個(gè)表沒法正常用了，這個(gè)時(shí)候MyISAM的優(yōu)越性就體現(xiàn)出來(lái)了，隨便從當(dāng)天拷貝的壓縮包取出對(duì)應(yīng)表的文件，隨便放到一個(gè)數(shù)據(jù)庫(kù)目錄下，然后dump成sql再導(dǎo)回到主庫(kù)，并把對(duì)應(yīng)的binlog補(bǔ)上。如果是Innodb，恐怕不可能有這么快速度，別和我說(shuō)讓Innodb定期用導(dǎo)出xxx.sql機(jī)制備份，因?yàn)槲移脚_(tái)上最小的一個(gè)數(shù)據(jù)庫(kù)實(shí)例的數(shù)據(jù)量基本都是幾十G大小。
4、從我接觸的應(yīng)用邏輯來(lái)說(shuō)，select count(*) 和order by是最頻繁的，大概能占了整個(gè)sql總語(yǔ)句的60%以上的操作，而這種操作Innodb其實(shí)也是會(huì)鎖表的，很多人以為Innodb是行級(jí)鎖，那個(gè)只是where對(duì)它主鍵是有效，非主鍵的都會(huì)鎖全表的。
5、還有就是經(jīng)常有很多應(yīng)用部門需要我給他們定期某些表的數(shù)據(jù)，MyISAM的話很方便，只要發(fā)給他們對(duì)應(yīng)那表的frm.MYD,MYI的文件，讓他們自己在對(duì)應(yīng)版本的數(shù)據(jù)庫(kù)啟動(dòng)就行，而Innodb就需要導(dǎo)出xxx.sql了，因?yàn)楣饨o別人文件，受字典數(shù)據(jù)文件的影響，對(duì)方是無(wú)法使用的。
6、如果和MyISAM比insert寫操作的話，Innodb還達(dá)不到MyISAM的寫性能，如果是針對(duì)基于索引的update操作，雖然MyISAM可能會(huì)遜色I(xiàn)nnodb,但是那么高并發(fā)的寫，從庫(kù)能否追的上也是一個(gè)問題，還不如通過多實(shí)例分庫(kù)分表架構(gòu)來(lái)解決。
7、如果是用MyISAM的話，merge引擎可以大大加快應(yīng)用部門的開發(fā)速度，他們只要對(duì)這個(gè)merge表做一些selectcount(*)操作，非常適合大項(xiàng)目總量約幾億的rows某一類型(如日志，調(diào)查統(tǒng)計(jì))的業(yè)務(wù)表。
當(dāng)然Innodb也不是絕對(duì)不用，用事務(wù)的項(xiàng)目如模擬炒股項(xiàng)目，我就是用Innodb的，活躍用戶20多萬(wàn)時(shí)候，也是很輕松應(yīng)付了，因此我個(gè)人也是很喜歡Innodb的，只是如果從數(shù)據(jù)庫(kù)平臺(tái)應(yīng)用出發(fā)，我還是會(huì)首MyISAM。
另外，可能有人會(huì)說(shuō)你MyISAM無(wú)法抗太多寫操作，但是我可以通過架構(gòu)來(lái)彌補(bǔ)，說(shuō)個(gè)我現(xiàn)有用的數(shù)據(jù)庫(kù)平臺(tái)容量：主從數(shù)據(jù)總量在幾百T以上，每天十多億pv的動(dòng)態(tài)頁(yè)面，還有幾個(gè)大項(xiàng)目是通過數(shù)據(jù)接口方式調(diào)用未算進(jìn)pv總數(shù)，(其中包括一個(gè)大項(xiàng)目因?yàn)槌跗趍emcached沒部署,導(dǎo)致單臺(tái)數(shù)據(jù)庫(kù)每天處理9千萬(wàn)的查詢)。而我的整體數(shù)據(jù)庫(kù)服務(wù)器平均負(fù)載都在0.5-1左右。

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

InnoDB適合：
(1)可靠性要求比較高，或者要求事務(wù)；
(2)表更新和查詢都相當(dāng)?shù)念l繁，并且表鎖定的機(jī)會(huì)比較大的情況指定數(shù)據(jù)引擎的創(chuàng)建
讓所有的靈活性成為可能的開關(guān)是提供給ANSI SQL的MySQL擴(kuò)展——TYPE參數(shù)。MySQL能夠讓你在表格這一層指定數(shù)據(jù)庫(kù)引擎，所以它們有時(shí)候也指的是table formats。下面的示例代碼表明了如何創(chuàng)建分別使用MyISAM、ISAM和HEAP引擎的表格。要注意，創(chuàng)建每個(gè)表格的代碼是相同的，除了最后的 TYPE參數(shù)，這一參數(shù)用來(lái)指定數(shù)據(jù)引擎。

以下為引用的內(nèi)容：

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

以下為引用的內(nèi)容：

MySQL用三步來(lái)實(shí)現(xiàn)這一目的。首先，這個(gè)表格的一個(gè)副本被創(chuàng)建。然后，任何輸入數(shù)據(jù)的改變都被排入隊(duì)列，同時(shí)這個(gè)副本被移動(dòng)到另一個(gè)引擎。最后，任何排入隊(duì)列的數(shù)據(jù)改變都被送交到新的表格里，而原來(lái)的表格被刪除。

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

你可以使用SHOW TABLE命令（這是MySQL對(duì)ANSI標(biāo)準(zhǔn)的另一個(gè)擴(kuò)展）來(lái)確定哪個(gè)引擎在管理著特定的表格。SHOW TABLE會(huì)返回一個(gè)帶有多數(shù)據(jù)列的結(jié)果集，你可以用這個(gè)結(jié)果集來(lái)查詢獲得所有類型的信息：數(shù)據(jù)庫(kù)引擎的名稱在Type字段里。下面的示例代碼說(shuō)明了 SHOW TABLE的用法：

 你可以用SHOW CREATE TABLE [TableName]來(lái)取回SHOW TABLE能夠取回的信息。
一般情況下，MySQL會(huì)默認(rèn)提供多種存儲(chǔ)引擎，可以通過下面的查看:
（1）看你的MySQL現(xiàn)在已提供什么存儲(chǔ)引擎: mysql> show engines；
（2）看你的MySQL當(dāng)前默認(rèn)的存儲(chǔ)引擎: mysql> show variables like '%storage_engine%'；
（3）你要看某個(gè)表用了什么引擎(在顯示結(jié)果里參數(shù)engine后面的就表示該表當(dāng)前用的存儲(chǔ)引擎): mysql> show create table 表名；
最后，如果你想使用沒有被編譯成MySQL也沒有被激活的引擎，那是沒有用的，MySQL不會(huì)提示這一點(diǎn)。而它只會(huì)給你提供一個(gè)缺省格式（MyISAM）的表格。除了使用缺省的表格格式外，還有辦法讓MySQL給出錯(cuò)誤提示，但是就現(xiàn)在而言，如果不能肯定特定的數(shù)據(jù)庫(kù)引擎是否可用的話，你要使用SHOW TABLE來(lái)檢查表格格式。
更多的選擇意味著更好的性能
用于特定表格的引擎都需要重新編譯和追蹤，考慮到這種的額外復(fù)雜性，為什么你還是想要使用非缺省的數(shù)據(jù)庫(kù)引擎呢？答案很簡(jiǎn)單：要調(diào)整數(shù)據(jù)庫(kù)來(lái)滿足你的要求。
可以肯定的是，MyISAM的確快，但是如果你的邏輯設(shè)計(jì)需要事務(wù)處理，你就可以自由使用支持事務(wù)處理的引擎。進(jìn)一步講，由于MySQL能夠允許你在表格這一層應(yīng)用數(shù)據(jù)庫(kù)引擎，所以你可以只對(duì)需要事務(wù)處理的表格來(lái)進(jìn)行性能優(yōu)化，而把不需要事務(wù)處理的表格交給更加輕便的MyISAM引擎。對(duì)于 MySQL而言，靈活性才是關(guān)鍵。

性能測(cè)試
所有的性能測(cè)試在：Micrisoft window xp sp2 ， Intel(R) Pentinum(R) M processor 1.6oGHz 1G 內(nèi)存的電腦上測(cè)試。
測(cè)試方法：連續(xù)提交10個(gè)query， 表記錄總數(shù)：38萬(wàn) ， 時(shí)間單位 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）加了索引以后，對(duì)于MyISAM查詢可以加快：4 206.09733倍，對(duì)InnoDB查詢加快510.72921倍，同時(shí)對(duì)MyISAM更新速度減慢為原來(lái)的1/2，InnoDB的更新速度減慢為原來(lái)的1/30。要看情況決定是否要加索引，比如不查詢的log表，不要做任何的索引。
（2）如果你的數(shù)據(jù)量是百萬(wàn)級(jí)別的，并且沒有任何的事務(wù)處理，那么用MyISAM是性能最好的選擇。
（3）InnoDB表的大小更加的大，用MyISAM可省很多的硬盤空間。

在我們測(cè)試的這個(gè)38w的表中，表占用空間的情況如下：

引擎類型MyISAM   InnoDB
數(shù)據(jù)  53,924 KB   58,976 KB
索引 13,640 KB   21,072 KB

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

另外一個(gè)176W萬(wàn)記錄的表， 表占用空間的情況如下：
引擎類型MyIsam   InnorDB
數(shù)據(jù)  56,166 KB   90,736 KB
索引  67,103 KB   88,848 KB

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

其他
    MySQL 官方對(duì)InnoDB是這樣解釋的：InnoDB給MySQL提供了具有提交、回滾和崩潰恢復(fù)能力的事務(wù)安全（ACID兼容）存儲(chǔ)引擎。InnoDB鎖定在行級(jí)并且也在SELECT語(yǔ)句提供一個(gè)Oracle風(fēng)格一致的非鎖定讀，這些特色增加了多用戶部署和性能。沒有在InnoDB中擴(kuò)大鎖定的需要，因?yàn)樵贗nnoDB中行級(jí)鎖定適合非常小的空間。InnoDB也支持FOREIGN KEY強(qiáng)制。在SQL查詢中，你可以自由地將InnoDB類型的表與其它MySQL的表的類型混合起來(lái)，甚至在同一個(gè)查詢中也可以混合。
    InnoDB是為處理巨大數(shù)據(jù)量時(shí)的最大性能設(shè)計(jì)，它的CPU效率可能是任何其它基于磁盤的關(guān)系數(shù)據(jù)庫(kù)引擎所不能匹敵的。
    InnoDB存儲(chǔ)引擎被完全與MySQL服務(wù)器整合，InnoDB存儲(chǔ)引擎為在主內(nèi)存中緩存數(shù)據(jù)和索引而維持它自己的緩沖池。InnoDB存儲(chǔ)它的表＆索引在一個(gè)表空間中，表空間可以包含數(shù)個(gè)文件（或原始磁盤分區(qū)）。這與MyISAM表不同，比如在MyISAM表中每個(gè)表被存在分離的文件中。InnoDB 表可以是任何尺寸，即使在文件尺寸被限制為2GB的操作系統(tǒng)上。
    InnoDB默認(rèn)地被包含在MySQL二進(jìn)制分發(fā)中。Windows Essentials installer使InnoDB成為Windows上MySQL的默認(rèn)表。
    InnoDB被用來(lái)在眾多需要高性能的大型數(shù)據(jù)庫(kù)站點(diǎn)上產(chǎn)生。著名的Internet新聞?wù)军c(diǎn)Slashdot.org運(yùn)行在InnoDB上。 Mytrix, Inc.在InnoDB上存儲(chǔ)超過1TB的數(shù)據(jù)，還有一些其它站點(diǎn)在InnoDB上處理平均每秒800次插入/更新的.