chikaku

且听风吟

永远是深夜有多好｡
github
email
首頁封存

InnoDB 儲存引擎概覽

#数据库#MySQL#InnoDB63
AI 翻譯
這篇文章透過AI由簡體中文翻譯成繁體中文。查看原文

《MySQL 技術內幕 InnoDB 儲存引擎 (第二版)》 讀書筆記。

緩衝池#

對於資料庫來說，查詢和讀取的效率是非常關鍵的，而一般資料庫都是將資料儲存在磁碟中。但是相對而言磁碟的讀寫速度太慢了無法滿足資料庫高速讀寫的需求，所以一般儲存引擎會利用緩衝池技術，將磁碟中的一部分資料讀到記憶體中快取下來，稱之為緩衝池。

  • 對於讀請求，先看緩衝池裡面有沒有對應的資料，有就直接從記憶體中讀返回，沒有就在從磁碟裡面讀出來，放到記憶體裡再返回。
  • 對於寫請求，先查看緩衝池裡有沒有對應的資料，有就直接修改記憶體資料返回。如果沒有，就還是從磁碟裡讀出來對應的那一塊資料放到記憶體，然後修改記憶體資料返回。儲存引擎會在之後的某些時間點把髒資料重新刷回到磁碟。

緩衝池最重要的參數是緩衝池的大小，這個數值將直接影響資料庫的性能。想像以下如果緩衝池不夠大，快取的命中率很低，需要經常讀磁碟，那效率可太低了。 InnoDB 也使用了緩衝池技術，其大小配置在 innodb_buffer_pool_size 中。在 InnoDB 緩衝池中快取的不僅僅是資料，還有索引，undo buffer, change buffer, hash index 等等。這些記憶體資料在記憶體中以頁 (page) 的形式組織，頁的大小配置在 innodb_page_size 中預設是 16K。

InnoDB 支持多個緩衝池實例，每個頁會根據哈希分配到不同的實例，配置在 innodb_buffer_pool_instances 預設是 1 多個緩衝池可以減少內部的資源如鎖的競爭，提升並發性能。

LRU List#

緩衝池的任務就是從記憶體中讀取請求需要的資料頁，如果能從記憶體中讀到，可以稱之為命中，響應速度就會很快。如果讀不到，沒命中，就需要讀磁碟，速度就很慢。觀察一個緩衝池的性能最重要的參數就是緩衝命中率。

為了提高緩衝的命中率，一般會採用 LRU(latest recent used) 算法。這個算法維護了一個緩衝列表，把最經常訪問的頁放在列表的最前面，最少使用的放在最後面。如果快取沒有命中，從磁碟中讀取新的頁，就從列表中丟棄最後的那一個頁，再把新讀取的頁放到列表裡面去。至於新讀取的頁放到列表的那個位置，要看具體的實現。

InnoDB 就採用了這種算法，但是做了一些優化。在 InnoDB 的 LRU 列表中，新讀取的頁會放到列表中 midpoint 的位置 midpoint 是一個百分比，在這個百分比之後的列表稱為 old 列表，在此之前的稱為 new 列表。以 modpoint 為分界線 InnoDB 的 LRU 其實是由這兩個單獨的列表共同組成的。modpoint 值配置在 innodb_old_blocks_pct 預設是 37 也就是說新讀取的頁會放到距列表尾端 37% 的位置。

為什麼沒有放到最前端呢？設想一下，如果放到了最前端，也就是把這個頁當作最常用的頁。但是對於某些 SQL 操作比如索引或者是資料的掃描，經常會訪問大量的資料，但是這些資料很可能僅僅是在這次掃描中使用了一次。如果把這樣的資料每次都放到最前端很可能就把真正的熱點資料給頂出 LRU 列表，造成命中率下降。不過新讀取的資料頁也有可能真的是熱點資料，該如何判斷呢？

首先需要知道的是，在 new 列表中命中的頁會被提升到 new 列表的最前端。在 old 列表中命中的頁也會提升到 old 列表的最前端。

那麼假設新讀取的這個資料頁是熱點資料，一段時間內，這個頁會肯定被經常訪問，每次訪問都會把它提升到 old 列表的頭部這個頁是不會被刷出去的。但如果不是熱點資料而是一個臨時資料，那應該很快就會被頂到下面去，甚至刷出 old 列表。這樣，通過讓資料頁自己來保證自己能夠存活的足夠久，基本上可以證明這是一個熱點資料頁。所以 InnoDB 還增加了另外一個參數配置在 innodb_old_blocks_time 表示新讀取的頁在 old 列表中持續存在多長時間後會被放到 new 列表裡面去。把頁從 old 中移到 new 也叫做 page made young 而在 old 列表中沒有存活足夠長的時間後被刷出叫做 page not made young。

