SelectDB統計資訊 - ApsaraDB for SelectDB

本文介紹ApsaraDB for SelectDB中手動記錄統計資訊或者自動收集統計資訊，為您進行查詢效能最佳化提供參考。

概述

ApsaraDB for SelectDB支援手動記錄統計資訊或者自動收集統計資訊。收集統計資訊有助於最佳化器瞭解資料分布特性。在進行基於成本最佳化（CBO）時，最佳化器會利用這些統計資訊來計算謂詞的選擇性，並估算每個執行計畫的成本，從而選擇更優的計劃以大幅提升查詢效率。

統計資訊資料

主要收集列相關的統計資訊，資訊說明如下。

資訊	資訊說明
row_count	總行數。
data_size	總資料量。
avg_size_byte	值的平均⻓度。
ndv	不同值數量。
min	最小值。
max	最⼤值。
null_count	空值數量。

統計資訊收集

SelectDB支援您通過提交ANALYZE語句來手動觸發統計資訊的收集和更新。

文法

ANALYZE < TABLE | DATABASE table_name | db_name > 
    [ (column_name [, ...]) ]
    [ [ WITH SYNC ] [ WITH SAMPLE PERCENT | ROWS ] ];

參數說明

參數名稱	參數說明
table_name	指定的目標表。可以是`db_name.table_name`形式。
column_name	指定的目標列。必須是`table_name`中存在的列，多個列名稱用逗號分隔。
sync	同步收集統計資訊，收集完後返回。若不指定則非同步執行並返回JOB ID。
sample percent \| rows	抽樣收集統計資訊。可以指定抽樣比例或者抽樣行數。

樣本

對一張表按照10%的比例採樣收集統計資料，樣本如下。
```
ANALYZE TABLE lineitem WITH SAMPLE PERCENT 10;
```
對一張表按採樣10萬行收集統計資料，樣本如下。
```
ANALYZE TABLE lineitem WITH SAMPLE ROWS 100000;
```

自動收集

預設為開啟狀態。

執行邏輯

在每次匯入事務提交後，SelectDB將記錄本次匯入事務更新的錶行數用以估算當前已有表的統計資料的健康度（對於沒有收集過統計資料的表，其健康度為0）。當表的健康度低於60（可通過參數table_stats_health_threshold調節）時，SelectDB會認為該表的統計資訊已經過時，並在之後觸發對該表的統計資訊收集作業。而對於統計資訊健康度高於60的表，則不會重複進行收集。

配置項

統計資訊的收集作業需要佔用一定的系統資源。為了降低開銷，對於資料量較大（預設為5 GiB，通過設定FE參數huge_table_lower_bound_size_in_bytes來調節此行為）的表，SelectDB會自動採取採樣的方式去收集。自動採樣預設採樣4194304（2^22）行，以降低對系統造成的負擔並儘快完成收集作業。

如果希望採樣更多的行以獲得更準確的資料分布資訊，可通過調整參數huge_table_default_sample_rows增大採樣行數。此外，對於資料量大於（huge_table_lower_bound_size_in_bytes*5）的表，SelectDB保證其收集時間間隔不小於12小時（該時間可通過調整參數huge_table_auto_analyze_interval_in_millis控制）。

如果擔心自動收集作業對業務造成幹擾，可結合實際情況通過設定參數auto_analyze_start_time和參數auto_analyze_end_time指定自動收集作業在業務負載較低的時間段執行，也可以通過設定參數enable_auto_analyze為false來徹底關閉該功能。

External Catalog預設不參與自動收集。因為External Catalog包含海量歷史資料，如果參與自動收集，可能佔用過多資源。可以通過設定Catalog的Property來開啟或關閉External Catalog的自動收集。

ALTER CATALOG external_catalog SET PROPERTIES ('enable.auto.analyze'='true'); // 開啟自動收集
ALTER CATALOG external_catalog SET PROPERTIES ('enable.auto.analyze'='false'); // 關閉自動收集

統計作業管理

查看統計作業

通過SHOW ANALYZE來查看統計資訊收集作業的資訊。

