通過MySQL儲存原理來分析排序和鎖

先丟擲幾個問題

• 1.為什麼不建議使用訂單號作為主鍵?
• 2.為什麼要在需要排序的欄位上加索引?
• 3.for update 的記錄不存在會導致鎖住全表?
• 4.redolog 和 binlog 有什麼區別?
• 5.MySQL 如何回滾一條 sql ?
• 6.char(50) 和 varchar(50) 效果是一樣的麼?

索引知識回顧

對於 MySQL 資料庫而言,資料是儲存在檔案裡的，而為了能夠快速定位到某張表裡的某條記錄進行查詢和修改,我們需要將這些資料以一定的資料結構進行儲存，這個資料結構就是我們說的索引。回憶一下我們大學裡學過的演算法與資料結構，能夠支援快速查詢的資料結構有：順序陣列、雜湊、搜尋樹。

陣列要求 insert 的時候保證有序，這樣查詢的時候可以利用二分查詢法達到 O(log(N)) 的時間複雜度，對範圍查詢支援也很好，但是 insert 的時候如果不是在陣列尾部，就需要摞動後面所有的資料，時間複雜度為 O(N) 。所以有序陣列只適合儲存靜態資料，例如幾乎很少變動的配置資料，或者是歷史資料。這裡應該會有人有疑問：我用另外一種線性資料結構連結串列來替代陣列不就可以解決陣列因為要移動資料導致太慢的問題了麼，要回答這個問題我們需要了解作業系統讀取檔案的流程，磁碟 IO 是一個相對很慢的操作，為了提高讀取速度，我們應該儘量減少磁碟 IO 操作，而作業系統一般以 ４kb 為一個資料頁讀取資料，而 MySQL 一般為 16kb 作為一個資料塊，已經讀取的資料塊會在記憶體進行快取，如果多次資料讀取在同一個資料塊，則只需要一次磁碟 IO ，而如果順序一致的記錄在檔案中也是順序儲存的，就可以一次讀取多個資料塊，這樣範圍查詢的速度也可以大大提升，顯然連結串列沒有這方面的優勢。

類似於 jdk 中的 hashmap ，雜湊表通過一個特定的雜湊函式將 key 值轉換為一個固定的地址，然後將對應的 value 放到這個位置，如果發生雜湊碰撞就在這個位置拉出一個連結串列，由於雜湊函式的離散特性，所以經過雜湊函式處理後的 key 將失去原有的順序，所以雜湊結構的索引無法滿足範圍查詢，只適合等值查詢的情況例如一些快取的場景。

二叉樹在極端情況下會變成線性結構，也就是每個節點都只有左子節點或者只有右子節點，這樣就無法利用二分查詢只能從第一個節點開始向後遍歷了，所以為了維持 O(log(N)) 的時間複雜度，我們需要在插入節點的時候對節點進行調整以保證樹的平衡，所以平衡二叉樹插入的時間複雜度也是 O(log(N)) ，二叉樹只有兩個子節點，如果資料量很大則樹就很高，樹的每一層一般不在同一個資料塊中儲存，為了儘量的減少磁碟讀寫次數，我們用Ｎ叉樹來代替二叉樹，在 MySQL 中這個Ｎ一般為 1200 ，這樣樹高是 ４ 的話也可以儲存億級別的資料，而且樹的前面兩層一般都在記憶體中， MySQL 中用到的 Ｂ＋ 樹，一般用非葉子節點構建索引，而葉子節點用來儲存具體的值。

InnoDB 中，有聚簇索引和普通索引之分，聚簇索引根據主鍵來構建，葉子節點存放的是該主鍵對應的這一行記錄，而普通索引根據申明這個索引時候的列來構建，葉子節點存放的是這一行記錄對應的主鍵的值，而普通索引中還有唯一索引和聯合索引兩個特例，唯一索引在插入和修改的時候會校驗該索引對應的列的值是否已經存在，而聯合索引將兩個列的值按照申明時候的順序進行拼接後在構建索引。

根據以上描述我們可以得到以下資訊：

