總結MySQL儲存引擎MyISAM與InnoDB區別

1、MySQL預設儲存引擎的變遷

在MySQL 5.1之前的版本中，預設的搜尋引擎是MyISAM，從MySQL 5.5之後的版本中，預設的搜尋引擎變更為InnoDB。

2、MyISAM與InnoDB儲存引擎的主要特點

MyISAM儲存引擎的特點是：表級鎖、不支援事務和全文索引，適合一些CMS內容管理系統作為後臺資料庫使用，但是使用大併發、重負荷生產系統上，表鎖結構的特性就顯得力不從心；

以下是MySQL 5.7 MyISAM儲存引擎的版本特性：

InnoDB儲存引擎的特點是：行級鎖、事務安全（ACID相容）、支援外來鍵、不支援FULLTEXT型別的索引(5.6.4以後版本開始支援FULLTEXT型別的索引)。InnoDB儲存引擎提供了具有提交、回滾和崩潰恢復能力的事務安全儲存引擎。InnoDB是為處理巨大量時擁有最大效能而設計的。它的CPU效率可能是任何其他基於磁碟的關聯式資料庫引擎所不能匹敵的。

以下是MySQL 5.7 InnoDB儲存引擎的版本特性：

注意：

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

兩種型別最主要的差別就是InnoDB支援事務處理與外來鍵和行級鎖。而MyISAM不支援。所以MyISAM往往就容易被人認為只適合在小專案中使用。

3、MyISAM與InnoDB效能測試

下邊兩張圖是官方提供的MyISAM與InnoDB的壓力測試結果

可以看出，隨著CPU核數的增加，InnoDB的吞吐量反而越好，而MyISAM，其吞吐量幾乎沒有什麼變化，顯然，MyISAM的表鎖定機制降低了讀和寫的吞吐量。

4、事務支援與否

MyISAM是一種非事務性的引擎，使得MyISAM引擎的MySQL可以提供高速儲存和檢索，以及全文搜尋能力，適合資料倉儲等查詢頻繁的應用；

InnoDB是事務安全的；

事務是一種高階的處理方式，如在一些列增刪改中只要哪個出錯還可以回滾還原，而MyISAM就不可以了。

5、MyISAM與InnoDB構成上的區別

（1）每個MyISAM在磁碟上儲存成三個檔案：

第一個檔案的名字以表的名字開始，副檔名指出檔案型別，.frm檔案儲存表定義。 
第二個檔案是資料檔案，其副檔名為.MYD (MYData)。 
第三個檔案是索引檔案，其副檔名是.MYI (MYIndex)。

（2）基於磁碟的資源是InnoDB表空間資料檔案和它的日誌檔案，InnoDB 表的 大小隻受限於作業系統檔案的大小，一般為 2GB。

6、MyISAM與InnoDB表鎖和行鎖的解釋

MySQL表級鎖有兩種模式：表共享讀鎖（Table Read Lock）和表獨佔寫鎖（Table Write Lock）。什麼意思呢，就是說對MyISAM表進行讀操作時，它不會阻塞其他使用者對同一表的讀請求，但會阻塞對同一表的寫操作；而對MyISAM表的寫操作，則會阻塞其他使用者對同一表的讀和寫操作。

InnoDB行鎖是透過給索引項加鎖來實現的，即只有透過索引條件檢索資料，InnoDB才使用行級鎖，否則將使用表鎖！行級鎖在每次獲取鎖和釋放鎖的操作需要消耗比表鎖更多的資源。在InnoDB兩個事務發生死鎖的時候，會計算出每個事務影響的行數，然後回滾行數少的那個事務。當鎖定的場景中不涉及Innodb的時候，InnoDB是檢測不到的。只能依靠鎖定超時來解決。

7、是否儲存資料庫表中表的具體行數

InnoDB 中不儲存表的具體行數，也就是說，執行 select count(*) from table 時，InnoDB要掃描一遍整個表來計算有多少行，但是MyISAM只要簡單的讀出儲存好的行數即可。

注意的是，當 count(*)語句包含 where條件時，兩種表的操作是一樣的。也就是 上述“6”中介紹到的InnoDB使用表鎖的一種情況。

8、如何選擇

MyISAM適合：

• （1）做很多count 的計算；
• （2）插入不頻繁，查詢非常頻繁，如果執行大量的SELECT，MyISAM是更好的選擇；
• （3）沒有事務。

InnoDB適合：

• （1）可靠性要求比較高，或者要求事務；
• （2）表更新和查詢都相當的頻繁，並且表鎖定的機會比較大的情況指定資料引擎的建立；
• （3）如果你的資料執行大量的INSERT或UPDATE，出於效能方面的考慮，應該使用InnoDB表；
• （4）DELETE FROM table時，InnoDB不會重新建立表，而是一行一行的 刪除；
• （5）LOAD TABLE FROM MASTER操作對InnoDB是不起作用的，解決方法是首先把InnoDB表改成MyISAM表，匯入資料後再改成InnoDB表，但是對於使用的額外的InnoDB特性（例如外來鍵）的表不適用。

要注意，建立每個表格的程式碼是相同的，除了最後的 TYPE引數，這一引數用來指定資料引擎。

其他區別：

1、對於AUTO_INCREMENT型別的欄位，InnoDB中必須包含只有該欄位的索引，但是在MyISAM表中，可以和其他欄位一起建立聯合索引。

2、DELETE FROM table時，InnoDB不會重新建立表，而是一行一行的刪除。

3、LOAD TABLE FROMMASTER操作對InnoDB是不起作用的，解決方法是首先把InnoDB表改成MyISAM表，匯入資料後再改成InnoDB表，但是對於使用的額外的InnoDB特性(例如外來鍵)的表不適用。

4、 InnoDB儲存引擎被完全與MySQL伺服器整合，InnoDB儲存引擎為在主記憶體中快取資料和索引而維持它自己的緩衝池。

5、對於自增長的欄位，InnoDB中必須包含只有該欄位的索引，但是在MyISAM表中可以和其他欄位一起建立聯合索引。

6、清空整個表時，InnoDB是一行一行的刪除，效率非常慢。MyISAM則會重建表。