使用ANALYZE和AUTO ANALYZE收集统计信息优化查询-实时数仓 Hologres-阿里云

本文将为您介绍如何使用Analyze命令，以及更加简单的Auto Analyze的相关机制。

Analyze

统计信息决定是否能够生成正确的执行计划。Hologres需要收集数据的采样统计信息，包括数据的分布和特征、表的统计信息、列的统计信息、行数、列数、字段宽度、基数、频度、最大值、最小值、高频值、分桶分布特征等信息。这些信息将为优化器更新算子执行预估COST、搜索空间裁剪、估算最优JOIN ORDER、估算内存开销、估算并行度，从而生成更优的执行计划。

Analyze命令用于收集数据库中表内容的统计信息，优化器会根据这些统计信息生成最佳的查询计划，从而提高查询效率。

使用语法

-- 更新某个表的统计信息，默认会收集表中所有列的统计信息
analyze <tablename>;

-- 更新某个列的统计信息，会比更新表时采样的数据更多，更精准，主要用于更新管理条件的列
analyze <tablename>(<colname>, <colname>);

参数说明
tablename为更新统计信息的表名称，colname为更新统计信息的列名称。
语法说明
两个Analyze命令的说明如下。
- 相同点
  - 对列统一收集包括行数、列宽、列的最常用值（Most Common Values）、列的直方图（Histogram）信息，列的非重复值的个数（Number of Distinct Value，NDV）在内的信息。
  - 两个命令都会相互覆盖指定列的统计信息，但不会覆盖其他列的信息。例如analyze <tablename>(<colname1>); 命令会覆盖（更新）之前colname1列收集的统计信息，但并不会改变colname2列的统计信息。
- 不同点
  - analyze <tablename>;基于采样数据，计算得出统计信息。
  - analyze <tablename>(<colname>, <colname>);会对列的Number of Distinct Value（NDV）进行APPROX_COUNT_DISTINCT计算，在很多情况下，这样计算的值相比采样更准确，但开销比采样表更大，因此只适合对重点列进行指定ANALYZE。NDV以外的Histogram、Width等信息，仍然通过采样得到。
  因此对于具有两列的table (colname1, colname2)，analyze table;不完全等价于analyze table(colname1, colname2);。
  对于常用的Join列、Group By列，推荐使用analyze <tablename>(<colname>, <colname>);命令进行额外的统计信息收集。
需要执行Analyze的情况
推荐您在如下情况下运行analyze <tablename>;命令。
- 在表执行大量的INSERT、UPDATE以及DELETE操作之后，包括导入数据。
- 在性能下降的情况下，多表Join查询之前，对Join的列、Group by的列进行Analyze。
- 执行CREATE FOREIGN TABLE命令后，通过Analyze收集当前外部表统计信息。
- 执行IMPORT FOREIGN SCHEMA后，对后续需要查询的表进行Analyze。
- 执行CREATE EXTERNAL DATABASE后，通过Analyze收集当前外部表统计信息。
注意事项
- 在Hologres V0.10和V1.1版本中，如果有对父表的查询，需要Analyze分区父表；如果直接对子表查询，请对子表Analyze；如果两者都有，建议两者都进行Analyze，否则可能会有缺失统计信息的情况。
- 如果遇到以下问题，您需要先执行Analyze，再运行导入任务，可以系统地提升效率。
  - 多表JOIN超出内存OOM：通常会产生Query executor exceeded total memory limitation xxxxx: yyyy bytes used报错。
  - 导入效率较低：在Hologres查询或导入数据时，效率较低，运行的任务长时间不结束。
- 如果有超宽列（例如Bitmap等Bytea数据，超过1KB的Text数据等），这些超宽列的统计信息没有作用，还会使采样更消耗内存。因此对于具有上述超宽列的表，尽量避免执行analyze <tablename>;命令，而是采用analyze <tablename>(<colname>, <colname>);避开超宽列，转为Analyze必要的列（例如上面推荐的Join的列、Group by的列和Filter列等）。
  说明
  1KB是经验值，宽度标准可以根据业务情况自行决定。

Auto Analyze

