Оптимизация запросов в ClickHouse: индексы, EXPLAIN и лучшие практики. Урок 6

Итак, вы освоили типы данных, создали таблицы на правильных движках MergeTree и даже научились писать сложные запросы. Кажется, что вы готовы к работе с реальными данными. Однако на больших объемах вы можете столкнуться с ситуацией, когда даже на мощном “железе” запрос выполняется не так быстро, как хотелось бы. В чем же дело? Ответ почти всегда кроется в оптимизации запросов ClickHouse. Простого знания SQL недостаточно; нужно понимать, как CH выполняет вашу команду “под капотом”. В этой статье мы глубоко погрузимся в три кита производительности ClickHouse: правильное использование первичного ключа, вторичные индексы и анализ плана выполнения запроса.

Краеугольный камень оптимизации запросов ClickHouse: Правильное использование Первичного Ключа

Мы уже говорили об этом, но это настолько важно, что требует повторения и углубления. Производительность 99% ваших запросов зависит от того, как вы используете первичный ключ (то, что указано в ORDER BY вашей MergeTree таблицы).

Напомним, ClickHouse хранит данные на диске отсортированными по этому ключу. Поверх этих данных он строит разреженный (sparse) первичный индекс. “Разреженный” означает, что индекс хранит “засечки” не для каждой строки, а для каждого блока данных (гранулы). Когда вы фильтруете по первичному ключу, ClickHouse смотрит в этот индекс и мгновенно определяет, какие именно гранулы ему нужно прочитать с диска, игнорируя остальные 99% данных.

Золотое правило: Фильтруйте по префиксам ключа без функций

Чтобы индекс работал, ваше WHERE условие должно применяться напрямую к столбцам из ORDER BY.

ПРАВИЛЬНО (Быстро):

Предположим, у нас есть таблица access_logs с ORDER BY (event_date, user_id).

SELECT count()
FROM access_logs
WHERE
    event_date >= '2025-06-01' AND event_date < '2025-07-01'
    AND user_id IN (101, 102, 103);

Почему это быстро? ClickHouse использует индекс для event_date, чтобы быстро найти нужный диапазон гранул, а затем внутри этого диапазона так же эффективно ищет по user_id.

НЕПРАВИЛЬНО (Медленно):

SELECT count()
FROM access_logs
WHERE toMonth(event_date) = 6;

Почему это медленно? Применение любой функции (toMonth, toString) к столбцу из первичного ключа делает индекс бесполезным. ClickHouse не может использовать отсортированные данные. Ему приходится прочитать весь столбец event_date за весь период, применить к каждому значению функцию toMonth() и только потом сравнить результат. Это называется full scan (полное сканирование) и является главным врагом производительности.

Вторичные индексы (Data Skipping Indexes): Когда первички не хватает

Что делать, если вам часто нужно фильтровать по столбцу, который не входит в первичный ключ? Например, в нашей таблице access_logs мы часто ищем по url или event_type. Именно для этого и существуют вторичные индексы, или Data Skipping Indexes. Они не указывают на конкретные строки, а хранят агрегированную мета-информацию о каждой грануле данных. Когда вы фильтруете по столбцу с таким индексом, ClickHouse сначала смотрит на метаданные и решает, можно ли пропустить (to skip) тот или иной блок данных целиком.

Синтаксис и популярные типы

• minmax: Хранит минимальное и максимальное значение для столбца в блоке. Подходит для числовых и временных типов.
• set(N): Хранит до N уникальных значений столбца в блоке. Идеально для столбцов с невысокой кардинальностью.
• bloom_filter: Вероятностная структура данных. Отлично подходит для столбцов с высокой кардинальностью (например, url). Она с высокой точностью может сказать, нет ли искомого значения в блоке.

Пример: Давайте добавим в нашу таблицу индексы для event_type и url.

ALTER TABLE access_logs ADD INDEX idx_event_type event_type TYPE set(3) GRANULARITY 1;
ALTER TABLE access_logs ADD INDEX idx_url url TYPE bloom_filter() GRANULARITY 1;

Теперь запросы вида WHERE event_type = 'click' или WHERE url LIKE '%/login%' будут выполняться значительно быстрее.

Анализ оптимизации запроса ClickHouse  с EXPLAIN: Заглянем под капот

Как понять, работает ваш индекс или нет? Для этого существует команда EXPLAIN. Нас больше всего интересует EXPLAIN PLAN или EXPLAIN ESTIMATE, который показывает пошаговый план выполнения и оценку.

“Хороший” запрос: SELECT count() FROM access_logs WHERE event_date = '2025-06-20'

EXPLAIN PLAN SELECT count() FROM access_logs WHERE event_date = '2025-06-20';
...
 ReadFromMergeTree
  Partitions: 1/12
  Marks: 16/1953
...

Ключевые показатели здесь Marks: 16/1953. Это означает, что для выполнения запроса ClickHouse прочитал всего 16 гранул из 1953 имеющихся. Это превосходный результат!

“Плохой” запрос: SELECT count() FROM access_logs WHERE toYear(event_date) = 2025

EXPLAIN PLAN SELECT count() FROM access_logs WHERE toYear(event_date) = 2025;
...
 ReadFromMergeTree
  Partitions: 12/12
  Marks: 1953/1953
...

Здесь мы видим Marks: 1953/1953. Это значит, что ClickHouse был вынужден прочитать все гранулы. Индекс не сработал, произошел full scan.

Дополнительные практики для оптимизации запросов ClickHouse

• Используйте PREWHERE: Это аналог WHERE, но он выполняется до чтения всех столбцов, указанных в SELECT. Если вы сильно фильтруете данные по столбцу, который не используется в SELECT, поместите это условие в PREWHERE.
• Никогда не используйте SELECT *: Всегда перечисляйте только те столбцы, которые вам действительно нужны.
• Оптимизируйте запросы с JOIN: ClickHouse — не лучшая база для JOIN‘ов, но если они нужны, используйте правые таблицы как можно меньшего размера и рассмотрите использование движка Join https://clickhouse.com/docs/en/engines/table-engines/special-table-engines/join или внешних словарей (Dictionary).
• Аккуратно с GROUP BY: Группировка по столбцам с высокой кардинальностью (например, user_id или url) потребляет очень много оперативной памяти.

Заключение

Оптимизация запросов в ClickHouse — это не магия, а систематический подход.

Фундамент: Проектируйте таблицы с правильным ключом сортировки (ORDER BY).

Запросы: Всегда пишите WHERE условия так, чтобы они использовали этот ключ напрямую, без функций.

Вспомогательные инструменты: Для частых фильтров по неключевым столбцам создавайте вторичные индексы (data skipping).

Проверка: Постоянно анализируйте свои запросы с помощью EXPLAIN PLAN и следите за количеством читаемых “меток” (Marks).

Освоив эти принципы, вы сможете добиться от ClickHouse той феноменальной производительности, ради которой он и создавался.

Использованные референсы и материалы по Оптимизации Запросов в ClickHouse

1. Официальная документация ClickHouse по индексам Data Skipping: https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree#data-skipping-indexes
2. Статья “Primary Keys in ClickHouse” от Altinity: https://altinity.com/blog/2020/4/15/clickhouse-primary-keys-and-their-bad-relatives

SQL-блокнот к Уроку 6 бесплатного курса доступен в нашем репозитории на GitHub