文档

统计信息

更新时间:
一键部署

本文介绍云数据库 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_analyzefalse来彻底关闭该功能。

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内存限制。

  • 本页导读 (1)