在 Amazon Athena 中使用 EXPLAIN 和 EXPLAIN ANALYZE 优化联合查询性能

Amazon Athena 是一种交互式查询服务，可使用标准 SQL 轻松分析 Amazon Simple Storage Service（Amazon S3）中的数据。Athena 是一种无服务器服务，因此您无需管理任何基础设施，而且只需为所运行的查询付费。2019 年，Athena 增加了对联合查询的支持，以便跨存储在关系、非关系、对象和自定义数据来源中的数据运行 SQL 查询。

2021 年，Athena 增加了对 EXPLAIN 语句的支持，可以帮助您理解和提高查询的效率。EXPLAIN 语句提供查询运行计划的详细细分。您可以对计划进行分析，以确定和降低查询复杂性，并改善其运行时间。您还可以在运行查询之前使用 EXPLAIN 验证 SQL 语法。这样做有助于防止在运行查询时可能发生的错误。

Athena 还增加了 EXPLAIN ANALYZE，可以显示查询的计算成本及其运行计划。管理员可以从使用 EXPLAIN ANALYZE 中受益，因为它提供了扫描数据的计数，可帮助您减少用户查询带来的财务影响，并应用优化以更好地控制成本。

在这篇博文中，我们将演示如何使用和解释 EXPLAIN 和 EXPLAIN ANALYZE 语句，来提高查询多个数据来源时的 Athena 查询性能。

解决方案概览

为了演示如何使用 EXPLAIN 和 EXPLAIN ANALYZE 语句，我们使用以下服务和资源：

来自美国职棒大联盟数据集的五个数据库表
AWS CloudFormation 用于预调配 AWS 资源
Amazon S3、Amazon DynamoDB 和 Amazon Aurora MySQL 兼容版作为数据存储

Athena 使用 AWS Glue 数据目录在您的 AWS 账户中存储和检索 Amazon S3 数据的表元数据。表元数据让 Athena 查询引擎知道如何查找、读取和处理要查询的数据。我们使用 Athena 数据来源连接器连接到 Amazon S3 外部数据来源。

先决条件

要部署 CloudFormation 模板，您必须具备以下内容：

一个 AWS 账户
有权访问以下服务的 AWS Identity and Access Management（IAM）用户：
- Amazon Athena
- AWS CloudFormation
- Amazon DynamoDB
- AWS Glue
- AWS Lambda
- Amazon Relational Database Service（Amazon RDS）
- Amazon S3
- Amazon Virtual Private Cloud（Amazon VPC）

使用 AWS CloudFormation 预调配资源

要部署 CloudFormation 模板，请完成以下步骤：

选择 Launch Stack（启动堆栈）：

按照 AWS CloudFormation 控制台上的提示创建堆栈。
注意堆栈的 Outputs（输出）选项卡上的键值对。

配置 Athena 数据来源连接器时使用这些值。

CloudFormation 模板会创建以下资源：

S3 存储桶用于存储数据并充当 Lambda 的临时溢出存储桶
用于 S3 存储桶中数据的 AWS Glue 数据目录表
一个 DynamoDB 表和 Amazon RDS for MySQL 表，用于连接来自不同来源的多个表
Amazon RDS for MySQL 和 DynamoDB 需要的 VPC、子网和端点

下图显示了数据加载的高级数据模型。

创建 DynamoDB 数据来源连接器

要为 Athena 创建 DynamoDB 连接器，请完成以下步骤：

在 Athena 控制台的导航窗格中，选择 Data sources（数据来源）。
选择 Create data source（创建数据来源）。
对于 Data sources（数据来源），选择 Amazon DynamoDB。
选择 Next（下一步）。

对于 Data source name（数据来源名称），输入 DDB。

对于 Lambda function（Lambda 函数），选择 Create Lambda function（创建 Lambda 函数）。

这时将会在您的浏览器中打开一个新选项卡。

