JOIN

Джойним эффективно

Выбираем правильный джоин

Помимо основных типов джойнов, в кликхаус есть ещё дополнительные типы. Обсудим два из них — SEMI JOIN и ANY JOIN:

• LEFT (RIGHT) SEMI JOIN

  • Как LEFT JOIN, но оставляет только те значения из левой таблицы, у которых есть совпадения в правой таблице. При этом возвращается только первое найденное совпадение.

• LEFT (RIGHT) ANY JOIN

  • Как LEFT JOIN, но из правой таблицы берет только первое совпадение. Если совпадений нет, всё равно вернёт строку из левой таблицы.

Самый главный способ оптимизировать запрос при выполнении джоинов — выбрать правильный джоин. В общем случае можно расположить джоины в следующем порядке от менее производительного к более производительному:

• CROSS JOIN — декартово произведение таблиц это всегда самая дорогая операция.

• LEFT (RIGHT) OUTER JOIN — команду OUTER можно опускать и писать просто LEFT JOIN.

• LEFT (RIGHT) ANY JOIN — лучше использовать его вместо LEFT JOIN, если нас устраивает присоединение только одного первого вхождения из второй таблицы.

• INNER JOIN — используется по умолчанию, если указать просто JOIN в запросе.

• LEFT (RIGHT) SEMI JOIN — самая дешёвая операция, т.к. мы не просто возвращаем пересечение таблиц, но и берём только первое из них.

Используем подзапросы

Сравним два запроса. Первый — где мы фильтруем строки после джоина:

SELECT
    ar.available_resource_id,
    ar.available_resource_title,
    arp.procedure_id,
    arp.cur as cur_ar_serv,
    ar.cur as cur_ar
FROM
    dm_ap2.dct_available_resources ar
JOIN dm_ap2.crs_available_resource_to_procedure arp
ON arp.available_resource_id = ar.available_resource_id
WHERE cur_ar_serv = True

Второй — где мы фильтруем строки второй таблицы ДО джоина:

SELECT
    ar.available_resource_id,
    ar.available_resource_title,
    arp.procedure_id,
    arp.cur as cur_ar_serv,
    ar.cur as cur_ar
FROM
    dm_ap2.dct_available_resources ar
JOIN (
    SELECT
        procedure_id,
        available_resource_id,
        cur
    FROM
        dm_ap2.crs_available_resource_to_procedure
    WHERE cur = True
) arp
ON arp.available_resource_id = ar.available_resource_id

Результаты запросов аналогичны, но в первом случае мы читаем обе таблицы, соединяем их и только потом фильтруем. А во втором случае мы сначала фильтруем вторую таблицу перед соединением, а затем соединяем. Второй запрос будет производительнее первого. Это не удастся продемонстрировать из-за внутренней оптимизации кликхаус, но все равно подзапросы нужно использовать, потому что:

• Правило работает и для других СУБД и вам наверняка предстоит работать не только с ClickHouse

• При джоине нескольких таблиц оптимизация не всегда будет срабатывать

В общем случае правило можно сформулировать так — старайтесь уменьшить вторую таблицу перед соединением, насколько это возможно.

Выводы

• Выбирайте правильный тип JOIN

• Используйте подзапросы для уменьшения размера присоединяемой таблицы