为了减少重复、手动的Analyze，从Hologres V0.10版本开始，支持Auto Analyze机制。开启auto analyze后，系统会根据用户的建表、数据写入和修改情况等来判断是否需要对相关的表在后台自动Analyze，无需再手动对表进行Analyze，降低操作复杂度，同时减少遗漏Analyze而导致缺失统计信息的情况。

使用语法

查看是否开启Auto Analyze

SHOW hg_enable_start_auto_analyze_worker;  -- V1.1及以上版本语法，查看当前开启/关闭状态

SHOW hg_experimental_enable_start_auto_analyze_worker;  -- V0.10语法，查看当前开启/关闭状态

开启/关闭语法如下，需要Superuser执行。

-- DB级别，执行后整个DB生效，V1.1及以上版本开启/关闭语法
ALTER DATABASE dbname SET hg_enable_start_auto_analyze_worker = ON;  -- 开启（默认）
ALTER DATABASE dbname SET hg_enable_start_auto_analyze_worker = OFF; -- 关闭

-- DB级别，执行后整个DB生效，V0.10开启/关闭语法
ALTER DATABASE dbname SET hg_experimental_enable_start_auto_analyze_worker = ON;  -- 开启（默认）
ALTER DATABASE dbname SET hg_experimental_enable_start_auto_analyze_worker = OFF; -- 关闭

-- DB级别，对External Database开启Auto Analyze 
ALTER EXTERNAL DATABASE dbname WITH enable_auto_analyze 'true';

使用限制
在Hologres中使用Auto Analyze，具体限制如下：
- Auto Analyze功能仅Hologres V0.10及以上版本支持，请在Hologres管理控制台的实例详情页查看当前版本，如果您的实例是V0.10以下版本，请您使用常见升级准备失败报错或加入Hologres钉钉交流群反馈，详情请参见如何获取更多的在线支持？。
- 仅支持Superuser执行开启或关闭Auto Analyze操作。
- Auto Analyze对分区表的限制如下。
  - 分区子表发生改变，需要Auto Analyze时，会统一Analyze其父表。
  - 分区表有扫描行数限制，采样数据时默认扫描的最大记录数是2²⁴条（16,777,216条），即若所有分区子表的记录数总和超过16,777,216条，会做一定的分区裁剪，只对其中若干分区（总和不超过16,777,216条）进行采样。
    说明
    分区列统计信息总是全的，不受裁剪影响，但是这可能会影响与分区列同分布的列（例如极端情况是，与分区列数据一样的列）的统计信息，即一部分值采样不到，行数估计可能不准确。如果有需求可以搜索（钉钉群号：32314975）加入实时数仓Hologres交流群联系技术支持，技术侧根据实例情况评估调整扫描的最大记录数。
- Auto Analyze默认最大收集256列的统计信息，如表超过256列，取前256列。可通过调整hg_experimental_auto_analyze_max_columns_count改变此值。
- Auto Analyze默认单个Worker限制的内存是4 GB，如果存在超宽的列，采样可能超出内存而导致Analyze失败。可调整auto_analyze_work_memory_mb参数改变其大小，但是要注意对系统内存的影响。实例规格越大，Worker数越多，Auto Analyze可用内存限制越大。
- 仅Hologres V3.1.0版本起，支持使用Analyze和Auto Analyze对External Database下的外部表收集统计信息。
- 不支持对HMS外部表执行Analyze和Auto Analyze。
Auto Analyze工作原理
当开始Auto Analyze后，系统后台会定期巡检是否有表需要执行Analyze。
- 普通表（内部表，包括单表和分区表）
  - 每隔1分钟巡检是否有表的最新动作（主要是INSERT、UPDATE、DELETE等DML操作，可能改变了数据量）。满足以下条件，系统后台触发表的Analyze，收集表的统计信息。
    - 表有DML执行完成且数据条数变化超过当前表的数据条数的10%。若表是分区子表，则是指变化条数超过此分区数据条数的10%。
    - TRUNCATE TABLE清空表。
    - 表的DDL发生变更。例如CREATE TABLE新建表，ALTER TABLE修改表结构等，不包括CALL SET_TABLE_PROPERTY修改表属性。
  - 每隔10分钟检测所有内部表的数据变化，如果满足数据条数变化超过上一次检测的10%，则后台触发该表的Analyze。
    说明
    这一步骤是为了检测到非显式DML（例如通过Flink、数据集成、HoloClient实时写入）更新的数据。
