Можно утверждать что 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)
Понимание джойнов сломано. Это точно не пересечение кругов, честно
Так получилось, что я провожу довольно много собеседований на должность веб-программиста. Один из обязательных вопросов, который я задаю — это чем отличается INNER JOIN от LEFT JOIN.
Чаще всего ответ примерно такой: «inner join — это как бы пересечение множеств, т.е. остается только то, что есть в обеих таблицах, а left join — это когда левая таблица остается без изменений, а от правой добавляется пересечение множеств. Для всех остальных строк добавляется null». Еще, бывает, рисуют пересекающиеся круги.
Я так устал от этих ответов с пересечениями множеств и кругов, что даже перестал поправлять людей.
Дело в том, что этот ответ в общем случае неверен. Ну или, как минимум, не точен.
Давайте рассмотрим почему, и заодно затронем еще парочку тонкостей join-ов.
Во-первых, таблица — это вообще не множество. По математическому определению, во множестве все элементы уникальны, не повторяются, а в таблицах в общем случае это вообще-то не так. Вторая беда, что термин «пересечение» только путает.
(Update. В комментах идут жаркие споры о теории множеств и уникальности. Очень интересно, много нового узнал, спасибо)
INNER JOIN
Давайте сразу пример.
Итак, создадим две одинаковых таблицы с одной колонкой id, в каждой из этих таблиц пусть будет по две строки со значением 1 и еще что-нибудь.
INSERT INTO table1 (id) VALUES (1), (1) (3); INSERT INTO table2 (id) VALUES (1), (1), (2);Давайте, их, что ли, поджойним
SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id;Если бы это было «пересечение множеств», или хотя бы «пересечение таблиц», то мы бы увидели две строки с единицами.
На практике ответ будет такой:
| id | id | | --- | --- | | 1 | 1 | | 1 | 1 | | 1 | 1 | | 1 | 1 |
Для начала рассмотрим, что такое CROSS JOIN. Вдруг кто-то не в курсе.
CROSS JOIN — это просто все возможные комбинации соединения строк двух таблиц. Например, есть две таблицы, в одной из них 3 строки, в другой — 2:
select * from t1;id ---- 1 2 3
select * from t2;id ---- 4 5
Тогда CROSS JOIN будет порождать 6 строк.
select * from t1 cross join t2; id | id ----+---- 1 | 4 1 | 5 2 | 4 2 | 5 3 | 4 3 | 5
Так вот, вернемся к нашим баранам.
Конструкция
t1 INNER JOIN t2 ON condition— это, можно сказать, всего лишь синтаксический сахар к
t1 CROSS JOIN t2 WHERE conditionТ.е. по сути INNER JOIN — это все комбинации соединений строк с неким фильтром condition . В общем-то, можно это представлять по разному, кому как удобнее, но точно не как пересечение каких-то там кругов.
Небольшой disclaimer: хотя inner join логически эквивалентен cross join с фильтром, это не значит, что база будет делать именно так, в тупую: генерить все комбинации и фильтровать. На самом деле там более интересные алгоритмы.
LEFT JOIN
Если вы считаете, что левая таблица всегда остается неизменной, а к ней присоединяется или значение из правой таблицы или null, то это в общем случае не так, а именно в случае когда есть повторы данных.
Опять же, создадим две таблицы:
insert into t1 (id) values (1), (1), (3); insert into t2 (id) values (1), (1), (4), (5);Теперь сделаем LEFT JOIN:
SELECT * FROM t1 LEFT JOIN t2 ON t1.id = t2.id;Результат будет содержать 5 строк, а не по количеству строк в левой таблице, как думают очень многие.
| id | id | | --- | --- | | 1 | 1 | | 1 | 1 | | 1 | 1 | | 1 | 1 | | 3 | |
Так что, LEFT JOIN — это тоже самое что и INNER JOIN (т.е. все комбинации соединений строк, отфильтрованных по какому-то условию), и плюс еще записи из левой таблицы, для которых в правой по этому фильтру ничего не совпало.
LEFT JOIN можно переформулировать так:
SELECT * FROM t1 CROSS JOIN t2 WHERE t1.id = t2.id UNION ALL SELECT t1.id, null FROM t1 WHERE NOT EXISTS ( SELECT FROM t2 WHERE t2.id = t1.id )Сложноватое объяснение, но что поделать, зато оно правдивее, чем круги с пересечениями и т.д.
Условие ON
Удивительно, но по моим ощущениям 99% разработчиков считают, что в условии ON должен быть id из одной таблицы и id из второй. На самом деле там любое булево выражение.
Например, есть таблица со статистикой юзеров users_stats, и таблица с ip адресами городов.
Тогда к статистике можно прибавить город
SELECT s.id, c.city FROM users_stats AS s JOIN cities_ip_ranges AS c ON c.ip_range && s.ipгде && — оператор пересечения (см. расширение посгреса ip4r)
Если в условии ON поставить true, то это будет полный аналог CROSS JOIN
"table1 JOIN table2 ON true" == "table1 CROSS JOIN table2"Производительность
Есть люди, которые боятся join-ов как огня. Потому что «они тормозят». Знаю таких, где есть полный запрет join-ов по проекту. Т.е. люди скачивают две-три таблицы себе в код и джойнят вручную в каком-нибудь php.
Это, прямо скажем, странно.
Если джойнов немного, и правильно сделаны индексы, то всё будет работать быстро. Проблемы будут возникать скорее всего лишь тогда, когда у вас таблиц будет с десяток в одном запросе. Дело в том, что планировщику нужно определить, в какой последовательности осуществлять джойны, как выгоднее это сделать.
Сложность этой задачи O(n!), где n — количество объединяемых таблиц. Поэтому для большого количества таблиц, потратив некоторое время на поиски оптимальной последовательности, планировщик прекращает эти поиски и делает такой план, какой успел придумать. В этом случае иногда бывает выгодно вынести часть запроса в подзапрос CTE; например, если вы точно знаете, что, поджойнив две таблицы, мы получим очень мало записей, и остальные джойны будут стоить копейки.
Кстати, Еще маленький совет по производительности. Если нужно просто найти элементы в таблице, которых нет в другой таблице, то лучше использовать не ‘LEFT JOIN… WHERE… IS NULL’, а конструкцию EXISTS. Это и читабельнее, и быстрее.
Выводы
Как мне кажется, не стоит использовать диаграммы Венна для объяснения джойнов. Также, похоже, нужно избегать термина «пересечение».
Как объяснить на картинке джойны корректно, я, честно говоря, не представляю. Если вы знаете — расскажите, плиз, и киньте в коменты.
Update В этом видео я наглядно объясняю, как правильно визуализировать джойны (English):
Update2 Продолжение статьи здесь: https://habr.com/ru/post/450528/
Больше полезного можно найти на telegram-канале о разработке «Cross Join», где мы обсуждаем базы данных, языки программирования и всё на свете!
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
- Как прятать текстовые блоки от Яндекса?
- Будет ли сайт корректно работать на CloudFlare?
- Поиск от Yahoo! продолжает терять позиции
Что быстрее: 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 (Открывается в новом окне)