GaussDB(DWS)性能調優系列基礎篇一：萬物之始analyze統計信息

本文簡單介紹一下什么是統計信息、統計信息記錄了什么、為什么要收集統計信息、怎么收集統計信息以及什么時候收集統計信息。

1????? WHY：為什么需要統計信息

1.1????? query執行流程

下圖描述了GaussDB的SQL引擎從接收客戶端SQL語句到執行SQL語句需要經歷的關鍵步驟，以及各個流程中可能對執行產生影響的因素

1)??詞法&語法解析

按照約定的SQL語句規則，把輸入的SQL語句從字符串轉化為格式化結構(Stmt)，如果SQL語句存在語法錯誤，都會在這個環節報錯。

2)??語義解析

語義解析類似一個翻譯器，把外部輸入的可視化的對象翻譯為數據庫內部可識別的對象（比如把Stmt中以字符串記錄的表名稱轉化為數據庫內部可識別的oid），如果語句存在語義錯誤(比如查詢的表對象不存在)，數據庫會在這個環節報錯。

3)? ?查詢重寫

根據規則將“語義解析”的輸出等價轉化為執行上更為優化的結構，比如把查詢語句中的視圖逐層展開至最低層的表查詢。

4)??查詢優化

數據庫確認SQL執行方式、生成執行計劃的過程

5)??查詢執行

根據執行計劃執行SQL并輸出結果的過程

整個執行流程中，優化器決定了查詢語句的具體執行方式，對SQL語句的性能起著關鍵性的作用。數據庫查詢優化器分為兩類：基于規則的優化器(Rule-Based Optimizer，RBO) 和基于代價的優化器(Cost-Based Optimizer，CBO)。RBO是一種基于規則的優化，對于指定的場景采用指定的執行方式，這種優化模型對數據不敏感；SQL的寫法往往會影響執行計劃，不了解RBO的細則的人員開發的SQL性能不可控，因此RBO逐漸被拋棄，目前GaussDB等數據庫廠商的優化器都是CBO模型。CBO模型是根據SQL語句生成一組可能被使用的執行計劃，并估算出每種執行計劃的代價，最終選擇選擇一個代價最小的執行方式。

1.2????? CBO模型

數據庫執行SQL語句的時候，會把執行拆分為若干步驟，如下SQL

select?*

from t1 join t2 on t1.a=t2.b

where t1.b = 2 and t2.a = 3;

在具體執行的時候會拆分為表掃描和表關聯兩個主要查詢動作。這兩個查詢動作都存在多種執行方式，比如表掃描均存在SeqScan、IndexScan、IndexOnlyScan、BitmapScan等多種執行方式、表關聯存在NestLoop、HashJoin、MergeJoin三種執行方式，那么在具體的業務場景下什么樣的查詢動作才是代價最小的執行方式，這就是優化器的核心工作。

CBO主要工作原理是通過代價模型(Cost Model)和統計信息估算每種執行方式的代價，然后選擇一種執行代價最優的執行方式。這里面代價模型是核心算法邏輯，統計信息是cost計算的數據源，二者配合完成cost計算；如果統計信息缺失，計算時代價模型會使用默認值來計算cost，當然這時cost會跟真實值存在較大偏差，大概率會出現選擇非最優執行計劃的情況，因此統計信息是CBO模型中 cost計算的數據輸入，是CBO最核心的科技之一。

2????? WHAT：都有哪些統計信息

統計信息是指數據庫描述表或者索引數據特征的信息，常見的有表記錄條數、頁面數等描述表規模的信息，以及描述數據分布特征的MCV(高頻非NULL值)、HISTOGRAM（直方圖）、CORRELATION等信息。

本文中通過如下用例來展示統計信息是如何表現表的數據特征的

DROP TABLE public.test;

CREATE TABLE public.test(a int, b int, c int[]);

INSERT INTO public.test VALUES (generate_series(1, 20), generate_series(1, 1200));

INSERT INTO public.test VALUES (generate_series(1, 1200), generate_series(1, 1200));

UPDATE public.test SET c = ('{' || a || ','|| a || '}')::int[] WHERE b <= 1000;

UPDATE public.test SET c = ('{' || a || ','|| b || '}')::int[] WHERE b > 1000;

ANALYZE public.test;

3????? WHERE：統計信息在哪里

3.1????? 表規模信息

系統表pg_class中的reltuples和relpages兩個字段能夠反映表規模信息信息，其中relpages記錄了表數據存儲到幾個page頁里面，主要用于表從存儲接口掃描數據的代價計算；reltuples記錄了表記錄條數，主要用于掃描結果集行數估算。

