系統(tǒng)隱形殺手——阻塞與等待(SQL)

前言

應(yīng)用系統(tǒng)承載著大量的業(yè)務(wù)，隨之而來(lái)的是復(fù)雜的業(yè)務(wù)邏輯，在數(shù)據(jù)庫(kù)上的表現(xiàn)就是有著大量的不同種類(lèi)的SQL語(yǔ)句。

SQL語(yǔ)句執(zhí)行的快慢又與阻塞等待有著密不可分的原因。

系統(tǒng)慢可能有很多種原因，硬件資源不足，語(yǔ)句不優(yōu)化，結(jié)構(gòu)設(shè)計(jì)不合理，缺少必要的運(yùn)維方式。所有的這些問(wèn)題都可以在阻塞與等待中看出端倪，發(fā)現(xiàn)并解決問(wèn)題。

今天這篇我們主要講述怎么樣發(fā)現(xiàn)并解決系統(tǒng)的阻塞和等待。

場(chǎng)景描述

您的系統(tǒng)是否有這樣的問(wèn)題？

1. 系統(tǒng)運(yùn)行緩慢，很多功能需要幾十秒才能呈現(xiàn)結(jié)果，用戶(hù)體驗(yàn)極差，領(lǐng)導(dǎo)們不斷施壓，作為系統(tǒng)的負(fù)責(zé)人，只知道系統(tǒng)慢又不知道慢在哪里？我們遲遲不能解決問(wèn)題，領(lǐng)導(dǎo)已經(jīng)對(duì)我們?cè)孤曒d道了或者已經(jīng)慢習(xí)慣了，不再反饋了。
2. 系統(tǒng)的功能運(yùn)行緩慢，在生產(chǎn)環(huán)境中語(yǔ)句運(yùn)行時(shí)間很長(zhǎng)，但是在測(cè)試環(huán)境或者單獨(dú)拿出這條語(yǔ)句運(yùn)行的卻很快？這好像不科學(xué)呀？
3. 我對(duì)數(shù)據(jù)有較多的了解，我能查出系統(tǒng)的等待，但是我不知道這些等待意味著什么，百度的答案五花八門(mén)解決不了我的問(wèn)題。
4. 我能找到等待，也能解決這部分等待，但只是通過(guò)一些腳本，不能全面了解現(xiàn)狀，只能東一錘子西一棒子的游擊戰(zhàn)。
5. 我是專(zhuān)家問(wèn)題我都能解決，但不能給領(lǐng)導(dǎo)一個(gè)直觀(guān)的展現(xiàn)。

系統(tǒng)等待簡(jiǎn)介

一個(gè)好的SQL語(yǔ)句就好比一輛時(shí)速180的好車(chē)，好的系統(tǒng)硬件（CPU，內(nèi)存，磁盤(pán)）就好比平坦寬闊的馬路?？此坪密?chē)配好路，一定可以開(kāi)的很快了！其實(shí)還忽略了一點(diǎn)！當(dāng)你駕駛一輛法拉利跑在北京寬闊的三環(huán)上，就算你是老炮中的“三環(huán)十二少“，早高峰你能開(kāi)到多少? 北京的早高峰！北京的早高峰！

這個(gè)例子就引出了系統(tǒng)阻塞和等待的概念，紅燈（硬件等待，如IO等待），這就是正常的等待。另外一輛車(chē)在你前面不走了或開(kāi)的很慢，那么你也只能等待（也可以說(shuō)成你被他阻塞了）！

一張圖告訴你系統(tǒng)的主要等待類(lèi)型及解決思路：

問(wèn)題診斷

任何問(wèn)題的診斷都要從全局的角度考慮，最忌諱的就是看到一個(gè)指標(biāo)高就冒然定位問(wèn)題，然后以偏概全的去分析問(wèn)題。

一個(gè)問(wèn)題點(diǎn)可能涉及到很多部分，所以我們首先要從全局的角度定位系統(tǒng)問(wèn)題，阻塞也是一樣，到底系統(tǒng)中存在哪些類(lèi)型的阻塞，哪些是主因，哪些是關(guān)聯(lián)原因，哪些是次要的。

全局定位阻塞與等待　　

首先我們要關(guān)心數(shù)據(jù)庫(kù)中有哪些等待類(lèi)型

注：這部分呈現(xiàn)的是系統(tǒng)中的等待情況，和使用腳本類(lèi)似，已經(jīng)排除了不必要關(guān)心的類(lèi)型，同時(shí)對(duì)等待情況進(jìn)行歸類(lèi)統(tǒng)計(jì)。

橫坐標(biāo)：等待類(lèi)型

縱坐標(biāo)：收集時(shí)間段內(nèi)出現(xiàn)的次數(shù)

