Google BigQuery简单查询与关联查询无结果/过慢问题排查求助

1. 无关联4表计数查询的问题原因及解决 #

你的第一个查询用逗号分隔多张表，这会触发笛卡尔积计算——四个表的所有记录会两两组合，总数据量是202685 × 175642 × 199392 × 174947，这是一个远超数据库处理能力的天文数字，因此永远无法返回结果。

正确的做法是通过子查询分别统计各表数据，再合并结果：

SELECT
  (SELECT count(_sequence_num) FROM `xxx.TAX_TRANSACTIONS`) AS txc,
  (SELECT count(_sequence_num) FROM xxx.ORDER) AS oc,
  (SELECT count(_sequence_num) FROM xxx.TRANSACTION) AS tc,
  (SELECT count(_sequence_num) FROM xxx.ORDER_LINES) AS olc;

如果需要分行列展示，也可以用UNION ALL：

SELECT 'TAX_TRANSACTIONS' AS table_name, count(_sequence_num) AS record_count FROM `xxx.TAX_TRANSACTIONS`
UNION ALL
SELECT 'ORDER' AS table_name, count(_sequence_num) AS record_count FROM xxx.ORDER
UNION ALL
SELECT 'TRANSACTION' AS table_name, count(_sequence_num) AS record_count FROM xxx.TRANSACTION
UNION ALL
SELECT 'ORDER_LINES' AS table_name, count(_sequence_num) AS record_count FROM xxx.ORDER_LINES;

2. 关联查询的问题排查与优化 #

你的关联查询无法返回结果，大概率是以下原因导致，对应优化方案如下：

原因1：WHERE子句的OR条件导致索引失效 #

多分支OR会让数据库无法有效利用索引，只能执行全表扫描，加上多表关联的开销，最终导致查询超时。

优化方案：将OR拆分为多个独立查询，用UNION ALL合并结果：

-- 第一个条件分支
SELECT ol.DEVICE_ID AS VIN,
       tx.TAX_LINES AS SKU,
       o.USER_ID AS ACCOUNT_DN,
       o.ORDER_NUMBER,
       cast(t.AMOUNT as FLOAT64)/100 AS TOTAL_AMOUNT ,
       t.TRANSACTION_STATUS,
       t.TRANSACTION_TYPE,
       t.TRANSACTION_TAG,
       t.CREATED_ON ,
       tx.TAX_CALCULATED,
       tx.TRANSACTION_STATUS AS TAX_TXN_STATUS,
       tx.ERROR_MESSAGE REMARKS,
       tx.TRANSACTION_ID AS TAX_TXN_ID,
       tx.TAXATION_TYPE AS TAX_TXN_TYPE,
       tx.TRANSACTION_DATE TAX_TXN_DATE
FROM xxx.TAX_TRANSACTIONS tx 
JOIN `xxx.ORDER` o ON o.ORDER_NUMBER = tx.ORDER_NUMBER 
JOIN xxx.TRANSACTION t ON o.ORDER_NUMBER = t.ORDER_NUMBER 
JOIN xxx.ORDER_LINES ol ON o.ID = ol.ORDER_ID
WHERE t.TRANSACTION_TYPE IN ('purchase') 
  AND t.TRANSACTION_STATUS = 'approved' 
  AND tx.TAXATION_TYPE = 'SalesInvoice'

UNION ALL

-- 第二个条件分支
SELECT ol.DEVICE_ID AS VIN,
       tx.TAX_LINES AS SKU,
       o.USER_ID AS ACCOUNT_DN,
       o.ORDER_NUMBER,
       cast(t.AMOUNT as FLOAT64)/100 AS TOTAL_AMOUNT ,
       t.TRANSACTION_STATUS,
       t.TRANSACTION_TYPE,
       t.TRANSACTION_TAG,
       t.CREATED_ON ,
       tx.TAX_CALCULATED,
       tx.TRANSACTION_STATUS AS TAX_TXN_STATUS,
       tx.ERROR_MESSAGE REMARKS,
       tx.TRANSACTION_ID AS TAX_TXN_ID,
       tx.TAXATION_TYPE AS TAX_TXN_TYPE,
       tx.TRANSACTION_DATE TAX_TXN_DATE
