Greenplum性能优化analyze

最新推荐文章于 2024-09-27 10:10:21 发布

代元培

最新推荐文章于 2024-09-27 10:10:21 发布

阅读量1.8k

点赞数

分类专栏： Greenplum 文章标签： ANALYZE

原文链接：https://cloud.tencent.com/developer/article/1685910

版权

Greenplum 专栏收录该内容

20 篇文章 1 订阅

订阅专栏

　　为什么需要ANALYZE

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

　　RBO（Rule-Based Optimizer）

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

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

　　因此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支持三种粒度，列，表，库，如下：

-- 创建表
CREATE TABLE open.t_ttt(
    f_id bigint,
    f_name character varying(128)
) WITH (appendonly=true) DISTRIBUTED BY (f_id);
-- 只搜集f_name列的统计信息
ANALYZE open.t_ttt(f_name);
-- 搜集open.t_ttt表的统计信息
ANALYZE open.t_ttt;
-- 搜集当前库所有表的统计信息，需要有权限才行
ANALYZE;

　　限制

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

　　速度

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

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

default_statistics_target

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

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

　　时机

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

　　批量加载数据后，比如COPY

　　创建索引之后

　　INSERT,UPDATE,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数据发生变化的分区，是更好的实践。

　　命令：analyze[talbe[(column,..)]]

　　收集表内容的统计信息，以优化执行计划。如创建索引后，执行此命令，对于随即查询将会利用索引。

　　自动统计信息收集

　　在postgresql.conf中有控制自动收集的参数gp_autostats_mode设置，gp_autostats_mode三个值：none、no_change、on_no_stats（默认）

　　none：禁止收集统计信息

　　on change：当一条DML执行后影响的行数超过gp_autostats_on_change_threshold参数指定的值时，会执行完这条DML后再自动执行一个analyze的操作来收集表的统计信息。

　　no_no_stats：当使用create talbe as select、insert、copy时，如果在目标表中没有收集过统计信息，那么会自动执行analyze来收集这张表的信息。gp默认使用on_no_stats，对数据库的消耗比较小，但是对于不断变更的表，数据库在第一次收集统计信息之后就不会再收集了。需要人为定时执行analyze。

　　如果有大量的运行时间在1分钟以下的SQL，你会发现大量的时间消耗在收集统计信息上。为了降低这一部分的消耗，可以指定对某些列不收集统计信息，如下所示：1.create table test(id int,name text,note text);上面是已知道表列note不需出现在join列上，也不会出现在where语句的过滤条件下，因为可以把这个列设置为不收集统计信息：1.alter table test alter note SET STATISTICS 0;