Есть один миф про 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, только без декартового произведения. Выбирается только первое попавшееся совпадение. Это позволит сэкономить ресурсы. Код запускаемого запроса:
clickhouse client --send_logs_level='trace' --query " 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.name_good = t2.name_good"
Результат:
Сразу стоит отметить, что 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 уменьшает время выполнения соединения, так как он производит внешнюю сортировку, и эта операция осуществляется быстрее по уже отсортированным данным.
Но снова возникает резонный вопрос - а зачем он сортирует данные, если они и так отсортированы? Можем ли мы ему сказать "не сортируй"?
full_sorting_merge
full_sorting_merge в данном случае будет являться оптимальным алгоритмом. В старых версиях в этом виде соединения нужно явно указывать отключение сортировки - добавляем к запросу настройку max_rows_in_set_to_optimize_join = 0. Смотрим на ресурсы:
115 секунд против 105 у partial_merge, но зато 276МБ против 1.8ГБ, а если сравнивать с базовым hash - то разница колоссальная: 3.67 / 0.276 ~ 13. В 13 раз уменьшили потребление RAM!
Вывод
ClickHouse умеет джойнить, но не оптимально. По умолчанию выбран алгоритм hash (а в новых версиях parallel_hash, это как hash, только параллельно, что еще быстрее сожрет всю RAM), который является самым быстрым, но и самым ресурсоемким в то же время. Но вы можете влиять на это, указав более оптимальный вид соединения с помощью настройки join_algorithm. В общем - рубрика эксперименты + собери сам.
Ну а изучить ClickHouse и многие из его фишек (хотя бы 8192) вы можете на БЕСПЛАТНОМ курсе от автора статьи - https://stepik.org/course/277938/promo
