如何手動或自動收集統計資訊 - AnalyticDB

本文介紹AnalyticDB for MySQL統計資訊的作用與分類，自動收集統計資訊的機制，手動收集統計資訊的方法，以及如何查看統計資訊。

功能介紹

查詢最佳化工具（Query Optimizer）將查詢轉換為執行計畫，並交給執行引擎執行。執行計畫的品質會影響查詢的效能。統計資訊作為查詢最佳化工具的輸入，可以協助查詢最佳化工具產生高品質的執行計畫。

AnalyticDB for MySQL支援自動收集統計資訊功能，該功能預設開啟。除此以外，3.1.9.2及以上核心版本的叢集支援列組統計資訊功能，該功能預設關閉，您可以手動開啟該功能。在營運時間內，AnalyticDB for MySQL會根據表中資料量的大小自動全量或採樣收集基礎統計資訊、長條圖和列組統計資訊，如果待收集統計資訊的列過多，可能需要多天時間才能完成全量收集。在營運時間外，AnalyticDB for MySQL定時自動增量收集基礎統計資訊。

當資料通過不同的方式匯入後，AnalyticDB for MySQL的收集策略如下：

INSERT OVERWRITE大量匯入：AnalyticDB for MySQL會立即自動收集基礎統計資訊。
INSERT INTO、REPLACE INTO等即時匯入：AnalyticDB for MySQL需等到營運時間，或者Build完成後的增量收集周期時間觸發增量收集任務，建議您在匯入資料後手動收集一次基礎統計資訊。

您也可以關閉自動收集統計資訊的功能，手動執行ANALYZE TABLE收集統計資訊。詳細資料，請參見手動收集統計資訊。

AnalyticDB for MySQL自動收集統計資訊功能僅管理內表，不管理外表。但手動收集統計資訊功能既可以管理內表，也可以管理外表。

注意事項

企業版、基礎版及湖倉版和數倉版叢集都支援統計資訊功能，但叢集核心版本需為3.1.6.1及以上。

說明

請在雲原生資料倉儲AnalyticDB MySQL控制台集群資訊頁面的配寘資訊地區，查看和升級核心版本。

統計資訊分類與選擇

AnalyticDB for MySQL統計資訊分為三類：基礎統計資訊（BASIC）、長條圖（HISTOGRAM）和列組統計資訊（GROUP_STATS）。長條圖和列組統計資訊的收集方式為全量和採樣收集，基礎統計資訊的收集方式為全量、採樣和自動增量收集，預設為自動增量收集。

重要

僅3.1.9.2及以上核心版本的叢集支援採樣收集基礎統計資訊、長條圖和列組統計資訊。

基礎統計資訊

基礎統計資訊包含列的最大值、最小值、平均長度（單位：位元組）、不同值的個數、NULL值比例等。

適用情境：

不參與過濾和關聯運算的列。
資料分布比較均勻的列，如主鍵列。

長條圖

根據表的基礎統計資訊，將一段區間內的資料再次劃分進一個桶形成長條圖。長條圖的每個桶描述了一段區間內的資料特徵。

分類：

Hybrid Histogram，由等高長條圖變形而來，能夠更好地描述熱點值。
Frequency Histogram，適用於不同值個數較少的列，每個值會對應一個桶。

說明

AnalyticDB for MySQL會自動為您選擇合適的長條圖。

適用情境：資料分布不均勻且參與過濾和關聯運算的列。如果資料分布均勻，那麼在過濾和關聯運算情境下，基礎統計資訊可以代替長條圖。

長條圖相對基礎統計資訊能更準確地反映表的統計資訊。在表很多的情境下，如果收集所有列的長條圖，會導致快取命中率下降。長條圖會佔用更多的統計資訊緩衝空間，成本會高於基礎統計資訊的成本。預設的統計資訊緩衝空間能緩衝約2萬列長條圖或200萬列基礎統計資訊。

列組統計資訊

重要

