BigQuery三表关联查询性能过慢问题及优化咨询

问题背景 #

我们有三张表（每张表约300k-500k条记录），希望通过关联创建视图，原始查询语句如下：

SELECT ROW_NUMBER() OVER(ORDER BY CREATED_ON DESC) AS RN, t.* from (SELECT
o.USER_ID AS ACCOUNT_DN,
t.TRANSACTION_STATUS,
t.CREATED_ON ,
tx.TAX_CALCULATED,
tx.TRANSACTION_STATUS AS TAX_TXN_STATUS
FROM abc.xyz.TAX_TRANSACTIONS tx join
`abc.xyz.ORDER` o on o.ORDER_NUMBER = tx.ORDER_NUMBER join
abc.xyz.TRANSACTION t on o.ORDER_NUMBER = t.ORDER_NUMBER
WHERE
t.TRANSACTION_TYPE != 'auth' AND
((t.TRANSACTION_TYPE IN ("purchase") AND t.TRANSACTION_STATUS ="approved" AND tx.TAXATION_TYPE = "SalesInvoice") or
(t.TRANSACTION_TYPE IN ("refund") AND tx.TAXATION_TYPE = "ReturnInvoice") or
(tx.TRANSACTION_STATUS IN ("Error")))) as t
ORDER BY CREATED_ON DESC

该查询耗时超过2小时，执行计划相关图表：

• General：展示查询整体执行概况的图表
• S00：对应执行阶段00的详细执行图表
• S01：对应执行阶段01的详细执行图表

现有研究与疑问 #

我们尝试了以下方向，但仍有疑问：

1. 无法使用分区表：BigQuery仅支持日期/时间和整数范围分区，而关联基于字符串列ORDER_NUMBER；
2. 无法使用搜索索引：官方说明小于10GB的表创建搜索索引不会被填充，我们的表不符合条件；
3. 集群表可行性：计划基于JOIN子句中的ORDER_NUMBER创建集群表，但不确定是否需要包含WHERE子句中的列（如TRANSACTION_TYPE、TRANSACTION_STATUS等）；
4. 是否存在思路错误？或者有更高效的优化方案？

补充测试结果 #

• 仅关联TAX_TRANSACTIONS和ORDER两张表，查询仅需2-3秒：

SELECT
o.USER_ID AS ACCOUNT_DN,
tx.TAX_CALCULATED,
tx.TRANSACTION_STATUS AS TAX_TXN_STATUS
FROM abc.xyz.TAX_TRANSACTIONS tx
join `abc.xyz.ORDER` o on o.ORDER_NUMBER = tx.ORDER_NUMBER

• 仅关联ORDER和TRANSACTION两张表，查询仅需2-3秒：

SELECT
o.USER_ID AS ACCOUNT_DN,
t.TRANSACTION_STATUS,
t.CREATED_ON ,
FROM 
`abc.xyz.ORDER` o 
join abc.xyz.TRANSACTION t on o.ORDER_NUMBER = t.ORDER_NUMBER
WHERE
t.TRANSACTION_TYPE != 'auth' 

• 关联三张表（即使移除ROW_NUMBER()窗口函数），查询仍需数小时，且执行计划显示卡在关联步骤：

SELECT
o.USER_ID AS ACCOUNT_DN,
t.TRANSACTION_STATUS,
t.CREATED_ON ,
tx.TAX_CALCULATED,
tx.TRANSACTION_STATUS AS TAX_TXN_STATUS
FROM abc.xyz.TAX_TRANSACTIONS tx
join `abc.xyz.ORDER` o on o.ORDER_NUMBER = tx.ORDER_NUMBER
join abc.xyz.TRANSACTION t on o.ORDER_NUMBER = t.ORDER_NUMBER

WHERE
t.TRANSACTION_TYPE != 'auth' AND
((t.TRANSACTION_TYPE IN ("purchase") AND t.TRANSACTION_STATUS ="approved" AND tx.TAXATION_TYPE = "SalesInvoice") or
(t.TRANSACTION_TYPE IN ("refund") AND tx.TAXATION_TYPE = "ReturnInvoice") or
(tx.TRANSACTION_STATUS IN ("Error")))
ORDER BY CREATED_ON DESC