查詢pg_class中的表規模估算信息，顯示表為2400行

單表全量數據查詢，通過explain查看表規模估算，顯示表掃描輸出行數估算為2400

3.2????? 單列統計信息

單列統計信息是指表的單列的數據特征信息，存儲在系統表pg_statistic中。因為pg_statistic會存儲一些關鍵采樣值來描述數據特征，因此pg_statistic數據是敏感的，只有超級用戶才可以訪問pg_statistic。通常我們推薦用戶使用查詢系統視圖pg_stats來查詢當前用戶有查詢權限的表的統計信息，同時pg_stats信息的可讀性更強，pg_stats字段信息如下

名稱

描述

schemaname

統計對象所在的namespace名

tablename

統計對象名

attname

統計列的名稱

inherited

如果為真，那么統計分析時采樣樣本包括繼承表數據

null_frac

該字段NULL值的個數比率

avg_width

該字段非NULL值的平均字節寬度

n_distinct

字段中非NULL值的distinct值。如果大于0，則表示實際distinct值個數；如果小于0，則它的絕對值表示distinct值占全部非NULL值個數比例。例如-1表示distinct值的數目與行數相同

n_dndistinct

第一個DN上該字段非NULL值的distinct值，取值含義與n_distinct一樣

most_common_vals

高頻非空值按照出現的頻次排序的列表，列表中的值我們一般簡稱為MCV值

most_common_freqs

對應每個MCV值出現的頻率列表，列表中的每個值表示對應的MCV值出現的次數與表的總記錄數(包含NULL值)的比例

histogram_bounds

去除NULL值和most_common_vals之外的其它值按照’<’操作符排序，然后按照個數等分的邊界值。如果此字段的數據類型沒有<操作符或取值都在most_common_vals中出現過，則這個字段為NULL

correlation

字段值的物理行序和按照’<’排序的邏輯行序的相關性，我們一般稱之為排序相關性，取值范圍為-1到+1；數據越按照’<’操作符排序，取值越接近1；數據越按照’>’操作符排序，取值越接近-1。取值越接近于-1或者+1，說明索引掃描時引入的隨機IO開銷越小，索引掃描的隨機IO的cost值也越小。如果字段數據類型沒有<操作符，則這個字段為NULL。

most_common_elems

數組類型的最常用的非空元素的列表，類似most_common_vals，但記錄的不是字段值，而是構成數組字段的元素

most_common_elem_freqs

most_common_elems中每個元素出現的頻次與該字段非NULL值的記錄數的比例，同時還在字段尾部依次追加了元素的最小值、最大值、NULL值個數的比例，所以此字段元素的個數總是比most_common_elems元素的個數多3個

elem_count_histogram

數組類型非NULL distinct值的直方圖信息，末尾為平均唯一值個數

查詢表public.test的a列的數據特征信息如下

通過統計新可以看出public.test的a列的NULL值比例為0，存在120個distinct值， 1~20是MCV值，每個出現的概率是0.0254167；21~1200出現在在直方圖統計信息中；

以查詢語句“SELECT count(1) FROM public.test WHERE a < 44;”為例說明統計信息在優化過程中行數估算場景下的作用

a)? ?所有MCV值均滿足a < 44，所有MCV值的比例為0.0254167 * 20 = 0.5083340

b)? ?44為直方圖中第三個邊界，直方圖中滿足a < 44的值的比例為(1-0.5083340)/100 *(3-1)= .0098333200

那么表中滿足a<56的tuples的個數為1243.6015680 ≈1244，通過explain打印執行計劃如下

3.3????? 擴展統計信息

擴展統計信息存儲在系統表pg_statistic_ext里面，當前只支持多列統計信息這一種擴展統計信息類型。pg_statistic_ext會存儲一些關鍵采樣值來描述數據特征，因此pg_statistic_ext數據是敏感的，只有超級用戶才可以訪問pg_statistic_ext，通常我們推薦用戶使用查詢系統視圖pg_ext_stats來查詢當前用戶有查詢權限的擴展統計信息。

名稱

描述

schemaname

統計對象所在的namespace名

tablename

統計對象名

attname

擴展統計信息涉及列編號的數組

inherited

如果為真，那么統計分析時采樣樣本包括繼承表數據

null_frac

該字段組合中所有字段均為NULL值的個數比率

avg_width

該字段組合的平均字節寬度

n_distinct

字段中非NULL值的distinct值。大于零的數值是多字段組合的不同值的實際數，小于零是多字段組合的distinct值占全部非NULL值個數比例的負數

