降低鎖競爭 減少M(fèi)ySQL用戶等待時(shí)間
通過鎖機(jī)制，可以實(shí)現(xiàn)多線程同時(shí)對某個(gè)表進(jìn)行操作。如下圖所示，在某個(gè)時(shí)刻，用戶甲、用戶乙、用戶丙可能會同時(shí)或者先后(前面一個(gè)作業(yè)還沒有完成)對數(shù)據(jù)表A進(jìn)行查詢或者更新的操作。當(dāng)某個(gè)線程涉及到更新操作時(shí)，就需要獲得獨(dú)占的訪問權(quán)。在更新的過程中，所有其它想要訪問這個(gè)表的線程必須要等到其更新完成為止。此時(shí)就會導(dǎo)致鎖競爭的問題。從而導(dǎo)致用戶等待時(shí)間的延長。在這篇文章中，筆者將跟大家討論，采取哪些措施可以有效的避免鎖競爭，減少M(fèi)ySQL用戶的等待時(shí)間。
背景模擬：
為了更加清楚的說明這個(gè)問題，筆者先模擬一個(gè)日常的案例。通過案例大家來閱讀下面的內(nèi)容，可能條理會更加的清晰。現(xiàn)在MySQL數(shù)據(jù)庫遇到如上圖所示這種情況。
首先，用戶甲對數(shù)據(jù)表A發(fā)出了一個(gè)查詢請求。
然后，用戶乙又對數(shù)據(jù)表A發(fā)出了一個(gè)更新請求。此時(shí)用戶乙的請求只有在用戶甲的作業(yè)完成之后才能夠得到執(zhí)行。
***，用戶丙又對數(shù)據(jù)表A發(fā)出了一個(gè)查詢請求。在MySQL數(shù)據(jù)庫中，更新語句的優(yōu)先級要比查詢語句的優(yōu)先級高，為此用戶丙的查詢語句只有在用戶乙的更新作業(yè)完成之后才能夠執(zhí)行。而用戶乙的更新作業(yè)又必須在用戶甲的查詢語句完成之后才能夠執(zhí)行。此時(shí)就存在比較嚴(yán)重的鎖競爭問題。
現(xiàn)在數(shù)據(jù)庫工程師所要做的就是在數(shù)據(jù)庫設(shè)計(jì)與優(yōu)化過程中，采取哪些措施來降低這種鎖競爭的不利情況?
措施一：利用Lock Tables來提高更新速度
對于更新作業(yè)來說，在一個(gè)鎖定中進(jìn)行許多更新要比所有鎖定的更新要來得快。為此如果一個(gè)表更新頻率比較高，如超市的收銀系統(tǒng)，那么可以通過使用Lock Tables選項(xiàng)來提高更新速度。更新的速度提高了，那么與Select查詢作業(yè)的沖突就會明顯減少，鎖競爭的現(xiàn)象也能夠得到明顯的抑制。
措施二：將某個(gè)表分為幾個(gè)表來降低鎖競爭
如一個(gè)大型的購物超市，如沃爾瑪，其銷售紀(jì)錄表每天的更新操作非常的多。此時(shí)如果用戶在更新的同時(shí)，另外有用戶需要對其進(jìn)行查詢，顯然鎖競爭的現(xiàn)象會比較嚴(yán)重。針對這種情況，其實(shí)可以人為的將某張表分為幾個(gè)表。如可以為每一臺收銀機(jī)專門設(shè)置一張數(shù)據(jù)表。如此的話，各臺收銀機(jī)之間用戶的操作都是在自己的表中完成，相互之間不會產(chǎn)生干擾。在數(shù)據(jù)統(tǒng)計(jì)分析時(shí)，可以通過視圖將他們整合成一張表。
措施三：調(diào)整某個(gè)作業(yè)的優(yōu)先級
默認(rèn)情況下，在MySQL數(shù)據(jù)庫中，更新操作比Select查詢有更高的優(yōu)先級。如上圖所示，如果用戶乙先發(fā)出了一個(gè)查詢申請，然后用戶丙再發(fā)出一個(gè)更新請求。當(dāng)用戶甲的查詢作業(yè)完成之后，系統(tǒng)會先執(zhí)行誰的請求呢?注意，默認(rèn)情況下系統(tǒng)并不遵循先來后到的規(guī)則，即不會先執(zhí)行用戶乙的查詢請求，而是執(zhí)行用戶丙的更新進(jìn)程。這主要是因?yàn)椋逻M(jìn)程比查詢進(jìn)程具有更高的優(yōu)先級。
但是在有些特定的情況下，可能這種優(yōu)先級不符合企業(yè)的需求。此時(shí)數(shù)據(jù)庫管理員需要根據(jù)實(shí)際情況來調(diào)整語句的優(yōu)先級。如果確實(shí)需要的話，那么可以通過以下三種方式來實(shí)現(xiàn)。
一是通過LOW_PRIOITY屬性。這個(gè)屬性可以將某個(gè)特定的語句的優(yōu)先級降低。如可以調(diào)低某個(gè)特定的更新語句或者插入語句的優(yōu)先級。不過需要注意的是，這個(gè)屬性只有對特定的語句有用。即其作用域只針對某個(gè)特定的語句，而不會對全局造成影響。
二是通過HIGH_PRIOITY屬性。與通過LOW_PRIOITY屬性對應(yīng)，有一個(gè)HIGH_PRIOITY屬性。顧名思義，這個(gè)屬性可以用來提高某個(gè)特定的Select查詢語句的優(yōu)先級。如上面這個(gè)案例，在用戶丙的查詢語句中加入HIGH_PRIOITY屬性的話，那么用戶甲查詢完畢之后，會立即執(zhí)行用戶丙的查詢語句。等到用戶丙執(zhí)行完畢之后，才會執(zhí)行用戶乙的更新操作。可見，此時(shí)查詢語句的優(yōu)先級得到了提升。這里需要注意，跟上面這個(gè)屬性一樣，這個(gè)作用域也只限于特定的查詢語句。而不會對沒有加這個(gè)參數(shù)的其他查詢語句產(chǎn)生影響。也就是說，其他查詢語句如果沒有加這個(gè)屬性，那么其優(yōu)先級別仍然低于更新進(jìn)程。
三是通過Set LOW_PRIORIT_UPDATES=1選項(xiàng)。以上兩個(gè)屬性都是針對特定的語句，而不會造成全局的影響。如果現(xiàn)在數(shù)據(jù)庫管理員需要對某個(gè)連接來調(diào)整優(yōu)先級別，該如何實(shí)現(xiàn)呢?如上例，現(xiàn)在用戶需要將用戶丙連接的查詢語句的優(yōu)先級別提高，而不是每次查詢時(shí)都需要使用上面的屬性。此時(shí)就需要使用Set LOW_PRIORIT_UPDATES=1選項(xiàng)。通過這個(gè)選項(xiàng)可以制定具體連接中的所有更新進(jìn)程都是用比較低的優(yōu)先級。注意這個(gè)選項(xiàng)只針對特定的連接有用。對于其他的連接，就不適用。
四是采用Low_Priority_updates選項(xiàng)。上面談到的屬性，前面兩個(gè)針對特定的語句，后面一個(gè)是針對特定的連接，都不會對整個(gè)數(shù)據(jù)庫產(chǎn)生影響。如果現(xiàn)在需要在整個(gè)數(shù)據(jù)庫范圍之內(nèi)，降低更新語句的優(yōu)先級，是否可以實(shí)現(xiàn)?如上面這個(gè)案例，在不使用其他參數(shù)的情況下，就讓用戶丙的查詢語句比用戶乙的更新具有更先執(zhí)行?如果用戶有這種需求的話，可以使用Low_Priority_updates選項(xiàng)來啟動數(shù)據(jù)庫。采用這個(gè)選項(xiàng)啟動數(shù)據(jù)庫時(shí)，系統(tǒng)會給數(shù)據(jù)庫中所有的更新語句比較低的優(yōu)先級。此時(shí)用戶丙的查詢語句就會比用戶用戶乙的更新請求更早的執(zhí)行。而對于查詢作業(yè)來說，不存在鎖定的情況。為此用戶甲的查詢請求與用戶丙的查詢請求可以同時(shí)進(jìn)行。為此通過調(diào)整語句執(zhí)行的優(yōu)先級，可以有效的降低鎖競爭的情況。
可見，可以利用屬性或者選項(xiàng)來調(diào)整某條語句的優(yōu)先級。如現(xiàn)在有一個(gè)應(yīng)用，主要供用戶來進(jìn)行查詢。更新的操作一般都是有管理員來完成，并且對于用戶來說更新的數(shù)據(jù)并不敏感。此時(shí)基于用戶優(yōu)先的原則，可以考慮將查詢的優(yōu)先級別提高。如此的話，對于用戶來說，其遇到鎖競爭的情況就會比較少，從而可以縮短用戶的等待時(shí)間。在調(diào)整用戶優(yōu)先級時(shí)，需要考慮其調(diào)整的范圍。即只是調(diào)整特定的語句、還是調(diào)整特定的連接，又或者對整個(gè)數(shù)據(jù)庫生效。
措施四：對于混合操作的情況，可以采用特定的選項(xiàng)
有時(shí)候會遇到混合操作的作業(yè)，如即有更新操作又有插入操作又有查詢操作時(shí)，要根據(jù)特定的情況，采用特定的選項(xiàng)。如現(xiàn)在需要對數(shù)據(jù)表同時(shí)進(jìn)行插入和刪除的作業(yè)，此時(shí)如果能夠使用Insert Delayed選項(xiàng)，將會給用戶帶來很大的幫助。再如對同一個(gè)數(shù)據(jù)表執(zhí)行Select和Delete語句會有鎖競爭的情況。此時(shí)數(shù)據(jù)庫管理員也可以根據(jù)實(shí)際情況來選擇使用Delete Limint選項(xiàng)來解決所遇到速度問題。
通常情況下，鎖競爭與死鎖不同，并不會對數(shù)據(jù)庫的運(yùn)行帶來很大的影響。只是可能會延長用戶的等待時(shí)間。如果用戶并發(fā)訪問的機(jī)率并不是很高，此時(shí)鎖競爭的現(xiàn)象就會很少。那么采用上面的這些措施并不會帶來多大的收益。相反，如果用戶對某個(gè)表的并發(fā)訪問比較多，特別是不同的用戶會對表執(zhí)行查詢、更新、刪除、插入等混合作業(yè)，那么采取上面這些措施可以在很大程度上降低鎖沖突，減少用戶的等待時(shí)間。
【編輯推薦】
