优化查询

使用本节中介绍的查询优化技巧来加快查询的运行速度，或者将其作为超出 Athena 资源限制的查询的变通方法。

优化联接

在分布式查询引擎中执行联接有许多不同的策略。其中最常见的两种是分布式哈希联接和具有复杂联接条件的查询。

在分布式哈希联接中，将大型表放在左边，小型表放在右边

最常见的连接类型使用等式比较作为连接条件。Athena 以分布式哈希联接的形式运行这种联接。

在分布式哈希联接中，引擎从联接的一端构建查找表（哈希表）。该端称为构建端。构建端的记录分布在各个节点上。每个节点都为其子集生成一个查找表。然后，联接的另一端（称为探测端）通过节点进行流式传输。探测端的记录以与构建端相同的方式分布在节点上。这使每个节点都能够通过在自己的查找表中查找匹配的记录来执行联接。

当从联接的构建端创建的查找表无法存入内存时，查询会失败。即使构建端的总大小小于可用内存，如果记录的分布存在明显偏差，查询也会失败。在极端情况下，所有记录的联接条件值可能相同，并且必须放入单个节点上的内存中。如果将一组值发送到同一个节点，并且这些值加起来超过可用内存，则即使是偏差较小的查询也会失败。节点确实能够将记录溢出到磁盘，但是溢出会减慢查询的执行速度，可能不足以防止查询失败。

Athena 尝试重新排序联接，使用较大的关系作为探测端，将较小的关系用作构建端。但是，由于 Athena 不管理表中的数据，因此它的信息有限，通常必须假设第一个表更大，第二个表更小。

使用基于等式的联接条件编写联接时，假设 JOIN 关键字左侧的表是探测端，右侧的表是构建端。确保右侧的表（即构建端）是表格中较小的一个。如果无法将联接的构建端缩小到足以放入内存，请考虑运行多个查询来联接构建表的子集。

使用 EXPLAIN 分析具有复杂联接的查询

具有复杂联接条件的查询（例如，使用 LIKE、> 或其他运算符的查询）通常对计算要求很高。在最糟糕的情况下，必须将联接一侧的每条记录与联接另一侧的每条记录进行比较。由于执行时间随记录数的平方而增长，因此此类查询有可能超过最大执行时间。

要提前了解 Athena 将如何执行您的查询，您可以使用 EXPLAIN 语句。有关更多信息，请参阅在 Athena 中使用 EXPLAIN 和 EXPLAIN ANALYZE 和了解 Athena EXPLAIN 语句结果。

缩小窗口函数的范围，或者将其移除

由于窗口函数是资源密集型操作，因此它们可能会使查询运行缓慢甚至失败，并显示消息查询在此缩放系数耗尽了资源。窗口函数将它们操作的所有记录保存在内存中，以便计算其结果。当窗口非常大时，窗口函数可能会耗尽内存。

要确保查询在可用内存限制内运行，请减小窗口函数操作的窗口的大小。为此，您可以添加 PARTITIONED BY 子句或缩小现有分区子句的范围。

使用非窗口函数

有时，使用窗口函数的查询可以在没有窗口函数的情况下重写。例如，您可以使用 ORDER BY 和 LIMIT，而不是使用 row_number 来查找前 N 条记录。您可以使用 max_by、min_by 和任意聚合函数，而不是使用 row_number 或 rank 删除重复记录。

例如，假设您有一个包含来自传感器的更新的数据集。传感器会定期报告其电池状态，并包含一些元数据（例如位置）。如果您想知道每个传感器的上次电池状态及其位置，可以使用以下查询：

SELECT sensor_id,
       arbitrary(location) AS location,
       max_by(battery_status, updated_at) AS battery_status
FROM sensor_readings
GROUP BY sensor_id

由于每条记录的位置等元数据都相同，因此您可以使用 arbitrary 函数从组中选取任何值。

要获取最后电池状态，您可以使用 max_by 函数。max_by 函数从发现另一列的最大值的记录中选取一列的值。在这种情况下，它将返回记录的电池状态以及组内最后一次更新时间。与使用窗口函数的等效查询相比，此查询运行速度更快，占用的内存也更少。

优化聚合

当 Athena 执行聚合时，它会使用 GROUP BY 子句中的列在工作节点之间分配记录。为了尽可能高效地将记录与组进行匹配，节点会尝试将记录保存在内存中，但必要时会将其溢出到磁盘。

避免在 GROUP BY 子句中包含多余的列也是个不错的做法。由于较少的列需要更少的内存，因此使用较少的列描述组的查询效率更高。数字列使用的内存也比字符串少。例如，当您聚合同时具有数字类别 ID 和类别名称的数据集时，在 GROUP BY 子句中仅使用类别 ID 列。

有时，查询会在 GROUP BY 子句中包含列，以避免列必须是 GROUP BY 子句的一部分或是聚合表达式。如果不遵守此规则，您可能会收到如下错误消息：

EXPRESSION_NOT_AGGREGATE：第 1:8 行：'category' 必须是聚合表达式或出现在 GROUP BY 子句中

为避免在 GROUP BY 子句中添加冗余列，可以使用任意函数，如下例所示。

SELECT country_id,
       arbitrary(country_name) AS country_name,
       COUNT(*) AS city_count
