MySQL資料庫之MyISAM與InnoDB的區別

標籤：sql   hit   儲存   緩衝池   執行個體   項目   效能   應用   適合   

MySQL資料庫之MyISAM與InnoDB的區別從以下幾個方面：

1、儲存結構 
每個MyISAM在磁碟上儲存成三個檔案。第一個檔案的名字以表的名字開始，副檔名指出檔案類型。 
.frm檔案儲存體表定義。 
資料檔案的副檔名為.MYD (MYData)。 
索引檔案的副檔名是.MYI (MYIndex)。

2、儲存空間 
MyISAM：可被壓縮，儲存空間較小。 
InnoDB：需要更多的記憶體和儲存，它會在主記憶體中建立其專用的緩衝池用於高速緩衝資料和索引。 
MyISAM的索引和資料是分開的，並且索引是有壓縮的，記憶體使用量率就對應提高了不少。能載入更多索引，而Innodb是索引和資料是緊密捆綁的，沒有使用壓縮從而會造成Innodb比MyISAM體積龐大不小

3、交易處理 
MyISAM類型的表強調的是效能，其執行數度比InnoDB類型更快，但是不支援外鍵、不提供事務支援。 
InnoDB提供事務支援事務，外部鍵（foreign key）等進階資料庫功能。

SELECT、UPDATE、INSERT、Delete操作 
如果執行大量的SELECT，MyISAM是更好的選擇。 
如果你的資料執行大量的INSERT或UPDATE，出於效能方面的考慮，應該使用InnoDB表。 
DELETE FROM table時，InnoDB不會重建立立表，而是一行一行的刪除。而MyISAM則是重建立立表。在innodb上如果要清空儲存有大量資料的表，最好使用truncate table這個命令。

AUTO_INCREMENT 
MyISAM：可以和其他欄位一起建立聯合索引。引擎的自動成長列必須是索引，如果是複合式索引，自動成長可以不是第一列，他可以根據前面幾列進行排序後遞增。 
InnoDB：InnoDB中必須包含只有該欄位的索引。引擎的自動成長列必須是索引，如果是複合式索引也必須是複合式索引的第一列。

4、表的具體行數 
MyISAM：儲存有表的總行數，如果select count(*) from table;會直接取出該值。 
InnoDB：沒有儲存表的總行數，如果使用select count(*) from table；就會遍曆整個表，消耗相當大，但是在加了where後，myisam和innodb處理的方式都一樣。

5、全文索引 
MyISAM：支援 FULLTEXT類型的全文索引。不支援中文。 
InnoDB：不支援FULLTEXT類型的全文索引，但是innodb可以使用sphinx外掛程式支援全文索引，並且效果更好。

6、表鎖差異 
MyISAM：只支援表級鎖，只支援表級鎖，使用者在操作myisam表時，select，update，delete，insert語句都會給表自動加鎖。 
InnoDB：支援事務和行級鎖，是innodb的最大特色。行鎖大幅度提高了多使用者並行作業的新能。但是InnoDB的行鎖也不是絕對的，如果在執行一個SQL語句時MySQL不能確定要掃描的範圍，InnoDB表同樣會鎖全表， 例如update table set num=1 where name like “%aaa%”

一般來說： 
MyISAM適合： 
(1)做很多count 的計算； 
(2)插入不頻繁，查詢非常頻繁； 
(3)沒有事務。 
InnoDB適合： 
(1)可靠性要求比較高，或者要求事務； 
(2)表更新和查詢都相當的頻繁，並且表鎖定的機會比較大的情況。

 

　兩種類型最主要的差別就是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表做一些select count(*)操作，非常適合大項目總量約幾億的rows某一類型(如日誌，調查統計)的業務表。

　　當然Innodb也不是絕對不用，用事務的項目就用Innodb的。另外，可能有人會說你MyISAM無法抗太多寫操作，但是可以通過架構來彌補。

 

MySQL資料庫之MyISAM與InnoDB的區別