1、MyISAM：默認表類型，它是基于傳統的ISAM類型，ISAM是Indexed Sequential Access Method (有索引的順序訪問方法) 的縮寫，它是存儲記錄和文件的標準方法。不是事務安全的，而且不支持外鍵，如果執行大量的select，insert MyISAM比較適合。

2、InnoDB：支持事務安全的引擎，支持外鍵、行鎖、事務是他的最大特點。如果有大量的update和insert，建議使用InnoDB，特別是針對多個并發和QPS較高的情況。

一、表鎖差異

MyISAM:

myisam只支持表級鎖，用戶在操作myisam表時，select，update，delete，insert語句都會給表自動加鎖，如果加鎖以后的表滿足insert并發的情況下，可以在表的尾部插入新的數據。也可以通過lock table命令來鎖表，這樣操作主要是可以模仿事務，但是消耗非常大，一般只在實驗演示中使用。

InnoDB ：

Innodb支持事務和行級鎖，是innodb的最大特色。

事務的ACID屬性：atomicity,consistent,isolation,durable。

并發事務帶來的幾個問題：更新丟失，臟讀，不可重復讀，幻讀。

事務隔離級別：未提交讀(Read uncommitted)，已提交讀(Read committed)，可重復讀(Repeatable read)，可序列化(Serializable)

四種隔離級別的比較

讀數據一致性及并發副作用

?

隔離級別

讀數據一致性

臟讀

不可重復讀

幻讀

為提交讀(read uncommitted)?? ?
最低級別，不讀物理上順壞的數據

是

是

是

已提交讀(read committed)?? ?
語句級

否

是

是

可重復讀(Repeatable red)?? ?
事務級

否

否

是

可序列化(Serializable)?? ?
最高級別，事務級

否

否

否

?

查看mysql的默認事務隔離級別“show global variables like ‘tx_isolation’; ”

Innodb的行鎖模式有以下幾種：共享鎖，排他鎖，意向共享鎖(表鎖)，意向排他鎖(表鎖)，間隙鎖。

注意：當語句沒有使用索引，innodb不能確定操作的行，這個時候就使用的意向鎖，也就是表鎖

關于死鎖：

什么是死鎖？當兩個事務都需要獲得對方持有的排他鎖才能完成事務，這樣就導致了循環鎖等待，也就是常見的死鎖類型。

解決死鎖的方法：

1、??數據庫參數

2、??應用中盡量約定程序讀取表的順序一樣

3、??應用中處理一個表時，盡量對處理的順序排序

4、??調整事務隔離級別（避免兩個事務同時操作一行不存在的數據，容易發生死鎖）

二、數據庫文件差異
MyISAM ：

myisam屬于堆表

myisam在磁盤存儲上有三個文件，每個文件名以表名開頭，擴展名指出文件類型。

.frm 用于存儲表的定義

.MYD 用于存放數據

.MYI 用于存放表索引

myisam表還支持三種不同的存儲格式：

靜態表(默認，但是注意數據末尾不能有空格，會被去掉)

動態表

壓縮表

InnoDB ：

innodb屬于索引組織表

innodb有兩種存儲方式，共享表空間存儲和多表空間存儲

兩種存儲方式的表結構和myisam一樣，以表名開頭，擴展名是.frm。

如果使用共享表空間，那么所有表的數據文件和索引文件都保存在一個表空間里，一個表空間可以有多個文件，通過innodb_data_file_path和innodb_data_home_dir參數設置共享表空間的位置和名字，一般共享表空間的名字叫ibdata1-n。

如果使用多表空間，那么每個表都有一個表空間文件用于存儲每個表的數據和索引，文件名以表名開頭，以.ibd為擴展名。

三、索引差異

1、關于自動增長

myisam引擎的自動增長列必須是索引，如果是組合索引，自動增長可以不是第一列，他可以根據前面幾列進行排序后遞增。

innodb引擎的自動增長咧必須是索引，如果是組合索引也必須是組合索引的第一列。

2、關于主鍵

myisam允許沒有任何索引和主鍵的表存在，

myisam的索引都是保存行的地址。

innodb引擎如果沒有設定主鍵或者非空唯一索引，就會自動生成一個6字節的主鍵(用戶不可見)

innodb的數據是主索引的一部分，附加索引保存的是主索引的值。

3、關于count()函數