unzip LRU#

在緩衝池中有些資料頁的大小可能不是 16KB 的這種頁如果頁佔用 LRU 的 16KB 頁會有些浪費，所以 innodb 添加了一個 unzip_LRU 列表用來管理這種非 16KB 的壓縮頁。

現在假設我們需要一個 2KB 的頁，unzip_LRU 會首先尋找 2KB 的頁：

  • 沒找到，繼續向上找 4KB 的頁，如果找到了分成兩個 2KB 的頁，完成分配
  • 沒找到，繼續向上找 8KB 的頁，如果找到了分成一個 4KB 兩個 2KB 的頁完成分配
  • 沒找到，繼續向上找 16KB 的頁，分成一個 8KB 一個 4KB 兩個 2KB 的頁完成分配

Free List#

上面討論 LRU 列表的時候都是在假設 LRU 已經滿了的情況下去討論的，但是實際上在資料庫剛啟動的時候 LRU 是空的。這個時候緩衝池中的所有頁都放在 Free 列表上。每次有從磁碟中讀取新的頁出來後，首先要從 Free 列表中找，有沒有空閒的頁，如果有的話，把空閒頁從中取出，放到 LRU 列表裡面去。如果沒有的話，才是按照上面 LRU 中討論的情況來丟棄某個頁。

Flush List#

以上兩種列表討論的都是讀的情況，正常的資料庫請求中也會有很多寫操作，對於寫操作，首先還是按照 LRU 的規則讀記憶體中的資料頁，然後修改這個資料頁，這個被修改了的資料頁被稱為髒頁 (dirty page) 但是髒頁並不會從 LRU 中移除，而是同時添加到另外一個 Flush 列表中。這樣在記憶體中就完成了資料的修改，後序這個髒頁的讀還是由 LRU 來負責而且能夠讀到最新的資料保證可用性。另一邊儲存引擎會通過一定的機制把 Flush 列表中的資料都刷回到磁碟，這個刷新並不是即時的，否則大量的磁碟寫將會非常影響資料庫的性能。當然如果資料庫宕機，可能會導致髒頁還沒來得及寫回去，這種情況下磁碟資料的持久性由事務的實現來保證。

Checkpoint 機制#

在討論 Checkpoint 技術之前，先討論上面講的 Flush 列表沒來得及刷新髒頁的問題。在 InnoDB 的事務實現中，每次事務提交之前，會先寫一個 redo log 也叫做重做日誌，先寫到記憶體的 redo log buffer 中然後按一定頻率刷到磁碟上，記錄了這條事務對資料的完整修改。然後再去修改記憶體中的頁，這樣當資料庫宕機後，重啟時可以通過 redo log 來恢復資料。通過這樣的方式，理想情況下甚至資料庫都不需要把髒頁刷新回磁碟，每次重啟載入 redo log 就行了。當然前提是記憶體和磁碟足夠大，這種情況是不現實的。所以髒頁還是要刷回磁碟的。當資料庫宕機重啟時，只需要恢復上次已經刷回到磁碟往後的 redo log 即可。

除了對資料的修改，在 LRU old 列表中如果被丟棄的頁是髒頁，那這個時候也需要刷回磁碟的，不然下次從磁碟裡取的就是舊資料了。在記憶體中 redo log buffer 的大小配置在 innodb_log_buffer_size 如果超過了這個上限，就沒辦法保證事務了，所以也是即時即時刷髒頁到磁碟保證有空的 redo log buffer。

InnoDB 採用了 Checkpoint 機制來將髒頁刷新回磁碟。Checkpoint 在內部分為兩種：

一種是 Sharp Checkpoint 在資料庫正常關閉的時候把所有的髒頁刷回磁碟。這裡的正常關閉是指配置 innodb_fast_shutdown 被設置為預設值 1 就是把緩衝池裡的髒頁刷新回去，其他就不管了。如果這個值是 0 那麼資料庫在關機前會進行完整的頁回收和 change buffer 的合併等等稱為 slow shutdown 而如果配置為 2 則只是把日誌文件給寫到磁碟日誌文件裡，就停掉。表資料的完整性需要等待下次啟動時的恢復。這個參數的具體行為可以查看 官網文檔 與之相關的還有一個配置 innodb_force_recovery 控制 InnoDB 的恢復。

