Идиомы запросов ClickHouse

Префикс ключа: считаем на гранулах

Возьмём таблицу событий на 2 миллиона строк с ORDER BY ts. Делим на гранулы по 8192 строки: 2 000 000 разделить на 8192 - это 244 гранулы. Столько единиц чтения в таблице.

Теперь фильтр по одному дню. ClickHouse смотрит в индекс, видит, какие гранулы попадают в диапазон, и читает только их: EXPLAIN indexes=1 показывает Granules 12/244. Двенадцать гранул - это около 98 тысяч строк вместо двух миллионов, в двадцать раз меньше работы.

Работает это только для префикса ключа - для первых его колонок. Если ORDER BY (tenant, ts), то фильтр по tenant отсекает гранулы, фильтр по tenant и ts отсекает ещё лучше, а фильтр по одному только ts не отсекает почти ничего: данные разных арендаторов перемешаны по всему диапазону.

// Поэтому первое, что смотрят при разборе медленного запроса, - строчка Granules в EXPLAIN indexes=1. Она сразу говорит, работал индекс или таблицу прочитали целиком.

EXPLAIN indexes = 1
SELECT count() FROM events
WHERE ts >= '2024-01-10 00:00:00' AND ts < '2024-01-11 00:00:00';

-- Indexes:
--   PrimaryKey
--     Keys: ts
--     Granules: 12/244   <- прочитано 12 гранул из 244

Функция на колонке ключа: в ClickHouse это не приговор

Правило, которое все привезли из других баз: функция вокруг индексируемой колонки убивает индекс, потому что база не умеет заглянуть внутрь функции. В ClickHouse это неверно, и проверяется за минуту.

Тот же запрос по 2 миллионам строк, но фильтр написан как toDate(ts) = '2024-01-10'. EXPLAIN показывает те же Granules 12/244, что и явный диапазон по ts. Причина в том, что ClickHouse знает про монотонные функции: toDate не переставляет значения местами, значит условие на результат можно превратить в диапазон по самому ключу. То же самое умеют toStartOfHour, toStartOfMonth и подобные.

А вот немонотонная функция так не разворачивается. Условие toHour(ts) = 5 нельзя свести к одному отрезку - пять утра встречается каждый день. Индекс тут помогает уже частично: в том же эксперименте прочиталось 57 гранул из 244 вместо двенадцати.

// Практический вывод спокойный: писать диапазоном по ключу всё равно надёжнее, потому что результат не зависит от того, признал ли оптимизатор твою функцию монотонной. Но фраза «toDate в WHERE убивает индекс» для ClickHouse просто неправда, и на собеседовании её лучше не повторять.

Колонки стоят денег

База колоночная: каждая колонка лежит в своём файле и читается отдельно. Значит счёт идёт не за строки, а за прочитанные колонки.

Измерим. Таблица на 500 тысяч строк с семью колонками занимает на диске 6,12 МиБ. Из них колонка uid - 1,91 МиБ. Запрос, которому нужен только uid, прочитает эти 1,91 МиБ. Тот же запрос со звёздочкой прочитает все 6,12 - втрое больше, чтобы получить ровно тот же ответ. На широкой таблице в полсотни колонок разница уже не втрое.

PREWHERE - вторая половина этой идеи. Сначала читаются только колонки из условия, по ним отсеиваются строки, и лишь для выживших дочитываются остальные колонки. Настройка optimize_move_to_prewhere включена по умолчанию, так что чаще всего ClickHouse переносит условие сам, но знать механику надо: она объясняет, почему дешёвое условие по узкой колонке ускоряет запрос сильнее, чем кажется.

// Отсюда же бытовое правило: SELECT * в ClickHouse - не лень, а прямые деньги. В разведочных запросах он допустим, в дашборде и в коде - нет.

Диалект: argMax, словари, приблизительные функции

argMax(значение, по_чему_максимум) возвращает значение той строки, где вторая колонка максимальна. Это местная замена оконным функциям и джойнам таблицы с самой собой: «последнее состояние по каждому пользователю» пишется как argMax(status, ts) с GROUP BY user_id, и считается это одним проходом.

JOIN устроен просто и потому больно: правая таблица целиком загружается в память и превращается в хеш-таблицу. Джойн большой таблицы с маленькой - нормально, большой с большой - упирается в память. Идиоматичные замены две: денормализация при записи (положить нужные поля рядом сразу) и словари - справочник, живущий в памяти сервера, к которому обращаются функцией dictGet вместо джойна.

Приблизительные функции - осознанный размен точности на скорость. На миллионе разных значений uniq вернул 1 001 943 против точного 1 000 000 у uniqExact: ошибка около 0,2 процента при кратно меньшей памяти и времени. Для разведки и дашбордов это честная сделка, для биллинга - нет.

// Массивы с функциями высшего порядка (arrayFilter, arrayMap) и ARRAY JOIN закрывают то, что в других базах пишется подзапросами по событиям. Это уже вторая ступень диалекта, но именно она отличает «пишу как в Postgres» от «пишу по-кликхаусовски».

dictGet

чтение атрибута из словаря в памяти по ключу; стандартная замена джойну со справочником

uniq / uniqExact

приближённый счёт уникальных с ошибкой около доли процента / точный и дорогой

Как отвечать: «Запрос по большой таблице медленный. Что проверишь?»

Иду по порядку стоимости причин. Первое - попадает ли фильтр в префикс ORDER BY. Смотрю EXPLAIN indexes=1 на строчку Granules: если прочитано 244 из 244, индекс не сработал и дальше можно не гадать. Второе - какие колонки читаются: база колоночная, и на нашей таблице звёздочка стоит 6 мегабайт против двух за одну нужную колонку, поэтому убираю SELECT *. Третье - джойны: правая таблица целиком уходит в память, так что большой с большим меняю на словарь или на денормализацию при записи. Дальше смотрю system.query_log - сколько строк и байт запрос реально прочитал, это разом подтверждает или опровергает все гипотезы. А вот функцию вокруг колонки ключа я бы сходу не обвинял: если она монотонная, вроде toDate, ClickHouse разворачивает её в диапазон и гранулы отсекаются так же, как при явном условии по ts.

Диагностический маршрут от самой дорогой причины к дешёвым, с проверкой по логу и без расхожего мифа про функцию на колонке.

На чём валят

• −Фильтровать по колонке вне префикса ORDER BY и удивляться чтению всей таблицы.
• −Джойнить две большие таблицы «как в Postgres»: правая часть целиком уходит в память.
• −Оставлять SELECT * там, где нужны три колонки: в колоночной базе это прямая плата за чтение.
• −Брать uniqExact на миллиардах, где хватило бы uniq с ошибкой в доли процента.
• −Повторять миф, что toDate(ts) в WHERE отключает индекс: монотонные функции ClickHouse разворачивает в диапазон.
• −Писать оконные функции там, где argMax с GROUP BY решает задачу одним проходом.