林春-金融數據庫轉型方法與實踐(0103).pdf

1、/2024.01.04/金融數據庫轉型方法與實踐演講人中國太保數智研究院首席數據庫專家林春金融數據庫轉型方法論PostgreSQL最佳實踐PostgreSQL表膨脹預識別與優化CONTENT金融數據庫轉型方法論/2024.01.04/數據庫能力建設整體框架數字化轉型能力建設分布式數據庫運維平臺故障隔離彈性擴、縮容批量作業全棧故障定位異地容災定制化適配同城雙活一鍵安裝部署補丁包升級自動化運維數字化轉型遷移支持平臺冗余索引瘦身冗余表瘦身失效對象識別日志負載畫像高開銷 SQL 畫像預掃描識別SQL 審核平臺執行計劃檢查多表連接檢查低效 SQL 檢查字符集檢查分布式設計檢查基礎賦能平臺基礎PaaS 運

2、維服務 安裝部署服務 遷移服務定制化數據庫培訓 測試支持 性能優化 架構設計 問題咨詢規范文檔建設 開發規范 安裝部署規范 運維規范分布式域名規范核心攻堅支持周邊工具適配 集群設計規范Oracle 遷移 手冊 監控閾值說明 問題排查手冊報表及導數性能數據庫配置優化核心攻堅關鍵點梳理及目標數據庫選擇架構方案規劃遷移、切換方案關鍵模塊性能提升數據生命周期管理資源規劃及資源彈性擴縮容全鏈路故障快速定位分布式架構、應用設計核心能力提升數字化轉型攻堅大講堂新員工入職數據庫培訓聯合數字化轉型測試實驗室 知識庫體系建設數字化轉型路線指引 硬件資源計算模型規劃、管控 應用改造工作量模型 開發調優手冊同城雙活異

3、地容災能力建設故障隔離及范圍控制快速遷移、數據比對、數據回寫 軟硬件、服務成本估算模型/2024.01.04/數字化轉型改造降本方法論高頻高邏輯讀SQL優化；游標未關閉泄露排查；數據庫瘦身等 數字化轉型成本優化技術路線數字化轉型賦能（杠桿）應用改造測試成本遷移優化硬件成本架構設計綜合軟硬件成本、業務場景需求、產品收斂原則問題識別SQL 優化改造 Oracle 側前置太保應用預掃描工具“指南針”經營化，預期問題識別環節降本 50%研發優化輔助工具，結合 SQL 審核、調優培訓和開發規范，預期提升優化環節人工成本 30%影響遷移性能，不兼容的關鍵卡點 降C縮容評估 數據庫降 C 縮容存儲利舊硬件資

4、源估算模型大對象從數據庫拆離存儲推廣國產數據庫認證、數字化轉型攻堅大講堂案例分享、知識庫沉淀依賴重AP場景拆離到數據中臺PostgreSQL設計實踐7分布式表類型描述表設計類型注意事項基礎代碼表包括險種、部門、機構等信息，數據量較少，DML操作不頻繁，存在與其他業務表較多連接。建議設計為復制表。以減少跨節點開銷單表數據量較少，創建索引與全表掃描性能差異較小，但是由于存在與其他大表連接，有無索引性能會有放大效應，建議按照業務實際需求在連接字段創建索引；對復制表的高頻更新、刪除會存在較大開銷且可能導致分布式死鎖。業務流水表業務流水表跟業務本身性質、保單跟險種性質相關，包括財務流水表、事件流水表、申

5、請流水表等，數據量通常較多。建議設計為分片表。單片數據量不超過32GB。對于索引需要根據數據分布、sql訪問特點、表連接字段等構建索引組合，實現以盡可能少的索引滿足盡可能多的數據讀取要求；對于更新頻繁的表需要考慮將表的fillfactor因子降低；對于通常23年歸檔的事件流水表等需要考慮分區、歸檔需求。臨時表系統臨時表對臨時數據進行處理；業務臨時表 用于簡化交易統計。建議設計為分片表。PG基座數據庫自身的臨時表類型存放在CN節點，性能不及普通表，禁止使用，建議替換為普通分片表，使用前做truncate操作。中間表數據加工過程中用于核對。根據數據量和使用特點選擇合適類型。日志表主要用于階段性用途

