Greenplum性能优化之路 --（三）ANALYZE

说明

本文描述问题及解决方法同样适用于 腾讯云云数据仓库 CDW。

为什么需要ANALYZE

首先介绍下RBO和CBO，这是数据库引擎在执行SQL语句时的2种不同的优化策略。

RBO（Rule-Based Optimizer）

基于规则的优化器，就是优化器在优化查询计划的时候，是根据预先设置好的规则进行的，这些规则无法灵活改变。举个例子，索引优先于扫描，这是一个规则，优化器在遇到所有可以利用索引的地方，都不会选择扫描。这在多数情况下是正确的，但也不完全如此：

比如一张个人信息表中性别栏目加上索引，由于性别是只有2个值的枚举类，也就是常说的基数非常低的列，在这种列上使用索引往往效果还不如扫描

代码语言：txt复制

SELET count(*) FROM person WHERE gender = 'M';

因此RBO的优化方式是死板的，粗放的，目前已逐渐被CBO方式取代。

CBO（Cost Based Optimizer）

基于代价的优化器，就是优化器在优化查询计划的时候，是根据动态计算出来的Cost（代价）来判断如何进行选择。那如何计算代价呢？这里一般是基于代价模型和统计信息，代价模型是否合理，统计信息是否准确都会影响优化的效果。

还是拿上面员工性别统计为例，在CBO的优化方式下，物理计划就不会选择走索引。当然上面的例子比较简单，在Greenplum运行的复杂SQL中，优化器最核心的还是在scan和join的各种实现方式中做出选择，这才是能大幅提升性能的关键点。

前面提到CBO需要一个代价模型和统计信息，代价模型和规则一样，需要预先设置好，那统计信息是如何收集的？多数基于CBO优化的计算引擎，包括Greenplum，Oracle，Hive，Spark等都类似，除了可以按一定规则自动收集统计信息外，还都支持手动输入命令进行收集，通常这个命令都叫ANALYZE。

结论：由于CBO优化的需求，因此我们需要使用ANALYZE命令去收集统计信息。

ANALYZE怎么使用

说明

ANALYZE是Greenplum提供的收集统计信息的命令。

ANALYZE支持三种粒度，列，表，库，如下：

代码语言：txt复制

CREATE TABLE foo (id int NOT NULL, bar text NOT NULL) DISTRIBUTED BY (id); // 创建测试表foo
ANALYZE foo(bar);  // 只搜集bar列的统计信息
ANALYZE foo; // 搜集foo表的统计信息
ANALYZE; // 搜集当前库所有表的统计信息，需要有权限才行

限制

ANALYZE会给目标表加SHARE UPDATE EXCLUSIVE锁，也就是与UPDATE，DELETE，还有DDL语句冲突。

速度

ANALYZE是一种采样统计算法，通常不会扫描表中所有的数据，但是对于大表，也仍会消耗一定的时间和计算资源。

采样统计会有精度的问题，因此Greenplum也提供了一个参数default_statistics_target，调整采样的比例。简单说来，这个值设置得越大，采样的数量就越多，准确性就越高，但是消耗的时间和资源也越多。

直接修改服务器的参数会影响整个集群，通常不建议这样操作。如果确实有需要，可以尝试只修改某列的对应参数，如下：

代码语言：txt复制

ALTER TABLE {table_name}  ALTER COLUMN {col_name} SET STATISTICS {-1|0-1000};

时机

根据上文所述，ANALYZE会加锁并且也会消耗系统资源，因此运行命令需要选择合适的时机尽可能少的运行。根据Greenplum官网建议，以下3种情况发生后建议运行ANALYZE

批量加载数据后，比如COPY
创建索引之后
INSERT, UPDATE, and DELETE大量数据之后

自动化

除了手动运行，ANALYZE也可以自动化。实际上默认情况下，我们对空表写入数据后，Greenplum也会自动帮我们收集统计信息，不过之后在写入数据，就需要手动操作了。

有2个参数可以用来调整自动化收集的时机，gp_autostats_mode 和 gp_autostats_on_change_threshold。 gp_autostats_mode默认是on_no_stats，也就是如果表还没有统计信息，这时候写入数据会导致自动收集，这之后，无论表数据变化多大，都只能手动收集了。如果将gp_autostats_mode修改为on_change ，就是在数据变化量达到gp_autostats_on_change_threshold参数配置的量之后，系统就会自动收集统计信息。

分区表

Greenplum官网对于分区表的ANALYZE专门进行了讲解，其实只要保持默认值，不去修改系统参数optimizer_analyze_root_partition，那么对于分区表的操作并没有什么不同，直接在root表上进行ANALYZE即可，系统会自动把所有叶子节点的分区表的统计信息都收集起来。

如果分区表的数目很多，那在root表上进行ANALYZE可能会非常耗时，通常的分区表都是带有时间维度的，历史的分区表并不会修改，因此单独ANALYZE数据发生变化的分区，是更好的实践。

统计信息去了哪里

pg_class

表的大小是统计信息里面最直观，也几乎是最重要的，这个信息是放在pg_catalog.pg_class系统表中，reltuples代表元组数（行数），relpages代表实际占用的page数目（Greenplum中一个page为32KB）。

需要注意以下3点

1. reltuples不是准确值，获取表的准确行数还是需要count。

2. reltuples和relpages需要通过ANALYZE进行收集，对于已有数据的表，系统不会自动更新。

