Оптимизация запросов — ClickHouse: от схемы до

Оптимизация запросов в ClickHouse

EXPLAIN, анализ планов выполнения, оптимизация JOIN, предикаты, типичные антипаттерны

#Результат урока

Вы построите воспроизводимый профиль запроса, определите главный ограничивающий ресурс и измените только один фактор. Оптимизация считается успешной по плану, объёму чтения, памяти и серии запусков, а не по одному удачному времени.

#EXPLAIN — анализ плана выполнения

#Базовый EXPLAIN

EXPLAIN SELECT * FROM events WHERE user_id = 123;

Результат:

Expression (Projection)
  Filter (WHERE)
    ReadFromMergeTree (events)

Этапы:

ReadFromMergeTree — чтение данных из таблицы
Filter — применение условий WHERE
Expression — вычисления и проекция колонок

#EXPLAIN с деталями

-- План с индексами
EXPLAIN indexes = 1 
SELECT * FROM events WHERE user_id = 123;

-- План с проекциями
EXPLAIN projections = 1 
SELECT user_id, count() FROM events GROUP BY user_id;

-- Полный план
EXPLAIN indexes = 1, projections = 1, json = 1
SELECT * FROM events WHERE user_id = 123;

#EXPLAIN PIPELINE

-- Визуализация конвейера выполнения
EXPLAIN PIPELINE 
SELECT * FROM events WHERE user_id = 123;

-- С графиком выполнения
EXPLAIN PIPELINE graph = 1
SELECT * FROM events WHERE user_id = 123;

EXPLAIN показывает план, но не заменяет фактические метрики. Присвойте запуску query_id, выполните SYSTEM FLUSH LOGS и прочитайте system.query_log. Сохраняйте как минимум read_rows, read_bytes, memory_usage и query_duration_ms.

Вывод:

ReadFromMergeTree
    ↓
Filter
    ↓
Expression
    ↓
Null (результат)

#Чтение плана выполнения

#Ключевые операции

Операция	Описание	На что обратить внимание
ReadFromMergeTree	Чтение из таблицы	Columns read, Parts read
Filter	Применение WHERE	Selectivity
Expression	Вычисления	Number of columns
Aggregating	GROUP BY/агрегаты	Memory usage
Join	JOIN операции	Type, Size
Sorting	ORDER BY	Memory, Disk
Limit	LIMIT	Rows before limit
Union	UNION ALL	Number of inputs

#Пример анализа

EXPLAIN 
SELECT 
    user_id,
    count() AS events,
    sum(value) AS total_value
FROM events
WHERE event_date = '2026-03-01'
  AND event_type IN ('click', 'view')
GROUP BY user_id
HAVING events > 10
ORDER BY total_value DESC
LIMIT 100;

План:

1. Limit (LIMIT 100)
2. Sorting (ORDER BY total_value DESC)
3. Filter (HAVING events > 10)
4. Aggregating (GROUP BY user_id)
5. Filter (WHERE event_date, event_type)
6. ReadFromMergeTree (events)

Оптимизации:

Filter применяется до Aggregating (push-down предикатов)
Limit применяется после Sorting
ReadFromMergeTree читает только нужные колонки

#Оптимизация WHERE-условий

#Push-down предикатов

ClickHouse применяет условия WHERE как можно раньше:

-- Оптимизировано: фильтр применяется при чтении
SELECT * FROM events
WHERE event_date = '2026-03-01'
  AND toHour(event_time) = 12;

-- Проблема: toHour() вычисляется для всех строк
-- Лучше:
SELECT * FROM events
WHERE event_date = '2026-03-01'
  AND event_time >= '2026-03-01 12:00:00'
  AND event_time < '2026-03-01 13:00:00';

#Селективность условий

Перестановка условий в тексте WHERE обычно мало что меняет: анализатор сам переставляет и проталкивает предикаты. Важнее соответствие фильтров ключу сортировки, объём прочитанных гранул и стоимость самого условия. Проверяйте это через EXPLAIN indexes = 1.

#Использование индексов

-- Использует индекс (event_date в начале ORDER BY)
SELECT * FROM events
WHERE event_date = '2026-03-01';

-- Может потребовать преобразования или монотонного анализа функции;
-- не делайте вывод без EXPLAIN indexes = 1
SELECT * FROM events
WHERE toYYYYMM(event_time) = 202603;