僅3.1.9.2及以上核心版本的叢集支援列組統計資訊功能。

基礎統計資訊和長條圖針對於單個列收集統計資訊，而列組統計資訊是收集同一張表多個列的統計資訊，描述多個列之間的關聯程度。

適用情境：多列彙總計算。若多個列之間的關聯性較強，使用列組統計資訊可以更準確地估算出其輸出行數，選擇更優的執行方式。

自動收集統計資訊

開啟或關閉自動收集統計資訊的功能

AnalyticDB for MySQL支援自動收集統計資訊功能，該功能預設開啟，您可以通過下列命令關閉或重新開啟自動收集統計資訊的功能。

SET adb_config O_CBO_AUTONOMOUS_STATS_ENABLED = [false｜true];

開啟或關閉自動收集列組統計資訊的功能

3.1.9.2及以上核心版本的叢集支援自動收集列組統計資訊功能，該功能預設關閉，您可以通過下列命令開啟或關閉自動收集列組統計資訊的功能。

SET adb_config O_CBO_MAINTENANCE_WINDOW_COLLECT_GROUP_STATS_ENABLED = [false｜true];

說明

使用自動收集列組統計資訊功能時，需確保已開啟自動收集統計資訊功能。

設定營運時間

自動收集統計資訊的營運時間預設為04:00~05:00。您可以通過下列命令修改營運時間，建議設定在業務低峰期。開始時間與結束時間的間隔最短不能小於1分鐘，最長不能超過3小時。開始時間要早於結束時間。設定錯誤時，會使用預設時間。

說明

營運時間與叢集目前時間所在時區保持一致。

SET adb_config O_CBO_MAINTENANCE_WINDOW_DURATION = [04:00-05:00];

設定收集統計資訊的資料量閾值

使用如下命令設定收集統計資訊的資料量閾值。預設值為5000000000（50億行），單位為行。

SET adb_config O_CBO_MAINTENANCE_WINDOW_COLLECTOR_ROW_LIMIT = 10000;

當表的行數超過資料量閾值時：

3.1.9.2以下核心版本的叢集，收集統計資訊時會跳過該表。
3.1.9.2及以上核心版本的叢集，採樣收集該表的統計資訊。

開啟或關閉收集統計資訊的負載限制

營運時間內，AnalyticDB for MySQL會限制自動收集統計資訊時的負載，減少掃描表時IO資源佔用。預設值true （開啟限速）。如果營運時間內資源空閑，可以關閉負載限制以加快統計資訊更新。

SET adb_config O_CBO_AUTONOMOUS_STATS_SCAN_RATE_LIMIT_ENABLED = [false｜true];

在指定資源群組收集統計資訊

自動收集統計資訊預設使用系統帳號執行命令。如果希望在指定資源群組執行自動收集統計資訊的命令，可以通過下列命令指定資料庫帳號。指定資料庫帳號後，AnalyticDB for MySQL將在該資料庫帳號綁定的資源群組中執行自動收集統計資訊的命令。請確保該資料庫帳號擁有所有表所有列的查詢許可權，且已綁定資源群組，綁定操作請參見建立和管理資源群組。

SET adb_config O_CBO_AUTONOMOUS_STATS_ACCOUNT = [user_name];

設定列的到期比例

列的到期比例預設為0.1（10%）。當表的UPDATE、DELETE、INSERT或REPLACE等操作影響的行數相對於表的總行數的比例大於到期比例時，會判定該表的統計資訊到期，AnalyticDB for MySQL會在營運時間內對到期的表的所有列重新收集統計資訊。列的到期比例未超過設定的比例時，在營運時間內也不會自動收集統計資訊。

SET adb_config O_CBO_STATS_EXPIRED_RATIO = 0.1;

手動收集統計資訊

收集全表統計資訊

ANALYZE TABLE命令會掃描全表來收集統計資訊，對於資料量大的表，全表收集統計資訊需消耗更多的時間，建議在業務低峰期執行或採樣收集統計資訊。

文法

