Использование JOIN в ClickHouse

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

• Для достижения оптимальной производительности пользователи должны стремиться уменьшить количество JOIN в запросах, особенно для аналитических рабочих нагрузок в реальном времени, где требуется работа с миллисекундной производительностью. Стремитесь к максимуму в 3-4 объединения в одном запросе. Мы подробно описываем ряд изменений, чтобы минимизировать объединения в разделе моделирования данных, включая денормализацию, словари и материализованные представления.
• В настоящее время ClickHouse не меняет порядок объединений. Всегда убедитесь, что самая маленькая таблица находится справа от объединения. Это будет находиться в памяти для большинства алгоритмов объединения и обеспечит наименьшее использование памяти для запроса.
• Если вашему запросу требуется прямое объединение, то есть LEFT ANY JOIN — как показано ниже, мы рекомендуем использовать Словари, где это возможно.

• Если вы выполняете внутренние объединения, часто более оптимально записывать их как подзапросы с использованием условия IN. Рассмотрим следующие запросы, которые функционально эквивалентны. Оба находят количество posts, которые не упоминают ClickHouse в вопросе, но делают это в comments.

Обратите внимание, что мы используем ANY INNER JOIN вместо простого INNER JOIN, так как не хотим получать декартово произведение, то есть хотим лишь одно соответствие для каждого поста.

Это объединение можно переписать, используя подзапрос, что значительно улучшит производительность:

Хотя ClickHouse пытается передавать условия во все предложения объединения и подзапросы, мы рекомендуем пользователям всегда вручную применять условия ко всем подзапросам, где это возможно, минимизируя таким образом размер данных, которые нужно JOIN. Рассмотрим следующий пример, где мы хотим вычислить количество положительных голосов за посты, связанные с Java, начиная с 2020 года.

Наивный запрос, с более крупной таблицей слева, завершается за 56 секунд:

Изменение порядка этого объединения значительно улучшает производительность до 1.5 секунд:

Добавление фильтра к таблице справа еще больше улучшает производительность до 0.5 секунд.

Этот запрос можно улучшить еще больше, переместив INNER JOIN в подзапрос, как отмечалось ранее, сохраняя фильтр как в внешнем, так и во внутренних запросах.

Выбор алгоритма объединения

ClickHouse поддерживает несколько алгоритмов объединения. Эти алгоритмы обычно жертвуют использованием памяти ради производительности. Ниже представлено общее описание алгоритмов объединения ClickHouse на основе их относительного потребления памяти и времени выполнения:

Эти алгоритмы определяют способ, которым планируется и выполняется запрос для объединения. По умолчанию ClickHouse использует прямой или хеш-алгоритм для объединения в зависимости от используемого типа объединения, строгости и движка объединяемых таблиц. В качестве альтернативы ClickHouse может быть настроен на адаптивный выбор и динамическое изменение алгоритма объединения во время выполнения, в зависимости от доступности и использования ресурсов: Когда join_algorithm=auto, ClickHouse сначала пробует хеш-алгоритм, и если лимит памяти этого алгоритма нарушается, алгоритм переключается в процессе выполнения на частичное слияние. Вы можете наблюдать, какой алгоритм был выбран, через трассировку логирования. ClickHouse также позволяет пользователям самостоятельно указывать желаемый алгоритм объединения с помощью настройки join_algorithm.

Поддерживаемые типы JOIN для каждого алгоритма объединения показаны ниже и должны быть учтены перед оптимизацией:

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

Выбор подходящих алгоритмов объединения зависит от того, хотите ли вы оптимизировать использование памяти или производительность.

Оптимизация производительности JOIN

Если вашим ключевым показателем оптимизации является производительность, и вы хотите выполнить объединение как можно быстрее, вы можете использовать следующее дерево решений для выбора правильного алгоритма объединения:

• (1) Если данные из правой таблицы могут быть предварительно загружены в структуру данных ключ-значение в памяти с низкой задержкой, например, в словарь, и если ключ объединения соответствует атрибуту ключа подлежащего хранилища ключ-значение, и если семантика LEFT ANY JOIN подходящая, то применяется прямое объединение и предлагает самый быстрый подход.

• (2) Если физический порядок строк вашей таблицы соответствует порядку сортировки ключа объединения, тогда это зависит. В этом случае полное сортировочное слияние пропускает фазу сортировки, что приводит к значительно уменьшенному потреблению памяти, плюс, в зависимости от объема данных и распределения значений ключа объединения, более быстрые времена выполнения, чем некоторые алгоритмы хеширования.

• (3) Если правая таблица помещается в память, даже с дополнительными затратами на использование памяти параллельного хеш-объединения, тогда этот алгоритм или хеш-объединение может быть быстрее. Это зависит от объема данных, типов данных и распределения значений столбцов ключа объединения.

• (4) Если правая таблица не помещается в память, то это опять зависит. ClickHouse предлагает три алгоритма объединения, не привязанные к памяти. Все три временно выполняют выгрузку данных на диск. Полное сортировочное слияние и частичное слияние требуют предварительной сортировки данных. Grace hash join строит хеш-таблицы на основе данных вместо этого. В зависимости от объема данных, типов данных и распределения значений столбцов ключа объединения могут быть сценарии, когда создание хеш-таблиц из данных быстрее, чем сортировка данных. И наоборот.

Частичное слияние оптимизировано для минимизации использования памяти при объединении больших таблиц, за счет скорости объединения, которая довольно медленная. Это особенно актуально, когда физический порядок строк левой таблицы не соответствует порядку сортировки ключа объединения.

Grace hash join является самым универсальным из трех алгоритмов объединения, не привязанных к памяти, и предлагает хороший контроль использования памяти по сравнению со скоростью объединения с настройкой grace_hash_join_initial_buckets. В зависимости от объема данных grace hash может быть быстрее или медленнее, чем частичное слияние, когда количество ведер выбрано так, чтобы использование памяти обоих алгоритмов было примерно одинаковым. Когда использование памяти grace hash join настроено так, чтобы оно примерно совпадало с использованием памяти полного сортировочного слияния, тогда полное сортировочное слияние всегда было быстрее в наших тестах.

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

Оптимизация для использования памяти

Если вы хотите оптимизировать объединение для минимального использования памяти вместо самой быстрой вероятной скорости выполнения, тогда вы можете использовать это дерево решений:

• (1) Если физический порядок строк вашей таблицы соответствует порядку сортировки ключа объединения, тогда использование памяти полного сортировочного слияния минимально. С дополнительным преимуществом хорошей скорости объединения, так как фаза сортировки отключена.
• (2) Grace hash join может быть настроен для очень низкого использования памяти, путем конфигурации большого числа ведер за счет скорости объединения. Частичное слияние намеренно использует мало основной памяти. Полное сортировочное слияние с включенной внешней сортировкой обычно использует больше памяти, чем частичное слияние (при условии, что порядок строк не соответствует порядку сортировки ключа), с преимуществом значительно лучшего времени выполнения объединения.

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