MySQL優(yōu)化index?merge引起的死鎖怎么解決

本篇內(nèi)容介紹了“MySQL優(yōu)化index merge引起的死鎖怎么解決”的有關(guān)知識(shí)，在實(shí)際案例的操作過(guò)程中，不少人都會(huì)遇到這樣的困境，接下來(lái)就讓小編帶領(lǐng)大家學(xué)習(xí)一下如何處理這些情況吧！希望大家仔細(xì)閱讀，能夠?qū)W有所成！

背景

生產(chǎn)環(huán)境出現(xiàn)死鎖流水，通過(guò)查看死鎖日志，看到造成死鎖的是兩條一樣的update語(yǔ)句（只有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;

一開(kāi)始比較費(fèi)解，通過(guò)大量查詢跟學(xué)習(xí)后，分析出了死鎖形成的具體原理，特分享給大家，希望能幫助到遇到同樣問(wèn)題的朋友。

因?yàn)?code>MySQL知識(shí)點(diǎn)較多，這里對(duì)很多名詞不進(jìn)行過(guò)多介紹，有興趣的朋友，可以后續(xù)進(jìn)行專項(xiàng)深入學(xué)習(xí)。

死鎖日志

*** (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)

簡(jiǎn)要分析下上邊的死鎖日志：

• 1、第一塊內(nèi)容（第1行到第9行）中，第6行為事務(wù)(1)執(zhí)行的SQL語(yǔ)句，第7和第8行意思為事務(wù)(1)在等待 idx_status 索引上的X鎖；

• 2、第二塊內(nèi)容（第11行到第19行）中，第16行為事務(wù)(2)執(zhí)行的SQL語(yǔ)句，第17和第18行意思為事務(wù)(2)持有 idx_status 索引上的X鎖；

• 3、第三塊內(nèi)容（第21行到第23行）的意思為，事務(wù)(2)在等待 PRIMARY 索引上的X鎖。（but not gap指不是間隙鎖）

• 4、最后一句的意思即為，MySQL將事務(wù)(1)進(jìn)行了回滾操作。

表結(jié)構(gòu)

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

通過(guò)表結(jié)構(gòu)可以看出，trans_id 列上有一個(gè)唯一索引uniq_trans_id ，status 列上有一個(gè)普通索引idx_status ，id列為主鍵索引 PRIMARY 。

InnoDB引擎中有兩種索引：

• 聚簇索引： 將數(shù)據(jù)存儲(chǔ)與索引放到了一塊，索引結(jié)構(gòu)的葉子節(jié)點(diǎn)保存了行數(shù)據(jù)。

• 輔助索引： 輔助索引葉子節(jié)點(diǎn)存儲(chǔ)的是主鍵值，也就是聚簇索引的鍵值。

主鍵索引 PRIMARY 就是聚簇索引，葉子節(jié)點(diǎn)中會(huì)保存行數(shù)據(jù)。uniq_trans_id 索引和idx_status 索引為輔助索引，葉子節(jié)點(diǎn)中保存的是主鍵值，也就是id列值。

當(dāng)我們通過(guò)輔助索引查找行數(shù)據(jù)時(shí)，先通過(guò)輔助索引找到主鍵id，再通過(guò)主鍵索引進(jìn)行二次查找（也叫回表），最終找到行數(shù)據(jù)。

執(zhí)行計(jì)劃

通過(guò)看執(zhí)行計(jì)劃，可以發(fā)現(xiàn)，update語(yǔ)句用到了索引合并，也就是這條語(yǔ)句既用到了 uniq_trans_id 索引，又用到了 idx_status 索引，Using intersect(uniq_trans_id,idx_status)的意思是通過(guò)兩個(gè)索引獲取交集。

為什么會(huì)用 index_merge（索引合并）

MySQL5.0之前，一個(gè)表一次只能使用一個(gè)索引，無(wú)法同時(shí)使用多個(gè)索引分別進(jìn)行條件掃描。但是從5.1開(kāi)始，引入了 index merge 優(yōu)化技術(shù)，對(duì)同一個(gè)表可以使用多個(gè)索引分別進(jìn)行條件掃描。

如執(zhí)行計(jì)劃中的語(yǔ)句：

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