ANALYZE TABLE [schema_name.]table_name [UPDATE [BASIC|HISTOGRAM|GROUP_STATS]] [ON column_name[,...]] [WITH ENABLE SAMPLING]

參數

參數	是否必選	說明
`schema_name`	否	資料庫名稱。
`table_name`	是	表名。AnalyticDB for MySQL將收集該表的統計資訊。一個`ANALYZE TABLE`語句只能指定一個表，可以指定內表或外表。
`UPDATE [BASIC\|HISTOGRAM\|GROUP_STATS]`	否	指定統計資訊的類型，取值： BASIC（預設值）：基礎統計資訊。 HISTOGRAM：長條圖。 GROUP_STATS：列組統計資訊。重要僅3.1.9.2及以上核心版本的叢集支援列組統計資訊功能。
`ON column_name[,...]`	否	指定需要收集統計資訊的列。如果不指定列，則會收集該表所有列的統計資訊。
`WITH ENABLE SAMPLING`	否	採樣收集統計資訊。重要僅3.1.9.2及以上核心版本的叢集支援採樣收集基礎統計資訊、長條圖和列組統計資訊。

樣本

收集adb_demo.customer表所有列的基礎統計資訊。可以選擇以下任意一種方式：
- ```
ANALYZE TABLE adb_demo.customer;
```
- ```
ANALYZE TABLE adb_demo.customer UPDATE BASIC;
```
收集adb_demo.customer表customer_id列的基礎統計資訊。
```
ANALYZE TABLE adb_demo.customer UPDATE BASIC ON customer_id;
```

收集adb_demo.customer表customer_id和login_time列的長條圖資訊。

ANALYZE TABLE adb_demo.customer UPDATE HISTOGRAM ON customer_id,login_time;

採樣收集adb_demo.customer表customer_id和login_time列的列組統計資訊。

ANALYZE TABLE adb_demo.customer UPDATE GROUP_STATS ON customer_id,login_time with enable sampling;

收集分區統計資訊

版本限制

僅3.1.9.1及以上核心版本的企業版、基礎版及湖倉版叢集支援使用ANALYZE TABLE命令全量收集OSS外表分區的基礎統計資訊。

說明

請在雲原生資料倉儲AnalyticDB MySQL控制台集群資訊頁面的配寘資訊地區，查看和升級核心版本。

文法

ANALYZE TABLE table_name WITH PARTITIONS = ARRAY[ARRAY[PARTITION_KEYS] [, PARTITION_KEYS, ....]]

參數說明

參數	是否必選	說明
`table_name`	是	表名。AnalyticDB for MySQL將收集該表的統計資訊。一個`ANALYZE TABLE`語句只能指定一個表，可以指定內表或外表。
`PARTITION_KEYS`	是	指定需要收集統計資訊的分區。

樣本

收集test1分區表中2023-01和2023-02兩個分區的統計資訊。

ANALYZE TABLE test1 WITH PARTITIONS = ARRAY[ARRAY['2023-01'], ARRAY['2023-02']];

收集test2分區表中（1,1）和（1,0）兩個分區的統計資訊。
```
ANALYZE TABLE test2 WITH PARTITIONS = ARRAY[ARRAY[1, 1], ARRAY[1, 0]];
```

查看統計資訊

統計資訊以二進位形式儲存在AnalyticDB for MySQL內。您可以通過系統資料表INFORMATION_SCHEMA查看統計資訊。

執行以下命令，查看錶級統計資訊：

SELECT * FROM INFORMATION_SCHEMA.TABLE_STATISTICS;

執行以下命令，查看列級統計資訊（包括基礎統計資訊、長條圖、列組統計資訊）：
```
SELECT * FROM INFORMATION_SCHEMA.COLUMN_STATISTICS;
```

常見問題與解決方案

ANALYZE命令為什麼會被診斷為慢查詢

