Вопрос по database, join, mysql, query-optimization – JOIN запросы против нескольких запросов

135

Являются ли запросы JOIN быстрее, чем несколько запросов? (Вы запускаете свой основной запрос, а затем запускаете много других SELECT на основе результатов вашего основного запроса)

Я спрашиваю, потому что присоединение к ним усложнит дизайн моего приложения.

Если они быстрее, может ли кто-нибудь приблизительно приблизиться к тому, насколько? Если это в 1,5 раза больше, мне все равно, но если оно в 10 раз больше, я думаю, что мне нравится.

Я предполагаю, что они будут быстрее. Я знаю, что один INSERT по сравнению с 10 отдельными INSERT-запросами намного быстрее. alex
Это может быть важно, если ваши несколько запросов находятся внутри хранимой процедуры, если они происходят из приложения (отредактируйте ваш вопрос с этой информацией). Первый будет намного быстрее, чем последний. colithium

Ваш Ответ

14   ответов
2

они протестированы в базе данных Oracle, но помните, что SQL - это стандарт, который отличается от Oracle, MS SQL Server, MySQL и других баз данных SQL-диалектом:

http://javaforlearn.com/100-sql-queries-learn/

1

но в нем отсутствуют некоторые критерии. Я сравнил JOIN с его 2 конкурентами:

N+1 queries 2 queries, the second one using a WHERE IN(...) or equivalent

Результат ясен: на MySQLJOIN являетсяmuch Быстрее. N + 1 запросы могут резко снизить производительность приложения:

JOIN vs WHERE IN vs N+1

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

JOIN vs N+1 - all records pointing to the same foreign record

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

вынос:

For *-to-one relationships, always use JOIN For *-to-many relationships, a second query might be faster

Увидетьмоя статья на Среднем для дополнительной информации.

1

нужно ли объединениеmakes sense, Только на этом этапе производительность даже нужно учитывать, так как почти во всех других случаяхworse спектакль.

Различия в производительности во многом будут зависеть от того, насколько связана информация, к которой вы обращаетесь. Присоединяются к работе, и они быстро, когдаthe data is related и вы правильно индексируете вещи, но они часто приводят к некоторой избыточности и иногда к большему количеству результатов, чем необходимо И если ваши наборы данных не связаны напрямую, их привязка к одному запросу приведет к тому, что называется декартовым продуктом (в основном, всеми возможными комбинациями строк), что почти никогда не соответствует вашим ожиданиям.

Это часто вызвано отношениями «многие к одному». Например,Ответ HoldOffHunger'а упомянул один запрос для сообщений, тегов и комментариев. Комментарии связаны с постом, как и теги ... но теги не имеют отношения к комментариям.

+------------+     +---------+     +---------+
|  comment   |     |   post  |     |  tag    |
|------------|*   1|---------|1   *|---------|
| post_id    |-----| post_id |-----| post_id |
| comment_id |     | ...     |     | tag_id  |
| user_id    |     |         |     | ...     |
| ...        |     |         |     | ...     |
+------------+     +---------+     +---------+

В этом случае однозначно лучше, чтобы это было как минимум два отдельных запроса. Если вы попытаетесь объединить теги и комментарии, поскольку между ними нет прямой связи, вы получите все возможные комбинации тегов и комментариев.many * many == manymany, Кроме того, поскольку посты и теги не связаны, вы можете выполнять эти два запроса параллельно, что приведет к потенциальной выгоде.

Давайте рассмотрим другой сценарий: вы хотите, чтобы комментарии были прикреплены к сообщению, а комментаторы & apos; Контактная информация.

 +----------+     +------------+     +---------+
 |   user   |     |  comment   |     |   post  |
 |----------|1   *|------------|*   1|---------|
 | user_id  |-----| post_id    |-----| post_id |
 | username |     | user_id    |     | ...     |
 | ...      |     | ...        |     +---------+
 +----------+     +------------+

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

12

выбрав одну строку из таблицы строк 50000 и соединившись с одной строкой из таблицы строк 100000. В основном выглядело так:

$id = mt_rand(1, 50000);
$row = $db->fetchOne("SELECT * FROM table1 WHERE id = " . $id);
$row = $db->fetchOne("SELECT * FROM table2 WHERE other_id = " . $row['other_id']);

против

