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

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

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

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

SELECT count()
FROM stackoverflow.posts AS p
ANY INNER `JOIN` stackoverflow.comments AS c ON p.Id = c.PostId
WHERE (p.Title != '') AND (p.Title NOT ILIKE '%clickhouse%') AND (p.Body NOT ILIKE '%clickhouse%') AND (c.Text ILIKE '%clickhouse%')

┌─count()─┐
│       86 │
└─────────┘

1 строка в наборе. Прошло: 8.209 сек. Обработано 150.20 млн строк, 56.05 ГБ (18.30 млн строк/с., 6.83 ГБ/с.)
Пиковое потребление памяти: 1.23 ГиБ.

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

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

SELECT count()
FROM stackoverflow.posts
WHERE (Title != '') AND (Title NOT ILIKE '%clickhouse%') AND (Body NOT ILIKE '%clickhouse%') AND (Id IN (
        SELECT PostId
        FROM stackoverflow.comments
        WHERE Text ILIKE '%clickhouse%'
))
┌─count()─┐
│       86 │
└─────────┘

1 строка в наборе. Затрачено: 2.284 сек. Обработано 150.20 млн строк, 16.61 ГБ (65.76 млн строк/с., 7.27 ГБ/с.)
Пиковое использование памяти: 323.52 МиБ.

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

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

SELECT countIf(VoteTypeId = 2) AS upvotes
FROM stackoverflow.posts AS p
INNER JOIN stackoverflow.votes AS v ON p.Id = v.PostId
WHERE has(arrayFilter(t -> (t != ''), splitByChar('|', p.Tags)), 'java') AND (p.CreationDate >= '2020-01-01')

┌─upvotes─┐
│  261915 │
└─────────┘

1 row in set. Elapsed: 56.642 sec. Processed 252.30 million rows, 1.62 GB (4.45 million rows/s., 28.60 MB/s.)

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

```sql
SELECT countIf(VoteTypeId = 2) AS upvotes
FROM stackoverflow.votes AS v
INNER JOIN stackoverflow.posts AS p ON v.PostId = p.Id
WHERE has(arrayFilter(t -> (t != ''), splitByChar('|', p.Tags)), 'java') AND (p.CreationDate >= '2020-01-01')

┌─upvotes─┐
│  261915 │
└─────────┘

1 строка в наборе. Затраченное время: 1,519 с. Обработано 252,30 млн строк, 1,62 GB (166,06 млн строк/с, 1,07 GB/с.)

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

SELECT countIf(VoteTypeId = 2) AS upvotes
FROM stackoverflow.votes AS v
INNER JOIN stackoverflow.posts AS p ON v.PostId = p.Id
WHERE has(arrayFilter(t -> (t != ''), splitByChar('|', p.Tags)), 'java') AND (p.CreationDate >= '2020-01-01') AND (v.CreationDate >= '2020-01-01')

┌─upvotes─┐
│  261915 │
└─────────┘

1 строка в наборе. Затрачено: 0.597 сек. Обработано 81.14 млн строк, 1.31 ГБ (135.82 млн строк/с., 2.19 ГБ/с.)
Пиковое использование памяти: 249.42 МиБ.

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

SELECT count() AS upvotes
FROM stackoverflow.votes
WHERE (VoteTypeId = 2) AND (PostId IN (
        SELECT Id
        FROM stackoverflow.posts
        WHERE (CreationDate >= '2020-01-01') AND has(arrayFilter(t -> (t != ''), splitByChar('|', Tags)), 'java')
))

┌─upvotes─┐
│  261915 │
└─────────┘

Получена 1 строка. Затрачено: 0,383 сек. Обработано 99,64 млн строк, 804,55 МБ (259,85 млн строк/сек., 2,10 ГБ/сек.)
Пиковое использование памяти: 250,66 МиБ.

Выбор алгоритма JOIN

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Оптимизация по памяти

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

• (1) Если физический порядок строк в вашей таблице совпадает с порядком сортировки по ключу JOIN, то потребление памяти у full sorting merge join будет минимально возможным. Дополнительным преимуществом является высокая скорость выполнения JOIN, поскольку фаза сортировки отключается.
• (2) Grace hash join можно настроить на очень низкое потребление памяти, настроив большое количество бакетов ценой снижения скорости JOIN. Partial merge join изначально спроектирован так, чтобы использовать малый объем оперативной памяти. Full sorting merge join с включенной внешней сортировкой, как правило, потребляет больше памяти, чем partial merge join (если порядок строк не совпадает с порядком сортировки по ключу), но обеспечивает значительно более высокую скорость выполнения JOIN.

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