Операторы IN

Операторы IN, NOT IN, GLOBAL IN и GLOBAL NOT IN рассматриваются отдельно, поскольку их функциональность довольно разнообразна.

Левая сторона оператора может быть либо одной колонкой, либо кортежем.

Примеры:

Если левая сторона — это одна колонка, которая находится в индексе, а правая сторона — это набор констант, система использует индекс для обработки запроса.

Не перечисляйте слишком много значений явно (т.е. миллионы). Если набор данных большой, поместите его во временную таблицу (например, см. раздел Внешние данные для обработки запросов), а затем используйте подзапрос.

Правая сторона оператора может быть набором константных выражений, набором кортежей с константными выражениями (как показано в приведенных выше примерах) или названием таблицы базы данных или подзапросом SELECT в скобках.

ClickHouse позволяет типам различаться на левой и правой частях подзапроса IN. В этом случае он преобразует значение левой стороны в тип правой стороны, как если бы применялась функция accurateCastOrNull. Это означает, что тип данных становится Nullable, и если преобразование невозможно, возвращается NULL.

Пример

Запрос:

Результат:

Если правая сторона оператора — это название таблицы (например, UserID IN users), это эквивалентно подзапросу UserID IN (SELECT * FROM users). Используйте это при работе с внешними данными, которые отправляются вместе с запросом. Например, запрос может быть отправлен вместе с набором идентификаторов пользователей, загруженных во временную таблицу 'users', которую следует отфильтровать.

Если правая сторона оператора — это название таблицы, у которой есть движок Set (подготовленный набор данных, который всегда находится в ОЗУ), набор данных не будет создан заново для каждого запроса.

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

Пример:

Колонки слева и справа от оператора IN должны иметь одинаковый тип.

Оператор IN и подзапрос могут возникать в любой части запроса, включая агрегатные функции и лямбда-функции. Пример:

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

Обработка NULL

При обработке запроса оператор IN предполагает, что результат операции с NULL всегда равен 0, независимо от того, находится ли NULL справа или слева от оператора. Значения NULL не включаются в какой-либо набор данных, не соответствуют друг другу и не могут быть сравнены, если transform_null_in = 0.

Вот пример с таблицей t_null:

Запуск запроса SELECT x FROM t_null WHERE y IN (NULL,3) дает вам следующий результат:

Вы можете видеть, что строка, в которой y = NULL, отброшена из результатов запроса. Это происходит потому, что ClickHouse не может решить, включен ли NULL в набор (NULL,3), возвращает 0 в качестве результата операции, и SELECT исключает эту строку из финального вывода.

Распределенные подзапросы

Существует два варианта операторов IN с подзапросами (аналогично операторам JOIN): обычные IN / JOIN и GLOBAL IN / GLOBAL JOIN. Они различаются тем, как выполняются для распределенной обработки запросов.

примечание

Помните, что описанные ниже алгоритмы могут работать по-разному в зависимости от настройки distributed_product_mode.

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

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

Для нераспределенного запроса используйте обычный IN / JOIN.

Будьте осторожны при использовании подзапросов в условиях IN / JOIN для распределенной обработки запросов.

Рассмотрим несколько примеров. Предположим, что каждый сервер в кластере имеет обычную local_table. У каждого сервера также есть таблица distributed_table с типом Distributed, которая обращается ко всем серверам в кластере.

Для запроса к distributed_table запрос будет отправлен на все удаленные серверы и выполнен на них с использованием local_table.

Например, запрос

будет отправлен на все удаленные серверы как

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

Теперь рассмотрим запрос с IN:

• Расчет пересечения аудиторий двух сайтов.

Этот запрос будет отправлен на все удаленные серверы как

Иными словами, набор данных в условии IN будет собран на каждом сервере независимо, только по данным, которые хранятся локально на каждом из серверов.

Это будет работать правильно и оптимально, если вы готовы к этому случаю и распределили данные по серверам кластера так, чтобы данные для единственного UserID полностью находились на одном сервере. В этом случае все необходимые данные будут доступны локально на каждом сервере. В противном случае результат будет неточным. Мы называем этот вариант запроса "локальным IN".

Чтобы исправить то, как работает запрос, когда данные распределены случайным образом по серверам кластера, вы можете указать distributed_table внутри подзапроса. Запрос будет выглядеть так:

Этот запрос будет отправлен на все удаленные серверы как

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

Например, если у вас есть кластер из 100 серверов, выполнение всего запроса потребует 10,000 элементарных запросов, что в целом считается неприемлемым.

В таких случаях вы всегда должны использовать GLOBAL IN вместо IN. Давайте посмотрим, как это работает для запроса:

Запрашивающий сервер выполнит подзапрос:

и результат будет помещен во временную таблицу в ОЗУ. Затем запрос будет отправлен на каждый удаленный сервер как:

Временная таблица _data1 будет отправлена на каждый удаленный сервер с запросом (название временной таблицы определяется реализацией).

Это более оптимально, чем использование обычного IN. Однако учитывайте следующие моменты:

1. При создании временной таблицы данные не уникализируются. Чтобы уменьшить объем передаваемых данных по сети, укажите DISTINCT в подзапросе. (Не нужно делать это для обычного IN).
2. Временная таблица будет отправлена на все удаленные серверы. Передача не учитывает топологию сети. Например, если 10 удаленных серверов находятся в дата-центре, который очень далеко от запрашивающего сервера, данные будут отправлены 10 раз по каналу в удаленный дата-центр. Старайтесь избегать больших наборов данных при использовании GLOBAL IN.
3. При передаче данных на удаленные серверы ограничения на пропускную способность сети не настраиваются. Вы можете перегрузить сеть.
4. Старайтесь распределять данные по серверам так, чтобы вам не приходилось регулярно использовать GLOBAL IN.
5. Если вам часто нужно использовать GLOBAL IN, планируйте расположение кластера ClickHouse так, чтобы одна группа реплик находилась не более чем в одном дата-центре с быстрой сетью между ними, чтобы запрос мог обрабатываться полностью в одном дата-центре.

Также имеет смысл указывать локальную таблицу в условии GLOBAL IN, если эта локальная таблица доступна только на запрашивающем сервере, и вы хотите использовать из нее данные на удаленных серверах.

Распределенные подзапросы и max_rows_in_set

Вы можете использовать max_rows_in_set и max_bytes_in_set для контроля объема передаваемых данных при распределенных запросах.

Это особенно важно, если запрос GLOBAL IN возвращает большой объем данных. Рассмотрим следующий SQL:

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

Распределенные подзапросы и max_parallel_replicas

Когда max_parallel_replicas больше 1, распределенные запросы дополнительно трансформируются.

Например, следующее:

преобразуется на каждом сервере в:

где M — это значение от 1 до 3 в зависимости от того, на какой реплике выполняется локальный запрос.

Эти настройки влияют на каждую таблицу из семьи MergeTree в запросе и имеют тот же эффект, что и применение SAMPLE 1/3 OFFSET (M-1)/3 к каждой таблице.

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

Одним из обходных методов, если local_table_2 не соответствует требованиям, является использование GLOBAL IN или GLOBAL JOIN.

Если у таблицы нет ключа выборки, можно использовать более гибкие варианты для parallel_replicas_custom_key, которые могут привести к различному и более оптимальному поведению.