MySQL是一個資料庫，但是儲存資料的方式有很多種，也就是storage engine有很多種。不同的storage engine在內部運作就有不同的方式，也適用不同的應用，當然在語法上也會有不同的結果。
Storage engine是什麼?
Storage engine可以想成汽車的引擎，引擎有分成好多種類，例如汽油引擎、柴油引擎，或是依照汽缸的排列方式不同而有V型引擎、W型引擎等。這些引擎的目的都是一樣的，就是讓汽車動起來，只是處理方式不同，也各有適用的應用。
Storage engine是指資料庫管理系統(DBMS)中負責存儲和檢索資料的組件或模組。它通常是一個軟體層，負責將資料寫入硬碟或其他存儲媒介，並提供高效的查詢和資料檢索功能。
不同的DBMS可能會提供不同的存儲引擎，每個存儲引擎都有其獨特的優缺點。例如，MySQL資料庫管理系統提供了多個存儲引擎，包括InnoDB、MyISAM、Memory等，每個引擎都有其自己的特性和適用場景。 選擇合適的存儲引擎對於資料庫的性能和穩定性非常重要，因此在設計和實現資料庫時，需要仔細考慮存儲引擎的選擇。
要知道MySQL支援哪些storage engine，你可以使用指令
SHOW ENGINES;
執行結果如下 :
雖然有這麼多的storage engine，常用的只有兩種InnoDB與MyISAM。
如何知道我的表單是哪個storage engine呢? 請執行以下指令 :
SELECT table_schema, table_name
FROM INFORMATION_SCHEMA.TABLES
WHERE engine = 'Innodb';
把InnoDB改為MyISAM就可以看MyISAM的。
執行結果如下 :
過去，MyISAM被認為是比較好的選擇，因為它快速且經過大量讀取操作優化。它被用於許多不同的用途，從執行數據分析到開發用於論壇的簡單內容管理系統，或構建較小的搜索引擎。一些開發人員創建了針對MyISAM量身定制的工具和解決方案，因為它被認為比InnoDB更簡單。在2009年，它被InnoDB替換為預設的MySQL存儲引擎。現在如果你不特別設定，應該都是使用InnoDB。
InnoDB是MySQL的通用存儲引擎，從存儲子系統發展成為一個功能完整的存儲引擎。由於其高性能和可靠性的結合，它從版本5.6開始成為MySQL的預設引擎。雖然MySQL決定資料保存到資料庫的方式，但InnoDB存儲引擎將資料存儲在磁盤上或在主記憶體中以便快速訪問。當交易完成時，資料根據交易寫入存儲介質。重要的是要注意，未完成的更改不會存儲在資料庫中。
MyISAM引擎在存儲大量資料方面表現不太好，因為它將所有資料都存儲在一個表中。當您需要向資料庫添加資料時，您必須鎖定整個表，這可能會導致您的資料庫停止工作，直到它被解鎖。在InnoDB引擎中，每行資料都單獨存儲在一個單獨的表中。這意味著當您將資料插入到MySQL資料庫時，您不需要鎖定所有行，也就是不必鎖定整個表。
資料庫引擎InnoDB與MyISAM有何詳細的差異呢? 我們整理如下 :
(1) Storage engine type
InnoDB是一個transactional storage engine，而MyISAM屬於non-transactional storage engine類別。如果您的數據操作涉及到一個交易，如果該交易未完成，需要自動觸發rollback，就需要使用InnoDB。因為MyISAM不支持交易，在MyISAM中，您將需要手動rollback。
transactional是什麼? 例如我們進行轉帳時，必須先確定轉出帳戶內有足夠的錢，然後轉到對方的帳戶內，如果金額已經從轉出帳戶扣掉了，卻發現對方帳戶禁止轉入，這時整個交易就必須rollback，不然轉帳不成功，你的錢已經被扣掉就不正確了。
使用InnoDB時，可以讓這樣的交易自動觸發rollback，但是MyISAM就必須手動rollback。
範例 https://www.mysql.tw/2018/05/mysqlrollbackcommit.html
(2) Transactions
InnoDB是用於在線交易處理的存儲引擎，它將實際資料存儲在稱為InnoDB緩衝池的單獨區域中，該區域允許MySQL數據庫有效快速地定位和訪問資料。InnoDB引擎是MySQL資料庫穩定性和性能的關鍵。相比之下，MyISAM引擎是一個較老且不太流行的存儲引擎，適合用於存儲表格。
也就是說InnoDB的資料不是直接寫到資料庫，而是先在記憶體的緩衝池，因此他的存取速度會比MyISAM快。
(3) ACID Properties
原子性(Atomicity)、一致性(consistency)、隔離性(isolation)和持久性(durability)是眾所周知的ACID屬性。在發生錯誤或系統故障的情況下，遵守這些屬性可以保證交易的完成。而InnoDB提供了完全的遵循，但MyISAM則沒有這些保證。
原子性(Atomicity)指的是一個交易中的所有操作要麼全部完成，要麼全部不完成，不會出現中途中斷的情況。如果一個交易中的任何操作失敗，所有的操作都必須回滾（rollback）到交易開始之前的狀態，以確保資料的一致性，原子性可以保證資料庫的完整性和一致性。
隔離性(Isolation)指的是同時運行的多個交易之間應當相互隔離，不應互相干擾，每個交易應當感覺到它是在獨立運行。具體來說，當多個交易在同一時間對資料進行讀寫時，隔離性保證每個交易看到的資料是一致的，而不會受到其他交易的影響。隔離性可以避免資料庫中的並發問題，例如Dirty Read、Non-repeatable Read和Phantom Read等問題。
Dirty Read : 讀取了應該已經被更新但是尚未commit的資料 (uncommitted data is read)。
Non-repeatable Read : 讀取同一個資料兩次卻讀到不同的資料 (old and modified data is read)。
Phantom Read : 跟Non-repeatable Read很類似，Phantom Read是因為add/delete。
參考資料
https://jennyttt.medium.com/dirty-read-non-repeatable-read-and-phantom-read-bd75dd69d03a
持久性(Durability)指的是一旦交易被提交，其所做的修改就應該永久保存在資料庫中，即使在故障（如電源故障，操作系統崩潰或資料庫崩潰）發生的情況下。資料庫管理系統應該能夠在恢復後將資料庫恢復到交易提交后的狀態。持久性保證了資料庫的可靠性和持久性，即使在不可預測的情況下，如軟硬體故障，也能夠保證資料的安全性。
一致性(Consistency)指的是在交易開始和完成時，資料庫中的資料必須滿足所有定義的規範和限制。如果交易違反了任何一個限制，則該交易應該被回滾（rollback），以確保資料庫的一致性。
(4) Performance comparison
InnoDB支持交易性屬性，它提供了更高速的代碼編寫與回滾和提交。在處理大量資料時，InnoDB的性能優於MyISAM。MyISAM雖然讀取速度更快，但不支持交易性屬性，並且在處理大量資料時與InnoDB相比表現得不太理想。
就讀取速度來說，因為MyISAM不支援交易的rollback以及不支援外鍵約束，所以讀取速度會比InnoDB好。但是整體來說，資料庫的處理不是只有讀取，全部都考量的話，InnoDB在效能上還是比較好。
(5) Foreign key support
一個表的外鍵是指一組屬性，它引用另一個表的主鍵。一個表可以有多個外鍵，每個外鍵可以有不同的父表。使用MyISAM，您無法添加外鍵約束，而InnoDB完全支持此功能。
範例 https://www.mysql.tw/2017/06/innodb-foreign-key.html
(6) Table-level locking vs row-level locking
InnoDB提供靈活的行級鎖 (row-level locking)，而MyISAM只能進行表級鎖定 (Table-level locking)。
範例 https://www.mysql.tw/2018/06/mysql-lock-table-lockrow-lock.html
(7) Caching and indexing
在InnoDB中，有一個大的緩衝池，可以用來存儲資料和索引。對於MyISAM，有一個用於索引的關鍵字緩衝區。同時，主要的緩存機制是緩存關鍵字，它使用MYI文件來緩存頁面。
(8) Data Recovery
如果MyISAM表格有問題，它只會影響到該表格。如果其他表格或數據庫有任何問題，它們不會影響其他表格或數據庫的操作。因此MyISAM可以推薦用於具有多個站點的服務器，建議使用InnoDB存儲引擎進行頻繁的表操作，它具有更好的性能，還可以大幅減少服務器的內存使用。但仍值得注意的是，由於InnoDB表格的問題可能會導致其他InnoDB表格的資料丟失，因此重要的是確保啟用了自動備份。
結論 :
總結上面的差異，InnoDB使用緩衝池來存儲資料和索引，支援交易的commit與rollback，因此資料的正確性及效率會更好，且具有ACID的特性。另外在Foreign Key的約束上，以及Row-level Lock的支援也讓InnoDB優於MyISAM。最後，InnoDB比較劣勢的是會因為一個表格出問題而引起其他表格的資料丟失，這是一個明顯的缺點，但是可以用自動備份機制來彌補。
如果應用場景是讀取密集型的應用，可以考慮使用MyISAM存儲引擎；如果應用場景需要支持高效率及資料完整性，則推薦使用InnoDB存儲引擎。
0 留言