6、，生命周期結束后不再使用。根據數據量和使用特點選擇合適類型。需要考慮及時對日志表進行分區，便于歸檔，以降低冗余數據成本。流程狀態中間表記錄流程記錄中間狀態,包括流程驅動所有環節。根據數據量和使用特點選擇合適類型，審計、合規使用，一般回溯周期在五年以內。需要根據數據生命周期，制定相應數據分區、歸檔策略。運營數據報表根據數據類型，選取合適的分區表。根據數據量和使用特點選擇合適類型，推薦全局表。針對日報、月報、年報等類型，創建不同的分區表。歷史數據表存放例如流水歸檔等歷史數據，用于回溯。建議設計為分片表，再做一層分區表。根據業務字段做分片，再使用時間字段做range分區，便于歸檔。常見業務表設計參考

7、表常見業務表設計參考表/2024.01.04/數據庫表設計使用規范業務表推薦增加insertime、updatetime時間戳字段，insertime用于保存插入時間、updatetime用于保存記錄修改時間。對于更新（update/delete）頻繁的數據表，要降低其填充率，通常降低到80%，對于頻繁更新的數據表，設置更小的填充率，在剛開始時占用空間雖然更大，但更新后空間幾乎沒變化，而且并發性能更好。autovacuum_vacuum_scale_factor=0.0002；autovacuum_vacuum_threshold=50；默認值：表變動的數據行超過50行，并且占總行數比例超過0

8、.02%時，autovacuum程序就會掃描該表，進行回收，通常情況下這組參數足夠適配大部分場景；對于數據量比較小并且高頻更新的表，可以配置更小的回收頻率；/2024.01.04/數據庫索引設計建議使用Btree 索引，不推薦使用GIN索引；創建索引可以加上CONCURRENTLY關鍵字在線創建索引，以避免阻塞其他會話的DML操作，但會阻塞DDL操作。但是在線創建索引會涉及到全表掃描，資源開銷較大，對于數據量較大的表，建議在業務維護窗口以離線的方式創建索引。在線創建索引語法如下：CREATE INDEX CONCURRENTLY t1_f1_idx ON t1(f1);對固定條件的（一般有特定

9、業務含義）且選擇比好（數據占比低）的query，創建帶where的索引；對經常使用表達式作為查詢條件的query，使用表達式或函數索引加速query；不要建過多index，不要超過6個，超過需要評審合理性；通過查詢系統視圖pg_stat_all_indexes可以評估索引使用情況；對于大的文本字段，如超過8k，則建立索引往往效果不好，而且pg_trgm_gin索引還不支持字段長度超過8K；/2024.01.04/數據庫SQL開發最佳實踐如果需要向某個表一次插入多條記錄，推薦使用value(),()方法，因為這種操作可以減少應用與數據庫之間的交互次數，減少系統開銷。如果業務需要判斷一條記錄存在時

10、就更新記錄，不存在時就插入記錄，推薦使用on conflict方法，該方法類似于Oracle中的merge語句，適用于大量插入并且有少量更新的場景。如果業務需要在插入記錄后，返回該插入的記錄，推薦使用insert.returning方法，因為這種操作可以減少應用與數據庫之間的交互次數如果業務需要在刪除記錄后，返回該刪除的記錄，推薦使用delete.returning方法，因為這種操作可以減少應用與數據庫之間的交互次數，而且業務邏輯也是最簡單刪除一個表所有數據時禁止使用delete,強制使用truncate清除數據；truncate效率高、而且能直接釋放空間，但truncate的數據無法同步到k

