Краеугольные камни ClickHouse

Привет, Хабр! Меня зовут Артемий Кравцов, я работаю инженером в Wildberries. Сегодня расскажу про то, что люблю — про ClickHouse. Моя цель — осветить некоторые ключевые особенности в архитектуре ClickHouse и в том, как он хранит данные. Затронем широко распространенные стереотипы про ClickHouse.

Наш план:

1. что такое куски и слияния,

2. как быть с частыми вставками,

3. как обновлять значения в строках,

4. что не так с точечными чтениями,

5. как сделать тяжёлый JOIN.

Статья рассчитана на тех, кто только знакомится с ClickHouse.

Структура хранения данных

Начнем с LSM-деревьев (Log-Structured Merge), потому что ClickHouse во многом очень похож на LSM-дерево. Так выглядит классическое LSM-дерево:

Основное их свойство — append‑only (только добавление). Это означает, что все вставки в таблицу копятся в памяти до тех пор, пока их не наберется какое‑то достаточное количество. После этого из них формируется целостная и самодостаточная структура, которая последовательно записывается на диск большим куском, — практически таблица внутри таблицы. Оригинальное ее название — SS Tables (sorted string tables). Обратите внимание на S, который означает отсортированный. Данные в SS Tables отсортированы. При создании SS Table рядом с данными появляются метаданные и всё то, что впоследствии поможет ускорить работу с данными: индексы, фильтры Блума и так далее.

Часто LSM‑деревьям противопоставляют базы данных на основе B‑деревьев. Эти БД вы прекрасно знаете — Oracle, PostgreSQL, MySQL. Там всё происходит немного по‑другому: данные хранятся в небольших по размерам страницах, и потом, когда нужно что‑то исправить или удалить, база данных правит эти же самые страницы. Для этого B‑деревья нарочно оставляют немного свободного места в своих страницах — чтобы было пространство для апдейтов. В таких базах индексы обычно относятся ко всей таблице сразу. Для того, чтобы провести одну вставку, нужно перебрать всю эту здоровую индексную структуру (которая относится ко всей таблице), найти место, куда вставить новую запись, потом, возможно, перестроить или отсортировать — то есть сделать какое‑то количество лишней работы.

В LSM‑деревьях ничего подобного нет: данные пишутся достаточно большими кусками, вставки проходят точечно, не приходится делать много глобальных правок. Поэтому считается, что LSM‑деревья оптимизированы на запись. И ClickHouse — одна из таких баз, в которой запись в самом деле проходит довольно быстро. Даже несмотря на то, что в ClickHouse нет MemTable.

Все, что вы вставляете в ClickHouse, попадает на диск сразу же, не задерживаясь в MemTable. ClickHouse формирует кусок данных (здесь и далее по тексту под «куском» подразумевается та самая таблица внутри таблицы, то есть SSTable) из того, что вы вставляете в таблицу, и данные сразу попадают на диск. Если вы вставили 100 строк, то кусок у вас образуется из ста строк, если 500, то из пятиста и т. д. Только если в вашей вставке больше миллиона строк (либо больше ~250 мегабайт, смотря что наступит раньше), ClickHouse начнет дробить вставку на несколько кусков. Гарантируется атомарность в рамках одного куска. Это означает, что ваши вставки в ClickHouse будут атомарны до тех пор, пока вы вставляете в одну таблицу на одном сервере, в одну партицию и вставляете меньше, чем миллион строк. Настройку с миллионом строк или 250 мегабайтами можно подтюнить, если вам для чего‑то хочется увеличить границы атомарности, и вы готовы отдать на вставки чуть больше памяти.

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

1. В ней два поля: idx — числовое, и name — строковое (стринговое), оно вычисляется автоматически при вставке в зависимости от значения в поле idx.

2. Указываем ENGINE = MergeTree, это означает что мы хотим, чтобы таблица была создана на основе механик LSM‑дерева.

3. И ORDER BY (idx), это означает, что мы хотим, чтобы данные в кусках были отсортированы по полю idx.

Делаем в эту таблицу три вставки:

• от 1 до 100 тысяч;

• от 50 до 200 тысяч;

• от 1 до 300 тысяч.

Как видите, там есть пересекающиеся диапазоны по полю idx. Смотрим, что получилось — вставка прошла, все данные в таблице.

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

