[[423960]]

圖片來(lái)自 包圖網(wǎng)

大概就是，在線上執(zhí)行一條 update 語(yǔ)句修改數(shù)據(jù)庫(kù)數(shù)據(jù)的時(shí)候，where 條件沒(méi)有帶上索引，導(dǎo)致業(yè)務(wù)直接崩了，被老板教訓(xùn)了一波。

這次我們就來(lái)看看：

• 為什么會(huì)發(fā)生這種的事故?
• 又該如何避免這種事故的發(fā)生?

說(shuō)個(gè)前提，接下來(lái)說(shuō)的案例都是基于 InnoDB 存儲(chǔ)引擎，且事務(wù)的隔離級(jí)別是可重復(fù)讀。

為什么會(huì)發(fā)生這種的事故?

InnoDB 存儲(chǔ)引擎的默認(rèn)事務(wù)隔離級(jí)別是「可重復(fù)讀」，但是在這個(gè)隔離級(jí)別下，在多個(gè)事務(wù)并發(fā)的時(shí)候，會(huì)出現(xiàn)幻讀的問(wèn)題。

所謂的幻讀是指在同一事務(wù)下，連續(xù)執(zhí)行兩次同樣的查詢語(yǔ)句，第二次的查詢語(yǔ)句可能會(huì)返回之前不存在的行。

因此 InnoDB 存儲(chǔ)引擎自己實(shí)現(xiàn)了行鎖，通過(guò) next-key 鎖(記錄鎖和間隙鎖的組合)來(lái)鎖住記錄本身和記錄之間的“間隙”，防止其他事務(wù)在這個(gè)記錄之間插入新的記錄，從而避免了幻讀現(xiàn)象。

當(dāng)我們執(zhí)行 update 語(yǔ)句時(shí)，實(shí)際上是會(huì)對(duì)記錄加獨(dú)占鎖(X 鎖)的，如果其他事務(wù)對(duì)持有獨(dú)占鎖的記錄進(jìn)行修改時(shí)是會(huì)被阻塞的。

另外，這個(gè)鎖并不是執(zhí)行完 update 語(yǔ)句就會(huì)釋放的，而是會(huì)等事務(wù)結(jié)束時(shí)才會(huì)釋放。

在 InnoDB 事務(wù)中，對(duì)記錄加鎖帶基本單位是 next-key 鎖，但是會(huì)因?yàn)橐恍l件會(huì)退化成間隙鎖，或者記錄鎖。加鎖的位置準(zhǔn)確的說(shuō)，鎖是加在索引上的而非行上。

比如，在 update 語(yǔ)句的 where 條件使用了唯一索引，那么 next-key 鎖會(huì)退化成記錄鎖，也就是只會(huì)給一行記錄加鎖。

這里舉個(gè)例子，這里有一張數(shù)據(jù)庫(kù)表，其中 id 為主鍵索引。

假設(shè)有兩個(gè)事務(wù)的執(zhí)行順序如下：

可以看到，事務(wù) A 的 update 語(yǔ)句中 where 是等值查詢，并且 id 是唯一索引，所以只會(huì)對(duì) id = 1 這條記錄加鎖，因此，事務(wù) B 的更新操作并不會(huì)阻塞。

但是，在 update 語(yǔ)句的 where 條件沒(méi)有使用索引，就會(huì)全表掃描，于是就會(huì)對(duì)所有記錄加上 next-key 鎖(記錄鎖 + 間隙鎖)，相當(dāng)于把整個(gè)表鎖住了。

假設(shè)有兩個(gè)事務(wù)的執(zhí)行順序如下：

可以看到，這次事務(wù) B 的 update 語(yǔ)句被阻塞了。

這是因?yàn)槭聞?wù) A的 update 語(yǔ)句中 where 條件沒(méi)有索引列，所有記錄都會(huì)被加鎖，也就是這條 update 語(yǔ)句產(chǎn)生了 4 個(gè)記錄鎖和 5 個(gè)間隙鎖，相當(dāng)于鎖住了全表。