对于 Application name（应用程序名称），输入 AthenaDynamoDBConnector。
对于 SpillBucket，输入 AthenaSpillBucket 的 CloudFormation 堆栈中的值。
对于 AthenaCatalogName，输入 dynamodb-lambda-func。
将剩余值保留为默认值。
选择 I acknowledge that this app creates custom IAM roles and resource policies（我确认此应用程序创建了自定义 IAM 角色和资源策略）。
选择 Deploy（部署）。

您将返回到 Athena 控制台上的 Connect data sources（连接数据来源）部分。

选择 Lambda function（Lambda 函数）旁边的刷新图标。
选择您刚刚创建的 Lambda 函数（dynamodb-lambda-func）。

选择 Next（下一步）。
查看设置并选择 Create data source（创建数据来源）。
如果您尚未设置 Athena 查询结果位置，请选择 Athena 查询编辑器页面上的 View settings（查看设置）。

选择 Manage（管理）。
对于 Location of query result（查询结果的位置），浏览 CloudFormation 模板中为 Athena 溢出存储桶指定的 S3 存储桶。
将 Athena-query 添加到 S3 路径中。
选择 Save（保存）。

在 Athena 查询编辑器中，对于 Data source（数据来源），选择 DDB。
对于 Database（数据库），选择 default（默认）。

现在，您可以浏览 sportseventinfo 表的模式；DynamoDB 中的数据是相同的。

选择 sportseventinfo 表的选项图标，然后选择 Preview Table（预览表）。

创建 Amazon RDS for MySQL 数据来源连接器

现在我们为 Amazon RDS for MySQL 创建连接器。

在 Athena 控制台的导航窗格中，选择 Data sources（数据来源）。
选择 Create data source（创建数据来源）。
对于 Data sources（数据来源），选择 MySQL。
选择 Next（下一步）。

对于 Data source name（数据来源名称），输入 MySQL。

对于 Lambda function（Lambda 函数），选择 Create Lambda function（创建 Lambda 函数）。

对于 Application name（应用程序名称），输入 AthenaMySQLConnector。
对于 SecretNamePrefix，输入 AthenaMySQLFederation。
对于 SpillBucket，输入 AthenaSpillBucket 的 CloudFormation 堆栈中的值。
对于 DefaultConnectionString，输入 MySQLConnection 的 CloudFormation 堆栈中的值。
对于 LambdaFunctionName，输入 mysql-lambda-func。
对于 SecurityGroupIds，输入 RDSSecurityGroup 的 CloudFormation 堆栈中的值。
对于 SubnetIds，输入 RDSSubnets 的 CloudFormation 堆栈中的值。
选择 I acknowledge that this app creates custom IAM roles and resource policies（我确认此应用程序创建了自定义 IAM 角色和资源策略）。
选择 Deploy（部署）。

在 Lambda 控制台上，打开您创建的函数（mysql-lambda-func）。
在 Configuration（配置）选项卡的 Environment variables（环境变量）下，选择 Edit（编辑）。

选择 Add environment variable（添加环境变量）。
输入新的键值对：
- 对于 Key（键），输入 MYSQL_connection_string。
- 对于 Value（值），输入 MySQLConnection 的 CloudFormation 堆栈中的值。
选择 Save（保存）。

返回 Athena 控制台上的 Connect data sources（连接数据来源）部分。
选择 Lambda function（Lambda 函数）旁边的刷新图标。
选择您创建的 Lambda 函数（mysql-lamdba-function）。

选择 Next（下一步）。
查看设置并选择 Create data source（创建数据来源）。
在 Athena 查询编辑器中，对于 Data Source（数据来源），选择 MYSQL。
对于 Database（数据库），选择 sportsdata。

选择表旁边的选项图标，然后选择 Preview Table（预览表）以检查数据和模式。

在以下各节中，我们将演示优化查询的不同方法。

使用 EXPLAIN 计划的最佳联接顺序

联接是一种基本 SQL 操作，它使用匹配列中的关系来查询多个表中的数据。联接操作会影响从表中读取的数据量、通过网络传输到中间阶段的数据量，以及构建哈希表以促进联接所需的内存量。

