《SQL性能優(yōu)化與批判》是黃浩老師的系列新作，他將從過(guò)往在項(xiàng)目技術(shù)支持中碰到的諸多案例入手，細(xì)化到每一條問(wèn)題 SQL 的內(nèi)在病因，反思每一個(gè)案例的背后深思，抽絲剝繭，層層深入。

[[203675]]

今天跟大家分享的是 WM_CONCAT 優(yōu)化，這是一次憑借技術(shù)+經(jīng)驗(yàn)+運(yùn)氣三重加成才得以解決的案例，are you ready?

案例

01.初來(lái)乍到，如臨深淵

公元 2015 年 7 月 20 日，天氣還是一如既往的炙熱，徐徐海風(fēng)也吹不散身上的熱量。在經(jīng)過(guò)近一個(gè)小時(shí)的班車(chē)加徒步，我正式開(kāi)啟了在 H 公司 I 項(xiàng)目技術(shù)支持的***天。

因?yàn)樾畔踩木壒剩?**次進(jìn)入項(xiàng)目現(xiàn)場(chǎng)的外協(xié)人員需要辦理接待電子流。因?yàn)槭欠茄邪l(fā)區(qū)域，倒也快捷，經(jīng)過(guò)兩重關(guān)卡后，順利進(jìn)入到項(xiàng)目現(xiàn)場(chǎng)。

媽呀，一個(gè)足球場(chǎng)般大小的辦公場(chǎng)地，一排排的辦公桌和電腦井然有序，但桌面上的辦公用品卻凌亂狼藉，而座位跟座位之間沒(méi)有任何的遮擋。

當(dāng)時(shí)已經(jīng)九點(diǎn)多，基本上座無(wú)虛席，雖然開(kāi)著空調(diào)，仍然能感覺(jué)到一股由電腦散發(fā)出來(lái)的摻雜著鐵銹及灰塵味的熱氣，以及由此帶來(lái)的壓抑感。

在與現(xiàn)場(chǎng)同事簡(jiǎn)短的寒暄后，我便立馬投入到工作——當(dāng)然是交接工作。與同事的溝通中，我獲取了如下信息：

• 這位同事來(lái)這個(gè)項(xiàng)目不足兩周。
• 離職的原因是適應(yīng)不了外包的工作方式。
• 項(xiàng)目組性能優(yōu)化工作開(kāi)展很困難，項(xiàng)目組在這方面的投入不夠，重視度也不夠。

綜合起來(lái)就是一個(gè)字：坑，而且是巨坑。原本擔(dān)心我主觀上的能力問(wèn)題會(huì)影響到工作，沒(méi)想到客觀環(huán)境也是如此糟糕，我的心情跌倒了冰點(diǎn)。

明天是這位同事在項(xiàng)目組的 last day，所以交接工作必須在今天內(nèi)完成。好在同事進(jìn)項(xiàng)目不久，還沒(méi)有接觸到太多的工作內(nèi)容，手頭上就一個(gè)在優(yōu)化的 SQL。

因?yàn)檫@個(gè) SQL 的優(yōu)化已經(jīng)持續(xù)了幾天時(shí)間，所以到目前顯得有些緊迫：該 SQL 的優(yōu)化被安排在周六上線，因此必須要在周三前給出優(yōu)化方案。

離周三只有不到 2 天的時(shí)間了，而目前的優(yōu)化進(jìn)度還停留在問(wèn)題定位階段，還不確定問(wèn)題處在哪里?換句話說(shuō)，不是工作交接，而是從零開(kāi)始。

我在同事的交接文檔中找到了問(wèn)題 SQL，代碼如下：

02.戰(zhàn)戰(zhàn)兢兢，如履薄冰

沒(méi)有任何的注釋，代碼中的表呀，字段呀什么的，我一個(gè)也不認(rèn)識(shí)，唯一親切的就是 select from where join group 這些被標(biāo)綠的 SQL 關(guān)鍵字。

“這個(gè) SQL 有什么性能癥狀?”

“跑起來(lái)很慢。”

“慢到什么程度?”

“大概需要半個(gè)多小時(shí)才能跑完。”

“數(shù)據(jù)量很大嗎?”

