BigQuery Materialized View查询性能咨询：耗时20-23秒是否合理？

我在BigQuery中有3个包含样本数据的表，分别为agents_data、orders_data和customers_data，总数据量约1.8GB（630万行）。已通过以下SQL语句JOIN这3张表创建Materialized View：

SELECT
    t2.food_item AS food_item,
    t2.amount AS amount,
    t1.address AS address,
    t3.agent_name AS agent_name ,
    t1.gender AS customer_gender,
    t1.customer_id AS customer_id,
    t1.customer_name AS customer_name,
    t1.date AS date,
    t3.phone_number AS phone_number,
    t1.zip_code AS zipCode,
    t3.rating AS rating
FROM
    project_id.my_dataset.customers_data AS t1
JOIN
project_id.my_dataset.orders_data AS t2 
ON
    t1.customer_id = t2.customer_id
JOIN 
project_id.my_dataset.agents_data AS t3 
ON
    t2.order_id = t3.order_id

当前执行以下查询该Materialized View的语句耗时20-23秒，处理814MB数据：

SELECT
    t0.food_item,
    t0.amount,
    t0.address,
    t0.agent_name,
    t0.customer_gender,
    t0.customer_id,
    t0.customer_name,
    t0.date,
    t0.phone_number,
    t0.zipCode,
    t0.rating
FROM
    `project-Id.my_dataset.materialized_table` AS t0
GROUP BY
    t0.address,
    t0.agent_name,
    t0.amount,
    t0.customer_gender,
    t0.customer_id,
    t0.customer_name,
    t0.food_item,
    t0.phone_number,
    t0.date,
    t0.rating,
    t0.zipCode
ORDER BY
    t0.food_item DESC
LIMIT
  2000001

我尝试对该Materialized View按日期进行按月Partitioning，并以ORDER BY字段food_item进行Clustering，但未发现查询速度提升。请问此耗时是否符合预期？能否进一步优化以缩短查询时间？

1. 耗时合理性判断 #

20-23秒处理814MB数据的耗时不符合最优预期，但属于BigQuery无针对性优化下的正常范围。630万行量级的查询通常可压缩到10秒以内，当前耗时偏高主要是查询逻辑与物化视图优化策略不匹配导致。

2. 分区与聚类失效原因 #

• 分区未生效：查询未对date字段做过滤（WHERE条件），按月分区的核心优势是跳过无关分区数据，全表扫描场景下分区无法带来性能提升。
• 聚类未生效：聚类的作用是让相同值的记录物理相邻，加速过滤、分组或排序。但你的查询是全字段分组（本质是去重）后排序，且无过滤条件，BigQuery需扫描全表数据，聚类无法减少扫描量，排序阶段的收益也被全量数据抵消。

3. 具体优化措施 #

（1）简化查询逻辑 #

你的GROUP BY包含所有SELECT字段，等价于SELECT DISTINCT *，可直接替换为：

SELECT DISTINCT
    t0.food_item,
    t0.amount,
    t0.address,
    t0.agent_name,
    t0.customer_gender,
    t0.customer_id,
    t0.customer_name,
    t0.date,
    t0.phone_number,
    t0.zipCode,
    t0.rating
FROM
    `project-Id.my_dataset.materialized_table` AS t0
ORDER BY
    t0.food_item DESC
LIMIT
  2000001

BigQuery对SELECT DISTINCT的优化效率远高于全字段GROUP BY，能减少执行计划中的不必要步骤。

（2）调整物化视图优化策略 #

• 若查询经常需要按food_item排序+去重，可在物化视图创建时预先完成去重：

  CREATE MATERIALIZED VIEW `project_id.my_dataset.materialized_table`
  CLUSTER BY food_item
  AS
  SELECT DISTINCT
      t2.food_item AS food_item,
      t2.amount AS amount,
      t1.address AS address,
      t3.agent_name AS agent_name ,
      t1.gender AS customer_gender,
      t1.customer_id AS customer_id,
      t1.customer_name AS customer_name,
      t1.date AS date,
      t3.phone_number AS phone_number,
      t1.zip_code AS zipCode,
      t3.rating AS rating
  FROM
      project_id.my_dataset.customers_data AS t1
  JOIN
  project_id.my_dataset.orders_data AS t2 
  ON
      t1.customer_id = t2.customer_id
  JOIN 
  project_id.my_dataset.agents_data AS t3 
  ON
      t2.order_id = t3.order_id
  

  预先去重后，查询时直接读取处理后的数据集，大幅减少计算量。
• 若后续查询不会加入日期过滤，可去掉按月分区，避免额外的存储和维护开销；若有日期过滤需求则保留分区。

（3）利用聚类优化排序+LIMIT #

如果物化视图已按food_item聚类，BigQuery可直接从聚类后的块中读取Top N数据，无需全表排序。但需确保查询中无破坏聚类优势的操作，预先在物化视图中去重是关键前提。

（4）检查数据倾斜 #

查看food_item字段的分布，若存在极少数值占比极高的情况，会导致排序阶段数据倾斜，拖慢查询。可通过以下语句检查：

SELECT food_item, COUNT(*) AS cnt
FROM `project-Id.my_dataset.materialized_table`
GROUP BY food_item
ORDER BY cnt DESC
LIMIT 10

若存在数据倾斜，可考虑拆分查询或调整聚类字段组合（如food_item, date）。

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