Операторы 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. Однако имейте в виду следующие пункты:
- При создании временной таблицы данные не становятся уникальными. Чтобы уменьшить объем данных, передаваемых по сети, укажите DISTINCT в подзапросе. (Не нужно делать это для обычного
IN.) - Временная таблица будет отправлена на все удаленные серверы. Передача не учитывает топологию сети. Например, если 10 удаленных серверов расположены в датацентре, сильно удаленном от сервера запрашивающего, данные будут отправлены 10 раз по каналу до удаленного датацентра. Пытайтесь избегать больших наборов данных при использовании
GLOBAL IN. - При передаче данных на удаленные серверы ограничения по пропускной способности сети не конфигурируемы. Вы можете перегрузить сеть.
- Старайтесь распределять данные по серверам так, чтобы вам не пришлось использовать
GLOBAL INна регулярной основе. - Если вам часто нужно использовать
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, которые могут обеспечивать различное и более оптимальное поведение.