myisam保存有表的總行數，如果select count(*) from table;會直接取出出該值

innodb沒有保存表的總行數，如果使用select count(*) from table；就會遍歷整個表，消耗相當大，但是在加了wehre???????條件后，myisam和innodb處理的方式都一樣。

4、全文索引

myisam支持 FULLTEXT類型的全文索引

innodb不支持FULLTEXT類型的全文索引，但是innodb可以使用sphinx插件支持全文索引，并且效果更好。（sphinx???是一個開源軟件，提供多種語言的API接口，可以優化mysql的各種查詢）

5、delete from table

使用這條命令時，innodb不會從新建立表，而是一條一條的刪除數據，在innodb上如果要清空保存有大量數據的表，最???????好不要使用這個命令。(推薦使用truncate table，不過需要用戶有drop此表的權限)

6、索引保存位置

myisam的索引以表名+.MYI文件分別保存。

innodb的索引和數據一起保存在表空間里。

四、開發的注意事項

1、可以用 show create table tablename 命令看表的引擎類型。

2、對不支持事務的表做start/commit操作沒有任何效果，在執行commit前已經提交。

3、可以執行以下命令來切換非事務表到事務（數據不會丟失），innodb表比myisam表更安全：alter table tablename type=innodb;或者使用 alter table tablename engine = innodb;

4、默認innodb是開啟自動提交的，如果你按照myisam的使用方法來編寫代碼頁不會存在錯誤，只是性能會很低。如何在編寫代碼時候提高數據庫性能呢？

a、盡量將多個語句綁到一個事務中，進行提交，避免多次提交導致的數據庫開銷。

b、在一個事務獲得排他鎖或者意向排他鎖以后，如果后面還有需要處理的sql語句，在這兩條或者多條sql語句之間程序應盡量少的進行邏輯運算和處理，減少鎖的時間。

c、盡量避免死鎖

d、sql語句如果有where子句一定要使用索引，盡量避免獲取意向排他鎖。

f、針對我們自己的數據庫環境，日志系統是直插入，不修改的，所以我們使用混合引擎方式，ZION_LOG_DB照舊使用myisam存儲引擎，只有ZION_GAME_DB，ZION_LOGIN_DB，DAUM_BILLING使用Innodb引擎。

五、究竟該怎么選擇

下面先讓我們回答一些問題：???

◆你的數據庫有外鍵嗎？???

◆你需要事務支持嗎？???

◆你需要全文索引嗎？???

◆你經常使用什么樣的查詢模式？???

◆你的數據有多大？???
??
myisam只有索引緩存???
innodb不分索引文件數據文件 innodb buffer???
myisam只能管理索引，在索引數據大于分配的資源時，會由操作系統來cache；數據文件依賴于操作系統的cache。innodb不管是索引還是數據，都是自己來管理??
??
思考上面這些問題可以讓你找到合適的方向，但那并不是絕對的。如果你需要事務處理或是外鍵，那么InnoDB 可能是比較好的方式。如果你需要全文索引，那么通常來說 MyISAM是好的選擇，因為這是系統內建的，然而，我們其實并不會經常地去測試兩百萬行記錄。所以，就算是慢一點，我們可以通過使用Sphinx從InnoDB中獲得全文索引。??
??
數據的大小，是一個影響你選擇什么樣存儲引擎的重要因素，大尺寸的數據集趨向于選擇InnoDB方式，因為其支持事務處理和故障恢復。數據庫的在小決定了故障恢復的時間長短，InnoDB可以利用事務日志進行數據恢復，這會比較快。而MyISAM可能會需要幾個小時甚至幾天來干這些事，InnoDB只需要幾分鐘。??
??
操作數據庫表的習慣可能也會是一個對性能影響很大的因素。比如： COUNT() 在 MyISAM 表中會非常快，而在InnoDB 表下可能會很痛苦。而主鍵查詢則在InnoDB下會相當相當的快，但需要小心的是如果我們的主鍵太長了也會導致性能問題。大批的inserts 語句在 MyISAM下會快一些，但是updates 在InnoDB下會更快一些——尤其在并發量大的時候。??
??
所以，到底你檢使用哪一個呢？根據經驗來看，如果是一些小型的應用或項目，那么MyISAM 也許會更適合。當然，在大型的環境下使用 MyISAM 也會有很大成功的時候，但卻不總是這樣的。如果你正在計劃使用一個超大數據量的項目，而且需要事務處理或外鍵支持，那么你真的應該直接使用 InnoDB方式。但需要記住InnoDB 的表需要更多的內存和存儲，轉換100GB 的MyISAM 表到InnoDB 表可能會讓你有非常壞的體驗。??
??
對于支持事務的InnoDB類型的表，影響速度的主要原因是AUTOCOMMIT默認設置是打開的，而且程序沒有顯式調用BEGIN 開始事務，導致每插入一條都自動Commit，嚴重影響了速度。可以在執行sql前調用begin，多條sql形成一個事務（即使autocommit打開也可以），將大大提高性能。??

