クエリ最適化の戦略

BigQuery入門

Matt Forrest

Field CTO

3つの原則

最適化の基本は3つ:

処理すべきデータ量を減らす
クエリ処理を最適化する
クエリの出力サイズを減らす

¹ 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 と 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入門