[[208300]]

編者按

《SQL性能優(yōu)化與批判》是黃浩老師的系列新作，他將從過往在項(xiàng)目技術(shù)支持中碰到的諸多案例入手，細(xì)化到每一條問題SQL的內(nèi)在病因，反思每一個(gè)案例的背后深思。今天跟大家分享的是第四個(gè)案例：獲取責(zé)任人，需要回顧前情的同學(xué)請戳這里：案例一、案例二、案例三。

一、案例

這一天，I項(xiàng)目組的一個(gè)迭代版本需要上線，這是一個(gè)大版本，需要全員現(xiàn)場支撐，并要求上線后三天待命。

1、不速之客，來者不善

而就在上線前兩天，即9月24日下午4點(diǎn)鐘，一直以來波瀾不驚有驚無險(xiǎn)的性能優(yōu)化，突然被放了一個(gè)大招，某個(gè)頁面被測出了嚴(yán)重的性能問題，大致情況如下：

測試人員在性能環(huán)境做了一輪壓力測試，數(shù)據(jù)增加了5倍，其它功能點(diǎn)基本上達(dá)到了性能指標(biāo)，而該功能則需要6s，整整超出了3s。

瞬間，大家都緊張起來了。

由于0926版本是公司級(jí)的大版本，不光是I項(xiàng)目組發(fā)布版本，H公司的其它系統(tǒng)也會(huì)同時(shí)發(fā)布版本。為了控制風(fēng)險(xiǎn)，會(huì)提前兩天凍結(jié)代碼。按照“不帶BUG上生產(chǎn)”的原則，我們必須要在版本凍結(jié)截至?xí)r(9月24日18點(diǎn)準(zhǔn))“斃”掉這個(gè)性能BUG單。而距離18點(diǎn)還不到2個(gè)小時(shí)。

PM在得知這一消息后也高度關(guān)注，責(zé)令優(yōu)化小組全力攻關(guān)，要人給人。這樣，組長、模塊SE及我就組成了臨時(shí)應(yīng)急小組。大家全力以赴，很快就把問題梳理出來了，大致如下：

該頁面加載共需要執(zhí)行8條SQL，單條SQL的執(zhí)行都不長，都在性能指標(biāo)范圍內(nèi)，但是加起來超過了5s;

剩下的2s耗在頁面的邏輯處理。

當(dāng)時(shí)，組長當(dāng)機(jī)立斷，一方面要求對(duì)這些SQL進(jìn)行優(yōu)化，優(yōu)化到2s左右;另一方面將頁面的處理耗時(shí)降到1s內(nèi)，這樣就能確保3s的性能要求。

SQL優(yōu)化任務(wù)自然落在我的頭上，8個(gè)SQL的代碼如下：

2、兵分兩路，把雞蛋放在兩個(gè)籃子里

看著這8個(gè)不長不短整整齊齊的SQL，我的***反應(yīng)是：一個(gè)頁面加載怎么會(huì)存在8個(gè)SQL語句?這8個(gè)SQL之間又有著什么樣的關(guān)聯(lián)關(guān)系?是否還可以合并成一個(gè)?

如果做SQL合并的話，就意味著我需要詳讀這8個(gè)SQL，但時(shí)間的指針已經(jīng)指在了17:00，離18:00下班不足一個(gè)小時(shí)。用中國足球賽事評(píng)論員的話說就是“現(xiàn)在留給中國對(duì)的時(shí)間已經(jīng)不多了”，已經(jīng)沒有時(shí)間讓我解讀這8個(gè)SQL;況且，即便能快速解讀，也未必能合并。

那么就要像組長提議的：尋求單個(gè)SQL的優(yōu)化突破。而8個(gè)SQL優(yōu)化到2秒，也就是說單個(gè)SQL平均耗時(shí)在0.25秒，這個(gè)壓力也是非常大的。

我在與組長簡短商議后，為了降低風(fēng)險(xiǎn)，不至于孤注一擲，做出了如下決定：兵分兩路，由我執(zhí)行合并方案，優(yōu)化小組的DBA負(fù)責(zé)單個(gè)SQL進(jìn)行優(yōu)化。