InnoDB

InnoDB 給 MySQL 提供了具有事務(commit)、回滾(rollback)和崩潰修復能力 (crash recovery capabilities)的事務安全(transaction-safe (ACID compliant))型表。 InnoDB 提供了行鎖(locking on row level)，提供與 Oracle 類型一致的不加鎖讀取(non- locking read in SELECTs)。這些特性均提高了多用戶并發操作的性能表現。在InnoDB表中不需要擴大鎖定 (lock escalation)，因為 InnoDB 的列鎖定(row level locks)適宜非常小的空間。 InnoDB 是 MySQL 上第一個提供外鍵約束(FOREIGN KEY constraints)的表引擎。??

InnoDB 的設計目標是處理大容量數據庫系統，它的 CPU 利用率是其它基于磁盤的關系數據庫引擎所不能比的。在技術上，InnoDB 是一套放在 MySQL 后臺的完整數據庫系統，InnoDB 在主內存中建立其專用的緩沖池用于高速緩沖數據和索引。 InnoDB 把數據和索引存放在表空間里，可能包含多個文件，這與其它的不一樣，舉例來說，在 MyISAM 中，表被存放在單獨的文件中。InnoDB 表的大小只受限于操作系統的文件大小，一般為 2 GB。??
InnoDB所有的表都保存在同一個數據文件 ibdata1 中（也可能是多個文件，或者是獨立的表空間文件）,相對來說比較不好備份，免費的方案可以是拷貝數據文件、備份 binlog，或者用 mysqldump。??

MyISAM???
MyISAM 是MySQL缺省存貯引擎 .???
每張MyISAM 表被存放在三個文件 。frm 文件存放表格定義。 數據文件是MYD (MYData) 。 索引文件是 MYI (MYIndex) 引伸。???
因為MyISAM相對簡單所以在效率上要優于InnoDB..小型應用使用MyISAM是不錯的選擇.???
MyISAM表是保存成文件的形式,在跨平臺的數據轉移中使用MyISAM存儲會省去不少的麻煩???
??
以下是一些細節和具體實現的差別：???
??
1.InnoDB不支持FULLTEXT類型的索引。???
2.InnoDB 中不保存表的具體行數，也就是說，執行select count(*) from table時，InnoDB要掃描一遍整個表來計算有多少行，但是MyISAM只要簡單的讀出保存好的行數即可。注意的是，當count(*)語句包含 where條件時，兩種表的操作是一樣的。??
3.對于AUTO_INCREMENT類型的字段，InnoDB中必須包含只有該字段的索引，但是在MyISAM表中，可以和其他字段一起建立聯合索引。???
4.DELETE FROM table時，InnoDB不會重新建立表，而是一行一行的刪除。???
5.LOAD TABLE FROM MASTER操作對InnoDB是不起作用的，解決方法是首先把InnoDB表改成MyISAM表，導入數據后再改成InnoDB表，但是對于使用的額外的InnoDB特性（例如外鍵）的表不適用。??

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

任何一種表都不是萬能的，只用恰當的針對業務類型來選擇合適的表類型，才能最大的發揮MySQL的性能優勢。???

六、重復地總結一遍

1、MyISAM不支持事務，InnoDB是事務類型的存儲引擎，當我們的表需要用到事務支持的時候，那肯定是不能選擇MyISAM了。

