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

Операторы 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 (подготовленный набор данных, постоянно находящийся в оперативной памяти), этот набор данных не будет создаваться заново для каждого запроса.

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

Пример:

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. Они различаются тем, как они выполняются при распределённой обработке запросов.

Примечание

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

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

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

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

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 в случае, если эта локальная таблица доступна только на сервере-инициаторе запроса, а вы хотите использовать данные из неё на удалённых серверах.

Распределённые подзапросы и 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>)

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