$id = mt_rand(1, 50000);
$db->fetchOne("SELECT table1.*, table2.*
    FROM table1
    LEFT JOIN table1.other_id = table2.other_id
    WHERE table1.id = " . $id);

Метод «два выбора» занял 3,7 секунды для 50 000 операций чтения, тогда как на моем медленном домашнем компьютере JOIN занял 2,0 секунды. INNER JOIN и LEFT JOIN ничего не изменили. Выборка нескольких строк (например, с использованием IN SET) дала аналогичные результаты.

Индексируются ли столбцы id и other_id?
Может быть, разница может измениться, если выбрать страницу строк (например, 20 или 50), как для типичной сетки веб-представления, и сравнить одиночный LEFT JOIN с двумя запросами - выбрать 2 или 3 идентификатора с некоторыми критериями WHERE, а затем запустить другой ВЫБЕРИТЕ запрос с помощью IN ().
6

Do these records have a one-to-one relationship or a one-to-many relationship?

TLDR Answer:

Если один к одному, используйтеJOIN заявление.

Если один ко многим, используйте один (или много)SELECT операторы с оптимизацией кода на стороне сервера.

Why and How To Use SELECT for Optimization

SELECT«(с несколькими запросами вместо объединений) для большой группы записей на основе отношения« один ко многим »обеспечивает оптимальную эффективность, так какJOIN'имеет проблему экспоненциальной утечки памяти. Соберите все данные, а затем используйте язык сценариев на стороне сервера, чтобы разобраться в них:

SELECT * FROM Address WHERE Personid IN(1,2,3);

Результаты:

Address.id : 1            // First person and their address
Address.Personid : 1
Address.City : "Boston"

Address.id : 2            // First person's second address
Address.Personid : 1
Address.City : "New York"

Address.id : 3            // Second person's address
Address.Personid : 2
Address.City : "Barcelona"

Здесь я получаю все записи в одном операторе выбора. Это лучше чемJOIN, который будет получать небольшую группу этих записей, по одной, как подкомпонент другого запроса. Затем я анализирую его с помощью серверного кода, который выглядит примерно так ...

<?php
    foreach($addresses as $address) {
         $persons[$address['Personid']]->Address[] = $address;
    }
?>

When Not To Use JOIN for Optimization

JOIN"большая группа записей на основе отношения один к одному с одной записью дает оптимальную эффективность по сравнению с несколькимиSELECT операторы, один за другим, которые просто получают следующий тип записи.

НоJOIN неэффективно при получении записей с отношением один ко многим.

Пример: Блоги базы данных имеют 3 таблицы интереса: Blogpost, Tag и Comment.

SELECT * from BlogPost
LEFT JOIN Tag ON Tag.BlogPostid = BlogPost.id
LEFT JOIN Comment ON Comment.BlogPostid = BlogPost.id;

Если есть 1 запись блога, 2 тега и 2 комментария, вы получите следующие результаты:

Row1: tag1, comment1,
Row2: tag1, comment2,
Row3: tag2, comment1,
Row4: tag2, comment2,

Обратите внимание, как дублируется каждая запись. Итак, 2 комментария и 2 тега - это 4 строки. Что если у нас есть 4 комментария и 4 тега? Вы не получаете 8 строк - вы получаете 16 строк:

Row1: tag1, comment1,
Row2: tag1, comment2,
Row3: tag1, comment3,
Row4: tag1, comment4,
Row5: tag2, comment1,
Row6: tag2, comment2,
Row7: tag2, comment3,
Row8: tag2, comment4,
Row9: tag3, comment1,
Row10: tag3, comment2,
Row11: tag3, comment3,
Row12: tag3, comment4,
Row13: tag4, comment1,
Row14: tag4, comment2,
Row15: tag4, comment3,
Row16: tag4, comment4,

Добавьте больше таблиц, больше записей и т. Д., И проблема быстро раздуется до сотен строк, которые все заполненыmostly избыточные данные.

Сколько стоят эти дубликаты? Память (в SQL-сервере и коде, который пытается удалить дубликаты) и сетевые ресурсы (между SQL-сервером и вашим сервером кода).

Источник:https://dev.mysql.com/doc/refman/8.0/en/nested-join-optimization.html ; https://dev.mysql.com/doc/workbench/en/wb-relationship-tools.html

@cHao: Спасибо за ваш комментарий. Мой ответ выше - это краткое изложение документации MySQL, найденной здесь:dev.mysql.com/doc/workbench/en/wb-relationship-tools.html
@cHao: Извините, я имел в виду документацию MySQL (R) для MySQL WorkBench (TM), а не MySQL Server (TM).
Это не документация MySQL. Это документация для конкретного инструмента с графическим интерфейсом для работы с базами данных MySQL. И он не предлагает каких-либо указаний относительно того, когда объединения являются (или не являются) подходящими.
Вы упускаете суть. Речь идет не об одном-одном (одном | многих). Речь идет о том, имеет ли смысл наборы строк в паре вместе. Вы запрашиваете только два тангенциально связанных набора данных. Если вы спрашивали комментарии и, скажем, их авторов & apos; контактная информация, которая имеет больше смысла в качестве присоединения, даже если люди могут предположительно написать более одного комментария.
Помимо педантизма, актуальность не ясна. Оба упоминают отношения один-к-одному и один-ко-многим, но на этом общность заканчивается. В любом случае, проблема связана с отношениями между наборами данных. Присоединитесь к двум несвязанным сетам, и вы получите каждую комбинацию из двух. Разбейте связанные данные на несколько вариантов выбора, и теперь вы сделали несколько запросов для сомнительной выгоды и начали выполнять работу MySQL для этого.
64

чтобы дать вам ответ, относящийся к вашему конкретному случаю. Это зависит от многих вещей. Джефф Этвуд (основатель этого сайта) на самом деленаписал об этом, По большей части, тем не менее, если у вас есть правильные индексы и вы правильно выполняете свои СОЕДИНЕНИЯ, как правило, будет быстрее совершить 1 поездку, чем несколько.

Я думаю, что это зависит от вашего определения "быстрее" ... например, 3 внутренних соединения PK могут развернуться быстрее, чем 4 обхода, из-за перегрузок в сети, а также потому, что вам нужно остановиться, подготовить и отправить каждый запрос после завершения предыдущего запроса. Однако, если вам нужно было тестировать сервер под нагрузкой, в большинстве случаев объединения будут занимать больше процессорного времени по сравнению с запросами PK, а также часто приводят к увеличению нагрузки на сеть.
если вы объединяете 3 или более таблиц на разных ключах, часто базы данных (например, mysql) могут использовать только один индекс на таблицу, что означает, что, возможно, одно из соединений будет быстрым (и будет использовать индекс), тогда как другие будут чрезвычайно медленными. Для нескольких запросов вы можете оптимизировать индексы для использования в каждом запросе.
5

что обычно быстрее выполнять несколько запросов, особенно при извлечении больших наборов данных.

При взаимодействии с базой данных из другого приложения, такого как PHP, существует аргумент одной поездки на сервер из-за многих.

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

Я не новичок в том, что касается SQL, я думаю, что разработчики, особенно юниоры, склонны тратить много времени, пытаясь написать очень умные объединения, потому что они выглядят умными, тогда как на самом деле есть умные способы извлечения данных, которые выглядят просто.

Последний абзац был личным мнением, но я надеюсь, что это поможет. Я согласен с другими, хотя, кто говорит, что вы должны ориентироваться. Ни один из подходов не является серебряной пулей.

Да, мы также должны учитывать не только сами запросы, но и обработку данных внутри приложения. При извлечении данных с помощью внешних объединений существует некоторая избыточность (иногда она может стать очень большой), которая должна быть отсортирована приложением (обычно в некоторой библиотеке ORM), таким образом, в итоге один запрос SELECT с JOIN может потреблять больше ресурсов ЦП и раз, чем два простых выбора
8

так и объединения, а затем оцените каждый из них - ничто не поможет больше, чем реальные цифры.

Тогда еще лучше - добавь "EXPLAIN" в начале каждого запроса. Это скажет вам, сколько подзапросов MySQL использует для ответа на ваш запрос данных, и сколько строк проверено для каждого запроса.

0

один запрос с использованием JOINS будет быстрее. Хотя, не зная отношений таблиц, к которым вы обращаетесь, размера набора данных или того, где находятся первичные ключи, почти невозможно сказать, насколько быстрее.

Почему бы не протестировать оба сценария, тогда вы наверняка узнаете ...

7

аботчика, может быть проще выполнять много вызовов SELECT.

Попробуйте запустить некоторую статистику базы данных как для JOIN, так и для нескольких SELECTS. Посмотрите, если в вашей среде JOIN быстрее / медленнее, чем SELECT.

С другой стороны, если изменение его на JOIN будет означать дополнительный день / неделю / месяц работы разработчика, я буду придерживаться нескольких SELECT

Ура,

BLT

1

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

Настоящий вопрос - как вы хотите получить доступ к данным? Single выбирает поддержку позднего связывания. Например, если вам нужна только информация о сотруднике, вы можете выбрать ее из таблицы «Сотрудники». Отношения внешнего ключа могут использоваться для извлечения связанных ресурсов позднее и по мере необходимости. У селекторов уже будет ключ для указания, поэтому они должны быть очень быстрыми, и вам нужно только получить то, что вам нужно. Сетевая задержка всегда должна учитываться.

Объединения будут получать все данные одновременно. Если вы создаете отчет или заполняете сетку, это может быть именно тем, что вам нужно. Скомпилированные и оптомизированные объединения просто будут быстрее, чем одиночные выборки в этом сценарии. Помните, что специальные объединения могут быть не такими быстрыми - вы должны скомпилировать их (в сохраненный процесс). Скорость ответа зависит от плана выполнения, который точно описывает, какие шаги СУБД предпринимает для извлечения данных.

3

Но он также потенциально блокирует больше объектов базы данных одновременно (в зависимости от вашей базы данных и вашей схемы) и тем самым уменьшает параллелизм. По моему опыту, людей часто вводят в заблуждение из-за «меньшего количества обращений к базе данных»; аргументом, когда в действительности в большинстве систем OLTP, где база данных находится в одной локальной сети, реальным узким местом является редко сеть.

18

и после прочтения данных ответов я могу только согласиться с тем, что лучший способ сравнить производительность запросов к БД - это получить реальные цифры, поскольку необходимо учитывать только много переменных. НО, я также думаю, что сравнение чисел между ними не приносит пользы почти во всех случаях. Я имею в виду, что цифры всегда следует сравнивать с приемлемым числом, а не сравнивать друг с другом.

Я могу понять, если один способ запроса занимает, например, 0,02 секунды, а другой - 20 секунд, это огромная разница. Но что, если один способ запроса занимает 0,0000000002 секунды, а другой - 0,0000002 секунды? В обоих случаях один способ колоссально в 1000 раз быстрее другого, но так ли это?really все еще "колоссальный" во втором случае?

Суть в том, как я лично это вижу: если все работает хорошо, выбирайте простое решение.

@dudewad На самом деле, когда Facebook начал, я гарантирую, что они пошли с более простым решением. Цукерберг сказал, что он запрограммировал первую версию всего за 2 недели. Стартапы должны двигатьсяfast to compete and the ones that survive usually don't worry about scaling until they actually need it. Then they refactor stuff after they have millions of investment dollars and can hire rockstar programmers that specialize in performance. To your point, I would expect Facebook often goes for the more complex solution for minute performance gains now, but then most of us aren't programming Facebook.
Ха-ха, да ... потому что в Google 1 потерянная наносекунда буквально равна примерно 10 миллиардам триллионов долларов ... но это всего лишь слухи.
Это, конечно, зависит от того, планируете ли вы масштабирование. Потому что когда Facebook начал свою работу, я уверен, что у них были такие запросы, но они имели в виду масштабирование и пошли на более эффективное, хотя, возможно, более сложное решение.
@ Dudewad имеет смысл. Все зависит от того, что вам нужно, в конце концов.
78

поскольку вы получаете только совпадающие строки. Для левых объединений несколько запросов намного лучше ... посмотрите на следующий тест, который я сделал:

Single query with 5 Joins

query: 8.074508 seconds

result size: 2268000

5 queries in a row

combined query time: 0.00262 seconds

result size: 165 (6 + 50 + 7 + 12 + 90)

.

Обратите внимание, что мы получаем одинаковые результаты в обоих случаях (6 х 50 х 7 х 12 х 90 = 2268000)

левые соединения используют экспоненциально больше памяти с избыточными данными.

Ограничение памяти может быть не таким плохим, если вы объединяете только две таблицы, но, как правило, три или более, и это становится полезным для разных запросов.

Как примечание, мой сервер MySQL находится рядом с моим сервером приложений ... поэтому время соединения незначительно. Если ваше время соединения в секундах, то, возможно, есть преимущество

Фрэнк

@cHao Очевидно, вы не встречали Magento во время вашего первого комментария
Если мы отбросим досадный маленький факт, что никто в здравом уме не делает перекрестное соединение между 5 таблицами (по этой причине, а в большинстве случаев этоjust doesn't make sense) ваш & quot; эталон & quot; может иметь некоторые достоинства. Но левое или внутреннее объединение являются нормой, обычно по ключу (что делает поиск намного быстрее), и дублирование данных обычно значительно,much меньше, чем ты думаешь.
Посмотрите на результаты, хотя. & quot; размер результата: 2268000 & quot; по сравнению с «размером результата: 165». Я думаю, что ваше замедление с JOIN связано с тем, что ваши записи имеют отношение один ко многим друг с другом, в то время как если бы они имели отношение один к одному, JOIN был бы абсолютно намного быстрее и, безусловно, не имел бы результата. размер больше, чем SELECT.
@NathanAdams: левые и внутренние соединения совсем не плохие. (На самом деле, если вы не объединяете таблицы здесь и там, вы неправильно выполняете SQL.) Я говорил о том, чтоcross joins, которые почти всегда нежелательны даже между двумя таблицами, не говоря уже о 5 - и которые были бы почти единственным способом получить в противном случае полностью фиктивную "2268000" результаты, упомянутые выше.
@cHao говорит кто? Я просто посмотрел SMF и phpBB и увидел JOIN между 3 таблицами - если вы добавите плагины или модификации, они могут легко добавить к этому. Любое крупное приложение имеет потенциал для многих JOIN. Возможно, плохо написанный / неправильно использованный ORM может присоединиться к таблицам, которые ему на самом деле не нужны (возможно, даже к каждой таблице).

Похожие вопросы