InnoDB和MyISAM存儲引擎的差別彙總

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

2、MyISAM隻支援表級鎖，而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不支援（5.6以後支援全文索引）

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	InnoDB
資料	56,166 KB	90,736 KB
索引	67,103 KB	88,848 KB
占用總空間	123,269 KB	179,584 KB