11、afka，如果數據需要同步到數據湖，則可以先delete數據后，再truncate 數據表；大批量數據delete時推薦使用分批方案，減少行鎖數，對業務影響最小化。如果業務需要在更新記錄后，返回該更新的記錄，推薦使用update.returning方法，因為這種操作可以減少應用與數據庫之間的交互次數；推薦使用不等值條件做篩選，避免對相同字段值做無效更新；大批量數據update時推薦使用分批方案，減少行鎖數，對業務影響最小化。PostgreSQL表膨脹識別與優化-12-數據庫表膨脹數據庫表膨脹影響測試影響測試n PG未使用UnDO表空間實現MVCC，在更新時對原記錄做刪除標記，然后在相同數據文件

12、再插入新的記錄實現。對于有頻繁更新的表，需要關注“表膨脹問題”。n 表膨脹對表數據存儲空間和索引空間影響都很大，而索引空間的影響比對表更大，一張表數據全部更新，表空間膨脹略小于100%，而索引接近120%，表和索引空間膨脹會影響范圍掃描及全表掃描性能。n 49GB的理賠政策表更新100%記錄后，離線回收空間時間約為47分鐘。n 3張典型測試表使用數據庫畫像腳本在產險車險理賠中臺系統篩選，信息如下：表名特征說明數據量測試內容理賠政策表test_attr_policy大表，更新不頻繁2.5億行不同更新比例下的表膨脹情況Vacuum情況外部合同表test_external_contact_detai

13、l更新頻繁100萬行不同更新、插入、刪除情況下的表膨脹情況不同填充因子下更新的表膨脹情況chat記錄表test_chat_record寬表含clob字段100萬行不同更新比例下的表膨脹情況-13-PGPG基座數據庫表膨脹基座數據庫表膨脹測試測試表名測試內容表大小MB索引大小MB更新比例更新時間更新后表大小/MB表膨脹比例更新后索引大小/MB索引膨脹比例理賠政策表test_attr_policy不同更新比例下表膨脹情況2457616384100:12:07266248.33%174086.25%2457616384200:20:452867216.67%1945618.75%2457616384

14、501:06:143686450.00%2560056.25%24576163841002:13:094912599.89%35840118.75%理賠政策表test_attr_policynosharding模式不同更新比例下表膨脹情況2457616384100:12:21266248.33%174086.25%2457616384200:20:582867216.67%1945618.75%2457616384501:06:453686450.00%2560056.25%24576163841002:13:314912599.89%35840118.75%理賠政策表test_attr_po

15、licy100%更新后執行Vacuum Full Table4912535840-0:47:0024576-49.97%16384-54.29%chat記錄表test_chat_record不同更新比例下的表膨脹情況777241100:00:108316.95%29823.65%777241200:00:2087913.13%37656.02%777241500:01:07107638.48%45488.38%7772411000:01:38128865.77%540124.07%-14-PGPG基座基座數據庫表膨脹數據庫表膨脹測試測試表名測試內容表大小MB索引大小MB更新比例更新時間更新后表

16、大小/MB表膨脹比例更新后索引大小/MB索引膨脹比例外部合同表test_external_contact_detail不同更新比例下表膨脹情況60515710%0:00:086578.60%19322.93%60515720%0:00:1570917.19%24757.32%60515750%0:00:3786542.98%28984.08%605157100%0:01:18112585.95%352124.20%1125352100%更新基礎上再更新20%0:00:5811250.00%3612.56%外部合同表test_external_contact_detail填充因子90%時更新情況

17、675206100%0:01:10117473.93%37983.98%填充因子70%時更新情況876206100%0:00:43124041.55%30246.60%混合update、delete、insert 比例按 5:1:1 更新情況605157100%0:00:3791851.74%30191.72%填充因子90%時混合update、delete、insert 比例按 5:1:1 更新情況605205100%0:00:3991851.74%32458.05%-15-表膨脹優化建議表膨脹優化建議n 如果更新頻繁的大表存在長查詢和長事務，會影響空間的釋放及重復利用。n 更新頻繁的表如果平

