數據庫應用設計優化淺談

收藏待读

數據庫應用設計優化淺談

數據庫應用設計優化淺談

數據庫應用設計優化淺談

作者簡介

劉晨

中航信研發中心 運維經理

數據庫應用設計優化淺談

前言:眾所周知對於 OLTP 的交易系統最重要的操作就是數據庫的CRUD,數據庫層面或者SQL優化的程度,對於整個系統的並發處理能力起到至關重要的作用。

很多朋友都會碰到這樣的問題,系統運行初期,數據庫層面運轉非常穩定,SQL處理能力也很強。當業務發展一定階段,SQL語句性能變差,導致應用並發處理能力下降,究其原因,可能是資源的問題、環境的問題、還可能是數據庫設計的問題。

今天我們選擇某個核心交易系統歷史過期數據刪除的邏輯優化案例。通過一些介紹了解業務發展的不同階段,舊數據的刪除邏輯是如何演進的,以及不同演進過程當中有哪些設計優化的經驗。我分為以下四個部分做分享。

一、案例背景

數據庫應用設計優化淺談 首先我們看一下需求背景,這是一套核心的OLTP的交易系統,這套應用背後有二十幾張關聯的主子表,表中數據過期數據要及時清理。

數據庫應用設計優化淺談 開發人員用java寫了刪除的程序,我們希望每天六點業務高峰期之前完成數據清理工作。

二、問題描述

數據庫應用設計優化淺談 接下來我們看下問題。隨着業務的推廣,相應數據刪除執行時間逐漸變長,從最開始只需要三十分鐘,逐漸增加到兩小時、五小時、甚至十幾個小時。

數據庫應用設計優化淺談

經過分析有一張終極情況下5000萬記錄的子表B,刪除效率比較低,屬於這個問題的主要矛盾。終極情況下主表A會有2000萬數據,存儲七天之前到未來兩天的數據,子表B關聯主表的ID。子表B每天刪除七百萬,對於主表A來說需要從子表找到需要刪除的記錄。

數據庫應用設計優化淺談 同時因為主表A下面有一系列的子表,我們最開始設計是按串型的方式逐一刪除每一張子表,再刪除主表。

三、細察深剖

數據庫應用設計優化淺談 我們會從幾個階段來看看針對數據刪除的邏輯我們是如何演進的,如何採用不同的方案進行優化。

數據庫應用設計優化淺談 首先階段一業務投入初期,業務量非常有限,開發人員使用SQL通過A表篩選出來七天之前的ID記錄,然後和子表B做一個關聯刪除B表中的數據,同時有rownum作為條件,限制一次刪除交易的數據量,避免大事務的產生。

因為子查詢當中只存在十天的數據,因此採用了全表掃描的執行計劃,根據之前的數據量預估推測,它需要執行七百次的A表全表掃描。這個階段來說業務量有限,數據庫的配置比較高,因此執行時間,可以接受。

數據庫應用設計優化淺談

系統投產初期,數據量很有限,這個階段,其實任何滿足刪除邏輯需求的SQL都是可以接受的,但是它的隱患非常明顯,因為我們對一張大表執行了全表掃描,執行時間一定會隨着數據量的增加而變長。

數據庫應用設計優化淺談到了階段二,業務量有所增加,這時候 SQL 執行時間也變長了,為了提升效率我們做了一些優化。第一個思路就是能不能少做一些事情,創建一張中間表 C ,存儲的是在主表 A 中要刪除數據的 ID 值,通過中間表 C 再和子表 B 做關聯,它和階段 1 的差別是什麼 ?階段 1 是做 700 2000 萬數據的 全表 掃描,現在只需要做 700 200 萬數據的 全表 掃描, 並將刪除程序 改為並行執行, 能同時刪除多張子表

數據庫應用設計優化淺談相比階段一,它把對於子表的刪除由串型改成了並行, 同時全表掃描的數據量有所下降 性能 會有一定的 提升,但是 隱患還在,因為還是對兩百萬的表進行了全表掃描,執行時間還是隨着業務的推廣逐漸增加。

