您當前位置：首頁 > 數據庫 > 數據庫應用 > 談談innodb和myisam的區別

談談innodb和myisam的區別

來源：程序員人生發布時間：2014-12-12 08:57:48 閱讀次數：3949次

MyISAM 和 InnoDB 講授

　　InnoDB和MyISAM是許多人在使用MySQL時最經常使用的兩個表類型，這兩個表類型各有優劣，視具體利用而定。基本的差別為：MyISAM類型不支持事務處理等高級處理，而InnoDB類型支持。MyISAM類型的表強調的是性能，其履行數度比InnoDB類型更快，但是不提供事務支持，而InnoDB提供事務支持已外部鍵等高級數據庫功能。

　　以下是1些細節和具體實現的差別：

　　◆1.InnoDB不支持FULLTEXT類型的索引。

　　◆2.InnoDB 中不保存表的具體行數，也就是說，履行select count(*) from table時，InnoDB要掃描1遍全部表來計算有多少行，但是MyISAM只要簡單的讀出保存好的行數便可。注意的是，當count(*)語句包括 where條件時，兩種表的操作是1樣的。

　　◆3.對AUTO_INCREMENT類型的字段，InnoDB中必須包括只有該字段的索引，但是在MyISAM表中，可以和其他字段1起建立聯合索引。

　　◆4.DELETE FROM table時，InnoDB不會重新建立表，而是1行1行的刪除。

　　◆5.LOAD TABLE FROM MASTER操作對InnoDB是不起作用的，解決方法是首先把InnoDB表改成MyISAM表，導入數據后再改成InnoDB表，但是對使用的額外的InnoDB特性(例如外鍵)的表不適用。

　　另外，InnoDB表的行鎖也不是絕對的，假設在履行1個SQL語句時MySQL不能肯定要掃描的范圍，InnoDB表一樣會鎖全表，例如update table set num=1 where name like “%aaa%”

　　兩種類型最主要的差別就是Innodb 支持事務處理與外鍵和行級鎖.而MyISAM不支持.所以MyISAM常常就容易被人認為只合適在小項目中使用。

　　我作為使用MySQL的用戶角度動身，Innodb和MyISAM都是比較喜歡的，但是從我目前運維的數據庫平臺要到達需求：99.9%的穩定性，方便的擴大性和高可用性來講的話，MyISAM絕對是我的首選。

　　緣由以下：

　　1、首先我目前平臺上承載的大部份項目是讀多寫少的項目，而MyISAM的讀性能是比Innodb強很多的。

　　2、MyISAM的索引和數據是分開的，并且索引是有緊縮的，內存使用率就對應提高了很多。能加載更多索引，而Innodb是索引和數據是緊密捆綁的，沒有使用緊縮從而會造成Innodb比MyISAM體積龐大不小。

　　3、從平臺角度來講，常常隔1，2個月就會產生利用開發人員不謹慎update1個表where寫的范圍不對，致使這個表沒法正經常使用了，這個時候MyISAM的優越性就體現出來了，隨意從當天拷貝的緊縮包取出對應表的文件，隨意放到1個數據庫目錄下，然后dump成sql再導回到主庫，并把對應的binlog補上。如果是Innodb，恐怕不可能有這么快速度，別和我說讓Innodb定期用導出xxx.sql機制備份，由于我平臺上最小的1個數據庫實例的數據量基本都是幾10G大小。

　　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,但是那末高并發的寫，從庫能否追的上也是1個問題，還不如通過量實例分庫分表架構來解決。

　　7、如果是用MyISAM的話，merge引擎可以大大加快利用部門的開發速度，他們只要對這個merge表做1些select count(*)操作，非常合適大項目總量約幾億的rows某1類型(如日志，調查統計)的業務表。

　　固然Innodb也不是絕對不用，用事務的項目如摹擬炒股項目，我就是用Innodb的，活躍用戶20多萬時候，也是很輕松應付了，因此我個人也是很喜歡Innodb的，只是如果從數據庫平臺利用動身，我還是會首選MyISAM。

　　另外，可能有人會說你MyISAM沒法抗太多寫操作，但是我可以通過架構來彌補，說個我現有用的數據庫平臺容量：主從數據總量在幾百T以上，每天10多億 pv的動態頁面，還有幾個大項目是通過數據接口方式調用未算進pv總數，(其中包括1個大項目由于早期memcached沒部署,致使單臺數據庫每天處理 9千萬的查詢)。而我的整體數據庫服務器平均負載都在0.5⑴左右。

InnoDB與Myisam的6大區分