文法

SHOW [AUTO] ANALYZE < table_name | job_id >
    [ WHERE [ STATE = [ "PENDING" | "RUNNING" | "FINISHED" | "FAILED" ] ] ];

參數說明

參數名稱	參數說明
AUTO	僅展示自動收集歷史作業資訊。說明預設只儲存過去20000個執行完畢的自動收集作業的狀態。
table_name	目標表的表名。可以是`db_name.table_name`形式。指定後可查看該表對應的統計作業資訊，不指定時返回所有統計作業資訊。
job_id	統計資訊作業ID，執行`ANALYZE`非同步收集時得到。不指定ID時此命令返回所有統計作業資訊。

樣本

SHOW ANALYZE 245073\G;
*************************** 1. row ***************************
              job_id: 245073
        catalog_name: internal
             db_name: default_cluster:tpch
            tbl_name: lineitem
            col_name: [l_returnflag,l_receiptdate,l_tax,l_shipmode,l_suppkey,l_shipdate,l_commitdate,l_partkey,l_orderkey,l_quantity,l_linestatus,l_comment,l_extendedprice,l_linenumber,l_discount,l_shipinstruct]
            job_type: MANUAL
       analysis_type: FUNDAMENTALS
             message: 
last_exec_time_in_ms: 2023-11-07 11:00:52
               state: FINISHED
            progress: 16 Finished  |  0 Failed  |  0 In Progress  |  16 Total
       schedule_type: ONCE

返回結果參數說明如下。

參數名稱	參數說明
job_id	統計作業ID。
catalog_name	Catalog名稱。
db_name	資料庫名稱。
tbl_name	表名稱。
col_name	列名稱列表。
job_type	作業類型。
analysis_type	統計類型。
message	作業資訊。
last_exec_time_in_ms	上次執行時間。
state	作業狀態。
schedule_type	調度方式。

查看錶統計資訊收集概況

通過SHOW TABLE STATS查看錶的統計資訊收集概況。

文法

SHOW TABLE STATS <table_name>;

參數說明

參數名稱	參數說明
table_name	目標表表名。可以是`db_name.table_name`形式。

樣本

查看錶lineitem的統計資訊收集條件概況，樣本如下。

SHOW TABLE STATS lineitem\G
*************************** 1. row ***************************
updated_rows: 0
 query_times: 0
   row_count: 6001215
updated_time: 2023-11-07
     columns: [l_returnflag, l_receiptdate, l_tax, l_shipmode, l_suppkey, l_shipdate, l_commitdate, l_partkey, l_orderkey, l_quantity, l_linestatus, l_comment, l_extendedprice, l_linenumber, l_discount, l_shipinstruct]
     trigger: MANUAL

返回結果參數說明如下。

參數名稱	參數說明
updated_rows	自上次ANALYZE以來該表的更新行數。
query_times	保留列，後續版本用以記錄該表查詢次數。
row_count	行數（不反映命令執行時的準確行數）。
updated_time	上次更新時間。
columns	收集過統計資訊的列。
trigger	觸發方式。

查看列統計資訊收集概況

每個收集作業中可以包含一到多個任務，每個任務對應一列的收集。您可通過如下命令查看具體每列的統計資訊收集完成情況。

文法

SHOW ANALYZE TASK STATUS [job_id]

參數說明

參數名稱	參數說明
job_id	統計資訊作業ID。

樣本

查看任務ID為20038的列統計資訊收集條件概況，樣本如下。

SHOW ANALYZE TASK STATUS 20038;
+---------+----------+---------+----------------------+----------+
| task_id | col_name | message | last_exec_time_in_ms | state    |
+---------+----------+---------+----------------------+----------+
| 20039   | col4     |         | 2023-06-01 17:22:15  | FINISHED |
| 20040   | col2     |         | 2023-06-01 17:22:15  | FINISHED |
| 20041   | col3     |         | 2023-06-01 17:22:15  | FINISHED |
| 20042   | col1     |         | 2023-06-01 17:22:15  | FINISHED |
+---------+----------+---------+----------------------+----------+