資料是以行為單位儲存在聚簇索引裡的，根據主鍵查詢可以直接利用聚簇索引定位到所在記錄，根據普通索引查詢需要先在普通索引上找到對應的主鍵的值，然後根據主鍵值去聚簇索引上查詢記錄，俗稱回表。

普通索引上儲存的值是主鍵的值，如果主鍵是一個很長的字串並且建了很多普通索引，將造成普通索引佔有很大的物理空間，這也是為什麼建議使用 自增ID 來替代訂單號作為主鍵，另一個原因是 自增ID 在 insert 的時候可以保證相鄰的兩條記錄可能在同一個資料塊，而訂單號的連續性在設計上可能沒有自增ID好，導致連續插入可能在多個資料塊，增加了磁碟讀寫次數。

如果我們查詢一整行記錄的話，一定要去聚簇索引上查詢，而如果我們只需要根據普通索引查詢主鍵的值，由於這些值在普通索引上已經存在，所以並不需要回表，這個稱為索引覆蓋，在一定程度上可以提高查詢效率，由於聯合索引上通過多個列構建索引，有時候我們可以將需要頻繁查詢的欄位加到聯合索引裡面，例如如果經常需要根據 name 查詢 age 我們可以建一個 name 和 age 的聯合索引。

查詢的時候如果在索引上用了函式，將導致無法用到根據之前列上的值構建的索引，索引遵循最左匹配原則，所以如果需要查詢某個列的值中間是否包含某個字串，將無法利用索引，如果有這種需求可以利用全文索引，而如果查詢是否以某個字串開頭就可以，聯合索引根據第一個列查詢可以用到索引，僅僅根據第二個列將無法用到索引，查詢的時候用 IN 的效率高於 NOT = 。另外建議將索引的列設定為非空，這個和 NULL 欄位的儲存有關，下文在分析。

儲存格式

有了以上的索引知識我們在來分析資料是怎麼儲存的，InnoDB 儲存引擎的邏輯儲存結構從大到小依次可以分為：表空間、段、區、頁、行。

表空間作為儲存結構的最高層，所有資料都存放在表空間中，預設情況下用一個共享表空間 ibdata1 ，如果開啟了 innodb_file_per_table 則每張表的資料將儲存在單獨的表空間中，也就是每張表都會有一個檔案，

表空間由各個段構成，InnoDB儲存引擎由索引組織的，而索引中的葉子節點用來記錄資料，儲存在資料段，而非葉子節點用來構建索引，儲存在索引段，而回滾段我們在後面分析鎖的時候在聊。

區是由連續的頁組成，任何情況下一個區都是 1MB ，

一個區中可以有多個頁，每個頁預設為 16KB ，所以預設情況下一個區中可以包含64個連續的頁，頁的大小是可以通過 innodb_page_size 設定，頁中儲存的是具體的行記錄。一行記錄最終以二進位制的方式儲存在檔案裡，我們要能夠解析出一行記錄中每個列的值，儲存的時候就需要有固定的格式，至少需要知道每個列佔多少空間，而 MySQL 中定義了一些固定長度的資料型別，例如 int、tinyint、bigint、char陣列、float、double、date、datetime、timestamp 等，這些欄位我們只需要讀取對應長度的位元組，然後根據型別進行解析即可，對於變長欄位，例如 varchar、varbinary 等，需要有一個位置來單獨儲存欄位實際用到的長度，當然還需要頭資訊來儲存後設資料，例如記錄型別，下一條記錄的位置等。下面我們以 Compact 行格式分析一行資料在 InnoDB 中是怎麼儲存的。

• 變長欄位長度列表，該位置用來儲存所申明的變長欄位中非空欄位實際佔有的長度列表，例如有3個非空欄位，其中第一個欄位長度為3，第二個欄位為空，第三個欄位長度為1，則將用 01 03 表示，為空欄位將在下一個位置進行標記。變長欄位長度不能超過 2 個位元組，所以 varchar 的長度最大為 65535。

• NULL 標誌位，佔 1 個位元組，如果對應的列為空則在對應的位上置為 1 ，否則為 0 ，由於該標誌位佔一個位元組，所以列的數量不能超過 255。如果某欄位為空，在後面具體的列資料中將不會在記錄。這種方式也導致了在處理索引欄位為空的時候需要進行額外的操作。

