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

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

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

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

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

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

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

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

Перестановка этого соединения значительно улучшает производительность до 1,5 секунды:

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

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

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

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

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

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

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

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

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

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

• (1) Если данные из таблицы справа могут быть заранее загружены в in-memory структуру данных ключ-значение с низкой задержкой, например, словарь, и если ключ соединения соответствует атрибуту ключа основного хранилища ключ-значение, и если семантика 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 можно настроить для очень низкого использования памяти путем конфигурирования большого количества ведер за счет скорости соединения. Частичное объединение специально использует небольшое количество основной памяти. Полное сортировочное соединение с внешней сортировкой, как правило, использует больше памяти, чем частичное объединение (при условии, что порядок строк не совпадает с порядком сортировки ключа), с пользой значительно лучшего времени выполнения соединения.

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