另一種就是在運行時不斷的執行的 Fuzzy Checkpoint 在一定情況下刷新一部分的髒頁到磁碟。這幾類情況如下：

  • 主線程每隔一段時間會異步的把緩衝池裡面的髒頁刷到磁碟
  • LRU 列表裡面淘汰了一個資料頁，如果是髒頁，要刷回到磁碟
  • redo log 日誌可用空間不多的時候，如果達到 redo log 文件最大容量 75% 以上的時候就要需要執行異步刷髒頁到磁碟了不然後序 redo log 不夠用會影響到事務提交。達到 90% 以上的時候就需要同步刷新到磁碟。
  • 如果髒頁的數量太多也是要刷回磁碟的，配置 innodb_max_dirty_pages_pct_lwm 首先表示了一個較低的髒頁佔緩衝池數量的百分比比值，達到這個百分比後開始進行預刷新 (TODO: 什麼是預刷新？) 而達到配置 innodb_max_dirty_pages_pct 的百分比後將會進行正常的刷新。

查看緩衝池狀態#

通過查看 innodb 儲存引擎的狀態的命令可以查看 innodb 的運行狀態，此處列舉了緩衝池和記憶體相關的幾個關鍵參數，基本上從名字都能看出來。這裡 Free 加上 LRU 並不等於總的緩衝池大小，因為緩衝池中的 page 還會分配給其他用途比如 hash 或 change buffer 等等。

> show engine innodb status;

----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 137363456  // 分配給 innodb 的總記憶體大小
Buffer pool size   8192                 // 記憶體池的頁的個數，大小要乘上 page_size
Free buffers       7146                 // Flush List 頁數量
Database pages     1042                 // LRU List 頁數量
Old database pages 402                  // LRU 中 old 列表頁數量
Buffer pool hit rate 1000 / 1000        // 快取命中率

InnoDB 引擎特性#

MySQL 和 InnoDB 簡介#

MySQL 是比較常用的一個關聯型資料庫，他和其他資料庫的一個區別在於提供了插件式的儲存引擎。按照官方的說法，這個插件式在於你可以自己編寫一個儲存引擎，然後把他加到一個運行中的 MySQL 實例上，甚至不需要重新編譯和重啟。

InnoDB 是 MySQL5.5.8 以後的預設儲存引擎。設計目標主要是做 OLTP 特點是行鎖設計，支持外鍵，預設讀取操作不會產生鎖，可以將每個表的資料存放到一個單獨的 .idb 文件中。
InnoDB 通過 MVCC(multiversion concurrency control) 獲取高並發性。其實現了四種隔離級別，默認為 REPEATABLE。

InnoDB 表資料的存儲是按照主鍵的順序進行存放，如果沒有顯式的在定義的時候指定主鍵，InnoDB 會自動生成一個六字節的 ROWID 做主鍵。如果資料量超出這個 ROWID 的範圍 (281 萬億行)，最早的資料會被覆蓋。

Change buffer#

通常在資料庫的每個表中都會有一個聚集索引 (也就是我們常說的主鍵)，也可能會多個的輔助索引。假設我們需要向表中插入一條新的記錄，那這個時候索引樹是需要更新的。對於只有一個聚集索引的情況來說，很好辦因為聚集索引一般都是順序遞增的，新加一行的時候只需要順序寫，把新的索引添加到索引樹的最後就行了。但是如果表中有輔助索引那就需要在輔助索引樹中找到新加的索引應該在的位置。如果這個索引頁不在緩衝池裡的話，就需要進行磁碟的隨機讀寫。對於大量的寫請求，這樣多次的隨機磁碟 IO 是非常影響性能的。

所以 InnoDB 引進了一種 change buffer 的方案，在對輔助索引進行插入，更新，刪除操作的時候，如果這個索引頁在緩衝池裡，就直接改緩衝池。如果不在緩衝池裡，就先放到 change buffer 裡面去，等其他操作，比如有一個讀當前索引頁的請求，那這時候就一定要從磁碟裡讀出來到緩衝池了。然後 change buffer 再把自己的修改給合併進去，這樣就避免了每次 insert/update/delete 需要進行的隨機磁碟 IO。

除此之外如果一段時間內，索引頁沒有被讀到緩衝池，但是對這個索引頁又進行了多次訪問或者修改，那麼多次操作也會在 change buffer 的對應索引頁上進行合併，後續只需要回寫一次磁碟就可以了。

另外 change buffer 也是有一定限制的，首先，這個索引一定是個輔助索引，而且這個索引不能是唯一的，因為如果這個索引需要唯一，那 insert/update 的時候肯定就不可避免的要把所有索引頁掃描一遍確認是否有相同的索引，再做 change buffer 就沒意義了。

