本文轉自網際網路

本系列文章將整理到我在GitHub上的《Java面試指南》倉庫，更多精彩內容請到我的倉庫裡檢視
> https://github.com/h2pl/Java-Tutorial

喜歡的話麻煩點下Star哈

文章首發於我的個人部落格：
> www.how2playlife.com

本文是微信公眾號【Java技術江湖】的《重新學習MySQL資料庫》其中一篇，本文部分內容來源於網路，為了把本文主題講得清晰透徹，也整合了很多我認為不錯的技術部落格內容，引用其中了一些比較好的部落格文章，如有侵權，請聯絡作者。

該系列博文會告訴你如何從入門到進階，從sql基本的使用方法，從MySQL執行引擎再到索引、事務等知識，一步步地學習MySQL相關技術的實現原理，更好地瞭解如何基於這些知識來最佳化sql，減少SQL執行時間，透過執行計劃對SQL效能進行分析，再到MySQL的主從複製、主備部署等內容，以便讓你更完整地瞭解整個MySQL方面的技術體系，形成自己的知識框架。

如果對本系列文章有什麼建議，或者是有什麼疑問的話，也可以關注公眾號【Java技術江湖】聯絡作者，歡迎你參與本系列博文的創作和修訂。

## 前言

作為一名開發人員，在日常的工作中會難以避免地接觸到資料庫，無論是基於檔案的 sqlite 還是工程上使用非常廣泛的 MySQL、PostgreSQL，但是一直以來也沒有對資料庫有一個非常清晰並且成體系的認知，所以最近兩個月的時間看了幾本資料庫相關的書籍並且閱讀了 MySQL 的官方文件，希望對各位瞭解資料庫的、不瞭解資料庫的有所幫助。

