MySQL提升筆記（2）：存儲引擎盤點
在前面我們了解了server層調用存儲引擎層介面來完成sql的執行，使用存儲引擎的好處是：每個存儲引擎都有各自的特點，能夠根據具體的應用建立不同存儲引擎表。
需要注意的是，存儲引擎是基於表的，而不是資料庫。
MySQL 5.7 支援的存儲引擎有 InnoDB、MyISAM、Memory、Merge、Archive、Federated、CSV、BLACKHOLE 等。
其中最常用的3種，InnoDB、MyISAM、Memory，MySQL5.5.8以後，默認存儲引擎為InnoDB。
1、常用存儲引擎
1.1、InnoDB存儲引擎
InnoDB是MySQL的默認事務型引擎，也是最重要、使用最廣泛的存儲引擎。特點是行鎖設計，支援外鍵，5.6之後支援全文索引。
InnoDB的數據存儲在一個邏輯表空間（tablespace）中，表空間是由InnoDB管理的一個黑盒，由一系列的數據文件組成。在MySQL 4.1以後的版本中，InnoDB可以將每個表的數據和索引存放在單獨的文件中。InnoDB也可以使用裸設備作為表空間的存儲介質。
InnoDB採用MVCC來支援高並發，並且實現了四個標準的隔離級別。其默認級別是REPEATABLE READ（可重複讀），並且通過間隙鎖（next-key locking）策略防止幻讀的出現。
除此之外，InnoDB存儲引擎還提供了插入緩衝（insert buffer）、二次寫（double write）、自適應哈希索引（adaptive hash index）、預讀（read ahead）等高性能和高可用的功能。
InnoDB表是基於聚簇索引建立的，InnoDB的索引結構和MySQL的其他存儲引擎有很大的不同，聚簇索引對主鍵查詢有很高的性能。
InnoDB內部做了很多優化，包括從磁碟讀取數據時採用的可預測性預讀，能夠自動在記憶體中創建hash索引以加速讀操作的自適應哈希索引（adaptive hash index），以及能夠加速插入操作的插入緩衝區（insert buffer）等。
1.2、MyISAM存儲引擎
MyISAM存儲引擎不支援事務、只支援表鎖、支援全文索引。在MySQL5.5之前是MySQl默認的存儲引擎。
MyISAM對整張表加鎖，而不是針對行。讀取時會對需要讀到的所有表加共享鎖，寫入時則對錶加排他鎖。但是在表有讀取查詢的同時，也可以往表中插入新的記錄（這被稱為並發插入，CONCURRENT INSERT）。
MyISAM存儲引擎表是由MYD和MYI組成，MYD用來存放數據文件，MYI用來存放索引文件。
MyISAM引擎設計簡單，數據以緊密格式存儲，所以在某些場景下的性能很好。MyISAM有一些伺服器級別的性能擴展限制，比如對索引鍵緩衝區（key cache）的Mutex鎖，MariaDB基於段（segment）的索引鍵緩衝區機制來避免該問題。但MyISAM最典型的性能問題還是表鎖的問題，很容易導致所有的查詢都長期處於「Locked」狀態。
1.3、Memory存儲引擎
Memory可以理解為臨時表——當然二者不是一個東西。
Memory存儲引擎將表中的數據存放在記憶體中，不需要進行磁碟I/O，速度非常快。但是如果資料庫重啟或者崩潰，Memory表的結構會保留，但表裡的數據都會丟失。
Memroy表在很多場景可以發揮好的作用：
- 用於查找（lookup）或者映射（mapping）表，例如將郵編和地名映射的表。
- 用於快取周期性聚合數據（periodically aggregated data）的結果。
- 用於保存數據分析中產生的中間數據。
Memory表默認使用Hash索引，因此查找操作非常快。雖然Memory表的速度非常快，但還是無法取代傳統的基於磁碟的表。Memroy表是表級鎖，因此並發寫入的性能較低。它不支援BLOB或TEXT類型的列，並且每行的長度是固定的，所以即使指定了VARCHAR列，實際存儲時也會轉換成CHAR，這可能導致部分記憶體的浪費（其中一些限制在Percona版本已經解決）。
如果MySQL在執行查詢的過程中需要使用臨時表來保存中間結果，內部使用的臨時表就是Memory表。
如果中間結果太大超出了Memory表的限制，或者含有BLOB或TEXT欄位，則臨時表會轉換成MyISAM表。
2、存儲引擎對比
不同的存儲引擎都有各自的特點，以適應不同的需求，如表所示。為了做出選擇，首先要考慮每一個存儲引擎提供了哪些不同的功能。
| 功能 | MylSAM | MEMORY | InnoDB | Archive |
|---|---|---|---|---|
| 存儲限制 | 256TB | RAM | 64TB | None |
| 支援事務 | No | No | Yes | No |
| 支援全文索引 | Yes | No | Yes | No |
| 支援樹索引 | Yes | Yes | Yes | No |
| 支援哈希索引 | No | Yes | Yes | No |
| 支援數據快取 | No | N/A | Yes | No |
| 支援外鍵 | No | No | Yes | No |
InnoDB支援的哈希索引是自適應的，InnoDB會根據表的使用情況自動為表生成哈希索引，不能人為干預是否在一張表中生成哈希索引。
MySQL 5.6開始InnoDB支援全文索引。
以根據以下的原則來選擇 MySQL 存儲引擎：
- 如果要提供提交、回滾和恢復的事務安全（ACID 兼容）能力，並要求實現並發控制，InnoDB 是一個很好的選擇。
- 如果數據表主要用來插入和查詢記錄，則 MyISAM 引擎提供較高的處理效率。
- 如果只是臨時存放數據，數據量不大，並且不需要較高的數據安全性，可以選擇將數據保存在記憶體的 MEMORY 引擎中，MySQL 中使用該引擎作為臨時表，存放查詢的中間結果。
- 如果只有 INSERT 和 SELECT 操作，可以選擇Archive 引擎，Archive 存儲引擎支援高並發的插入操作，但是本身並不是事務安全的。Archive 存儲引擎非常適合存儲歸檔數據，如記錄日誌資訊可以使用 Archive 引擎。
提示：使用哪一種引擎要根據需要靈活選擇，因為存儲引擎是基於表的，所以一個資料庫中多個表可以使用不同的引擎以滿足各種性能和實際需求。使用合適的存儲引擎將會提高整個資料庫的性能。
一般MySQL的深入都是圍繞InnoDB展開。
參考：
【1】：《高性能MySQL》
【2】：極客時間 《MySQL實戰45講》
【3】：《MySQL技術內幕 InnoDB存儲引擎》
【5】：MySQL – 常用存儲引擎區別總結（2020最新版）
