Что быстрее inner join или left join
Перейти к содержимому

Что быстрее inner join или left join

  • автор:

Можно утверждать что INNER JOIN всегда быстрее LEFT JOIN?

Даже не буду пытаться на это отвечать, потому что всё написано до нас ещё 12 лет назад.

Darth_Revan ★★★★★
( 09.02.22 22:05:50 MSK )

Я не проверял, но полагаю, что да. Хотя по идее если и быстрей, то не намного.

Legioner ★★★★★
( 09.02.22 22:19:20 MSK )
Ответ на: комментарий от Legioner 09.02.22 22:19:20 MSK

На пустых таблицах может оказаться и иначе, граничные случаи тоже важны 😉

sislochka
( 10.02.22 10:02:35 MSK )

Утверждать такое точно нельзя, как минимум потому что они могут выполняться абсолютно одинаково, например когда вообще нет null значений. Можно было наивно предположить что он выполняется не медленнее, но и это в общем случае неверно.

Джойны можно выполнять кучей разных способов, способы эти планировщик выбирает по прикидкам, и всегда есть случаи когда он ошибётся. Например, из-за наличия большого количества null значений выберет nested loop с fullscan по левой таблице вместо, например, index scan, что теоретически медленнее, но на практике может оказаться быстрее на небольших таблицах, потому что эффективнее последовательно (а может и параллельно) прочитать несколько страниц таблицы, чем рандомно лазить по индексам. Может быть и наоборот, планировщик решит что эффективнее fullscan, потому что записей мало, но на деле в таблице в тысячу раз больше мёртвых строк до которых не успел добраться vacuum.

Причём для двух абсолютно одинаковых запросов идущих друг за другом при отсутствии другой нагрузки может отличаться как поведение планировщика (из-за обновления статистики), так и фактическая производительность (из-за наполнения страничного кэша), причём в итоге в любую сторону.

slovazap ★★★★★
( 10.02.22 16:04:33 MSK )
Последнее исправление: slovazap 10.02.22 16:11:54 MSK (всего исправлений: 3)

Вы не можете добавлять комментарии в эту тему. Тема перемещена в архив.

