Перейти к основному содержанию
Перейти к основному содержанию

Операторы IN

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

Левая часть оператора — это либо один столбец, либо кортеж.

Примеры:

SELECT UserID IN (123, 456) FROM ...
SELECT (CounterID, UserID) IN ((34, 123), (101500, 456)) FROM ...

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

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

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

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

Это означает, что тип данных становится Nullable, и если преобразование невозможно выполнить, возвращается значение NULL.

Пример

Запрос:

SELECT '1' IN (SELECT 1);

Результат:

┌─in('1', _subquery49)─┐
│                    1 │
└──────────────────────┘

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

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

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

Пример:

SELECT (CounterID, UserID) IN (SELECT CounterID, UserID FROM ...) FROM ...

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

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

SELECT
    EventDate,
    avg(UserID IN
    (
        SELECT UserID
        FROM test.hits
        WHERE EventDate = toDate('2014-03-17')
    )) AS ratio
FROM test.hits
GROUP BY EventDate
ORDER BY EventDate ASC

┌──EventDate─┬────ratio─┐
│ 2014-03-17 │        1 │
│ 2014-03-18 │ 0.807696 │
│ 2014-03-19 │ 0.755406 │
│ 2014-03-20 │ 0.723218 │
│ 2014-03-21 │ 0.697021 │
│ 2014-03-22 │ 0.647851 │
│ 2014-03-23 │ 0.648416 │
└────────────┴──────────┘

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

Обработка NULL​

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

Ниже приведен пример с таблицей t_null:

┌─x─┬────y─┐
│ 1 │ ᴺᵁᴸᴸ │
│ 2 │    3 │
└───┴──────┘

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

┌─x─┐
│ 2 │
└───┘

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

SELECT y IN (NULL, 3)
FROM t_null

┌─in(y, tuple(NULL, 3))─┐
│                     0 │
│                     1 │
└───────────────────────┘

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

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

:::note
Имейте в виду, что описанные ниже алгоритмы могут работать по-разному в зависимости от значения настройки 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.

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

SELECT uniq(UserID) FROM distributed_table

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

SELECT uniq(UserID) FROM local_table

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

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

SELECT uniq(UserID) FROM distributed_table WHERE CounterID = 101500 AND UserID IN (SELECT UserID FROM local_table WHERE CounterID = 34)
  • Расчёт пересечения аудиторий двух сайтов.

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

SELECT uniq(UserID) FROM local_table WHERE CounterID = 101500 AND UserID IN (SELECT UserID FROM local_table WHERE CounterID = 34)

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

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

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

SELECT uniq(UserID) FROM distributed_table WHERE CounterID = 101500 AND UserID IN (SELECT UserID FROM distributed_table WHERE CounterID = 34)

Этот запрос будет отправлен на все удалённые серверы в следующем виде:

SELECT uniq(UserID) FROM local_table WHERE CounterID = 101500 AND UserID IN (SELECT UserID FROM distributed_table WHERE CounterID = 34)

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

SELECT UserID FROM local_table WHERE CounterID = 34

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

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

SELECT uniq(UserID) FROM distributed_table WHERE CounterID = 101500 AND UserID GLOBAL IN (SELECT UserID FROM distributed_table WHERE CounterID = 34)

Сервер, инициировавший запрос, выполнит подзапрос:

SELECT UserID FROM distributed_table WHERE CounterID = 34

и результат будет помещён во временную таблицу в оперативной памяти (RAM). Затем запрос будет отправлен на каждый удалённый сервер в следующем виде:

SELECT uniq(UserID) FROM local_table WHERE CounterID = 101500 AND UserID GLOBAL IN _data1

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

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

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

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

Distributed Subqueries and max_rows_in_set​

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

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

SELECT * FROM table1 WHERE col1 GLOBAL IN (SELECT col1 FROM table2 WHERE <некий_предикат>)

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

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

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

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

SELECT CounterID, count() FROM distributed_table_1 WHERE UserID IN (SELECT UserID FROM local_table_2 WHERE CounterID < 100)
SETTINGS max_parallel_replicas=3

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

SELECT CounterID, count() FROM local_table_1 WHERE UserID IN (SELECT UserID FROM local_table_2 WHERE CounterID < 100)
SETTINGS parallel_replicas_count=3, parallel_replicas_offset=M

где 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, которые могут обеспечить другое, более оптимальное поведение.