MySQL會(huì)根據(jù) trans_id = ‘38’這個(gè)條件，利用 uniq_trans_id 索引找到葉子節(jié)點(diǎn)中保存的id值；同時(shí)會(huì)根據(jù) status = 0這個(gè)條件，利用 idx_status 索引找到葉子節(jié)點(diǎn)中保存的id值；然后將找到的兩組id值取交集，最終通過(guò)交集后的id回表，也就是通過(guò) PRIMARY 索引找到葉子節(jié)點(diǎn)中保存的行數(shù)據(jù)。

這里可能很多人會(huì)有疑問(wèn)了，uniq_trans_id 已經(jīng)是一個(gè)唯一索引了，通過(guò)這個(gè)索引最終只能找到最多一條數(shù)據(jù)，那MySQL優(yōu)化器為啥還要用兩個(gè)索引取交集，再回表進(jìn)行查詢呢，這樣不是多了一次 idx_status 索引查找的過(guò)程么。我們來(lái)分析一下這兩種情況執(zhí)行過(guò)程。

第一種 只用uniq_trans_id索引 ：

• 根據(jù) trans_id = ‘38’查詢條件，利用uniq_trans_id 索引找到葉子節(jié)點(diǎn)中保存的id值；

• 通過(guò)找到的id值，利用PRIMARY索引找到葉子節(jié)點(diǎn)中保存的行數(shù)據(jù)；

• 再通過(guò) status = 0 條件對(duì)找到的行數(shù)據(jù)進(jìn)行過(guò)濾。

第二種 用到索引合并 Using intersect(uniq_trans_id,idx_status)：

• 根據(jù) trans_id = ‘38’ 查詢條件，利用 uniq_trans_id 索引找到葉子節(jié)點(diǎn)中保存的id值；

• 根據(jù) status = 0 查詢條件，利用 idx_status 索引找到葉子節(jié)點(diǎn)中保存的id值；

• 將1/2中找到的id值取交集，然后利用PRIMARY索引找到葉子節(jié)點(diǎn)中保存的行數(shù)據(jù)

上邊兩種情況，主要區(qū)別在于，第一種是先通過(guò)一個(gè)索引把數(shù)據(jù)找到后，再用其它查詢條件進(jìn)行過(guò)濾；第二種是先通過(guò)兩個(gè)索引查出的id值取交集，如果取交集后還存在id值，則再去回表將數(shù)據(jù)取出來(lái)。

當(dāng)優(yōu)化器認(rèn)為第二種情況執(zhí)行成本比第一種要小時(shí)，就會(huì)出現(xiàn)索引合并。（生產(chǎn)環(huán)境流水表中 status = 0 的數(shù)據(jù)非常少，這也是優(yōu)化器考慮用第二種情況的原因之一）。

為什么用了 index_merge 就死鎖了

上面簡(jiǎn)要畫(huà)了一下兩個(gè)update事務(wù)加鎖的過(guò)程，從圖中可以看到，在idx_status 索引和 PRIMARY （聚簇索引） 上都存在重合交叉的部分，這樣就為死鎖造成了條件。

如，當(dāng)遇到以下時(shí)序時(shí)，就會(huì)出現(xiàn)死鎖：

事務(wù)1等待事務(wù)2釋放鎖，事務(wù)2等待事務(wù)1釋放鎖，這樣就造成了死鎖。

MySQL檢測(cè)到死鎖后，會(huì)自動(dòng)回滾代價(jià)更低的那個(gè)事務(wù)，如上邊的時(shí)序圖中，事務(wù)1持有的鎖比事務(wù)2少，則MySQL就將事務(wù)1進(jìn)行了回滾。

解決方案

一、從代碼層面

• where 查詢條件中，只傳 trans_id ，將數(shù)據(jù)查詢出來(lái)后，在代碼層面判斷 status 狀態(tài)是否為0；

• 使用 force index(uniq_trans_id) 強(qiáng)制查詢語(yǔ)句使用 uniq_trans_id 索引；

• where 查詢條件后邊直接用 id 字段，通過(guò)主鍵去更新。

二、從MySQL層面

• 刪除 idx_status 索引或者建一個(gè)包含這倆列的聯(lián)合索引；

• 將MySQL優(yōu)化器的index merge優(yōu)化關(guān)閉。

“MySQL優(yōu)化index merge引起的死鎖怎么解決”的內(nèi)容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業(yè)相關(guān)的知識(shí)可以關(guān)注億速云網(wǎng)站，小編將為大家輸出更多高質(zhì)量的實(shí)用文章！