一、常見儲存引擎
1.1 InnoDB
InnoDB 是 MySQL 5.5 之後預設的儲存引擎，它具有高可靠、高效能的特點，主要具備以下優勢：
- DML 操作完全遵循 ACID 模型，支援事務，支援崩潰恢復，能夠極大地保護使用者的資料安全；
- 支援多版本併發控制，它會儲存資料的舊版本資訊，從而可以支援併發和事務的回滾；
- 支援行級鎖，支援類似 Oracle 的一致性讀的特性，從而可以承受高併發地訪問；
- InnoDB 組織資料時預設按照主鍵進行聚簇，從而可以提高主鍵查詢的效率。對於頻繁訪問的資料，InnoDB 還會為其建立雜湊索引，從而提高等值查詢的效率，這也稱為自適應雜湊索引；
- InnoDB 基於磁碟進行儲存，所有儲存記錄按 頁 的方式進行管理。為彌補 CPU 速度與磁碟速度之間的鴻溝，InnoDB 引用快取池 (Buffer Pool) 來提高資料的整體效能。查詢時，會將目標頁讀入快取中；修改時，會先修改緩衝池中的頁，然後再遵循 CheckPoint 機制將頁刷回磁碟。所有快取頁通過最近最少使用原則 ( LRU ) 來進行定期清理。
- InnoDB 支援兩次寫 (DoubleWrite) ，從而可以保證資料的安全，提高系統的可靠性。
一個 InnoDB 引擎完整的記憶體結構和磁碟結構如下圖所示：
1.2 MyISAM
MyISAM 是 MySQL 5.5 之前預設的儲存引擎。建立 MyISAM 表時會建立兩個同名的檔案：
- 副檔名為
.MYD（MYData）：用於儲存表資料； - 副檔名為
.MYI（MYIndex）： 用於儲存表的索引資訊。
在 MySQL 8.0 之後，只會建立上述兩個同名檔案，因為 8.0 後表結構的定義儲存在 MySQL 資料字典中，但在 MySQL 8.0 之前，還會存在一個副檔名為 .frm 的檔案，用於儲存表結構資訊。MyISAM 與 InnoDB 主要的區別其只支援表級鎖，不支援行級鎖，不支援事務，不支援自動崩潰恢復，但可以使用內建的 mysqlcheck 和 myisamchk 工具來進行檢查和修復。
1.3 MEMORY
MEMORY 儲存引擎（又稱為 HEAP 儲存引擎）通常用於將表中的資料儲存在記憶體中，它具有以下特徵：
- MEMORY 表的表定義資訊儲存在 MySQL 資料字典中，而實際的資料則儲存在記憶體空間中，並以塊為單位進行劃分；因此當伺服器重啟後，表本身並不會被刪除，只是表中的所有資料都會丟失。
- MEMORY 儲存引擎支援 HASH 索引和 BTREE 索引，預設採用 HASH 索引。
- MEMORY 表使用固定長度的行儲存格式，即便是 VARCHAR 型別也會使用固定長度進行儲存。
- MEMORY 支援 AUTO_INCREMENT 列，但不支援 BLOB 列或 TEXT 列。
- MEMORY 表和 MySQL 內部臨時表的區別在於：兩者預設都採用記憶體進行儲存，但 MEMORY 表不受儲存轉換的影響，而內部臨時表則會在達到閾值時自動轉換為磁碟儲存。
基於以上特性，MEMORY 表主要適合於儲存臨時資料 ，如會話狀態、實時位置等資訊。
1.4 CSV
CSV 儲存引擎使用逗號分隔值的格式將資料儲存在文字檔案中。建立 CSV 表時會同時建立兩個同名的檔案：
- 一個副檔名為
csv，負責儲存表的資料，其檔案格式為純文字，可以通過電子表格應用程式 (如 Microsoft Excel ) 進行修改，對應的修改操作也會直接反應在資料庫表中。 - 另一個副檔名為
CSM，負責儲存表的狀態和表中存在的行數。
1.5 ARCHIVE
ARCHIVE 儲存引擎預設採用 zlib 無損資料壓縮演算法進行資料壓縮，能夠利用極小的空間儲存大量的資料。建立 ARCHIVE 表時，儲存引擎會建立與表同名的 ARZ 檔案，用於儲存資料。它還具有以下特點：
- ARCHIVE 引擎支援 INSERT，REPLACE 和 SELECT，但不支援 DELETE 或 UPDATE。
- ARCHIVE 引擎支援 AUTO_INCREMENT 屬性，並支援在其對應的列上建立索引，如果嘗試在不具有 AUTO_INCREMENT 屬性的列上建立索引，則會丟擲異常。
- ARCHIVE 引擎不支援分割槽操作。
1.6 MEGRE
MERGE 儲存引擎，也稱為 MRG_MyISAM 引擎，是一組相同 MyISAM 表的集合。 ”相同” 表示所有表必須具有相同的列資料型別和索引資訊。可以通過 UNION = (list-of-tables) 選項來建立 MERGE 表，如下：
mysql> CREATE TABLE t1 ( a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, message CHAR(20)) ENGINE=MyISAM;
mysql> CREATE TABLE t2 ( a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, message CHAR(20)) ENGINE=MyISAM;
mysql> INSERT INTO t1 (message) VALUES ('Testing'),('table'),('t1');
mysql> INSERT INTO t2 (message) VALUES ('Testing'),('table'),('t2');
mysql> CREATE TABLE total (a INT NOT NULL AUTO_INCREMENT,message CHAR(20), INDEX(a))
ENGINE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST;
建立表時可以通過 INSERT_METHOD 選項來控制 MERGE 表的插入：使用 FIRST 或 LAST 分別表示在第一個或最後一個基礎表中進行插入；如果未指定 INSERT_METHOD 或者設定值為 NO ，則表示不允許在 MERGE 表上執行插入操作。MERGE 表支援 SELECT，DELETE，UPDATE 和 DELETE 語句，示例如下：
mysql> SELECT * FROM total;
+---+---------+
| a | message |
+---+---------+
| 1 | Testing |
| 2 | table |
| 3 | t1 |
| 1 | Testing |
| 2 | table |
| 3 | t2 |
+---+---------+
二、索引
2.1 B+ tree 資料結構
如果沒有特殊說明，通常大多數資料庫採用的索引都是 B+ tree 索引，它是基於 B+ tree 這種資料結構構建的。為什麼採用 B+ tree 而不是平衡二叉樹 (AVL) 或紅黑樹等資料結構？這裡假設索引為 1-16 的自增資料，各類資料結構的表現如下：
平衡二叉樹資料結構：
紅黑樹資料結構：
Btree 資料結構：
B+ Tree 資料結構
以上圖片均通過資料結構視覺化網站 Data Structure Visualizations 自動生成，感興趣的小夥伴也可自行嘗試。
從上面的圖示中我們可以看出 B+ Tree 樹具有以下優點：
- B+ Tree 樹的所有非葉子節點 (如 003，007)，都會在葉子節點冗餘一份，所有葉子節點按照連結串列的方式進行組織，這樣帶來的好處是在範圍查詢中，只需要通過遍歷葉子節點就可以獲取到所有的索引資訊。
- B+ Tree 的所有非葉子節點都可以儲存多個資料值，儲存量取決於節點的大小，在 MySQL 中每個節點的大小為 16K ，因此其具備更大的出度，即在相同的資料量下，其樹的高度更低。
- 所有非葉子節點都只儲存索引值，不儲存實際的資料，只有葉子節點才會儲存指標資訊或資料資訊。按照每個節點為 16K 的大小計算，對於千萬級別的資料，其樹的高度通常都在 3~6 左右 (取決於索引值的位元組數)，因此其查詢效能非常優異。
- 葉子節點儲存的資料取決於不同資料庫的實現，對於 MySQL 來說，取決於使用的儲存引擎和是否是主鍵索引。
2.2 B+ tree 索引
對於 InnoDB ，因為主鍵索引是聚集索引，所以其葉子節點儲存的就是實際的資料。而非主鍵索引儲存的則是主鍵的值 ：
對於 MyISAM，因為主鍵索引是非聚集索引，所以其葉子節點儲存的只是指向資料位置的指標：
綜上所述，B+ tree 結構普遍適用於範圍查詢，優化排序和分組等操作。B+ tree 是基於字典序進行構建的，因此其適用於以下查詢：
- 全值匹配：以索引為條件進行精確查詢。如
emp_no欄位為索引，查詢條件為emp_no = 10008。 - 字首匹配：以聯合索引的字首為查詢條件。如
emp_no和dept_no為聯合索引，查詢條件為emp_no = 10008。 - 列字首匹配：匹配索引列的值的開頭部分。如
dept_no為索引，查詢條件為dept_no like "d1%"。字首匹配和列字首匹配都是索引字首性的體現，在某些時候也稱為字首索引。 - 匹配範圍值：按照索引列匹配一定範圍內的值。如
emp_no欄位為索引，查詢條件為emp_no > 10008。 - 只訪問索引的查詢：如
emp_no欄位為索引，查詢語句為select emp_no from employees，此時 emp_no 索引被稱為本次查詢的覆蓋索引，即只需要從索引上就可以獲取全部的查詢資訊，而不必訪問實際表中的資料。 - 精確匹配某一列並範圍匹配某一列：如
emp_no和dept_no為聯合索引，查詢條件為dept_no = "d004" and emp_no < 10020，這種情況下索引順序可以是（emp_no, dept_no），也可以是（dept_no, emp_no），使用 EXPLAIN 來分析的話，其 TYPE 型別都是 range（使用索引進行範圍掃描），但（dept_no, emp_no）效能更好。
2.3 雜湊索引
使用雜湊索引時，儲存引擎會對索引列的值進行雜湊運算，並將計算出的雜湊值和指向該行資料的指標儲存在索引中，因此它更適用於等值查詢，而不是範圍查詢，同樣也不能用於優化排序和分組等操作。在建立雜湊索引時，需要選取選擇性比較高的列，即列上的資料不容易重複 (如身份證號)，這樣可以儘量避免雜湊衝突。因為雜湊索引並不需要儲存索引列的資料，所以其結構比較緊湊，對應的查詢速度也比較快。
InnoDB 引擎有一個名為 “自適應雜湊索引 (adaptive hash index)” 的功能，當某些索引值被頻繁使用時，它會在記憶體中基於 B+ tree 索引再建立一個雜湊索引，從而讓 B-Tree 索引具備雜湊索引快速查詢的優點。
2.4 索引的優點
- 索引極大減少了伺服器需要掃描的資料量；
- 索引可以幫助伺服器避免排序和臨時表；
- 索引可以將隨機 IO 轉換為順序 IO。
2.5 使用策略
- 在查詢時，應該避免在索引列上使用函式或者表示式。
- 對於多列索引，應該按照使用頻率由高到低的順序建立聯合索引。
- 儘量避免建立冗餘的索引。如存在索引 (A，B)，接著又建立了索引 A，因為索引 A 是索引 (A，B) 的字首索引，從而出現冗餘。
- 建立索引時，應該考慮查詢時候的排序和分組的需求。只有當索引列的順序和 ORDER BY 字句的順序完全一致，並且遵循同樣的升序或降序規則時候，MySQL 才會使用索引來對結果做排序。
三、鎖
3.1 共享鎖與排它鎖
InnoDB 儲存引擎支援以下兩種標準的行級鎖：
- 共享鎖 (S Lock，又稱讀鎖) ：允許加鎖事務讀取資料；
- 排它鎖 (X Lock，又稱寫鎖) ：允許加鎖事務刪除或者修改資料。
排它鎖和共享鎖的相容情況如下：
| X | X | |
|---|---|---|
| X | 不相容 | 不相容 |
| S | 不相容 | 相容 |
3.2 意向共享鎖與意向排它鎖
為了說明意向鎖的作用，這裡先引入一個案例：假設事務 A 利用 S 鎖鎖住了表中的某一行，讓其只能讀不能寫。之後事務 B 嘗試申請整個表的寫鎖，如果事務 B 申請成功，那麼理論上它就應該能修改表中的任意一行，這與事務 A 持有的行鎖是衝突的。想要解決這個問題，資料庫必須知道表中某一行已經被鎖定，從而在事務 B 嘗試申請整個表的寫鎖時阻塞它。想要知道表中某一行被鎖定，可以對錶的每一行進行遍歷，這種方式可行但是效能比較差，所以 InnoDB 引入了意向鎖。
- 意向共享鎖 (IS Lock) ：當前表中某行或者某幾行資料存在共享鎖；
- 意向排它鎖 (LX Lock) ：當前表中某行或者某幾行資料存在排它鎖。
按照意向鎖的規則，當上面的事務 A 給表中的某一行加 S 鎖時，會同時給表加上 IS 鎖，之後事務 B 嘗試獲取表的 X 鎖時，由於 X 鎖與 IS 鎖並不相容，所以事務 B 會被阻塞。
| X | IX | S | IS | |
|---|---|---|---|---|
| X | 不相容 | 不相容 | 不相容 | 不相容 |
| IX | 不相容 | 相容 | 不相容 | 相容 |
| S | 不相容 | 不相容 | 相容 | 相容 |
| IS | 不相容 | 相容 | 相容 | 相容 |
3.3 一致性讀
1. 一致性非鎖定讀
一致非鎖定讀 (consistent nonlocking read) 是指在 InnoDB 儲存引擎下，如果將要讀取的行正在執行 DELETE 或 UPDATE 操作，此時不必去等待行上鎖的釋放，而是去讀取 undo 日誌上該行的快照資料，具體如下：
- 在 READ COMMITTED 事務隔離級別下，讀取被鎖定行的最新一份快照資料；
- 在 REPEATABLE READ 事務隔離級別下，讀取事務開始時所處版本的資料。
基於多版本併發控制和一致性非鎖定讀，可以避免獲取鎖的等待，從而提高併發訪問下的效能。
2. 一致性鎖定讀
一致性鎖定讀則允許使用者按照自己的需求在進行 SELECT 操作時手動加鎖，通常有以下兩種方式：
- SELECT ... FOR SHARE：在讀取行上加 S 鎖；
- SELECT ... FOR UPDATE：在讀取行上加 X 鎖。
3.4 鎖的演算法
InnoDB 儲存引擎支援以下三種鎖的演算法：
Record Lock：行鎖，用於鎖定單個行記錄。示例如下：
-- 利用行鎖可以防止其他事務更新或刪除該行
SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE;
Gap Lock：間隙鎖，鎖定一個範圍，但不包括記錄本身，主要用於解決幻讀問題，示例如下：
-- 利用間隙鎖可以阻止其他事務將值15插入列 t.c1
SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE;
Next-Key Lock：等價於 行鎖+間隙鎖，既鎖定範圍，也鎖定記錄本身。可以用於解決幻讀中的 ”當前讀“ 的問題。
四、事務
4.1 ACID 定義
InnoDB 儲存引擎完全支援 ACID 模型：
1. 原子性（Atomicity）
事務是不可分割的最小工作單元，事務的所有操作要麼全部提交成功，要麼全部失敗回滾，不存在部分成功的情況。
2. 一致性（Consistency）
資料庫在事務執行前後都保持一致性狀態，資料庫的完整性沒有被破壞。
3. 隔離性（Isolation）
允許多個併發事務同時對資料進行操作，但一個事務所做的修改在最終提交以前，對其它事務是不可見的。
4. 永續性（Durability）
一旦事務提交，則其所做的修改將會永遠儲存到資料庫中。即使當機等故障，也不會丟失。
4.2 事務的實現
資料庫隔離性由上一部分介紹的鎖來實現，而原子性、一致性、永續性都由 undo log 和 redo log 來實現。
- undo log：儲存在 undo 表空間或全域性臨時表空間的 undo 日誌段 (segment) 上，用於記錄資料修改前的狀態，主要用於幫助事務回滾以及實現 MVCC 功能 (如一致性非鎖定讀)。
- redo log：負責記錄資料修改後的值，主要用於保證事務的持久化。
4.3 併發問題
在併發環境下，資料的更改通常會產生下面四種問題：
1.丟失更新
一個事務的更新操作被另外一個事務的更新操作鎖覆蓋，從而導致資料不一致：
2. 髒讀
在不同的事務下，一個事務讀取到其他事務未提交的資料：
3. 不可重複讀
在同一個事務的兩次讀取之間，由於其他事務對資料進行了修改，導致對同一條資料兩次讀到的結果不一致：
4.幻讀
在同一個事務的兩次讀取之間，由於其他事務對資料進行了修改，導致第二次讀取到第一次不存在資料，或第一次原本存在的資料，第二次卻讀取不到，就好像之前的讀取是 “幻覺” 一樣：
4.4 隔離級別
想要解決以上問題，可以通過設定隔離級別來實現：InnoDB 支援以下四個等級的隔離級別，預設隔離級別為可重複讀：
- 讀未提交：在此級別下，一個事務中的修改，即便沒有提交，對其他事務也是可見的。
- 讀已提交：在此級別下，一個事務中的修改只有已經提交的情況下，對其他事務才是可見的。
- 可重複讀：保證在同一個事務中多次讀取同樣資料的結果是一樣的。
- 序列化：所有事務強制序列執行，由於已經不存在並行，所以上述所有併發問題都不會出現。
在每個級別下，併發問題是否可能出現的情況如下：
| 隔離級別 | 髒讀 | 不可重複讀 | 幻讀 |
|---|---|---|---|
| 讀未提交（READ UNCOMMITTED） | 可能出現 | 可能 | 可能 |
| 讀已提交（READ COMMITTED） | 不可能出現 | 可能 | 可能 |
| 可重複讀（REPEATABLE READ） | 不可能 | 不可能 | 可能 |
| 序列化（SERIALIZABLE） | 不可能 | 不可能 | 不可能 |
就資料庫層面而言，當前任何隔離級別下都不會發生丟失更新的問題，以 InnoDB 儲存引擎為例，如果你想要更改表中某行資料，該行資料上必然會加上 X 鎖，而對應的表上則會加上 IX 鎖，其他任何事務都必須等待獲取該鎖才能進行修改操作。
五、資料庫設計正規化
資料庫設計當中常用的三正規化如下：
第一正規化：屬性不可分
要求表中的每一列都是不可再細分的原子項。這是最低的正規化要求，通常都能夠被滿足。
第二正規化：屬性完全依賴於主鍵
要求非主鍵列必須完全依賴於主鍵列，而不能存在部分依賴。示例如下：
| mechanism_id （組織機構程式碼） | employee_id （僱員編號） | ename （僱員名稱） | mname （機構名稱） |
|---|---|---|---|
| 28193182 | 10001 | heibaiying | XXXX公司 |
以上是一張全市在職人員統計表，主鍵為：機構編碼 + 僱員編號。表中的僱員名稱完全依賴於此聯合主鍵，但機構名稱卻只依賴於機構編碼，這就是部分依賴，因此違背了第二正規化。此時常用的解決方式是建立一張組織機構與組織名稱的字典表。
第三正規化：避免傳遞依賴
非主鍵列不能依賴於其他非主鍵列，如果其他非主鍵列又依賴於主鍵列，此時就出現了傳遞依賴。示例如下：
| employee_id （僱員編號） | ename （僱員名稱） | dept_no (部門編號) | dname（部門名稱） |
|---|---|---|---|
| 10001 | heibaiying | 06 | 開發部 |
以上是一張僱員表，僱員名稱和所屬的部門編號都依賴於主鍵 employee_id ，但部門名稱卻依賴於部門編號，此時就出現了非主鍵列依賴於其他非主鍵列，這就違背的第三正規化。此時常用的解決方式是建立一張部門表用於維護部門相關的資訊。
反正規化設計
從上面的例子中我們也可以看出，想要完全遵循三正規化設計，可能需要額外增加很多表來進行維護。所以在日常開發中，基於其他因素的綜合考量，可能並不會完全遵循正規化設計，甚至可能違反正規化設計，這就是反正規化設計。
參考資料
- 官方文件：The InnoDB Storage Engine，Optimization and Indexes，InnoDB Locking and Transaction Model
- 姜承堯 . MySQL技術內幕:InnoDB儲存引擎(第2版) . MySQL技術內幕 . 2013-05
- 施瓦茨 (Baron Schwartz) / 扎伊採夫 (Peter Zaitsev) / 特卡琴科 (Vadim Tkachenko) . 高效能mysql(第3版) . 電子工業出版社 . 2013-05-01
- InnoDB 資料頁解析
- MySQL索引背後的資料結構及演算法原理
- MYSQL-B+TREE索引原理
- Innodb中的事務隔離級別和鎖的關係
更多文章，歡迎訪問 [全棧工程師手冊] ，GitHub 地址：https://github.com/heibaiying/Full-Stack-Notes