• 記錄頭資訊，固定佔 5 位元組，包含下一條記錄的位置，該行記錄總長度，記錄型別，是否被刪除，對應的 slot 資訊等

• 列資料 包含具體的列對應的值，加上兩個隱藏列，事務 ID 列和回滾指標列。如果沒有申明主鍵，還會增加一列記錄內部 ID。

下面我們以《MySQL 技術內幕》第二版中的例子分析下一行記錄在表空間具體的儲存結構。

CREATE TABLE mytest(
t1 varchar(10),
t2 varchar(10),
t3 char(10),
t4 varchar(10)
) engine = innodb;

insert into mytest VALUES('a','bb','bb','ccc');
insert into mytest VALUES('d',NULL,NULL,'fff');

複製程式碼

該表定義了 3 個變長欄位和 1 個定長欄位，然後插入兩行記錄，第二行記錄包含空值，我們開啟表空間 mytest.ibd 檔案，轉換為 16 進位制，並定位到如下內容：

//第一行記錄
03 02 01 為變長欄位長度列表，這裡是倒序存放的，分別對應 ccc、bb、a 的長度。
00 表示沒有為空的欄位
00 00 10 00 2c 為記錄頭
00 00 00 2b 68 00 沒有申明主鍵，維護內部 ID
00 00 00 00 06 05 事務ID
80 00 00 00 32 01 10 回滾指標
61 第一列 a 的值
62 62 第二列 bb 的值
62 62 20 20 20 20 20 20 20 20 第三列 bb 的值，固定長度 char(10) 以20進行填充
63 63 63 第四列 ccc 的值

//第二行記錄
03 01 為變長欄位長度列表，這裡是倒序存放的，分別對應 fff、a 的長度，第二列位空。
06 轉換為二進位制為 00000110 表示第二列和第三列為空
00 00 20 ff 98 為記錄頭
00 00 00 2b 68 01 沒有申明主鍵，維護內部 ID
00 00 00 00 06 06 事務ID
80 00 00 00 32 01 10 回滾指標
64 第一列 d 的值
65 65 65 第四列 fff 的值


複製程式碼

到此，我們瞭解了一個資料行是怎麼儲存的，然而資料行並不是儲存引擎管理的最小儲存單位，索引只能夠幫助我們定位到某個資料頁，每一次磁碟讀寫的最小單位為也是資料頁，而一個資料頁記憶體儲了多個資料行，我們需要了解資料頁的內部結構才能知道儲存引擎怎麼定位到某一個資料行。InnoDB 的資料頁由以下 7 個部分組成：

• 檔案頭（File Header） 固定 38 個位元組 （頁的位置，上一頁下一頁位置，checksum , LSN）
• 資料頁頭（ Page Header）固定 56 個位元組 包含slot數目，可重用空間起始地址，第一個記錄地址，記錄數，最大事務ID等
• 虛擬的最大最小記錄 （Infimum + Supremum Record）
• 使用者記錄 （User Records） 包含已經刪除的記錄以連結串列的形式構成可重用空間
• 待分配空間 （Free spaces） 未分配的空間
• 頁目錄 （Page Directory） slot 資訊，下面單獨介紹
• 檔案尾 （File Trailer） 固定8個位元組,用來保證頁的完整性

頁目錄裡維護多個 slot ，一個 slot 包含多個行記錄。每個 slot 佔 2 個位元組，記錄這個 slot 裡的行記錄相對頁初始位置的偏移量。由於索引只能定位到資料頁，而定位到資料頁內的行記錄還需要在記憶體中進行二分查詢，而這個二分查詢就需要藉助 slot 資訊，先找到對應的 slot ，然後在 slot 內部通過資料行中記錄頭裡的下一個記錄地址進行遍歷。每一個 slot 可以包含 4 到 8 個資料行。如果沒有 slot 輔助，連結串列本身是無法進行二分查詢的。

排序