- 外部表
  每隔4小时定期巡检外部表元数据或数据变化情况。满足以下条件，系统后台触发表的Analyze，收集统计信息。
  外部表对应的外部系统的表（例如MaxCompute外部表和DLF外部表）在两次巡检间隔（例如4小时内）改变过，改变的标准是对应MaxCompute表的last_modify_time处于巡检间隔之间。
说明
巡检和执行在同一个调度任务中，所以下一次巡检调度开始依赖Analyze执行结束，只要离上一次开始巡检的时间满足调度周期，就可以进入下一次巡检。

配置参数

开启Auto Analyze后，系统默认会自动周期性巡检，决定需要执行Analyze的表，并进行采样计算，收集统计信息，对系统资源有一定的消耗。

在某些业务场景下，默认的机制可能不适用于业务场景，例如数据写入更新不频繁场景，可以通过修改默认参数来减少自动Analyze的频率。诸如此类可以根据业务情况更改默认参数，以此达到部分性能调优的目的。

说明

只有Superuser能调整Auto Analyze的默认行为，且都需要数据库级别设置参数，且在下一分钟后生效。

使用语法

--Superuser修改Auto Analyze参数的默认值
ALTER DATABASE <dbname> SET <GUC>=<values>;

dbname为数据库名称；GUC为参数名称；values为参数值。

参数列表

参数	参数描述	支持版本	默认值	使用示例
autovacuum_naptime	巡检是否有表的最新动作的周期，单位是秒（s）。	V1.1.0及以上版本说明需后台调整，如需调整请搜索（钉钉群号：32314975）加入实时数仓Hologres交流群申请。	60s	`ALTER DATABASE <dbname> SET autovacuum_naptime = 60;` `ALTER DATABASE <dbname> SET autovacuum_naptime = '60s';` `ALTER DATABASE <dbname> SET autovacuum_naptime = '10min';`
hg_auto_check_table_changes_interval	检查所有内部表的数据变化的周期，单位是秒（s）。	V1.1.0及以上版本	600s（10min）	`--V1.1及以上版本命令语法 ALTER DATABASE <dbname> SET hg_auto_check_table_changes_interval = '600s'; --V0.10版本命令语法 ALTER DATABASE <dbname> SET hg_experimental_auto_check_table_changes_interval = '600s';`
hg_auto_check_foreign_table_changes_interval	检查所有外部表的数据变化的周期，单位是秒（s）。	V1.1.0及以上版本	14400s（4小时）	`--V1.1及以上版本命令语法 ALTER DATABASE <dbname> SET hg_auto_check_foreign_table_changes_interval = '14400s'; --V0.10版本命令语法 ALTER DATABASE <dbname> SET hg_experimental_auto_check_foreign_table_changes_interval = '14400s';`
hg_experimental_auto_analyze_max_columns_count	自动收集统计信息的列数，单位是个。	V1.1.0及以上版本	256个	`ALTER DATABASE <dbname> SET hg_experimental_auto_analyze_max_columns_count =300;`
auto_analyze_work_memory_mb	Auto Analyze单个表的内存限制，单位是MB。	V1.1.54及以上版本	默认单个Worker 4096 MB，即4GB，实例规格越大，Worker越多，真实内存限制越大。	Auto Analyze单个表的内存限制修改为9GB。 `ALTER DATABASE <dbname> SETauto_analyze_work_memory_mb =9216;`
hg_experimental_auto_analyze_start_time	Auto-Analyze在每天运行的开始时间说明需要与end_time是同一时区，并且start time要小于等于end_time。	V1.1.54及以上版本	00:00 +0800	修改为仅需要在0～6点执行Auto-Analyze，白天内外部表数据不变，无需Analyze的情况。 `ALTER DATABASE <dbname> SET hg_experimental_auto_analyze_start_time = '00:00 +0800';` `ALTER DATABASE <dbname> SET hg_experimental_auto_analyze_end_time = '06:00 +0800';`
hg_experimental_auto_analyze_end_time	Auto Analyze在每天运行的结束时间。	V1.1.54及以上版本	23:59 +0800
autovacuum_enabled	表Auto Analyze的开启状态。	V1.1.54及以上版本	true，即默认全部开启。	关闭某表的Auto Analyze，以后Analyze将跳过此表。说明仅支持使用如下命令为Hologres内部表关闭Auto Analyze。 `ALTER TABLE <tablename> SET (autovacuum_enabled = false);`

