資料庫系列：MySQL引擎MyISAM和InnoDB的比較

Brand發表於2023-10-31
資料庫MySql

1、資料庫核心知識點
資料庫系列：MySQL慢查詢分析和效能最佳化
資料庫系列：MySQL索引最佳化總結（綜合版）
資料庫系列：高併發下的資料欄位變更
資料庫系列：覆蓋索引和規避回表
資料庫系列：資料庫高可用及無損擴容
資料庫系列：使用高區分度索引列提升效能
資料庫系列：字首索引和索引長度的取捨

2、MySQL完整學習
MySQL全面瓦解30篇：合輯地址

1 比較和分析

MyISAM和InnoDB是兩種不同的資料庫儲存引擎，它們在資料儲存結構、事務支援、鎖的支援、外來鍵支援、主鍵觀念、效能和最佳化方式等方面都存在明顯的差異。

  • 資料儲存結構：MyISAM在磁碟上儲存了三個檔案，包括表的定義檔案(.frm)、資料檔案(.MYD)和索引檔案(.MYI)。而InnoDB在磁碟上儲存了兩個檔案，包括表的定義檔案(.frm) 和 資料+索引檔案(.ibd)。
  • 事務支援：MyISAM不支援事務，而InnoDB支援事務，且有完善的鎖、事務控制機制等功能。如需要執行大量的INSERT、UPDATE操作，InnoDB可以保證資料的一致性和完整性，提高多使用者併發操作的效能。
  • 鎖的支援：MyISAM只支援表鎖，效率相對較低。而InnoDB支援行鎖，效率會高一些。執行大量SELECT查詢時，MyISAM具有更好的效能，因它支援不加鎖讀取。在執行修改資料操作（UPDATE、DELETE）時，InnoDB行級鎖可以減少鎖定時間，提高併發效能。
  • 主鍵觀念：MyISAM可以不定義主鍵，如果定義了主鍵，則會是主鍵索引。而InnoDB必須要有主鍵，就算沒有定義，那麼會自動建立一個隱藏的6byte的int型的索引。
  • 效能和最佳化：MyISAM適合執行大量的select操作，如閱讀查詢密集型的系統。而InnoDB適合執行大量的insert、update操作，如寫入查詢密集型的網站。
  • 儲存空間：MyISAM儲存空間較小，而InnoDB需要更多的記憶體來儲存。
  • 對外來鍵的支援：InnoDB支援外來鍵，而MyISAM不支援。外來鍵用於關聯兩個表的資料，使得兩個表之間的關係更加清晰，同時也提高了資料的完整性。

總的來說，選擇哪種儲存引擎需要根據具體的應用場景來決定。

2 InnoDB在實踐中的幾點比較

2.1 資料計量：count(*)

MyISAM： MyISAM在執行COUNT(*)操作時，會掃描整個表，計算所有行的數量。由於MyISAM不支援事務和行級鎖，因此在高併發環境下，多個客戶端同時執行COUNT(*)操作可能會導致效能問題。但是如果查詢中使用了索引，會使用索引來加速計算行數。

InnoDB： InnoDB在執行COUNT(*)操作時，會使用統計資訊來估計行數，而不是掃描整個表。InnoDB支援事務和行級鎖，透過在統計資訊中維護行數統計，可以更快地執行COUNT(*)操作，提高效能。
如果查詢中使用了索引，也不會使用索引來計算行數。這是因為統計資訊是基於表資料，而不是基於索引。但是索引還是有益的，InnoDB會使用索引來加速滿足查詢條件的行的查詢。
當加了where條件後，兩種儲存引擎的處理方式類似，都是根據條件來按照行掃描。
例如：

select count(*) from tb_userinfo where  sex=0 and age > 22 ;

按照條件進行使用者查詢，兩種儲存引擎的處理方式類似，都是根據條件按照索引進行查詢。
所以無論哪種儲存引擎，建議都儘量建立好索引，並適當的透過where條件進行資料過濾。

2.2 全文索引

InnoDB5.6之前不支援全文索引，如果需要使用全文索引，可以使用Sphinx等搜尋引擎。而MyISAM支援全文索引，這使得在文字搜尋方面MyISAM具有更好的效能。
但是MySQL資料庫本身不是為了全文檢索而設計的，所以在資料量大併發量大的情況下，都不應該使用資料庫自帶的全文索引，會導致大量資料庫資源和記憶體內損耗，更推薦的是使用類似 es、Sphinx等專業的全文檢索引擎或元件。

2.3 事務機制

nnoDB支援事務，而MyISAM不支援。事務是一組資料庫操作命令組成的程式邏輯單元，可以保證這組命令在執行過程中符合ACID特性（原子性、一致性、隔離性和永續性）。如果應用中需要執行大量的INSERT或UPDATE操作，使用InnoDB可以保證資料的一致性和完整性，提高多使用者併發操作的效能

2.4 外來鍵

InnoDB支援外來鍵，而MyISAM不支援。外來鍵用於關聯兩個表的資料，使得兩個表之間的關係更加清晰，同時也提高了資料的完整性。
但是在資料量大併發量大的情況下，使用外來鍵可能會導致效能瓶頸和死鎖問題。
因此，為了提高效能和併發性，避免使用外來鍵可能是一個更好的選擇。在處理高度併發的場景時，可以考慮使用其他方法來管理資料的一致性和引用完整性，例如使用應用程式級別的邏輯或快取等。

2.5 行鎖與表鎖

MyISAM只支援表級鎖，而InnoDB支援行級鎖。表級鎖是加鎖時對整張表進行加鎖，行級鎖是對錶中的某一行進行加鎖。因此，在執行大量SELECT查詢時，MyISAM具有更好的效能，因為它支援不加鎖讀取。
然而，在執行需要修改資料的操作（如UPDATE、DELETE）的需求時，InnoDB的行級鎖可以減少鎖定時間和鎖定的影響範圍，提高併發效能。
我們在實際的業務場景中，絕大部分是讀寫混合的（基本都是讀多寫少），資料量和併發量只要上去了，都對效能有一定要求，所以還是推薦使用InnoDB。
需要注意的點是：儘量建立合適的索引，因為InnoDB的行鎖是實現在索引上的，如果沒有命中索引，就退化為表鎖，那對併發效能反而有所降低。

舉例如下：

# 表設計
tb_userinfo(id, username, age, sex, tel) innodb;
id PK # 主鍵預設建立索引

# 命中索引，執行行鎖
update tb_userinfo set sex=0 where id=10086;

# 未命中索引，退化為表鎖
update tb_userinfo set sex=0 where username='brand';

所以綜上，InnoDB儘可能建立好合適的索引，否則鎖粒度變成表鎖，併發效能會受到很大的影響。

3 總結

總的來說，選擇哪種儲存引擎需要根據具體的應用場景來決定。如果需要執行大量的SELECT查詢，且不需要事務支援，那麼MyISAM可能一個選擇。如果需要執行大量的INSERT或UPDATE操作，且需要事務支援、行級鎖和外來鍵支援，那麼InnoDB可能是一個更好的選擇。
在現有的網際網路場景下，對大資料的處理要求很頻繁，對高併發效能要求也很高，所以InnoDB是更優的選擇。

相關文章