查询优化策略

BigQuery 入门

Matt Forrest

Field CTO

三条经验法则

三条主要优化准则：

降低需要被处理的数据量
优化查询操作
减少查询输出大小

¹ https://cloud.google.com/bigquery/docs/best-practices-performance-compute#use-bi-engine

减少数据量

避免使用 SELECT *，只选取所需列
在 CTE 中尽早且多次过滤数据
用 WHERE 子句尽早且多次过滤数据

优化连接

使用 CTE 降低数据量。
用 INT64 类型进行连接。

WITH filter_my_data AS (SELECT 
-- Filter data with 
-- WHERE in the CTE first
)
SELECT 
-- This query will run 
-- faster with less data
JOIN a USING (user_id)

优化 WHERE 子句

在 BigQuery 中，使用
- BOOL
- INT
- FLOAT
- DATE
在 WHERE 中使用 STRING 或 BYTE 的数据类型并非最优。

并非最优

SELECT user_id, date_ordered
FROM dataset.table
WHERE product = 'shoes'

最优

SELECT user_id, date_ordered
FROM dataset.table
WHERE product_id = 1234

ORDER BY 优化

ORDER BY 应始终放在查询的最外层（末尾）
仅在窗口子句内使用 ORDER BY 时可例外

未优化的 ORDER BY

非最优

WITH order_total AS (SELECT
user_id,           
sum(product_price) as order_sum
FROM orders
GROUP BY user_id   
-- Order by is not at the end of the query
ORDER BY last_purchase_date
)
SELECT order_total.order_sum, 
users.user_name
FROM dataset.users users
JOIN order_total USING (user_id);

已优化的 ORDER BY

最优

WITH order_total AS (SELECT
user_id,    
last_purchase_date
sum(product_price) as order_sum
GROUP BY user_id         
)
SELECT order_total.order_sum, 
users.user_name
FROM dataset.users users
JOIN a USING (user_id)
-- Order by should always be at the end
ORDER BY orders_total.last_purchase_date;

使用 EXISTS vs. COUNT

只需判断表中是否存在记录时，用 EXISTS
避免用 COUNT 处理该场景

SELECT EXISTS (

  -- Write the main query as a subquery within the exists call
  SELECT
    user_id
  FROM
    dataset.table
  WHERE
    product_category = 'home_goods'
    AND status = 'Closed Account'
);

其他优化方法

使用近似聚合函数，如 APPROX_TOP_SUM 或 APPROX_COUNT_DISTINCT。
许多 BigQuery 表按日期分区——在 WHERE 子句中包含日期。

让我们来练习！

BigQuery 入门