Оптимизация ClickHouse: партиционирование, индексы, MV

Запрос SELECT по таблице в 2 триллиона строк выполняется 40 секунд. Disk I/O под 100%, сервер гудит. Знакомая картина. Обычно это означает, что первичный ключ подобран неудачно, партиционирование не соответствует паттерну запросов, или данные гоняются через движок без использования skip-индексов. Мы — команда инженеров с 10+ годами опыта в эксплуатации ClickHouse на продакшн-нагрузках. Наш подход гарантирует измеримый результат: типичная экономия времени выполнения запросов — 5–10 раз, снижение нагрузки на диск — до 70%. Свяжитесь с нами для аудита вашей схемы — мы оценим потенциал оптимизации за 1 день.

Почему партиционирование — не панацея

Типичная ошибка: создать таблицу с PARTITION BY toYYYYMM(event_date) и ждать чудес. Партиционирование в ClickHouse — это прежде всего инструмент управления данными (TTL, дропы старых партиций), а не ускорения запросов. Запрос по конкретному месяцу действительно пропустит лишние партиции, но если фильтр по дате нестрогий или вовсе отсутствует — ClickHouse прочитает всё.

Реальная история: клиент партиционировал таблицу событий по toYYYYMMDD(ts) — 365 партиций в год. Это привело к тому, что на инсерте каждую ночь создавались тысячи мелких part-ов, MergeTree не успевал их слить, и запросы начинали открывать сотни файловых дескрипторов. Системный журнал пестрел Too many parts (300). Merges are processing significantly slower than inserts. Откат к партиционированию по месяцу плюс настройка merge_with_ttl_timeout решил проблему за час.

Оптимальная гранулярность партиции — та, при которой одна партиция содержит данные за период, удобный для дропа. День — только если объём дневных данных превышает несколько гигабайт. Иначе работайте с месяцами.

Как выбрать правильный ORDER BY?

В ClickHouse ORDER BY определяет физическую сортировку данных на диске и одновременно является первичным ключом (если PRIMARY KEY не указан явно). Гранула по умолчанию — 8192 строк. Каждая гранула описывается в sparse-индексе, и именно по нему ClickHouse решает, какие гранулы читать.

Если первый элемент ORDER BY — это user_id UUID, а все запросы фильтруют по event_type и ts, индекс бесполезен. ClickHouse прочитает все гранулы.

Правило подбора ключа: сначала поля с низкой кардинальностью, которые встречаются в WHERE чаще всего, потом поля с высокой. Например:

CREATE TABLE events (
    event_type LowCardinality(String),
    project_id UInt32,
    ts DateTime,
    user_id UUID,
    payload String
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(ts)
ORDER BY (event_type, project_id, ts)
SETTINGS index_granularity = 8192;

Запрос WHERE event_type = 'click' AND project_id = 42 AND ts >= now() - INTERVAL 1 YEAR теперь прочитает только нужный диапазон гранул.

Как работают skip-индексы?

minmax, set, bloom_filter, tokenbf_v1 — четыре основных типа skip-индексов. Они не заменяют первичный ключ, а дополняют его для полей, которые не вошли в ORDER BY.

bloom_filter незаменим для поиска по UUID или хэшу:

ALTER TABLE events ADD INDEX idx_user_id user_id TYPE bloom_filter(0.01) GRANULARITY 4; 

После материализации (MATERIALIZE INDEX) запрос WHERE user_id = '...' пропустит гранулы, в которых этого UUID точно нет. При 1% false positive rate и 1000 грануль реально читать будет 10-50 вместо всех.

tokenbf_v1 работает по токенам строки — удобно для поиска подстрок в логах без LIKE '%pattern%'.

Что такое материализованные представления и когда их использовать?

Если один и тот же агрегирующий запрос выполняется часто — перенесите агрегацию на момент вставки. MV в ClickHouse — это триггер на INSERT, который направляет данные в целевую таблицу-агрегат.

CREATE MATERIALIZED VIEW events_hourly_mv TO events_hourly AS
SELECT
    toStartOfHour(ts) AS hour,
    event_type,
    project_id,
    count() AS cnt,
    uniqHLL12(user_id) AS uniq_users
FROM events
GROUP BY hour, event_type, project_id;

Целевая таблица использует движок AggregatingMergeTree с AggregateFunction:

CREATE TABLE events_hourly (
    hour DateTime,
    event_type LowCardinality(String),
    project_id UInt32,
    cnt AggregateFunction(count),
    uniq_users AggregateFunction(uniqHLL12, UUID)
) ENGINE = AggregatingMergeTree()
ORDER BY (hour, event_type, project_id);

Запрос по агрегированным данным работает в 10-100 раз быстрее, чем по сырым. Но важно понимать: MV обновляется только на новых вставках. Исторические данные нужно добавлять вручную через INSERT INTO ... SELECT.

Как профилировать запросы?

Прежде чем что-то менять, смотрим в system.query_log:

SELECT query_id, query, read_rows, read_bytes, result_rows, elapsed, memory_usage
FROM system.query_log
WHERE type = 'QueryFinish'
  AND query_start_time >= now() - INTERVAL 1 HOUR
ORDER BY elapsed DESC
LIMIT 20;

read_bytes / result_bytes — коэффициент избыточного чтения. Если читаем 100 GB ради 1 MB результата — индекс не работает или его нет.

system.parts показывает состояние партиций и number of parts. Если count(*) > 300 для одной партиции — MergeTree не успевает мержить, нужно смотреть на background_pool_size и max_bytes_to_merge_at_max_space_in_pool.

Как мы оптимизируем ClickHouse

Начинаем с аудита: собираем топ-20 тяжёлых запросов из system.query_log, смотрим схему таблиц, профилируем через EXPLAIN indexes=1, actions=1. На этом этапе уже видно 80% проблем.

Дальше — проектирование: предлагаем изменения ORDER BY/PARTITION BY, список skip-индексов, перечень MV. Согласовываем с командой — изменение ключа сортировки требует пересоздания таблицы с data migration.

Реализация: создаём новые таблицы, настраиваем MV, переливаем данные партиями через INSERT INTO new_table SELECT * FROM old_table WHERE partition_key = X. Параллельно пишем тесты производительности — сравниваем elapsed до и после на идентичных запросах.

После миграции оставляем документацию: какой индекс для каких запросов, как мониторить system.parts, когда нужен OPTIMIZE TABLE ... FINAL.

Ориентировочные сроки и объём работ

Стоимость рассчитывается индивидуально после аудита инфраструктуры и анализа схемы данных. Типичная экономия на инфраструктуре за счёт снижения нагрузки — до 40% затрат на облачные инстансы.

Что входит в работу

• Анализ схемы данных и топ-20 тяжёлых запросов (EXPLAIN, system.query_log)
• Проектирование партиционирования, первичного ключа и skip-индексов
• Создание и настройка материализованных представлений
• Миграция данных с минимальным downtime (rolling migration)
• Документация по эксплуатации: рекомендации по мониторингу, типовые запросы, runbook
• Обучение команды: как работать с новой схемой, как добавлять новые поля

Наши гарантии: все изменения тестируются на копии данных, результат измеряется в цифрах. Мы не просто меняем схему — мы доказываем ускорение на ваших запросах. Оставьте заявку на аудит — мы свяжемся в течение дня.