PolarDB-X 1.0-用户指南-SQL调优指南-SQL调优进阶-优化聚合与排序

本文涉及的产品
云原生数据库 PolarDB 分布式版,标准版 2核8GB
简介: 本文介绍如何优化器和执行器如何处理聚合(Group-by)与排序(Order-by)算子,以达到减少数据传输量和提高执行效率的效果。

基本概念

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

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

排序操作(Sort)语义为按照指定的ORDER BY列对输入进行排序。

本文介绍均为不下推的Agg或Sort的算子的实现。如果已被下推到LogicalView中,则由存储层MySQL来选择执行方式。

聚合(Agg)

聚合(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在输入数据已按分组列排序的情况,对各个分组依次完成聚合。

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

相比 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函数也被拆分成 SUMCOUNT 以实现两阶段的计算:

> 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`")

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

排序(Sort)

PolarDB-X中的排序算子主要包括 MemSort、TopN,以及 MergeSort。

  • MemSort
    PolarDB-X中的通用的排序实现为MemSort算子,即内存中运行快速排序(Quick Sort)算法。
    下面是一个用到MemSort算子的例子:
> explain select t1.name from t1 join t2 on t1.id = t2.id order by t1.name,t2.name;
Project(name="name")
  MemSort(sort="name ASC,name0 ASC")
    Project(name="name", name0="name0")
      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 ('?'))")
  • TopN
    当SQL中ORDER BY和LIMIT一起出现时,Sort算子和Limit算子会合并成TopN算子。
    TopN算子维护一个最大或最小堆,按照排序键的值,堆中始终保留最大或最小的N行数据。当处理完全部的输入数据时,堆中留下的N个行(或小于N个)就是需要的结果。
    下面是一个用到 TopN 算子的例子:
> explain select t1.name from t1 join t2 on t1.id = t2.id order by t1.name,t2.name limit 10;
Project(name="name")
  TopN(sort="name ASC,name0 ASC", offset=0, fetch=?0)
    Project(name="name", name0="name0")
      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 ('?'))")
  • MergeSort
    通常,只要语义允许,SQL中的排序操作会被下推到MySQL上执行,而PolarDB-X执行层只做最后的归并操作,即MergeSort。严格来说,MergeSort 不仅仅是排序,更是一种数据重分布算子(类似 Gather)。
    下面的SQL是对t1表进行排序,经过PolarDB-X查询优化器的优化,Sort算子被下推至各个MySQL分片中执行,最终只在上层做归并操作。
> explain select name from t1 order by name;
MergeSort(sort="name ASC")
  LogicalView(tables="t1", shardCount=2, sql="SELECT `name` FROM `t1` AS `t1` ORDER BY `name`")
  • 相比 MemSort,MergeSort 算法可以减少PolarDB-X层的内存消耗,并充分利用 MySQL 层的计算能力。

优化组合的例子

下面是一个组合优化的例子,在这个例子中,用到了以下优化规则:

  • Agg下推穿过Join
  • Join算法选择为SortMergeJoin
  • Agg算法选择为SortAgg
  • SortMergeJoin中需要的排序利用了SortAgg输出的有序
  • 两阶段Agg
  • Agg下推
  • Sort下推

>  explain select count(*) from t1 join t2 on t1.name = t2.name group by t1.name;
Project(count(*)="count(*) * count(*)0")
  SortMergeJoin(condition="name = name", type="inner")
    SortAgg(group="name", count(*)="SUM(count(*))")
      MergeSort(sort="name ASC")
        LogicalView(tables="t1", shardCount=2, sql="SELECT `name`, COUNT(*) AS `count(*)` FROM `t1` AS `t1` GROUP BY `name` ORDER BY `name`")
    SortAgg(group="name", count(*)="SUM(count(*))")
      MergeSort(sort="name ASC")
        LogicalView(tables="t2_[0-3]", shardCount=4, sql="SELECT `name`, COUNT(*) AS `count(*)` FROM `t2` AS `t2` GROUP BY `name` ORDER BY `n
相关实践学习
快速体验PolarDB开源数据库
本实验环境已内置PostgreSQL数据库以及PolarDB开源数据库:PolarDB PostgreSQL版和PolarDB分布式版,支持一键拉起使用,方便各位开发者学习使用。
相关文章
|
1月前
|
存储 SQL 关系型数据库
【MySQL调优】如何进行MySQL调优?从参数、数据建模、索引、SQL语句等方向,三万字详细解读MySQL的性能优化方案(2024版)
MySQL调优主要分为三个步骤:监控报警、排查慢SQL、MySQL调优。 排查慢SQL:开启慢查询日志 、找出最慢的几条SQL、分析查询计划 。 MySQL调优: 基础优化:缓存优化、硬件优化、参数优化、定期清理垃圾、使用合适的存储引擎、读写分离、分库分表; 表设计优化:数据类型优化、冷热数据分表等。 索引优化:考虑索引失效的11个场景、遵循索引设计原则、连接查询优化、排序优化、深分页查询优化、覆盖索引、索引下推、用普通索引等。 SQL优化。
272 15
【MySQL调优】如何进行MySQL调优?从参数、数据建模、索引、SQL语句等方向,三万字详细解读MySQL的性能优化方案(2024版)
|
2天前
|
SQL Oracle 关系型数据库
Oracle SQL:了解执行计划和性能调优
Oracle SQL:了解执行计划和性能调优
8 1
|
1月前
|
SQL 分布式计算 数据库
SQL调优总结
数据库表的规范化和反规范化设计,设计合适的字段数据类型……
38 8
|
1月前
|
SQL 关系型数据库 分布式数据库
PolarDB Proxy配置与优化:提升数据库访问效率
【9月更文挑战第6天】PolarDB是阿里云推出的高性能分布式关系型数据库,PolarDB Proxy作为其关键组件,位于客户端与PolarDB集群间,负责SQL请求的解析与转发,并支持连接池管理、SQL过滤及路由规则等功能。本文详细介绍了PolarDB Proxy的配置方法,包括连接池、负载均衡和SQL过滤设置,并探讨了监控调优、缓存及网络优化策略,以帮助提升数据库访问效率。
34 1
|
2月前
|
存储 缓存 负载均衡
【PolarDB-X 技术揭秘】Lizard B+tree:揭秘分布式数据库索引优化的终极奥秘!
【8月更文挑战第25天】PolarDB-X是阿里云的一款分布式数据库产品,其核心组件Lizard B+tree针对分布式环境优化,解决了传统B+tree面临的数据分片与跨节点查询等问题。Lizard B+tree通过一致性哈希实现数据分片,确保分布式一致性;智能分区实现了负载均衡;高效的搜索算法与缓存机制降低了查询延迟;副本机制确保了系统的高可用性。此外,PolarDB-X通过自适应分支因子、缓存优化、异步写入、数据压缩和智能分片等策略进一步提升了Lizard B+tree的性能,使其能够在分布式环境下提供高性能的索引服务。这些优化不仅提高了查询速度,还确保了系统的稳定性和可靠性。
66 5
|
2月前
|
存储 Oracle 关系型数据库
PolarDB-X 存储引擎核心技术 | Lizard B+tree 优化
PolarDB-X 分布式数据库,采用集中式和分布式一体化的架构,为了能够灵活应对混合负载业务,作为数据存储的 Data Node 节点采用了多种数据结构,其中使用行存的结构来提供在线事务处理能力,作为 100% 兼容 MySQL 生态的数据库,DN 在 InnoDB 的存储结构基础上,进行了深度优化,大幅提高了数据访问的效率。
7375 13
|
2月前
|
SQL 流计算
Flink SQL 在快手实践问题之Window TVF改进窗口聚合功能如何解决
Flink SQL 在快手实践问题之Window TVF改进窗口聚合功能如何解决
21 1
|
2月前
|
关系型数据库 分布式数据库 数据库
PolarDB资源隔离技术:在多租户环境中的应用与优化
随着云计算普及,多租户架构助力云服务商提供高效服务。阿里云PolarDB采用独特分布式设计,在多租户环境下确保每个用户数据独立与资源隔离。通过逻辑与物理隔离技术,如Schema和分区,结合分布式存储节点,实现资源独占及安全。此技术不仅保障数据安全,还能动态分配资源,满足高性能需求。通过优化资源分配、增强事务处理及监控机制,进一步提升PolarDB在多租户环境中的表现。
111 4
|
2月前
|
C# UED 定位技术
WPF控件大全:初学者必读,掌握控件使用技巧,让你的应用程序更上一层楼!
【8月更文挑战第31天】在WPF应用程序开发中,控件是实现用户界面交互的关键元素。WPF提供了丰富的控件库,包括基础控件(如`Button`、`TextBox`)、布局控件(如`StackPanel`、`Grid`)、数据绑定控件(如`ListBox`、`DataGrid`)等。本文将介绍这些控件的基本分类及使用技巧,并通过示例代码展示如何在项目中应用。合理选择控件并利用布局控件和数据绑定功能,可以提升用户体验和程序性能。
51 0
|
2月前
|
Java XML Maven
跨越时代的飞跃:Struts 2 升级秘籍——从旧版本无缝迁移到最新版,焕发应用新生!
【8月更文挑战第31天】随着软件技术的发展,Struts 2 框架也在不断更新。本文通过具体案例指导开发者如何从旧版平滑升级到 Struts 2.6.x。首先更新 `pom.xml` 中的依赖版本,并执行 `mvn clean install`。接着检查 `struts.xml` 配置,确保符合新版本要求,调整包扫描器等设置。审查 Action 类及其注解,检查配置文件中的弃用项及插件。更新自定义拦截器实现,并验证日志配置。最后,通过一系列测试确保升级后的系统正常运行。通过这些步骤,可以顺利完成 Struts 2 的版本升级,提升应用的安全性和性能。
115 0

相关产品

  • 云原生分布式数据库 PolarDB-X