數據庫應用設計優化淺談

到了階段三,這個時候業務發展比較迅猛,業務量接近終級的狀態了,這時候夜維執行時間超出了需求,現在的優化目標就是能不能避免子表全表掃描的操作,我們通過下面四步操作來看一下,首先在創建的中間表C中增加pkid字段,即將原先B表每次批量刪除1萬條的限制,推至內層循環,以讓C表使用索引,避免全表掃描。例如,第一次子查詢pkid的字段是1到10001,接下來就是10001到20001。

根據業務的評估,C表中一個ID,對應子表B兩到三條。雖然和之前相比一次刪除的數據量增加了,但是量級上基本可控。

這個階段我們的方案通過pkid的索引避免子查詢的全表掃描,雖然一次刪除B表的數據多了,但相應的執行次數減少了。如果按照我們最初的分析,這種方案應該是比較完美了,可以解決我們之前的問題。

數據庫應用設計優化淺談

數據庫應用設計優化淺談

數據庫應用設計優化淺談

但是,現實和理想是有差距的。我們在上線當晚就報錯了,從日誌當中看到它拋了一個ORA-01555的錯誤。這是一個非常經典的錯誤號。原因就是在做數據檢索的時候,有數據變化,我們需要從UNDO檢索數據,做一致性讀,如果SQL執行空間比較長,UNDO中的鏡像就可能被其他事務覆蓋,這時候就會拋出ORA-01555的錯誤。

數據庫應用設計優化淺談 對於C表它採用了索引範圍掃描,用到了索引,但是對於子表B執行了全表掃描,正是因為全表掃描才導致SQL執行時間非常長,才讓程序出現了錯誤。

數據庫應用設計優化淺談

數據庫應用設計優化淺談

現在問題來了,是不是因為子查詢一萬數據太多了?我們嘗試將子查詢縮小到五千、兩千五以及十一個數據,它依然沒有改變。偶然的是,我們嘗試 1910001到1920001的參數值時,發現執行計劃變了,對兩表進行了索引掃描,以及嵌套循環連接,這才是我們真正需要的執行計劃。

數據庫應用設計優化淺談

現在問題又來了,這是為什麼?通過分析,pkid區間1-10001對應B表a_id的值比較無序,而且間隔較遠,pkid區間1910001-1920001對應B表a_id的值比較有序,間隔較近,由此,我們聯想到Oracle中Clustering Factor聚簇因子這個概念。他表示的是索引鍵值的排列順序,和對應表中數據排列順序的相近程度。

通過一個索引掃描一張表時需要訪問的表的數據塊的數量。反映了索引範圍掃描可能帶來的對整個表訪問過程的IO開銷情況。如果值越小(接近表的數據塊數量),說明表中數據是有序的,同一個索引葉子結點上相鄰的索引鍵值,對應的表中記錄可能位於相同的數據塊上,(表是按照索引字段的順序存儲),相應根據索引,回表檢索數據,就會消耗更少的物理IO。

如果值越大(接近表的行數),說明表中數據是無序的,同一個索引葉子節點上相鄰的索引鍵值,對應的表中記錄就可能位於不同的數據塊上,(表不是按照索引字段的順序存儲),相應根據索引,回表檢索數據,就會消耗更多的物理IO。

我們通過圖示,可以更加形象的理解,這張圖是聚簇因子比較小的示意圖,我們看一個索引葉子節點上,索引鍵值對應到表的記錄,可能位於相同的數據塊,組織有序。

數據庫應用設計優化淺談

這張圖是聚簇因子比較大的示意圖,同一個索引葉子結點上,相鄰的索引鍵值,對應表中的記錄,可能位於不同的惡數據塊,組織無序。相同一次檢索,回表需要消耗更多的物理IO。

數據庫應用設計優化淺談