-- Использует индекс (диапазон)
SELECT * FROM events
WHERE event_time >= '2026-03-01' 
  AND event_time < '2026-04-01';

#Оптимизация JOIN

#Планировщик и алгоритмы JOIN

В 26.3 LTS анализатор умеет глобально переупорядочивать распространённые типы JOIN. Старое правило «вручную поставьте меньшую таблицу справа» больше не универсально. Оно остаётся полезной моделью памяти для hash join, но решение нужно сверять с планом.

-- Локальный JOIN без сетевой рассылки
SELECT e.*, u.country
FROM events_local e
JOIN users_local u ON e.user_id = u.id;

-- GLOBAL JOIN рассылает правую часть по узлам
SELECT e.*, u.country
FROM events_all e
GLOBAL JOIN users_all u ON e.user_id = u.id;

-- CROSS JOIN создаёт декартово произведение
SELECT * FROM events_all
CROSS JOIN dimensions;

#Оптимизация GLOBAL JOIN

1. Предварительная фильтрация:

-- Фильтрация перед JOIN уменьшает объём данных
SELECT e.*, u.country
FROM 
    (SELECT * FROM events_all WHERE event_date = '2026-03-01') e
    GLOBAL JOIN 
    (SELECT * FROM users_all WHERE active = 1) u 
    ON e.user_id = u.id;

2. Использование словарей:

-- Вместо JOIN
SELECT 
    user_id,
    dictGet('users_dict', 'country', user_id) AS country
FROM events_all;

#JOIN с USING

-- USING убирает дублирование одноимённого ключа в результате
SELECT * FROM events
JOIN users USING (user_id);

-- Вместо:
SELECT * FROM events
JOIN users ON events.user_id = users.user_id;

#Оптимизация агрегаций

#Агрегация до JOIN

-- Плохо: JOIN больших таблиц с последующей агрегацией
SELECT 
    u.country,
    count() AS events
FROM events_all e
JOIN users_all u ON e.user_id = u.id
GROUP BY u.country;

-- Лучше: агрегация до JOIN
WITH events_by_user AS (
    SELECT user_id, count() AS user_events
    FROM events_all
    GROUP BY user_id
)
SELECT 
    u.country,
    sum(e.user_events) AS events
FROM events_by_user e
JOIN users_all u ON e.user_id = u.id
GROUP BY u.country;

#Approximate агрегаты

-- Точный подсчёт (медленно для больших данных)
SELECT count(DISTINCT user_id) AS users FROM events;

-- Приблизительный (быстро, ошибка ~1-2%)
SELECT uniq(user_id) AS users FROM events;

-- Комбинированный (авто-выбор)
SELECT uniqCombined(user_id) AS users FROM events;

#Агрегация с проекциями

-- Создание проекции для ускорения
ALTER TABLE events ADD PROJECTION daily_stats (
    SELECT 
        toDate(event_time) AS date,
        event_type,
        count() AS events
    GROUP BY date, event_type
    ORDER BY date
);

-- Запрос автоматически использует проекцию
SELECT 
    toDate(event_time) AS date,
    event_type,
    count() AS events
FROM events
GROUP BY date, event_type;

#Оптимизация ORDER BY и LIMIT

#LIMIT с ORDER BY

-- ClickHouse оптимизирует: сортировка до LIMIT
SELECT * FROM events
ORDER BY event_time DESC
LIMIT 100;

-- Сложный случай: сортировка после агрегации
SELECT 
    user_id,
    sum(value) AS total
FROM events
GROUP BY user_id
ORDER BY total DESC
LIMIT 100;

#WITH TIES

-- WITH TIES возвращает все строки с последним значением
SELECT * FROM events
ORDER BY event_time DESC
LIMIT 100 WITH TIES;

#Оптимизация TOP-N

-- argMax для получения строки с максимумом
SELECT 
    user_id,
    argMax(event_type, event_time) AS last_event
FROM events
GROUP BY user_id;

-- Вместо:
SELECT 
    e1.user_id,
    e1.event_type
FROM events e1
JOIN (
    SELECT user_id, max(event_time) AS max_time
    FROM events
    GROUP BY user_id
) e2 ON e1.user_id = e2.user_id AND e1.event_time = e2.max_time;

#Типичные антипаттерны

#1. SELECT *

-- Плохо: читает все колонки
SELECT * FROM events WHERE user_id = 123;