排序有好多種演算法來實現，在 MySQL 中經常會帶上一個 limit ,表示從排序後的結果集中取前 100 條，或者取第 n 條到第 m 條，要實現排序，我們需要先根據查詢條件獲取結果集，然後在記憶體中對這個結果集進行排序，如果結果集數量特別大，還需要將結果集寫入到多個檔案裡，然後單獨對每個檔案裡的資料進行排序，然後在檔案之間進行歸併，排序完成後在進行 limit 操作。沒錯，這個就是 MySQL 實現排序的方式，前提是排序的欄位沒有索引。

CREATE TABLE `person` (
  `id` int(11) NOT NULL,
  `city` varchar(16) NOT NULL,
  `name` varchar(16) NOT NULL,
  `age` int(11) NOT NULL,
  `addr` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `city` (`city`)
) ENGINE=InnoDB;

select city,name,age from person where city='武漢' order by name limit 100  ;

複製程式碼

使用 explain 發現該語句會使用 city 索引，並且會有 filesort . 我們分析下該語句的執行流程

• 1.初始化 sortbuffer ，用來存放結果集
• 2.找到 city 索引，定位到 city 等於武漢的第一條記錄，獲取主鍵索引ID
• 3.根據 ID 去主鍵索引上找到對應記錄，取出 city,name,age 欄位放入 sortbuffer
• 4.在 city 索引取下一個 city 等於武漢的記錄的主鍵ID
• 5.重複上面的步驟，直到所有 city 等於武漢的記錄都放入 sortbuffer
• 6.對 sortbuffer 裡的資料根據 name 做快速排序
• 7.根據排序結果取前面 1000 條返回

這裡是查詢 city,name,age 3個欄位，比較少，如果查詢的欄位較多，則多個列如果都放入 sortbuffer 將佔有大量記憶體空間，另一個方案是隻區出待排序的欄位和主鍵放入 sortbuffer 這裡是 name 和 id ,排序完成後在根據 id 取出需要查詢的欄位返回，其實就是時間換取空間的做法，這裡通過 max_length_for_sort_data 引數控制，是否採用後面的方案進行排序。

另外如果 sortbuffer 裡的條數很多，同樣會佔有大量的記憶體空間，可以通過引數 sort_buffer_size 來控制是否需要藉助檔案進行排序，這裡會把 sortbuffer 裡的資料放入多個檔案裡，用歸併排序的思路最終輸出一個大的檔案。

以上方案主要是 name 欄位沒有加上索引，如果 name 欄位上有索引，由於索引在構建的時候已經是有序的了，所以就不需要進行額外的排序流程只需要在查詢的時候查出指定的條數就可以了，這將大大提升查詢速度。我們現在加一個 city 和 name 的聯合索引。

alter table person add index city_user(city, name);
複製程式碼

這樣查詢過程如下：

• 1.根據 city,name 聯合索引定位到 city 等於武漢的第一條記錄，獲取主鍵索引ID
• 2.根據 ID 去主鍵索引上找到對應記錄，取出 city,name,age 欄位作為結果集返回
• 3.繼續重複以上步驟直到 city 不等於武漢，或者條數大於 1000

由於聯合所以在構建索引的時候，在 city 等於武漢的索引節點中的資料已經是根據 name 進行排序了的，所以這裡只需要直接查詢就可，另外這裡如果加上 city, name, age 的聯合索引，則可以用到索引覆蓋，不行到主鍵索引上進行回表。

總結一下，我們在有排序操作的時候，最好能夠讓排序欄位上建有索引，另外由於查詢第一百萬條開始的一百條記錄，需要過濾掉前面一百萬條記錄，即使用到索引也很慢，所以可以根據 ID 來進行區分，分頁遍歷的時候每次快取上一次查詢結果最後一條記錄的 id ， 下一次查詢加上 id > xxxx limit 0,1000 這樣可以避免前期掃描到的結果被過濾掉的情況。

InnoDB 儲存模型