Как видите, появилось три каталога – по одному на каждый кусок. По куску на каждую вставку.

Можно зайти также в любой из этих каталогов и посмотреть, что там лежит.

1. Бинарники. В нашем случае idx.bin и name.bin — это те самые файлы, в которых хранятся колонки с данными.

2. Два файла *.cmrk2  — файлы с засечками, которые нужны в дополнение к индексу.

3. Файл *.cidx с самими индексами.

4. Есть еще какое‑то количество метаданных. Эти файлики позволяют рассматривать кусок как целостную и самодостаточную структуру. Например, в файле default_compression_codec.txt хранятся данные про кодек, т. е. внутри одной таблицы разные куски могут быть даже сжаты по‑разному.

Еще один важный момент — иммутабельность (неизменяемость) кусков. Нельзя что‑либо менять в кусках — это принципиально. Это нужно для того, чтобы получать бонусы от режима append‑only (только добавление):

• Последовательное чтение и запись (IO), которое, как рассказывают, даже на SSD‑шках в целом быстрее, чем рандомное.

• Хорошее сжатие. Сами куски довольно здоровые, поэтому лучше сжимаются. Кроме того, хранение данных колоночное, то есть сжимаем данные одного типа — это тоже плюс к сжатию.

• Вы можете забыть про блокировки. Блокировок в ClickHouse нет, потому что нет мультиверсионности кусков. Любая операция с данными, заранее знает, что те куски, с которыми она начала работать, не изменятся в процессе работы.

Тут мы сталкиваемся с проблемой — со временем появляется много избыточных данных. Например, мы вставили одну строку по какому‑то ключу, потом эту строку хотим поменять. Но мы не можем пойти в тот кусок базы, где эта строка оказалась, и поменять там. Мы должны вставить еще одну строку (более актуальную версию), потом начать читать таблицу с конца и первое, что найдем, считать актуальным.

То же самое с удалениями: если мы удаляем строку, то не можем просто вытащить её из куска. Нужно записать еще одну строку, так называемый tombstone («надгробие»), означающую, что данные по ключу удалены. Вот мы вставили одну строку — это одна запись, потом изменили эту строку — это еще одна запись, и лежат эти две записи в разных кусках. Потом, допустим, мы хотим строку удалить — получается третья запись. И после неё мы могли бы все три строки снести, но мы этого не делаем, потому что строки лежат в разных кусках, и друг о друге не знают.

То же самое с селектами в ClickHouse. Когда мы хотим забрать данные по ключу, нам приходится пойти в каждый из этих трех кусков. Хотя, если бы строки лежали рядом, мы бы это все сделали за одно чтение. Вот поэтому вся подобная архитектура — в LSM деревьях, в том числе в ClickHouse — стремится путём слияний кусков друг с другом уменьшить, насколько возможно, их количество.

Слияния кусков

Поговорим о слияниях. Берём нашу таблицу, группируем по имени куска и считаем (count). Видим три куска. После этого мы запускаем OPTIMIZE.

Слияния происходят в фоне, ClickHouse сам определяет, в каком порядке нужно мерджить куски и как часто это делать. А с помощью OPTIMIZE можно вручную запустить внеплановое слияние.

Ожидаемо, тот же самый запрос на количество строк по кускам теперь возвращает только одну строку — в таблице теперь только один кусок, и в нем 550 тысяч строк.

Делаем SELECT из системной таблицы, где хранится информация про все куски, которые есть в базе, и обнаруживаем четыре куска в таблице, с которой работаем.

У одного из них — самого недавнего — в первой колонке выставлено active равным единице, а остальные три помечены неактивными, но ещё не удалены. Так нужно для того, чтобы успели доработать те операции, которые начали работать со старыми кусками до начала слияния. Имейте в виду, что на короткое время после слияния куски занимают x2 от первоначального объёма, так что забитый под завязку диск не очень хорошая идея для ClickHouse (да и вообще не очень хорошая).

Операции при слиянии

Момент создания нового куска — единственный подходящий, чтобы модифицировать данные, пока они снова не стали иммутабельными: например, при наличии нескольких строк по ключу можем оставить только одну, а остальные схлопнуть. За эту дополнительную логику при слияниях в ClickHouse отвечают движки семейства MergeTree. Вы обязаны указать движок, когда создаете таблицу. Вот несколько движков.

