Есть один миф про ClickHouse - он плохо джойнит. Подавляющее большинство не знает, с чем это утверждение связано, и просто верят на слово. А дальше срабатывает эффект сарафанного радио. В итоге в IT-сообществе есть твердое убеждение, что ClickHouse плохо джойнит. Но апогеем стала статья от школы обучения программированию, в которой написано, что ClickHouse вообще не умеет джойнить:
В этот момент стало понятно, что пора внести ясность в происходящее (я на этот бред только недавно наткнулся, хотя статья 2023 года). Давайте разбираться, что же не так с джойнами в ClickHouse и откуда взялся миф, переросший в абсурд. У меня установлен ClickHouse версии 23.12 поднятый в Docker. Специально берем более старую версию, ведь миф зародился давно. Ограничение по RAM - 7ГБ. Для тестов большего не требуется.
Теперь создадим две таблицы по 20 000 000 записей в каждой.
-- создаем левую таблицу с тестовыми данными
drop table if exists join_test_left;
CREATE TABLE join_test_left (
id Int64,
name_good String,
event_time DateTime
) ENGINE = MergeTree()
ORDER BY name_good
SETTINGS index_granularity = 8192;
-- вставляем данные
INSERT INTO join_test_left (id, name_good, event_time)
SELECT * FROM generateRandom('a Int64, b String, c DateTime', 1, 10, 2) LIMIT 20000000; -- 20 000 000
-- создаем правую таблицу с тестовыми данными
drop table if exists join_test_right;
CREATE TABLE join_test_right (
id Int64,
name_good String,
event_time DateTime
) ENGINE = MergeTree()
ORDER BY name_good
SETTINGS index_granularity = 8192;
-- вставляем данные
INSERT INTO join_test_right (id, name_good, event_time)
SELECT * FROM generateRandom('a Int64, b String, c DateTime', 1, 10, 2) LIMIT 20000000; -- 20 000 000
Стоит уточнить, что generateRandom - функция, позволяющая генерировать случайные данные. Удобна как раз для таких тестов.
А теперь попробуем их соединить НЕ по индексу name_good, а по полю id. Оборачиваем это все в create table чтобы не видеть вывод при трейсинге. В качестве логического вида соединения выбираем left any join. Это как left join, только без декартового произведения. Выбирается только первое попавшееся совпадение. Это позволит сэкономить ресурсы. Код запускаемого запроса:
create table join_test_result engine=MergeTree order by id as
select *
from join_test_left t1
left any join join_test_right t2
on t1.id = t2.id"
Результат:
Сразу стоит отметить, что ClickHouse все-таки умеет джойнить. А корень всех зол и мифов кроется в ключевой особенности - типе физического соединения HashJoin. hash - алгоритм, при котором вся правая таблица помещается в оперативную память. И он является алгоритмом по умолчанию, в чем убедимся позже.
Вот ответ на все вопросы. Если справа большая таблица - то он либо выдаст ошибку ООМ, либо же засунет "впритык" всю правую таблицу в оперативную память.
И самое худшее - когда он все-таки засунул правую таблицу в оперативную память. ClickHouse работает по принципу "умри но сделай" - это касается очень многих функций, не только JOIN. И как вы понимаете, помимо вашего джойна на сервере происходит и множество других запросов ваших коллег/BI-систем и т.д., что может привести к падению клика. Именно поэтому и говорят, что клик плохо джойнит - он ради одного джойна может забрать 99% RAM.
Кстати, а давайте взглянем на то, сколько на пике было потрачено ресурсов на выполнение запроса и за сколько времени он выполнился:
Видим 2.33ГБ RAM и 15 секунд.
А теперь давайте попробуем соединить по ключу соединения name_good и посмотрим, какой алгоритм соединения выберет клик и сколько ресурсов затратит:
Видим все тот же HashJoin.
Ну а по ресурсам видим 1.93ГБ RAM и 49 секунд. Индексом специально сделал поле строкового типа, чтобы ему потяжелее было.
Но тут встает логичный вопрос - неужели в клике нет других видов соединения? И если есть - то почему он не выбрал более оптимальный вид соединения?
И вот тут вторая печаль - у клика по умолчанию выбран алгоритм hash + нет CBO (Cost-Based Optimizer) + нет алгоритма выбора оптимального вида соединения (теоретически можно проверить ключи сортировки таблицы и выбрать другой вид соединения). А это значит, что он никогда не поменяет hash без ваших манипуляций.
Вот теперь мы окончательно разобрались, в чем корень проблемы. Но давайте подумаем - а как мы можем это исправить?
partial_merge
Для начала разберемся с настройкой, отвечающей за вид соединения - https://clickhouse.com/docs/operations/settings/settings#join_algorithm.
В ней перечислены все виды физического соединения. Но не совсем верно указано значение по умолчанию. Давайте взглянем на него:
Видим, что по умолчанию выставлено легаси значение default, что на практике, как мы убедились, эквивалентно hash. Хорошая новость в том, что мы можем ее менять не только на уровне сервера, но и на уровне отдельных запросов.
А теперь подумаем: у нас две таблицы, обе отсортированы по ключу name_good. Здесь же прям напрашивается использование алгоритма partial_merge! Давайте попробуем:
Видим, что настройка действительно применилась, и теперь вместо HashJoin выбран алгоритм MergeJoin. Давайте взглянем на ресурсы:
Видим, что RAM было затрачено 1.71ГБ, что немного меньше, чем при hash. Но зато по скорости выполнения получили ускорение в 3 раза! Это отличный результат, но окончательные выводы делать рано.
Увеличиваем нагрузку
Дабы не мусорить скринами опишу текстом, что я сделал: пересоздал таблицы, наполнил 40 млн вместо 20 млн. И тот же самый left any join с разными видами физического соединения. Просто увеличил нагрузку, остальное все тоже самое. Начнем с hash и сразу же покажу ресурсы:
Видим 3.67ГБ RAM и 108 секунд время выполнения.
А теперь взглянем на partial_merge:
Видим все те же 1.8ГБ RAM, но уже 105 секунд время выполнения.
Какой вывод можно сделать? partial_merge довольно часто будет выполняться дольше, чем hash, но он не вылетит по памяти и не убьет сервер, если соединение производится по ключу сортировки (в клике это не просто индекс, а реальный ключ сортировки, т.е. данные на диске будут лежать сразу физически отсортированными по ключу, что, кстати, при partial_merge исключает стадию сортировки правой таблице в принципе, отсюда и одинаковое потребление RAM что при 20 млн что при 40 млн)
Вывод
ClickHouse умеет джойнить, но не оптимально. По умолчанию выбран алгоритм hash (а в новых версиях parallel_hash, это как hash, только параллельно, что еще быстрее сожрет всю RAM), который является самым быстрым, но и самым ресурсоемким в то же время. Но вы можете влиять на это, указав более оптимальный вид соединения с помощью настройки join_algorithm. В общем - рубрика эксперименты + собери сам.
Ну а изучить ClickHouse и многие из его фишек (хотя бы 8192) вы можете на БЕСПЛАТНОМ курсе от автора статьи - https://stepik.org/course/277938/promo