避免 SQL 反模式

以下最佳做法提供了相关指导,说明如何避免在 BigQuery 中使用会影响性能的查询反模式。

自联接

最佳做法:避免使用自联接,请改用窗口函数

通常情况下,自联接用于计算依赖于行的关系。如果使用自联接,则可能导致输出行数翻倍。输出数据的增加可能会导致性能变差。

如果不使用自联接,而使用窗口(解析)函数,则可以减少查询生成的额外字节数。

数据倾斜

最佳做法:如果您的查询处理的键严重偏向一些值,请尽早过滤您的数据。

分区倾斜(有时称为数据倾斜)是指数据分入大小极不均匀的分区这种情况。这会导致槽之间发送的数据量不平衡。您不能在槽之间共享分区,因此如果一个分区特别大,就可能减慢处理该分区的槽的速度,甚至可能导致槽的崩溃。

如果分区键有一个值出现的频率高于其他任何值,则分区就会变大。例如,按 user_id 字段分组,其中 guestNULL 的条目有很多。

如果某个槽的资源不足以满足需求,则会出现 resources exceeded 错误。达到槽的 Shuffle 上限(压缩后所占内存为 2TB)也会导致 Shuffle 写入磁盘并进一步影响性能。采用固定价格的客户可以增加分配槽数。

如果您查看查询说明计划,发现计算时间的平均值与最大值之间存在明显差异,则说明可能存在数据倾斜。

为了避免数据倾斜引起的性能问题,请采取以下措施:

  • 使用近似聚合函数(如 APPROX_TOP_COUNT)确定是否存在数据倾斜。
  • 尽早过滤您的数据。

不平衡联接

在您使用 JOIN 子句时,也可能会出现数据倾斜。因为 BigQuery 对联接两侧的数据执行 Shuffle 操作,所以具有相同联接键的所有数据都会进入同一分片。这种 Shuffle 操作可能会导致槽过载。

为了避免与不平衡联接相关的性能问题,请采取以下措施:

  • 预先过滤表中具有不平衡键的行。
  • 如有可能,将查询拆分为两个查询。

交叉联接(笛卡尔积)

最佳做法:避免使用产生的输出多于输入的联接。如果需要 CROSS JOIN,请预先聚合您的数据。

交叉联接是指第一个表中的每行与第二个表中的每行相联接(两侧都存在非唯一键)的查询。最差情况输出是左表中的行数乘以右表中的行数。在极端情况下,查询可能无法完成。

如果查询作业完成,则查询计划说明会显示输出行与输入行的对比情况。您可以修改查询以显示 JOIN 子句两侧的行数(按联接键分组),从而确认笛卡尔积

为了避免与产生的输出多于输入的联接相关的性能问题,请采取以下措施:

  • 使用 GROUP BY 子句预先聚合数据。
  • 使用窗口函数。窗口函数通常比使用交叉联接更高效。要了解详情,请参阅解析函数

用于更新或插入单行的 DML 语句

最佳做法:避免使用特定于点的 DML 语句(一次更新或插入 1 行)。批量处理您的更新和插入。

使用特定于点的 DML 语句是尝试将 BigQuery 视为在线事务处理 (OLTP) 系统。BigQuery 使用表扫描而不是点查询,侧重于在线分析处理 (OLAP)。如果您需要类似 OLTP 的行为(单行更新或插入),请考虑使用旨在支持 OLTP 用例的数据库,例如 Cloud SQL

BigQuery DML 语句适用于批量更新。BigQuery 中的 UPDATEDELETE DML 语句用于定期重写数据,而不是单行修改。请谨慎使用 INSERT DML 语句。插入操作所占用的修改配额与加载作业相同。如果您的使用场景涉及频繁进行单行插入,不妨考虑改为对您的数据进行流式处理

如果批量处理 UPDATE 语句产生非常长的包含很多元组的查询,就可能接近 256 KB 的查询长度上限。为了避免发生查询长度上限问题,请考虑是否可以根据逻辑标准(而不是一系列直接元组替换)来处理您的更新。

例如,您可以将替换记录集加载到另一个表中,然后编写 DML 语句在未更新的列匹配时更新原始表中的所有值。例如,如果原始数据位于表 t 中,而更新暂存在表 u 中,则查询可能如下所示:

UPDATE
  dataset.t t
SET
  my_column = u.my_column
FROM
  dataset.u u
WHERE
  t.my_key = u.my_key
此页内容是否有用?请给出您的反馈和评价:

发送以下问题的反馈:

此网页
需要帮助?请访问我们的支持页面