一條update語句的優化探索

今天經開發同學反饋，發現有一些update語句阻塞了部分業務流程，為什麼說一些而不是一條，是因為這些update語句都在一個儲存過程中，語句結構相仿，真有一種一榮俱榮，一損俱損的感覺。而比較糾結的是這樣的update語句有差不多10個。從我收到反饋到觀察分析，裡面的第一條update語句執行了近5個小時，還沒有完成，從SQL Monitor的報告來看，似乎進度甚微，按照這個進度，這些語句的執行時間會非常驚人。

我先拿到了一個初步的報告。

概覽資訊如下：

這條語句從生成的執行計劃來看，簡直完美，但是執行時間卻差強人意，所以由此來看是執行計劃出現了巨大的偏差。這個時候SQL Monitor是一個利器，可以真實還原問題時段的執行計劃情況。

如果看上面的執行計劃，其實看起來消耗也不大，好像都走了索引，在這樣的一個評估值的情況下，可見資料集的變化不大。而問題就在於右邊的部分。

紅色的小框處標出的資訊，可以看出實際得到的結果集非常驚人，結果集行數都是4G，這是一個什麼級別的概念。所以這個語句的瓶頸就在這個地方。

我們來看看語句：

這個語句看起來還是比較複雜的，兩個相關的表都是千萬級別，紅色的部分就是涉及的關鍵部分，都涉及到vip_recharge_log這張大表。從執行計劃來看是在這裡出了問題。

vip_recharge_log對應的索引資訊如下：

可以看出這個語句是根據時間欄位來做的資料過濾。這種方式為什麼效能低效呢，和between的部分有著重大的關係。

時間跨度有多大呢，可以通過如下的表示式來得到一個時間範圍。

這是取近半年的資料結果，對於一個OLTP的千萬級表來說，全表掃描的代價其實要更低一些。這樣SQL在執行的過程中先根據時間欄位來過濾得到一個極大的結果集，然後在這個基礎上去根據id得到一個極小的結果集。這種方式簡直是百害而無一利。如果根據id得到一些客戶的資訊，因為本身結果集就小很多，在這個基礎上再根據時間來過濾，那效率會大大提高，在目前的這個場景中可以看見明顯的效能問題。

所以初步的評估就是重構索引。目前的索引是根據時間欄位或者根據id來建立索引，其實可以考慮複合索引，根據id，時間欄位來過濾資料，成本相對要低很多。所以考慮建立一個新的索引

CREATE INDEX "IDX_VIP_RECHARGE_MIX" ON "VIP_RECHARGE_LOG"
       (CN,CHARGE_DATE )  ;

這樣資料過濾的效果就會好很多。這個瓶頸能夠化解了，其它的幾個問題也就引刃而解。

所以在這種場景下，不修改SQL語句，調整索引就預估達到極大的效能提升。而對於此還是需要很謹慎的，我複製了表中的資料，在另外的環境進行了快速的復現，執行計劃的效率大大提高。在這個基礎上，考慮新增了並行，雖然會消耗伺服器的資源，但是能夠極大提高效率，這些付出也是合理的。在這些簡單調整之後，再次測試執行語句，1分半鐘就能夠順利完成。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/23718752/viewspace-2122951/，如需轉載，請註明出處，否則將追究法律責任。