如果您有多个联接操作，但这些联接表的顺序不正确，则可能会遇到性能问题。为了演示这一点，我们使用以下来自不同来源的表，并按一定的顺序联接它们。然后，我们使用 Athena 的 EXPLAIN 功能观察查询运行时并提高性能，该功能为优化查询提供了一些建议。

您之前运行的 CloudFormation 模板将数据加载到以下服务中：

AWS 存储	表名称	行数
Amazon DynamoDB	sportseventinfo	657
Amazon S3	person	7,025,585
Amazon S3	ticketinfo	2,488

我们构造一个查询，按门票类型查找所有参与活动的人。使用以下联接的查询运行时间大约需要 7 分钟才完成：

SELECT t.id AS ticket_id,
e.eventid,
p.first_name 
FROM 
"DDB"."default"."sportseventinfo" e,
"AwsDataCatalog"."athenablog"."person" p,
"AwsDataCatalog"."athenablog"."ticketinfo" t 
WHERE 
t.sporting_event_id = cast(e.eventid as double) 
AND t.ticketholder_id = p.id

现在我们在查询中使用 EXPLAIN 来查看其运行计划。我们使用与之前相同的查询，但添加了解释（TYPE DISTRIBUTED）：

EXPLAIN (TYPE DISTRIBUTED)
SELECT t.id AS ticket_id,
e.eventid,
p.first_name 
FROM 
"DDB"."default"."sportseventinfo" e,
"AwsDataCatalog"."athenablog"."person" p,
"AwsDataCatalog"."athenablog"."ticketinfo" t 
WHERE 
t.sporting_event_id = cast(e.eventid as double) 
AND t.ticketholder_id = p.id

以下屏幕截图显示了我们的输出

注意片段 1 中的交叉联接。联接将转换为每个表的笛卡尔乘积，其中表中的每条记录都与另一个表中的每条记录进行比较。因此，完成此查询需要大量时间。

为了优化我们的查询，我们可以通过将联接表重新排序为首先是 sportseventinfo，其次是 ticketinfo，最后是 person 来重写。之所以出现这种情况，是因为在查询计划阶段转换为 JOIN ON 子句的 WHERE 子句在 person 表和 sportseventinfo 表之间没有联接关系。因此，查询计划生成器将联接类型转换为交叉联接（笛卡尔乘积），这会降低效率。对表进行重新排序会使 WHERE 子句与 INNER JOIN 类型对齐，该类型满足 JOIN ON 子句的要求，运行时间从 7 分钟缩短到 10 秒。

我们优化的查询的代码如下所示：

SELECT t.id AS ticket_id,
e.eventid,
p.first_name 
FROM 
"DDB"."default"."sportseventinfo" e,
"AwsDataCatalog"."athenablog"."ticketinfo" t,
"AwsDataCatalog"."athenablog"."person" p 
WHERE 
t.sporting_event_id = cast(e.eventid as double) 
AND t.ticketholder_id = p.id

以下是我们对联接子句进行重新排序后的查询的 EXPLAIN 输出：

EXPLAIN (TYPE DISTRIBUTED) 
SELECT t.id AS ticket_id,
e.eventid,
p.first_name 
FROM 
"DDB"."default"."sportseventinfo" e,
"AwsDataCatalog"."athenablog"."ticketinfo" t,
"AwsDataCatalog"."athenablog"."person" p 
WHERE t.sporting_event_id = cast(e.eventid as double) 
AND t.ticketholder_id = p.id

以下屏幕截图显示了我们的输出。

交叉联接更改为在列（eventid、id、ticketholder_id）上进行联接的 INNER JOIN，这会导致查询运行得更快。ticketinfo 和 person 表之间的联接转换为 PARTITION 分配类型，其中由于 person 表的大小，左表和右表都会在所有 Worker 节点之间进行哈希分区。sportseventinfo 表和 ticketinfo 之间的联接将转换为 REPLICATED 分配类型，其中一个表在所有 Worker 节点之间进行哈希分区，另一个表复制到所有 Worker 节点以执行联接操作。

