Минимизация и оптимизация JOIN

Страница с рекомендациями по минимизации и оптимизации JOIN

ClickHouse поддерживает широкий спектр типов и алгоритмов JOIN, а производительность JOIN в последних релизах значительно выросла. Однако JOIN по своей природе затратнее, чем запросы к одной денормализованной таблице. Денормализация переносит вычислительную нагрузку с этапа выполнения запроса на этап вставки или предварительной обработки, что часто существенно снижает задержку во время выполнения. Для аналитических запросов в реальном времени или запросов, чувствительных к задержке, денормализация настоятельно рекомендуется.

В общем случае денормализация оправдана, если:

• Таблицы изменяются редко или допустимо пакетное обновление.
• Связи не являются многие-ко-многим и не имеют чрезмерно высокой мощности.
• В запросах будет использоваться только ограниченное подмножество столбцов, то есть некоторые столбцы можно исключить из денормализации.
• У вас есть возможность перенести обработку из ClickHouse во внешние системы, такие как Flink, где можно выполнять обогащение или уплощение в реальном времени.

Денормализовать нужно не все данные — сосредоточьтесь на атрибутах, которые чаще всего используются в запросах. Также рассмотрите materialized views, чтобы инкрементально вычислять агрегаты вместо дублирования целых подтаблиц. Если схема обновляется редко, а задержка критична, денормализация обеспечивает наилучший компромисс по производительности.

Полное руководство по денормализации данных в ClickHouse см. здесь.

​

Когда требуются JOIN

Когда требуются JOIN, убедитесь, что вы используете как минимум версию 24.12, а лучше — самую последнюю, поскольку производительность JOIN продолжает улучшаться с каждым новым релизом. Начиная с ClickHouse 24.12, планировщик запросов автоматически размещает меньшую таблицу справа в JOIN для оптимальной производительности — раньше это приходилось делать вручную. В ближайшее время ожидаются и другие улучшения, включая более агрессивный pushdown фильтров и автоматическое переупорядочивание нескольких JOIN.

Следуйте этим рекомендациям, чтобы повысить производительность JOIN:

• Избегайте декартовых произведений: если значение в левой части соответствует нескольким значениям в правой, JOIN вернет несколько строк — так называемое декартово произведение. Если в вашем сценарии не нужны все совпадения из правой части и достаточно любого одного, можно использовать JOIN ANY (например, LEFT ANY JOIN). Они работают быстрее и потребляют меньше памяти, чем обычные JOIN.
• Уменьшайте размер таблиц, участвующих в JOIN: время выполнения и потребление памяти у JOIN растут пропорционально размерам левой и правой таблиц. Чтобы уменьшить объем данных, обрабатываемых в JOIN, добавьте дополнительные условия фильтрации в секции WHERE или JOIN ON запроса. ClickHouse проталкивает условия фильтрации как можно глубже в план запроса, обычно до JOIN. Если фильтры по какой-либо причине не проталкиваются автоматически, перепишите одну из сторон JOIN как подзапрос, чтобы принудительно применить pushdown.
• Используйте direct JOIN через словари, если это уместно: стандартные JOIN в ClickHouse выполняются в две фазы: на фазе build правая часть проходится для построения хеш-таблицы, затем на фазе probe левая часть проходится для поиска подходящих строк через lookups в хеш-таблице. Если правая часть — это словарь или другой движок таблицы с характеристиками ключ-значение (например, EmbeddedRocksDB или движок таблицы Join), ClickHouse может использовать алгоритм JOIN “direct”, который фактически устраняет необходимость строить хеш-таблицу и ускоряет обработку запросов. Это работает для JOIN INNER и LEFT OUTER и предпочтительно для аналитических рабочих нагрузок в реальном времени.
• Используйте сортировку таблиц для JOIN: каждая таблица в ClickHouse сортируется по столбцам primary key. Эту сортировку можно использовать с помощью так называемых алгоритмов sort-merge JOIN, таких как full_sorting_merge и partial_merge. В отличие от стандартных алгоритмов JOIN на основе хеш-таблиц (см. ниже: parallel_hash, hash, grace_hash), алгоритмы sort-merge JOIN сначала сортируют, а затем объединяют обе таблицы. Если запрос выполняет JOIN обеих таблиц по соответствующим столбцам их primary key, sort-merge использует оптимизацию, которая пропускает этап сортировки, экономя время обработки и снижая накладные расходы.
• Избегайте JOIN со spilling на диск: промежуточные состояния JOIN (например, хеш-таблицы) могут стать настолько большими, что перестанут помещаться в оперативную память. В такой ситуации ClickHouse по умолчанию вернет ошибку нехватки памяти. Некоторые алгоритмы JOIN (см. ниже), например grace_hash, partial_merge и full_sorting_merge, могут выгружать промежуточные состояния на диск и продолжать выполнение запроса. Тем не менее эти алгоритмы JOIN следует использовать с осторожностью, поскольку доступ к диску может значительно замедлить обработку JOIN. Вместо этого мы рекомендуем оптимизировать JOIN-запрос другими способами, чтобы уменьшить размер промежуточных состояний.
• Значения по умолчанию как маркеры отсутствия совпадения во внешних JOIN: левый/правый/full outer join включают все значения из левой/правой/обеих таблиц. Если для некоторого значения в другой таблице не найден партнер JOIN, ClickHouse заменяет его специальным маркером. Стандарт SQL требует, чтобы базы данных использовали в качестве такого маркера NULL. В ClickHouse для этого требуется обернуть результирующий столбец в Nullable, что создает дополнительную нагрузку на память и производительность. В качестве альтернативы можно задать параметр join_use_nulls = 0 и использовать значение по умолчанию типа данных результирующего столбца в качестве маркера.

