Разрываем осеннюю непогоду 🍁 заметками про Clickhouse: немного наюнсов, связанных PARTITION BY и ORDER BY
Чуть-чуть тема затронута в посте О-Optimization, там дан инструмент для поиска
неоптимальности при запросах в Clickhouse.
Зачем все эти BY:
- PARTITION BY - ключ партицирования = физическое разделение данных на дисках, то данные для
разных партиций хранятся отдельно, это даёт 2 положительных эффекта:
- Partition pruning - техника, которая при построении плана запроса позволяет пропускать не нужные партции (которые не содержат запрашиваемых данных) -> меньше данных -> выше эффективность запроса
- обновление данных - возможно управлять сразу всей партицией (удалять DROP, обновлять\заменять\перемещать REPLACE/MOVE)
- ORDER BY:
- побочный эффект - данные отсортированы в указанном порядке -> некоторые агрегаты, join, оконки будут работать быстрее
- skipping indexes (скипающие индексы) - при построении плана запроса позволяют пропускать гранулы (блоки строк) -> меньше данных -> выше эффективность запроса
Подробности в доке partitions, а нас будут интересовать детали, которые никто и нигде не напишет,
если только шепотом расскажет 🤫
.
Выше показано, что и PARTITION и ORDER могут быть использованы для эффективной фильтрации данных в запросах, но порой можно
сильно споткнуться на этом:
- партиции есть, а запрос долгий
- индекс есть, а запрос долгий
- и даже поля из партиций и индексов используются в WHERE, а всё равно долго
В предыдущем посте показано как узнать о неоптимальности своего запроса = сравнить два вывода:
explain estimate select * from your_table
vs
explain estimate select * from your_table where ..... -- ваши условия
Если цифры в обоих случаях идентично, то что-то явно идет не так....
.
1️⃣ Поле партицирования: то, да не то!
Задать выражение для партиций можно множеством способов, например:
- года (toYYYY(..) или toStartOfYear)
- месяца (toYYYYMM(..) или toStartOfMonth)
- недели
- дни (можно явно report_date, можно неявно toDate(report_date))
Некоторые преобразования линейные (не меняют тип данных в зависимости от типов до и после, например, toDate или toStartOfWeek, по факту транкейт даты)
нелинейные (меняют тип данных, но смысл остается тем же, например, toYYYYMM).
Выбор поля партиционирования и выражения для него влияют на использование механизма Partition pruninng или нет: для нелинейных преобразований механизм не будет использован ☝️
Например, ddl:
PARTITION BY toYYYYMM(report_date)
Пользовательские запросы любых видов используются фильтрацию report_date или иные преобразования будут неэффективны, например:
select *
from your_table
where toDate(report_date) >= ....
-- toStartOfWeek(date) = ....
и тд
Хотя с точки зрения пользователя может наблюдаться явное непонимание, особенно когда фильтрация явно указывается по году (
report_date >= toDate('2025-01-01'')).Второй распространенный кейс (из области мисскоммуникаций): выражение в партиции использует datetime + в самой таблице есть явное поле date:
PARTITION BY toDate(report_datetime)
Пользователь может совершать логическую ошибку: использование report_date (партиции же по дням).
☝️ Описанное выше протестировано локально на clickhouse-server:25.9.2.1 и проблемы описанные с нелинейными преобразованиями не удалось вопроизвести, хотя есть рабочий r&d (для версии 24.3), подтверждающий, что
нелинейные преобразования отключают Partition pruning. Одно можно сказать точно:
- использование при фильтрации преобразования аналогично указанному в DDL даёт 100% гарантию эффективной фильтрации ✅
#clickhouse
#partition_by
