调优建议｜学习笔记

开发者学堂课程【AnalyticDB PostgreSQL 产品调优及最佳实践：调优建议】学习笔记，与课程紧密联系，让用户快速学习知识。

课程地址：https://developer.aliyun.com/learning/course/1232/detail/18395

调优建议

 

内容介绍

一、ADB PG日常使用

二、执行计划

三、常用调优方式

 

一、ADB PG日常使用

表在MPP节点间的三种分布方式

ADB PG用的是MPP数据库，数据一般分布在各个节点上的，默认一般用 Hash分布方式，分布建很重要，默认在用户选择的时候挑一个合适的分布键，因为有的时候ADB PG会有一些从RDS TB有关的,因为RDS TB 为单机没有分布键的概念，没有加上DISTRIBUTED BY，默认是Hash分布，如果不带上DISTRIBUTED BY，就默认为第一列CREATE TABLE products(

第一列有的时候不是主键，如果是性变的话则说明倾斜的非常厉害，查询即发现性能不行，就需要各种诊断。因此数据分布一定要均匀是非常关键的。

（1）默认根据分布键的hash值分布

CREATE TABLB prouducts(

name varchar(40),

prod_id integer,

supplier_id integer)

DISTRIBUTED BY (prod_id0；

（2）若没有适合的列做hash分布，可以采用随机分布

CREATE TABLB random_stuff(

things text,

dooddads text,

etc text)

DISTRIBUTED RANDOMLY;

（3）小表、维度表在各个节点有一份全量复制

CREATE TABLB replicated_stuff(

things text,

doodads text,

etc text)

DISTRIBUTED RANDOMLY;

表的分布键选择原则

有主键就选择主键，如果多表需要JOIN，则看JOIN键是否可以作为主键，若JOIN键又为主键又是做JOIN最好，JOIN键如果是分布键能够减少网络sharp,不需要sharp到一个节点上再去算。

1、选择数据分布均匀的列:若选择的分布列数值分布不均匀，则可能导致数据倾斜。某些Segment分区节点存储数据多(查询负载高)。根据木桶原理，时间消耗会卡在数据多的节点上。故不应选择 bool 类型，时间日期类型数据作为分布键。（

2、选择经常需要JOIN 的列作为分布键，可以实现图一所示本地关联( Collocated JOIN)计算，即当JOIN键和分布键一致时，可以在 Segment分区节点内部完成JOIN，否则需要将一个表进行重分布( Redistribute motion)来实现图二所示重分布关联(Redistributed Join)或者广播其中小表(Broadcast motion)来实现图三所示广播关联( Broadcast Join)，后两种方式都会有较大的网络开销

3、尽量选择高频率出现的查询条件列作为分布键，从而可能实现按分布键做节点 segment 的裁剪

4、按主指定分布键，默认表的主键为分布键，若表没有主键，则默认将第一列当做分布键

5、分布键可以被定义为一个或多列；

6、谨慎选择随机分布DISTRIBUTED RANDOMLY,这将使得上述本地关联，或者节点裁剪不可能实现。

支持表按区间或者值进行分区，自动分区裁剪，提升查询效率

但目前来说也不太建议，因为现在用的CPU的优化器，如果多表交易的时候每张表分区表都很多的情况下，优化器生成的计划也慢一点，也不建议多表场景下分区太多，一般来说200-300就足够，不要将几年的分区都建了，以前出现这种情况后，我们建议用户如果查询量不高的，可以建一个月或一年的分区来减少分区的个数。分区也支持如下：

范围(RANGE)分区:基于一个数值型范围划分数据，例如按着日期区间定义。

值( LIST)分区:基于一个值列表划分数据，例如按着 城市属性定义

多级分区表:上述两种类型的多级组合，最多支持三级分区

分区表支持多种分区管理操作，包括新增分区，删除分区，重命名分区，清空截断分区，交换分区，分裂分区等

注意:分区个数建议小于200，否则会影响查询的SQL优化性能

https://help.alivun.com/document detail/118173.html

 

多级分区表设计实例，一级分区采用按月的区间（Range）分区，二级分区采用按地区的值（List）分区设计。

CREATE TABLE sales

(id int, yoax int,month int, day int, region text)

DISTRIBUTED BY (id)

PARTITION BY RANGE(month)

SUBPARTITION BY LIST (region)

SUBPARTITION TEMPLATE(

SUBPARTITION usa VALUES ('usa”)，

SUBPARTITION europe VALUES ('europe") ,

SUBPARTITION asia VALUES ('asia).

DEFAULT SUBPARTITION other regions)

(START (1) END (13) EVERY (1)，DEPAULT PARTITION other months );

支持数据按行存储或者按列存储列存根本上降低数据仓库大规模IO

行存表:高吞吐写入更新，点查询

数据按行存储，操作某列必须读入整行

适合较多数据更新操作场景

通过索引，支持高并发的点查询

CREATE TABLE foo (a INT，b TEXT) DISTRIBUTED BY (a);

列存表:批量加载，全表聚合，压缩率高、数据按列存储 -每一列单独存放

只访问查询涉及的列-大量降低系统IO

数据类型一致，数据特征相似 - 实现高压缩率

适合更新少，全表聚合操作

CREATE TABLE foo (a INT，b TEXT)

WITH (APPENDONLY = TRUE ， ORIENTATION=COLUMN)

DISTRIBUTED BY (a);

多种压缩算法，成本和性能取得平衡

数据压缩可用于列存表或者行存追加表，平均3倍以上数据压缩率

 

 CREATE TABLE foo (a int,b text)

WIT日 (appendonly=true， orientation=column， compresstype=zstd, compresslevel=9)

DISTRIBUTED BY (a);

列存也可以带上压缩，现在一般默认推荐LC4。相对来说LC4比zstd解压效果更好，但压缩效率差不多。

统计信息：目前自动收集方式

CPU的优化要根据统计信息来计算代价，所以统计信息的收集非常关键，现在是默认会收集统计信息。用下表中工具开发控制。如果关

闭则默认不收集，下面的参数不用更改。之前有一个客户反映在insert时感觉比想象中的慢，然后发现后台在insert的时候，若这张表没有数据单场插入的时候会花一点时间收集统计信息。即这些统计信息在后台是默认的，用户不用太感知。

·truncate语句会删除统计信息，再次插入数据时，数据表会处于无统计信息状态，再次插入数据时，会根据gp_autostats_mode参数按无统计信息处理

ON_CHANGE更新行数阈值由

gp_autostats_on_change_threshold控制

 

二、执行计划

执行计划：两种收集模式

收集模式

在控制台的诊断模块有一个分析诊断功能，里面有图形化的展现，但目前来说只做到了运行后的图形化展现，还不能做到动态实时的展现图。

 

执行计划：explain

如果explain估算出来的cost，估算大概有多少行，每一行的宽度，用的是postgres还是optimizer。

执行计划：explain analyze

 

(cost=2851.68..2934.18 rows=11000 width=16)(actual time=13.748..15.897 rows=11121 loops=1) 

前面为估算后面为真正执行时的行数。还可看出内存使用情况，判断内存是否足够或是否落盘。

执行计划：如何发现问题

（1）自上而下，梳理痛点

·从上向下梳理计划，查看时间到底花在了什么算子上面，然后针对具体算子深入分析

（2）查看代价，对比行数

·查看比较代价估算的异常( 特别小或特别大)，对比估算行数和实际执行行数，找到代价估算和行数估算的问题

（3）·耗时算子，尽量避免

·AP场景很少需要NestLoop、Sort+GroupByAgg，遇到它们需要谨慎

·(4)具体算子，是否合理

·Motion:是否有不必要的Motion?是否可以优化分布键?是否可以使用复制表?

·Join:内外表顺序是否合理?

·Scan:是否可以使用索引?是否可以使用分区表?

(5)内存信息，调整参数

·查看下盘情况，分析后适当调整statement_mem参数

通过索引提升查询性能

 

 

因为APE是兼容内核为9.4的PG的，有可能scanland可以加索引，如果用bitmap索引

或许可以用class聚集一下等方面都存在优化手段，但各个业务场景不一样因此优化手段也不一样。

优化建议

where条件的过滤效果较好时，可以尝试添加索引。TP场景尤其需要梳理业务，创建合适索引

创建索引后，建议进行统计信息收集

不建议在更新较多的表上建索引，更新较为频繁的表上创建索引，会降低数据更新效率

避免在一个表上建过多索引，一个表的索引数最好不超过6个

避免创建包含列数过多的组合索引，建议组合索引中包含的列数少于3列

组合索引是从前向后匹配where条件的，不能命中前导列的where条件，不会使用该索引

批量导入大量数据前可删除索引，导入数据后重建索引

避免创建重复的索引或具有相同前导列的索引

三、常用调优方式

SQL诊断

通过pg_stat_activity视图查看当前耗时较长的SQL

可看到当前正在运行中的一些circle，包括下表所示等内容

 

select current timestamp - query_start as runtime, datname, usename, waiting, waiting_reason, query

from pg_stat_activity

where current_query != '<IDLE>' order by 1 desc;

常用调优方式：消除Redistribute Motion

一种方法为分布键调整交易列，如果估算不太准，则可以把一张表建成一个分布表，每个节点一份，但这只针对小表，因为这很占用存储空间。

 t1表的分布键为t1.a，t2表的分布列是t2.b会出现t2表的重分布

t1表的分布键为t1.a，t2表的分布列是t2.a无需重分布直接Join

 

常用调优方式:消除Broadcast Motion

代价估算行数估算问题

如果计划中，一个分支中出现多次Broadcast，则需要观察行数估算信息，是否行数估小了，是否收集了统计信息

使用复制表

如果计划中，在基表扫描后，出现一次Broadcast，可以考虑将基表改为复制表、

 

常见调优方法：避免下盘

当分配给查询节点的内存不足以用来执行操作时，部分数据会下盘，影响查询性能

优化建议:调整statement_mem(默认2GB)

节点内存是有限的，每一条circle的内存使用也是有限的，因为如果每一条circle内存任务太多，会把系统打到MEM的，因此默认2GB，超了就会写磁盘，所以有的计算节点用的是临时磁盘大小，如果写落盘最多的一个节点不能超过60G。

 

其他调优方式：SQL优化策略（1）

（1）优化能力相关

优化的时候如果硬条件过多则优化circle的时间可能会较长。

索引一般挑索引力度比较好的，因为有些索引可能建了也没什么效果，前面在讲膨胀的地方有一个诊断建了以后用了多少次，索引的扫描和表的全局扫描，在数据里面一个叫index scan,一个叫cicon scan，cicon scan顺序扫描全表，index scan用索引怎么做，诊断里面有一个index scan和cicon scan次数对比，如果建了一个索引发现cicon scan少了10次，而index scan只少了一次，则意味着带where 条件的那个索引优化性可能估算代价走cicon scan比走index scan好。也意味着索引建了可能没有什么效果，所以不是所有场景下都要建索引。

·控制in条件或or条件的条目数量，过多的条目会导致ORCA优化时间加长

尽量避免在where条件中使用复杂表达式或函数操作，可能导致优化器行数估算不准确.

索引相关

为避免全表扫描，可考虑在where条件上加索引

where条件避免使用!=或<>操作符号，只有在=、<、<=、>>=、between时才可用到索引

where条件中避免使用or条件，or条件会导致索引失效，若索引可提升性能，可使用union替代or条件

like条件可考虑使用全文检索替代e

（2）数据类型相关

尽量使用数值类型，避免使用字符串类型，字符串类型会降低查询和连接性能

尽量使用varchar(n)替代char(n)，节省存储空间，减小计算内存，加速字符串比较效率

很多地方是follow PE思想的，PE思想在做字符串比较的时候，是有编解码相对数字比较耗时会更多一点，所以说通常情况下能用数值的用数值，有时候会发现有的客户有的字段DS叫日期，我们建议他建成timespace。但客户可能是从大数据那边过来的，就将它建成了DS字符串显示模式。但最好数比较多时间最好就建时间类型。

其他调优方式:SQL优化策略(2)

（1）输出列

尽量避免使用select*，从业务角度优化需要的输出列。如果是纯列纯表就不要用select*，但有些地方临时表比较方便。

（2）临时表

复杂查询可使用临时表暂存中间结果，一方面方便业务调试，另一方面避免不必要的重复计算

（3）where条件

如果有in子句，尽量将出现频率高的值放在in子句的前面，减少比较次数

（4）Join相关

一个查询中参与Join的表数量控制在12个以内，多余12个表join，可以考虑使用临时表拆分语句

（5）存储过程/函数

能使用SQL语句实现的，不要用循环去实现