MyISAM	InnoDB
構成上的區分：	每一個MyISAM在磁盤上存儲成3個文件。第1個文件的名字以表的名字開始，擴大名指出文件類型。 .frm文件存儲表定義。數據文件的擴大名為.MYD (MYData)。索引文件的擴大名是.MYI (MYIndex)。	基于磁盤的資源是InnoDB表空間數據文件和它的日志文件，InnoDB 表的大小只受限于操作系統文件的大小，1般為 2GB
事務處理上方面:	MyISAM類型的表強調的是性能，其履行數度比InnoDB類型更快，但是不提供事務支持	InnoDB提供事務支持事務，外部鍵等高級數據庫功能
SELECT UPDATE,INSERT，Delete操作	如果履行大量的SELECT，MyISAM是更好的選擇	1.如果你的數據履行大量的INSERT或UPDATE，出于性能方面的斟酌，應當使用InnoDB表 2.DELETE FROM table時，InnoDB不會重新建立表，而是1行1行的刪除。 3.LOAD TABLE FROM MASTER操作對InnoDB是不起作用的，解決方法是首先把InnoDB表改成MyISAM表，導入數據后再改成InnoDB表，但是對使用的額外的InnoDB特性（例如外鍵）的表不適用
對AUTO_INCREMENT的操作	每表1個AUTO_INCREMEN列的內部處理。 MyISAM為INSERT和UPDATE操作自動更新這1列。這使得AUTO_INCREMENT列更快（最少10%）。在序列頂的值被刪除以后就不能再利用。(當AUTO_INCREMENT列被定義為多列索引的最后1列，可以出現重使用從序列頂部刪除的值的情況）。 AUTO_INCREMENT值可用ALTER TABLE或myisamch來重置對AUTO_INCREMENT類型的字段，InnoDB中必須包括只有該字段的索引，但是在MyISAM表中，可以和其他字段1起建立聯合索引更好和更快的auto_increment處理	如果你為1個表指定AUTO_INCREMENT列，在數據詞典里的InnoDB表句柄包括1個名為自動增長計數器的計數器，它被用在為該列賦新值。自動增長計數器僅被存儲在主內存中，而不是存在磁盤上關于該計算器的算法實現，請參考 AUTO_INCREMENT列在InnoDB里如何工作
表的具體行數	select count() from table,MyISAM只要簡單的讀出保存好的行數，注意的是，當count()語句包括 where條件時，兩種表的操作是1樣的	InnoDB 中不保存表的具體行數，也就是說，履行select count(*) from table時，InnoDB要掃描1遍全部表來計算有多少行
鎖	表鎖	提供行鎖(locking on row level)，提供與 Oracle 類型1致的不加鎖讀取(non-locking read in SELECTs)，另外，InnoDB表的行鎖也不是絕對的，如果在履行1個SQL語句時MySQL不能肯定要掃描的范圍，InnoDB表一樣會鎖全表，例如update table set num=1 where name like “%aaa%”

MySQL數據庫MyISAM和InnoDB存儲引擎的比較

MySQL有多種存儲引擎，MyISAM和InnoDB是其中經常使用的兩種。這里介紹關于這兩種引擎的1些基本概念（非深入介紹）。

MyISAM是MySQL的默許存儲引擎，基于傳統的ISAM類型，支持全文搜索，但不是事務安全的，而且不支持外鍵。每張MyISAM表寄存在3個文件中：frm 文件寄存表格定義；數據文件是MYD (MYData)；索引文件是MYI (MYIndex)。

InnoDB是事務型引擎，支持回滾、崩潰恢復能力、多版本并發控制、ACID事務，支持行級鎖定（InnoDB表的行鎖不是絕對的，如果在履行1個SQL語句時MySQL不能肯定要掃描的范圍，InnoDB表一樣會鎖全表，如like操作時的SQL語句），和提供與Oracle類型1致的不加鎖讀取方式。InnoDB存儲它的表和索引在1個表空間中，表空間可以包括數個文件。

主要區分：

MyISAM是非事務安全型的，而InnoDB是事務安全型的。
MyISAM鎖的粒度是表級，而InnoDB支持行級鎖定。
MyISAM支持全文類型索引，而InnoDB不支持全文索引。
MyISAM相對簡單，所以在效力上要優于InnoDB，小型利用可以斟酌使用MyISAM。
MyISAM表是保存成文件的情勢，在跨平臺的數據轉移中使用MyISAM存儲會省去很多的麻煩。
InnoDB表比MyISAM表更安全，可以在保證數據不會丟失的情況下，切換非事務表到事務表（alter table tablename type=innodb）。

利用場景：

MyISAM管理非事務表。它提供高速存儲和檢索，和全文搜索能力。如果利用中需要履行大量的SELECT查詢，那末MyISAM是更好的選擇。
InnoDB用于事務處理利用程序，具有眾多特性，包括ACID事務支持。如果利用中需要履行大量的INSERT或UPDATE操作，則應當使用InnoDB，這樣可以提高多用戶并發操作的性能。

經常使用命令：

　　（1）查看表的存儲類型（3種）：