因此，當(dāng)在數(shù)據(jù)量非常大的數(shù)據(jù)庫(kù)表執(zhí)行 update 語(yǔ)句時(shí)，如果沒(méi)有使用索引，就會(huì)給全表的加上 next-key 鎖， 那么鎖就會(huì)持續(xù)很長(zhǎng)一段時(shí)間，直到事務(wù)結(jié)束。

而這期間除了 select ... from 語(yǔ)句，其他語(yǔ)句都會(huì)被鎖住不能執(zhí)行，業(yè)務(wù)會(huì)因此停滯，接下來(lái)等著你的，就是老板的挨罵。

那 update 語(yǔ)句的 where 帶上索引就能避免全表記錄加鎖了嗎?并不是。

關(guān)鍵還得看這條語(yǔ)句在執(zhí)行過(guò)程中，優(yōu)化器最終選擇的是索引掃描，還是全表掃描，如果走了全表掃描，就會(huì)對(duì)全表的記錄加鎖了。

又該如何避免這種事故的發(fā)生?

我們可以將 MySQL 里的 sql_safe_updates 參數(shù)設(shè)置為 1，開(kāi)啟安全更新模式。

官方的解釋：

If set to 1, MySQL aborts UPDATE or DELETE statements that do not use a key in the WHERE clause or a LIMIT clause. (Specifically, UPDATE statements must have a WHERE clause that uses a key or a LIMIT clause, or both. DELETE statements must have both.) This makes it possible to catch UPDATE or DELETE statements where keys are not used properly and that would probably change or delete a large number of rows. The default value is 0.

大致的意思是，當(dāng) sql_safe_updates 設(shè)置為 1 時(shí)，update 語(yǔ)句必須滿足如下條件之一才能執(zhí)行成功：

• 使用 where，并且 where 條件中必須有索引列。
• 使用 limit。
• 同時(shí)使用 where 和 limit，此時(shí) where 條件中可以沒(méi)有索引列。

delete 語(yǔ)句必須滿足如下條件之一才能執(zhí)行成功：

• 使用 where，并且 where 條件中必須有索引列。
• 同時(shí)使用 where 和 limit，此時(shí) where 條件中可以沒(méi)有索引列。

如果 where 條件帶上了索引列，但是優(yōu)化器最終掃描選擇的是全表，而不是索引的話，我們可以使用 force index([index_name]) 可以告訴優(yōu)化器使用哪個(gè)索引，以此避免有幾率鎖全表帶來(lái)的隱患。

總結(jié)

不要小看一條 update 語(yǔ)句，在生產(chǎn)機(jī)上使用不當(dāng)可能會(huì)導(dǎo)致業(yè)務(wù)停滯，甚至崩潰。

當(dāng)我們要執(zhí)行 update 語(yǔ)句的時(shí)候，確保 where 條件中帶上了索引列，并且在測(cè)試機(jī)確認(rèn)該語(yǔ)句是否走的是索引掃描，防止因?yàn)閽呙枞恚鴮?duì)表中的所有記錄加上鎖。

我們可以打開(kāi) MySQL 里的 sql_safe_updates 參數(shù)，這樣可以預(yù)防 update 操作時(shí) where 條件沒(méi)有帶上索引列。

如果發(fā)現(xiàn)即使在 where 條件中帶上了列索引列，優(yōu)化器走的還是全標(biāo)掃描，這時(shí)我們就要使用 force index([index_name]) 可以告訴優(yōu)化器使用哪個(gè)索引。

這次就說(shuō)到這啦，下次要小心點(diǎn)，別再被老板挨罵啦。

作者：小林

編輯：陶家龍

出處：轉(zhuǎn)載自公眾號(hào)小林coding(ID：CodingLin)