动手实践：查询分析

为数据湖编写高效SQL查询，需要转变传统数据库优化的思维方式。关系型数据库通过其存储引擎透明地处理索引和物理数据获取。对于解耦的数据湖架构，查询必须明确设计，以与底层文件组织保持一致，从而减少网络I/O和解码开销。

本实践部分着重于使用执行计划诊断查询性能问题。我们将考察一个常见情况：一个在大型数据集上运行的缓慢聚合查询。你将学习如何生成执行计划，识别全表扫描，并重写查询以应用分区剪枝。

场景

设想一个网络服务器访问日志数据集，存储在对象存储（S3或Azure Blob Storage）中。数据以Apache Parquet格式存储，并遵循基于事件日期的标准分区方案。

• 表名： access_logs
• 格式： Parquet
• 分区策略： year / month / day
• 总大小： 500 TB
• 文件数量： 200,000个对象

一名数据分析师提交以下查询，以计算2023年10月某个特定URL的总点击次数：

SELECT 
    url, 
    COUNT(*) as hit_count
FROM access_logs
WHERE event_timestamp >= TIMESTAMP '2023-10-01 00:00:00'
  AND event_timestamp < TIMESTAMP '2023-11-01 00:00:00'
GROUP BY url;

尽管只筛选了一个月的数据，这个查询的执行时间超过45分钟。为了弄清原因，我们需要查看查询引擎如何将此SQL转化为实际操作。

生成与读取执行计划

大多数分布式查询引擎，包括Trino（前身为PrestoSQL）和Apache Spark SQL，都提供EXPLAIN命令。此命令返回引擎计划执行的逻辑或物理计划。它不实际运行查询，而是概述所涉及的步骤。

要查看计划，请在语句前加上关键词：

EXPLAIN SELECT url, COUNT(*) FROM access_logs ...

输出通常是树状结构，表示数据从底部（存储）到顶部（结果）的流动。以下是上述慢查询计划的简化表示：

执行流从下往上。红色节点表示开销大的操作，即引擎在过滤数据之前会扫描所有数据。

在上图中，底部最核心的部分是TableScan。

1. 表扫描： 引擎列出access_logs表中的所有文件。
2. 过滤： 引擎打开每个Parquet文件，读取event_timestamp列，并检查值是否在10月份范围内。
3. 聚合： 匹配项被传递上去进行计数。

性能问题存在于模式与查询之间的关联。该表按year、month和day分区，但查询在event_timestamp上进行过滤。尽管这些列在语义上相关，查询引擎不会自动知道event_timestamp与目录结构完全对应。因此，它默认执行全表扫描，读取500 TB的元数据和文件尾部信息以查找相关行。

实现分区剪枝

为了优化这一点，我们必须明确触发分区剪枝。这发生在查询引擎使用目录结构（例如year=2023/month=10）时，完全跳过文件，而不是在读取行后再进行过滤。

我们重写查询以包含对分区列的过滤：

SELECT 
    url, 
    COUNT(*) as hit_count
FROM access_logs
WHERE year = '2023'
  AND month = '10'
  AND event_timestamp >= TIMESTAMP '2023-10-01 00:00:00'
GROUP BY url;

当我们对这个修改后的查询运行EXPLAIN时，TableScan节点会发生显著变化。引擎会查看元数据目录，识别出只有满足year=2023和month=10的目录是必需的，并忽略其余部分。

如果数据集跨越10年，筛选到一个月份可以将输入数据量减少大约120倍。

$$\text{优化后的输入大小} \approx \frac{\text{总大小}}{120}$$

量化影响

扫描过滤和剪枝过滤之间的差异，通常是查询失败和一分钟内响应之间的区别。以下图表比较了这两种方法的扫描数据量和执行时间。

比较全扫描与剪枝扫描的资源消耗。请注意Y轴上的对数刻度；减少是指数级的。

分析物理执行计划统计信息

现代引擎通常提供EXPLAIN ANALYZE（Trino）或提供显示实际运行时统计信息的UI（Spark History Server）。在检查这些详细计划时，关注三个特定指标：

1. 输入行数与输出行数： 查看Filter操作符。如果输入是10亿行而输出是1000行，则你的过滤器选择性很高。然而，如果此过滤发生在扫描之后，你已经为10亿行数据支付了I/O开销。目标是通过分区将这种减少下推到Scan层面。

2. 分片或任务： 一个“分片”代表一个工作单元，通常是分配给工作节点的一个文件或一个文件块。

   • 高分片数量，低数据量： 表明存在小文件问题。由于打开连接和读取头部的开销，读取10,000个每个1MB的文件比读取10个每个1GB的文件要慢。
   • 低分片数量，慢执行： 表明存在数据倾斜。某个特定文件或分区可能比其他文件或分区大得多，导致一个工作节点处理困难，而其他节点闲置。

3. 洗牌（数据交换）开销： 在GROUP BY阶段，数据在节点之间移动（洗牌），以便所有具有相同url的记录都到达同一个工作节点。如果执行计划显示大量数据通过Exchange节点移动，请考虑在聚合步骤之前更积极地过滤数据或增加集群的并行度。

向量化与文件格式

一旦分区剪枝限制了查询范围，文件格式就决定了扫描速度。由于我们使用Parquet，引擎会运用向量化读取。引擎不是一次反序列化一个对象（面向行），而是将一列整数或字符串加载到内存块中。

在你的执行计划中验证向量化是否已启用。在Spark中，在Scan步骤中查找Batched: true。在Trino中，这是Parquet和ORC的默认行为。

如果你的查询只选择特定列（例如SELECT url），列式格式允许引擎只从存储中获取url数据块。event_timestamp和其他未使用的列永远不会从磁盘读取。这就是所谓的列投影。

分区剪枝（根据目录跳过行）和列投影（根据文件结构跳过列）的结合，确保查询处理回答问题所需的绝对最少量数据。