![mysql](https://raw.githubusercontent.com/Draveness/Analyze/master/contents/Database/images/mysql/mysql.png)

本文中對於資料庫的介紹以及研究都是在 MySQL 上進行的，如果涉及到了其他資料庫的內容或者實現會在文中單獨指出。

## [](https://draveness.me/mysql-innodb#%E6%95%B0%E6%8D%AE%E5%BA%93%E7%9A%84%E5%AE%9A%E4%B9%89)資料庫的定義

很多開發者在最開始時其實都對資料庫有一個比較模糊的認識，覺得資料庫就是一堆資料的集合，但是實際卻比這複雜的多，資料庫領域中有兩個詞非常容易混淆，也就是_資料庫_和_例項_：

*   資料庫：物理操作檔案系統或其他形式檔案型別的集合；
*   例項：MySQL 資料庫由後臺執行緒以及一個共享記憶體區組成；

> 對於資料庫和例項的定義都來自於 [MySQL 技術內幕：InnoDB 儲存引擎](https://book.douban.com/subject/24708143/) 一書，想要了解 InnoDB 儲存引擎的讀者可以閱讀這本書籍。

### [](https://draveness.me/mysql-innodb#%E6%95%B0%E6%8D%AE%E5%BA%93%E5%92%8C%E5%AE%9E%E4%BE%8B)資料庫和例項

在 MySQL 中，例項和資料庫往往都是一一對應的，而我們也無法直接運算元據庫，而是要透過資料庫例項來運算元據庫檔案，可以理解為資料庫例項是資料庫為上層提供的一個專門用於操作的介面。

![Database - Instance](https://raw.githubusercontent.com/Draveness/Analyze/master/contents/Database/images/mysql/Database%20-%20Instance.jpg)

在 Unix 上，啟動一個 MySQL 例項往往會產生兩個程式，`mysqld` 就是真正的資料庫服務守護程式，而 `mysqld_safe` 是一個用於檢查和設定 `mysqld` 啟動的控制程式，它負責監控 MySQL 程式的執行，當 `mysqld` 發生錯誤時，`mysqld_safe` 會對其狀態進行檢查並在合適的條件下重啟。

### [](https://draveness.me/mysql-innodb#mysql-%E7%9A%84%E6%9E%B6%E6%9E%84)MySQL 的架構

MySQL 從第一個版本釋出到現在已經有了 20 多年的歷史，在這麼多年的發展和演變中，整個應用的體系結構變得越來越複雜：

![Logical-View-of-MySQL-Architecture](https://raw.githubusercontent.com/Draveness/Analyze/master/contents/Database/images/mysql/Logical-View-of-MySQL-Architecture.jpg)

最上層用於連線、執行緒處理的部分並不是 MySQL 『發明』的，很多服務都有類似的組成部分；第二層中包含了大多數 MySQL 的核心服務，包括了對 SQL 的解析、分析、最佳化和快取等功能，儲存過程、觸發器和檢視都是在這裡實現的；而第三層就是 MySQL 中真正負責資料的儲存和提取的儲存引擎，例如：[InnoDB](https://en.wikipedia.org/wiki/InnoDB)、[MyISAM](https://en.wikipedia.org/wiki/MyISAM) 等，文中對儲存引擎的介紹都是對 InnoDB 實現的分析。

## [](https://draveness.me/mysql-innodb#%E6%95%B0%E6%8D%AE%E7%9A%84%E5%AD%98%E5%82%A8)資料的儲存

在整個資料庫體系結構中，我們可以使用不同的儲存引擎來儲存資料，而絕大多數儲存引擎都以二進位制的形式儲存資料；這一節會介紹 InnoDB 中對資料是如何儲存的。

在 InnoDB 儲存引擎中，所有的資料都被**邏輯地**存放在表空間中，表空間（tablespace）是儲存引擎中最高的儲存邏輯單位，在表空間的下面又包括段（segment）、區（extent）、頁（page）：

![Tablespace-segment-extent-page-row](https://raw.githubusercontent.com/Draveness/Analyze/master/contents/Database/images/mysql/Tablespace-segment-extent-page-row.jpg)

同一個資料庫例項的所有表空間都有相同的頁大小；預設情況下，表空間中的頁大小都為 16KB，當然也可以透過改變 `innodb_page_size` 選項對預設大小進行修改，需要注意的是不同的頁大小最終也會導致區大小的不同：

![Relation Between Page Size - Extent Size](https://raw.githubusercontent.com/Draveness/Analyze/master/contents/Database/images/mysql/Relation%20Between%20Page%20Size%20-%20Extent%20Size.png)

從圖中可以看出，在 InnoDB 儲存引擎中，一個區的大小最小為 1MB，頁的數量最少為 64 個。

### [](https://draveness.me/mysql-innodb#%E5%A6%82%E4%BD%95%E5%AD%98%E5%82%A8%E8%A1%A8)如何儲存表

MySQL 使用 InnoDB 儲存表時，會將**表的定義**和**資料索引**等資訊分開儲存，其中前者儲存在 `.frm` 檔案中，後者儲存在 `.ibd` 檔案中，這一節就會對這兩種不同的檔案分別進行介紹。

![frm-and-ibd-file](https://raw.githubusercontent.com/Draveness/Analyze/master/contents/Database/images/mysql/frm-and-ibd-file.jpg)

#### [](https://draveness.me/mysql-innodb#frm-%E6%96%87%E4%BB%B6).frm 檔案

無論在 MySQL 中選擇了哪個儲存引擎，所有的 MySQL 表都會在硬碟上建立一個 `.frm` 檔案用來描述表的格式或者說定義；`.frm` 檔案的格式在不同的平臺上都是相同的。

```
CREATE TABLE test_frm(
    column1 CHAR(5),
    column2 INTEGER
);

```

當我們使用上面的程式碼建立表時，會在磁碟上的 `datadir` 資料夾中生成一個 `test_frm.frm` 的檔案，這個檔案中就包含了表結構相關的資訊：

![frm-file-hex](https://raw.githubusercontent.com/Draveness/Analyze/master/contents/Database/images/mysql/frm-file-hex.png)

> MySQL 官方文件中的 [11.1 MySQL .frm File Format](https://dev.mysql.com/doc/internals/en/frm-file-format.html) 一文對於 `.frm` 檔案格式中的二進位制的內容有著非常詳細的表述，在這裡就不展開介紹了。

#### [](https://draveness.me/mysql-innodb#ibd-%E6%96%87%E4%BB%B6).ibd 檔案

InnoDB 中用於儲存資料的檔案總共有兩個部分，一是系統表空間檔案，包括 `ibdata1`、`ibdata2` 等檔案，其中儲存了 InnoDB 系統資訊和使用者資料庫表資料和索引，是所有表公用的。

當開啟 `innodb_file_per_table` 選項時，`.ibd` 檔案就是每一個表獨有的表空間，檔案儲存了當前表的資料和相關的索引資料。

### [](https://draveness.me/mysql-innodb#%E5%A6%82%E4%BD%95%E5%AD%98%E5%82%A8%E8%AE%B0%E5%BD%95)如何儲存記錄

與現有的大多數儲存引擎一樣，InnoDB 使用頁作為磁碟管理的最小單位；資料在 InnoDB 儲存引擎中都是按行儲存的，每個 16KB 大小的頁中可以存放 2-200 行的記錄。

當 InnoDB 儲存資料時，它可以使用不同的行格式進行儲存；MySQL 5.7 版本支援以下格式的行儲存方式：

![Antelope-Barracuda-Row-Format](https://raw.githubusercontent.com/Draveness/Analyze/master/contents/Database/images/mysql/Antelope-Barracuda-Row-Format.jpg)

> Antelope 是 InnoDB 最開始支援的檔案格式，它包含兩種行格式 Compact 和 Redundant，它最開始並沒有名字；Antelope 的名字是在新的檔案格式 Barracuda 出現後才起的，Barracuda 的出現引入了兩種新的行格式 Compressed 和 Dynamic；InnoDB 對於檔案格式都會向前相容，而官方文件中也對之後會出現的新檔案格式預先定義好了名字：Cheetah、Dragon、Elk 等等。

兩種行記錄格式 Compact 和 Redundant 在磁碟上按照以下方式儲存：

![COMPACT-And-REDUNDANT-Row-Format](https://raw.githubusercontent.com/Draveness/Analyze/master/contents/Database/images/mysql/COMPACT-And-REDUNDANT-Row-Format.jpg)

Compact 和 Redundant 格式最大的不同就是記錄格式的第一個部分；在 Compact 中，行記錄的第一部分倒序存放了一行資料中列的長度（Length），而 Redundant 中存的是每一列的偏移量（Offset），從總體上上看，Compact 行記錄格式相比 Redundant 格式能夠減少 20% 的儲存空間。

#### [](https://draveness.me/mysql-innodb#%E8%A1%8C%E6%BA%A2%E5%87%BA%E6%95%B0%E6%8D%AE)行溢位資料

當 InnoDB 使用 Compact 或者 Redundant 格式儲存極長的 VARCHAR 或者 BLOB 這類大物件時，我們並不會直接將所有的內容都存放在資料頁節點中，而是將行資料中的前 768 個位元組儲存在資料頁中，後面會透過偏移量指向溢位頁。

![Row-Overflo](https://raw.githubusercontent.com/Draveness/Analyze/master/contents/Database/images/mysql/Row-Overflow.jpg)

但是當我們使用新的行記錄格式 Compressed 或者 Dynamic 時都只會在行記錄中儲存 20 個位元組的指標，實際的資料都會存放在溢位頁面中。

![Row-Overflow-in-Barracuda](https://raw.githubusercontent.com/Draveness/Analyze/master/contents/Database/images/mysql/Row-Overflow-in-Barracuda.jpg)

當然在實際儲存中，可能會對不同長度的 TEXT 和 BLOB 列進行最佳化，不過這就不是本文關注的重點了。

> 想要了解更多與 InnoDB 儲存引擎中記錄的資料格式的相關資訊，可以閱讀 [InnoDB Record Structure](https://dev.mysql.com/doc/internals/en/innodb-record-structure.html)

### [](https://draveness.me/mysql-innodb#%E6%95%B0%E6%8D%AE%E9%A1%B5%E7%BB%93%E6%9E%84)資料頁結構

頁是 InnoDB 儲存引擎管理資料的最小磁碟單位，而 B-Tree 節點就是實際存放表中資料的頁面，我們在這裡將要介紹頁是如何組織和儲存記錄的；首先，一個 InnoDB 頁有以下七個部分：

![InnoDB-B-Tree-Node](https://raw.githubusercontent.com/Draveness/Analyze/master/contents/Database/images/mysql/InnoDB-B-Tree-Node.jpg)

每一個頁中包含了兩對 header/trailer：內部的 Page Header/Page Directory 關心的是頁的狀態資訊，而 Fil Header/Fil Trailer 關心的是記錄頁的頭資訊。

在頁的頭部和尾部之間就是使用者記錄和空閒空間了，每一個資料頁中都包含 Infimum 和 Supremum 這兩個**虛擬**的記錄（可以理解為佔位符），Infimum 記錄是比該頁中任何主鍵值都要小的值，Supremum 是該頁中的最大值：

![Infimum-Rows-Supremum](https://raw.githubusercontent.com/Draveness/Analyze/master/contents/Database/images/mysql/Infimum-Rows-Supremum.jpg)

User Records 就是整個頁面中真正用於存放行記錄的部分，而 Free Space 就是空餘空間了，它是一個連結串列的資料結構，為了保證插入和刪除的效率，整個頁面並不會按照主鍵順序對所有記錄進行排序，它會自動從左側向右尋找空白節點進行插入，行記錄在物理儲存上並不是按照順序的，它們之間的順序是由 `next_record` 這一指標控制的。

B+ 樹在查詢對應的記錄時，並不會直接從樹中找出對應的行記錄，它只能獲取記錄所在的頁，將整個頁載入到記憶體中，再透過 Page Directory 中儲存的稀疏索引和 `n_owned`、`next_record` 屬性取出對應的記錄，不過因為這一操作是在記憶體中進行的，所以通常會忽略這部分查詢的耗時。

InnoDB 儲存引擎中對資料的儲存是一個非常複雜的話題，這一節中也只是對錶、行記錄以及頁面的儲存進行一定的分析和介紹，雖然作者相信這部分知識對於大部分開發者已經足夠了，但是想要真正消化這部分內容還需要很多的努力和實踐。

## [](https://draveness.me/mysql-innodb#%E7%B4%A2%E5%BC%95)索引

索引是資料庫中非常非常重要的概念，它是儲存引擎能夠快速定位記錄的秘密武器，對於提升資料庫的效能、減輕資料庫伺服器的負擔有著非常重要的作用；**索引最佳化是對查詢效能最佳化的最有效手段**，它能夠輕鬆地將查詢的效能提高几個數量級。

### [](https://draveness.me/mysql-innodb#%E7%B4%A2%E5%BC%95%E7%9A%84%E6%95%B0%E6%8D%AE%E7%BB%93%E6%9E%84)索引的資料結構

在上一節中，我們談了行記錄的儲存和頁的儲存，在這裡我們就要從更高的層面看 InnoDB 中對於資料是如何儲存的；InnoDB 儲存引擎在絕大多數情況下使用 B+ 樹建立索引，這是關係型資料庫中查詢最為常用和有效的索引，但是 B+ 樹索引並不能找到一個給定鍵對應的具體值，它只能找到資料行對應的頁，然後正如上一節所提到的，資料庫把整個頁讀入到記憶體中，並在記憶體中查詢具體的資料行。

![B+Tree](https://raw.githubusercontent.com/Draveness/Analyze/master/contents/Database/images/mysql/B+Tree.jpg)

B+ 樹是平衡樹，它查詢任意節點所耗費的時間都是完全相同的，比較的次數就是 B+ 樹的高度；在這裡，我們並不會深入分析或者動手實現一個 B+ 樹，只是對它的特性進行簡單的介紹。

### [](https://draveness.me/mysql-innodb#%E8%81%9A%E9%9B%86%E7%B4%A2%E5%BC%95%E5%92%8C%E8%BE%85%E5%8A%A9%E7%B4%A2%E5%BC%95)聚集索引和輔助索引

資料庫中的 B+ 樹索引可以分為聚集索引（clustered index）和輔助索引（secondary index），它們之間的最大區別就是，聚集索引中存放著一條行記錄的全部資訊，而輔助索引中只包含索引列和一個用於查詢對應行記錄的『書籤』。

#### [](https://draveness.me/mysql-innodb#%E8%81%9A%E9%9B%86%E7%B4%A2%E5%BC%95)聚集索引

InnoDB 儲存引擎中的表都是使用索引組織的，也就是按照鍵的順序存放；聚集索引就是按照表中主鍵的順序構建一顆 B+ 樹，並在葉節點中存放表中的行記錄資料。

```
CREATE TABLE users(
    id INT NOT NULL,
    first_name VARCHAR(20) NOT NULL,
    last_name VARCHAR(20) NOT NULL,
    age INT NOT NULL,
    PRIMARY KEY(id),
    KEY(last_name, first_name, age)
    KEY(first_name)
);

```

如果使用上面的 SQL 在資料庫中建立一張表，B+ 樹就會使用 `id` 作為索引的鍵，並在葉子節點中儲存一條記錄中的**所有**資訊。

![Clustered-Index](https://raw.githubusercontent.com/Draveness/Analyze/master/contents/Database/images/mysql/Clustered-Index.jpg)

> 圖中對 B+ 樹的描述與真實情況下 B+ 樹中的資料結構有一些差別，不過這裡想要表達的主要意思是：聚集索引葉節點中儲存的是整條行記錄，而不是其中的一部分。

聚集索引與表的物理儲存方式有著非常密切的關係，所有正常的表應該**有且僅有一個**聚集索引（絕大多數情況下都是主鍵），表中的所有行記錄資料都是按照**聚集索引**的順序存放的。

當我們使用聚集索引對錶中的資料進行檢索時，可以直接獲得聚集索引所對應的整條行記錄資料所在的頁，不需要進行第二次操作。

#### [](https://draveness.me/mysql-innodb#%E8%BE%85%E5%8A%A9%E7%B4%A2%E5%BC%95)輔助索引

資料庫將所有的非聚集索引都劃分為輔助索引，但是這個概念對我們理解輔助索引並沒有什麼幫助；輔助索引也是透過 B+ 樹實現的，但是它的葉節點並不包含行記錄的全部資料，僅包含索引中的所有鍵和一個用於查詢對應行記錄的『書籤』，在 InnoDB 中這個書籤就是當前記錄的主鍵。

輔助索引的存在並不會影響聚集索引，因為聚集索引構成的 B+ 樹是資料實際儲存的形式，而輔助索引只用於加速資料的查詢，所以一張表上往往有多個輔助索引以此來提升資料庫的效能。

> 一張表一定包含一個聚集索引構成的 B+ 樹以及若干輔助索引的構成的 B+ 樹。

![Secondary-Index](https://raw.githubusercontent.com/Draveness/Analyze/master/contents/Database/images/mysql/Secondary-Index.jpg)

如果在表 `users` 中存在一個輔助索引 `(first_name, age)`，那麼它構成的 B+ 樹大致就是上圖這樣，按照 `(first_name, age)` 的字母順序對錶中的資料進行排序，當查詢到主鍵時，再透過聚集索引獲取到整條行記錄。

![Clustered-Secondary-Index](https://raw.githubusercontent.com/Draveness/Analyze/master/contents/Database/images/mysql/Clustered-Secondary-Index.jpg)

上圖展示了一個使用輔助索引查詢一條表記錄的過程：透過輔助索引查詢到對應的主鍵，最後在聚集索引中使用主鍵獲取對應的行記錄，這也是通常情況下行記錄的查詢方式。

### [](https://draveness.me/mysql-innodb#%E7%B4%A2%E5%BC%95%E7%9A%84%E8%AE%BE%E8%AE%A1)索引的設計

索引的設計其實是一個非常重要的內容，同時也是一個非常複雜的內容；索引的設計與建立對於提升資料庫的查詢效能至關重要，不過這不是本文想要介紹的內容，有關索引的設計與最佳化可以閱讀 [資料庫索引設計與最佳化](https://draveness.me/%E6%95%B0%E6%8D%AE%E5%BA%93%E7%B4%A2%E5%BC%95%E8%AE%BE%E8%AE%A1%E4%B8%8E%E4%BC%98%E5%8C%96) 一書，書中提供了一種非常科學合理的方法能夠幫助我們在資料庫中建立最適合的索引，當然作者也可能會在之後的文章中對索引的設計進行簡單的介紹和分析。

## [](https://draveness.me/mysql-innodb#%E9%94%81)鎖

我們都知道鎖的種類一般分為樂觀鎖和悲觀鎖兩種，InnoDB 儲存引擎中使用的就是悲觀鎖，而按照鎖的粒度劃分，也可以分成行鎖和表鎖。

### [](https://draveness.me/mysql-innodb#%E5%B9%B6%E5%8F%91%E6%8E%A7%E5%88%B6%E6%9C%BA%E5%88%B6)併發控制機制

樂觀鎖和悲觀鎖其實都是併發控制的機制，同時它們在原理上就有著本質的差別；

*   樂觀鎖是一種思想，它其實並不是一種真正的『鎖』，它會先嚐試對資源進行修改，在寫回時判斷資源是否進行了改變，如果沒有發生改變就會寫回，否則就會進行重試，在整個的執行過程中其實都**沒有對資料庫進行加鎖**；
*   悲觀鎖就是一種真正的鎖了，它會在獲取資源前對資源進行加鎖，確保同一時刻只有有限的執行緒能夠訪問該資源，其他想要嘗試獲取資源的操作都會進入等待狀態，直到該執行緒完成了對資源的操作並且釋放了鎖後，其他執行緒才能重新操作資源；

雖然樂觀鎖和悲觀鎖在本質上並不是同一種東西，一個是一種思想，另一個是一種真正的鎖，但是它們都是一種併發控制機制。

![Optimistic-Pessimistic-Locks](https://raw.githubusercontent.com/Draveness/Analyze/master/contents/Database/images/mysql/Optimistic-Pessimistic-Locks.jpg)

樂觀鎖不會存在死鎖的問題，但是由於更新後驗證，所以當**衝突頻率**和**重試成本**較高時更推薦使用悲觀鎖，而需要非常高的**響應速度**並且**併發量**非常大的時候使用樂觀鎖就能較好的解決問題，在這時使用悲觀鎖就可能出現嚴重的效能問題；在選擇併發控制機制時，需要綜合考慮上面的四個方面（衝突頻率、重試成本、響應速度和併發量）進行選擇。

### [](https://draveness.me/mysql-innodb#%E9%94%81%E7%9A%84%E7%A7%8D%E7%B1%BB)鎖的種類

對資料的操作其實只有兩種，也就是讀和寫，而資料庫在實現鎖時，也會對這兩種操作使用不同的鎖；InnoDB 實現了標準的行級鎖，也就是共享鎖（Shared Lock）和互斥鎖（Exclusive Lock）；共享鎖和互斥鎖的作用其實非常好理解：

*   **共享鎖（讀鎖）**：允許事務對一條行資料進行讀取；
*   **互斥鎖（寫鎖）**：允許事務對一條行資料進行刪除或更新；

而它們的名字也暗示著各自的另外一個特性，共享鎖之間是相容的，而互斥鎖與其他任意鎖都不相容：

![Shared-Exclusive-Lock](https://raw.githubusercontent.com/Draveness/Analyze/master/contents/Database/images/mysql/Shared-Exclusive-Lock.jpg)

稍微對它們的使用進行思考就能想明白它們為什麼要這麼設計，因為共享鎖代表了讀操作、互斥鎖代表了寫操作，所以我們可以在資料庫中**並行讀**，但是隻能**序列寫**，只有這樣才能保證不會發生執行緒競爭，實現執行緒安全。

### [](https://draveness.me/mysql-innodb#%E9%94%81%E7%9A%84%E7%B2%92%E5%BA%A6)鎖的粒度

無論是共享鎖還是互斥鎖其實都只是對某一個資料行進行加鎖，InnoDB 支援多種粒度的鎖，也就是行鎖和表鎖；為了支援多粒度鎖定，InnoDB 儲存引擎引入了意向鎖（Intention Lock），意向鎖就是一種表級鎖。

與上一節中提到的兩種鎖的種類相似的是，意向鎖也分為兩種：

*   **意向共享鎖**：事務想要在獲得表中某些記錄的共享鎖，需要在表上先加意向共享鎖；
*   **意向互斥鎖**：事務想要在獲得表中某些記錄的互斥鎖，需要在表上先加意向互斥鎖；

隨著意向鎖的加入，鎖型別之間的相容矩陣也變得愈加複雜：

![Lock-Type-Compatibility-Matrix](https://raw.githubusercontent.com/Draveness/Analyze/master/contents/Database/images/mysql/Lock-Type-Compatibility-Matrix.jpg)

意向鎖其實不會阻塞全表掃描之外的任何請求，它們的主要目的是為了表示**是否有人請求鎖定表中的某一行資料**。

> 有的人可能會對意向鎖的目的並不是完全的理解，我們在這裡可以舉一個例子：如果沒有意向鎖，當已經有人使用行鎖對錶中的某一行進行修改時，如果另外一個請求要對全表進行修改，那麼就需要對所有的行是否被鎖定進行掃描，在這種情況下，效率是非常低的；不過，在引入意向鎖之後，當有人使用行鎖對錶中的某一行進行修改之前，會先為表新增意向互斥鎖（IX），再為行記錄新增互斥鎖（X），在這時如果有人嘗試對全表進行修改就不需要判斷表中的每一行資料是否被加鎖了，只需要透過等待意向互斥鎖被釋放就可以了。

### [](https://draveness.me/mysql-innodb#%E9%94%81%E7%9A%84%E7%AE%97%E6%B3%95)鎖的演算法

到目前為止已經對 InnoDB 中鎖的粒度有一定的瞭解，也清楚了在對資料庫進行讀寫時會獲取不同的鎖，在這一小節將介紹鎖是如何新增到對應的資料行上的，我們會分別介紹三種鎖的演算法：Record Lock、Gap Lock 和 Next-Key Lock。

#### [](https://draveness.me/mysql-innodb#record-lock)Record Lock

記錄鎖（Record Lock）是加到**索引記錄**上的鎖，假設我們存在下面的一張表 `users`：

```
CREATE TABLE users(
    id INT NOT NULL AUTO_INCREMENT,
    last_name VARCHAR(255) NOT NULL,
    first_name VARCHAR(255),
    age INT,
    PRIMARY KEY(id),
    KEY(last_name),
    KEY(age)
);

```

如果我們使用 `id` 或者 `last_name` 作為 SQL 中 `WHERE` 語句的過濾條件，那麼 InnoDB 就可以透過索引建立的 B+ 樹找到行記錄並新增索引，但是如果使用 `first_name` 作為過濾條件時，由於 InnoDB 不知道待修改的記錄具體存放的位置，也無法對將要修改哪條記錄提前做出判斷就會鎖定整個表。

#### [](https://draveness.me/mysql-innodb#gap-lock)Gap Lock

記錄鎖是在儲存引擎中最為常見的鎖，除了記錄鎖之外，InnoDB 中還存在間隙鎖（Gap Lock），間隙鎖是對索引記錄中的一段連續區域的鎖；當使用類似 `SELECT * FROM users WHERE id BETWEEN 10 AND 20 FOR UPDATE;` 的 SQL 語句時，就會阻止其他事務向表中插入 `id = 15` 的記錄，因為整個範圍都被間隙鎖鎖定了。

> 間隙鎖是儲存引擎對於效能和併發做出的權衡，並且只用於某些事務隔離級別。

雖然間隙鎖中也分為共享鎖和互斥鎖，不過它們之間並不是互斥的，也就是不同的事務可以同時持有一段相同範圍的共享鎖和互斥鎖，它唯一阻止的就是**其他事務向這個範圍中新增新的記錄**。

#### [](https://draveness.me/mysql-innodb#next-key-lock)Next-Key Lock

Next-Key 鎖相比前兩者就稍微有一些複雜，它是記錄鎖和記錄前的間隙鎖的結合，在 `users` 表中有以下記錄：

```
+------|-------------|--------------|-------+
|   id | last_name   | first_name   |   age |
|------|-------------|--------------|-------|
|    4 | stark       | tony         |    21 |
|    1 | tom         | hiddleston   |    30 |
|    3 | morgan      | freeman      |    40 |
|    5 | jeff        | dean         |    50 |
|    2 | donald      | trump        |    80 |
+------|-------------|--------------|-------+

```

如果使用 Next-Key 鎖，那麼 Next-Key 鎖就可以在需要的時候鎖定以下的範圍：

```
(-∞, 21]
(21, 30]
(30, 40]
(40, 50]
(50, 80]
(80, ∞)

```

> 既然叫 Next-Key 鎖，鎖定的應該是當前值和後面的範圍，但是實際上卻不是，Next-Key 鎖鎖定的是當前值和前面的範圍。

當我們更新一條記錄，比如 `SELECT * FROM users WHERE age = 30 FOR UPDATE;`，InnoDB 不僅會在範圍 `(21, 30]` 上加 Next-Key 鎖，還會在這條記錄後面的範圍 `(30, 40]` 加間隙鎖，所以插入 `(21, 40]` 範圍內的記錄都會被鎖定。

Next-Key 鎖的作用其實是為了解決幻讀的問題，我們會在下一節談事務的時候具體介紹。

### [](https://draveness.me/mysql-innodb#%E6%AD%BB%E9%94%81%E7%9A%84%E5%8F%91%E7%94%9F)死鎖的發生

既然 InnoDB 中實現的鎖是悲觀的，那麼不同事務之間就可能會互相等待對方釋放鎖造成死鎖，最終導致事務發生錯誤；想要在 MySQL 中製造死鎖的問題其實非常容易：

![Deadlocks](https://raw.githubusercontent.com/Draveness/Analyze/master/contents/Database/images/mysql/Deadlocks.jpg)

兩個會話都持有一個鎖，並且嘗試獲取對方的鎖時就會發生死鎖，不過 MySQL 也能在發生死鎖時及時發現問題，並保證其中的一個事務能夠正常工作，這對我們來說也是一個好訊息。

## [](https://draveness.me/mysql-innodb#%E4%BA%8B%E5%8A%A1%E4%B8%8E%E9%9A%94%E7%A6%BB%E7%BA%A7%E5%88%AB)事務與隔離級別

在介紹了鎖之後，我們再來談談資料庫中一個非常重要的概念 —— 事務；相信只要是一個合格的軟體工程師就對事務的特性有所瞭解，其中被人經常提起的就是事務的原子性，在資料提交工作時，要麼保證所有的修改都能夠提交，要麼就所有的修改全部回滾。

但是事務還遵循包括原子性在內的 ACID 四大特性：原子性（Atomicity）、一致性（Consistency）、隔離性（Isolation）和永續性（Durability）；文章不會對這四大特性全部展開進行介紹，相信你能夠透過 Google 和資料庫相關的書籍輕鬆獲得有關它們的概念，本文最後要介紹的就是事務的四種隔離級別。

### [](https://draveness.me/mysql-innodb#%E5%87%A0%E7%A7%8D%E9%9A%94%E7%A6%BB%E7%BA%A7%E5%88%AB)幾種隔離級別

事務的隔離性是資料庫處理資料的幾大基礎之一，而隔離級別其實就是提供給使用者用於在效能和可靠性做出選擇和權衡的配置項。

ISO 和 ANIS SQL 標準制定了四種事務隔離級別，而 InnoDB 遵循了 SQL:1992 標準中的四種隔離級別：`READ UNCOMMITED`、`READ COMMITED`、`REPEATABLE READ` 和 `SERIALIZABLE`；每個事務的隔離級別其實都比上一級多解決了一個問題：

*   `RAED UNCOMMITED`：使用查詢語句不會加鎖，可能會讀到未提交的行（Dirty Read）；
*   `READ COMMITED`：只對記錄加記錄鎖，而不會在記錄之間加間隙鎖，所以允許新的記錄插入到被鎖定記錄的附近，所以再多次使用查詢語句時，可能得到不同的結果（Non-Repeatable Read）；
*   `REPEATABLE READ`：多次讀取同一範圍的資料會返回第一次查詢的快照，不會返回不同的資料行，但是可能發生幻讀（Phantom Read）；
*   `SERIALIZABLE`：InnoDB 隱式地將全部的查詢語句加上共享鎖，解決了幻讀的問題；

MySQL 中預設的事務隔離級別就是 `REPEATABLE READ`，但是它透過 Next-Key 鎖也能夠在某種程度上解決幻讀的問題。

![Transaction-Isolation-Matrix](https://raw.githubusercontent.com/Draveness/Analyze/master/contents/Database/images/mysql/Transaction-Isolation-Matrix.jpg)

接下來，我們將資料庫中建立如下的表並透過個例子來展示在不同的事務隔離級別之下，會發生什麼樣的問題：

```
CREATE TABLE test(
    id INT NOT NULL,
    UNIQUE(id)
);

```

### [](https://draveness.me/mysql-innodb#%E8%84%8F%E8%AF%BB)髒讀

> 在一個事務中，讀取了其他事務未提交的資料。

當事務的隔離級別為 `READ UNCOMMITED` 時，我們在 `SESSION 2` 中插入的**未提交**資料在 `SESSION 1` 中是可以訪問的。

![Read-Uncommited-Dirty-Read](https://raw.githubusercontent.com/Draveness/Analyze/master/contents/Database/images/mysql/Read-Uncommited-Dirty-Read.jpg)

### [](https://draveness.me/mysql-innodb#%E4%B8%8D%E5%8F%AF%E9%87%8D%E5%A4%8D%E8%AF%BB)不可重複讀

> 在一個事務中，同一行記錄被訪問了兩次卻得到了不同的結果。

當事務的隔離級別為 `READ COMMITED` 時，雖然解決了髒讀的問題，但是如果在 `SESSION 1` 先查詢了**一行**資料，在這之後 `SESSION 2` 中修改了同一行資料並且提交了修改，在這時，如果 `SESSION 1` 中再次使用相同的查詢語句，就會發現兩次查詢的結果不一樣。

![Read-Commited-Non-Repeatable-Read](https://img.draveness.me/mweb/2019-07-16-Read-Commited-Non-Repeatable-Read.jpg)

不可重複讀的原因就是，在 `READ COMMITED` 的隔離級別下，儲存引擎不會在查詢記錄時新增行鎖，鎖定 `id = 3` 這條記錄。

### [](https://draveness.me/mysql-innodb#%E5%B9%BB%E8%AF%BB)幻讀

> 在一個事務中，同一個範圍內的記錄被讀取時，其他事務向這個範圍新增了新的記錄。

重新開啟了兩個會話 `SESSION 1` 和 `SESSION 2`，在 `SESSION 1` 中我們查詢全表的資訊，沒有得到任何記錄；在 `SESSION 2` 中向表中插入一條資料並提交；由於 `REPEATABLE READ` 的原因，再次查詢全表的資料時，我們獲得到的仍然是空集，但是在向表中插入同樣的資料卻出現了錯誤。

![Repeatable-Read-Phantom-Read](https://raw.githubusercontent.com/Draveness/Analyze/master/contents/Database/images/mysql/Repeatable-Read-Phantom-Read.jpg)

這種現象在資料庫中就被稱作幻讀，雖然我們使用查詢語句得到了一個空的集合，但是插入資料時卻得到了錯誤，好像之前的查詢是幻覺一樣。

在標準的事務隔離級別中，幻讀是由更高的隔離級別 `SERIALIZABLE` 解決的，但是它也可以透過 MySQL 提供的 Next-Key 鎖解決：

![Repeatable-with-Next-Key-Lock](https://raw.githubusercontent.com/Draveness/Analyze/master/contents/Database/images/mysql/Repeatable-with-Next-Key-Lock.jpg)

`REPEATABLE READ` 和 `READ UNCOMMITED` 其實是矛盾的，如果保證了前者就看不到已經提交的事務，如果保證了後者，就會導致兩次查詢的結果不同，MySQL 為我們提供了一種折中的方式，能夠在 `REPEATABLE READ` 模式下加鎖訪問已經提交的資料，其本身並不能解決幻讀的問題，而是透過文章前面提到的 Next-Key 鎖來解決。

## [](https://draveness.me/mysql-innodb#%E6%80%BB%E7%BB%93)總結

> 文章中的內容大都來自於 [高效能 MySQL](https://book.douban.com/subject/23008813/)、[MySQL 技術內幕：InnoDB 儲存引擎](https://book.douban.com/subject/24708143/)、[資料庫索引設計與最佳化](https://book.douban.com/subject/26419771/) 以及 MySQL 的 [官方文件](https://dev.mysql.com/doc/)。

由於篇幅所限僅能對資料庫中一些重要內容進行簡單的介紹和總結，文中內容難免有所疏漏，如果對文章內容的有疑問，可以在部落格下面評論留言。

## [](https://draveness.me/mysql-innodb#reference)Reference

*   [mysqld_safe version different than mysqld?](https://dba.stackexchange.com/questions/35962/mysqld-safe-version-different-than-mysqld)
*   [File Space Management](https://dev.mysql.com/doc/refman/5.7/en/innodb-file-space.html)
*   [Externally Stored Fields in InnoDB](http://mysqlserverteam.com/externally-stored-fields-in-innodb/)
*   [InnoDB Record Structure](https://dev.mysql.com/doc/internals/en/innodb-record-structure.html)
*   [InnoDB Page Structure](https://dev.mysql.com/doc/internals/en/innodb-page-structure.html)
*   [Difference between clustered and nonclustered index](https://stackoverflow.com/questions/5070529/difference-between-clustered-and-nonclustered-index)
*   [InnoDB Locking](https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html)
*   [樂觀鎖與悲觀鎖的區別](http://www.cnblogs.com/Bob-FD/p/3352216.html)
*   [Optimistic concurrency control](https://en.wikipedia.org/wiki/Optimistic_concurrency_control)
*   [MySQL 四種事務隔離級的說明](http://www.cnblogs.com/zhoujinyi/p/3437475.html)

重新學習Mysql資料庫3：Mysql儲存引擎與資料儲存原理

相關文章