2、MyISAM只支持表級鎖，BDB支持頁級鎖和表級鎖默認為頁級鎖，而InnoDB支持行級鎖和表級鎖默認為行級鎖
?
表級鎖：直接鎖定整張表，在鎖定期間，其他進程無法對該表進行寫操作，如果設置的是寫鎖，那么其他進程讀也不允許
?
MyISAM是表級鎖定的存儲引擎，它不會出現死鎖問題
?
對于write，表鎖定原理如下：
?
如果表上沒有鎖，在其上面放置一個寫鎖，否則，把鎖定請求放在寫鎖隊列中。
?
對于read，表鎖定原理如下 ：
?
如果表上沒有寫鎖定，那么把一個讀鎖放在其上面，否則把鎖請求放在讀鎖定隊列中
?
當一個鎖定被釋放時，表可被寫鎖定隊列中的線程得到，然后才是讀鎖定隊列中的線程。這意味著，如果你在一個表上有許多更新，那么你的SELECT語句將等到所有的寫鎖定線程執行完。

行級鎖：只對指定的行進行鎖定，其他進程還是可以對表中的其他行進行操作的。
?
行級鎖是Mysql粒度最小的一種鎖，它能大大的減少數據庫操作的沖突，但是粒度越小實現成本也越大。
?
行級鎖可能會導致“死鎖”，那到底是怎么導致的呢，分析原因：Mysql行級鎖并不是直接鎖記錄，而是鎖索引。索引分為主鍵索引和非主鍵索引兩種，如果一條sql語句操作了主鍵索引，那么Mysql就會鎖定這個主鍵索引，如果sql語句操作的是非主鍵索引，那么Mysql會先鎖定這個非主鍵索引，再去鎖定主鍵索引。
?
在UPDATE 和 DELETE操作時Mysql不僅會鎖定所有WHERE 條件掃描過得索引，還會鎖定相鄰的鍵值。
?
“死鎖”舉例分析：
?
表Test：（ID,STATE,TIME）??主鍵索引：ID??非主鍵索引：STATE
?
當執行"UPDATE??STATE =1011 WHERE STATE=1000"??語句的時候會鎖定STATE索引，由于STATE 是非主鍵索引,所以Mysql還會去請求鎖定ID索引
?
當另一個SQL語句與語句1幾乎同時執行時：“UPDATE STATE=1010 WHERE ID=1”??對于語句2 Mysql會先鎖定ID索引，由于語句2操作了STATE字段，所以Mysql還會請求鎖定STATE索引。這時。彼此鎖定著對方需要的索引，又都在等待對方釋放鎖定。所以出現了"死鎖"的情況。
?
行級鎖的優點：
?
有許多線程訪問不同的行時，只存在少量的沖突。
?
回滾時只有少量的更改
?
可以長時間鎖定單一的行
?
行級鎖缺點：
?
相對于頁級鎖和表級鎖來說占用了更多的內存
?
當表的大部分行在使用時，比頁級鎖和表級鎖慢，因為你必須獲得更多的鎖
?
當在大部分數據上經常使用GROUP BY操作，肯定會比表級鎖和頁級鎖慢。
?
頁級鎖：表級鎖速度快，但是沖突多；行級鎖速度慢，但沖突少；頁級鎖就是他倆折中的，一次鎖定相鄰的一組記錄。

3、MyISAM引擎不支持外鍵，InnoDB支持外鍵

4、MyISAM引擎的表在大量高并發的讀寫下會經常出現表損壞的情況
?
我們以前做的項目就遇到這個問題，表的INSERT 和 UPDATE操作很頻繁，原來用的MyISAM引擎，導致表隔三差五就損壞，后來更換成了InnoDB引擎。
?
其他容易導致表損壞原因：
?
服務器突然斷電導致數據文件損壞，強制關機（mysqld未關閉情況下）導致表損壞
?
mysqld進程在寫入操作的時候被殺掉
?
磁盤故障
?
表損壞常見癥狀：
?
查詢表不能返回數據或返回部分數據
?
打開表失敗： Can’t open file: ‘×××.MYI’ (errno: 145) 。
?
Error: Table 'p' is marked as crashed and should be repaired 。

Incorrect key file for table: '...'. Try to repair it
?
Mysql表的恢復：

對于MyISAM表的恢復：

可以使用Mysql自帶的myisamchk工具： myisamchk -r tablename??或者 myisamchk -o tablename（比前面的更保險） 對表進行修復

5、對于count()查詢來說MyISAM更有優勢

因為MyISAM存儲了表中的行數記錄，執行SELECT COUNT() 的時候可以直接獲取到結果，而InnoDB需要掃描全部數據后得到結果。

但是注意一點：對于帶有WHERE 條件的 SELECT COUNT()語句兩種引擎的表執行過程是一樣的，都需要掃描全部數據后得到結果

