SQL调优指南—SQL调优进阶—聚合优化和执行-阿里云开发者社区

开发者社区> -技术小能手-> 正文

SQL调优指南—SQL调优进阶—聚合优化和执行

简介: 本文介绍如何优化器和执行器如何处理聚合(Group-by),以达到减少数据传输量和提高执行效率的效果。
+关注继续查看

基本概念

聚合操作(Aggregate,简称Agg)语义为按照GROUP BY指定列对输入数据进行聚合的计算,或者不分组、对所有数据进行聚合的计算。PolarDB-X支持如下聚合函数:

  • COUNT
  • SUM
  • AVG
  • MAX
  • MIN
  • BIT_OR
  • BIT_XOR
  • GROUP_CONCAT

聚合(Agg)

本文介绍均为不下推的Agg的实现。如果已被下推到LogicalView中,则由存储层MySQL来选择执行方式,聚合(Agg)由两种主要的算子HashAgg和SortAgg实现。

HashAgg

HashAgg利用哈希表实现聚合:

  1. 根据输入行的分组列的值,通过Hash找到对应的分组。
  2. 按照指定的聚合函数,对该行进行聚合计算。
  3. 重复以上步骤直到处理完所有的输入行,最后输出聚合结果。


> explain select count(*) from t1 join t2 on t1.id = t2.id group by t1.name,t2.name;
Project(count(*)="count(*)")
  HashAgg(group="name,name0", count(*)="COUNT()")
    BKAJoin(condition="id = id", type="inner")
      Gather(concurrent=true)
        LogicalView(tables="t1", shardCount=2, sql="SELECT `id`, `name` FROM `t1` AS `t1`")
      Gather(concurrent=true)
        LogicalView(tables="t2_[0-3]", shardCount=4, sql="SELECT `id`, `name` FROM `t2` AS `t2` WHERE (`id` IN ('?'))")

Explain结果中,HashAgg算子还包含以下关键信息:

  • group:表示GROUP BY字段,示例中为name,name0分别引用t1,t2表的name列,当存在相同别名会通过后缀数字区分 。
  • 聚合函数:等号(=) 前为聚合函数对应的输出列名,其后为对应的计算方法。示例中 count(*)="COUNT()" ,第一个 count(*) 对应输出的列名,随后的COUNT()表示对其输入数据进行计数。

HashAgg对应可以通过Hint来关闭:/*+TDDL:cmd_extra(ENABLE_HASH_AGG=false)*/

SortAgg

SortAgg在输入数据已按分组列排序的情况,对各个分组依次完成聚合。

  • 保证输入按指定的分组列排序(例如,可能会看到 MergeSort 或 MemSort)。
  • 逐行读入输入数据,如果分组与当前分组相同,则对其进行聚合计算。
  • 如果分组与当前分组不同,则输出当前分组上的聚合结果。

相比 HashAgg,SortAgg 每次只要处理一个分组,内存消耗很小;相对的,HashAgg 需要把所有分组存储在内存中,需要消耗较多的内存。


> explain select count(*) from t1 join t2 on t1.id = t2.id group by t1.name,t2.name order by t1.name, t2.name;
Project(count(*)="count(*)")
  MemSort(sort="name ASC,name0 ASC")
    HashAgg(group="name,name0", count(*)="COUNT()")
      BKAJoin(condition="id = id", type="inner")
        Gather(concurrent=true)
          LogicalView(tables="t1", shardCount=2, sql="SELECT `id`, `name` FROM `t1` AS `t1`")
        Gather(concurrent=true)
          LogicalView(tables="t2_[0-3]", shardCount=4, sql="SELECT `id`, `name` FROM `t2` AS `t2` WHERE (`id` IN ('?'))")

SortAgg对应可以通过Hint来关闭:/*+TDDL:cmd_extra(ENABLE_SORT_AGG=false)*/

两阶段聚合优化

两阶段聚合,即通过将Agg拆分为部分聚合(Partial Agg)和最终聚合(Final Agg)的两个阶段,先对部分结果集做聚合,然后将这些部分聚合结果汇总,得到整体聚合的结果。

如下示例的SQL中,HashAgg 中拆分出的部分聚合(PartialAgg)会被下推至MySQL上的各个分表,而其中的AVG函数也被拆分成 SUM和 COUNT 以实现两阶段的计算:


> explain select avg(age) from t2 group by name
Project(avg(age)="sum_pushed_sum / sum_pushed_count")
  HashAgg(group="name", sum_pushed_sum="SUM(pushed_sum)", sum_pushed_count="SUM(pushed_count)")
    Gather(concurrent=true)
      LogicalView(tables="t2_[0-3]", shardCount=4, sql="SELECT `name`, SUM(`age`) AS `pushed_sum`, COUNT(`age`) AS `pushed_count` FROM `t2` AS `t2` GROUP BY `name`")

两阶段聚合的优化能大大减少数据传输量、提高执行效率。

总的来说,大部分场景做聚合的时候都倾向于选择HashAgg,只要当以下场景下才适合选择SortAgg做聚合:

  1. 数据比较多,内存严重不足。
  2. 聚合算子的输入已经按照Group By 列做好排序,这样做SortAgg就不需要额外排序,执行效率会更高。
  3. 当数据有严重倾斜,导致HashAgg执行效率不高,优先使用SortAgg

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
神经架构优化(NAO):新的神经架构搜索(NAS)算法
如果你是一名深度学习实践者,你可能发现自己经常会遇到同一个关键问题:我应该为现在的任务选择哪种神经网络架构?
521 0
《Oracle高性能SQL引擎剖析:SQL优化与调优机制详解》一2.4 执行计划各个操作的含义
本节书摘来自华章出版社《Oracle高性能SQL引擎剖析:SQL优化与调优机制详解》一 书中的第2章,第2.4节,作者:黄玮,更多章节内容可以访问云栖社区“华章计算机”公众号查看。
1569 0
2010年SQLite3学习笔记之五(进阶SQLite3中级编程知识线路图)
进阶SQLite3中级编程知识线路图 引言: 小老虎多多的针对SQLite3中级编程的知识要点。 一.编码转换 UTF8编码与GB2312 编码的类型相互转换; 二.安全保护 即对SQLite3进行安全保护,即密码保护; 三.
804 0
《Oracle高性能SQL引擎剖析:SQL优化与调优机制详解》一第3章 查 询 转 换
本节书摘来自华章出版社《Oracle高性能SQL引擎剖析:SQL优化与调优机制详解》一 书中的第3章,作者:黄玮,更多章节内容可以访问云栖社区“华章计算机”公众号查看。
852 0
1219
文章
0
问答
来源圈子
更多
文章排行榜
最热
最新
相关电子书
更多
文娱运维技术
立即下载
《SaaS模式云原生数据仓库应用场景实践》
立即下载
《看见新力量:二》电子书
立即下载