Должен ли я использовать функцию JOIN или запускать несколько запросов в структуре цикла?

Ни один из предложенных двух решений, вероятно, не является оптимальным, НО решение 1 НЕПРЕРЫВНО и, таким образом, НЕПРАВИЛЬНО ВОЗМОЖНО!

Одна из первых вещей, которые вы узнаете при работе с большими базами данных, – это то, что «лучший способ» выполнить запрос часто зависит от факторов (называемых метаданными) в базе данных:

• Сколько строк есть.
• Сколько таблиц вы запрашиваете.
• Размер каждой строки.

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

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

Есть некоторые золотые правила с запросами, которые редко следует прерывать:

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

Из-за этого из двух я бы пошел на второй запрос (заменяя SELECT * только нужными столбцами), но , вероятно, есть более эффективные способы структурирования запроса, если у вас есть время для оптимизации.

Тем не менее, скорость не должна быть вашим единственным рассмотрением в этом, есть БОЛЬШАЯ причина не использовать предложение:

ПРОГНОЗИРОВАНИЕ: почему считывающие замки – хорошая вещь

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

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

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

Эта задержка – это ХОРОШАЯ вещь. Почему, спросите вы? Целостность реляционных данных.

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

 SELECT * FROM `users` JOIN `items` ON `users`.`id`=`items`.`inventory_id` WHERE `users`.`logged_in` = 1; 

Что произойдет, если во время этой операции запроса пользователь торгует товар другому пользователю? Используя этот запрос, мы видим состояние игры, как это было при запуске запроса: элемент существует один раз, в инвентаре пользователя, у которого он был до того, как мы выполнили запрос.

Но что произойдет, если мы запустим его в цикле?

В зависимости от того, продавал ли пользователь его до или после того, как мы читаем его данные, и в каком порядке мы читаем инвентарь двух игроков, есть четыре возможности:

1. Элемент может отображаться в инвентаре первого пользователя (сканировать пользователя B -> сканировать пользователя A -> проданный объект или сканировать пользователя B -> сканировать пользователя A -> торгуемый товар).
2. Элемент может отображаться в инвентаре второго пользователя (товар торгуется -> сканировать пользователя A -> пользователь сканирования B ИЛИ продаваемый объект -> сканировать пользователя B -> сканировать пользователя A).
3. Элемент может отображаться в обеих инвентарях (сканирование пользователя A -> товар торгуется -> сканирование пользователя B).
4. Элемент может отображаться в ни из инвентаря пользователя (сканировать пользователя B -> проданный товар -> сканировать пользователя A).

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

Если вы планируете дать 5000 долларов парню с номером 1000000 в полночь во вторник, я надеюсь, что у вас есть 10 тысяч долларов. Если ваша программа использует уникальные элементы, которые уникальны при съемке снимков, вы, возможно, создадите исключение из этого типа запроса.

Блокировка хороша тем, что повышает предсказуемость и защищает целостность результатов.

Примечание. Вы можете заставить цикл заблокировать транзакцией , но он все равно будет медленнее.

О, и, наконец, ИСПОЛЬЗУЕМЫЕ ГОТОВЫЕ ЗАЯВЛЕНИЯ!

У вас никогда не должно быть утверждения, которое выглядит так:

 mysqli_query("SELECT * FROM Table2 WHERE ColumnAId=" . $row['ColumnAId'], $con); 

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

Определенно второй путь. Вложенный запрос – это уродливая вещь, так как каждый раз каждый запрос на вложенные запросы (исполнение, сеть e tc) получает каждый раз для каждого вложенного запроса, тогда как один запрос JOIN будет выполняться один раз – т.е. все накладные расходы будут выполняться только один раз.

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

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

В таком случае вам следует использовать другую возможность хранения / получения, такую ​​как хранилище для ключей (там есть постоянные, а более новые версии MySQL предоставляют интерфейс для ключей и для InnoDB, но он по-прежнему использует реляционную база данных для хранения ключей, а также неправильный инструмент для работы).

Если вы STILL хотите использовать свое решение:

Benchmark.

Я часто обнаружил, что выдача нескольких запросов может быть быстрее, чем один запрос, потому что MySQL должен анализировать меньше запросов, оптимизатор имеет меньше работы, и чаще всего оптимизатор MySQL просто терпит неудачу (вот почему такие вещи, как STRAIGHT СОВМЕСТИМОСТЬ и подсказки индекса существуют). И даже если это не сработает, несколько запросов могут быть еще быстрее в зависимости от базового механизма хранения, а также того, сколько потоков пытается получить доступ к данным сразу (блокировка детализации – это применимо только при микшировании в запросах обновления – ни MyISAM, ни InnoDB блокирует всю таблицу для запросов SELECT по умолчанию). Опять же, вы можете получить разные результаты с двумя решениями, если не используете транзакции, поскольку данные могут меняться между запросами, если вы используете несколько запросов по сравнению с одним.

В двух словах: есть еще вопрос к вашему вопросу, чем то, что вы отправили / попросили, и что может дать общий ответ.

Что касается ваших решений: я бы предпочел первое решение, если у вас есть среда, в которой a) изменения данных являются общими и / или b) у вас есть много одновременных запущенных потоков (запросов), которые обеспечивают доступ и обновление ваших таблиц (блокировка детализации лучше с разбиением запросов, а также кэшируемость запросов) ; если ваша база данных находится в другой сети, например, задержка в сети является проблемой, вы, вероятно, лучше с первым решением (но большинство людей, которых я знаю, имеют MySQL на одном сервере, используя соединения сокетов, а локальные соединения сокетов обычно не работают, t имеют много латентности).

Ситуация может также изменяться в зависимости от того, как часто цикл for фактически выполняется.

Опять же: контрольный показатель

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

Для эффективности использования памяти версия нескольких запросов, безусловно, лучше, так как у вас есть php-массив в любом случае (который очень неэффективен с точки зрения памяти!) В обоих решениях, но соединение может использовать временную таблицу в зависимости от нескольких обстоятельств (обычно это должно 't, но там есть случаи, где это происходит – вы можете проверить, используя EXPLAIN для своих запросов)

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

Если вы хотите показать 1000 строк И какой-то один запрос (основные данные)

вы должны запустить 1000 с ограничением между 10-100

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

пример

Выберите productID, date from transaction_product limit 100

Получите весь идентификатор продукта и сделайте его уникальным

Затем выберите цену из master_product WHERE IN (1,2 3 4) limit 4 (количество от общего уникального)

foreach (транзакция) master_poduct [productID]