FROM world_cities
GROUP BY country_id

ARBITRARY 函数从组中返回任意值。当您知道组中所有记录的某列值相同，但该值不能标识该组时，该函数很有用。

优化前 N 个查询

ORDER BY 子句按排序顺序返回查询结果。Athena 使用分布式排序在多个节点上并行运行排序操作。

如果您不严格要求对结果进行排序，请避免添加 ORDER BY 子句。此外，如果不是严格必需的，请避免将 ORDER BY 添加到内部查询中。在许多情况下，查询计划程序可以删除冗余排序，但这并不能保证。此规则的一个例外情况是，如果内部查询正在执行前 N 个操作，例如查找 N 个最新或 N 个最常见的值。

当 Athena 看到 LIMIT 与 ORDER BY 一起使用时，它会知道您正在运行前 N 个查询，因此会相应地使用专用操作。

仅包含必需列

如果您并不严格需要某一列，请不要将其包含在查询中。查询需要处理的数据越少，运行速度就越快。这样既可以减少所需的内存量，也减少了必须在节点之间发送的数据量。如果您使用的是列式文件格式，则减少列数也会减少从 Amazon S3 读取的数据量。

Athena 对结果中的列数没有具体限制，但是查询的执行方式限制了可能的列组合大小。列的组合大小包括其名称和类型。

例如，以下错误是由超出关系描述符大小限制的关系引起的：

GENERIC_INTERNAL_ERROR: io.airlift.bytecode.CompilationException

要解决此问题，请减少查询中的列数，或创建子查询并使用可降低检索数据量的 JOIN。如果您的查询在最外层的查询中执行 SELECT *，则应将 * 更改为仅包含所需列的列表。

使用近似值优化查询

Athena 支持近似聚合函数，用于计算不同值、最常见的值、百分位数（包括近似中位数）和创建直方图。当不需要精确值时，请使用这些函数。

与 COUNT(DISTINCT col) 操作不同，approx_distinct 使用的内存要少得多，运行速度更快。同样，使用 numeric_histogram 代替直方图会使用近似法，因此内存更少。

优化 LIKE

您可以使用 LIKE 来查找匹配的字符串，但是对于长字符串，这将占用大量计算资源。在大多数情况下，regexp_like 函数是一种更快的替代方案，而且还提供了更大的灵活性。

通常，您可以通过锚定要查找的子字符串来优化搜索。例如，如果您正在寻找前缀，最好使用 'substr%' 而不是 '%substr%'。或者，如果您使用的是 regexp_like，请使用 '^substr'。

使用 UNION ALL 代替 UNION

UNION ALL 和 UNION 还有两种方法可以将两个查询的结果合并为一个结果。UNION ALL 将第一个查询的记录与第二个查询的记录连接起来，UNION 执行相同的操作，同时也会删除重复的记录。UNION 需要处理所有记录并找到重复项，这需要占用大量内存和计算，但 UNION ALL 操作速度相对较快。除非需要对记录进行重复数据删除，否则请使用 UNION ALL 以获得最佳性能。

对大型结果集使用 UNLOAD

当查询的结果预计会很大（例如，成千上万行或更多）时，请使用 UNLOAD 导出结果。在大多数情况下，这比运行常规查询要快，而且使用 UNLOAD 还可以让您更好地控制输出。

查询执行完毕后，Athena 会将结果作为单个未压缩的 CSV 文件存储在 Amazon S3 上。这需要比 UNLOAD 更长的时间，这不仅是因为结果未压缩，还因为操作无法并行化。相比之下，UNLOAD 直接从 Worker 节点写入结果，并充分利用计算集群的并行度。此外，您可以配置 UNLOAD 为以压缩格式和其他文件格式（例如 JSON 和 Parquet）写入结果。

有关更多信息，请参阅 UNLOAD。

使用 CTAS 或 Glue ETL 来具体化常用的聚合

‘Materializing' 查询是一种通过存储预先计算的复杂查询结果（例如聚合和联接），以便在后续查询中重复使用，从而提高查询性能的方法。

如果您的许多查询包含相同的联接和聚合，则可以将常见子查询具体化为新表，然后对该表运行查询。您可以使用 从查询结果创建表（CTAS） 或专用 ETL 工具（如 Glue ETL）创建新表。

例如，假设您有一个控制面板，其中包含显示订单数据集不同方面的小部件。每个小部件都有自己的查询，但所有查询都共享相同的联接和筛选器。订单表与订单项目表联接，并且有一个筛选器仅显示最近三个月。如果您确定了这些查询的常用功能，则可以创建小部件可以使用的新表。这样可以减少重复并提高性能。缺点是必须使新表保持最新状态。

重复使用查询结果

同一个查询通常会在短时间内多次运行。例如，当多人打开同一个数据控制面板时，可能会发生这种情况。运行查询时，您可以让 Athena 重复使用之前计算的结果。您可以指定要重复使用的结果的最大期限。如果之前在该时间范围内运行过相同的查询，Athena 将返回这些结果，而不会再次运行查询。有关更多信息，请参阅《Amazon Athena 用户指南》中的 在 Athena 中重复使用查询结果，以及 AWS 大数据博客中的通过 Amazon Athena 查询结果重复使用来降低成本并提高查询性能。