-- Хорошо: только нужные колонки
SELECT event_time, event_type, value 
FROM events 
WHERE user_id = 123;

#2. Функции в WHERE

-- Плохо: функция предотвращает использование индекса
SELECT * FROM events 
WHERE toYYYYMM(event_time) = 202603;

-- Хорошо: диапазонное условие
SELECT * FROM events 
WHERE event_time >= '2026-03-01' 
  AND event_time < '2026-04-01';

#3. DISTINCT и GROUP BY без измерения

-- DISTINCT прямо выражает удаление дублей
SELECT DISTINCT user_id, event_type FROM events;

-- GROUP BY нужен, когда далее появляются агрегаты
SELECT user_id, event_type FROM events GROUP BY user_id, event_type;

Для одного и того же набора колонок оптимизатор может построить близкие планы. Не заменяйте DISTINCT механически; выбирайте выражение по смыслу и сравнивайте план при реальной проблеме.

#4. Коррелированные подзапросы с большим числом повторных вычислений

-- Плохо: коррелированный подзапрос
SELECT 
    user_id,
    (SELECT count() FROM events e WHERE e.user_id = u.id) AS events
FROM users u;

-- Лучше: JOIN
SELECT 
    u.id AS user_id,
    count(e.user_id) AS events
FROM users u
LEFT JOIN events e ON u.id = e.user_id
GROUP BY u.id;

#5. JOIN без ранней фильтрации

-- Исходный вариант читает широкие таблицы
SELECT * FROM 
    events_all e  -- 1 млрд строк
    JOIN users_all u ON e.user_id = u.id  -- 10 млн строк
WHERE u.country = 'RU';

-- Раннее уменьшение данных помогает, если анализатор не сделал это сам
SELECT * FROM 
    (SELECT * FROM users_all WHERE country = 'RU') u
    JOIN events_all e ON u.id = e.user_id;

#Настройки оптимизации

#Важные настройки

-- Разрешить оптимизации
SET optimize_read_in_order = 1;
SET optimize_aggregation_in_order = 1;
SET optimize_or_like_chain = 1;

-- Оптимизация JOIN
SET join_algorithm = 'auto';  -- auto, hash, partial_merge
SET prefer_columnar_to_block_limit = 1024;

-- Оптимизация агрегации
SET optimize_aggregators_order_key = 1;
SET force_aggregation_memory_efficient = 0;

-- Оптимизация сортировки
SET max_bytes_before_external_sort = 0;  -- 0 = без диска

#Проверка настроек

SELECT name, value, changed
FROM system.settings
WHERE changed = 1;

#Мониторинг производительности

#system.query_log

SELECT 
    query,
    read_rows,
    read_bytes,
    result_rows,
    result_bytes,
    elapsed,
    memory_usage
FROM system.query_log
WHERE query_date = today()
  AND query LIKE '%SELECT%'
ORDER BY elapsed DESC
LIMIT 20;

#system.processes

-- Текущие запросы
SELECT 
    query_id,
    query,
    elapsed,
    read_rows,
    read_bytes,
    memory_usage
FROM system.processes
ORDER BY elapsed DESC;

#Практика: одна гипотеза, одно изменение

Возьмите медленный запрос из system.query_log и сохраните его исходные метрики.
По EXPLAIN indexes = 1 и EXPLAIN PIPELINE назовите предполагаемое узкое место.
Измените один фактор: DDL, форму предиката, алгоритм JOIN или предвычисление.
Выполните не менее пяти запусков каждого варианта с одинаковыми настройками.
Запишите медиану, read_bytes, память и цену изменения для вставок или хранения.

Вывод должен содержать область применимости и случай, в котором новая версия станет хуже.

Если запрос уже читает ровно то, что должен, но всё ещё не укладывается в бюджет, следующий вариант — не вычислять один и тот же результат заново. Этому посвящены материализованные представления.

Операция

Описание

На что обратить внимание

ReadFromMergeTree

Чтение из таблицы

Columns read, Parts read

Filter

Применение WHERE

Selectivity

Expression

Вычисления

Number of columns

Aggregating

GROUP BY/агрегаты

Memory usage

Join

JOIN операции

Type, Size

Sorting

ORDER BY

Memory, Disk

Limit

LIMIT

Rows before limit

Union

UNION ALL

Number of inputs