18、均記錄長度較長或包含clob字段，可以考慮應用層面對表做拆分。n 在Oracle數據庫遷移到PG前，需要識別前20位大表中更新頻繁的表以及更新頻繁程度排在前20位的表，在遷移之前將相應表fillfactor設為80（默認100）n 在PG監控中，監控閾值評估時需要增加對表膨脹的監控（以天為維度）n 需要關注autovacuum_vacuum_scale_factor、autovacuum_vacuum_threshold的設置n 在做了大批量DML操作后，隨即執行vacuum analyze操作n 如果系統存在頻繁更新的大表、長查詢、長事務，空間回收問題也需要關注如果系統存在頻繁更新的大表、長

19、查詢、長事務，空間回收問題也需要關注，Vacuum full需要固定較長維護窗口及兩倍表的空間，這種情況不適合24*7的系統。DML類型并發tps維護時間窗口空間回收清理分析查詢為主，DML較少不存在“表膨脹”問題DML主要以insert為主，僅存在少量update不存在“表膨脹”問題查詢為主，存在一定量DML操作并發量不大非7*24應用，有足夠維護時間窗口每天有維護窗口進行VACUUM FULL操作DML較多，存在長查詢、長事務并發量較大非7*24應用，有足夠維護時間窗口每天有維護窗口進行VACUUM FULL操作DML較多，存在長查詢、長事務并發量不大7*24場景，存在較為空閑的維護窗口定

20、期在非業務高峰期進行VACUUM FULL，需對數據量及VACUUM FULL時間做評估DML較多，存在長查詢、長事務并發量較大7*24場景，存在業務量較小時間段窗口需要進行評估，避免VACUUM FULL操作影響業務DML較多且數據量較大，存在長查詢、長事務 并發量較大7*24場景，維護窗口時間較短需對應用做評估，使用需要對應用做一定改造-16-l用以抓取高負載時間段大事務SQL，便于遷移到OB或TD前提前優化l以小時為維度，一天什么時間段日志負載最大l以周為維度，一周周幾最繁忙l以月為維度，一個月那天最繁忙l用于識別未使用索引，進行瘦身l實例啟動以來，從未使用過的索引l超過5個索引的表識別

21、l未帶主鍵表識別l全局索引識別l識別實例啟動以來未修改過的表l識別實例啟動以來未訪問過的表l識別僅有插入、沒有更新、修改的大表l無效索引識別l失效對象識別l長查詢畫像l高CPU開銷SQL畫像l高IO開銷SQL畫像l高排序開銷SQL畫像數據庫畫像SQL畫像表瘦身畫像索引畫像表負載畫像日志負載畫像失效對象畫像l前20位大表每日更新、插入、刪除記錄數（按更新記錄數排序）、記錄總數、平均記錄長度、表空間大小、索引空間總大小l更新排名前20位表每日更新、插入、刪除記錄數、記錄總數、平均記錄長度、表空間大小、索引空間總大小遷移前數據庫遷移前數據庫畫像腳本功能畫像腳本功能-17-統一客戶認證系統用戶登錄歷史

22、表優化統一客戶認證系統用戶登錄歷史表優化n 優化效果：遷移時間縮短一半，PG側直接節省5T以上空間約2.25萬元+（不包括備份、容災、入湖、應用及系統運維成本，且不包括后續每年2.5T約1.125萬元動態增量成本）。n 用戶登錄歷史表包括100張根據客戶號分拆的子表，截止當前包含約50個月數據，總計空間約3.5T。在Oracle側增加一張表對超過兩年以上的用戶信息按月為維度收集登錄次數，并將超過2年以上的數據備份到備份一體機后清理。PG側增加每月定時任務，對用戶信息按月為維度收集登錄次數，并做數據清理。遷移前Oracle側歷史數據清理遷移前TDSQL-PG側工作應用下發2月11日前完成刪除需要循環批量提交，避免大事務腳本中實現腳本中實現THANK YOU