change buffer 分為三類：

  • insert buffer 表示一個插入操作緩衝
  • delete buffer 表示將記錄標記為刪除
  • purge buffer 表示將記錄真正的刪除

在 InnoDB 內部，update 是由 delete 和 purge 兩步操作共同完成的。配置 innodb_change_buffering 可以指定 change buffer 緩衝的類型，預設是 all 也就是緩衝全部類型。

在 InnoDB 內部，change buffer 的結構是一顆 B+ 樹。這個樹當然也有大小限制，配置在 innodb_change_buffer_max_size 表示 change buffer 占總緩衝池大小的最大百分比預設是 25 如果 change buffer 的量達到了一定大小，內部也會強制讀取索引頁進行合併，避免 change buffer 空間不足。

Double write#

在資料庫運行的過程中，時時刻刻都在將緩衝池中的資料同步到磁碟中，保證資料的持久性。這個時候就可能會產生一些，比如我們一個 16KB 的資料頁在寫磁碟的過程中，剛寫完 4KB 資料庫宕機了，這個時候即使有 redo log 但是磁碟上的頁已經被寫了 4KB 髒了 redo log 儲存的物理日誌並不能恢復完整的這一個頁。(詳細的原因看下面 redo log 這一節) 所以在用 redo log 之前需要先備份這個頁，如果寫入失效，可以通過備份頁把磁碟頁還原回去，之後再用 redo log 嘗試恢復。

InnoDB 使用 doublewrite 技術來解決這個問題。在記憶體中有一塊區域叫做 doublewrite buffer 大小為 2MB 在磁碟的共享表空間上也有一塊大小 2MB 的空間。在緩衝池髒頁刷新的過程中，首先會把髒頁給寫到磁碟的共享表空間裡執行 fsync 然後再去寫各個表資料文件。這樣即使再寫資料文件的過程中出錯，也能從共享表空間中找回髒頁的副本。(TODO: 刷新到共享表空間的時候為什麼會分成兩次，每次 1MB 的寫？)

通過配置 innodb_doublewrite 可以禁用 doublewrite 功能，在某些提供寫失效保護的檔案系統上是不需要這種功能的。

異步 IO#

InnoDB 採用異步 IO 的方式把資料刷新到磁碟，這樣就不會產生執行緒阻塞，而且 AIO 也可以進行 IO Merge 減少磁碟 IO 次數。關於 Linux 下的 AIO 可以查看 man 文檔。

可以通過配置 innodb_use_native_aio 開關 AIO。

刷新臨近頁#

InnoDB 在刷新一個髒頁回磁碟的時候可以檢測這個頁所在區的所有頁，如果有其他髒頁，也一並刷新到磁碟，這樣可以利用 IO Merge 減少 IO 次數。通過配置 innodb_flush_neighbors 來開關。

InnoDB 工作執行緒#

上面我們看了 InnoDB 在運行時的一些狀態，下面就介紹一些 InnoDB 是如何工作的。

Master thread#

Master thread 是 InnoDB 運行的主執行緒，負責大部分的工作。主要有：

  • 刷新髒頁到磁碟
  • 把日誌文件刷新到磁碟，即使事務還沒有提交，提高事務的提交速度
  • 合併 change buffer
  • undo 頁回收

IO thread#

IO 執行緒主要負責 AIO 的請求回調，通過配置 innodb_read_io_threads 和 innodb_write_io_threads 可以更改讀寫執行緒數量。

Purge thread#

在事務的執行過程中，會記錄一個 undo log 用來在事務失敗的時候回滾資料。purge 執行緒的工作就是回收這些在事務執行完以後不再需要的 undo 頁。可以通過配置 innodb_purge_threads 來修改此執行緒的數量。

日誌文件#

這裡講的日誌其實是 MySQL 的日誌跟 InnoDB 無關。因為經常需要查看和處理所以就連帶著放上來了。

error log#

錯誤日誌記錄的是 MySQL 啟動，運行，關閉過程中的錯誤和警告。通過 log_error 變數查看。

slow query log#

慢查詢日誌記錄運行時間超過配置的 long_query_time 的 SQL 語句，預設是 10 秒。通過 slow_query_log 打開，預設是關閉的。日誌記錄位置在配置 slow_query_log_file 中。

general query log#

普通查詢日誌記錄了所有的資料庫請求，記錄在 general_log_file 文件中。通過配置 general_log 來開關。一般是關著的，一直開著對性能有一定的影響。一般只有在調試的時候打開。

binary log#