InnoDB 通過一些列後臺執行緒將相關操作進行非同步處理，如下圖所示，同時藉助緩衝池來減小 CPU 和磁碟速度上的差異。當查詢的時候會先通過索引定位到對應的資料頁，然後檢測資料頁是否在緩衝池內，如果在就直接返回，如果不在就去聚簇索引中通過磁碟 IO 讀取對應的資料頁並放入緩衝池。一個資料頁會包含多個資料行。快取池通過 LRU 演算法對資料頁進行管理，也就是最頻繁使用的資料頁排在列表前面，不經常使用的排在隊尾，當緩衝池滿了的時候會淘汰掉隊尾的資料頁。從磁碟新讀取到的資料頁並不會放在佇列頭部而是放在中間位置，這個中間位置可以通過引數進行修。緩衝池也可以設定多個例項，資料頁根據雜湊演算法決定放在哪個緩衝池。

InnoDB 在更新資料的時候會採用 WAL 技術，也就是 Write Ahead Logging ，這個日誌就是 redolog 用來保證資料庫當機後可以通過該檔案進行恢復。這個檔案一般只會順序寫，只有在資料庫啟動的時候才會讀取 redolog 檔案看是否需要進行恢復。該檔案記錄了對某個資料頁的物理操作，例如某個 sql 把某一行的某個列的值改為 10 ，對應的 redolog 檔案格式可能為：把第5個資料頁中偏移量為99的位置寫入一個值 10 。redolog 不是無限大的，他的大小是可以配置的，並且是迴圈使用的，例如配置大小為 4G ，一共 4 個檔案，每個檔案 1G 。 首先從第一個檔案開始順序寫，寫到第四個檔案後在從第一個檔案開始寫，類似一個環，用一個後臺執行緒把 redolog 裡的資料同步到聚簇索引上的資料頁上。寫入 redolog 的時候不能將沒有同步到資料頁上的記錄覆蓋，如果碰到這種情況會停下來先進行資料頁同步然後在繼續寫入 redolog 。另外執行更新操作的時候，會先更新緩衝池裡的資料頁，然後寫入 redolog ， 這個時候真正儲存資料的地方還沒有更新，也就是說這時候緩衝池中的資料頁和磁碟不一致，這種資料頁稱為髒頁，當髒頁由於記憶體不足或者其他原因需要丟棄的時候，一定要先將該髒頁對應的redolog 重新整理到磁碟裡的真實資料頁，不然下次查詢的時候由於 redolog 沒有同步到磁碟，而查詢直接通過索引定位到資料頁就會查詢出髒資料。

更新的時候先從磁碟或者緩衝池中讀取對應的資料頁，然後對資料頁裡的資料進行更改並生成 redolog 到對應的緩衝池（redolog buffer）進行快取，當事務提交的時候將快取寫入到 redolog 的物理磁碟檔案上。這裡由於作業系統的檔案寫入 InnoDB 並沒有使用 O_DIRECT 直接寫入到檔案，為了保證效能而是先寫入作業系統的快取，之後在進行 flush ，所以事務提交的時候 InnoDB 需要在呼叫一次 fsync 的系統呼叫來確保資料落盤。為了提高效能 InnoDB 可以通過引數 innodb_flush_log_at_trx_commit 來控制事務提交時是否強制刷盤。預設為 1 ，事務每次提交都需要呼叫 fsync 進行刷盤，0 表示事務提交的時候不會呼叫  redolog 的檔案寫入，通過後臺執行緒每秒同步一次，2 表示事務提交的時候會寫入檔案但是隻保證寫入作業系統快取，不進行 fsync 操作。 redolog 檔案只會順序寫，所以磁碟操作效能不會太慢，所以建議生產環境都設定為 １ ，以防止資料庫當機導致資料丟失。

在執行更新邏輯的時候還會寫入另外一個日誌：undolog 。這個檔案儲存在共享表空間中，也就是即使開啟了 innodb_file_per_table 引數，所有的表的 undolog 都儲存在同一個檔案裡。該檔案主要用來做事務回滾和 MVCC 。undolog 是邏輯日誌，也就是他不是記錄的將物理的資料頁恢復到之前的狀態，而是記錄的和原 sql 相反的 sql , 例如 insert 對應 delete , delete 對應 insert ，update 對應另外一個 update 。事務回滾很好理解，執行相反的操作回滾到之前的狀態，而 MVCC 是指映象讀，當一個事務需要查詢某條記錄，而該記錄已經被其他事務修改，但該事務還沒提交，而當前事務可以通過 undolog 計算到之前的值。這裡我們只需要知道和 redolog 一樣， undolog 也是需要在執行 update 語句的時候在事務提交前需要寫入到檔案的。另外 undolog 的寫入也會有對應的 redolog ，因為 undolog 也需要持久化，通過 WAL 可以提高效率。這裡可以總結下，在事務提交的時候要保證 redolog 寫入到檔案裡，而這個 redolog 包含 主鍵索引上的資料頁的修改，以及共享表空間的回滾段中 undolog 的插入。 另外 undolog 的清理通過一個後臺執行緒定時處理，清理的時候需要判斷該 undolog 是否所有的事務都不會用到。

熟悉 MySQL 的都知道，他通過 binlog 來進行高可用，也就是通過 binlog 來將資料同步到叢集內其他的 MySQL 例項。binlog 和 redolog 的區別是，他是在儲存引擎上層 Server 層寫入的，他記錄的是邏輯操作，也就是對應的 sql ,而 redolog 記錄的底層某個資料頁的物理操作，redolog 是迴圈寫的，而binlog 是追加寫的，不會覆蓋以前寫的資料。而binlog 也需要在事務提交前寫入檔案。binlog 的寫入頁需要通過 fsync 來保證落盤，為了提高 tps ，MySQL 可以通過引數  sync_binlog 來控制是否需要同步刷盤，該策略會影響當主庫當機後備庫資料可能並沒有完全同步到主庫資料。由於事務的原子性，需要保證事務提交的時候 redolog 和 binlog 都寫入成功，所以 MySQL 執行層採用了兩階段提交來保證 redolog 和 binlog 都寫入成功後才 commit，如果一方失敗則會進行回滾。

下面我們理一下一條 update 語句的執行過程：

update person set age = 30 where id = 1;
複製程式碼

• 1.分配事務 ID ，開啟事務，獲取鎖，沒有獲取到鎖則等待。
• 2.執行器先通過儲存引擎找到 id = 1 的資料頁，如果緩衝池有則直接取出，沒有則去主鍵索引上取出對應的資料頁放入緩衝池。
• 3.在資料頁內找到 id = 1 這行記錄，取出，將 age 改為 30 然後寫入記憶體
• 4.生成 redolog undolog 到記憶體，redolog 狀態為 prepare
• 5.將 redolog undolog 寫入檔案並呼叫 fsync
• 6.server 層生成 binlog 並寫入檔案呼叫 fsync
• 7.事務提交，將 redolog 的狀態改為 commited 釋放鎖

鎖

資料庫使用鎖是為了對共享資源進行併發訪問控制，從而保證資料的完整性和一致性。InnoDB 中鎖的最小粒度為行，和 jdk 中的 ReadWriteLock 一樣，InnoDB提供了共享鎖和排他鎖，分別用來讀和寫。共享鎖之間可以相容，其他都互斥。根據加鎖的範圍，可以分為：全域性鎖、表級鎖、行鎖。全域性鎖會把整個資料庫例項加鎖，命令為 flush tables withs read lock ， 將使資料庫處於只讀狀態，其他資料寫入和修改表結構等語句會阻塞，一般在備庫上做全域性備份使用。而表級鎖有兩種，一種是表鎖，命令為 lock table with read/write ，和讀寫鎖一樣，另外一種是後設資料鎖，也叫意向鎖，不需要顯示申明，當執行修改表結構，加索引的時候會自動加後設資料寫鎖，對錶進行增刪改查的時候會加後設資料讀鎖。這樣當兩條修改語句的事務之間後設資料鎖都是讀鎖不互斥，但是修改表結構的時候執行更新由於互斥就需要阻塞。還有一種行級鎖稱為間隙鎖，他鎖定的是兩條記錄之間的間隙，防止其他事務往這個間隙插入資料，間隙鎖是隱式鎖，是儲存引擎自己加上的。

非鎖定讀