Используйте словари с осторожностью

При использовании словарей для JOIN в ClickHouse важно понимать, что словари по своей природе не допускают дублирования ключей. При загрузке данных повторяющиеся ключи незаметно дедуплицируются: для каждого ключа сохраняется только последнее загруженное значение. Поэтому словари хорошо подходят для связей «один к одному» и «многие к одному», где нужно только самое актуальное или авторитетное значение. Однако использование словаря для связей «один ко многим» или «многие ко многим» (например, при соединении ролей с актёрами, когда у одного актёра может быть несколько ролей) приведёт к незаметной потере данных, поскольку все совпадающие строки, кроме одной, будут отброшены. В результате словари не подходят для сценариев, где требуется полная реляционная целостность при наличии нескольких совпадений. Подробнее о том, когда словари полезны (и когда нет), см. в разделе Лучшие практики работы со словарями.

​

Выбор правильного алгоритма JOIN

ClickHouse поддерживает несколько алгоритмов JOIN, различающихся по скорости и потреблению памяти:

• Parallel Hash JOIN (default): Быстрый для небольших и средних правых таблиц, помещающихся в память.
• Direct JOIN: Идеален при использовании словарей (или других движков таблиц с характеристиками ключ-значение) с INNER или LEFT ANY JOIN — самый быстрый метод для точечных обращений, поскольку не требует построения хеш-таблицы.
• Full Sorting Merge JOIN: Эффективен, когда обе таблицы отсортированы по ключу JOIN.
• Partial Merge JOIN: Минимизирует потребление памяти, но работает медленнее — лучше всего подходит для JOIN больших таблиц при ограниченном объеме памяти.
• Grace Hash JOIN: Гибкий алгоритм с настраиваемым потреблением памяти, хорошо подходит для больших датасетов с регулируемыми характеристиками производительности.

Каждый алгоритм поддерживает разные типы JOIN. Полный список поддерживаемых типов JOIN для каждого алгоритма можно найти здесь.

Вы можете позволить ClickHouse выбрать лучший алгоритм, установив join_algorithm = 'auto' (значение по умолчанию), либо явно задать его в зависимости от вашей рабочей нагрузки. Если вам нужно выбрать алгоритм JOIN для оптимизации производительности или затрат памяти, мы рекомендуем это руководство.

Для оптимальной производительности:

• Сводите количество JOIN к минимуму в рабочих нагрузках с высокими требованиями к производительности.
• Избегайте более 3–4 JOIN в одном запросе.
• Проводите бенчмарк разных алгоритмов на реальных данных — производительность зависит от распределения ключей JOIN и объема данных.

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