二進制日誌記錄了所有對資料庫執行的更改 (不包括 select 和 show 這種) 操作。可以通過 bin log 來進行備份恢復，和主從複製等。此日誌記錄在 datadir 下的 bin_log.xxxx 文件中。其中 bin_log.index 是二進制索引文件，儲存之前的 bin log 序號。

InnoDB 事務執行過程中會把二進制日誌記錄到緩衝區中，等事務提交後再將緩衝區中的 bin log 寫到磁碟的 bin log 中。通過配置 sync_binlog 調整寫多少次緩衝後同步到磁碟文件。預設是 1 也就是每次寫完緩衝就刷新到磁碟裡。如果這個參數設置的比較大，可能會有宕機導致 bin log 沒來得及刷到磁碟導致主從資料不同步的問題。

儲存結構#

InnoDB 中所有的資料儲存在一個空間中，稱為共享表空間。表空間從大到小的組織單位是：段 (segment)，區 (extend)，頁 (page) 其中每一级都是由多個下級單位組織而成的。

表結構文件#

在 MySQL 中任何的儲存引擎下都會有一個 .frm 文本文件儲存表的結構和用戶創建的視圖的結構。

表空間文件#

InnoDB 預設所有表有一個共享的表空間。通過配置 innodb_file_per_table 可以將每張表的資料放到一個單獨的表空間。但是這樣每張表的表空間只儲存資料，索引和 change buffer 的 bitmap 頁。其他的資料都還是放在原來的共享表空間裡。表空間文件的後綴是 .ibd 預設情況下的共享表空間文件是 ibddata1。

表空間文件是由各種段組成的，比如資料段，索引段，undo 段等。段是由多個區組成的，區又是由多個頁組成的，且區中的頁都是連續的。頁是 InnoDB 內的最小單元。頁分為很多種，比如資料頁，undo 頁，事務資料頁等等。

行記錄格式#

InnoDB 內資料以行的形式存放在資料頁中，保存在 B+ 樹的節點上，如果有某一列的資料類型長度可變比如 text，則有可能資料會存放到溢出頁中，源資料行保留一個溢出行的偏移量。對於定長的資料，如果資料長度過大，導致一個頁上只能存放一行資料不滿足 B+ 樹的結構，InnoDB 內部也會自動的把行資料存放到溢出頁中。

資料分區#

資料分區是 MySQL 支持的功能，可以把一個表或者是索引資料，物理上分解成多個部分。當前 MySQL 只支持水平分區，即將一個表中的不同行分配到不同的物理文件。而且是局部分區索引，即每個物理文件中即存放資料，也存放索引。

MySQL 的分區支持以下幾種類型：

  • range 分區，根據指定連續的行資料區間，每個區間保存在一個分區中 (1, 2, 3, 4) (5, 6, 7, 8)
  • list 分區，和 range 類似，不過分區的資料可以是離散的比如 (1, 3, 5, 7) (2, 4, 6, 8)
  • hash 分區，根據用戶自定義的表達式的返回值進行 hash 分到不同的區中比如 hash (id % 1000)
  • key 分區，根據資料庫提供的 hash 函數來進行分區，只需要指定被 hash 的字段即可。

在分區的基礎上 MySQL 還可以再進一步進行分區，稱為子分區。MySQL 允許在 range 和 list 分區上再進行 hash 或者 key 分區。具體的分區語法可以參考 官方文檔。

索引#

索引是提升資料庫查詢性能最關鍵的技術。InnoDB 內部支持多種索引：B+ 樹索引，全文索引，哈希索引等。一般開發人員都是用 B+ 樹索引比較多，所以這裡就只討論 B+ 樹索引。B+ 樹索引也分為很多種。

聚集索引#

每個 InnoDB 的資料表中都會有一個主鍵，聚集索引就是按照表的主鍵構造的一顆 B+ 樹，這顆樹的葉子節點就是表的資料頁，保存完整的行資料。每個資料頁通過一個雙向鏈表進行連接，跟表資料中按主鍵存放資料的順序是一樣的。所有表資料的查詢，除了緩衝命中和一些特殊情況之外，都会通过聚集索引樹上查找對應的資料頁。

聚集索引當然也是有磁碟上的物理資料的，但是聚集索引在磁碟上並不是物理連續的，而是在邏輯上連續比如一個一個索引節點的最後一項指向下一個索引節點在文件中的偏移量。否則維護聚集索引的成本會很高。

輔助索引#

每個輔助索引也是一個單獨的 B+ 樹但是這棵樹的葉子節點並不保存完整的行資料，只保存了輔助索引的鍵和表主鍵。通過輔助索引進行查詢行，會首先得到表主鍵，然後再通過主鍵去聚集索引樹上查找完整的行資料。當然有一種特殊情況是查詢的資料在輔助索引的鍵裡面，那這個時候就不必再去聚集索引樹上去找了，這種也叫做 覆蓋索引。