Не будем останавливаться на всех, но хочется обратить внимание на Replacing и Сollapsing. Эти два движка позволяют делать в ClickHouse точечные апдейты и делиты, о которых пойдет речь дальше. Часто с этими движками придется активно работать.

Небольшая иллюстрация. Чуть ранее с помощью OPTIMIZE мы мёрджили данные из трех кусков — вот так они выглядели перед слиянием. 

Конечно, это не все данные, а только фрагменты. Также видно, что есть пересекающиеся данные по столбцу idx. И вот что произошло, когда мы сделали мерж.

Количество строк не поменялось. И есть дублирующиеся значения по первичному ключу (тут важно сказать, что первичный ключ в ClickHouse не гарантирует уникальность, к этому тоже надо привыкнуть). С точки зрения пользователя, после этого мерджа ничего не поменялось — какие строки были, такие и остались. Но это стандартный MergeTree.

А вот что было бы, если бы мы создали ту же таблицу, но на движке Replacing MergeTree.

Мы бы оставили только по одной строке на каждое уникальное значение idx. Однако, не стоит пользоваться Replacing для хранения истории . Специальные движки MergeTree, включая Replacing, целесообразно использовать, если это позволит вам сократить количество строк на порядки. Кардинальность данных по ключу сортировки не должна быть очень высокая, и по любому из ключей однажды может появиться новое значение. Replacing подойдёт, когда вы делаете такую таблицу, где хотите хранить последнее действие для каждого пользователя.

Но если вы собираетесь хранить вообще всю историю действий всех пользователей, ситуация меняется. Replacing здесь тоже можно использовать (допустим, чтобы дедуплицировать данные по какому‑то набору полей) но не стоит рассчитывать на то, что в больших таблица слияния больших кусков или слияния OPTIMIZE‑ом будут выполняться за какое‑то разумное время.

Мутации

Любой таблице в ClickHouse иногда бывает нужна мутация. Когда вы собираетесь делать Update или Delete, вы можете воспользоваться специальными движками, но это, как вы поняли, далеко не всегда подходит. Иногда у вас есть историческая таблица (или вообще любая таблица на любом движке), в которой нужно разово удалить или обновить какие‑то строки. В таком случае вам помогут мутации.

Мутация — это длительный процесс, который, так же как и слияния, перезаписывает куски: создаёт новые, удаляет старые. Для некоторых мутаций можно указать партицию, тогда мутация затронет только куски внутри выбранной партиции.

В ClickHouse есть несколько команд, которые запускают мутации. Пригодятся все, но особенно часто — Alter Update и Alter Delete (апдейт и делит).

Что важно знать про мутации?

• Не атомарны.

• Выполняются в хронологическом порядке.

• Не блокируют друг друга.

• Не затрагивают строки, вставленные после запуска.

• Продолжат выполняться даже после рестарта.

Рассмотрим, как это работает. Вернемся к таблице, с которой начали работать, и представим, что не делали в ней никаких слияний: у нас по‑прежнему две колонки и три куска. И запускаем ALTER UPDATE, как на скриншоте ниже.

Мы хотим проапдейтить поле Name только для тех строк, где idx больше, чем 250 000. Можно представить, что это изменение должно сказаться только на одном куске, потому что только в одном куске (во втором) есть данные больше, чем 200 000. Вот что получится после выполнения мутации:

Обратите внимание, после мутации появились новые каталоги — три штуки — мутация образовала три новых куска. Это активные куски, а старые три скоро удалятся.

Можно подумать, что это не сильно лучше, чем OPTIMIZE: мы тоже перезаписали данные, просто не в один кусок, а в три. Но есть нюанс: мы делали апдейт только одной колонки.

Обратите внимание на inode до мутации и после мутации.

Inode поменялась только у той колонки, которую мы правили. ClickHouse не перезаписывает колонки, не затронутые UPDATE‑ом. Но все же мутация — это тяжелая операция и огромный объем работы. Можно проиллюстрировать на примере: вы захотите поменять имя одного пользователя «Артем» на «Артемий», а перезаписать придётся всю колонку. Поэтому область применения мутаций довольно ограничена. Плюс они не атомарны: если мутация по какой‑то причине прервется (из‑за ошибки или вы сами ее остановите), то в таблице будут как куски, которые уже мутировали, так и те, которые только должны были мутировать. Обратно никаких откатов не будет.

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

