MySQL 優化 index merge引起的死鎖分析

2022-04-19 19:02:16

背景

生產環境出現死鎖流水，通過檢視死鎖紀錄檔，看到造成死鎖的是兩條一樣的update語句（只有where條件中的值不同），

如下：

UPDATE test_table SET `status` = 1 WHERE `trans_id` = 'xxx1' AND `status` = 0;
UPDATE test_table SET `status` = 1 WHERE `trans_id` = 'xxx2' AND `status` = 0;

一開始比較費解，通過大量查詢跟學習後，分析出了死鎖形成的具體原理，特分享給大家，希望能幫助到遇到同樣問題的朋友。

因為MySQL知識點較多，這裡對很多名詞不進行過多介紹，有興趣的朋友，可以後續進行專項深入學習。

死鎖紀錄檔

*** (1) TRANSACTION:
TRANSACTION 791913819, ACTIVE 0 sec starting index read, thread declared inside InnoDB 4999
mysql tables in use 3, locked 3
LOCK WAIT 4 lock struct(s), heap size 1184, 3 row lock(s)
MySQL thread id 462005230, OS thread handle 0x7f55d5da3700, query id 2621313306 x.x.x.x test_user Searching rows for update
UPDATE test_table SET `status` = 1 WHERE `trans_id` = 'xxx1' AND `status` = 0;
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 110 page no 39167 n bits 1056 index `idx_status` of table `test`.`test_table` trx id 791913819 lock_mode X waiting
Record lock, heap no 495 PHYSICAL RECORD: n_fields 2; compact format; info bits 0

*** (2) TRANSACTION:
TRANSACTION 791913818, ACTIVE 0 sec starting index read, thread declared inside InnoDB 4999
mysql tables in use 3, locked 3
5 lock struct(s), heap size 1184, 4 row lock(s)
MySQL thread id 462005231, OS thread handle 0x7f55cee63700, query id 2621313305 x.x.x.x test_user Searching rows for update
UPDATE test_table SET `status` = 1 WHERE `trans_id` = 'xxx2' AND `status` = 0;
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 110 page no 39167 n bits 1056 index `idx_status` of table `test`.`test_table` trx id 791913818 lock_mode X
Record lock, heap no 495 PHYSICAL RECORD: n_fields 2; compact format; info bits 0

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 110 page no 41569 n bits 88 index `PRIMARY` of table `test`.`test_table` trx id 791913818 lock_mode X locks rec but not gap waiting
Record lock, heap no 14 PHYSICAL RECORD: n_fields 30; compact format; info bits 0

*** WE ROLL BACK TRANSACTION (1)

簡要分析下上邊的死鎖紀錄檔：

1、第一塊內容（第1行到第9行）中，第6行為事務(1)執行的SQL語句，第7和第8行意思為事務(1)在等待 idx_status 索引上的X鎖；
2、第二塊內容（第11行到第19行）中，第16行為事務(2)執行的SQL語句，第17和第18行意思為事務(2)持有 idx_status 索引上的X鎖；
3、第三塊內容（第21行到第23行）的意思為，事務(2)在等待 PRIMARY 索引上的X鎖。（but not gap指不是間隙鎖）
4、最後一句的意思即為，MySQL將事務(1)進行了回滾操作。

表結構

CREATE TABLE `test_table` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`trans_id` varchar(21) NOT NULL,
`status` int(11) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uniq_trans_id` (`trans_id`) USING BTREE,
KEY `idx_status` (`status`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

通過表結構可以看出，trans_id 列上有一個唯一索引uniq_trans_id ，status 列上有一個普通索引idx_status ，id列為主鍵索引 PRIMARY 。

InnoDB引擎中有兩種索引：

聚簇索引： 將資料儲存與索引放到了一塊，索引結構的葉子節點儲存了行資料。
輔助索引： 輔助索引葉子節點儲存的是主鍵值，也就是聚簇索引的鍵值。

主鍵索引 PRIMARY 就是聚簇索引，葉子節點中會儲存行資料。uniq_trans_id 索引和idx_status 索引為輔助索引，葉子節點中儲存的是主鍵值，也就是id列值。

當我們通過輔助索引查詢行資料時，先通過輔助索引找到主鍵id，再通過主鍵索引進行二次查詢（也叫回表），最終找到行資料。

執行計劃

通過看執行計劃，可以發現，update語句用到了索引合併，也就是這條語句既用到了 uniq_trans_id 索引，又用到了 idx_status 索引，Using intersect(uniq_trans_id,idx_status)的意思是通過兩個索引獲取交集。

為什麼會用 index_merge（索引合併）

MySQL5.0之前，一個表一次只能使用一個索引，無法同時使用多個索引分別進行條件掃描。但是從5.1開始，引入了 index merge 優化技術，對同一個表可以使用多個索引分別進行條件掃描。

如執行計劃中的語句：

UPDATE test_table SET `status` = 1 WHERE `trans_id` = '38' AND `status` = 0 ;

MySQL會根據 trans_id = ‘38’這個條件，利用 uniq_trans_id 索引找到葉子節點中儲存的id值；同時會根據 status = 0這個條件，利用 idx_status 索引找到葉子節點中儲存的id值；然後將找到的兩組id值取交集，最終通過交集後的id回表，也就是通過 PRIMARY 索引找到葉子節點中儲存的行資料。

這裡可能很多人會有疑問了，uniq_trans_id 已經是一個唯一索引了，通過這個索引最終只能找到最多一條資料，那MySQL優化器為啥還要用兩個索引取交集，再回表進行查詢呢，這樣不是多了一次 idx_status 索引查詢的過程麼。我們來分析一下這兩種情況執行過程。

第一種只用uniq_trans_id索引：