如何手动或自动收集统计信息_云原生数据仓库AnalyticDB MySQL版(AnalyticDB for MySQL)-阿里云帮助中心

本文介绍AnalyticDB MySQL统计信息的作用与分类，自动收集统计信息的机制，手动收集统计信息的方法，以及如何查看统计信息。

功能介绍

查询优化器（Query Optimizer）将查询转换为执行计划，并交给执行引擎执行。执行计划的质量会影响查询的性能。统计信息作为查询优化器的输入，可以帮助查询优化器生成高质量的执行计划。

AnalyticDB MySQL版支持自动收集统计信息功能，该功能默认开启。除此以外，3.1.9.2及以上内核版本的集群支持列组统计信息功能，该功能默认关闭，您可以手动开启该功能。在运维时间内，AnalyticDB MySQL版会根据表中数据量的大小自动全量或采样收集基础统计信息、直方图和列组统计信息，如果待收集统计信息的列过多，可能需要多天时间才能完成全量收集。在运维时间外，AnalyticDB MySQL版定时自动增量收集基础统计信息。

当数据通过不同的方式导入后，AnalyticDB MySQL的收集策略如下：

INSERT OVERWRITE批量导入：AnalyticDB MySQL会立即自动收集基础统计信息。
INSERT INTO、REPLACE INTO等实时导入：AnalyticDB MySQL需等到运维时间，或者Build完成后的增量收集周期时间触发增量收集任务，建议您在导入数据后手动收集一次基础统计信息。

您也可以关闭自动收集统计信息的功能，手动执行ANALYZE TABLE收集统计信息。详细信息，请参见手动收集统计信息。

AnalyticDB MySQL版自动收集统计信息功能仅管理内表，不管理外表。但手动收集统计信息功能既可以管理内表，也可以管理外表。

注意事项

湖仓版（3.0）和数仓版（3.0）集群都支持统计信息功能，但集群内核版本需为3.1.6.1及以上。

说明

如何查看集群的内核版本，请参见如何查看实例版本信息。
若需升级内核版本，请提交工单联系技术支持。

统计信息分类与选择

AnalyticDB MySQL版统计信息分为三类：基础统计信息（BASIC）、直方图（HISTOGRAM）和列组统计信息（GROUP_STATS）。直方图和列组统计信息的收集方式为全量和采样收集，基础统计信息的收集方式为全量、采样和自动增量收集，默认为自动增量收集。

重要

仅3.1.9.2及以上内核版本的集群支持采样收集基础统计信息、直方图和列组统计信息。

基础统计信息

基础统计信息包含列的最大值、最小值、平均长度（单位：字节）、不同值的个数、NULL值比例等。

适用场景：

不参与过滤和关联运算的列。
数据分布比较均匀的列，如主键列。

直方图

根据表的基础统计信息，将一段区间内的数据再次划分进一个桶形成直方图。直方图的每个桶描述了一段区间内的数据特征。

分类：

Hybrid Histogram，由等高直方图变形而来，能够更好地描述热点值。
Frequency Histogram，适用于不同值个数较少的列，每个值会对应一个桶。

说明

AnalyticDB MySQL会自动为您选择合适的直方图。

适用场景：数据分布不均匀且参与过滤和关联运算的列。如果数据分布均匀，那么在过滤和关联运算场景下，基础统计信息可以代替直方图。

直方图相对基础统计信息能更准确地反映表的统计信息。在表很多的场景下，如果收集所有列的直方图，会导致缓存命中率下降。直方图会占用更多的统计信息缓存空间，成本会高于基础统计信息的成本。默认的统计信息缓存空间能缓存约2万列直方图或200万列基础统计信息。

列组统计信息

重要

仅3.1.9.2及以上内核版本的集群支持列组统计信息功能。

基础统计信息和直方图针对于单个列收集统计信息，而列组统计信息是收集同一张表多个列的统计信息，描述多个列之间的关联程度。

适用场景：多列聚合计算。若多个列之间的关联性较强，使用列组统计信息可以更准确地估算出其输出行数，选择更优的执行方式。

自动收集统计信息

开启或关闭自动收集统计信息的功能

AnalyticDB 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 MySQL会限制自动收集统计信息时的负载，减少扫描表时IO资源占用。默认值true （开启限速）。如果运维时间内资源空闲，可以关闭负载限制以加快统计信息更新。

SET adb_config O_CBO_AUTONOMOUS_STATS_SCAN_RATE_LIMIT_ENABLED = [false｜true];

在指定资源组收集统计信息

自动收集统计信息默认使用系统账号执行命令。如果希望在指定资源组执行自动收集统计信息的命令，可以通过下列命令指定数据库账号。指定数据库账号后，AnalyticDB MySQL将在该数据库账号绑定的资源组中执行自动收集统计信息的命令。请确保该数据库账号拥有所有表所有列的查询权限，且已绑定资源组，绑定操作请参见新建资源组。

SET adb_config O_CBO_AUTONOMOUS_STATS_ACCOUNT = [user_name];

设置列的过期比例

列的过期比例默认为0.1（10%）。当表的UPDATE、DELETE、INSERT或REPLACE等操作影响的行数相对于表的总行数的比例大于过期比例时，会判定该表的统计信息过期，AnalyticDB 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 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及以上内核版本的湖仓版（3.0）集群支持使用ANALYZE TABLE命令全量收集OSS外表分区的基础统计信息。

说明如何查看集群内核版本，请参见如何查看实例版本信息。如需升级内核版本，请联系技术支持。

语法

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

参数说明

参数	是否必选	说明
`table_name`	是	表名。AnalyticDB 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 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 MySQL数仓版（3.0）集群才默认开启的，所以当集群内核版本从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完成后的增量收集周期时间触发增量收集任务，建议您在导入数据后手动收集一次基础统计信息。详情请参见手动收集统计信息。