后续更新 #

1. 关联三张表后生成了8.9G的中间数据；
2. 查询执行1小时后出现错误：超出资源限制：查询使用了过多资源，无法在合理时间内完成。考虑优化查询或增加资源配额。

优化建议 #

1. 调整关联顺序与提前过滤 #

当前查询先关联所有表再过滤，建议先对大表做过滤，减少参与关联的数据量：

• 先对TRANSACTION表应用TRANSACTION_TYPE != 'auth'的过滤条件；
• 对TAX_TRANSACTIONS表提前过滤符合WHERE子句中tx.TAXATION_TYPE或tx.TRANSACTION_STATUS条件的数据，再进行关联。
  示例改写：

WITH filtered_t AS (
    SELECT ORDER_NUMBER, TRANSACTION_STATUS, CREATED_ON, TRANSACTION_TYPE
    FROM abc.xyz.TRANSACTION
    WHERE TRANSACTION_TYPE != 'auth'
),
filtered_tx AS (
    SELECT ORDER_NUMBER, TAX_CALCULATED, TRANSACTION_STATUS AS TAX_TXN_STATUS, TAXATION_TYPE
    FROM abc.xyz.TAX_TRANSACTIONS
    WHERE TAXATION_TYPE IN ("SalesInvoice", "ReturnInvoice") OR TRANSACTION_STATUS IN ("Error")
)
SELECT
    o.USER_ID AS ACCOUNT_DN,
    t.TRANSACTION_STATUS,
    t.CREATED_ON,
    tx.TAX_CALCULATED,
    tx.TAX_TXN_STATUS
FROM filtered_tx tx
JOIN `abc.xyz.ORDER` o ON o.ORDER_NUMBER = tx.ORDER_NUMBER
JOIN filtered_t t ON o.ORDER_NUMBER = t.ORDER_NUMBER
WHERE
    ((t.TRANSACTION_TYPE IN ("purchase") AND t.TRANSACTION_STATUS = "approved" AND tx.TAXATION_TYPE = "SalesInvoice") OR
     (t.TRANSACTION_TYPE IN ("refund") AND tx.TAXATION_TYPE = "ReturnInvoice") OR
     (tx.TAX_TXN_STATUS IN ("Error")))
ORDER BY t.CREATED_ON DESC

2. 集群表配置 #

针对集群表，建议优先按ORDER_NUMBER（关联键）进行集群，同时可以添加WHERE子句中频繁过滤的列（如TRANSACTION_TYPE、TAXATION_TYPE）作为次要集群列。这样关联时BigQuery可快速定位相同ORDER_NUMBER的数据块，减少扫描范围；过滤时也能利用集群列快速筛选数据。

3. 检查数据重复问题 #

三张表关联后数据量膨胀至8.9G，需检查是否存在ORDER_NUMBER对应多条TRANSACTION或TAX_TRANSACTIONS记录的情况，导致笛卡尔积。可先统计每个ORDER_NUMBER在两张表中的记录数：

-- 统计TRANSACTION表中每个ORDER_NUMBER的记录数
SELECT ORDER_NUMBER, COUNT(*) AS t_count
FROM abc.xyz.TRANSACTION
WHERE TRANSACTION_TYPE != 'auth'
GROUP BY ORDER_NUMBER
HAVING COUNT(*) > 1

-- 统计TAX_TRANSACTIONS表中每个ORDER_NUMBER的记录数
SELECT ORDER_NUMBER, COUNT(*) AS tx_count
FROM abc.xyz.TAX_TRANSACTIONS
GROUP BY ORDER_NUMBER
HAVING COUNT(*) > 1

如果存在大量一对多情况，需确认业务逻辑是否需要保留所有关联结果，或是否可通过聚合（如取最新记录）减少数据量。

4. 避免不必要的排序 #

如果视图不需要全局排序后的行号，可考虑移除ORDER BY CREATED_ON DESC和ROW_NUMBER()，或改为按ORDER_NUMBER局部排序，减少排序资源消耗。

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