Похожие темы

  • Форум [MySQL, LEFT JOIN] Запутался (2011)
  • Форум MySQL: Странное поведение left join (2017)
  • Форум LEFT JOIN, NULL и where (2006)
  • Форум left join для csv на bash (2019)
  • Форум sqlite3 left self-join, только наоборот (2023)
  • Форум left outer join и проблемы с JPQL. (2019)
  • Форум быстрый Nested Loop и медленный Merge join (2021)
  • Форум multicast join (2004)
  • Форум latex ( -> \left( (2019)
  • Форум Инверсный обзор мышью left/right в Quake 2 (2013)

Различие INNER, LEFT, RIGHT, FULL JOIN в MySQL

INNER JOIN выбирает строки, удовлетворяющие условию объединения из обеих таблиц. LEFT JOIN возвращает все строки из левой таблицы и соответствующие строки из правой. Если совпадения не обнаружены, возвращается NULL . RIGHT JOIN выводит все строки из правой таблицы и строки из левой, удовлетворяющие условию объединения. Если совпадения не находятся, вместо них проставляется NULL . FULL JOIN включает в себя строки из обеих таблиц, показывая все возможные совпадения. В случае отсутствия соответствия возвращается NULL .

Рассмотрим таблицы A и B :

A: 1 2 3 B: 3 4 5 INNER: 3 LEFT: 1 2 3(NO MATCH) RIGHT: (NO MATCH)3 4 5 FULL: 1 2 3(NO MATCH) 4 5

Примеры запросов на SQL:

Скопировать код

-- Inner: Строки, которые присутствуют в обеих таблицах SELECT * FROM A INNER JOIN B ON A.key = B.key; -- Left: Все строки из таблицы A, а также совпадающие строки из B. Если соответствий нет, возвращается NULL SELECT * FROM A LEFT JOIN B ON A.key = B.key; -- Right: Все строки из таблицы B, плюс подходящие строки из A. Если совпадений нет, возвращается NULL SELECT * FROM A RIGHT JOIN B ON A.key = B.key; -- Full: Все строки из A и B. Если нет пары в одной из таблиц, возвращается NULL SELECT * FROM A FULL JOIN B ON A.key = B.key;

Выбор типа соединения зависит от ваших потребностей — требуется ли в результате видеть строки без пар из одной из таблиц.

Понимание JOIN: Практические инсайты

Важность порядка: OUTER JOIN При использовании OUTER JOIN (LEFT, RIGHT или FULL) важно учесть порядок указания таблиц — первая таблица определяет приоритет вывода строк.

Магия оптимизации: Что происходит за кулисами Системы управления базами данных часто преобразуют RIGHT JOIN в LEFT JOIN , чтобы оптимизировать производительность запросов. Учтите это при отладке.

Обращение внимания на NULL в OUTER JOIN Если OUTER JOIN не находит соответствия, он использует NULL для заполнения пустот. Это следует учитывать, так как NULL может влиять на интерпретацию результатов запроса.

Визуализация

Представьте себе два набора элементов: одни с синими краями (��), другие — с желтыми (��):

Что быстрее: where или join?

Начнем с того, что, грубо говоря, «where» и «join» — не то же самое. Предложение where накладывает на выборку некое условие, а join — объединяет данные некоторых таблиц по некоторым условиям. Но если свести задачу к выводу данных из двух (или более) таблиц по условию, то результат будет в обоих случаях окажется идентичным. Сегодня хотелось бы выяснить, какую конструкцию использовать оптимальнее в данном случае.

Для теста буду использовать все те же таблицы из записи Оптимизация запросов PostgreSQL с explain analyze. В таблицу phonebook добавим новое поле phone_number (номер телефона):

ALTER TABLE public.phonebook ADD COLUMN phone_number text;

Задание: вывести номера телефонов и имена людей. Для первого теста количество записей в таблице people — 100 000. В таблице phonebook — 50 000. Для второго теста — 1 000 000 в одной, и 900 000 в другой таблице, соответственно. Ключ human_id все так же может быть неуникальным.

Тест 1
explain analyze select b.human_id, a.name, b.phone_number from people a, phonebook b where a.id = b.human_id Hash Join (cost=4063.98..7422.22 rows=50000 width=78) (actual time=53.831..137.790 rows=50000 loops=1) Hash Cond: (b.human_id = a.id) - Seq Scan on phonebook b (cost=0.00..968.00 rows=50000 width=41) (actual time=0.014..6.928 rows=50000 loops=1) - Hash (cost=1934.99..1934.99 rows=99999 width=41) (actual time=53.208..53.208 rows=100000 loops=1) Buckets: 65536 Batches: 4 Memory Usage: 2381kB - Seq Scan on people a (cost=0.00..1934.99 rows=99999 width=41) (actual time=0.008..20.413 rows=100000 loops=1) Planning time: 0.247 ms Execution time: 139.024 ms
explain analyze select b.human_id, a.name, b.phone_number from people a inner join phonebook b on a.id = b.human_id Hash Join (cost=4063.98..7422.22 rows=50000 width=78) (actual time=36.378..170.474 rows=50000 loops=1) Hash Cond: (b.human_id = a.id) - Seq Scan on phonebook b (cost=0.00..968.00 rows=50000 width=41) (actual time=0.010..10.994 rows=50000 loops=1) - Hash (cost=1934.99..1934.99 rows=99999 width=41) (actual time=35.775..35.775 rows=100000 loops=1) Buckets: 65536 Batches: 4 Memory Usage: 2381kB - Seq Scan on people a (cost=0.00..1934.99 rows=99999 width=41) (actual time=0.008..13.776 rows=100000 loops=1) Planning time: 0.178 ms Execution time: 172.356 ms

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

Тест 2
explain analyze select b.human_id, a.name, b.phone_number from people a, phonebook b where a.id = b.human_id Hash Join (cost=40636.00..94022.49 rows=900000 width=78) (actual time=912.484..2362.599 rows=900000 loops=1) Hash Cond: (b.human_id = a.id) - Seq Scan on phonebook b (cost=0.00..17412.00 rows=900000 width=41) (actual time=0.037..401.818 rows=900000 loops=1) - Hash (cost=19346.00..19346.00 rows=1000000 width=41) (actual time=909.446..909.446 rows=1000000 loops=1) Buckets: 65536 Batches: 32 Memory Usage: 2840kB - Seq Scan on people a (cost=0.00..19346.00 rows=1000000 width=41) (actual time=0.014..429.818 rows=1000000 loops=1) Planning time: 28.119 ms Execution time: 2383.102 ms
explain analyze select b.human_id, a.name, b.phone_number from people a inner join phonebook b on a.id = b.human_id Hash Join (cost=40636.00..94022.49 rows=900000 width=78) (actual time=469.435..1641.759 rows=900000 loops=1) Hash Cond: (b.human_id = a.id) - Seq Scan on phonebook b (cost=0.00..17412.00 rows=900000 width=41) (actual time=0.008..157.313 rows=900000 loops=1) - Hash (cost=19346.00..19346.00 rows=1000000 width=41) (actual time=467.118..467.118 rows=1000000 loops=1) Buckets: 65536 Batches: 32 Memory Usage: 2840kB -> Seq Scan on people a (cost=0.00..19346.00 rows=1000000 width=41) (actual time=0.009..165.728 rows=1000000 loops=1) Planning time: 0.186 ms Execution time: 1660.800 ms

Вывод объединенных данных через inner join по условию работает быстрее, на выборку первой записи на всех этапах построения запроса ему требуется меньше времени, чем запросу по условию where, хотя планы выполнения запросов одинаковые.

Поделиться ссылкой:

  • Послать ссылку другу по электронной почте (Открывается в новом окне)
  • Нажмите для печати (Открывается в новом окне)
  • Нажмите, чтобы поделиться в WhatsApp (Открывается в новом окне)
  • Нажмите, чтобы открыть на Facebook (Открывается в новом окне)
  • Нажмите, чтобы поделиться на Twitter (Открывается в новом окне)
  • Нажмите, чтобы поделиться записями на Pinterest (Открывается в новом окне)
  • Нажмите, чтобы поделиться в Telegram (Открывается в новом окне)
  • Нажмите, чтобы поделиться на LinkedIn (Открывается в новом окне)

LEFT JOIN быстрее INNER JOIN?

Продолжаю заниматься оптимизацией запросов и заметил такую штуку — уже на 5-м запросе типа SELECT замена INNER на LEFT ускоряет запрос как миниму в 2 раза, а то и в 10. Почему так происходит? Вот пример — время выполнения 4 сек.

SELECT t1.id, t1.created, t1.description, t2.title,
CONCAT_WS(‘-‘, t2.id, t2.alias) AS item_alias,
CONCAT_WS(‘-‘, t3.id, t3.alias) AS category_alias,
CONCAT_WS(‘-‘, t4.id, t4.alias) AS section_alias
FROM table1 AS t1
INNER JOIN table2 AS t2 ON t1.object_id = t2.id AND t1.object_group = ‘com_group’
LEFT JOIN table3 AS t3 ON t2.category_id = t3.id
LEFT JOIN table4 AS t4 ON t2.section_id = t4.id
ORDER BY t1.id DESC LIMIT 3;

Меняем INNER на LEFT — результат запроса остается тот же, а время сокращается до 0,005

SELECT t1.id, t1.created, t1.description, t2.title,
CONCAT_WS(‘-‘, t2.id, t2.alias) AS item_alias,
CONCAT_WS(‘-‘, t3.id, t3.alias) AS category_alias,
CONCAT_WS(‘-‘, t4.id, t4.alias) AS section_alias
FROM table1 AS t1
LEFT JOIN table2 AS t2 ON t1.object_id = t2.id AND t1.object_group = ‘com_group’
LEFT JOIN table3 AS t3 ON t2.category_id = t3.id
LEFT JOIN table4 AS t4 ON t2.section_id = t4.id
ORDER BY t1.id DESC LIMIT 3;

jpg 166854.jpg
На сайте с 25.11.2006
23 сентября 2017, 09:34

✅ Мой Телеграм канал по SEO, оптимизации сайтов и серверов: https://t.me/leonidukgLIVE
На сайте с 04.02.2005
23 сентября 2017, 09:51
Dram:
Меняем INNER на LEFT — результат запроса остается тот же, а время сокращается до 0,005
Но у вас и результат должен быть разный 1-й запрос != 2-му запросу


SELECT t1.id, t1.created, t1.description, t2.title,
CONCAT_WS('-', t2.id, t2.alias) AS item_alias,
CONCAT_WS('-', t3.id, t3.alias) AS category_alias,
CONCAT_WS('-', t4.id, t4.alias) AS section_alias
FROM table1 AS t1
LEFT JOIN table2 AS t2 ON t1.object_id = t2.id AND t1.object_group = 'com_group'
LEFT JOIN table3 AS t3 ON t2.category_id = t3.id
LEFT JOIN table4 AS t4 ON t2.section_id = t4.id
ORDER BY t1.id DESC LIMIT 3;

Не стесняйтесь — показывайте реальные названия таблиц и колонок Так проще понять и связи и остальное Потому что первый вообще какой-то «левый»


SELECT t1.id, t1.created, t1.description, t2.title,
CONCAT_WS('-', t2.id, t2.alias) AS item_alias,
CONCAT_WS('-', t3.id, t3.alias) AS category_alias,
CONCAT_WS('-', t4.id, t4.alias) AS section_alias
FROM table1 AS t1
INNER JOIN table2 AS t2 ON t1.object_id = t2.id AND t1.object_group = 'com_group'
LEFT JOIN table3 AS t3 ON t2.category_id = t3.id
LEFT JOIN table4 AS t4 ON t2.section_id = t4.id
ORDER BY t1.id DESC LIMIT 3;

за счет INNER JOIN table2 AS t2 ON t1.object_id = t2.id AND t1.object_group = ‘com_group’ ЗАчем? если t1.object_group не в видимости t2 Может Вам нужен


SELECT t1.id, t1.created, t1.description, t2.title,
CONCAT_WS('-', t2.id, t2.alias) AS item_alias,
CONCAT_WS('-', t3.id, t3.alias) AS category_alias,
CONCAT_WS('-', t4.id, t4.alias) AS section_alias
FROM table1 AS t1
INNER JOIN table2 AS t2 ON t1.object_id = t2.id'
LEFT JOIN table3 AS t3 ON t2.category_id = t3.id
LEFT JOIN table4 AS t4 ON t2.section_id = t4.id
WHERE t1.object_group = 'com_group
ORDER BY t1.id DESC LIMIT 3;

LEOnidUKG, Но это никак не объясняет разницы в быстродействии

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *