Объединение запросов против нескольких запросов

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

1. один запрос с 5 соединениями

   запрос: 8.074508 секунд

   размер результата: 2268000

2. 5 запросов подряд

   общее время запроса: 0.00262 секунд

   размер результату: 165 (6 + 50 + 7 + 12 + 90)

.

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

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

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

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

Фрэнк

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

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

итог, как я лично это вижу: если он работает хорошо, идти на легкий решение.

сделал быстрый тест, выбрав одну строку из таблицы строк 50,000 и присоединившись к одной строке из таблицы строк 100,000. В основном выглядело так:

$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']);

vs

$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 чтений, тогда как соединение заняло 2,0 секунды на моем домашнем медленном компьютере. Внутреннее соединение и левое соединение не имели значения. Выборка нескольких строк (например, использование в наборе) дала аналогичные результаты.

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

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

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

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

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

спасибо,

BLT

вопрос: у этих записей есть один-к-одному отношения или один-ко-многим отношения?

TLDR ответ:

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

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

почему и как использовать SELECT для оптимизации

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

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"

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

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

когда не использовать JOIN для оптимизации

JOIN'ing большая группа записей на основе отношения один к одному с одной записью обеспечивает оптимальную эффективность по сравнению с несколькими 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,

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

это часто вызвано отношениями "многие-ко-одному-многим". Например, HoldOffHunger это!--15--> упоминается один запрос для сообщений, тегов и комментариев. Комментарии связаны с постом, как и теги...но теги не связаны с комментариями.

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

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

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

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

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

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

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