А что насчет частых вставок? В любой статье про ClickHouse будет написано, что в ClickHouse нельзя часто вставлять. Это правда, но далеко не фатально — нужно просто понимать, что происходит. Каждый раз, когда вы делаете вставку, образуется новый кусок, и если вы вставляете очень часто, то однажды попадаете в ситуацию, когда в вашей таблице новые куски образуются быстрее, чем ClickHouse успевает сливать старые. Это не приводит к резким отказам, просто начинают понемногу деградировать селекты. В один момент нужно, чтобы кто‑то сказал «хватит». И ClickHouse берет эту роль на себя: когда в вашей таблице больше 3000 кусков и вы хотите вставить 3001-й раз, ClickHouse вас останавливает. Приходится ждать, пока сольются те куски, которые уже есть. Эту дефолтную настройку можно приподнять — например, до 5000 — но, если вы безудержно вставляете, рано или поздно все равно упретесь в лимит.

Для этой проблемы тоже найдется решение. Даже в самом ClickHouse есть довольно мощные инструменты, которые позволяют организовать частые вставки. Например, относительно недавно появился async_insert — он концептуально очень похож на Memtable из LSM‑дерева. Это буфер, который держится в оперативке, туда вставляются строки (можно вставлять довольно часто), и он время от времени делает сбросы на диск. Вы не можете селектить данные из этого буфера, но строки там лежат недолго (по дефолту не дольше секунды). Раз в секунду — достаточно, чтобы ClickHouse успевал мержить куски. 

Точечные чтения

Посмотрим, как ClickHouse выполняет чтения. Перед вами фрагмент индексного файла.

В ClickHouse индексы настолько просты, что их можно отобразить.

В чем здесь особенность? Так как данные отсортированы, в индексе не нужны ссылки на каждую строку в таблице. Мы можем хранить, скажем, каждую десятую или каждую сотую строку, и индекс будет разреженным. ClickHouse хранит запись для каждой 8192-й строки. И эти индексы ссылаются уже не на строку — одна индексная запись ведет к грануле.

Гранулой называют какое‑то количество строк внутри куска данных, на которые приходится одна индексная запись. Вот как у нас выглядит EXPLAIN indexes, когда мы пытаемся точечно забрать из таблицы значения по какому‑то индексу:

ClickHouse показывает, что он собирается прочитать из таблицы две гранулы, по одной грануле из двух кусков.

Каким образом работают индексы? Они автоматически создаются для колонки, по которой отсортированы данные (секция ORDER BY при создании таблицы), и включаются каждый раз, когда в SELECT‑запросе есть фильтр WHERE по этой колонке с использованием операторов равенства (кстати, оператор IN тоже задействует индексы). Когда в SELECT‑запросе есть подходящий фильтр, ClickHouse перед тем, как начать выполнять запрос, смотрит в индексы, и понимает, что:

• есть бинарник с колонкой, нужно найти там такой‑то блок со сжатыми данными;

• его разжать;

• на таком‑то оффсете найти первую строку гранулы, которую мы будем читать;

• на таком‑то оффсете найти последнюю строку;

• их отсканировать и с ними дальше работать.

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

Так что мы читаем только две гранулы, в каждой по 8 192 строки.

Кстати, 8 192 — не гвоздями прибитое значение. Когда строки весят много (например, пишем JSON‑ы), гранула может отщёлкнуть раньше, чем наберётся 8192 строки, так как ещё есть ограничение на максимальный вес гранулы. Настройки, влияющие на размер гранулы, можно настраивать — ниже я покажу, для чего это может быть нужно.

Итак, ClickHouse клиент сообщает, что ему для выполнения запроса пришлось прочитать 16 тысяч строк, несмотря на то, что запрос вернул только две строки. Всё сходится с тем, что мы видели в EXPLAIN.

А теперь давайте попробуем сделать так:

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

Как видите, несмотря на одну строку в выводе, прочитали 16 тысяч строк, т. е. две гранулы, как и во время предыдущего запуска.

Почему так? Давайте посмотрим на индексы с первого куска, откуда мы так много записей только что удалили. Обратите внимание на третью строчку.