“可能吧，我還沒(méi)有執(zhí)行過(guò)，只是聽(tīng)開(kāi)發(fā)人員這么說(shuō)的。”

看來(lái)我不能從這位同事這里得到更多有價(jià)值的信息了。

按下 F5 查看執(zhí)行計(jì)劃：

執(zhí)行計(jì)劃中，表訪問(wèn)方式基本上都是 index scan，而且也并無(wú)大成本的操作。奇怪了，問(wèn)題處在哪里呢?我又回到 SQL 窗口，按下 F8，果然只見(jiàn)時(shí)間過(guò)，不見(jiàn)數(shù)據(jù)出來(lái)。

在長(zhǎng)期與 SQL 相伴的日子里，我養(yǎng)成了一個(gè)習(xí)慣，喜歡在邊看著 Oracle 執(zhí)行，一邊分析代碼，大有“我忙著分析，你也別閑著偷懶”的“小人嘴臉”。

這個(gè) SQL 有兩個(gè)部分，***部分是用 with 封裝了一個(gè)結(jié)果集，第二部分是對(duì)***部分的結(jié)果集進(jìn)行 group by 處理。根據(jù)過(guò)往經(jīng)驗(yàn)，我將 SQL 復(fù)制到了另一個(gè) SQL 窗口，選中 with 子句單獨(dú)執(zhí)行，秒出呀。

排除了子查詢的性能嫌疑，那么很顯然問(wèn)題是出在第二部分的 SQL。第二部分 SQL 包含了 group by，難道是 group by 產(chǎn)生了性能問(wèn)題。要知道，group by 等聚合操作的性能對(duì)數(shù)據(jù)量是極其敏感的。難道是 with 子查詢的數(shù)據(jù)量非常大?

我趕緊 count 了***部分 SQL 的結(jié)果集，顯示不到 20 萬(wàn)數(shù)據(jù)。那就不應(yīng)該呀，20 萬(wàn)數(shù)據(jù)做 group by 也不至于慢成“蝸牛”呀。

繼續(xù)分析第二部分 SQL 代碼，在 select 子句中，驚現(xiàn) wm_concat 函數(shù)。此時(shí)，我還是有些小激動(dòng)的，因?yàn)樵谥耙灿龅竭^(guò)由于 wm_concat 引發(fā)的性能問(wèn)題。為了驗(yàn)證判斷，我將 wm_concat 注釋掉，按F8 運(yùn)行，果然飛快，不到 1s 就出結(jié)果。

至此，通過(guò)排除法，病因是找到了：由 wm_conca t引發(fā)了性能問(wèn)題。

03.順藤摸瓜，順手牽羊

原因已經(jīng)找到，那么對(duì)癥又該如何下藥呢?顯然，從 SQL 功能上，wm_concat 是必須的，我也嘗試過(guò)用 listagg 來(lái)替代 wm_concat，但是會(huì)因超過(guò) 4000 字符而報(bào)錯(cuò)。

其實(shí) wm_concat 函數(shù)之所以慢，就是因?yàn)橐?task_name 為維度需要拼湊的數(shù)據(jù)量太大導(dǎo)致的。難道就無(wú)解了嗎?

我轉(zhuǎn)念一想，為什么要用 wm_concat 函數(shù)?應(yīng)用程序在拿到這個(gè)字段后做什么用呢?在前端頁(yè)面顯示嗎?

這種顯示是沒(méi)有多大意義的，因?yàn)?wm_concat 的結(jié)果可能非常大，根本就顯示不了。既然顯示不完整，那么為什么又要從 DB 中獲取完整的內(nèi)容呢?

帶著這些疑惑，我與 SQL 開(kāi)發(fā)人員進(jìn)行了溝通，原來(lái)，應(yīng)用程序拿到這個(gè) SQL 的數(shù)據(jù)后，并不是在前端頁(yè)面展現(xiàn)，而是在應(yīng)用程序中繼續(xù)加工處理，在經(jīng)過(guò)若干復(fù)雜的邏輯處理后，以另一種形式在頁(yè)面展現(xiàn)。

此時(shí)，多年的從業(yè)經(jīng)驗(yàn)告訴我：既然可以用 Java 來(lái)實(shí)現(xiàn)的業(yè)務(wù)邏輯，那么肯定也能在 DB 中通過(guò) SQL 來(lái)實(shí)現(xiàn)，這樣就可以避開(kāi) wm_concat 函數(shù)。

