BigQuery中Delete语句执行数据量过大的原因及优化咨询

可能的原因 #

• 数据类型不匹配导致索引失效：如果你的date字段是DATE/DATETIME类型，但SQL里用FORMAT_DATE把日期转成字符串做比较，数据库没法使用date字段的索引，只能全表扫描整个270GB的数据，而非仅处理目标日期区间的内容。而Insert语句是直接插入对应日期的数据，自然处理量小很多。
• 目标区间存在大量冗余/重复数据：同日期的Insert数据量小，但Delete时该区间可能积累了大量重复记录（比如Insert环节没做去重校验），导致要删除的行数远大于当初插入的行数，对应数据量也就随之增大。
• date字段无索引：如果date字段没建索引，不管查询条件怎么写，Delete都会触发全表扫描，处理整个表的数据量，自然远超Insert的处理规模。
• 分区表配置不合理：如果表是分区表，但分区键不是date，或者分区规则和你的查询条件不匹配，Delete会跨多个分区扫描，额外处理大量无关数据。

优化方案 #

• 修复条件的类型匹配问题：直接用日期类型做比较，不要转成字符串，修改后的SQL如下：

DELETE FROM `TABLE`
 WHERE date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 5 DAY)
   AND DATE_SUB(CURRENT_DATE(), INTERVAL 2 DAY)

这样如果date有索引，就能直接走索引定位目标数据，不用扫全表，处理量会大幅降低。

• 给date字段建索引：如果还没建索引，执行以下语句创建：

CREATE INDEX idx_table_date ON `TABLE`(date);

后续Delete和相关查询都能快速定位目标区间数据。

• 清理冗余数据并避免重复插入：先查询区间内的重复记录：

SELECT COUNT(*), unique_key_column 
FROM `TABLE` 
WHERE date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 5 DAY) AND DATE_SUB(CURRENT_DATE(), INTERVAL 2 DAY)
GROUP BY unique_key_column 
HAVING COUNT(*) > 1;

清理重复数据后，给唯一键字段添加约束，防止后续Insert产生重复数据。

• 分批删除：如果目标数据量确实较大，不要一次性删完，分批次执行，比如每次删1000条，循环执行直到删完：

DELETE FROM `TABLE`
 WHERE date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 5 DAY)
   AND DATE_SUB(CURRENT_DATE(), INTERVAL 2 DAY)
LIMIT 1000;

这样能减少锁表时间，避免占用过多系统资源。

• 优化分区表（如果是分区表）：把date设为分区键，按日/周/月分区，这样Delete只会扫描对应日期的分区，不会处理其他分区的数据。

内容的提问来源于stack exchange，提问作者T.B.