Как избежать SQL запросов внутри циклов

В этой статье я попробую показать как избежать одного из признаков «загнивающего» кода, а именно SQL запросы внутри циклов. Примеры будут на простом PHP без использования ООП. Это значительно облегчит понимание. Прочтение займет от 5 до 10 минут.

Почему стоит избегать запросов внутри циклов?


Все очень просто. Каждый запрос это потеря времени на «подготовительные» и «заключительные» операции по обращению к БД. Предположим к каждому товару есть отдельная таблица для картинок, скидок и каких-нибудь опций(например выбор цвета платья). То есть для получения информации о каждом товаре, вам нужно будет выполнить 3 запроса. Т.е. 300 запросов на вывод 100 товаров + запрос на получение списка товаров. Итого 301 запрос на 100 товаров. И как результат, существенное снижение производительности вашего приложения. Этого можно и стоит избегать.

Ниже кусочек кода из OpenCart 3:

public function getProducts($data = array()) {

//.....
		$query = $this->db->query($sql);

		foreach ($query->rows as $result) {
			// for never get one more time with same product id
			if(!isset($product_data[$result['product_id']])){
			$product_data[$result['product_id']] = $this->getProduct($result['product_id']);
			}	
		}

		return $product_data;
	}
/*
$this->getProduct($result['product_id']) Делает выборку 1 товара из базы. Т.е. метод getProducts вытаскивает product_id-ы из базы, и потом в цикле "тащит" полные данные на каждого из товаров. Очень классное решение (сарказм).
*/

Задача для оптимизации


Рассмотрим задачу с двумя таблицами — Товары и Цены. У одного товара может быть несколько цен разного типа. Результат нужно получить в виде массива:

print_r($products)
/*
Array
(
    [1] => Array
        (
            [product_id] => 1
            [name] => Товар 1
            [prices] => Array
                (
                    [0] => Array
                        (
                            [price_id] => 45
                            [product_id] => 1
                            [type] => 3
                            [price] => 95.00
                        )

                    [1] => Array
                        (
                            [price_id] => 55
                            [product_id] => 1
                            [type] => 1
                            [price] => 90.00
                        )

                    [2] => Array
                        (
                            [price_id] => 58
                            [product_id] => 1
                            [type] => 2
                            [price] => 90.00
                        )

                )

        )

)
*/