3、原來如此，不過如此

按照以往的習(xí)慣，我肯定會(huì)先自己解讀這8個(gè)SQL，因?yàn)槲蚁嘈艅e人的時(shí)間也是時(shí)間，能自己解決的盡量不要占用別人的時(shí)間。但這次不行了，因?yàn)闀r(shí)間不允許了，我必須要快速了解8個(gè)SQL的業(yè)務(wù)功能。

于是我跟SE表達(dá)了我訴求，SE立即安排了開發(fā)責(zé)任人跟我對(duì)接。在與開發(fā)人員長達(dá)20分鐘的溝通后，終于理清了這個(gè)8個(gè)SQL的邏輯與關(guān)系，如下：

查詢?nèi)蝿?wù)列表，共3個(gè)SQL，共耗時(shí)1s，主SQL，包括了count和詳情

查詢責(zé)任人：4個(gè)SQL，共耗時(shí)3s，但是頁面自上而下共耗時(shí)5s

查詢網(wǎng)絡(luò)節(jié)點(diǎn)：1個(gè)SQL共耗時(shí)0.5s

這是個(gè)重大發(fā)現(xiàn)：6s多的時(shí)間中，查詢責(zé)任人花費(fèi)了5s，這是要重點(diǎn)照顧的對(duì)象。我繼續(xù)向開發(fā)責(zé)任人了解更多的信息：

“查詢責(zé)任人SQL，SQL單獨(dú)運(yùn)行是3s，為何頁面卻花費(fèi)了5s?”

“因?yàn)轫撁嫘枰獙?duì)SQL返回的數(shù)據(jù)集進(jìn)行判斷。”

“都做了哪些邏輯處理?”

“這四個(gè)SQL分別對(duì)應(yīng)四類權(quán)限，權(quán)限的最小單元是實(shí)體DU，在任務(wù)列表中獲取的DU，先用***個(gè)SQL判斷哪些DU具有***類權(quán)限，比如有100個(gè)，那么傳入第二個(gè)SQL的DU就是90個(gè)DU，由此類推，知道完成了4類權(quán)限的判。”

聽完后，我豁然開朗，邏輯流程圖如下：

4、對(duì)癥下藥，一蹴而就

至此，我已成竹在胸。

四個(gè)SQL對(duì)應(yīng)四種權(quán)限，如果我們把TASK_ID比作學(xué)生，把USER_ID比作班級(jí)，而將權(quán)限比作是學(xué)生選修的四門學(xué)科。那么“權(quán)限責(zé)任人查詢”就轉(zhuǎn)變成查詢當(dāng)前班級(jí)每個(gè)學(xué)生***分的科目。

這是典型的按優(yōu)先級(jí)排序后取***值的需求。當(dāng)前的方案是：

• 依次從DB中獲取四種權(quán)限對(duì)應(yīng)的DU_ID;
• 在JAVA中根據(jù)DB返回的權(quán)限判斷權(quán)限類型。

該方案存在兩個(gè)性能瓶頸：

• 將權(quán)限數(shù)據(jù)從DB傳輸?shù)絁AVA服務(wù)器是要一定的成本開銷的;
• 當(dāng)JAVA拿到權(quán)限數(shù)據(jù)數(shù)據(jù)時(shí)，需要循環(huán)逐一歸集權(quán)限類型，這個(gè)過程也會(huì)帶來一定的性能問題。

如果我們能將權(quán)限類別歸集放在DB中完成，即DB只需要返回當(dāng)前用戶的DUID所屬權(quán)限類別即可，那么至少省卻了4次數(shù)據(jù)傳輸?shù)臅r(shí)耗。當(dāng)然，權(quán)限類型歸集無論是放在DB還是JAVA，都是需要成本開銷，就看誰的算法更具優(yōu)勢。事實(shí)上Oracle則提供了完整的解決方案，即用rank over來實(shí)現(xiàn)優(yōu)先級(jí)排序。

此時(shí)時(shí)間已經(jīng)到了17:20，我來不及多想，立馬對(duì)查詢責(zé)任人的4個(gè)SQL進(jìn)行合并改寫，合并后的SQL如下： 

