SelectDB统计信息_云数据库 SelectDB 版(SelectDB)-阿里云帮助中心

本文介绍云数据库 SelectDB 版中手动记录统计信息或者自动收集统计信息，为您进行查询性能优化提供参考。

概述

云数据库 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内存限制。