Третья строчка говорит про такую гранулу, в которой 3 615 строк и первое значение idx, самое маленькое, которое там есть — 96 386, а у предыдущей гранулы минимальное значение 8 193. И каждый раз, когда мы будем искать какую‑то такую строку, у которой idx будет помещаться в этот диапазон, нам придется поднимать эту гранулу, даже если там нет того, что ищем. Отсюда — много лишних чтений. 

Лишние чтения

Лишняя работа, которую вы делаете при чтениях, это не только 8000 записей из той колонки, по которой вы фильтруете.

• Из каждой колонки в SELECT‑е вы тоже прочитаете минимум по грануле.

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

• Умножьте на количество партиций — при вставке данные бьются по партициям, и в каждой партиции свои куски.

• Файлы с засечками для каждой колонки тоже нужно поднять, в лучшем случае, из кэша.

Получается в итоге значительного размера амплификация. Из‑за этого ClickHouse, судя по официальной документации, не рекомендуется использовать в качестве Key‑Value хранилища.

Но если очень нужно, то можно попробовать, потому что все не так страшно. Ваши данные, хоть вы читаете много лишнего, хранятся компактно благодаря хорошему сжатию. И, хотя вы делаете много чтений, это — последовательные чтения с диска, так что всё равно довольно быстрые. Особенно хорошо, если вы заранее знаете, какую нагрузку вы собираетесь дать на таблицу, которая будет использоваться для точечных чтений, и можете как‑то подготовиться.

1. Сделайте так, чтобы в этой таблице было поменьше кусков — за этим лучше особенно следить. Частые вставки для подобной таблицы — особенно плохо. Много партиций — тоже не супер

2. Если вы будете читать из нескольких колонок, то имеет смысл положить их рядом друг с другом (например, в один JSON). Или сделать так, чтобы куски вашей таблицы имели тип Compact (стандартный тип — Wide, но ClickHouse может хранить данные построчно, в кусках с типом Compact).

3. Имеет смысл поиграть с гранулярностью — сделать её поменьше.

4. Высший пилотаж, если сможете здесь применить skip‑индексы. Ранее мы читали гранулу, в которой не было той строки, которую мы искали, потому что не знали наверняка, что есть внутри гранулы. Skip‑индексы — это способ, при помощи которого ClickHouse может понять, что есть внутри конкретной гранулы, и избежать ненужного чтения. Например, можно потестировать Bloom filter, он реализован в виде skip‑индекса.

Еще один важный момент касательно использования индексов для точных чтений. Индекс в ClickHouse создается по тому столбцу, по которому данные отсортированы. И если у вас в какой-то таблице есть несколько идентификаторов, и вы бы хотели, допустим, как в Postgres, на эту таблицу повесить несколько индексов, чтобы иметь быстрый индексный доступ по любому из этих идентификаторов, то в ClickHouse у вас этого сделать, к сожалению, не получится. Лучшее, что вы придумаете – это хранить данные дважды, и под это потребуется много места на диске . Тут не так много вариантов: либо две независимые таблицы (связанные, например, материализованным представлением, чтобы достаточно было вставлять только в одну), либо попробовать такие вещи как проекции.

Джойны

Все говорят, что ClickHouse плохо джойнит. И у этого утверждения есть свои причины. На момент написания этой статьи ClickHouse:

• Не оптимизирует порядок JOIN‑ов.

• Не фильтрует по ключу соединения (такая оптимизация, когда значения по ключу соединения используются для индексного чтения из одной из таблиц в джойне — и нет необходимости в дополнительном IN‑е)

• Не поддерживает сравнение значений (больше, меньше) в качестве условия соединения.

• Не выбирает алгоритм JOIN‑а, основываясь на собранной статистике.

• Не обрабатывает исключения по памяти.

Рассмотрим все, но начнем с главного, с краеугольного камня всех перечисленных выше проблем — ClickHouse не обрабатывает исключения по памяти. Это правда важно: представьте, вы пытаетесь выполнить какой‑то JOIN, ваш запрос начинает выполняться, проходит какое‑то время, а потом «бац!» — ошибка ‑ClickHouse говорит, что запросу не хватило памяти. Это происходит, потому что ClickHouse хочет быть быстрым. Если какую‑то операцию он может значительно быстрее сделать, используя оперативную память, он будет пытаться сделать это бескомпромиссно. То есть у него либо получится, и запрос выполнится, либо у него не получится, и вы увидите эту самую ошибку. И эта проблема касается далеко не только одних джойнов. Это проблема для целого ряда операций.

