Стратегії оптимізації запитів

Вступ до BigQuery

Matt Forrest

Field CTO

Три правила

Є три основні правила оптимізації:

Зменшуйте обсяг даних для обробки
Оптимізуйте операції запиту
Зменшуйте розмір вихідних даних запиту

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

Зменшення обсягу даних

Уникайте SELECT *, обирайте лише потрібні стовпці
Рано й часто фільтруйте дані у CTE
Рано й часто фільтруйте дані в WHERE

Оптимізація обʼєднань (JOIN)

Слідкуйте, щоб ми зменшували дані за допомогою 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