查看列統計資訊

通過SHOW COLUMN STATS來查看列的各項統計資料。

文法

SHOW COLUMN [cached] STATS table_name [ (column_name [, ...]) ];

參數說明

參數名稱	參數說明
cached	展示當前FE記憶體緩衝中的統計資訊。
table_name	收集統計資訊的目標表表名。可以是`db_name.table_name`形式。
column_name	指定的目標列，必須是`table_name`中存在的列，多個列名稱用逗號分隔。

樣本

查看列l_tax的統計資料，樣本如下。

SHOW COLUMN STATS lineitem(l_tax)\G
*************************** 1. row ***************************
  column_name: l_tax
        count: 6001215.0
          ndv: 9.0
     num_null: 0.0
    data_size: 4.800972E7
avg_size_byte: 8.0
          min: 0.00
          max: 0.08
       method: FULL
         type: FUNDAMENTALS
      trigger: MANUAL
  query_times: 0
 updated_time: 2023-11-07 11:00:46

終止統計作業

通過KILL ANALYZE來終止正在啟動並執行統計作業。

文法

KILL ANALYZE job_id;

參數說明

參數名稱	參數說明
job_id	統計資訊作業 ID。執行`ANALYZE`非同步收集統計資訊時所返回的值，也可以通過`SHOW ANALYZE`語句擷取。

樣本

終止ID為52357的統計作業，樣本如下。

KILL ANALYZE 52357;

會話變數及FE配置項

會話變數

會話變數	預設值	說明
auto_analyze_start_time	00:00:00	自動統計資訊收集開始時間。
auto_analyze_end_time	23:59:59	自動統計資訊收集結束時間。
enable_auto_analyze	true	是否開啟自動收集功能。
huge_table_default_sample_rows	4194304	對大表的採樣行數。
huge_table_lower_bound_size_in_bytes	5368709120	大小超過該值的表，在自動收集時將會自動通過採樣收集統計資訊。
huge_table_auto_analyze_interval_in_millis	43200000	控制對大表的自動ANALYZE的最小時間間隔，在該時間間隔內大小超過`huge_table_lower_bound_size_in_bytes*5`的表僅ANALYZE一次。
table_stats_health_threshold	60	取值在0-100之間，當自上次統計資訊收集操作之後，資料更新量達到`（100-table_stats_health_threshold）%`，認為該表的統計資訊已淘汰。
analyze_timeout	43200	控制ANALYZE逾時時間，單位為秒。
auto_analyze_table_width_threshold	70	控制自動統計資訊收集處理的最大表寬度，列數大於該值的表不會參與自動統計資訊收集。

FE配置項

如下FE配置項與統計資訊有關，通常情況下無需關注。

FE配置項	預設值	說明
analyze_record_limit	20000	控制統計資訊作業執行記錄的持久化行數。
stats_cache_size	500000	FE側統計資訊緩衝條數。
statistics_simultaneously_running_task_num	3	可同時執行的非同步作業數量。
statistics_sql_mem_limit_in_bytes	2,147,483,648 bytes (2 GiB)	控制每個統計資訊SQL可佔用的BE記憶體。

常見問題

Q：ANALYZE提交報錯：“Stats table not available...”。
A：若出現該報錯，請檢查BE叢集狀態。
您可通過SHOW BACKENDS，確定BE狀態是否正常。如果BE狀態正常，可使用命令ADMIN SHOW REPLICA STATUS FROM __internal_schema.[tbl_in_this_db]，檢查該庫下Tablet狀態，確保Tablet狀態正常。執行ANALYZE時，統計資料會被寫入到內部表__internal_schema.column_statistics中。FE會在執行ANALYZE前檢查該表Tablet狀態，如果存在停用Tablet則拒絕執行作業。
Q：大表ANALYZE失敗。
A：建議您使用ANALYZE ... WITH SAMPLE...。由於ANALYZE能夠使用的資源受到比較嚴格的限制，對一些大表的ANALYZE操作有可能逾時或者超出BE記憶體限制。