为什么Redshift中带GROUP BY的聚合查询执行速度远慢于MSSQL？

问题描述 #

Redshift实例中有一张名为order的表，总数据量780000行，执行以下GROUP BY语句耗时超过60秒，但相同查询在MSSQL中仅需1秒：

select
    salesorderid
    ,max(orderid)           as max_order_id
    ,min(latestdelivery)    as min_latestdelivery
    ,max(latestdelivery)    as max_latestdelivery
    ,min(sourceid)          as min_sourceid
    ,max(sourceid)          as max_sourceid
    ,min(salesitem)         as min_salesitem
    ,max(salesitem)         as max_salesitem
    ,min(qty)               as min_qty
    ,max(qty)               as max_qty
    ,min(weight)            as min_weight
    ,max(weight)            as max_weight
    ,min(refb)              as min_refb
    ,max(refb)              as max_refb
    ,min(blocked)           as min_blocked
    ,max(blocked)           as max_blocked
    ,min(updatemode)        as min_updatemode
from public.order o
where o.datecreated >= getdate() - interval '24 month'
group by salesorderid;

对应执行计划如下：

XN HashAggregate  (cost=35513.57..52310.29 rows=419918 width=99)
->  XN Seq Scan on "order" o  (cost=0.00..9738.60 rows=606470 width=99)
Filter: (datecreated >= '2019-10-17 11:52:14'::timestamp without time zone)

原因分析 #

• 全表扫描开销高：从执行计划可以看到查询走了全表顺序扫描，你用datecreated作为过滤条件，如果表没有将datecreated设置为排序键或分区键，Redshift需要扫描所有数据块才能筛选出符合条件的行，而MSSQL大概率在datecreated上建有索引，过滤效率高很多。
• 跨节点数据shuffle开销：Redshift是MPP分布式架构，如果你的order表没有将group by字段salesorderid设置为分布键，那么同个salesorderid的数据会分散在不同节点，聚合时需要跨节点传输数据做合并，这部分网络开销在小数据量场景下占比极高，而MSSQL是单机架构不存在这类开销。
• MPP架构固定开销：Redshift的设计目标是TB/PB级海量数据的并行分析，所有查询都存在任务调度、节点间通信的固定开销，对于百万行级的小数据集，这部分开销远大于MSSQL这类面向OLTP场景的单机数据库的执行开销。
• 表存储参数不合理：如果表没有做合理的压缩编码设置，或者存在大量未回收的删除标记数据，也会导致扫描数据时的IO开销升高。

优化建议 #

• 调整表的分区与排序键：将datecreated设置为表的分区键（可按月分区），同时将datecreated设为排序键的第一列，这样查询时可以直接跳过不符合时间范围的分区和数据块，避免全表扫描。
• 调整分布键：将salesorderid设置为表的分布键（DISTKEY(salesorderid)），让同个salesorderid的所有数据都存储在同一个节点，GROUP BY时不需要跨节点shuffle数据，直接在各节点本地完成聚合即可。
• 预聚合优化：如果这类聚合查询是高频场景，可以创建物化视图提前按salesorderid分组计算好所有min/max指标，根据数据时效性要求设置物化视图的刷新周期，查询时直接访问物化视图即可，查询耗时可降到秒级以内。
• 优化扫描范围：如果业务允许，尽量缩小datecreated的过滤时间范围，减少需要扫描的总数据量。
• 优化表存储：对表的各字段设置合适的压缩编码，定期执行VACUUM ANALYZE public.order;回收无效数据、更新统计信息，提升扫描和聚合效率。

内容的提问来源于stack exchange，提问作者Antonio