Оптимізація 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
• Навчання команди: як працювати з новою схемою, як додавати нові поля

Наші гарантії: всі зміни тестуються на копії даних, результат вимірюється в цифрах. Ми не просто змінюємо схему — ми доводимо прискорення на ваших запитах. Залиште заявку на аудит — ми зв'яжемося протягом дня.