Strategie optymalizacji zapytań

Wprowadzenie do BigQuery

Matt Forrest

Field CTO

Trzy zasady ogólne

Istnieją trzy główne zasady optymalizacji:

Zmniejszenie ilości przetwarzanych danych
Optymalizacja operacji zapytania
Zmniejszenie rozmiaru wyników zapytania

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

Zmniejszanie ilości danych

Należy unikać SELECT * i wybierać tylko potrzebne kolumny
Należy wcześnie i często filtrować dane w CTE
Należy wcześnie i często filtrować dane za pomocą klauzuli WHERE

Optymalizacja złączeń

Należy redukować dane za pomocą CTE.
Należy łączyć tabele przy użyciu typu 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)

Optymalizacja klauzuli WHERE

W BigQuery należy używać
- BOOL
- INT
- FLOAT
- DATE
Typy danych z WHERE, STRING lub BYTE są nieoptymalne.

Nieoptymalnie

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

Optymalnie

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

Optymalizacja ORDER BY

ORDER BY powinno zawsze znajdować się na końcu zapytania
Wyjątkiem jest użycie ORDER BY w klauzuli okna

ORDER BY bez optymalizacji

Nieoptymalnie

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 z optymalizacją

Optymalnie

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

Jeśli wystarczy sprawdzić, czy rekord istnieje w tabeli, należy użyć EXISTS
Należy unikać COUNT w tym przypadku

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'
);

Inne metody optymalizacji

Należy używać przybliżonych funkcji agregujących, takich jak APPROX_TOP_SUM lub APPROX_COUNT_DISTINCT.
Wiele tabel BigQuery jest partycjonowanych według daty — warto uwzględniać daty w klauzuli WHERE.

Czas na ćwiczenia!

Wprowadzenie do BigQuery