MySQL中InnoDB和MyISAM的儲存引擎的差異

MySQL資料庫區別於其他資料庫的很重要的一個特點就是其外掛程式的表格儲存引擎，其基於表，而不是資料庫。由於每個儲存引擎都有其特點，因此我們可以針對每一張表來挑選最合適的儲存引擎。

作為DBA，我們應該要深刻的認識儲存引擎。今天介紹兩種最常見的儲存引擎和它們的差異：InnoDB和MyISAM。

InnoDB儲存引擎

#InnoDB儲存引擎支援事務，其設計目標主要是以OLTP（On Line Transaction Processing 線上事務處理）的應用程式。特點為行鎖設計、支援外鍵，並支援非鎖定讀取。從5.5.8版本開始，InnoDB就成為了MySQL的預設儲存引擎。

InnoDB儲存引擎採用聚集索引（clustered）的方式來儲存數據，因此每個表都是按照主鍵的順序進行存放，如果沒有指定主鍵，InnoDB會為每行自動產生一個6位元組的ROWID作為主鍵。

MyISAM儲存引擎

#MyISAM儲存引擎不支援交易、表鎖設計，支援全文索引，主要面向OLAP（On Line Analytical Processing 線上分析處理）應用，適用於資料倉儲等查詢頻繁的場景。在5.5.8版本之前，MyISAM是MySQL的預設儲存引擎。該引擎代表著對海量資料進行查詢和分析的需求。它強調效能，因此在查詢的執行速度比InnoDB更快。

InnoDB和MyISAM的差異

交易

為了資料庫操作的原子性，我們需要事務。保證一組操作要嘛都成功，要嘛都失敗，例如轉帳的功能。我們通常將多條SQL語句放在begin和commit之間，組成一個交易。

InnoDB支持，MyISAM不支援。

主鍵

由於InnoDB的聚集索引，其如果沒有指定主鍵，就會自動產生主鍵。
MyISAM支援沒有主鍵的表存在。

外鍵

為了解決複雜邏輯的依賴，我們需要外鍵。例如高考成績的輸入，必須歸屬於某位同學，我們就需要高考成績資料庫裡有准考證號的外鍵。

InnoDB支持，MyISAM不支援。

索引

為了最佳化查詢的速度，進行排序和匹配查找，我們需要索引。例如所有人的姓名從a-z首字母進行順序存儲，當我們查找zhangsan或第44位的時候就可以很快的定位到我們想要的位置進行查找。

InnoDB是聚集索引，資料和主鍵的聚集索引綁定在一起，透過主鍵索引效率很高。如果透過其他列的輔助索引來進行查找，需要先查找到聚集索引，再查詢到所有數據，需要兩次查詢。

MyISAM是非聚集索引，資料檔案是分離的，索引保存的是資料的指標。

從InnoDB 1.2.x版本，MySQL5.6版本後，兩者都支援全文索引。

auto_increment自增

#對於自增數的字段，InnoDB要求該列必須是索引，同時必須是索引的第一個列，否則會報錯：

mysql> create table test(
    -> a int auto_increment,
    -> b int,
    -> key(b,a)
    -> ) engine=InnoDB;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

把(b,a)順序替換為(a,b)即可。

而MyISAM可以將該欄位與其他欄位隨意順序組成成聯合索引。

表格行數

很常見的需求是看表中有多少條數據，此時我們需要select count(*) from table_name。

InnoDB不儲存表格行數，需要進行全表掃描。 MyISAM用一個變數儲存，直接讀取該值，更快。當時當有where查詢的時候，兩者一樣。

儲存

資料庫的檔案都是需要在磁碟中進行存儲，當應用程式需要時再讀取到記憶體中。一般包含資料檔、索引檔。

InnoDB分為：

• .frm表格結構檔
• ##.ibdata1共享表空間
• .ibd表格獨佔空間
• .redo日誌檔

MyISAM分成三個檔案：

• .frm儲存表定義
• #.MYD儲存表資料
• #.MYI儲存資料表索引

執行速度#

如果你的操作是大量的查詢操作，例如SELECT，使用MyISAM效能會更好。
如果大部分是刪除和變更的操作，使用InnoDB。

InnoDB和MyISAM的索引都是B 樹索引，透過索引可以查詢到資料的主鍵，不熟悉B 樹的可以查看MySQL InnoDB索引原理和演算法。兩者的效能差異主要在於查詢到資料主鍵後兩者的處理方式卻不同。

InnoDB會快取索引和資料文件，一般以16KB為一個最小單元（資料頁大小）和磁碟進行交互，InnoDB#在查詢到索引數據後實際得到的是主鍵的ID，它需要在內存中的數據頁中查找該行的全部數據，但如果該數據不是加載過的熱數據，還需要進行資料頁的查找和替換，這其中可能牽涉到多次I/O操作和記憶體中資料查找，導致耗時較高。

而MyISAM儲存引擎只快取索引文件，不緩存資料文件，其資料檔案的快取直接使用作業系統的緩存，這一點非常獨特。此時相同的空間能夠載入更多的索引，因此當快取空間有限時，MyISAM的索引資料頁替換次數會更少。根據前面我們知道MyISAM的檔案分成MYI和MYD，當我們透過MYI找出主鍵ID時，其實得到是MYD資料檔的offset偏移量，找尋資料比InnoDB尋址要快的多。

但由於MyISAM是表鎖，而InnoDB支援行鎖，因此在牽涉到大量寫入操作時，InnoDB的並發效能比MyISAM好很多。同時InnoDB也透過MVVC多重版本控制來提高並發讀寫效能。

delete刪除資料

當呼叫delete from table時，MyISAM會直接重建表，InnoDB會一行一行的刪除，但可以用truncate table取代。參考: mysql清空表資料的兩種方式和差異。

鎖定

MyISAM僅支援表鎖，每次操作鎖定整個表。
InnoDB支援行鎖，每次操作都會鎖住最小數量的行資料。

表鎖比起行鎖消耗的資源較少，且不會出現死鎖，但同時並發效能差。行鎖消耗更多的資源，速度較慢，且可能發生死鎖，但是因為鎖定的粒度小、資料少，並發性能好。如果InnoDB的一條語句無法確定要掃描的範圍，也會鎖定整個表。

當行鎖發生死鎖的時候，會計算每個交易影響的行數，然後回滾行數較少的交易。

資料復原

MyISAM當機後無法快速的安全性復原。 InnoDB有一套完善的復原機制。

資料快取

MyISAM僅快取索引數據，透過索引查詢資料。 InnoDB不僅快取索引數據，同時快取數據信息，將數據按頁讀取到快取池，按LRU（Latest Rare Use 最近最少使用）演算法來進行更新。

如何選擇儲存引擎

建立表格的語句都是相同的，只有最後的type來指定存儲引擎。

MyISAM

1、大量查詢總count

2、查詢頻繁，插入不頻繁

3、沒有交易操作

InnoDB

#1、需要高可用性，或需要交易

2、表格更新頻繁

推薦學習：MySQL教學

以上是MySQL中InnoDB和MyISAM的儲存引擎的差異的詳細內容。更多資訊請關注PHP中文網其他相關文章！