查詢最佳化策略

BigQuery 入門

Matt Forrest

Field CTO

三大原則

有三個主要原則：

減少需要被處理的資料量
最佳化查詢運算
減少查詢輸出大小

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

減少資料量

避免使用 SELECT *，只選所需欄位
在 CTE 內儘早且多次過濾資料量
以 WHERE 子句儘早且多次過濾資料

最佳化 JOIN

確保用 CTE 先縮小所需資料。
用 INT64 型別進行 JOIN。

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 與 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 資料表按日期分割（partition），請在 WHERE 子句加入日期條件。

一起來練習吧！

BigQuery 入門