Strategie optimalizace dotazů

Introduction to BigQuery

Matt Forrest

Field CTO

Tři základní pravidla

Existují tři hlavní pravidla optimalizace:

Snížení množství zpracovávaných dat
Optimalizace operací dotazu
Snížení velikosti výstupu dotazu

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

Omezení množství dat

Vyhněte se SELECT * a vybírejte pouze potřebné sloupce
Filtrujte data v CTE co nejdříve a opakovaně
Filtrujte data pomocí klauzule WHERE co nejdříve a opakovaně

Optimalizace spojení

Omezte data pomocí CTE před jejich zpracováním.
Pro spojení použijte datový typ 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)

Optimalizace klauzule WHERE

V BigQuery používejte
- BOOL
- INT
- FLOAT
- DATE
Datové typy s WHERE, STRING nebo BYTE jsou neoptimální.

Neoptimální

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

Optimální

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

Optimalizace ORDER BY

ORDER BY by měl být vždy na konci dotazu
Výjimkou je použití ORDER BY v rámci klauzule okna

ORDER BY bez optimalizace

Neoptimální

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 s optimalizací

Optimální

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

Pokud potřebujeme zjistit, zda záznam v tabulce existuje, použijte EXISTS
Vyhněte se použití COUNT pro tento případ

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

Další metody optimalizace

Používejte přibližné agregační funkce jako APPROX_TOP_SUM nebo APPROX_COUNT_DISTINCT.
Mnoho tabulek BigQuery je rozděleno podle data – zahrňte datum do klauzule WHERE.

Pojďme si to procvičit!

Introduction to BigQuery