(轉)InnoDB與MyISAM引擎區別

標籤：環境   如何   tags   問題   lob   run   label   不同   方案   

MyISAM與InnoDB兩者之間區別與選擇，詳細總結，效能對比2015年06月25日 21:58:42 閱讀數：1827更多個人分類： mysql 

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。

(轉)InnoDB與MyISAM引擎區別