改寫后，放在DB中執(zhí)行，耗時(shí)0.98秒。這意味著，責(zé)任人查詢從5s成功降到了1s內(nèi)，足足下降了4s;這樣，整體上也完全滿足性能要求。

我在17：25將SQL移交給了開發(fā)，留給開發(fā)人員35分鐘時(shí)間去開發(fā)驗(yàn)證。

結(jié)果自然是皆大歡喜，項(xiàng)目順利上線。

二、心得

1、學(xué)無止境的態(tài)度

當(dāng)SE拿到我合并的SQL后，滿臉的疑惑：

“這個(gè)SQL會(huì)不會(huì)有問題?”

“我是按照業(yè)務(wù)需求改寫的，如果我沒有錯(cuò)誤理解需求的話，SQL就是正確的。”

“也是，我測試了好幾種場景，結(jié)果看起來都是正確的。”

接著我又詳細(xì)講解了Rank的功用和用法。SE長吁一聲說道：“早知道Oracle有如此“神器”，當(dāng)初就也不用費(fèi)老大勁在Java中做權(quán)限類型歸集了，還弄出了性能問題?？磥碚娴氖菍W(xué)無止境呀。”

在此，我無意于苛求SE“早知如此，何必當(dāng)初”，畢竟術(shù)業(yè)有專攻。唯一不解的是，偌大的一個(gè)項(xiàng)目組(近200人)，居然沒有配置一名DB開發(fā)工程師。建表，寫SQL這些活都是由Java開發(fā)人員包辦。而在與Java開發(fā)工程師溝通中了解到，部分人員根本沒有SQL基礎(chǔ)，更不用說是開發(fā)經(jīng)驗(yàn)。而他們寫SQL的方式即簡單又粗暴，是從同事那里拿一個(gè)功能類似的SQL，直接在此基礎(chǔ)上修改，也不知道該SQL的具體含義。

這種現(xiàn)學(xué)現(xiàn)賣的方式也直接導(dǎo)致了很大的性能問題。正是因?yàn)榇_實(shí)了DB開發(fā)工程師的崗位配置，大大弱化了SQL功能，使得DB退化成為僅僅是數(shù)據(jù)存儲(chǔ)功能，失去了真正的核心：組織和管理功能。

作為不僅僅是世界500強(qiáng)的企業(yè)，作為國內(nèi)代表***開發(fā)水準(zhǔn)的企業(yè)，在企業(yè)管理系統(tǒng)的開發(fā)項(xiàng)目中，尚且不配置專職DB開發(fā)工程師，而其它企業(yè)的開發(fā)團(tuán)隊(duì)的人員配置就更可想而知了。

2、點(diǎn)到為止的哲學(xué)

在組長的運(yùn)籌帷幄下，性能優(yōu)化小組在緊張備戰(zhàn)1017版本性能攻關(guān)的同時(shí)，很好地保障了926版本的性能需求，使得926版本順利上線，I項(xiàng)目PM也揚(yáng)眉吐氣了一把：在性能紅線上，終于沒有求爺爺告奶奶放一馬了。在926版本上線后，一方面為表謝意，另一方面也為1017版本打氣，PM宴請了小組成員，席間問起：

“黃工，就你來看，項(xiàng)目在SQL這方面還有多大的優(yōu)化空間?”

“這要看領(lǐng)到對(duì)性能的要求和優(yōu)化的決心了。”

“怎么說?”

“真正的優(yōu)化，***的空間還是在于從底層的模型設(shè)計(jì)，以及寫出規(guī)范和優(yōu)秀的SQL，因此應(yīng)該在項(xiàng)目上配置專職的DBA………..”

“呃，黃工，這樣可不行，如果真的是這樣了，那你們干嘛呢?”

領(lǐng)導(dǎo)就是領(lǐng)導(dǎo)，不正面沖突，在輕描淡寫中已經(jīng)說明了一切，而后來我在內(nèi)部資料中看到“現(xiàn)固化，再僵化，后優(yōu)化”的流程策略時(shí)，就更明白了。