1. 表的分布键选择原则
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,这将使得上述本地关联,或者节点裁剪不可能实现
7.小表可选择复制表模式(DISTRIBUTED REPLICATED),在所有Segment上均保存一份全量数据
2. 行存列存的选择
行存:适合频繁更新或者访问大部分字段的场景,特点是高吞吐写入更新,点查询快。
- 数据按行存储,操作某列必须读入整行
- 适合较多数据更新操作场景
- 通过索引,支持高并发的点查询
列存:批量加载,全表聚合,压缩率高,查询列子集时速度更快,减少磁盘IO。列存储更适合压缩,因为都是相同类型的数据存在一起。
- 数据按列存储 – 每一列单独存放
- 只访问查询涉及的列 – 大量降低系统IO
- 数据类型一致,数据特征相似 – 实现高压缩率
- 适合更新少,全表聚合操作
3. 统计信息
- 统计信息收集方式可基于全库、表、列级别,可根据实际情况确定收集范围
- 导入数据后、超过20%数据更新I/U/D(insert/update/delete)后、创建索引后,需进行统计信息收集
- 用户ETL任务过程中,会涉及多次I/U/D,可根据客户业务情况,在其中适当添加analyze语句
- 调优过程中,从执行计划中看到表行数估算为1行,计划中出现较多的Broadcast、Sort+GroupByAgg、NestLoop等算子时,考虑对相应数据表进行analyze
4. 执行计划
explain | 显示执行计划,不真正执行语句,在计划中显示估算信息 |
explain analyze | 显示执行计划,并且真正执行语句,在计划中显示真实执行信息 |
初步查看执行计划(是否走索引、是否有痛点算子),然后分析真正执行时的耗时情况
算子名称 | 计划中算子节点的名字,以“->”开头进行缩进 如Seq Scan、Sort、Motion等 |
算子属性 | 算子在本计划中的操作属性 如例子中的SortKey:b表示Sort算子的排序键是b列 |
cost | 估算的代价,包含启动代价和总代价,中间用“...”间隔 |
rows | 估算的行数 |
width | 估算的每行的宽度,单位字节 |
Optimizer | 生成该计划的优化器名字,ADBPG具有优化器自适应功能,可能和用户设置的不一致 |
扫描算子Scan | Seq Scan, Index Scan, Bitmap Index Scan + Bitmap Heap Scan |
关联算子Join | Hash Join, Nested Loop Join, Merge Join |
聚合算子Aggregate | Hash Aggregate, Group Aggregate |
数据重分布算子Motion | Redistribute Motion, Broadcast Motion, Gather Motion |
其他算子 | Hash, Sort, Limit, Append, etc. |
自上而下,梳理痛点 | 从上向下梳理计划,查看时间到底花在了什么算子上面,然后针对具体算子深入分析 |
查看代价,对比行数 | 查看比较代价估算的异常(特别小或特别大),对比估算行数和实际执行行数,找到代价估算和行数估算的问题 |
耗时算子,尽量避免 | AP场景很少需要NestLoop、Sort+GroupByAgg,遇到它们需要谨慎 |
具体算子,是否合理 | Motion:是否有不必要的Motion?是否可以优化分布键?是否可以使用复制表? |
Join:内外表顺序是否合理? | Scan:是否可以使用索引?是否可以使用分区表? |
内存信息,调整参数 | 查看落盘情况,分析后适当调整statement_mem参数 |
● 查看是否小表广播,如果有的话先看下是否统计信息估算的少了,可以进行analyze,统计信息没有问题的话可以考虑使用维度表/复制表来消除小表广播
● 查看是否有数据重分布,如果有的话可以看下分布键是可以进行调整
● 查看是否有数据落盘,如果有的话可以适当调整查询内存:statement_mem
● 是否有空间膨胀,可以进行vacuum或vacuum full进行消除膨胀
-
- 表中的数据被删除或更新后(UPDATE/DELTE),物理存储层面并不会直接删除数据,而是标记这些数据不可见,所以会在数据页中留下很多“空洞”,在读取数据时,这些“空洞”会随数据页一起加载,拖慢数据扫描速度,需要定期回收删除的空间
● 主要查看cost较久的算子,然后进行详细分析
● 查看是否有走分区裁剪
● 避免数据倾斜,如果有数据倾斜的话可以进行修改分布键
select gp_segment_id, count(1) from t1 group by 1 order by 2 desc; ALTER TABLE t1 SET DISTRIBUTED BY (b);
SQL调优策略
- 优化能力相关
-
- 控制in条件或or条件的条目数量,过多的条目会导致ORCA优化时间加长
- 尽量避免在where条件中使用复杂表达式或函数操作,可能导致优化器行数估算不准确
- 索引相关
-
- 为避免全表扫描,可考虑在where条件上加索引
- where条件避免使用!=或<>操作符号,只有在=、<、<=、>、>=、between时才可用到索引
- where条件中避免使用or条件,or条件会导致索引失效,若索引可提升性能,可使用union替代or条件
- like条件可考虑使用全文检索替代
- 数据类型相关
-
- 尽量使用数值类型,避免使用字符串类型,字符串类型会降低查询和连接性能
- 尽量使用varchar(n)替代char(n),节省存储空间,减小计算内存,加速字符串比较效率
- 输出列
-
- 尽量避免使用select *,从业务角度优化需要的输出列
- 临时表
-
- 复杂查询可使用临时表暂存中间结果,一方面方便业务调试,另一方面避免不必要的重复计算
- where条件
-
- 如果有in子句,尽量将出现频率高的值放在in子句的前面,减少比较次数
- Join相关
-
- 一个查询中参与Join的表数量控制在12个以内,多于12个表join,可以考虑使用临时表拆分语句
- 存储过程/函数
-
- 能使用SQL语句实现的,不要用循环去实现