聯合索引#

聯合索引是說對表上的多個列進行索引。用法跟單個鍵的輔助索引是一樣的。在索引的排列順序上是按照索引聲明的順序進行排列的，比如對於 (a, b) 聯合索引。資料的排列是按照先給 a 排序再給 b 排序來的，比如 (1, 1) (1, 2) (2, 1) (2, 2) 更多鍵的情況以此類推。

對於多個鍵的值查詢比如 where a = xx and b = xx 就可以使用聯合索引。而且在聯合索引中是會按第一個鍵進行排序的，所以對於 where a = xx 這樣單個鍵值的查詢也可以用聯合索引。

還有一種用法是，假設我們有 where a = x group by b limit n 這種查詢，對於單個輔助索引，查詢完還需要對 b 進行一次排序。但是聯合索引中，如果確定了第一個字段 a 是定值，第二個字段 b 本來就是排序好的，可以減少排序的操作。

自適應哈希索引#

InnoDB 採用 B+ 樹建立索引，索引的查找次數與 B+ 的深度有關，比如深度是 3 的樹，可能一個索引的查找要經過 3 次查詢，如果我們經常訪問一個資料，每次都要經歷 3 次索引查詢，是有些浪費的。在 InnoDB 內部會為每張表建立一個自適應哈希索引 (adaptive hash index) 用來快取某些熱點資料加快資料查詢。

比如我們連續請求了很多次 select a from t where id = 1; 那 AHI 就會給 where id = 1 這個模式建立一個索引。後續再進行相同的請求的時候，就不需要去查詢 B+ 樹索引，直接從自適應哈希索引取得資料頁。另外哈希索引是只能做等值查詢，不能快取範圍查詢。

建立哈希索引還有一定的條件限制，但是哈希索引本來就是 InnoDB 自己內部的優化，就不再仔細說了。用戶可以通過配置 innodb_adaptive_hash_index 來開關 AHI。

鎖和事務#

資料庫的訪問一定是並發的，這個時候為了保證資料的一致性，就需要有鎖。

InnoDB 內有兩類鎖：

  • latch 輕量級的鎖，鎖定的時間很短，一般是用來保護執行緒資料。latch 也分成兩種一種是 mutex 一種是 rwlock。
  • lock 這類鎖作用的對象是事務，鎖定資料庫中的，表，行。而且鎖定的時間會比較久，直到事務 commit 或者 rollback 的時候才會釋放。

這裡主要關注的還是跟事務有關的 lock 在事務中 InnoDB 會在行級別上對表資料進行加鎖。

行鎖和意向鎖#

InnoDB 支持兩種行鎖：

  • 共享鎖 Shared Lock 允許事務讀一行資料。共享鎖和任何其他鎖兼容。
  • 排他鎖 eXclusive Lock 允許事務刪除或者更新一行資料。排他鎖和任何其他鎖都不兼容。

假設有一個事務 T1 想要獲取某一行的資料，則需要或者這一行的 S 鎖。這個時候有另外一個資料 T2 也想要獲取這一行的資料，也需要一個 S 鎖，兩個 S 鎖是兼容的，所以沒問題。假設有一個 T3 想要更改這一行的資料，那 T3 就需要有這一行的 X 鎖，但是 X 鎖和 S 鎖是不兼容的，所以 T3 會阻塞住等待 T1 和 T2 把 S 鎖都釋放掉。

除了行鎖外，在表級別，InnoDB 還提供了一種 Intention Lock 即意向鎖。

  • 如果需要對某些行的資料進行修改，在獲取行的 X 鎖之前，首先需要獲取這個表的意向排他鎖 IX 表達自己接下來會請求某幾行的排他鎖。
  • 如果要讀某些行的資料，在獲取行的 S 鎖之前，要先獲取這個表的意向共享鎖 IS 表達自己接下來會請求某幾行資料的共享鎖。

意向鎖主要還是提前表達自己希望獲取的行的鎖類型，跟表級別的鎖是不會有衝突的，意向鎖主要還是用在與其他表鎖的比較和測試上。比如一個事務 T1 拿到了某一行資料的 X 鎖，肯定已經拿到了行的 IX 鎖。這個時候如果有一個全表更新的事務 T2 那 T2 需要獲取整個表的 X 鎖，T2 就需要知道當前是否還有沒有哪一行被 X 鎖佔著。如果一行一行的去掃描那就太慢了，這個時候就可以通過查詢表上的意向鎖，看到有一個 IX 鎖在上面，就可以知道當前還有事務持有某些行的 X 鎖，然後就只能阻塞住等這個 IX 解鎖了。