于是我決心深入了解業(yè)務(wù)功能，希望能從業(yè)務(wù)方案上有所突破。這樣就形成了一個(gè)初步的工作計(jì)劃：了解整體業(yè)務(wù)功能及邏輯-->了解應(yīng)用程序處理邏輯-->改寫(xiě) SQL 語(yǔ)句-->功能性測(cè)試-->性能輪回調(diào)整。

在大約兩個(gè)小時(shí)的一對(duì)一講解后，我基本上掌握了整體業(yè)務(wù)功能及邏輯、應(yīng)用技術(shù)架構(gòu)及處理邏輯。

這個(gè)其實(shí)是一個(gè)報(bào)表展現(xiàn)功能，是按區(qū)域、里程碑展現(xiàn)兩個(gè)相鄰里程碑之間的時(shí)間間隔，包括計(jì)劃間隔時(shí)間與實(shí)際間隔天數(shù)(平均)。

報(bào)表格式大致如下：

在 DB 中，里程碑的計(jì)劃與實(shí)際時(shí)間是存在二維表中，結(jié)構(gòu)示意如下：

在這里，就存在一個(gè)行列轉(zhuǎn)換的問(wèn)題，即將 TASK_NAME 從以行存儲(chǔ)轉(zhuǎn)換成以列展現(xiàn)。

為了實(shí)現(xiàn)這種結(jié)構(gòu)轉(zhuǎn)換，當(dāng)時(shí)的架構(gòu)設(shè)計(jì)如下：

• 通過(guò) SQL 從 DB 獲取每個(gè)里程碑、交付區(qū)域的 plan_start_time、plan_end_time、actural_start_time、actural_end_time 及 du 集合，即 SQL 中的 wm_concat 拼湊后的結(jié)果。
• Java 應(yīng)用程序拿到這個(gè)結(jié)果后，循環(huán)結(jié)果集，并依次分解由 wm_concat 拼湊的內(nèi)容：計(jì)算每一個(gè)里程碑內(nèi) DU 的平均時(shí)間間隔;判斷里程碑的前后置關(guān)系;計(jì)算前后置里程碑間的天數(shù)間隔;最終將計(jì)算結(jié)果展現(xiàn)在前端頁(yè)面。

04.水到渠成，一戰(zhàn)而定

從上述描述中，我們可以提煉出如下信息：

• WM_CONCAT 拼湊的內(nèi)容只是過(guò)渡的，在 Java 中還需要依次分解。
• Java 處理的幾個(gè)步驟完全可以由 SQL 來(lái)實(shí)現(xiàn)。

這樣就可以省卻以下幾個(gè)“麻煩”：

• 省卻了大量數(shù)據(jù)從 DB 傳輸?shù)?Java 服務(wù)器的成本開(kāi)銷(xiāo)。
• 可以順理成章的拔掉 wm_concat 這根刺。

那么，如果用 SQL 來(lái)實(shí)現(xiàn)上述邏輯功能，存在兩個(gè)難點(diǎn)，其一是如何判斷里程碑(task_name)前后置關(guān)系，其二是計(jì)算前后置里程碑的時(shí)間差。

進(jìn)一步分析后發(fā)現(xiàn)，里程碑(task_name)前后置關(guān)系可以通過(guò) SQL 來(lái)獲取，而在時(shí)間間隔的計(jì)算上，可以通過(guò) lead 窗口分析函數(shù)獲取后置時(shí)間，然后相減即可。

改造后的 SQL 如下：

將 SQL 在 DB 中運(yùn)行，不到 3 秒就執(zhí)行完成。

心得

01.心有余悸，學(xué)無(wú)止境

值得一提的是，這個(gè) SQL 并非一蹴而就的，從***次改寫(xiě)，到最終上線，經(jīng)歷了好幾個(gè)版本，但整體結(jié)構(gòu)并沒(méi)有變動(dòng)，只是對(duì)某些特殊場(chǎng)景做了調(diào)整。

我來(lái)項(xiàng)目的***個(gè) SQL 優(yōu)化就這樣跌跌撞撞、歪打正著的完成了。由于時(shí)間緊迫，整個(gè)過(guò)程都是繃緊了神經(jīng)。

