高级SQL优化之分组字段顺序优化

注：本文所使用的执行计划可视化工具为 PawSQL Explain Visualizer , 支持MySQL、PostgreSQL、openGauss等数据库执行计划的分析。

问题定义

如果一个查询中既包含来自同一个表的排序字段也包含分组字段，但字段顺序不同，可以通过调整分组字段顺序，使其和排序字段顺序一致，这样数据库可以避免一次排序操作。

考虑以下两个SQL, 二者唯一的不同点是分组字段的顺序(第一个SQL是o_custkey, o_orderdate, 第二个SQL是o_orderdate, o_custkey)，由于分组字段中不包括grouping set/cube/roll up等高级grouping操作，所以两个SQL是等价的。但是二者的执行计划及执行效率却不一样。第二个SQL的执行计划由于避免了对o_orderdate的一次排序操作，性能比第一个SQL要好，因此可以考虑将第一个SQL重写为第二个SQL。

select o_custkey, o_orderdate, sum(O_TOTALPRICE)
from orders
group by o_custkey,o_orderdate
order by o_orderdate;

重写为：

select o_custkey, o_orderdate, sum(o_totalprice)
from orders
group by o_orderdate,o_custkey
order by o_orderdate;

适用条件

分组字段重排序优化是针对查询块（Queryblock）来进行的，多个查询块可以独立进行此优化。分组字段重排序优化的适用条件如下：

• 在一个查询块中存在2个及2个以上分组字段。
• 在一个查询块中存在排序字段。
• 分组及排序排序字段来自同一个数据表。
• 分组排序字段无函数或计算。
• 排序字段是分组字段的真子集。
• 排序字段不是分组字段的前缀。

性能验证

• 优化前，在完成分组聚集运算后，还需要进行额外的排序操作，整体执行时间为339.64ms，其中排序所占用的时间为58.1ms。

• 重写优化后，无需对分组聚集的结果进行排序，整体执行时间为139.28ms, 性能提升了143.8%，也无需占用额外的内存。

PawSQL对此优化的支持

• 自动优化：PawSQL针对所有数据库默认开启此优化，以上的SQL可以PawSQL可以自动进行优化。

• 启用设置：用户可以在自己的默认优化设置或是定义每个优化任务的时候自主启用或禁用该选项。

关于PawSQL

PawSQL专注数据库性能优化的自动化和智能化，支持MySQL，PostgreSQL，openGauss，Oracle等数据库，提供的SQL优化产品包括

• PawSQL Cloud，在线自动化SQL优化工具，支持SQL审查，智能查询重写、基于代价的索引推荐，适用于数据库管理员及数据应用开发人员，
• PawSQL Advisor，IntelliJ 插件, 适用于数据应用开发人员，可以IDEA/DataGrip应用市场通过名称搜索“PawSQL Advisor”安装。
• PawSQL Engine, 是PawSQL系列产品的后端优化引擎，可以以docker镜像的方式独立安装部署，并通过http/json的接口提供SQL优化服务。

原文地址：https://mp.weixin.qq.com/s/sAhRRWQ9PUWe8uXIBR02uA