摘要：現在商用最佳化器大多都是基於統計資訊進行查詢代價評估，因此統計資訊是否實時且準確對查詢影響很大，特別是分散式資料庫場景。本文詳細介紹GaussDB(DWS)如何實現了一種輕量、實時、準確的統計資訊自動收集方案。
本文分享自華為雲社群《【最佳實踐】GaussDB(DWS) 統計資訊自動收集方案》，作者： leapdb。
一、統計資訊收集痛點
- 何時做analyze，多做空耗系統資源，少做統計資訊不及時。
- 多個資料來源併發加工一張表，手動analyze不能併發。
- 資料修改後立即查詢，統計資訊實時性要求高。
- 需要關心每張表的資料變化和治理，消耗大量人力。
二、基本功能介紹
三、自動收集方案
GaussDB(DWS) 支援統計資訊自動收集功能，主要解決統計資訊收集不及時和不準確的問題。
手動取樣：使用者在作業中，手動發起的顯示analyze。
輪詢取樣：autovacuum後臺執行緒，輪詢發起的analyze。
動態取樣：查詢時，最佳化器觸發的runtime analyze。
前臺動態取樣：負責統計資訊實時準確，資訊放記憶體（有淘汰機制），一級鎖（像查詢一樣輕量）。
autoanalyze=on; autoanalyze_mode='light';
後臺輪詢取樣：負責統計資訊的持久化，寫系統表（四級鎖），不要求特別及時。
autovacuum_mode=mix或analyze; --- 以前只有“後臺輪詢取樣”，都由後臺autovacuum執行緒控制做vacuum或analyze。 --- 後來開發“前臺動態取樣”，叫autoanalyze。 --- 請注意二者的區別。
二者都需要開啟。
替代場景
統計資訊基於收集時表資料生成，資料變化較多後可能失效。自動觸發也是基於閾值（50+表大小*10%）。
總結：
- 小表變化<10%且資料特徵變化明顯，需要“調低閾值自動收集”。
- 調整過取樣大小且實時性要求高的場景，需要“主動收集統計資訊”。
- 外表和冷熱表因訪問效能問題，不支援自動，需要“主動收集統計資訊”。
四、如何保證及時觸發
【觸發條件】“無統計資訊” or “表的修改量超過一定閾值（預設“50 + 表大小 * 10%”）”
【觸發場景】含stream計劃的SQL都可觸發動態取樣，包括select和帶條件的delete, update。
【修改計數】
1. 哪些修改行為會被記錄？
DML: Insert, Update, Delete, Copy, Merge，會累加修改計數。
DDL: truncate table，truncate/exchange/drop partition, alter column type, alter distribute，由於CN無法獲取DN修改計數，所以直接記錄一個超大修改計數。
2. 跨CN查詢場景，如何確保修改計數全域性一致？
非同步廣播：autovacuum後臺執行緒輪詢檢查時，向所有CN廣播全域性修改計數。修改計數達2/3時廣播一次，此後每增10%再廣播一次。
實時廣播：單SQL修改超過tuple_change_sync_threshold(預設1W)條時，直接實時廣播修改計數到其它CN。
總結：“修改計數記錄”和“修改計數廣播”，覆蓋都比較全面，能夠保證查詢及時觸發動態取樣。
五、最佳實踐
GaussDB(DWS) analyze使用指南8.1.3及以下版本
GaussDB(DWS) analyze使用指南8.2.0及以上版本
1.事務塊中手動analyze堵塞其它業務
【業務場景】
BEGIN; ANALYZE t_ucuser; INSERT INTO t_user_name(project_id, account_id, name_id, uid, etl_time) with t1 AS ( select project_id, account_id, name_id from t_user_name WHERE uid is null or uid = '' ) select a.project_id,a.account_id,a.name_id, b.user_name AS uid, CURRENT_TIMESTAMP AS etl_time from t1 a join t_ucuser b ON a.project_id = b.project_id AND a.account_id = b.account_id ON CONFLICT(project_id,account_id,name_id) DO UPDATE SET project_id=excluded.project_id, account_id=excluded.account_id, name_id=excluded.name_id, uid=excluded.uid, etl_time=excluded.etl_time; END;
【問題根因】
a. 某資料湖使用者，多個資料來源按照不同的分割槽進行資料匯入加工。
b. 事務塊中有手動analyze，且事務塊中後面的查詢長時間執行不完。
c. 因analyze對錶加四級鎖長時間不能釋放，導致其它相關表上的業務等鎖超時報錯。
【解決方案】開啟light動態取樣，去掉事務塊中的手動analyze。
2. 多資料來源併發加工同一張表的不同分割槽
【業務場景】
為了保證使用者查詢表總有資料，需要把加工過程放到一個事務裡面。堵塞其它人的動態取樣。
begin; alter table tab_partition truncate partition P2023_03; insert into tab_partition select * from t1; end;
【問題根因】alter table truncate parition對分割槽加8級鎖，事務過程中長時間持鎖。
【解決方案】使用exchange partition
CREATE TABLE IF NOT EXISTS tab_tmp1(like tab_partition INCLUDING DROPCOLUMNS INCLUDING DISTRIBUTION INCLUDING STORAGE INCLUDING RELOPTIONS); INSERT INTO tab_tmp1 SELECT * FROM t1; ALTER TABLE tab_partition exchange partition (P2023_03) WITH TABLE tab_tmp1;
3.多表併發反序analyze導致統計資訊收集失敗
【業務場景】
a. 某銀行客戶，多個表進行批處理資料加工，開啟了normal型別動態取樣。
b. 查詢A先對t1表觸發動態取樣，再對t2表觸發動態取樣。
c. 查詢B先對t2表觸發動態取樣，再對t1表觸發動態取樣。
d. 觸發動態取樣的順序不一致，互相申請四級鎖導致申鎖超時，統計資訊未收集。
【問題根因】多人同時按不同順序analyze多表導致死鎖。
【解決方案】開啟light動態取樣，僅加一級鎖不再有四級鎖衝突。
4.剛匯入的資料不在統計資訊中導致查詢計劃差
【業務場景】
a. 某財經使用者，按照月度視為會計期，月初時匯入少量資料，然後馬上查詢。
b. 觸發了動態取樣，但採集不到最新會計期的少量資料。
【問題根因】新插入資料佔比小，及時觸發了動態取樣但採集不到，導致估算偏差大。
【解決方案】
a. 開啟統計資訊推算enable_extrapolation_stats功能，根據上一個會計期的統計資訊推算當前會計期資料特徵。
b. 不提高取樣大小，利用歷史資訊增強統計資訊準確性。
5.隨機函式質量差導致資料特徵統計不準
【業務場景】
a. 某銀行客戶，按月度條件進行關聯查詢
b. 多次analyze，最多資料月份在MCV中佔比從13%~30%大幅波動
c. 詳細輸出樣本點位置和取樣隨機數發現，隨機數（小數點後6位）生成重複度高導致取樣扎堆兒嚴重。
【問題根因】取樣隨機數不夠隨機，樣本採集不均勻導致MCV資料特徵統計偏差。
【解決方案】
a. 每次傳入隨機種子再生成隨機數，提高隨機性和併發能力。控制引數random_function_version。
b. 不提高取樣大小，提升隨機數質量增強統計資訊準確性。
6.樣本分佈不均勻導致資料特徵統計不準
【業務場景】
a. tpc-h的lineitem表l_orderkey列，資料每4~8條批次重複。即同一個訂單購買多個商品。
b. 傳統取樣演算法由於取樣不均勻，採集到的重複資料稍多，導致採集的distinct值偏低。
【問題根因】資料特徵分佈不均勻，取樣無法抓準資料特徵，distinct值高的場景統計出的distinct值偏低。
【解決方案】
a. 使用自研的最佳化蓄水池取樣演算法，控制引數analyze_sample_mode=2，讓取樣更加均勻，以提升統計資訊準確性。
b. 如果上述方法沒有達到預期效果，可以手動修改distinct值。
select APPROX_COUNT_DISTINCT(l_orderkey) from lineitem; --近似計算distinct值 alter table lineitem alter l_orderkey set (n_distinct=10000); --手動設定distinct值，然後再analyze即可。
點選關注，第一時間瞭解華為雲新鮮技術~