避免 SQL 反模式

避免 SQL 反模式

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

自联接

最佳做法：避免使用自联接，请改用窗口函数。

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

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

数据倾斜

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

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

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

如果某个槽的资源不足以满足需求，则会出现 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 中的 UPDATE 和 DELETE 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