一次非典型SQL優化：如何通過業務邏輯優化另辟蹊徑？

拿到SQL的時候，發現非常簡單，如下：

在簡短溝通后，得到如下信息：

1. 該SQL的功能是從TM_TASK_T和TM_TASK_HIS_T表中找出相同的數據，即得到兩個表的 交集 ；

2. 除了該SQL外，還存在另外9個類似功能的SQL，其共同點是獲取 當前表和歷史表的交集；

3. 該SQL耗時大約在20S左右，但是全部10個SQL的 耗時加起來就超出了120S的超時閾值；

4. 這一系列SQL的目的是檢驗數據的 唯一性 ，即確保當前表和歷史表的數據 不重復 。

1 看執行計劃

我開始對該SQL進行分析，SQL看起來非常簡潔，就兩個表關聯，并且全部字段都來自于HIS表。執行計劃如下：

+

因為整個SQL中TM_TASK_T表 只在關聯條件上訪問了TASK_ID字段 ，而TASK_ID上又創建了UNIQUE索引TM_TASK_T_PK，所以對TASK表的訪問方式是：INDEX FAST FULL SCAN，也就是 沒有“回表” （table access by index rowid）。

我又查看了兩個表的數據量，HIS的數據量為500萬+，TASK的數據量接近4000萬，TM_TASK_HIS_T為驅動表，并且與TM_TASK_T表HASH JOIN也是正確的選擇。

因此，從執行計劃上看，似乎沒有什么優化的空間了。

2 等價SQL改寫

再回到功能上，SQL的功能是獲取兩個結果集的交集，再看看這兩個結果集的關系，從表對象的命名上就可以猜出個大概：即TM_TASK_HIS_T表是TM_TASK_T表的歷史表。而 TASK_ID是TM_TASK_T表的主鍵 ， 所以在取交集的時候， 只需要TASK_ID字段。

那么，在TM_TASK_HIS_T表中TASK_ID是否也有索引呢？查看了表結構后，發現在TM_TASK_HIS_T表中，TASK_ID上也創建了主鍵索引：TM_TASK_HIS_T_PK。

此時，一個習慣性優化方案在腦海中閃過： 可以通過這兩個主鍵索引先獲取TASK_ID的交集，再根據TASK_ID交集從TM_TASK_HIS_T表中獲取字段信息。

根據上述思路，我將SQL改寫如下：

改寫后的SQL耗時在10S左右，性能提升了1倍。

我們先對比下前后兩個執行計劃：

• 改寫前的執行計劃：

• 改寫后的執行計劃：

可以看出，改寫后在IO讀寫上還是有很大提升的。但是，現在的問題是，這種提升似乎還是 無法從根本上解決性能問題 ，仍然存在超時的風險，而且隨著 數據的增長，風險的概率也越來越大。

然而，基于SQL的優化空間在性能要求面前顯得捉襟見肘。

3 技術方案調整

既然SQL本身上無法突破，那就退一步，從功能框架上看看是否存在“突破口”。

此時，我想起了當時溝通是得到的一個信息：“除了該SQL外，還存在另外9個類似功能的SQL”。我進一步的了解到這10個功能點是相對獨立的，即相互之間 不存在依賴 ，也就是說，這10個原本串行執行的SQL是可以 并行執行 的，這樣一來，耗時的計算方法發生了變化：

性能耗時從串行執行的求和變成了并行執行的求最大值，也就是說，如果選擇了并行執行，只需要保證單個SQL的最大耗時不超過120S即可。而這點顯然是可以完成的。但是開發同事則極力反對，反對的理由是：

1. 這一系列SQL被應用程序封裝成一個任務，該任務并非定時執行，而是人為手動執行，如果被拆分成10個任務，也就意味著需要用戶手工觸發10個任務， 用戶體驗太差 ；

2. 拆分成10個任務，不利于后期的 維護管理 。

以上的兩個理由，尤其是第一個理由，的確無可辯駁。

4 探究原始需求

看來由串行改并行，此路不通。盡管如此， 生活還要繼續，優化不能停止 。此路不通，只能另覓他方，再退一步，回到SQL的最原始需求，從最原始的需求出發，看能否找到優化的空間。

解鈴還須系鈴人，再次與開發同事進行了深入溝通，了解到的信息如下：