其他优化

自Hologres V3.1版本开始，新增了Fast Rows功能，即当Hologres发现执行查询的SQL中表缺少统计信息时，会直接通过存储引擎获取该表的行数，获取行数的COST在10 ms左右。命令如下。

-- DB级别，执行后整个DB生效，开启语法
ALTER DATABASE dbname SET hg_experimental_get_fast_num_of_rows = ON;  -- 开启（默认关闭）

-- DB级别，执行后整个DB生效，开启语法
ALTER DATABASE dbname SET hg_experimental_get_fast_num_of_rows = OFF;  -- 关闭（默认关闭）

当未开启Fast Rows功能，在以下两种情况下，可有效减少计划不正确的概率。
- 若表没有统计信息，在查看查询计划时rows=1000行（表示没有统计信息，根据1000行预估生成计划）。
- 若系统认为表是空表，在查看查询计划时rows=1行（表示没有统计信息，根据1行预估生成计划）。
当开启Fast Rows功能且表没有统计信息，查看查询计划时rows不等于1000行（系统调用了存储引擎的结果，获取了表的行数）。

查询统计信息

表的统计信息被记录在hologres_statistic.hg_table_statistic表中，可以通过检查该表信息了解Analyze的状态，命令如下。

说明

如果需要查最近一次Analyze的信息，根据analyze_timestamp排序即可。

SELECT schema_name,                -- 表的Schema
       table_name,                 -- 表名称
       schema_version,             -- 表的版本
       statistic_version,          -- 最近一次ANALYZE的统计信息版本
       total_rows,                 -- 最近一次ANALYZE的行数
       analyze_timestamp           -- 最近一次ANALYZE的结束时间
FROM   hologres_statistic.hg_table_statistic
WHERE  table_name = '<tablename>'
ORDER BY analyze_timestamp DESC;

每个表在hologres_statistic.hg_table_statistic表中有0～n条记录。0条表示从未进行过Analyze，1条及以上表示运行过Analyze。
若出现两条及以上的情况，两条记录的schema_version一定不一样，因为表的Schema变化了（例如执行ADD COLUMN等命令会产生新的版本），会增加一条统计信息记录，老的schema_version对应的记录不再被使用。

示例查询结果如下，同一个表有两条记录，而第二条记录的schema_version低于第一条，那么第二条将作废，不会被使用，也无需关注。

 schema_name |    table_name    | schema_version | statistic_version | total_rows |  analyze_timestamp
-------------+------------------+----------------+-------------------+------------+---------------------
 public      | tbl_name_example |             13 |              8580 |      10002 | 2022-04-29 16:03:18
 public      | tbl_name_example |             10 |              8576 |      10002 | 2022-04-29 15:41:20
(2 rows)

Hologres V0.10和V1.1版本暂不会清理hg_table_statistic表中的历史过期记录，同时不用关心老的数据。

查看缺失统计信息的表

通过HG_STATS_MISSING视图，可以查看当前数据库中缺失统计信息的表，详情请参见HG_STATS_MISSING View。

常见问题

出现如下情况，代表Auto Analyze工作未正常，请参照解决方法进行处理。

表的统计信息是0条
- 问题现象：通过hologres_statistic.hg_table_statistic表查看表的统计信息，没有数据。
- 可能原因：
  - Auto Analyze没有工作，或者该表不符合Auto Analyze触发条件。
  - Auto Analyze本身的问题导致，需要搜索（钉钉群号：32314975）加入实时数仓Hologres交流群询问专业人员具体排查原因。
- 解决方法：手动触发一次Analyze。
analyze_timestamp过小
- 问题现象：查询结果中analyze_timestamp过小（即比当前时间小很多），代表长时间没有进行过Analyze。
- 可能原因：
  - 某种原因未能正常执行Auto Analyze。
  - 手动关闭过Auto Analyze。
- 解决方法：先手动触发Analyze，再搜索（钉钉群号：32314975）加入实时数仓Hologres交流群询问专业人员排查原因。