現(xiàn)在回想起來(lái)，既是慶幸又是后怕，慶幸的是問(wèn)題得到了及時(shí)解決;后怕的是，當(dāng)時(shí)可謂是不知者無(wú)畏，完全是在不熟悉環(huán)境，不熟悉利害關(guān)系的情況下解決了問(wèn)題。如果放在幾個(gè)月后，我想一定沒(méi)有當(dāng)時(shí)的勇氣和決心來(lái)完成這件事情。

回過(guò)頭來(lái)看，這起由 wm_concat 引發(fā)的性能事件還是給了我們很多的啟發(fā)：

SQL 優(yōu)化不是孤立的存在

SQL 優(yōu)化并不是孤立的，也就是說(shuō)并不是所有的 SQL 本身都存在優(yōu)化的空間。當(dāng) SQL 本身無(wú)法優(yōu)化的時(shí)候，或者優(yōu)化的空間不足以滿足用戶需求時(shí)，就需要從全局需求突破。

嘗試著按另一種方式得到結(jié)果：殊途同歸講的不就是這個(gè)道理嗎?正所謂山重水復(fù)疑無(wú)路，柳暗花明又一村，關(guān)鍵在于你是否愿意主動(dòng)尋求和突破。

SQL 優(yōu)化其實(shí)很樸素

SQL 優(yōu)化并不需要多么高深的知識(shí)和高級(jí)的技術(shù)，SQL 優(yōu)化也并不那么神秘，一點(diǎn)點(diǎn)技術(shù)，一點(diǎn)點(diǎn)經(jīng)驗(yàn)，再加上一點(diǎn)點(diǎn)運(yùn)氣就足夠了。

一點(diǎn)點(diǎn)技術(shù)

這里說(shuō)的技術(shù)是 SQL 技術(shù)。SQL 語(yǔ)言我認(rèn)為是除匯編外所有語(yǔ)言中最神奇、最簡(jiǎn)單、***藝術(shù)化的語(yǔ)言。

說(shuō)簡(jiǎn)單，就 select 查詢而言，就 select from where and or group order 等***的幾個(gè)關(guān)鍵字，拿 SQL 而言也就 select、update、delete、insert 四種功能。而且通俗易懂。

說(shuō)神奇，因?yàn)榫瓦@些關(guān)鍵字，無(wú)需排列組合，便可以千變?nèi)f化。在當(dāng)今的信息化大時(shí)代，無(wú)外乎就是增刪改查;大千世界，蕓蕓眾生，概莫能外。

就拿人類自身來(lái)說(shuō)，其***哲學(xué)就是：生老病死，出生就是 insert，歲月催人老就是 update，眾里尋他千百度就是 select，榮登極樂(lè)就是 delete。

說(shuō)藝術(shù)化，簡(jiǎn)單而不簡(jiǎn)約，這就是藝術(shù)，能以數(shù)個(gè)關(guān)鍵字撐起世間萬(wàn)物的起起落落，這就是藝術(shù)。

這里說(shuō)的掌握 SQL 技術(shù)，不僅僅是掌握這幾個(gè)關(guān)鍵字，用這幾個(gè)關(guān)鍵字變幻出種種結(jié)果，更是要掌握如何通過(guò)這幾個(gè)關(guān)鍵字來(lái)實(shí)現(xiàn)這種藝術(shù)化的效果。

一點(diǎn)點(diǎn)經(jīng)驗(yàn)

經(jīng)驗(yàn)這東西是美妙的，一旦你擁有了某個(gè)知識(shí)點(diǎn)的經(jīng)驗(yàn)，下次再遇到時(shí)，你會(huì)不費(fèi)吹灰之力就能解決了。

比如這次的 wm_concat 函數(shù)，我相信，之前的同事沒(méi)有定位出問(wèn)題所在，就是他沒(méi)有遇到過(guò) wm_concat 這個(gè)函數(shù)。所以總結(jié)經(jīng)驗(yàn)是絕對(duì)正確的，雖然經(jīng)驗(yàn)并不一定有用得上的機(jī)會(huì)。

一點(diǎn)點(diǎn)運(yùn)氣