Таблица товаров:
CREATE TABLE `product` (
  `product_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`product_id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;
INSERT INTO `product` (`product_id`, `name`) VALUES (1, 'Товар 1');
INSERT INTO `product` (`product_id`, `name`) VALUES (2, 'Товар 2');
INSERT INTO `product` (`product_id`, `name`) VALUES (3, 'Товар 3');
INSERT INTO `product` (`product_id`, `name`) VALUES (4, 'Товар 4');
INSERT INTO `product` (`product_id`, `name`) VALUES (5, 'Товар 5');
INSERT INTO `product` (`product_id`, `name`) VALUES (6, 'Товар 6');
INSERT INTO `product` (`product_id`, `name`) VALUES (7, 'Товар 7');
INSERT INTO `product` (`product_id`, `name`) VALUES (8, 'Товар 8');
INSERT INTO `product` (`product_id`, `name`) VALUES (9, 'Товар 9');
INSERT INTO `product` (`product_id`, `name`) VALUES (10, 'Товар 10');


Таблица цен:
CREATE TABLE `product_price` (
  `price_id` int(11) NOT NULL AUTO_INCREMENT,
  `product_id` int(11) NOT NULL,
  `type` enum('1','2','3') NOT NULL,
  `price` decimal(10,2) NOT NULL,
  PRIMARY KEY (`price_id`)
) ENGINE=InnoDB AUTO_INCREMENT=71 DEFAULT CHARSET=utf8;
INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (55, 1, '1', 90.00);
INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (58, 1, '2', 90.00);
INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (45, 1, '3', 95.00);
INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (56, 2, '1', 90.00);
INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (49, 2, '2', 45.00);
INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (42, 2, '3', 96.00);
INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (57, 3, '1', 23.00);
INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (47, 3, '2', 53.00);
INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (51, 3, '3', 12.00);
INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (43, 4, '1', 89.00);
INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (46, 4, '2', 4.00);
INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (52, 4, '3', 15.00);
INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (54, 5, '1', 43.00);
INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (41, 5, '2', 44.00);
INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (48, 5, '3', 34.00);
INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (44, 6, '1', 26.00);
INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (59, 6, '2', 26.00);
INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (60, 6, '3', 26.00);
INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (53, 7, '1', 87.00);
INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (61, 7, '2', 87.00);
INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (50, 7, '3', 77.00);
INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (62, 8, '1', 77.00);
INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (63, 8, '2', 77.00);
INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (64, 8, '3', 77.00);
INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (65, 9, '1', 77.00);
INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (66, 9, '2', 77.00);
INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (67, 9, '3', 77.00);
INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (68, 10, '1', 77.00);
INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (69, 10, '2', 77.00);
INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (70, 10, '3', 77.00);


Вариант с запросами внутри циклов:

<?php
define('DB_HOST' , 'localhost');
define('DB_USER' , 'mysqluser');
define('DB_PASSWORD' , 'password');
define('DB_NAME' , 'habr');

$conn = mysqli_connect(DB_HOST , DB_USER , DB_PASSWORD) or die('Can not connect to db');
mysqli_select_db($conn , DB_NAME) or die(mysqli_error($conn));
mysqli_query($conn , 'SET NAMES utf8');

//получаем все товары - 7 шт.
$sql = 'select * from product';
$product_result = mysqli_query($conn , $sql);

//Объявляем массив куда будем собирать все товары
$products = array();

while ($product = mysqli_fetch_assoc($product_result)) {
	//Получаем список цен у текущего товара. 
	$prices = array();
	$sql = 'select * from product_price WHERE product_id = ' . (int) $product['product_id'];
	$price_result = mysqli_query($conn , $sql);

	while ($price = mysqli_fetch_assoc($price_result)) {
		$prices[] = $price;
	}
	//Полученный список цены помещаем в товар
	$product['prices'] = $prices;
	//Товар помещаем в массив товаров
	$products[] = $product;
}

print_r($products);

if ($conn) {
	mysqli_close($conn);
}

Итак, чтобы вывести 7 товаров мы сделали 1+7 запросов. Как можно оптимизировать:

  1. Выбираем товары из таблицы. Помещаем их в массив, но в качестве ключей будем использовать product_id;
  2. Выбираем все цены для найденных товаров;
  3. Найденные цены «распихиваем» по товарам.

Тоже самое, но на понятном языке PHP:

<?php
define('DB_HOST' , 'localhost');
define('DB_USER' , 'mysqluser');
define('DB_PASSWORD' , 'password');
define('DB_NAME' , 'habr');

$conn = mysqli_connect(DB_HOST , DB_USER , DB_PASSWORD) or die('Can not connect to db');
mysqli_select_db($conn , DB_NAME) or die(mysqli_error($conn));
mysqli_query($conn , 'SET NAMES utf8');

//получаем 3 товара
$sql = 'select * from product LIMIT 3';
$product_result = mysqli_query($conn , $sql);

//Объявляем массив куда будем собирать все товары
$products = array();

while ($product = mysqli_fetch_assoc($product_result)) {
	//Инициализируем цены
	$product['prices'] = array();
	//Создаем новый продукт. Ключ уже указываем в качестве product_id
	$products[(int) $product['product_id']] = $product;
}

//Проверяем что есть товары, для которых надо получить цены.

if (count($products) > 0 ) {

	//Получаем список product_id-ов для которых теперь нам надо вытащить цены.
	$product_ids = array_keys($products);

	//составляем запрос на выборку
	$sql = 'select * from product_price where product_id in (' . implode(',' , $product_ids ). ')';
	$prices_result  = mysqli_query($conn , $sql) or die(mysqli_error($conn));
	while ($price = mysqli_fetch_assoc($prices_result)) {
		$products[(int) $price['product_id']]['prices'][] = $price;
	}
}

print_r($products);

if ($conn) {
	mysqli_close($conn);
}

Так теперь, для выборки любого количества товаров нужно будет выполнить всего 2 запроса. Работа такого скрипта будет иметь ощутимую разницу на выборках большого количества товаров. Данный подход можно обобщить. Например, избегайте в циклах обращение в внешним ресурсам(файловой системе, memcache, redis), если такое возможно. И помните о принципе разумности в принимаемых решениях.
Поделиться публикацией
Комментарии 140
    +7
    Предположим к каждому товару есть отдельная таблица для картинок, скидок и каких-нибудь опций(например выбор цвета платья). То есть для получения информации о каждом товаре, вам нужно будет выполнить 3 запроса.


    нет
      –6
      привет. Добавьте слов, пожалуйста.
        +4
        нет
          –3
          Добавьте слов, отличных от слова «Нет».
        0
        Они реально существуют.
        Я видел магазин, написанный этими людьми (или их братьями) — они не знали про join, они делали 3 вложенных цикла, 1 join на 3 таблицы работал на порядок быстрее (десятичный) и это на десятке товаров, O(N^3) при построении отчёта у клиентов, наверное, давало возможность пообедать. Действительно поверишь что «PHP-программисты какие-то не такие».
          +1

          А теперь давайте взглянем на проблему по другому. Предположим что у нас есть эти три таблицы. И мы хотим сделать их независимыми. То есть логика скидок отдельно, хранение картинок отдельно и т.д. Если я выводу список продуктов, я могу сделать where in запрос и склеить данные на стороне приложения.


          Если у меня 3 связанные таблицы то у меня будет только 3 запроса в базу а не 3N+1. Да, склейка на стороне приложения медленее нежели join на уровне базы, и если вдруг у меня будут какие-то проблемы с производительностью — мне будет не сложно подменить конкетную выборку на чистый SQL.


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


          Конечно же это не означает что построение отчетов стоит делать не через SQL — как раз таки наоборот. Но не стоит говорить что всегда стоит делать всю операцию на SQL за счет джойнов.

            0
            А где тут гибкость системы? В возможности оставить хвосты в виде фоток и скидок на несуществующие товары?
              0
              Что вы имеете ввиду под словом «хвосты»?
                0
                Осиротевшие записи — если таблицы не связаны ключами, то легко можно удалить запись о товаре, оставив мусор (или добавить мусор, не привязанный к основной записи).
                Если у вас есть ключи, то никакой жертвы гибкости (называемой обычно продуманной архитектурой) нет.
                  +1

                  за целостностью вполне в состоянии следить само приложение. И не размазывать ответственность между собой и хранилищем.

                    –1
                    То, которое гибкое и мимо которого можно залезть в базу?
                    В котором заодно может что-то упасть в процессе или быть закрыта страница и на выходе — неконсистентные данные. Надеюсь, вы занимаетесь разве что непопулярными магазинами.
                      +1

                      Надеюсь перейдя на личности вы потешили свое эго и чувствуете себя лучше)
                      Что же до остального. Не должен никто лазить в хранилище просто так. Или вы все ограничения бизнес логики реализовываете на стороне бд или у вас двойные стандарты. А целостность при падениях приложения обеспечивается использованием транзакций. Вы ведь и так их должны использовать например для защиты целостности при записи в бд. Так что снова двойные стандарты (целостность существования записей вы поддерживаете приложением а целостность отсутствия данных почему то не можете)

                        0
                        > вы потешили свое эго и чувствуете себя лучш

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

                        > двойные стандарты

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

                        вам лучше не знать как работает амазон)

                      0
                      Никто не говорил, что таблицы не связаны)) Таблицы имеют связь. И делается она не только для того, чтобы обеспечить «связанные» выборки, но и для поддержания этой самой целостности(речь про внешние ключи).
                      Не понятно, почему способ выборки должен влиять на появление хвостов. Или я не правильные выводы из вашего высказывания выше сделал?
                        0
                        Это был ответ на «обеспечить гибкость», не понятно зачем большая гибкость (перенести часть на другую СУБД? при тех объёмах, где безболезненно проходят такие фокусы? зачем?)

                        Вот @Fensor пишет что не пользуется FK и у него delete from Goods, и у него нет товаров, хотя в приложении он ничего такого не планировал.
                          +1
                          не пользуется FK

                          там где это можно. Тут важно разделение на контексты. А гибкость достигается за счет меньшей связанности. То есть, скажем, вместо 1 запроса и 10-и джойнов я делаю 3 запроса и в каждом 3-4 джойна. Эти выборки между собой не пересекаются и склеиваются на уровне приложения. Да, это не самый эффективный способ с точки зрения производительности, но не всегда есть возможность данные держать в одной базе. Да и в целом все это делает код немного более гибким в плане изменений.


                          и у него delete from Goods

                          где я такое писал? Продукты можно снять с продажи — это не означает удаление данных. Как никак у нас должна быть отчетность.

                            0
                            где я такое писал

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

                              0
                              это напишет кто-то с консоли

                              Мне кажется это какие-то проблемы с процессами...

                                0
                                Они всегда есть и всегда будут, если реальных рабочих мест тысячи, десятки заказчиков с разными процессами и админами разной квалификации, удалёнными точками, до которых 3 дня на собаках. И вот буквально на днях наблюдал — 2 таблицы без FK, при попытке добавить — ошибка, просто исторически кто-то добавил в обе, удалил из одной и получил отсутствие в мастере и наличие в подчинённой.
                    +1

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

                      +1

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


                      Кроме того — я не знаю как вы, а у меня товары не могут быть удалены. Могут быть сняты с продажи. не более того.

                      0

                      … уж если мы говорим о гибкости и о том, что с производительностью проблем нет, надо оставлять N+1, потому что это как раз лучше всего масштабируется (в том числе, за счет того, что все N запросов можно послать параллельно, и они еще и закэшируются где-нибудь).

                        0
                        Тогда все разговоры про оптимизацию, выборки и прочее, является бесконечным и бесполезным холиваром без привязки к конкретной задаче. Что и наблюдается во всех тредах к этому посту.
                          +1
                          является бесконечным и бесполезным холиваром без привязки к конкретной задаче

                          А без привязки к конкретной задаче так и есть. Fesor в своем комменте поменял условия задачи (сказав, что проблем с производительностью нет, и нужна гибкость системы) — поменялось и решение.

                      0

                      Осталось выяснить как устроен сам JOIN изнутри. На уровне кода. Полагаю и там без цикла не обошлось. Чудес не бывает даже в SQL.

                        0
                        это легко сделать, см.
                        habrahabr.ru/company/mailru/blog/266811

                        там же, в конце статьи, про чудеса.
                        0
                        Очень просто — если нам надо принести 10 кирпичей, открывая и закрывая дверь каждый раз, то быстрее взять 10 за раз, а не ходить 10 (накладные расходы на запрос).
                          0
                          А вот 100 принести уже не получится — пупок развяжется.
                    +8
                      –4
                      Можно на коротком примере?
                        0
                        Пример же в ссылке есть. SELECT * FROM Person INNER JOIN City ON Person.CityId = City.Id
                          –5
                          Не показательно. Кажется мы говорим о разных вещах.
                          Задача из реальности. Есть 10000 товаров. Для каждого из товаров есть от 0 до 10 фотографий с описанием. Нужно вывести 100 товаров и картинки которые к ним «привязаны».
                          Я как бы не могу понять, как мне поможет INNER JOIN

                          Можно, конечно и картинки и товары получить 1 запросом, но, в этом случае, я бы использовал LEFT JOIN, т.к. тогда я получу весь список товаров, даже у тех у кого картинок нет. В отличии от INNER.
                          Запрос будет выглядеть так:
                          SELECT p.name, p.product_Id, i.image FROM product p LEFT JOIN product_image i ON (i.product_id = p.product_id) WHERE ....
                          

                          А результат запроса так
                          Товар 1, 1, image_1_1.jpg
                          Товар 1, 1, image_1_2.jpg
                          Товар 1, 1, image_1_100500.jpg
                          Товар 2, 2, image_2_1.jpg
                          Товар 2, 2, image_5.jpg
                          Товар 3, 3, NULL


                          Кажется с таким массивом потом работать будет не очень удобно, и привести его придется к иному виду.
                          Как использовать INNER JOIN?
                            +2

                            Уууу, тут куча интересных и не всегда правильных вариантов:


                            1. В качестве одного из значений вложенный подзапрос, который возвращает через CONCAT список картинок одним полем
                            2. Денормализация какой либо разновидности
                            3. ДВА запроса :) Первый выводит товары и данные из таблиц, которые впихиваются в одну ячейку. Второй берёт список IDшников из первого и отправляет запрос на получение картинок по тику WHERE ID IN (1,2,3....)

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

                              0
                              п. 3 интересен.
                                0
                                п.3. Помнится мне что в IN c перечислением было определенное ограничение на количество этих самых значений, или даже на размер списка в байтах

                                НУ и названия картинок товаров хранить в базе как-то так себе идея. Проще генерить им путь по id в файловой системе
                                  0

                                  Тут по сути без разницы, главное из БД получить пачку информации о картинках для тех записей, о которых что-то написано.
                                  Потому что связь ид товара -> ид картинки можно реализовать на уровне файловой системы вообще:
                                  if file_exists($product_id+".jpg")
                                  но это корявенько при большом каталоге.

                                    0
                                    но это корявенько при большом каталоге.

                                    Это нормально, файловый кеш на fs чаще всего работает вообще быстрее, чем джойны в БД на больших табличках
                                      0

                                      https://habrahabr.ru/post/157613/
                                      похоже не всегда всё радужно :)

                                        0
                                        Нормально все. Проблемы которые описаны в указанной статье возникают на уровне прикладного утиля который сначала копить данные в собственно памяти и который ему может и не хватать в процессе.
                                          0
                                          А кто говорил о миллионах файлов в одной папке?
                                          Я говорил — генерировать путь до картинок по id товара.
                                          А как это уже другой вопрос. Хоть md5 и делить его на части. Не важно, главное «размазать» по папкам

                                          У нас к примеру это довольно простой метод
                                          /AC/000/486/486193.jpeg
                                          где первая 2-х символьная папка это вообще размазанный на физические разные сервера маппингом путь. До 256 серверов
                                    0
                                    Для PostgreSQL (если по какой-то причине выне хотите использовать массивы или JSON).
                                    Для MSSQL (с XML костылями).
                                    Для остального полагаю тоже есть.
                                    0
                                    SELECT p.name, p.product_Id, concat('image =>',JSON_ARRAYAGG(i.image)) AS attributes
                                    FROM product p
                                    LEFT JOIN product_image i ON (i.product_id = p.product_id)
                                    GROUP BY p.product_id
                                      0
                                      SELECT p.name, p.product_Id, concat('[image =>',JSON_ARRAYAGG(i.image),']') AS attributes
                                      FROM product p
                                      LEFT JOIN product_image i ON (i.product_id = p.product_id)
                                      GROUP BY p.product_id
                                  –3

                                  Вы не сталкивались с большой нагрузкой. Если таблица товаро очень широкая и их нужно выбрать много, то джойн тут ужасное решение. Я не говорю уже о том, что если нужно будет кроме цен получить ещё какую-то информацию типа один ко многим. Пример: 1000 товаров, у каждого 10 цен и у каждого 10 признаков. В итоге ваш джойн это всё перемножит и у вас выйдет 100000 строк.

                                    +5

                                    Жили с джойнами на десятки миллионов товаров помноженные на сотни аттрибутов. Полет нормальный даже на обычном mysql

                                      –1
                                      Да ну, напишите конфигурацию железа, время выборки и кол-во строк в результате? Таким явно не стоит гордиться, Наступит момент, когда вам придётся избавиться от джоинов в таких кейсах.
                                        0

                                        Ваш аргумент был про перформанс и join, я привел свой опыт, как контраргумент.
                                        Кластеры master-slave, на мастер до 7 слейвов, в зависимости от страны. Машинки не помню в деталях: 24 ядра, сколько оперативной памяти — не помню (не забываем, что я разработчик, а не dba).

                                          0

                                          1 млн товаров, цены, скидки, акции, купоны, атрибуты и прочее всякое.
                                          Время ответа сервера с результатми поиска по любому критерию — 300мс.
                                          Amazon t2.medium

                                    +1
                                    Все очень просто. Каждый запрос это потеря времени на «подготовительные» и «заключительные» операции по обращению к БД.

                                    Для этого используется кеш БД. Данные из которого будут дергаться, пока эти самые данные неизменны.


                                    Так теперь, для выборки любого количества товаров нужно будет выполнить всего 2 запроса

                                    Можно через 1.
                                    select pp.*,p.* from product_price pp, product p where pp.product_id=p.product_id;
                                    Затем просто на самом php расстасовать.


                                    Если уж решили заморочиться, надо привести к 6й форме, разнеся в 3 таблицы (продукты + цены + типы).

                                      0
                                      Запросы в цикле чуть ли не первое за что бьют по рукам 1сников)) Особенно если учесть что тривиальные запросы на выборку там редкость, чаще всего это вложенные запросы на вложенных запросах с несколькими соединениями и т.п., плюс сама 1с к запросам добавляет куски для проверки прав и т.п. В итоге если сделать запрос в цикле — падение производительности сразу заметным становится.
                                        –1
                                        Собственно с чего началось. Клиентский сайт на opencart. Таблица product связана «один ко многим» с image, discount, description(языки), store, specials, download etc.
                                        И все на вложенных циклах. Генерация страницы категории с 99 товарами создавал более 700 запросов. Кэш, конечно бы помог, но отображение к кэшу производится тоже очень часто, т.к. в циклах.
                                        Перешли на новую платформу — такая же беда. Приходиться исправлять.
                                        +3

                                        Подождите, вы правда не знаете про оператор JOIN в SQL?

                                            –3

                                            Вы не ответили на мой вопрос.

                                              +1
                                              Нет, не правда. У вас создалось ложное впечатление. Я хорошо представляю себе что такое джоины. Но здесь лучше без них.
                                                –1

                                                Без джойнов. Для страницы, на которой тысяча товаров. Хм… как? Или ваше решение ограничено "на странице мало товаров"?

                                                  –1
                                                  Собственно, наиболее полно ответили здесь — habrahabr.ru/post/350468/#comment_10696750

                                                    0

                                                    Так как же вы обойдетесь без джойнов и без запросов в цикле в случае, когда вам надо выбрать страницу в тысячу товаров, и у каждого товара — десять картинок?

                                                      –1
                                                      Пример в статье. Первый кусок кода с запросом в цикле, второй запрос находится вне цикла. Результат в первом случае в базу совершается 8 запросов, во втором 2. И 2 будет независимо от количества товаров.
                                                        –1
                                                        второй запрос находится вне цикла.

                                                        … и как вы выберете картинки только для той тысячи товаров, которая вам нужна?

                                                          0
                                                          $sql = 'select * from product_price where product_id in (' . implode(',' , $product_ids ). ')';

                                                            –1

                                                            Тысяча ID внутри IN. Без параметров, без всего, ага (привет, SQL-инъекции). Оптимизатор запросов на стороне SQL-сервера взял и взорвался, потому что он такое не может.

                                                              –1
                                                              Предлагаю протестить. Уверен, что мой вариант выиграет по скорости.
                                                              SQL иньекции в этот вопрос не входит. И в конкретном случае не возможна. Посмотрите внимательно код.
                                                                +2

                                                                Жил с этим на лайве под highload. Нет не выигрывает. Решение ужасное и с точки зрения performance

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

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

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

                                                                      Просто это нужно было делать вложенным запросом и на таскать туда-сюда данные которые прекрасно высчитываются на уровне рсубд в рамках одного запроса.
                                                                        0
                                                                        Если задача получить товары и связанные картинки, то id товаров все равно надо получать. А раз они есть, то можно отправить их обратно со вторым запросом. Тысячи товаров на одной странице обычно не показывают.
                                                                          0
                                                                          id товаров можно получить из вложенного запроса в рамках текущего. И нет смысла зря гонять туда-сюда данные если можно нужное получить одним запросом.
                                                                            0
                                                                            Так надо получить всю информацию о товарах, чтобы вывести ее на странице + на каждый товар информацию по нескольким картинкам. Покажите ваш вариант как это сделать.
                                                                              0
                                                                              Собственно glite в комментарии habrahabr.ru/post/350468/#comment_10699050 уже написал.
                                                                                0
                                                                                Так это не вложенный запрос, а джойн, да еще и с группировкой, да еще и со специфичной для СУБД и появившейся недавно функцией, да еще и требует нестандартной распаковки на стороне приложения, да еще и непонятно как с несколькими полями быть.

                                                                                А id товаров все равно надо получать.
                                                                                  0
                                                                                  SELECT p.product_id,
                                                                                  GROUP_CONCAT(DISTINCT i.image
                                                                                  ORDER BY i.image DESC SEPARATOR ', ')
                                                                                  FROM product p
                                                                                  LEFT JOIN product_image i ON (i.product_id = p.product_id)
                                                                                  GROUP BY p.product_id
                                                                                  Я не помню у меня тоже такая задача как то стояла из-за кривых рук архитектора DB, MYSQL уже забросил нет желания его для тестов поднимать, на крайний случай можно написать функцию которая будет получать id записи и собирать в строку данные с разделителем и возвращать строку в SELEC в любом случаи лучше собрать и подготовить данные внутри DB лучше чем парсить эти данные языком, даже PERL уступает по быстродействию любой DB
                                                                                    0
                                                                                    Ну а как это поддерживать, вручную всю обработку писать?
                                                                                    Способ в статье это часто используемое решение, с ORM запросы генерируются автоматически, и в результате будут готовые объекты с нужными полями, без всякого парсинга.

                                                                                    Да и не уверен я, что такая группировка с конкатом будет быстрее, чем 2 простых запроса без группировок.
                                                                                0

                                                                                Вы не поверите, но


                                                                                SELECT ...
                                                                                FROM Products p
                                                                                  LEFT OUTER JOIN ProductImages pi
                                                                                    ON p.Id = pi.ProductId
                                                                                WHERE ...
                                                                                  0
                                                                                  Это понятно, но товарищ говорил про вложенный запрос, с которым не надо вытаскивать id товаров из БД.

                                                                                  Ок, пусть будет джойн. Допустим, нужно 100 товаров, к каждому по 10 картинок. Информация о товаре 1 Кб. Средняя длина int id — 6-7 цифр. Передавать по сети лишних 0.9 Мб в одну сторону, вместо того чтобы передать лишних 700 байт в другую?
                                                                                    0
                                                                                    Передавать по сети лишних 0.9 Мб в одну сторону, вместо того чтобы передать лишних 700 байт в другую?

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


                                                                                    Кому не нравится, переходит на следующий уровень и делает UNION.

                                                                                      0
                                                                                      А что не так с кэшированием IN? Пробовал в интернете поискать, сходу ничего не нашлось.
                                                                                        0

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

                                                                                          0
                                                                                          БД обычно IN разворачивает в кучу UNION'ов, либо в условие через OR, что по сути те же N отдельных запросов. Плюс к тому, в таком случае получается не параметризированный запрос, что дает забивание кэша у парсера запросов, так как каждый такой запрос с другим списком параметров считается новым.
                                                                                            0
                                                                                            Тут вот пишут, что это оптимизируется.
                                                                                            http://www.mysqltutorial.org/sql-in.aspx
                                                                                            When the values in the list are all constants:

                                                                                            — First, MySQL evaluates the values based on the type of the column_1 or result of the expr expression.
                                                                                            — Second, MySQL sorts the values.
                                                                                            — Third, MySQL searches for values using binary search algorithm. Therefore, a query that uses the IN operator with a list of constants will perform very fast.
                                                                    +1

                                                                    Я за такое один раз чуть не убил вьетнамского коллегу! Когда пришел запросик с 80К idшников.
                                                                    За такое надо бить по рукам.

                                                                      0

                                                                      Сильный аргумент. Спасибо.

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

                                                                          Кто сказал, что мы использовали php?

                                                                            0
                                                                            При чем тут php? ограничение в mysql, а не в php
                                                                              0
                                                                              То есть оно прям вом гвоздями прибито и dba поменять что-то вроде dev.mysql.com/doc/refman/5.7/en/packet-too-large.html не могут?
                                                                                0
                                                                                Поменять max_allowed_packet то можно, только вот оно не в штуках, а в байтах. И по-умолчанию 4Мб с максимумом в 1Гб. И прямой корреляции с количеством значений в функции IN не имеет (пока весь запрос влезает в размер пакета будет работать. В теории)
                                                    +2
                                                    Странно было читать этот пост, но первокурсникам наверное пригодиться.
                                                    Странно видеть:
                                                    select * from product

                                                    и читать:
                                                    Как можно оптимизировать

                                                    если нужна только колонка product_id, то и запрашивать надо только её:
                                                    select product_id from product 

                                                    вот это называется оптимизация — когда запрашивается только то что надо, а не все что есть.
                                                    SQL Join
                                                    — очень годный совет.
                                                    Не надо делать 100500 запросов, надо сделать один запрос который вернёт полный набор данных, если конечно мы не торопимся выдать пользователю хотя бы что то, потому что полного набора данных ему не требуется.

                                                    совет от меня — UNION — это вообще супер оптимизация обмена с сервером СУБД, можно сделать в один раз 100500 запросов из самых разных источников данных, но в каждом отдельном запросе надо будет добавить колонок, так что бы во всех селектах было одинаковое количество запрашиваемых колонок:
                                                    
                                                    select product_id, product_title from product
                                                    UNION
                                                    select name, '' from manager -- добавили пустую колонку что бы количество колонок в запросе было тоже две - как в первом запросе
                                                    

                                                    В один присест получили наименование всех Продуктов и имена всех Менеджеров.
                                                      –1
                                                      Речь шла про решение одной проблемы — запросы в циклах. Тема оптимизации приложений очень обширна. Ее за раз не раскрыть. По части SQL тут за кадром осталось слишком дофига вопросов.
                                                      Насчет UNION — годный совет. Скажу честно, ни разу в голову не приходило. Надо тестить, есть подозрение, что работать это будет не очень быстро.
                                                        +2
                                                        Предлагаю обсудить этот метод. Он мне показался сильным анти-паттерном и примером того, как делать не нужно.
                                                        Во-первых, нужна дополнительная колонка, которая объяснит, какую таблицу вы получили. То есть,
                                                        select 'product', product_id, product_title from product
                                                        UNION
                                                        select 'manager', name, '' from manager

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

                                                        Мне кажется, в подобном случае сделать два отдельных запроса будет более очевидно и тестируемо, а еще лучше — использовать JOIN либо кэширование manager в оперативной памяти (я уверен, что этих менеджеров даже в самой большой фирме не больше двух-трех сотен, а на деле меньше 20, поэтому нужно просто сделать один раз запрос на менеджера и закэшировать это в Redis на несколько часов).
                                                          0
                                                          Преимущество — количество запросов меньше. Вроде быстрее должно быть.
                                                          Недостаток — «Страшный» запрос со всеми вытекающими(отладка сложная, разработка еще сложнее)
                                                          Под сомнением — производительность. Что дешевле 1 сложный запрос с юнионами, или 5 запросов к каждому источнику данных?
                                                            +1
                                                            Преимущество — количество запросов меньше. Вроде быстрее должно быть.

                                                            Очень частое заблуждение. Меньше запросов не всегда хорошо. Иногда 100 маленьких запросов быстрее одного большого, который сделает то же самое.
                                                              0
                                                              Все зависит от самих запросов. Да, один запрос будет немного быстрее. Но у меня бывали случаи, когда большая выборка длилась до 10-15 секунд, и тогда выполнить один запрос или несколько мелких — уже не имеет значения.
                                                              По своему опыту, если нужно получить таблицу менеджеров и продуктов — получил бы менеджеров отдельно, а продукты отдельно (учитывая, что там еще обычно нужно JOIN'ить с других таблиц дополнительные данные). Потеря от лишнего запроса компенсируется сопровождаемостью и очевидностью кода, а не использованием магических библиотек, которые обычно будут накладывать свой синтаксис или ограничения.
                                                              Несомненно плохо делать сотни или даже тысячи мелких запросов в цикле.
                                                              Пример с юнионом я вижу в виде следующей библиотеки (набросал первое, что пришло в голову):
                                                              $get_complex_query = new ComplexQuery(
                                                              new Query(['id', 'name', 'other_info'], $manager_table_name),
                                                              new Query(['id', 'name', 'price', 'color', 'many_other_product_data'], $product_table_name)
                                                              );
                                                              

                                                              Результатом будет либо еще объект со своими свойствами, либо массив типа array => [manager=>[...], product=>[...]]
                                                              В таком виде оно имеет право на существование, но когда нужно будет джойнить, добавлять WHERE, ORDER условия (в менеджеров — свои, в продуктах — свои и при этом динамически) — библиотека будет расти и усложняться, и в какой-то момент понимаешь, что проще было не создавать этот костыль. Когда придет новый человек в команду — ему придется еще тратить время на изучение этого велосипеда.
                                                              В итоге может оказаться, что все эти запросы очень ограниченные, а там, где они подходят — выигрыш слишком мелкий, чтобы жертвовать удобством разработки.
                                                              Плюс потеря будет еще в необходимости лишнего прохода циклом по всем данным. Данные, полученные с DB, нужно будет разнести по разным массивам и вернуть сразу весь результат. То есть, сделать динамический fetch мне не представляется возможным.
                                                              Ну либо будет возвращен fetch, и тогда все тяготы по различению, с какой таблицы эти данные — ложатся на плечи разработчика, либо передавать какие-то анонимные функции, что 100% будет влиять на смешивание модели и представления.

                                                              В этот момент пора задать вопрос: не лучше ли JOIN или два отдельных запроса?..
                                                              Ну или магия хранимых процедур, а это вообще отдельная тема для разговора.
                                                                0
                                                                Под сомнением — производительность. Что дешевле 1 сложный запрос с юнионами, или 5 запросов к каждому источнику данных?

                                                                Про то и говорю. Надо проверить на практике. Недостаток очевиден — сложно, очень сложно управлять таким монстром будет.
                                                                  0

                                                                  "вроде"? Вы проверяли?
                                                                  Тут "на глаз" не работает.

                                                                  0

                                                                  Действительно антипаттерн ещё и потому, что в случае ошибки в одном из запросов деления на ноль /неверного приведения типов/ и т.п. — долго и нудно ищем, в каком из запросов это произошло

                                                                  0
                                                                  Позвольте спросить, какого типа будет первая колонка?
                                                                    0
                                                                    можно привести к любому нужному типу, не надо воспринимать мой комент как универсальное решение, мой комент это направление мысли
                                                                  0
                                                                  $products[(int) $product['product_id']] = $product;

                                                                  image
                                                                    0
                                                                    На месте она останется. На месте…
                                                                      0
                                                                      Самый простой пример — вывести товары в порядке убывания даты появления на сайте. Пусть в упрощённом виде это будет ID товара. Чем больше ID, тем позже товар добавился на сайт.

                                                                      Более сложный пример, отсортировать по дате, по цене, по неведомой сортировке из совсем левой таблицы, по гороскопу, по <подставить своё>.

                                                                      Сейчас я вижу только то, что формируется массив с товарами. где индексы — это ID товаров. И он будет автоматом сортироваться по возрастанию индекса.
                                                                        0
                                                                        Вы не правы. Массивы в ПХП не сортируются по индексам. Они будут именно в той последовательности в которой вы их в массив положите. Только я отмечу, что обычно массивы обхожу следующим образом:
                                                                        foreach($products as $product_id => &$product) {
                                                                        //что-то делаю с proudct...
                                                                        
                                                                        }
                                                                        
                                                                          0
                                                                          Прошу прощения. Что-то мне спать пора. Глупость сморозил
                                                                  +5

                                                                  Вся проблема статьи в элементарности тех вещей которые преподносятся как некое открытие. Очень по детски материал выглядит.
                                                                  n+1 проблема древняя и всем известная
                                                                  join и where in естественно широко используемые ее решения, в том числе и во всяких orm.
                                                                  Так называемая жадная загрузка.
                                                                  У вас же это преподносится как открытие

                                                                    –1
                                                                    Замечание принято. На открытие не претендовал. Странно, что показалось так.
                                                                    0
                                                                    Вы же знаете, для скольких людей это всё-таки будет открытием на определённом этапе. Статья для новичков, но базы данных сейчас мало кто знает хорошо, со всем фундаментом. Такое можно и нужно повторять.
                                                                      +1

                                                                      Тогда и нужно это писать в корректных терминах с ссылками на статьи возможно и указанием возможных других решений, а не в таком дилетантской стиле

                                                                      0
                                                                      Спустя сутки. Такого сильного фидбэка не ожидал. Посмотрите, сколько комментариев выше. И какие решения предлагают — inner join, union, поиск по фаловой системе, кэширование… Похоже всем известное решение, которое применяется ряде ORM, известно только мне, вам и еще нескольким людям) Ну и разрабам этих самых ORM.
                                                                        0

                                                                        Вы исходите из того, что если люди предлагают другое решение, они не знают вашего?

                                                                          0
                                                                          Нет, я исхожу, что люди предлагают решение, но другой задачи.
                                                                            0

                                                                            А зря. Задача "давайте выберем все строчки со всеми связанными деталями" — она реально типовая, и у нее есть типовые же решения. Ваше — одно из них, и оно имеет ощутимые (перечисленные) недостатки. Решение c n + 1 — тоже типовое, и тоже имеет недостатки (но иногда работает лучше остальных, при этом). Наконец, после этого есть еще как минимум три решения (join; два запроса, где во втором используется join для критерия; и union по двум предыдущим) — и все они тоже имеют плюсы и минусы.

                                                                              0
                                                                              «давайте выберем все строчки со всеми связанными деталями»

                                                                              Нет. Исходная задача другая. Товар-Цена имеет отношение «один-ко-многим». Нужно получить массив товаров, с полями «цена» и отдать куда-нибудь дальше для вывода-обработки. При этом уберем запросы к бд из цикла. Не больше и не меньше. Условия отбора товаров я оставил за пределами поста.
                                                                                0
                                                                                Исходная задача другая. Товар-Цена имеет отношение «один-ко-многим». Нужно получить массив товаров, с полями «цена» и отдать куда-нибудь дальше для вывода-обработки.

                                                                                Это и есть "выберем все строчки со связанными деталями". "Один-ко-многим" — это "мастер-детали". Товар — мастер, цены — детали.

                                                                            0
                                                                            Кстати, решения дельные.
                                                                            +1
                                                                            вам не предлагают решение. Вам предлагают выучить SQL и принципы работа серверов бд.
                                                                              0
                                                                              Предложение принято))
                                                                                0
                                                                                Почему считаете это предложение уместным?
                                                                                  0

                                                                                  В задаче "мастер-детали" делать джойн может быть очень избыточно, если в "мастере", со стороны 1 в 1..N отношении данных в одной строке сильно больше чем в "деталях". Например, блоб с картинкой. Да и приводить из реляционного вида в иерархический результаты двух запросов куда проще, чем одного джойна.

                                                                                    0

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


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

                                                                                      0

                                                                                      Забыли добавить, что в общем случае производительность не константа, она меняется, её нужно измерять и выявлять узкие места. Собственно даже 1+N запрсов могут не быть узким местом и даже 1+1 будет преждевременной оптимизацией. Но вот вводдить джойн просто потому что увидел 1+N или 1+1 в коде — это точно преждевременная оптимизация, причём с привязкой к конкретному хранилищу или их типу, в большинстве случаев.

                                                                              +1
                                                                              Про запросы в циклах это и маленькому ежику понятно (но конечно не самому маленькому).
                                                                              — Скажу от себя. Я себя не считаю экспертом в области БД (все пути неисповедимы), но опыт достаточен. Так вот: даже при бытовых задачах, выбрать структуру запроса бывает довольно сложнее, нежели в более изощренных. Я уже не говорю про HIGHT LOAD.
                                                                              Тема очень тонкая в наше время.
                                                                              А по поводу кода из различных CMS, так бывает, смотришь на какую нить реализацию корзины товаров и ужасаешься. Приходилось сталкиваться с различными реализациями «корзин», когда имел дело по интеграциям различных сервисов.
                                                                              0
                                                                              По поводу джойнов — это решение в лоб, основанное на том, что обе таблицы в одной базе, что таблицы относятся 1..N, причём повторение первой записи N раз проблем не создаст, или 0..N и строки с null проблем не создадут. Ещё могут быть нюансы, в общем случае выборки с джойнами можно отнести к оптимизациям с ограниченной областью применимости.
                                                                                0
                                                                                Под джойн не обязательно засовывать таблицу, можно подзапрос, в котором 1) тянем только нужные поля, 2) фильтруем, группируем выборку, 3) решаем проблему с null и прочими непотребствами, и даже 4) подцепляемся к другой базе

                                                                                Это как пример, вариантов оптимизаций масса для каждого конкретного случая. Но отдать на откуп серверу то, для чего он создан, гораздо правильнее, чем гонять запросы в цикле.
                                                                                  0

                                                                                  Я в целом за подход 1+1 вместо 1+N по умолчанию.

                                                                                    0
                                                                                    Да «в целом» всем бы так, но на практике по разному получается :)
                                                                                0
                                                                                извините, но запросы внутри цикла — это вроде младшая школа, и если такое используется — значит в архитектуре Вашего приложения что-то не так.

                                                                                даже не знаю, есть ли смысл перечислить возможные способы как этого избежать… их просто масса…
                                                                                  0
                                                                                  SELECT p.name, p.product_Id, concat('[image =>',JSON_ARRAYAGG(i.image),']') AS attributes
                                                                                  FROM product p
                                                                                  LEFT JOIN product_image i ON (i.product_id = p.product_id)
                                                                                  GROUP BY p.product_id
                                                                                    0

                                                                                    image — blob? ) а на стороне апп-сервера джсон с ними парсить?

                                                                                      0
                                                                                      Нее, не думаю. Кажется имелось ввиду, что там будет какой-нибудь относительный путь.
                                                                                        0
                                                                                        SELECT concat('$#',GROUP_CONCAT(DISTINCT cast(p.product_id as text)
                                                                                        ORDER BY p.product_id DESC SEPARATOR ','),'$#',
                                                                                        GROUP_CONCAT(DISTINCT i.image
                                                                                        ORDER BY i.image DESC SEPARATOR ',')
                                                                                        FROM product p
                                                                                        LEFT JOIN product_image i ON (i.product_id = p.product_id)
                                                                                        where…
                                                                                        $keywords = preg_split("/[$#]+/", $data['concat']);
                                                                                        $ARRAY=[];
                                                                                        $ARRAY['image'] = explode(",",$keywords[1]);
                                                                                        $ARRAY['id'] = explode(",",$keywords[0]);
                                                                                    0
                                                                                    Не ну класс. Можно считать скил прокачан!
                                                                                    Лэвел школьник 9 класса.

                                                                                    Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

                                                                                    Самое читаемое