使用基于成本的优化器加速 Amazon Athena 查询 大数据博客

使用成本优化器加快 Amazon Athena 查询速度

关键要点

Amazon Athena 是一项无服务器的交互式分析服务，基于开源框架，支持开放表文件格式。Athena 提供了一种简化且灵活的方式，能够在数据所在位置直接分析 PB 级别的数据。用户可以通过 SQL 或 Python 从 Amazon Simple Storage Service (Amazon S3) 数据湖以及其他 30 种数据源包括本地数据源和其他云系统进行数据分析或构建应用。Athena 构建于开源的 Trino 和 Presto 引擎以及 Apache Spark 框架之上，不需要额外的配置或管理工作。

从今天开始，Athena SQL 引擎采用一种新的成本优化器 (CBO)，该特性利用存储在 AWS Glue 数据目录中的表和列统计信息，作为表的元数据。通过使用这些统计信息，CBO 可以改善查询执行计划，从而提升 Athena 中查询的性能。CBO 可利用的一些特定优化技术包括基于每个表和列的统计信息进行的连接重排序和聚合下推。

TPCDS 基准测试

这些基准测试显示了成本优化器的强大功能使用 CBO 启用的查询速度可提高至 2 倍，与没有启用 CBO 的同一 TPCDS 查询相比。

TPCDS 基准测试中的性能和成本比较

我们使用行业标准的 TPCDS 3 TB 数据集来代表不同的客户用例。这些用例代表了大小为基准的10倍的工作负载。这意味着 3 TB 的基准数据集准确地代表了 3050 TB 数据集上的客户工作负载。

在我们的测试中，数据集以未经压缩的 Parquet 格式存储在 Amazon S3 中，并使用 AWS Glue 数据目录来存储数据库和表的元数据。事实表按用于连接操作的日期列进行分区，每个事实表包含 2000 个分区。为了说明 CBO 的性能，我们比较了各种查询的执行情况，并突出了启用和禁用 CBO 时的性能差异。

以下图表展示了在启用与禁用 CBO 的情况下查询的运行时间。

以下图表展示了 TPCDS 基准测试中性能提升最大的前 10 个查询。

接下来，让我们讨论一些促进查询性能提高的成本优化技术。

基于成本的连接重排序

连接重排序是一种成本优化技术，分析不同的连接顺序，以选择最小化查询运行时间的顺序，从而减少每个步骤处理的中间数据量，降低内存和 CPU 要求。

我们以 TPCDS 3 TB 数据集中的查询 82 为例。此查询在四个表之间执行内连接：item、inventory、datedim 和 storesales。storesales 表有 86 亿行，并按日期进行分区。inventory 表有 10 亿行，也按日期进行分区。item 表包含 36 万行，datedim 表有 73 万行。

查询 82 代码示例

sqlselect iitemid iitemdesc icurrentpricefrom item inventory datedim storesaleswhere icurrentprice between 30 and 60and invitemsk = iitemskand ddatesk = invdateskand cast(ddate as date) between cast(20020530 as date) and (cast(20020530 as date) interval 60 day)and imanufactid in (437 129 727 663)and invquantityonhand between 100 and 500and ssitemsk = iitemskgroup by iitemid iitemdesc icurrentpriceorder by iitemidlimit 100

启用前的 CBO

在没有 CBO 的情况下，引擎将根据输入查询中表的顺序和内部启发式方法来确定连接顺序。输入查询的 FROM 子句为 from item inventory datedim storesales全部内连接。经过内部启发式处理后，Athena 选择的连接顺序为 ((item (inventory datedim)) storesales)，尽管 storesales 是最大的事实表，但因其在 FROM 子句中最后定义而被最后连接。这个计划未能尽早减小中间连接的大小，因此导致查询运行时间增加。以下示意图展示了没有 CBO 的连接顺序和不同阶段通过的行数。

启用后的 CBO

在使用 CBO 时，优化器通过多种数据包括统计信息、连接大小估计、连接构建侧和连接类型来确定最佳连接顺序。在这种情况下，Athena 选择的连接顺序为 ((storesales item) (inventory datedim))。在不被打乱顺序的情况下，最大的事实表 storesales 首先与 item 维度表连接。其他分区表 inventory 也首先与 datedim 维度表在原地连接。与维度表的连接会过滤掉事实表的数据，从而显著减少随后的连接输入数据大小。值得注意的是，表在连接中所处的侧是重要的，因为右侧的表将在内存中构建进行连接操作。因此，我们总是希望将较大的表放在左侧，较小的表放在右侧。CBO 选择的计划中，左侧的表原本为 86 亿，现在降至 1360 万。

使用 CBO 后，查询运行时间提升了 25从 15 秒降至 11 秒，选择了优化的连接顺序。

接下来，我们讨论 CBO 的另一个技术。

基于成本的聚合下推

聚合下推是一项通过推迟聚合操作如 SUM、COUNT 和 AVG到查询计划的更早阶段来提高性能的优化技术，同时保持相同的查询语义。这减少了阶段之间传输的数据量。通过最小化数据处理，聚合下推可以降低内存使用、I/O 成本和网络流量。

