MySQL優化index merge造成的死鎖怎麼解決

背景

生產環境出現死鎖流水，透過查看死鎖日誌，看到造成死鎖的是兩個相同的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鎖；

• 意思為：交易(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索引：

• 根據 trans_id = ‘38’查詢條件，利用uniq_trans_id 索引找到葉節點中已儲存的id值；

• 透過找到的id值，利用PRIMARY索引找到葉子節點中已儲存的行資料；

• 再透過 status = 0 條件對找到的行資料進行篩選。

第二個用到索引合併 Using intersect(uniq_trans_id,idx_status)：

• 根據 trans_id = ‘38’ 查詢條件，利用 uniq_trans_id 索引找出葉子節點中保存的id值;

• 根據 status = 0 查詢條件，利用 idx_status 索引找出葉子節點中保存的id值；

• 將1/2中找到的id值交集，然後利用PRIMARY索引找到葉子節點中儲存的行資料

##上邊兩種情況，主要差異在於，第一種是先透過一個索引把資料找到後，再用其它查詢條件進行過濾；第二種是先透過兩個索引查出的id值取交集，如果取交集後還存在id值，則再去回表將資料取出來。

當優化器認為第二種情況執行成本比第一種要小時，就會出現索引合併。 （生產環境流水錶中 

status = 0 的資料非常少，這也是最佳化器考慮用第二種情況的原因之一）。

為什麼用了 

index_merge 就死鎖了

上面簡單畫了一下兩個update事務加鎖的過程，從圖中可以看到，在

idx_status 索引和PRIMARY （叢集索引） 上都存在重疊交叉的部分，這樣就為死鎖造成了條件。

如，當遇到以下時序時，就會出現死鎖：

#交易1等待交易2釋放鎖定，事務2等待事務1釋放鎖，這樣就造成了死鎖。

MySQL偵測到死鎖後，會自動回滾代價更低的那個事務，如上邊的時序圖中，事務1持有的鎖定比事務2少，則MySQL就將事務1進行了復原.

解決方案

一、從程式碼層面

• #where 查詢條件中，只傳 

  trans_id ，將資料查詢出來後來，在程式碼層面判斷status 狀態是否為0;

##使用 
• force index(uniq_trans_id)

   強制查詢語句使用 uniq_trans_id 索引；

where 查詢條件後邊直接用id 字段，透過主鍵去更新。
二、從MySQL層面

刪除 
• idx_status

   索引或建立一個包含這兩個欄位的共同索引；

將MySQL最佳化器的
• index merge

  最佳化關閉。

  #

以上是MySQL優化index merge造成的死鎖怎麼解決的詳細內容。更多資訊請關注PHP中文網其他相關文章！