有关如何分析这些结果的更多信息，请参阅了解 Athena EXPLAIN 语句结果。

作为最佳实践，我们建议将 JOIN 语句与 ON 子句一起使用，如以下代码所示：

SELECT t.id AS ticket_id,
e.eventid,
p.first_name 
FROM 
"AwsDataCatalog"."athenablog"."person" p 
JOIN "AwsDataCatalog"."athenablog"."ticketinfo" t ON t.ticketholder_id = p.id 
JOIN "ddb"."default"."sportseventinfo" e ON t.sporting_event_id = cast(e.eventid as double)

另外，在联接两个表时，最佳做法是在联接的左侧指定较大的表，在联接的右侧指定较小的表。Athena 将右侧的表分配给 Worker 节点，然后流式传输左侧的表以进行联接。如果右侧的表较小，则占用的内存量较少，并且查询运行速度更快。

在以下各节中，我们将举例说明如何使用 EXPLAIN ANALYZE 优化筛选谓词的下推以及针对 Athena 数据来源的投影筛选操作。

Amazon RDS for MySQL 的 Athena 连接器的下推优化

下推是一种优化，通过将 SQL 查询的处理移到尽可能靠近数据的位置，从而提高 SQL 查询的性能。下推可以在通过网络传输数据之前对其进行筛选，在将数据加载到内存之前对其进行筛选，从而大大缩短 SQL 语句的处理时间。Amazon RDS for MySQL 的 Athena 连接器支持筛选谓词的下推和投影下推。

下表总结了我们用来演示使用 Aurora MySQL 进行下推的服务和表。

表名称	行数	以 KB 为单位的大小
player_partitioned	5,157	318.86
sport_team_partitioned	62	5.32

我们使用以下查询作为筛选谓词和投影筛选的示例：

SELECT full_name,
name 
FROM "sportsdata"."player_partitioned" a 
JOIN "sportsdata"."sport_team_partitioned" b ON a.sport_team_id=b.id 
WHERE a.id='1.0'

此查询根据球员的 ID 选择球员及其球队。它作为 WHERE 子句中的筛选操作和投影的示例，因为它只选择了两列。

我们使用 EXPLAIN ANALYZE 来获取运行此查询的成本：

EXPLAIN ANALYZE 
SELECT full_name,
name 
FROM "MYSQL"."sportsdata"."player_partitioned" a 
JOIN "MYSQL"."sportsdata"."sport_team_partitioned" b ON a.sport_team_id=b.id 
WHERE a.id='1.0'

以下屏幕截图显示了 player_partitioned 表的片段 2 中的输出，在该输出中，我们观察到连接器在源端有一个成功的下推筛选，因此它只尝试扫描表中 5,157 条记录中的一条记录。输出还显示查询扫描只有两列（full_name 作为投影列，sport_team_id 作为联接列），并使用 SELECT 和 JOIN，这表示投影下推成功。这有助于减少使用 Athena 数据来源连接器时的数据扫描。

现在我们看看筛选谓词下推不适用于 Athena 连接器的条件。

筛选谓词中的 LIKE 语句

我们从以下示例查询开始，演示在筛选谓词中使用 LIKE 语句：

SELECT * 
FROM "MYSQL"."sportsdata"."player_partitioned" 
WHERE first_name LIKE '%Aar%'

然后我们添加 EXPLAIN ANALYZE：

EXPLAIN ANALYZE 
SELECT * 
FROM "MYSQL"."sportsdata"."player_partitioned" 
WHERE first_name LIKE '%Aar%'

EXPLAIN ANALYZE 输出显示，尽管 WHERE 子句只有 30 条与条件 %Aar% 匹配的记录，查询会对所有记录执行表扫描（扫描包含 5,157 条记录的 player_partitioned 表）。因此，即使使用 WHERE 子句，数据扫描也会显示完整的表大小。

我们可以通过只选择所需的列来优化相同的查询：