n_dndistinct

第一個DN上的n_distinct值

most_common_vals

字段組合里最常用數值的列表，此字段要求多列的每個列都不存在NULL值

most_common_freqs

most_common_vals中每個值出現的頻率列表，列表中的每個元素描述了most_common_vals中對應值出現的次數與表的總記錄數(包含NULL值)的比例

most_common_vals_null

高頻非空值按照出現的頻次排序的列表，此字段要求多列中的至少1列包含NULL值，但又不全部是NULL值

most_common_freqs_null

most_common_vals_null中每個值出現的頻率列表

表的多個列有相關性且查詢中有同時基于這些列的過濾條件、關聯條件或者分組操作的時候，可嘗試收集多列統計信息。擴展統計信息需要手動進行收集(具體收集方法，下個小節會介紹)，如下為test表(a,b)兩列的統計信息

4????? HOW：如何生成統計信息

4.1????? 顯式收集統計信息

4.1.1 單列統計信息

通過如下命令收集單列統計信息：

{ ANALYZE | ANALYSE } [ VERBOSE ]? [ table_name [ ( column_name [, ...] ) ] ];

如語法描述，我們支持對指定列做統計信息，但是實際上我們很難統計實際業務SQL中到底使用了當前哪些表的列進行了代價估算，因此建議通常情況下對全表收集統計信息。

4.1.2 擴展統計信息

通過如下命令收集多列統計信息：

{ANALYZE | ANALYSE} [ VERBOSE ] table_name (( column_1_name, column_2_name [, ...] ));

需要注意的是，當前只支持在百分比采樣模式下生成擴展統計信息，因此在收集擴展統計信息之前請確保GUC參數default_statistics_target為負數

4.2????? 提升統計信息質量

analyze是按照隨機采樣算法從表上采樣，根據樣本計算表數據特征。采樣數可以通過配置參數default_statistics_target進行控制，default_statistics_target取值范圍為-100~10000，默認值為100。

1） 當default_statistics_target > 0時；采樣的樣本數為300*default_statistics_target，default_statistics_target取值越大，采樣的樣本也越大，樣本占用的內存空間也越大，統計信息計算耗時也越長

2） 當default_statistics_target < 0時，采樣的樣本數為 (default_statistics_target)/100*表的總行數，default_statistics_target取值越小，采樣的樣本也越大。但是default_statistics_target < 0時會把采樣數據下盤，不存在樣本占用的內存空間的問題，但是因為樣本過大，計算耗時長的問題同樣存在

default_statistics_target < 0時，實際采樣數是(default_statistics_target)/100*表的總行，所以我們又稱之為百分比采樣。

4.3????? 自動收集統計信息

當配置參數autoanalyze打開時，查詢語句走到優化器發現表不存在統計信息，會自動觸發統計信息收集，以滿足優化器的需求。以文檔的case為列

注：只有對統計信息敏感的復雜查詢動作(多表關聯等操作)的SQL語句執行時才會觸發自動收集統計信息；簡單查詢(比如單點，單表聚合等) 不會觸發自動收集統計信息

5????? WHEN：什么時候收集統計信息

5.1????? 大規模數據變化

大規模數據導入/UPDATE/DELETE等操作，會導致表數據行數變化，新增的大量數據也會導致數據特征發生大的變化，此時需要對表重新收集統計信息

5.2????? 查詢新增數據

常見于業務表新增數據查詢場景，這個也是收集業務中最常見、最隱蔽的統計信息沒有及時更新的問題，這種場景最主要的特征如下

1)?????? 存在一個按照時間增長的業務表

2)?????? 業務表每天入庫新一天的數據

3)?????? 數據入庫之后查詢新增數據進行數據加工分析

在最后步驟的數據加工分析時，最長的方法就是使用Filter條件從分區表中篩選數據，如passtime > ‘2020-01-19 00:00:00’ AND pastime < ‘2020-01-20 00:00:00’，假如新增數據入庫之后沒有做analyze，優化器發現Filter條件中的passtime取值范圍超過了統計信息中記錄的passtime值的上邊界，會把估算滿足passtime > ‘2020-01-19 00:00:00’ AND pastime < ‘2020-01-20 00:00:00’的tuple個數為1條，導致估算行數驗證失真

6????? WHO：誰來收集統計信息

AP場景下業務表數據量一般都很大，單次導入的數據量也比較大，而且經常是數據導入即用，因此建議在業務開發過程中，根據數據變化量和查詢特征在需要的地方主動對相關表做analyze。

數據倉庫服務 GaussDB(DWS)

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。