普通的 select 操作都是非鎖定讀，如果存在事務衝突，會利用 undolog 獲取新事務操作之前的映象返回，在讀已提交的隔離級別下，會獲取新事務修改前的最新的一份已經提交的資料，而在可重複讀的隔離級別下，會讀取該事務開始時的資料版本。當有多個事務併發操作同一行記錄時，該記錄會同時存在多個 undolog ，每個 undolog 就是一個版本，這種模式稱為多版本併發控制（MVCC） ，該模式能夠極大的提高資料庫的效能，想一想，如果基於鎖來控制的話，當對某個記錄進行修改的時候，另一個事務將需要等待，不管他是要讀取還是寫入，MVCC 允許寫入的時候還能夠進行讀操作，這對大部分都是查詢操作的應用來說極大的提高了 tps 。

鎖定讀

有時候我們在查詢的時候需要顯示的給記錄加鎖來保證一致性，select for update 將對掃描到的記錄加上排他鎖，而 select in share lock 將對掃描的記錄加上共享鎖。這兩個語句必須在一個事物內，也就是需要顯示開啟事物，begin transaction; 當事物提交的時候會釋放鎖。具體加鎖的邏輯我們後面在分析。另外所有的鎖定讀都是當前讀，也就是讀取當前記錄的最新版本，不會利用 undolog 讀取映象。另外所有的 insert、update、delete 操作也是當前讀，update、delete 會在更新之前進行一次當前讀，然後加鎖，而 insert 因為會觸發唯一索引檢測，也會包含一個當前讀。

自增長鎖：

在主鍵設定為自增長的情況下，該表會維護一個計數器，每個插入操作都會先獲取這個計數器的當前值，然後加 1 作為新的主鍵，顯然這個計數器是一個共享變數需要加排他鎖，而這個鎖不需要等到事物提交後才釋放，他在 sql 語句插入完成後就會釋放，新版本的 innoDB 採用互斥量來實現提高了插入速度。

鎖的問題

• 髒讀
• 不可重複讀
• 丟失更新
• 死鎖和熱點

髒讀是指事務A對某個資料頁進行了更改，但是並沒有提交，這個資料就成為髒資料，這裡稍微和上面提到的髒頁做下區分，髒頁是指記憶體中已經更改但是還沒有重新整理到磁碟的資料，髒頁是正常的，而髒讀是指一個事物讀取了另外一個事物沒有提交的資料，如果另外一個資料對這個資料又進行了更改，則出現資料一致性，髒讀違背了資料庫的隔離性。髒讀目前只能出現在讀未提交這個隔離級別下，目前 MySQL 預設的隔離級別為可重複讀。

不可重複讀是指一個事務先後兩次讀取同一條記錄的結果不一樣，因為第二次讀取的時候可能其他事務已經進行更改並提交，不可重複讀只發生在隔離級別為讀未提交和讀已提交裡。

丟失更新是指兩個事務同時更新某一條記錄，導致其中一個事務更新失效，理論上任何一個隔離級別都不會發生丟失更新，因為更新的時候會加上排他鎖，但是應用中卻經常發生，例如一個計數器應用，事務A查詢計數器的值 v=5,在記憶體中加 1 寫入到資料庫，在寫入之前另外一個事務讀取到計數器的值 v=5 ，然後加 1 寫入資料庫，這樣本來應該為 7 ， 現在卻是 6 ，這是因為 我們是先讀取在寫入，而讀取和寫入對資料庫而言是兩個操作，並不是一個原子操作，這裡可以通過把查詢的記錄加上排他鎖 select for update 來防止丟失更新現象。當然這裡直接將 sql 改為 v = v + 1 也可以。

死鎖是指兩個或兩個以上事務因爭奪資源而互相等待的情況，InnoDB 提供了死鎖檢測和超時機制來防止死鎖的影響，死鎖檢測是非常耗 CPU 的，當很多個事務同時競爭同一個資源的時候，例如搶購的時候扣商品份額，或者支付的時候所有的訂單都會用到一個公共賬戶，同一個資源競爭的事務越多，死鎖檢測越耗 CPU 。為了減少這種情況的影響，建議儘量在業務層減少熱點的產生，例如將熱點賬戶拆分成若個個同樣功能的賬戶，萬一發生高併發，建議在應用層做限流或者排隊，當然也可以在資料庫層做排隊，這個需要修改資料庫原始碼。