不过，聚合下推并不总是有利的，这取决于数据分布。例如，在进行连接前，在具有许多行但唯一值较少的列如性别上进行分组的效果更好。先进行分组意味着将大量记录聚合为较少的记录如只分为男性和女性。在连接后进行分组意味着大量记录必须参与连接后才能聚合。另一方面，在高基数列上，更好的做法是在连接之后进行分组。在连接前进行分组容易产生不必要的聚合开销，因为每个值可能是唯一的，这一步不会导致阶段间传输的数据量减少。

因此，是否推迟聚合应是基于成本的决定。让我们以运行在 3 TB TPCDS 数据集上的查询 2 为例，展示聚合下推的价值如何依赖于数据分布。websales 表有 21 亿行，catalogsales 表有 423 亿行，两个表都按日期列进行分区。

查询 2 代码示例

sqlwith wscs as ( select solddatesk salesprice from ( select wssolddatesk solddatesk wsextsalesprice salesprice from websales union all select cssolddatesk solddatesk csextsalesprice salesprice from catalogsales ))wswscs as ( select dweekseq sum(case when (ddayname=Sunday) then salesprice else null end) sunsales sum(case when (ddayname=Monday) then salesprice else null end) monsales sum(case when (ddayname=Tuesday) then salesprice else null end) tuesales sum(case when (ddayname=Wednesday) then salesprice else null end) wedsales sum(case when (ddayname=Thursday) then salesprice else null end) thusales sum(case when (ddayname=Friday) then salesprice else null end) frisales sum(case when (ddayname=Saturday) then salesprice else null end) satsales from wscs datedim where ddatesk = solddatesk group by dweekseq)select dweekseq1 round(sunsales1/sunsales2 2) round(monsales1/monsales2 2) round(tuesales1/tuesales2 2) round(wedsales1/wedsales2 2) round(thusales1/thusales2 2) round(frisales1/frisales2 2) round(satsales1/satsales2 2)from ( select wswscsdweekseq dweekseq1 sunsales sunsales1 monsales monsales1 tuesales tuesales1 wedsales wedsales1 thusales thusales1 frisales frisales1 satsales satsales1 from wswscs datedim where datedimdweekseq = wswscsdweekseq and dyear = 2001) y( select wswscsdweekseq dweekseq2 sunsales sunsales2 monsales monsales2 tuesales tuesales2 wedsales wedsales2 thusales thusales2 frisales frisales2 satsales satsales2 from wswscs datedim where datedimdweekseq = wswscsdweekseq and dyear = 2001 1) zwhere dweekseq1 = dweekseq2 53order by dweekseq1

启用前的 CBO

Athena 首先连接 websales 表与 catalogsales 表的联合所有结果，然后才对连接结果进行聚合。在这个例子中，需要连接的数据量巨大，导致查询运行时间较长。

启用后的 CBO

Athena 利用其中一个统计值，即唯一值计数，来评估推迟聚合和不推迟的成本影响。当某列有很多行但唯一值较少时，CBO 更有可能推迟聚合。这使得 websales 和 catalogsales 表匹配的行数缩减到 2590 和 3590 行。然后将这些聚合记录联合并用于与表的连接。与没有 CBO 的计划相比，来自两个大表参与连接的记录从 633 亿21 亿 423 亿降至仅 6180 行2590 3590。这显著减少了查询运行时间。

使用 CBO 后，查询运行时间提升了 50从 37 秒降至 18 秒。总而言之，CBO 助力 Athena 选择了一个最优的聚合下推计划，较之不使用成本优化将查询时间减半。

结论

在这篇文章中，我们讨论了 Athena 如何使用成本优化器 (CBO) 在其引擎 v3 中利用表统计信息生成更高效的查询执行计划。基于 TPCDS 基准测试的测试结果显示，使用 CBO 的情况下整体查询性能提升了 11。

CBO 使用的两项关键优化是连接重排序和聚合下推。连接重排序通过根据统计信息来智能选择连接表的顺序，从而减少中间数据。聚合下推则通过在有利时将聚合提前到计划的前面，减少了中间数据。

总结而言，Athena 的新成本优化器显著加速了查询，通过选择更优的执行计划来优化。CBO 基于存储在 AWS Glue 数据目录中的表统计信息进行优化。这种自动优化提升了 Athena 用户的生产力，使查询性能更为灵敏。如需利用 CBO 的优化技术，请参阅 处理列统计信息，以便在 AWS Glue 数据目录中生成表和列的统计信息。

关于作者

Darshit Thakkar 是 AWS 的技术产品经理，工作于 Amazon Athena 团队，位于马萨诸塞州波士顿。

Wei Zheng 是 Amazon Athena 的资深软件开发工程师。于 2021 年加入 AWS，参与了多个针对 Athena 性能的改进工作。

Chuho Chang 是 Amazon Athena 的软件开发工程师，已有十多年的查询优化器工作经验。

Pathik Shah 是 Amazon Athena 的资深分析架构师。自 2015 年加入 AWS 以来，他一直专注于大数据分析领域，帮助客户构建可扩展且稳健的解决方案。

正在加载评论