EXPLAIN ANALYZE 
SELECT sport_team_id,
full_name 
FROM "MYSQL"."sportsdata"."player_partitioned" 
WHERE first_name LIKE '%Aar%'

从 EXPLAIN ANALYZE 输出中，我们可以观察到连接器支持投影筛选下推，因为我们只选择了两列。这使数据扫描大小缩小到表大小的一半。

筛选谓词中的 OR 语句

我们从以下查询开始，演示在筛选谓词中使用 OR 语句：

SELECT id,
first_name 
FROM "MYSQL"."sportsdata"."player_partitioned" 
WHERE first_name = 'Aaron' OR id ='1.0'

我们在前面的查询中使用 EXPLAIN ANALYZE，如下所示：

EXPLAIN ANALYZE 
SELECT * 
FROM 
"MYSQL"."sportsdata"."player_partitioned" 
WHERE first_name = 'Aaron' OR id ='1.0'

与 LIKE 语句类似，以下输出显示查询扫描了表，而不是仅下推到与 WHERE 子句匹配的记录。此查询仅输出 16 条记录，但数据扫描表示已完成扫描。

适用于 DynamoDB 的 Athena 连接器的下推优化

对于使用 DynamoDB 连接器的示例，我们使用以下数据：

表	行数	以 KB 为单位的大小
sportseventinfo	657	85.75

我们使用以下查询测试 DynamoDB 表的筛选谓词和项目筛选操作。此查询尝试获取给定地点的所有活动和体育赛事。我们使用 EXPLAIN ANALYZE 进行查询，如下所示：

EXPLAIN ANALYZE 
SELECT EventId,
Sport 
FROM "DDB"."default"."sportseventinfo" 
WHERE Location = 'Chase Field'

EXPLAIN ANALYZE 的输出显示，筛选谓词仅检索了 21 条记录，而项目筛选只选择了两列下推到源。因此，此查询的数据扫描小于表大小。

现在我们看看筛选谓词下推在哪里不起作用。在 WHERE 子句中，如果您将 TRIM() 函数应用于 Location 列然后进行筛选，谓词下推优化不适用，但我们仍会看到投影筛选优化，它确实适用。请参阅以下代码：

EXPLAIN ANALYZE 
SELECT EventId,
Sport 
FROM "DDB"."default"."sportseventinfo" 
WHERE trim(Location) = 'Chase Field'

此查询的 EXPLAIN ANALYZE 输出显示，该查询会扫描所有行，但仍仅限于两列，这表明在应用 TRIM 函数时，筛选谓词不起作用。

我们从前面的示例中看到，Amazon RDS for MySQL 的 Athena 数据来源连接器确实支持用于下推优化的筛选谓词和投影谓词，但我们也看到，在筛选谓词中使用时，LIKE、OR 和 TRIM 等操作不支持下推到源。因此，如果您在联合 Athena 查询中遇到无法解释的费用，我们建议将 EXPLAIN ANALYZE 与查询结合使用，并确定您的 Athena 连接器是否支持下推操作。

请注意，运行 EXPLAIN ANALYZE 会产生成本，因为它会扫描数据。

结论

在这篇博文中，我们展示了如何使用 EXPLAIN 和 EXPLAIN ANALYZE 来分析 AWS S3 上数据来源的 Athena SQL 查询，以及 DynamoDB 和 Amazon RDS for MySQL 等数据来源的 Athena 联合 SQL 查询。您可以以此为例来优化查询，这也将节省成本。

关于作者

Nishchai JM 是 Amazon Web Services 的分析专家解决方案架构师。他擅长构建大数据应用程序，并帮助客户在云端实现应用程序的现代化。他认为数据是新石油，大部分时间都花在从数据中获取见解上。

Varad Ram 是 Amazon Web Services 的高级解决方案架构师。他喜欢帮助客户采用云技术，对人工智能特别感兴趣。他认为深度学习将推动未来的技术发展。在业余时间，他喜欢与女儿和儿子一起去户外。

亚马逊AWS官方博客