加鎖的流程

InnoDB的加鎖過程比較複雜，大致可以記住一個原則是：將所有掃描到的記錄都加鎖，範圍查詢會加間隙鎖，然後加鎖過程按照兩階段鎖 2PL 來實現，也就是先加鎖，然後所有的鎖在事物提交的時候釋放。怎麼加鎖和資料庫的隔離級別有關，然而我們一般很少更改 MySQL 的隔離級別，所以下面我們均按照可重複讀的隔離級別進行分析，另外一個因素是查詢條件中是否包含索引，是主鍵索引還是普通索引，是否是唯一索引等。我們以下面這條 sql 語句來分析加鎖過程。

select * from trade_order where order_no = '201912102322' for update; 
複製程式碼

• order_no 是主鍵索引 ，這種情況將在主鍵索引上的 order_no = '201912102322' 這條記錄上加排他鎖。
• order_no 是普通索引，並且是唯一索引 將會對 普通索引上對應的一套記錄加排他鎖，對主鍵索引上對應的記錄加排他鎖
• order_no 是普通索引，並且不是唯一索引 將會對 普通索引上 order_no = '201912102322' 一條或者多條記錄加鎖，並且對這些記錄對應的主鍵索引上的記錄加鎖。這裡除了加上行鎖外，還會加上間隙鎖，防止其他事物插入 order_no = '201912102322' 的記錄，然而如果是唯一索引就不需要間隙鎖，行鎖就可以。

• order_no 上沒有索引，innoDB 將會在主鍵索引上全表掃描，這裡並沒有加表鎖，而是將所有的記錄都會加上行級排他鎖，而實際上 innoDB 內部做了優化，當掃描到一行記錄後發現不匹配就會把鎖給釋放，當然這個違背了 2PL 原則在事務提交的時候釋放。這裡除了對記錄進行加鎖，還會對每兩個記錄之間的間隙加鎖，所以最終將會儲存所有的間隙鎖和 order_no = '201912102322' 的行鎖。

order_no = '201912102322' 這條記錄不存在的情況下，如果order_no 是主鍵索引，則會加一個間隙鎖，而這個間隙是主鍵索引中 order_no 小於 201912102322 的第一條記錄到大於 201912102322 的第一條記錄。試想一下如果不加間隙鎖，如果其他事物插入了一條 order_no = '201912102322' 的記錄，由於 select for update 是當前讀，即使上面那個事物沒有提交，如果在該事物中重新查詢一次就會發生幻讀。

如果沒有索引，則對掃描到的所有記錄和間隙都加鎖，如果不匹配行鎖將會釋放只剩下間隙鎖。回憶一下上面講的資料頁的結果中又一個最大記錄和最小記錄，Infimum 和 Supremum Record，這兩個記錄在加間隙鎖的時候就會用到。

事務

InnoDB 儲存引擎的事務需完全符合 ACID 特性。下面我們一起看下 InnoDB 做了哪些事情。

• 原子性 : 是指一個事務內的所有操作要麼全部成功要麼全部失敗，資料庫中將 redolog 和 binlog 的寫入採用兩階段提交就是為了保證事務的原子性。另外由於 InnodDB 是按頁進行儲存的，每個頁大小為 16kb 而作業系統的一般以 4KB 為一頁進行讀取，所以可能出現一個 InnoDB 的資料頁只寫了一部分的情況。而 InnoDB 為了防止這種情況的發生採用雙寫機制，除了寫入磁碟上的資料頁還會在共享空間中寫入。而 redolog 按塊儲存，每個塊 512 位元組，正好和扇區大小一樣所以，可以保證原子性，不需要進行雙寫。

• 一致性 ：保證磁碟和快取的資料一致，binlog 資料和 主庫中的資料一致。

• 隔離性 ： 預設為可重複讀，採用 undolog 來實現。

• 永續性 ： 事務一旦提交，其結果就是永久的，redolog 需要在事務提交前進行刷盤，磁碟採用 RAID 等。

微信 pigpdong 公眾號 lonelysnow