优化 CloudTrail Lake 查询

本页提供了有关如何优化 CloudTrail Lake 查询以提高性能和可靠性的指导。它涵盖特定优化技术以及常见查询失败的解决方法。

优化查询的建议

请按照本节中的建议优化您的查询。

优化聚合

在 GROUP BY 子句中排除冗余列可以提高性能，因为列越少，所需的内存就越少。例如，在以下查询中，我们可以在冗余列上使用 arbitrary 函数（如 eventType）来提高性能。eventType 上的 arbitrary 函数用于从组中随机选择字段值，因为该值是相同的，不需要包含在 GROUP BY 子句中。

SELECT eventName, eventSource, arbitrary(eventType), count(*) 
FROM $EDS_ID 
GROUP BY eventName, eventSource

可以通过将 GROUP BY 中的字段列表按其唯一值计数（基数）降序进行排序来提高 GROUP BY 函数的性能。例如，在获取每个 AWS 区域某种类型的事件数量时，可以通过在 GROUP BY 函数中使用 eventName、awsRegion 顺序（而不是 awsRegion、eventName）来提高性能，因为 eventName 的唯一值比 awsRegion 多。

SELECT eventName, awsRegion, count(*) 
FROM $EDS_ID 
GROUP BY eventName, awsRegion

使用近似技术

每当不需要精确值来统计不同值时，请使用近似聚合函数来查找最频繁值。例如，approx_distinct 使用的内存更少，运行速度比 COUNT(DISTINCT fieldName) 操作快。

限制查询结果

如果查询只需要示例响应，请使用 LIMIT 条件将结果限制为少量行。否则，查询将返回大量结果，并且查询执行时间会更长。

将 LIMIT 与 ORDER BY 结合使用可更快地提供前 N 条或后 N 条记录的结果，因为它减少了所需的内存量和排序所需的时间。

SELECT * FROM $EDS_ID
ORDER BY eventTime 
LIMIT 100;

优化 LIKE 查询

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

通常，您可以通过锚定要查找的子字符串来优化搜索。例如，如果您要查找某个前缀，则最好将“substr%”（而不是 %substr%）和 LIKE 运算符结合使用，并将“^substr”与 regexp_like 函数结合使用。

使用 UNION ALL 代替 UNION

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

仅包含必需列

如果您不需要某一列，请不要将其包含在查询中。查询需要处理的数据越少，运行速度就越快。如果您的查询在最外层查询中执行 SELECT *，则应将 * 更改为包含所需列的列表。

ORDER BY 子句按排序顺序返回查询结果。当对大量数据进行排序时，如果所需的内存不可用，则会将中间排序结果写入磁盘，这可能会减慢查询执行速度。如果您不严格要求对结果进行排序，请避免添加 ORDER BY 子句。此外，如果不是严格必需的，请避免将 ORDER BY 添加到内部查询中。

缩小窗口函数范围

窗口函数将它们操作的所有记录保存在内存中，以便计算其结果。当窗口非常大时，窗口函数可能会耗尽内存。要确保查询在可用内存限制内运行，请通过添加 PARTITION BY 子句来减小窗口函数操作的窗口的大小。

有时，使用窗口函数的查询可以在没有窗口函数的情况下重写。例如，您可以使用 max_by 或 min_by 等聚合函数，而不是使用 row_number 或 rank。

以下查询使用 max_by 来查找最近分配给每个 KMS 密钥的别名。

SELECT element_at(requestParameters, 'targetKeyId') as keyId, 
max_by(element_at(requestParameters, 'aliasName'), eventTime) as mostRecentAlias 
FROM $EDS_ID 
WHERE eventsource = 'kms.amazonaws.com' 
AND eventName in ('CreateAlias', 'UpdateAlias') 
AND eventTime > DATE_ADD('week', -1, CURRENT_TIMESTAMP) 
GROUP BY element_at(requestParameters, 'targetKeyId')

在这种情况下，max_by 函数会返回记录的别名以及组内的最新事件时间。与使用窗口函数的等效查询相比，此查询运行速度更快，占用的内存也更少。

查询失败的解决方法

本节提供了常见查询失败的解决方法。

由于响应太大，查询失败

如果响应太大，导致出现消息 Query response is too large，则查询可能会失败。如果发生这种情况，您可以缩小聚合范围。

array_agg 等聚合函数可能导致查询响应中至少有一行非常大，从而导致查询失败。例如，由于所选 CloudTrail 事件中存在重复的事件名称，因此使用 array_agg(eventName)（而不是 array_agg(DISTINCT eventName)）会大大增加响应大小。

由于资源耗尽，查询失败

如果在执行联接、聚合和窗口函数等内存密集型操作期间，可用内存不足，则中间结果会溢出到磁盘，但是溢出会减慢查询执行速度，可能不足以防止查询失败并出现 Query exhausted resources at this scale factor。重试查询即可解决此问题。

如果即使在优化查询后仍存在上述错误，则可以使用事件的 eventTime 缩小查询范围，并按照原始查询时间范围的较小间隔多次执行查询。