下面這幅圖是兼容矩陣，其中所有 IS, IX 和 IS, IX, S, X 的比較都是表級別的鎖的比較。比如 IS 和 X 不兼容是說表的意向共享鎖和表的排他鎖不兼容，而非用意向鎖和行級鎖做比較。

ISIXSX
IS兼容兼容兼容衝突
IX兼容兼容衝突衝突
S兼容衝突兼容衝突
S衝突衝突衝突衝突

一致性非鎖定讀#

一致性非鎖定讀指的是 InnoDB 通過 MVCC 讀取資料庫中的行。如果當前行被上了 X 鎖，可以通過讀取行快照的方式來避免阻塞。讀快照是用事務的 undo log 實現的。這種方式可以極大的提升資料庫的並發性能。

在 RC 和 RR 的隔離級別下 InnoDB 會使用一致性非鎖定讀。但是在 RC 下讀的快照資料是此時的最新資料，在 RR 下快照資料是事務開始的時候的資料。

某些情況下，如果用戶需要保證資料的一致性，可以通過加鎖的方式來進行一致性鎖定讀 InnoDB 支持兩種 select 加鎖模式：

  • select ... for update 會對行資料加一個 X 鎖
  • select ... lock in share mode 會對資料加一個 S 鎖

這兩條語句一定要放在事務裡用，事務提交或者回滾的時候會自動釋放這兩個鎖。

自增鎖#

對於自增的列，在並發寫情況下就需要使用鎖機制保證資料的一致性，每個自增列在記憶體中都有一個計數器用來分配新插入行的自增列的值。

InnoDB 內部會有一個特殊的自增鎖 AUTO-INC Locking 來維護這個值，每次有事務新增行的時候，自增鎖會通過一個語句 select max(auto_inc) from t for update 來獲取新的自增列值。獲取完之後這個自增鎖立即釋放，不等事務結束。但是這種方式還是鎖了表，在高並發情況下效率還是很低，事務必須等待其他事務用完自增鎖。而且如果有事務回滾，自增值被丟棄，在自增列上會產生不連續的空洞。

InnoDB 可以配置 innodb_autoinc_lock_mode 的值來控制自增列的鎖策略：

  • 0 採用自增鎖，效率很低
  • 1 預設值，對於 insert 和 replace 語句對記憶體中的計數器加 mutex 鎖。沒有事務鎖，相對會快很多。對於其他類型的插入還是會用自增鎖。
  • 2 所有的插入都使用 mutex 鎖效率會很高，但是可能會導致自增的值不連續。

關於自增列，更詳細的內容可以查看 官方文檔。

鎖算法#

行鎖有三種算法：

  • Record Lock 鎖單個行。比如我們要改某一行資料的時候。
  • Gap Lock 鎖一個範圍，不包含記錄本身。比如當前表裡面有 1 3 5 7 這四個資料，現在事務要向裡面加一個資料 4 那這個時候就要鎖定 (3, 5) 這個範圍避免同時有別的事務在這中間插入資料。如果還要再插入一條資料 8 就需要在 (7， +∞) 上再加一個範圍鎖。Gap Lock 的主要作用就是阻止多個事務插入到同一個範圍內。設置隔離級別為 RC 可以關閉 Gap Lock。
  • Next-Key Lock 相當於前兩個鎖相加，既鎖本身又鎖一個範圍。這個鎖是主要是用來解決幻讀問題。

當查詢的索引含有唯一屬性的時候，InnoDB 會將 Next-Key Lock 降級為 Record Lock 比如事務需要向表中插入一行資料 id=7 ... 而且 id 是唯一索引，那只需要鎖 7 這一行就夠了，不需要再去鎖範圍。

幻讀是說在同一個事務下，連續執行兩次同樣的 SQL 可能導致不一樣的結果，第二次的 SQL 可能返回之前不存在的行。

比如一個事務 T1 先執行了一條語句 select id from t where id > 10; 這一次沒有讀出來資料。但是同時有另外一個事務 T2 執行了一個語句 insert into t (id) values (11);接著 T2 先提交。然後 T1 再次執行 select id from t where id > 10; 的時候就會發現突然就有資料了。這種行為違反了事務的隔離性，一個事務能夠感知到另外一個事務的結果。