知道了等到類(lèi)型，我們要了解這些類(lèi)型中，哪種占用了大量的時(shí)間：

注：各種等待類(lèi)型所等待的時(shí)間也是排查的主要方向，結(jié)合等待類(lèi)型與等待時(shí)間，我們能了解到：系統(tǒng)中有哪些等待，哪些等待比較嚴(yán)重，哪個(gè)最嚴(yán)重。

橫坐標(biāo)：等待類(lèi)型

縱坐標(biāo)：平均等待時(shí)間

了解了主要的等待類(lèi)型和時(shí)間，我們還要分析一下：什么數(shù)據(jù)庫(kù)來(lái)的？哪些程序來(lái)的？什么用戶(hù)請(qǐng)求導(dǎo)致的？什么時(shí)間阻塞最嚴(yán)重？

具體語(yǔ)句看等待

系統(tǒng)的整體等待情況了然于心，下面我們改看看具體哪些語(yǔ)句造成的等待，這也是解決問(wèn)題的重要分析步驟。

哪些語(yǔ)類(lèi)句等待最頻繁

注：這里我們可以根據(jù)等待次數(shù)、等待時(shí)間、消耗的各種資源排序，來(lái)多維度分析阻塞的語(yǔ)句類(lèi)型

語(yǔ)句具體的等待情況時(shí)怎樣的呢？我們可以通過(guò)【原始視圖】查看具體語(yǔ)句在執(zhí)行過(guò)程中的真實(shí)阻塞情況

注：在阻塞的詳細(xì)視圖中我們可以清晰的看到語(yǔ)句的阻塞樹(shù)，并且可以看到阻塞的語(yǔ)句、時(shí)間、資源已經(jīng)阻塞等待的類(lèi)型

阻塞樹(shù)：本例中【會(huì)話(huà)68】被【會(huì)話(huà)66】阻塞，而【會(huì)話(huà)66】又被【會(huì)話(huà)104】阻塞，這樣3個(gè)會(huì)話(huà)就構(gòu)成了一個(gè)阻塞鏈也叫阻塞樹(shù)

診斷結(jié)論

通過(guò)全局定位，語(yǔ)句類(lèi)型分析，到具體的語(yǔ)句執(zhí)行阻塞狀態(tài)，根據(jù)阻塞類(lèi)型、次數(shù)、時(shí)間、連接程序、資源消耗等多種維度綜合分析，我們可以清楚的看出數(shù)據(jù)庫(kù)中的阻塞問(wèn)題。

本例中系統(tǒng)主要的阻塞類(lèi)型為CXPACKET和LCK_M_U，阻塞時(shí)間很長(zhǎng)，主要的阻塞產(chǎn)生時(shí)間為上午十一點(diǎn)左右，主要的阻塞語(yǔ)句是一條update 和一個(gè)復(fù)雜的select查詢(xún)等信息。

問(wèn)題解決

首先下面的這張圖已經(jīng)簡(jiǎn)單的說(shuō)明了系統(tǒng)對(duì)應(yīng)的等待需要怎么樣的解決思路?！　?/p>

注：根據(jù)不同的情況降低阻塞的辦法主要有：調(diào)整服務(wù)器、實(shí)例、數(shù)據(jù)庫(kù)配置參數(shù)（如：調(diào)整并行度），更改隔離級(jí)別（如：快照讀，nolock等），優(yōu)化語(yǔ)句（如：添加索引，優(yōu)化寫(xiě)法等）

本例中主要的CXPACKET是因?yàn)閷?shí)例并行度參數(shù)配置不佳而導(dǎo)致，LCK_M_U主要是一條update被一個(gè)批處理的另一條update阻塞鎖導(dǎo)致，優(yōu)化update這類(lèi)更新語(yǔ)句主要是保證update語(yǔ)句最優(yōu)化，執(zhí)行時(shí)間盡量縮短，另外高并發(fā)下的update比較常見(jiàn)的解決辦法是使用索引利用key鎖取代表鎖以提高并發(fā)，可能被更新的表只有幾十條記錄，添加索引與不加索引的并發(fā)效率差別也會(huì)很大。另外程序的設(shè)計(jì)也是非常重要的，各種奧秘各位看官只能在實(shí)際環(huán)境中慢慢體會(huì)了，而使用SQL專(zhuān)家云工具的主要目的在于全面的定位問(wèn)題，圖表統(tǒng)計(jì)等形式清晰的展現(xiàn)問(wèn)題，并根據(jù)工具提供的解決方案快速解決問(wèn)題。

以上就是本文的全部?jī)?nèi)容，希望本文的內(nèi)容對(duì)大家的學(xué)習(xí)或者工作能帶來(lái)一定的幫助，同時(shí)也希望多多支持腳本之家！