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

Операторы IN

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

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

Примеры:

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

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

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

Правая сторона оператора может быть множеством константных выражений, множеством кортежей с константными выражениями (показанными в примерах выше) или названием таблицы базы данных или подзапросом 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 полностью находились на одном сервере. В этом случае все необходимые данные будут доступны локально на каждом сервере. В противном случае результат будет неточным. Мы называем эту вариацию запроса "локальный 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

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

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