系統在營運時間自動發起的ANALYZE命令會低優先順序執行（IO限流+CPU低優先順序），因此執行緩慢，耗時很長，會被診斷為慢查詢，一般不會影響業務。如果CPU負載不高，或者CPU負載高與營運時間沒有明顯聯絡，可以忽略該問題。如果CPU負載持續很高，請參考下文CPU負載過高，查詢回應時間受到影響如何處理。

使用統計資訊功能過程中，CPU負載過高的原因

導致CPU負載過高的原因有如下兩點：

在預設的營運時間04:00-05:00，系統會對錶進行全量掃描，收集每列的統計資訊，在該時段CPU負載過高。
大部分統計資訊是增量收集的，一般資源消耗不會太高。由於統計資訊功能是在叢集核心版本為3.1.6及以上版本的AnalyticDB for MySQL數倉版叢集才預設開啟的，所以當叢集核心版本從3.1.6以下版本升級到3.1.6及以上版本時，會觸發一次全量資料的統計資訊收集，導致叢集核心版本完成升級後的一段時間內統計資訊收集的工作量較大，CPU負載較高，完成收集後即可緩解。

當CPU負載過高時，需要判斷查詢回應時間是否受影響。如果平均查詢回應時間沒有明顯變化，說明查詢回應時間未受到影響。因為ANALYZE命令是在CPU低優先順序和IO限流下緩慢執行，使用者本身的查詢不一定會受影響，即使監控項中顯示CPU負載高，但有查詢任務時，資源會優先服務查詢任務。

CPU負載過高，查詢回應時間受到影響如何處理

當查詢回應時間受到影響時，依次參考以下方案處理：

調整營運時間到業務低峰期。

set adb_config O_CBO_MAINTENANCE_WINDOW_DURATION = [04:00-05:00];

如果無法評估合適的低峰期，可以適當下調系統查詢IO限制，預設50 MB，調整時建議不低於16 MB。
```
set adb_config CSTORE_IO_LIMIT_SYSTEM_QUERY_BPS = 52428800;
```
將統計資訊收集工作劃分到指定的資源群組，如低優先順序資源群組，來隔離負載。詳情請參見自動收集統計資訊。
```
set adb_config O_CBO_AUTONOMOUS_STATS_ACCOUNT = [user_name];
```
上調列的到期比例，以減少收集工作量。列的到期比例預設為0.1（10%），取值範圍（0,1），建議上調值不超過0.5。
```
set adb_config O_CBO_STATS_EXPIRED_RATIO = 0.1;
```

如果以上方案均不能解決問題，可以嘗試關閉統計資訊自治功能（set adb_config O_CBO_AUTONOMOUS_STATS_ENABLED=false;）。但是關閉自動收集統計資訊後，可能會出現查詢效能回退。後續如果需要統計資訊，需要您手動維護。詳情請參見手動收集統計資訊。

**通過SELECT * FROM INFORMATION_SCHEMA.COLUMN_STATISTICS查詢統計資訊，多天未更新的原因**

統計資訊不更新的原因有如下兩點：

表的統計資訊未到期。
統計資訊預設到期比例是0.1（10%），即資料變化量（Update、Delete、Insert或Replace）需要超過10%才會更新。如果資料變化量不大，可以再觀察一周繼續正常使用即可。
表和列太多且資料量大。
預設情況下，排除累加式更新，一天只有1小時的收集時間。如果表和列很多，一天內無法全部更新，可能需要經過一周才能更新一次。如果表和列較多，如超1000列，並且統計資訊更新時間在一周內，統計資訊多天未更新屬於正常現象，繼續觀察使用即可。

建立的表匯入資料會自動更新統計資訊嗎

通過INSERT OVERWRITE大量匯入方式，資料匯入完成後會立即自動收集基礎統計資訊。通過INSERT INTO、REPLACE INTO等即時匯入方式匯入資料，需要等到營運時間，或者Build完成後的增量收集周期時間觸發增量收集任務，建議您在匯入資料後手動收集一次基礎統計資訊。詳情請參見手動收集統計資訊。