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

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

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

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

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

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

Хотя ClickHouse пытается перенести условия в все операторы объединения и подзапросы, мы рекомендуем пользователям всегда вручную применять условия ко всем подусловиям, где это возможно - тем самым минимизируя объем данных для объединения. Рассмотрим следующий пример, в котором мы хотим вычислить количество голосов "за" для постов о 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 строит хеш-таблицы из данных вместо этого. В зависимости от объема данных, типов данных и распределения значений столбцов ключей объединения могут быть сценарии, когда построение хеш-таблиц из данных быстрее, чем сортировка данных. И наоборот.

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

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

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

Оптимизация для памяти

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

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

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