我們再用圖示,看下pkid在1-10001和1910001-1920001兩個區間,可能的情況,1-10001對應B表待刪除a_id索引,可能位於不同的數據塊上,1910001-1920001對應B表待刪除a_id索引,可能位於有限個相鄰的數據塊,因此前者檢索B表的時候,有可能全表掃描的成本,就要低於索引範圍掃描,而後者索引範圍掃描的成本,低於全表掃描,所以pkid區間不同,導致B表掃描成本的不同,因此選擇了不同的執行計劃。

數據庫應用設計優化淺談

我們知道了原因,應該如何改造?很明顯,目標是讓 B 表一次刪除的 a_id 盡量接近。原先 C 表構造,只是根據時間 ,將符合條件的 A 表記錄,隨機插入 C 表,現在我們加上 order by id ,保證插入 C 表的時候,是按照 id 字段排序存儲,相當於按序構造了表 C

數據庫應用設計優化淺談從優化效果來看 ,子表 B 在優化之前都需要兩個小時到三個小時的刪除時間,優化之後,基本上五十分鐘就可以完成數據的刪除工作。

數據庫應用設計優化淺談 子表B是屬於優化主要矛盾,解決主要矛盾之後,相應夜維的總體執行時間也有所下降,原先需要四個小時以上的時間完成清理,現在僅需要兩個半小時時間就可以完成所有數據清的理工作。

上面是我們對數據刪除的演進過程進行了說明。

四、總結回顧

數據庫應用設計優化淺談最後我們簡單總結回顧一下問題。首先我們投產初期,只要 SQL 語句符合需求,在執行時間上就可以。之後全表掃描的隱患就會逐漸暴露出來,我們通過將串行改為並行,同時增加了 pkid 字段,旨在使用索引。

數據庫應用設計優化淺談

但是,實際過程中pkid的數據分佈變成了亂序的,進而導致使用索引掃描的成本值非常高,無法使用索引,還出現了ORA-01556的報錯。明確了原因之後解決方案也很簡單。我們通過指定排序構造中間表,來降低索引訪問的成本。

除了這些優化操作外,或許可能有其他的方法,比如使用引用分區特性、刪除外鍵約束並發執行,其實對於一些優化的工作,方法可能不止一種,殊途同歸,選擇你最熟悉、最易操作的執行就好,畢竟技術是為業務服務的。

數據庫應用設計優化淺談

通過案例我們也做了一些思考。

第一個就是好架構不是設計出來的,而是演進來的,對於數據庫應用的邏輯一樣如此,對於不同的階段,可能會有不同的邏輯調整。

第二點,在優化過程中,首先要抓住主要矛盾,不能眉毛鬍子一把抓,要明確主要的矛盾,作為首要的目標去解決。

第三個,就是無論對於開發人員還是架構師來說,我們不能把數據庫當做一個黑盒,我們理解越全面、越深入,越可以理解和發現問題。

最後一個就是對於一些性能隱患,是可以提前避免的,例如大表的全表掃描,它的執行時間,一定會隨着數據量的增加而增長。

咱們這次會議的主題,是自動化運維、智能運維。對於這些隱患,現在業界也有了像SQL審核工具這種產品,無論是開源的,還是商業的,通過自動化的方式,提前預警。

數據庫應用設計優化淺談 我們的團隊,也在做這方面的探索,正在自研我們自己的數據庫智能審核分析平台 Sherlock ,希望他可以像神探夏洛克一樣,幫助我們找出數據庫開發的隱患,輔助開發人員、 DBA ,進行數據庫開發的工作,踐行 DevOps ,希望未來有機會,分享出來這方面的工作。以上就是我今天的分享。感謝各位。

AIOps,DevOps?

運維行業的風向標

想知道

2019 運維行業必關注的行業趨勢?

4.12-13 ,我們在深圳等您

數據庫應用設計優化淺談

點擊閱讀原文,了解 GOPS 2019 · 深圳站更多精彩

原文 :

相關閱讀

免责声明:本文内容来源于mp.weixin.qq.com,已注明原文出处和链接,文章观点不代表立场,如若侵犯到您的权益,或涉不实谣言,敬请向我们提出检举。