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

Введение в BigQuery

Matt Forrest

Field CTO

Три основных правила

Существуют три основных правила оптимизации:

Уменьшите объём обрабатываемых данных
Оптимизируйте операции запроса
Сократите размер вывода запроса

¹ 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 vs. 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