MySQL 核心三劍客 —— 索引、鎖、事務

一、常見儲存引擎

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 ，但部門名稱卻依賴於部門編號，此時就出現了非主鍵列依賴於其他非主鍵列，這就違背的第三正規化。此時常用的解決方式是建立一張部門表用於維護部門相關的資訊。

反正規化設計

從上面的例子中我們也可以看出，想要完全遵循三正規化設計，可能需要額外增加很多表來進行維護。所以在日常開發中，基於其他因素的綜合考量，可能並不會完全遵循正規化設計，甚至可能違反正規化設計，這就是反正規化設計。

參考資料

1. 官方文件：The InnoDB Storage Engine，Optimization and Indexes，InnoDB Locking and Transaction Model
2. 姜承堯 . MySQL技術內幕:InnoDB儲存引擎(第2版) . MySQL技術內幕 . 2013-05
3. 施瓦茨 (Baron Schwartz) / 扎伊採夫 (Peter Zaitsev) / 特卡琴科 (Vadim Tkachenko) . 高效能mysql(第3版) . 電子工業出版社 . 2013-05-01
4. InnoDB 資料頁解析
5. MySQL索引背後的資料結構及演算法原理
6. MYSQL-B+TREE索引原理
7. Innodb中的事務隔離級別和鎖的關係

更多文章，歡迎訪問我的 GitHub 倉庫：Full-Stack-Notes ！