FROM xxx.TAX_TRANSACTIONS tx 
JOIN `xxx.ORDER` o ON o.ORDER_NUMBER = tx.ORDER_NUMBER 
JOIN xxx.TRANSACTION t ON o.ORDER_NUMBER = t.ORDER_NUMBER 
JOIN xxx.ORDER_LINES ol ON o.ID = ol.ORDER_ID
WHERE t.TRANSACTION_TYPE IN ('refund') 
  AND tx.TAXATION_TYPE = 'ReturnInvoice'

UNION ALL

-- 第三个条件分支
SELECT ol.DEVICE_ID AS VIN,
       tx.TAX_LINES AS SKU,
       o.USER_ID AS ACCOUNT_DN,
       o.ORDER_NUMBER,
       cast(t.AMOUNT as FLOAT64)/100 AS TOTAL_AMOUNT ,
       t.TRANSACTION_STATUS,
       t.TRANSACTION_TYPE,
       t.TRANSACTION_TAG,
       t.CREATED_ON ,
       tx.TAX_CALCULATED,
       tx.TRANSACTION_STATUS AS TAX_TXN_STATUS,
       tx.ERROR_MESSAGE REMARKS,
       tx.TRANSACTION_ID AS TAX_TXN_ID,
       tx.TAXATION_TYPE AS TAX_TXN_TYPE,
       tx.TRANSACTION_DATE TAX_TXN_DATE
FROM xxx.TAX_TRANSACTIONS tx 
JOIN `xxx.ORDER` o ON o.ORDER_NUMBER = tx.ORDER_NUMBER 
JOIN xxx.TRANSACTION t ON o.ORDER_NUMBER = t.ORDER_NUMBER 
JOIN xxx.ORDER_LINES ol ON o.ID = ol.ORDER_ID
WHERE tx.TRANSACTION_STATUS IN ('Error')

ORDER BY CREATED_ON DESC;

UNION ALL比OR更高效，因为每个分支可以单独利用索引，避免全表扫描。

原因2：关联键缺少索引 #

多表JOIN的关联键如果没有索引，数据库会执行低效的嵌套循环或哈希连接，开销极大。检查以下字段是否存在索引：

• xxx.TAX_TRANSACTIONS.ORDER_NUMBER
• xxx.ORDER.ORDER_NUMBER、xxx.ORDER.ID
• xxx.TRANSACTION.ORDER_NUMBER
• xxx.ORDER_LINES.ORDER_ID

如果没有，添加对应的B-tree索引：

CREATE INDEX idx_tax_transactions_order_number ON `xxx.TAX_TRANSACTIONS`(ORDER_NUMBER);
CREATE INDEX idx_order_order_number ON xxx.ORDER(ORDER_NUMBER);
CREATE INDEX idx_order_id ON xxx.ORDER(ID);
CREATE INDEX idx_transaction_order_number ON xxx.TRANSACTION(ORDER_NUMBER);
CREATE INDEX idx_order_lines_order_id ON xxx.ORDER_LINES(ORDER_ID);

原因3：ORDER BY字段缺少索引 #

ORDER BY CREATED_ON DESC如果没有索引，数据库需要先加载所有关联结果到内存排序，数据量大时会触发磁盘排序，速度极慢。可以在xxx.TRANSACTION.CREATED_ON上添加索引，或者结合WHERE条件创建复合索引：

-- 针对第一个条件分支的复合索引
CREATE INDEX idx_transaction_type_status_created ON xxx.TRANSACTION(TRANSACTION_TYPE, TRANSACTION_STATUS, CREATED_ON DESC);

这样数据库可以直接按顺序读取数据，避免排序开销。

原因4：数据过滤顺序不合理 #

尽量先过滤数据再关联，减少JOIN的中间数据量。比如在子查询中先过滤符合条件的记录：

FROM (SELECT * FROM xxx.TAX_TRANSACTIONS WHERE tx.TAXATION_TYPE IN ('SalesInvoice', 'ReturnInvoice') OR tx.TRANSACTION_STATUS = 'Error') tx
JOIN `xxx.ORDER` o ON o.ORDER_NUMBER = tx.ORDER_NUMBER 
JOIN ...

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