在使用了 Next-Key Lock 後，我們再執行語句 select id from t where id > 10; 的時候會在 (10, +∞) 加一個 X 鎖這樣 T2 再插入值的時候就會被阻塞住。而 T1 再次執行同一條語句的時候結果是肯定不會變的，因為這個範圍已經被上了 X 鎖沒有事務可以更改這個範圍內的資料。

InnoDB 預設的事務隔離級別 RR 下會使用 Next-Key Lock 來保護事務，避免幻讀。

使用鎖解決事務的隔離性問題#

  • 髒讀：是說一個事務可以讀取到另一個事務還沒有提交的修改。髒讀只會發生在 RU 隔離級別下。至少在 RC 下事務未提交的資料都不會被其他事務感知到。
  • 不可重複讀 / 幻讀：解決方案上面已經說過了。不再贅述。
  • 丟失更新：指的是兩個事務更新同一行，由於事務的隔離性，最後先提交的事務的結果會被後提交的事務的結果給覆蓋。這其實並不是資料庫本身的問題，而是並行事務本來就可能會產生的結果。一種解決方案是使用上面提到的一致性鎖定讀把事務串行化。也就是使用 SERIALIZABLE 隔離級別。

事務 ACID 和隔離級別#

有強大的鎖保護，InnoDB 實現的事務可以實現完全符合 ACID 即：

  • Atomicity 原子性：一個事務是不可分割的單元，事務中的所有操作都成功執行，事務才算成功執行。事務中有任何一個操作失敗，所有已經執行的操作也都要撤回。
  • Consistency 一致性：在事務開始前和結束後，資料庫的所有約束不會被破壞。比如唯一性約束。
  • Isolation 隔離性：每個事務的讀寫在提交前對其他事務都是不可見的。
  • Durability 持久性：事務一旦提交，結果就是永久性的，即使發生宕機也能夠重新恢復。

SQL 標準定了事務的四個隔離級別：

  • READ UNCOMMITTED: 未提交讀。某個事務在執行過程中可以讀到其他事務沒有提交的修改，也就是髒讀。
  • READ COMMITTED: 已提交讀。某個事務在執行過程中可以讀到其他事務已經提交的修改，有幻讀問題。
  • REPEATABLE READ: 可重複讀。某個事務可以讀到其他事務已經提交的新插入的記錄，但是不能讀到其他事務已經提交的修改。InnoDB 預設使用的隔離級別，且在此級別下通過 Next—Key Lock 解決幻讀問題。
  • SERIALIZABLE: 串行化事務。事務中每次讀寫都需要獲取表級別的共享鎖。

一般來講隔離級別越低，事務的鎖保護就越少，保持鎖的時間就越短。

InnoDB 事務的 redo log 和 undo log#

在事務提交前，必須把所有日誌寫到 redo log 文件裡面。這樣即使發生宕機，也能從 redo log 中恢復，保證了資料的持久性。同時為了確保 redo log 能夠寫入到文件中，模式每次寫磁碟 redo log 都會進行一次 fsync 可以通過配置 innodb_flush_log_at_trx_commit 來調整 redo log 刷到磁碟的策略。預設是 1 每次都執行 fsync 確保寫入。設置為 0 的時候在事務提交時不寫 redo log 到文件。設置為 2 的時候會把 redo log 寫到文件系統但是不執行 fsync 這樣如果日誌沒有刷到磁碟就宕機可能會產生資料丟失。刷新 redo log 的策略會很大程度上影響到事務提交的速度。

事務在執行過程中，除了 redo log 還會產生 undo log 當事務回滾時，會執行這些 undo log 來將資料修改回原來的樣子。undo log 只是邏輯日誌，每，並非把資料恢復成事務開始的時候，因為同時有可能並發的有多個事務已經修改了原始的資料。

事務控制語句#

在 MySQL 命令行的預設設置下事務都是自動提交的，每條 SQL 語句執行完都會立即執行 COMMIT 可以通過 SET AUTOCOMMIT=0 來禁用自動提交。當然也可以通過 BEGIN 命令顯示的開啟一個事務。這裡順便整理一下事務的控制語句：

  • BEGIN: 開啟一個事務
  • COMMIT: 提交事務
  • ROLLBACK: 回滾事務
  • SAVEPOINT id: 創建一個檢查點 id
  • RELEASE SAVEPOINT id: 刪除一個檢查點
  • ROLLBACK TO id: 回滾到檢查點 id 這個檢查點之前執行的才做不會被回滾
  • SET TRANSCATION: 設置事務的隔離級別

參考與引用
MySQL 技術內幕 InnoDB 儲存引擎 (第二版)
Mysql InnoDB 官網文檔

載入中......
此文章數據所有權由區塊鏈加密技術和智能合約保障僅歸創作者所有。