Если у вас какой‑нибудь тяжелый длинный запрос к большой таблице, вы пишете GROUP BY или ORDER BY, то следует быть настороже, потому что такие запросы будут всегда съедать количество памяти пропорциональное, если не размеру таблицы, то кардинальности тех выражений, с которыми работают эти секции. Но есть и хорошая новость — в ClickHouse существуют настройки, которые позволяют изменить алгоритмы группировки, сортировки и джойны.

А вообще почему JOIN в ClickHouse иногда требует так много оперативной памяти? Потому что ClickHouse делает hash join. Он пытается всю правую сторону джойна целиком поместить в hash‑таблицу — отсюда и проблемы.

Немного потрогаем джойны. Создаем две таблицы, которые по своей схеме абсолютно идентичны той таблице, с которой мы начали работать.

Делаем туда две вставки: в одну таблицу вставляем два миллиарда строк, во вторую — два миллиона строк. И делаем самый обычный JOIN. У нас слева два миллиарда, справа два миллиона, забираем оттуда count, получаем два миллиона. Все правильно.

ClickHouse‑клиент говорит, что, когда мы делали джойн, потребовалось построить hash‑таблицу на два миллиона строк. Хэш‑таблица заняла 128 мегабайт (указано в байтах). А весь запрос занял 136 мегабайт.

А вот что будет, когда мы поменяем местами эти две таблицы.

И сразу ошибка: ClickHouse говорит, что ему нужно было 64 гигабайта памяти на том этапе, когда он делал FillingRightJoinSide. Как вы понимаете, это произошло именно потому, что ClickHouse всегда пытается правую сторону джойна уложить в хэш‑таблицу, и в данном случае это два миллиарда строк.

Кстати, обратите внимание, что хэш‑таблица весит больше, чем весят те самые данные, которые мы пытаемся туда положить.

Вторая таблица на два миллиарда весит 35 гигабайт, а нам потребовалось 64. Так что иногда, если ваш запрос чуть‑чуть не пролезает по памяти, имеет смысл, по возможности, в правой части джойна скастовать типы данных к менее затратным. Ещё полезно дописать к JOIN, где возможно, ключевое слово ANY — например, LEFT ANY JOIN, тогда вы будете присоединять по одной случайной строчке из правой таблицы. Хэш‑таблица по ключу сможет держать уже не массив, а одну запись, и будет весить меньше.

Главное, что можно делать с джойнами, кроме перестановки местами правой и левой части‑ это использовать IN. IN легче и быстрее, чем JOIN. А еще IN может подтянуть из таблицы индексы — то, чего JOIN сделать сам не может, даже если вы соединяете по ключу, который входит в ключ сортировки обеих таблиц.

Так что сейчас мы правую таблицу (в которой 2 миллиарда) фильтруем при помощи IN, забираем оттуда только 2 миллиона, и с точки зрения результата мы получили то же самое, но теперь запрос уже не падает.

Даже более того: он выполнился быстро — за 2 секунды, это быстрее, чем все предыдущие джойны (3 секунды). И до этапа с построением hash‑таблицы дошли 2 миллиона строк, а не 2 миллиарда.

Но не всякий JOIN можно починить перестановкой местами таблиц, использованием IN или каких‑то других фильтров, которые позволяют подвести к JOIN таблицы меньшего размера. Иногда нужно придумывать что‑то еще. Например, менять алгоритм JOIN. И ClickHouse вместо вас этого делать не будет. В ClickHouse нет Cost‑based‑оптимизатора, который может оценить ситуацию. Давайте сами попробуем побыть Cost‑based‑оптимизатором и посмотреть на JOIN, который мы делаем.

Мы же тут джойним две таблицы, которые имеют общий ключ сортировки. Данные в этих таблицах, а именно в кусках, отсортированы по полю idx, и мы джойним по полю idx. Тут напрашивается merge‑join вместо hash‑join, не правда ли? Можно читать данные из таблиц параллельно, от начала до конца, сразу же поблочно джонить и отдавать на выход. Это будет далеко не так затратно по памяти!