所學(xué)的一點(diǎn)點(diǎn)知識(shí)和積累的一點(diǎn)點(diǎn)經(jīng)驗(yàn)恰好被用上了，這就是運(yùn)氣。因此運(yùn)氣也是辯證的，表面上是因?yàn)檫\(yùn)氣解決了這個(gè)問(wèn)題，實(shí)則不然，如果沒(méi)有那么一點(diǎn)點(diǎn)知識(shí)和經(jīng)驗(yàn)，也不會(huì)這么順利的解決?？梢?jiàn)偶然中也有必然。

批判

7 月 25 日周末上線，周一一大早，開(kāi)發(fā)兄弟像報(bào)喜一樣告訴我，優(yōu)化效果明顯，用戶非常滿意?？粗赡壑新詭酀男δ槪乙查L(zhǎng)舒一口氣，畢竟這是我的***個(gè)優(yōu)化案例。

“黃工，你是怎么知道可以這樣處理的?”

面對(duì)他的這個(gè)問(wèn)題，我一時(shí)啞口，該如何回答呢?

“那你當(dāng)初為什么要將 SQL 返回中間結(jié)果集，然后又在 Java 中做邏輯處理呢?”

“一方面，我們的架構(gòu)規(guī)范就是這樣的，要求盡量在 Java 中完成邏輯處理，減少 DB 的負(fù)載;另一方面，我也寫(xiě)不出這么復(fù)雜的 SQL，說(shuō)實(shí)話，你給我的 SQL，我到現(xiàn)在還沒(méi)有看明白。”

原來(lái)如此，我就告訴他：

“在二維關(guān)系的系統(tǒng)里面，Java 能處理的二維數(shù)據(jù)，在 SQL 中都能實(shí)現(xiàn)”

“哦”

“對(duì)了，你是怎么選擇 wm_concat 這個(gè)函數(shù)的?”我知道這個(gè)函數(shù)很少用，也是 Oracle 公司未公開(kāi)的內(nèi)部函數(shù)。

“我是在網(wǎng)上查到的資料，看到這個(gè)函數(shù)可以實(shí)現(xiàn)功能，就拿來(lái)用了，沒(méi)想到會(huì)帶來(lái)這么大的性能問(wèn)題。”

看得出來(lái)，他仍然保持了學(xué)生意氣，有些自責(zé)，他好像又想起了什么來(lái)，趕緊補(bǔ)充說(shuō)“因?yàn)闀r(shí)間太緊迫了，現(xiàn)在是敏捷開(kāi)發(fā)，每?jī)芍芤粋€(gè)版本，如果時(shí)間充裕的話，我想我也能通過(guò)查資料把這個(gè) SQL 寫(xiě)出來(lái)的。”

他說(shuō)著有些激動(dòng)，但事實(shí)上他是認(rèn)真的，也真的做到了。在后來(lái)的開(kāi)發(fā)過(guò)程中，他寫(xiě)出了連我都寫(xiě)不出來(lái)的復(fù)雜 SQL。

通過(guò)與他的對(duì)話，我大致可以勾畫(huà)出這個(gè)項(xiàng)目的一些基本元素：敏捷開(kāi)發(fā)，雙周迭代，無(wú)開(kāi)發(fā)型 DBA，重 Java 輕 SQL。

這些是國(guó)內(nèi)大多數(shù)項(xiàng)目的通病，本來(lái)是見(jiàn)怪不怪，但是出現(xiàn)在世界 500 強(qiáng)，國(guó)內(nèi) IT 軟件天堂的大公司，還是讓我有些意外，更讓人感到后脊涼涼的。

敏捷開(kāi)發(fā)要求快速交付，功能優(yōu)先性能，急功近利;偌大的一個(gè)企業(yè)級(jí)平臺(tái)項(xiàng)目，居然沒(méi)有匹配一個(gè)專職的開(kāi)發(fā) DBA，SQL 的質(zhì)量令人擔(dān)憂。

而重 Java 輕 SQL 在信息管理系統(tǒng)中是一個(gè)大忌，會(huì)暗藏很多性能風(fēng)險(xiǎn)，這些都是性能的催化劑。這意味著我接下來(lái)的道路勢(shì)必坎坷曲折、荊棘叢生。