大表单列更新疑问：为何Update与全表查询数据处理量一致？

我有一张规模较大的表，需要更新其中某一列的值，目前纠结于重建整张表还是使用UPDATE语句。我疑惑的是，为何执行全表查询（即select *）与该UPDATE语句处理的数据量均约为1TB。是否可在不读取整张表的情况下执行该UPDATE语句？

全表查询示例： #

select *
from `table` t WHERE
  date BETWEEN '2023-01-01'
    AND '2023-02-01'

=> 该查询处理1TB数据

UPDATE语句示例： #

UPDATE
  `table` t
SET
  col = u.value
FROM (
    SELECT
     token,
     value
  FROM
     `trans-gate-265215.sm_shopify.orders`) u
WHERE
  date BETWEEN '2023-01-01'
      AND '2023-02-01'
  AND
    t.key = u.token

=> 该更新语句同样处理1TB数据

原因分析 #

1. UPDATE的底层逻辑：从SQL语法看你应该用的是BigQuery这类数据仓库，这类系统中UPDATE本质是重写符合条件的分区或数据块。哪怕只更新单一列，数据库也需要读取目标行的完整数据，结合关联表的匹配数据计算出新行后再写入存储。如果date条件命中的分区总大小是1TB，那不管是SELECT *还是UPDATE，都会扫描这个分区的全部数据。
2. 关联查询的额外开销：你的UPDATE语句关联了orders表，数据库需要将目标表中符合date条件的所有行，与orders表做关联匹配，这个过程必须先读取目标表的1TB数据才能完成。

可行优化方案 #

1. 压缩关联与扫描范围 #

• 对orders表的token列建立索引（如果系统支持），减少关联时的查找耗时。
• 预先过滤orders表数据：只保留和目标表date范围匹配的记录，缩小关联数据集的大小，降低关联阶段的计算开销。

2. 用分区重建替代UPDATE #

如果目标分区内大部分行都需要更新，重建目标分区比UPDATE更高效：

• 先查询目标分区的所有数据，关联orders表计算出更新后的col值，将结果写入临时表。
• 替换原表的对应分区为临时表的分区。
  这种方式避免了UPDATE的行级重写开销，适合批量更新场景。

3. 确认过滤条件的有效性 #

检查date列是否为分区键或已建索引：如果没有，数据库可能会触发全表扫描，导致不必要的数据读取。查看执行计划，确认过滤条件是否正确命中分区或索引，调整后能确保只扫描目标范围的数据。

总结 #

无法完全避免读取目标分区的数据，但可以通过上述手段减少实际开销。如果更新行占分区比例高，重建分区更划算；如果仅少量行更新，配合索引的UPDATE会更高效。

内容的提问来源于stack exchange，提问作者Simon Breton