3. reltuples和relpages不一定能对齐，比如条数看起来不多的表，实际占用的page数目很大，这种一般是由于数据膨胀（bloat）造成，这时候需要vacuum等操作。

pg_statistic

关于列的统计信息都是存放在pg_catalog.pg_statistic系统表中。其中表的每一列（如果有统计）都会有一行对应的数据。了解并掌握pg_statistic的内容，对于深入理解查询优化非常重要。

列的统计信息内容很丰富，但是目的都是让优化器估算出，一个查询条件，能够过滤多少数据。

以下列举了pg_statistic的重要字段：

列名	说明
stanullfrac	null值的比例
stawidth	列值的平均宽度
stadistinct	大于0代表去重之后的个数小于0其绝对值代表去重之后个数的比例等于0代表无意义
stakindN	#define STATISTIC_KIND_MCV 1 #define STATISTIC_KIND_HISTOGRAM 2 #define STATISTIC_KIND_CORRELATION 3 #define STATISTIC_KIND_MCELEM 4 #define STATISTIC_KIND_DECHIST 5 #define STATISTIC_KIND_RANGE_LENGTH_HISTOGRAM 6 #define STATISTIC_KIND_BOUNDS_HISTOGRAM 7 #define STATISTIC_KIND_HLL 99

对于stakindN字段中的统计方式，这里选择3个最常见的进行说明：

1. STATISTIC_KIND_MCV

高频值，在一个列中出现最频繁的值。

高频值统计在很多场景下都有价值，这里举一个数据倾斜的hash join例子，如下代码：

代码语言：txt复制

/*
 * ExecHashBuildSkewHash
 *
 *		Set up for skew optimization if we can identify the most common values
 *		(MCVs) of the outer relation's join key.  We make a skew hash bucket
 *		for the hash value of each MCV, up to the number of slots allowed
 *		based on available memory.
 */
static void
ExecHashBuildSkewHash(HashJoinTable hashtable, Hash *node, int mcvsToUse){....}

hash join场景下，我们需要尽可能的把inner table构建在内存中，但内存资源是有限的，因此我们需要做出一些选择，什么内容优先放入内存中。如果外表有高频值，那我们可以考虑把高频值对应的内表信息优先放入到内存中，在实践中，Greenplum是单独构建一个skew hash table与main hash table并存。

2. STATISTIC_KIND_HISTOGRAM

直方图，使用等频直方图来描述一个列中的数据的分布。

直方图主要用于数据分布不均匀的情况下，对按列过滤后能返回多少数据进行预估。

举个例子，一个有3种产品的订单表，商品A很热销，订单量在90%，商品B一般，订单量在9%，商品C只有1%，则该列的NDV（Number of Distinct Value）值为3，如果一共有1000000条数据，在没有直方图统计的情况下，如果查询商品C的订单，优化器会预计要扫描1000000/3≈330000，因此可能选择全表scan，如果含有直方图统计，优化器就知道实际上C商品可能就几千条数据，因此会选择走索引。当然这个例子很简单，实际情况会复杂很多。

3. STATISTIC_KIND_CORRELATION

相关系数，记录的是当前列未排序的数据分布和排序后的数据分布的相关性。

用于估算索引扫描代价的，统计值在-1到1，值越大，表示相关性越高，也就是使用索引扫描代价越低。

举个例子，初始化如下2张表

代码语言：txt复制

create table t_correlation_asc (id int, number int) DISTRIBUTED BY (id);
INSERT INTO t_correlation_asc SELECT 1, i FROM generate_series(1, 1000) AS i;

create table t_correlation_desc (id int, number int) DISTRIBUTED BY (id);
INSERT INTO t_correlation_desc SELECT 1, 1001-i FROM generate_series(1, 1000) AS i;

在查看表对应的统计信息，可以看出在number列，你按升序写入1000个数，该列物理存储的数据实际上就是按升序排序的，反过来降序写入1000个数，由于顺序是相反的，所以相关性是-1

例子

以下将会构造一个大小表join的场景，来说明统计信息的收集对于查询计划的影响。

1. 初始化表结构和数据：

代码语言：txt复制

CREATE TABLE small_table (id int NOT NULL, bar text NOT NULL) DISTRIBUTED BY (id);
INSERT INTO small_table SELECT i, 'test:' || i FROM generate_series(1, 10) AS i;

CREATE TABLE big_table (id int NOT NULL, bar text NOT NULL) DISTRIBUTED BY (id);
INSERT INTO big_table SELECT i, 'test:' || i FROM generate_series(1, 100000) AS i;

pg_class中对应的数据如下：

2. 大小表join

注意为了构造小表广播的场景，这里关联键需要选择非分布键。

3. 给小表插入数据

这里给小表插入数据后，小表的数据量超过大表

代码语言：txt复制

INSERT INTO small_table SELECT i, 'test:' || i FROM generate_series(1, 200000) AS i;

在没有ANALYZE的情况下，pg_class中的数据没有发生变化，因此查询计划也没有发生变化。

4. 收集统计信息

运行ANALYZE收集小表的统计信息，如下：

在运行join语句，查询计划发生变化：

结论：查询优化器在收到新的统计信息之后，发现是2张数据量差不多的表进行join，因此选择重分布而不是小表广播。

数据库 TDSQLMySQL版云数据仓库PostgreSQL 云数据库postgresql 大数据

0 人点赞