1. 在日常業務運營中，因為業務需求，需要從當前表清理掉一部分數據，在清理前，程序會將本次要清理的數據 寫入到歷史表中，然后再清理；

2. 由于一些異常情況，會導致被清理的數據寫入了歷史表，而沒有從當前表中清理掉；

3. 為了及時發現這些異常數據，在后臺啟動了一個定時任務，周期性的監控歷史表與當前表的數據；

4. 當監控到數據異常（即重復）時，就會 手工觸發 這一系列的SQL清理數據。

此時，我的第一反應是：能不能 跑增量數據 ？ 目前是全表判斷是否重復，如果能做到增量判斷，性能的提升肯定是質變的。我設想的增量方案是： 以TM_TASK_HIS_T表為主表，獲取到自上次數據修復到本次數據修復期間的TASK_ID，判斷這些TASK_ID是否在TM_TASK_T表中存在？

增量的思路是讓人興奮的，但是如何做到增量呢？現有的模型結構是否能支撐增量方案呢？

說到增量，最先讓人想到的是時間字段，在HIS表中有CREATION_DATE和LAST_UPDATE_DATE，我們一般都會用LAST_UPDATE_DATE來識別增量數據。 HIS表中的LAST_UPDATE_DATE字段能用作本次的增量識別依據嗎？

經過一番論證，發現不能作為增量識別依據，理由是：當數據從當前表寫入到歷史表時，LAST_UPDATE_DATE字段值是不會發生變化的，也就是說寫入到歷史表的LAST_UPDATE_DATE值是 不連續 的，自然就 不能用作增量識別依據 。除非我們在寫入到歷史表的時候，將LAST_UPDATE_DATE的值賦為sysdate，但是顯然是不允許的，因為這樣一改寫，就破壞了數據的原始性。

LAST_UPDATE_DATE不行，CREATION_DATE就更加不行了。難道增量方案就這樣夭折了嗎？

5 山重水復疑無路，柳暗花明又一村

目前，TM_TASK_HIS_T表缺少這樣一個字段：能識別出數據寫入到歷史表的時間。如果有這樣一個字段，我們就能用來作為增量識別的依據。既然沒有，那么我們就可以 新建這樣一個字段 ，一方面記錄了數據被清理到歷史表的時間，以便后續核查；另一方面也滿足了增量識別的需求。 兩相歡喜，何樂而不為呢？

一開始，開發同事也不反對，緊接著他好像覺察到了什么？很是抵制，連說三個 “ 不行 ” 。原因是增加了這個字段后，他需要同步修改涉及到新增字段的代碼。我淡淡的說：不用擔心，在創建字段的時候， 增加sysdate的默認值 就行了。

6 總結

這個優化案例很簡單，但是過程卻有些曲折漫長，也顯得有些另類，因為這不是一個典型的基于SQL的優化案例，它最終是通過優化業務邏輯來滿足了性能需求，當然為了支撐這個業務邏輯優化，又涉及到了模型、SQL的變更。這個案例的意義在于它的整個過程涵蓋了因SQL引發性能問題的解決方案的 全路徑：調整執行計劃—>改寫等價SQL—>優化技術方案—>優化業務邏輯。

在SQL優化的過程中，我們都習慣性地止步于改寫等價SQL，一方面，80%以上的問題都能通過這兩步來完成，另一方面這兩步我們擁有 完全的控制權 。然而，當我們走完前兩步仍然沒有解決性能問題的時候，就需要考慮再往前邁一步，走出這一步，或許就海闊天空了。而邁出這一步是艱難的，原因如下：

1. 邁出這一步后，我們就需要與更多的人員溝通，這是SQL優化人員的普遍通病， 寧愿自己花10個小時悶頭苦干，也不愿意花一分鐘與相關人員溝通；

2. 邁出這一步后，我們就失去了優化的完全控制權，我們需要面臨利害相關人員的 干預，甚至抵制 ，他們會說：這不行，那也不行。因為我們的優化方案會導致他們做適應性修改，觸動了他們的 利益堡壘 ；

3. 邁出這一步后，我們就需要有一顆強大的心臟，隨時準備與相關人員PK，接受他們對優化方案的 質疑 ，并不斷 完善優化方案；

4. 邁出這一步后，我們就進入了一個利害糾紛的圈子，為了實施我們的優化方案，就需要與涉及到優化方案的相關人員 博弈。

 

來自：http://dbaplus.cn/news-21-798-1.html