6、 InnoDB是為處理巨大數據量時的最大性能設計，它的CPU效率可能是任何其它基于磁盤的關系數據庫引擎所不能匹敵的。

7、MyISAM支持全文索引（FULLTEXT），InnoDB不支持

8、MyISAM引擎的表的查詢、更新、插入的效率要比InnoDB高

網上截取了前輩們測試結論：?

測試方法：連續提交10個query， 表記錄總數：38萬 ， 時間單位 s
?
????????引擎類型????????????????????MyISAM????????????????InnoDB??????????????性能相差
?
????????count??????????????????????0.0008357????????????3.0163????????????????3609
?
????????查詢主鍵??????????????????0.005708??????????????0.1574????????????????27.57
?
????????查詢非主鍵??????????????????24.01??????????????????80.37????????????????3.348
?
????????更新主鍵??????????????????0.008124????????????0.8183????????????????100.7
?
????????更新非主鍵????????????????0.004141????????????0.02625??????????????6.338
?
????????插入????????????????????????0.004188????????????0.3694????????????????88.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 KB????????179,584 KB

七、性能對比

測試的版本是mysql ?Ver 14.14 Distrib 5.1.49, for debian-linux-gnu (i686)，使用的是Innodb plugin 1.0.8（官方稱比built-in版本性能更好）和默認的MyISAM。

測試機器是筆記本，配置如下：Intel 酷睿2雙核 P8600，2G*2 DDR3 1066內存，320G硬盤5400轉。

測試一：數據插入性能測試，這里我分別對innodb_flush_log_at_trx_commit參數打開和關閉都測了了一下，每次測試都是運行40s，表中數字都是實際插入條數。

???????????????????????MyISAM ????????????????Innodb (打開) ?????Innodb (關閉)

單線程，逐個插入 ????????120000 ????????????????60000 ?????????????60000

4線程，逐個插入 ?????????40000*4 ???????????????20000*4 ???????????40000*4

單線程，批量100條/次插入 ?3600*100 ??????????????800*100 ???????????3000*100

單線程，批量200條/次插入 ?1800*200 ??????????????400*200 ???????????1600*200

可以發現批量插入的性能遠高于單條插入，但是一次批量的大小對性能影響不大。每條記錄是否都刷新日志的參數對innodb性能的影響巨大。總體上來說，MyISAM性能更優一點。這里有一點需要注意，在插入測試過程中，我對系統資源進行了監控，發現MyISAM對系統資源占用很低，但是Innodb對磁盤占用卻很高，應該是對事務控制多了很多需要記錄的日志。

測試二：數據讀取性能測試。每次隨機讀取1000條記錄，反復進行讀取。

????????????????????????MyISAM ???????Innodb

單線程，200次讀取 ????????5.7s ?????????16.7s

4線程，200次讀取 ?????????12s ??????????40.8s

可以看出MyISAM的讀取性能非常恐怖，性能差距在3倍的樣子。

以上兩個測試發現MyISAM在無事務的需求下幾乎完勝，但是要知道它是表鎖，Innodb是行鎖，那么在并發讀寫同時存在的情況下，那結果會是怎么樣呢？！

測試三：兩個線程并發寫入，2個線程并發讀取。

???????????????????????MyISAM ????????????????????????????????Innodb

逐個插入 ???????????????寫入40s：10000*2 讀取200次*2：14s ???????寫入40s：60000*2 讀取200次*2：50s

批量100條/次插入 ???????寫入40s：1000*100*2 讀取200次*2：10s ?????寫入40s：1500*100*2 讀取200次*2：50s

這下立刻顯示出Innodb在并發情況下強勁的性能，幾乎沒有什么性能衰減。而MyISAM單條插入速度變得非常慢，批量插入也下降了40%性能。

總結一下，在寫多讀少的應用中還是Innodb插入性能更穩定，在并發情況下也能基本，如果是對讀取速度要求比較快的應用還是選MyISAM。
---------------------?
作者：wjtlht928?
來源：CSDN?
原文：https://blog.csdn.net/wjtlht928/article/details/46641865?
版權聲明：本文為博主原創文章，轉載請附上博文鏈接！

總結

以上是生活随笔為你收集整理的MyISAM与InnoDB两者之间区别与选择，详细总结，性能对比的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。