Если вы хотите воспользоваться этим алгоритмом, нужно дописать его в Settings, как сделано на скриншоте.

В Explain с этого момента нет ни слова про hash‑таблицы, только селект InOrder из тех источников, которые мы соединяем. Выполняем этот запрос и тратим на него всего лишь 88 мегабайт.

Шалость удалась, все получилось. Но если бы мы запороли сортировку в одном из источников, было бы сложнее. ClickHouse капризный на этот счет — очень просто испортить ему сортировку, даже на самом деле ничего не портя. Если ClickHouse решит, что данные неупорядочены, он начнёт их сортировать сам. Если такое произойдёт, вы наверняка упрётесь в ту же проблему с памятью но уже не из‑за джойна, а из‑за сортировки. Когда меняете дефолтные алгоритмы, проверяйте при помощи EXPLAIN‑а, что изменения срабатывают.

Если full_sorting_merge не подходит, можем выбрать другой алгоритм. В ClickHouse два алгоритма для merge join и три алгоритма для hash join — вы наверняка подберете что‑то подходящее. Например, здесь можно было бы использовать grace_hash. Это такой алгоритм, который говорит ClickHouse, чтобы он дробил правую часть джойна до тех пор, пока не дойдёт до такого размера, который уместится в память. И джойнил кусками этого размера, в то время как остальные будут ждать своей очереди, лёжа на диске.

Подводя итог, что же делать с проблемами джойнов в ClickHouse?

Здесь есть только один пункт, с которым ничего поделать нельзя — ClickHouse не поддерживает сравнение значений (больше‑меньше) в качестве условия соединения. Тут мы ничего сделать не можем, но добиться идентичного вывода нетрудно — просто переместите это «больше‑меньше» в секцию WHERE, и тогда сравнение выполнится уже после джойна. Кажется, это не самая критичная проблема.

Получается, ничего непоправимого нет. В ClickHouse для джоинов сейчас ручная коробка передач вместо автоматической. И это не обязательно значит, что в ClickHouse меньше возможностей. Это значит, что иногда, когда у вас сложный джойн, придется поработать руками и что‑то самому поднастроить.

JOIN‑ы в «длинных» OLAP‑запросах — всегда неоптимальный вариант. Это касается любой СУБД, не только ClickHouse. Лучше, когда данные сразу хранятся в денормализованных таблицах со всеми полями, которые понадобятся для группировок и фильтров. Если не получается сразу, то нормально обогащать данные один раз, по мере их поступления, хотя для этого уже потребуется оркестратор. JOIN‑ы приносят радость либо в «коротких» запросах, либо поверх уже агрегированных данных.

Если ваши признаковые таблицы небольшие и помещаются в память, из них можно сделать словарь (таблица на движке Dictionary), и вместо джойнов пользоваться функцией dictGet, которая работает супер‑быстро.

Насчет упомянутого сравнения «больше‑меньше», есть словари с секцией RANGE, которые позволяют забрать значение по ключу, учитывая его актуальность. И туда же относится ASOF JOIN — это единственный джойн в ClickHouse, который поддерживает сравнение. У него довольно ограниченная применимость, но иногда он очень полезен.

В итоге, если вам потребуется JOIN — вы его сделаете.

Заключение

Единственное, про что не упомянуто в материале — отсутствие транзакций. И это даже не стереотип, а факт. В ClickHouse транзакций нет. ClickHouse одна из таких баз, которые жертвуют ACID и гибкостью при моделировании схемы ДВХ для того, чтобы дать вам какие‑то другие плюшки. И в ClickHouse этих плюшек достаточно. Например:

• В ClickHouse отличный SQL‑диалект, к которому вы очень быстро привыкаете, как только начинаете с ним работать и потом не хотите от него отказываться.

• В ClickHouse есть большое количество предметных функций, которые позволяют работать со статистикой, с вероятностями, URL‑ами, массивами, машинным обучением, полнотекстовым поиском и т. д.

• ClickHouse хорошо масштабируется, притом как горизонтально, так и вертикально.

• В ClickHouse потрясающая скорость работы с аналитическими SQL‑запросами!

Если для вас среди упомянутых ограничений нет критичных, то советую попробовать ClickHouse!