Минимизировать и оптимизировать JOIN

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

В целом, денормализуйте, когда:

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

Не все данные нужно денормализовать - сосредоточьтесь на атрибутах, которые часто запрашиваются. Также рассмотрите использование материализованных представлений для поэтапного вычисления агрегатов вместо дублирования целых подтаблиц. Когда обновления схемы редки и задержка критична, денормализация предлагает наилучший компромисс в производительности.

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

Когда JOIN необходимы

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

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

• Избегайте декартовых произведений: Если значение с левой стороны совпадает с несколькими значениями с правой стороны, JOIN вернет несколько строк - так называемое декартово произведение. Если ваш случай использования не требует всех совпадений с правой стороны, а только любое отдельное совпадение, вы можете использовать ANY JOIN (например, LEFT ANY JOIN). Они быстрее и используют меньше памяти, чем обычные JOIN.
• Уменьшите размеры JOINed таблиц: Время выполнения и расход памяти JOIN пропорционально увеличиваются с размерами левой и правой таблиц. Чтобы уменьшить объем обрабатываемых данных при JOIN, добавьте дополнительные условия фильтрации в WHERE или JOIN ON clauses запроса. ClickHouse продвигает условия фильтрации как можно глубже по плану запроса, обычно перед JOIN. Если фильтры автоматически не проникают (по какой-либо причине), перепишите одну сторону JOIN как подзапрос, чтобы заставить фильтрацию.
• Используйте прямые JOIN через словари, если это уместно: Стандартные JOIN в ClickHouse выполняются в два этапа: сначала строится хеш-таблица по правой стороне, а затем выполняется обход левой стороны для нахождения соответствующих партнеров по JOIN с помощью поиска в хеш-таблице. Если правая сторона является словарем или другим движком таблиц с характеристиками ключ-значение (например, EmbeddedRocksDB или движок таблиц JOIN), тогда ClickHouse может использовать алгоритм "прямого" JOIN, который фактически устраняет необходимость в построении хеш-таблицы, ускоряя обработку запросов. Это работает для INNER и LEFT OUTER JOIN и предпочтительно для аналитических потоков в реальном времени.
• Используйте сортировку таблиц для JOIN: Каждая таблица в ClickHouse отсортирована по первичным ключевым колонкам таблицы. Можно использовать сортировку таблицы с помощью так называемых алгоритмов сортировки-слияния JOIN, таких как full_sorting_merge и partial_merge. В отличие от стандартных алгоритмов JOIN на основе хеш-таблиц (см. ниже, parallel_hash, hash, grace_hash), алгоритмы сортировки-слияния сначала сортируют, а затем сливают обе таблицы. Если запрос JOIN объединяет обе таблицы по соответствующим первичным ключевым колонкам, то сортировка-слияние имеет оптимизацию, которая пропускает шаг сортировки, экономя время обработки и накладные расходы.
• Избегайте JOIN с переполнением диска: Промежуточные состояния JOIN (например, хеш-таблицы) могут стать такими большими, что они перестают помещаться в основную память. В этой ситуации ClickHouse по умолчанию выдает ошибку нехватки памяти. Некоторые алгоритмы JOIN (см. ниже), например grace_hash, partial_merge и full_sorting_merge, способны перенести промежуточные состояния на диск и продолжить выполнение запроса. Тем не менее, эти алгоритмы следует использовать с осторожностью, так как доступ к диску может значительно замедлить обработку JOIN. Мы вместо этого рекомендуем оптимизировать запрос JOIN другими способами, чтобы уменьшить размер промежуточных состояний.
• Значения по умолчанию в качестве маркеров отсутствия совпадений в внешних JOIN: Левые/правые/полные внешние JOIN включают все значения из левой/правой/обеих таблиц. Если не найден партнер по JOIN в другой таблице для какого-либо значения, ClickHouse заменяет партнера по JOIN специальным маркером. Стандарт SQL предписывает, чтобы базы данных использовали NULL в качестве такого маркера. В ClickHouse это требует обертывания результирующей колонки в Nullable, создавая дополнительную накладную память и производительность. В качестве альтернативы вы можете настроить параметр join_use_nulls = 0 и использовать значение по умолчанию типа данных результирующей колонки в качестве маркера.

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

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

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

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

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

примечание

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

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

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

• Минимизируйте количество JOIN в рабочих нагрузках высокой производительности.
• Избегайте более 3-4 JOIN на запрос.
• Тестируйте различные алгоритмы на реальных данных - производительность варьируется в зависимости от распределения ключей JOIN и размера данных.

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