溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
本篇內容介紹了“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鎖；
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)的意思是通過兩個索引獲取交集。
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 字段，通過主鍵去更新。
刪除 idx_status 索引或者建一個包含這倆列的聯合索引；
將MySQL優化器的index merge優化關閉。
“MySQL優化index merge引起的死鎖怎么解決”的內容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業相關的知識可以關注億速云網站，小編將為大家輸出更多高質量的實用文章！
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
