Почему стоит избегать запросов внутри циклов?
Все очень просто. Каждый запрос это потеря времени на «подготовительные» и «заключительные» операции по обращению к БД. Предположим к каждому товару есть отдельная таблица для картинок, скидок и каких-нибудь опций(например выбор цвета платья). То есть для получения информации о каждом товаре, вам нужно будет выполнить 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 запросов. Как можно оптимизировать:
- Выбираем товары из таблицы. Помещаем их в массив, но в качестве ключей будем использовать product_id;
- Выбираем все цены для найденных товаров;
- Найденные цены «распихиваем» по товарам.
Тоже самое, но на понятном языке 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), если такое возможно. И помните о принципе разумности в принимаемых решениях.
Комментарии (120)
DarthVictor
05.03.2018 12:28+5
artshatov Автор
05.03.2018 12:38-1Можно на коротком примере?
onegreyonewhite
05.03.2018 12:42Пример же в ссылке есть. SELECT * FROM Person INNER JOIN City ON Person.CityId = City.Id
artshatov Автор
05.03.2018 13:09-1Не показательно. Кажется мы говорим о разных вещах.
Задача из реальности. Есть 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?remzalp
05.03.2018 13:48Уууу, тут куча интересных и не всегда правильных вариантов:
- В качестве одного из значений вложенный подзапрос, который возвращает через CONCAT список картинок одним полем
- Денормализация какой либо разновидности
- ДВА запроса :) Первый выводит товары и данные из таблиц, которые впихиваются в одну ячейку. Второй берёт список IDшников из первого и отправляет запрос на получение картинок по тику WHERE ID IN (1,2,3....)
Если этот коммент заинтересует больше чем одного человека, я постараюсь перебороть лень и попробую накатать статью про обходные пути в работе SQL из своего небогатого опыта.
NickyX3
05.03.2018 17:58п.3. Помнится мне что в IN c перечислением было определенное ограничение на количество этих самых значений, или даже на размер списка в байтах
НУ и названия картинок товаров хранить в базе как-то так себе идея. Проще генерить им путь по id в файловой системеremzalp
06.03.2018 08:12Тут по сути без разницы, главное из БД получить пачку информации о картинках для тех записей, о которых что-то написано.
Потому что связь ид товара -> ид картинки можно реализовать на уровне файловой системы вообще:
if file_exists($product_id+".jpg")
но это корявенько при большом каталоге.NickyX3
06.03.2018 09:36но это корявенько при большом каталоге.
Это нормально, файловый кеш на fs чаще всего работает вообще быстрее, чем джойны в БД на больших табличкахremzalp
06.03.2018 09:45https://habrahabr.ru/post/157613/
похоже не всегда всё радужно :)
alekciy
06.03.2018 10:06Нормально все. Проблемы которые описаны в указанной статье возникают на уровне прикладного утиля который сначала копить данные в собственно памяти и который ему может и не хватать в процессе.
NickyX3
06.03.2018 10:41А кто говорил о миллионах файлов в одной папке?
Я говорил — генерировать путь до картинок по id товара.
А как это уже другой вопрос. Хоть md5 и делить его на части. Не важно, главное «размазать» по папкам
У нас к примеру это довольно простой метод
/AC/000/486/486193.jpeg
где первая 2-х символьная папка это вообще размазанный на физические разные сервера маппингом путь. До 256 серверов
DarthVictor
05.03.2018 14:41DarthVictor
05.03.2018 14:51Для PostgreSQL (если по какой-то причине выне хотите использовать массивы или JSON).
Для MSSQL (с XML костылями).
Для остального полагаю тоже есть.
glite
06.03.2018 15:34SELECT 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
glite
06.03.2018 16:11SELECT 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
Sartor
05.03.2018 13:53Вы не сталкивались с большой нагрузкой. Если таблица товаро очень широкая и их нужно выбрать много, то джойн тут ужасное решение. Я не говорю уже о том, что если нужно будет кроме цен получить ещё какую-то информацию типа один ко многим. Пример: 1000 товаров, у каждого 10 цен и у каждого 10 признаков. В итоге ваш джойн это всё перемножит и у вас выйдет 100000 строк.
JekaMas
05.03.2018 19:35Жили с джойнами на десятки миллионов товаров помноженные на сотни аттрибутов. Полет нормальный даже на обычном mysql
Sartor
05.03.2018 22:00Да ну, напишите конфигурацию железа, время выборки и кол-во строк в результате? Таким явно не стоит гордиться, Наступит момент, когда вам придётся избавиться от джоинов в таких кейсах.
JekaMas
06.03.2018 09:33Ваш аргумент был про перформанс и join, я привел свой опыт, как контраргумент.
Кластеры master-slave, на мастер до 7 слейвов, в зависимости от страны. Машинки не помню в деталях: 24 ядра, сколько оперативной памяти — не помню (не забываем, что я разработчик, а не dba).
nikitasius
05.03.2018 12:52Все очень просто. Каждый запрос это потеря времени на «подготовительные» и «заключительные» операции по обращению к БД.
Для этого используется кеш БД. Данные из которого будут дергаться, пока эти самые данные неизменны.
Так теперь, для выборки любого количества товаров нужно будет выполнить всего 2 запроса
Можно через 1.
select pp.*,p.* from product_price pp, product p where pp.product_id=p.product_id;
Затем просто на самом php расстасовать.
Если уж решили заморочиться, надо привести к 6й форме, разнеся в 3 таблицы (продукты + цены + типы).
Neikist
05.03.2018 13:06Запросы в цикле чуть ли не первое за что бьют по рукам 1сников)) Особенно если учесть что тривиальные запросы на выборку там редкость, чаще всего это вложенные запросы на вложенных запросах с несколькими соединениями и т.п., плюс сама 1с к запросам добавляет куски для проверки прав и т.п. В итоге если сделать запрос в цикле — падение производительности сразу заметным становится.
artshatov Автор
05.03.2018 13:15Собственно с чего началось. Клиентский сайт на opencart. Таблица product связана «один ко многим» с image, discount, description(языки), store, specials, download etc.
И все на вложенных циклах. Генерация страницы категории с 99 товарами создавал более 700 запросов. Кэш, конечно бы помог, но отображение к кэшу производится тоже очень часто, т.к. в циклах.
Перешли на новую платформу — такая же беда. Приходиться исправлять.
lair
05.03.2018 13:07Подождите, вы правда не знаете про оператор
JOINв SQL?artshatov Автор
05.03.2018 13:17lair
05.03.2018 13:54Вы не ответили на мой вопрос.
artshatov Автор
05.03.2018 13:58+1Нет, не правда. У вас создалось ложное впечатление. Я хорошо представляю себе что такое джоины. Но здесь лучше без них.
lair
05.03.2018 14:01Без джойнов. Для страницы, на которой тысяча товаров. Хм… как? Или ваше решение ограничено "на странице мало товаров"?
artshatov Автор
05.03.2018 14:04Собственно, наиболее полно ответили здесь — habrahabr.ru/post/350468/#comment_10696750
lair
05.03.2018 14:06Так как же вы обойдетесь без джойнов и без запросов в цикле в случае, когда вам надо выбрать страницу в тысячу товаров, и у каждого товара — десять картинок?
artshatov Автор
05.03.2018 14:09Пример в статье. Первый кусок кода с запросом в цикле, второй запрос находится вне цикла. Результат в первом случае в базу совершается 8 запросов, во втором 2. И 2 будет независимо от количества товаров.
lair
05.03.2018 14:09второй запрос находится вне цикла.
… и как вы выберете картинки только для той тысячи товаров, которая вам нужна?
artshatov Автор
05.03.2018 14:11$sql = 'select * from product_price where product_id in (' . implode(',' , $product_ids ). ')';lair
05.03.2018 14:12Тысяча ID внутри
IN. Без параметров, без всего, ага (привет, SQL-инъекции). Оптимизатор запросов на стороне SQL-сервера взял и взорвался, потому что он такое не может.artshatov Автор
05.03.2018 14:18Предлагаю протестить. Уверен, что мой вариант выиграет по скорости.
SQL иньекции в этот вопрос не входит. И в конкретном случае не возможна. Посмотрите внимательно код.JekaMas
05.03.2018 19:38Жил с этим на лайве под highload. Нет не выигрывает. Решение ужасное и с точки зрения performance
Sartor
05.03.2018 22:05Вряд ли есть что-то быстрее прямой выборки по списку первичных ключей, пусть даже по огромному списку.
alekciy
06.03.2018 09:56В контексте этого треда это не тот случай, т.к. сначала из базы получили ID на уровне приложения, потом в райтайме их склеили в строку, потом снова отправили на уровень рсубд, потом ждем ответ…
Просто это нужно было делать вложенным запросом и на таскать туда-сюда данные которые прекрасно высчитываются на уровне рсубд в рамках одного запроса.michael_vostrikov
06.03.2018 13:40Если задача получить товары и связанные картинки, то id товаров все равно надо получать. А раз они есть, то можно отправить их обратно со вторым запросом. Тысячи товаров на одной странице обычно не показывают.
alekciy
06.03.2018 14:13id товаров можно получить из вложенного запроса в рамках текущего. И нет смысла зря гонять туда-сюда данные если можно нужное получить одним запросом.
michael_vostrikov
06.03.2018 15:14Так надо получить всю информацию о товарах, чтобы вывести ее на странице + на каждый товар информацию по нескольким картинкам. Покажите ваш вариант как это сделать.
alekciy
06.03.2018 15:49Собственно glite в комментарии habrahabr.ru/post/350468/#comment_10699050 уже написал.
michael_vostrikov
06.03.2018 18:04Так это не вложенный запрос, а джойн, да еще и с группировкой, да еще и со специфичной для СУБД и появившейся недавно функцией, да еще и требует нестандартной распаковки на стороне приложения, да еще и непонятно как с несколькими полями быть.
А id товаров все равно надо получать.glite
06.03.2018 20:58SELECT 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 уступает по быстродействию любой DBmichael_vostrikov
07.03.2018 14:22Ну а как это поддерживать, вручную всю обработку писать?
Способ в статье это часто используемое решение, с ORM запросы генерируются автоматически, и в результате будут готовые объекты с нужными полями, без всякого парсинга.
Да и не уверен я, что такая группировка с конкатом будет быстрее, чем 2 простых запроса без группировок.
lair
07.03.2018 00:09Вы не поверите, но
SELECT ... FROM Products p LEFT OUTER JOIN ProductImages pi ON p.Id = pi.ProductId WHERE ...michael_vostrikov
07.03.2018 05:49Это понятно, но товарищ говорил про вложенный запрос, с которым не надо вытаскивать id товаров из БД.
Ок, пусть будет джойн. Допустим, нужно 100 товаров, к каждому по 10 картинок. Информация о товаре 1 Кб. Средняя длина int id — 6-7 цифр. Передавать по сети лишних 0.9 Мб в одну сторону, вместо того чтобы передать лишних 700 байт в другую?lair
07.03.2018 09:57Передавать по сети лишних 0.9 Мб в одну сторону, вместо того чтобы передать лишних 700 байт в другую?
Ну, учитывая, что взамен мы получаем нормальный кэшируемый план запроса, одну короткую транзакцию и стриминг — да.
Кому не нравится, переходит на следующий уровень и делает
UNION.michael_vostrikov
07.03.2018 10:22А что не так с кэшированием IN? Пробовал в интернете поискать, сходу ничего не нашлось.
lair
07.03.2018 10:26Есть некое эмпирическое знание, что чем больше в запросе параметров, тем сложнее с ним жить движку БД (а еще запрос с двумя параметрами в
INи с тремя может оказаться для движка разными запросами). Но я не удивлюсь, если это знание устарело, впрочем. Мне и остальных аргументов достаточно (собственно, мне одного стриминга достаточно).
JekaMas
05.03.2018 19:37Я за такое один раз чуть не убил вьетнамского коллегу! Когда пришел запросик с 80К idшников.
За такое надо бить по рукам.Sartor
05.03.2018 22:04Если вы использовали pdo (а я надеюсь вы именно его использовали) — то вы пишите неправду, т.к. там принципиальное ограничение на 65к айдишников. И даже если бы была необходимость в 80к — то этот вариант может выиграть по скорости, чтобы не перемножать 80к на ещё одну таблицу.
SbWereWolf
05.03.2018 13:17+1Странно было читать этот пост, но первокурсникам наверное пригодиться.
Странно видеть:
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 -- добавили пустую колонку что бы количество колонок в запросе было тоже две - как в первом запросе
В один присест получили наименование всех Продуктов и имена всех Менеджеров.artshatov Автор
05.03.2018 13:25-1Речь шла про решение одной проблемы — запросы в циклах. Тема оптимизации приложений очень обширна. Ее за раз не раскрыть. По части SQL тут за кадром осталось слишком дофига вопросов.
Насчет UNION — годный совет. Скажу честно, ни разу в голову не приходило. Надо тестить, есть подозрение, что работать это будет не очень быстро.
Akdmeh
05.03.2018 13:33+1Предлагаю обсудить этот метод. Он мне показался сильным анти-паттерном и примером того, как делать не нужно.
Во-первых, нужна дополнительная колонка, которая объяснит, какую таблицу вы получили. То есть,
select 'product', product_id, product_title from product UNION select 'manager', name, '' from manager
Во-вторых, в этом методе придется писать какие-то обертки и делать слишком много неочевидной магии (за которую, собственно, так и ненавидят PHP).
В третьих, вам всегда придется указывать конкретное количество полей и помнить, в каком порядке они находятся. Если же вам придется добавить поле хотя бы в одну колонку — переписывать придется все запросы, которые находятся в UNION.
Мне кажется, в подобном случае сделать два отдельных запроса будет более очевидно и тестируемо, а еще лучше — использовать JOIN либо кэширование manager в оперативной памяти (я уверен, что этих менеджеров даже в самой большой фирме не больше двух-трех сотен, а на деле меньше 20, поэтому нужно просто сделать один раз запрос на менеджера и закэшировать это в Redis на несколько часов).artshatov Автор
05.03.2018 13:44Преимущество — количество запросов меньше. Вроде быстрее должно быть.
Недостаток — «Страшный» запрос со всеми вытекающими(отладка сложная, разработка еще сложнее)
Под сомнением — производительность. Что дешевле 1 сложный запрос с юнионами, или 5 запросов к каждому источнику данных?Spunreal
05.03.2018 13:55+1Преимущество — количество запросов меньше. Вроде быстрее должно быть.
Очень частое заблуждение. Меньше запросов не всегда хорошо. Иногда 100 маленьких запросов быстрее одного большого, который сделает то же самое.
Akdmeh
05.03.2018 13:57Все зависит от самих запросов. Да, один запрос будет немного быстрее. Но у меня бывали случаи, когда большая выборка длилась до 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 или два отдельных запроса?..
Ну или магия хранимых процедур, а это вообще отдельная тема для разговора.
artshatov Автор
05.03.2018 14:01Под сомнением — производительность. Что дешевле 1 сложный запрос с юнионами, или 5 запросов к каждому источнику данных?
Про то и говорю. Надо проверить на практике. Недостаток очевиден — сложно, очень сложно управлять таким монстром будет.
asamoilov
06.03.2018 12:24Действительно антипаттерн ещё и потому, что в случае ошибки в одном из запросов деления на ноль /неверного приведения типов/ и т.п. — долго и нудно ищем, в каком из запросов это произошло
Spunreal
05.03.2018 14:02$products[(int) $product['product_id']] = $product;
artshatov Автор
05.03.2018 14:04На месте она останется. На месте…
Spunreal
05.03.2018 14:10Самый простой пример — вывести товары в порядке убывания даты появления на сайте. Пусть в упрощённом виде это будет ID товара. Чем больше ID, тем позже товар добавился на сайт.
Более сложный пример, отсортировать по дате, по цене, по неведомой сортировке из совсем левой таблицы, по гороскопу, по <подставить своё>.
Сейчас я вижу только то, что формируется массив с товарами. где индексы — это ID товаров. И он будет автоматом сортироваться по возрастанию индекса.artshatov Автор
05.03.2018 14:15Вы не правы. Массивы в ПХП не сортируются по индексам. Они будут именно в той последовательности в которой вы их в массив положите. Только я отмечу, что обычно массивы обхожу следующим образом:
foreach($products as $product_id => &$product) { //что-то делаю с proudct... }
ellrion
05.03.2018 15:53Вся проблема статьи в элементарности тех вещей которые преподносятся как некое открытие. Очень по детски материал выглядит.
n+1 проблема древняя и всем известная
joinиwhere inестественно широко используемые ее решения, в том числе и во всяких orm.
Так называемая жадная загрузка.
У вас же это преподносится как открытиеartshatov Автор
05.03.2018 16:02Замечание принято. На открытие не претендовал. Странно, что показалось так.
Sartor
05.03.2018 22:08Вы же знаете, для скольких людей это всё-таки будет открытием на определённом этапе. Статья для новичков, но базы данных сейчас мало кто знает хорошо, со всем фундаментом. Такое можно и нужно повторять.
ellrion
06.03.2018 00:10Тогда и нужно это писать в корректных терминах с ссылками на статьи возможно и указанием возможных других решений, а не в таком дилетантской стиле
artshatov Автор
06.03.2018 10:47Спустя сутки. Такого сильного фидбэка не ожидал. Посмотрите, сколько комментариев выше. И какие решения предлагают — inner join, union, поиск по фаловой системе, кэширование… Похоже всем известное решение, которое применяется ряде ORM, известно только мне, вам и еще нескольким людям) Ну и разрабам этих самых ORM.
lair
06.03.2018 12:00Вы исходите из того, что если люди предлагают другое решение, они не знают вашего?
artshatov Автор
06.03.2018 12:20Нет, я исхожу, что люди предлагают решение, но другой задачи.
lair
06.03.2018 12:24А зря. Задача "давайте выберем все строчки со всеми связанными деталями" — она реально типовая, и у нее есть типовые же решения. Ваше — одно из них, и оно имеет ощутимые (перечисленные) недостатки. Решение c
n + 1— тоже типовое, и тоже имеет недостатки (но иногда работает лучше остальных, при этом). Наконец, после этого есть еще как минимум три решения (join; два запроса, где во втором используетсяjoinдля критерия; иunionпо двум предыдущим) — и все они тоже имеют плюсы и минусы.artshatov Автор
06.03.2018 12:56«давайте выберем все строчки со всеми связанными деталями»
Нет. Исходная задача другая. Товар-Цена имеет отношение «один-ко-многим». Нужно получить массив товаров, с полями «цена» и отдать куда-нибудь дальше для вывода-обработки. При этом уберем запросы к бд из цикла. Не больше и не меньше. Условия отбора товаров я оставил за пределами поста.lair
06.03.2018 12:58Исходная задача другая. Товар-Цена имеет отношение «один-ко-многим». Нужно получить массив товаров, с полями «цена» и отдать куда-нибудь дальше для вывода-обработки.
Это и есть "выберем все строчки со связанными деталями". "Один-ко-многим" — это "мастер-детали". Товар — мастер, цены — детали.
musicriffstudio
06.03.2018 13:05+1вам не предлагают решение. Вам предлагают выучить SQL и принципы работа серверов бд.
VolCh
06.03.2018 20:07В задаче "мастер-детали" делать джойн может быть очень избыточно, если в "мастере", со стороны 1 в 1..N отношении данных в одной строке сильно больше чем в "деталях". Например, блоб с картинкой. Да и приводить из реляционного вида в иерархический результаты двух запросов куда проще, чем одного джойна.
musicriffstudio
07.03.2018 09:25любой sql сервер это несоизмеримо больше чем пузырьковая сортировка массива. Производительность зависит от структуры данных, самих данных, от операций с данными за последнее время.
выяснить что лучше в конкретной ситуации можно после анализа плана запросов.
VolCh
07.03.2018 16:28Забыли добавить, что в общем случае производительность не константа, она меняется, её нужно измерять и выявлять узкие места. Собственно даже 1+N запрсов могут не быть узким местом и даже 1+1 будет преждевременной оптимизацией. Но вот вводдить джойн просто потому что увидел 1+N или 1+1 в коде — это точно преждевременная оптимизация, причём с привязкой к конкретному хранилищу или их типу, в большинстве случаев.
prishelec
05.03.2018 18:27Про запросы в циклах это и маленькому ежику понятно (но конечно не самому маленькому).
— Скажу от себя. Я себя не считаю экспертом в области БД (все пути неисповедимы), но опыт достаточен. Так вот: даже при бытовых задачах, выбрать структуру запроса бывает довольно сложнее, нежели в более изощренных. Я уже не говорю про HIGHT LOAD.
Тема очень тонкая в наше время.
А по поводу кода из различных CMS, так бывает, смотришь на какую нить реализацию корзины товаров и ужасаешься. Приходилось сталкиваться с различными реализациями «корзин», когда имел дело по интеграциям различных сервисов.
VolCh
05.03.2018 20:36По поводу джойнов — это решение в лоб, основанное на том, что обе таблицы в одной базе, что таблицы относятся 1..N, причём повторение первой записи N раз проблем не создаст, или 0..N и строки с null проблем не создадут. Ещё могут быть нюансы, в общем случае выборки с джойнами можно отнести к оптимизациям с ограниченной областью применимости.
ploop
06.03.2018 00:20Под джойн не обязательно засовывать таблицу, можно подзапрос, в котором 1) тянем только нужные поля, 2) фильтруем, группируем выборку, 3) решаем проблему с null и прочими непотребствами, и даже 4) подцепляемся к другой базе
Это как пример, вариантов оптимизаций масса для каждого конкретного случая. Но отдать на откуп серверу то, для чего он создан, гораздо правильнее, чем гонять запросы в цикле.
KirEv
06.03.2018 13:15извините, но запросы внутри цикла — это вроде младшая школа, и если такое используется — значит в архитектуре Вашего приложения что-то не так.
даже не знаю, есть ли смысл перечислить возможные способы как этого избежать… их просто масса…
glite
06.03.2018 16:12SELECT 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_idVolCh
06.03.2018 20:08image — blob? ) а на стороне апп-сервера джсон с ними парсить?
artshatov Автор
06.03.2018 20:16Нее, не думаю. Кажется имелось ввиду, что там будет какой-нибудь относительный путь.
glite
07.03.2018 11:43SELECT 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]);
musicriffstudio
нет
artshatov Автор
привет. Добавьте слов, пожалуйста.
sergof
нет
artshatov Автор
Добавьте слов, отличных от слова «Нет».
ToshiruWang
Они реально существуют.
Я видел магазин, написанный этими людьми (или их братьями) — они не знали про join, они делали 3 вложенных цикла, 1 join на 3 таблицы работал на порядок быстрее (десятичный) и это на десятке товаров, O(N^3) при построении отчёта у клиентов, наверное, давало возможность пообедать. Действительно поверишь что «PHP-программисты какие-то не такие».
Fesor
А теперь давайте взглянем на проблему по другому. Предположим что у нас есть эти три таблицы. И мы хотим сделать их независимыми. То есть логика скидок отдельно, хранение картинок отдельно и т.д. Если я выводу список продуктов, я могу сделать where in запрос и склеить данные на стороне приложения.
Если у меня 3 связанные таблицы то у меня будет только 3 запроса в базу а не 3N+1. Да, склейка на стороне приложения медленее нежели join на уровне базы, и если вдруг у меня будут какие-то проблемы с производительностью — мне будет не сложно подменить конкетную выборку на чистый SQL.
Или вы будете говорить что мне обязательно нужно жертвовать гибкостью системы в угоду производительности (с которой проблем, предположим нет).
Конечно же это не означает что построение отчетов стоит делать не через SQL — как раз таки наоборот. Но не стоит говорить что всегда стоит делать всю операцию на SQL за счет джойнов.
ToshiruWang
А где тут гибкость системы? В возможности оставить хвосты в виде фоток и скидок на несуществующие товары?
artshatov Автор
Что вы имеете ввиду под словом «хвосты»?
ToshiruWang
Осиротевшие записи — если таблицы не связаны ключами, то легко можно удалить запись о товаре, оставив мусор (или добавить мусор, не привязанный к основной записи).
Если у вас есть ключи, то никакой жертвы гибкости (называемой обычно продуманной архитектурой) нет.
ellrion
за целостностью вполне в состоянии следить само приложение. И не размазывать ответственность между собой и хранилищем.
ToshiruWang
То, которое гибкое и мимо которого можно залезть в базу?
В котором заодно может что-то упасть в процессе или быть закрыта страница и на выходе — неконсистентные данные. Надеюсь, вы занимаетесь разве что непопулярными магазинами.
ellrion
Надеюсь перейдя на личности вы потешили свое эго и чувствуете себя лучше)
Что же до остального. Не должен никто лазить в хранилище просто так. Или вы все ограничения бизнес логики реализовываете на стороне бд или у вас двойные стандарты. А целостность при падениях приложения обеспечивается использованием транзакций. Вы ведь и так их должны использовать например для защиты целостности при записи в бд. Так что снова двойные стандарты (целостность существования записей вы поддерживаете приложением а целостность отсутствия данных почему то не можете)
Fesor
вам лучше не знать как работает амазон)
artshatov Автор
Никто не говорил, что таблицы не связаны)) Таблицы имеют связь. И делается она не только для того, чтобы обеспечить «связанные» выборки, но и для поддержания этой самой целостности(речь про внешние ключи).
Не понятно, почему способ выборки должен влиять на появление хвостов. Или я не правильные выводы из вашего высказывания выше сделал?
VolCh
Как минимум возможность малой кровью пернести одну из таблиц в другую базу, в том числе совсем другую типа монги, а то и вообще во внешнее хранилище.
Fesor
Если под "хвостами" вы подразумеваете ситуации когда я делаю связи без FK, то… меня это не парит от слова совсем. Эти хвосты легко подчищаются, а отсутствие лишних FK (не все FK хороши но и без них вообще нельзя никак) позволяет мне уменьшить количество необходимых данных для тестирования фич в изоляции.
Кроме того — я не знаю как вы, а у меня товары не могут быть удалены. Могут быть сняты с продажи. не более того.
lair
… уж если мы говорим о гибкости и о том, что с производительностью проблем нет, надо оставлять N+1, потому что это как раз лучше всего масштабируется (в том числе, за счет того, что все N запросов можно послать параллельно, и они еще и закэшируются где-нибудь).
artshatov Автор
Тогда все разговоры про оптимизацию, выборки и прочее, является бесконечным и бесполезным холиваром без привязки к конкретной задаче. Что и наблюдается во всех тредах к этому посту.
lair
А без привязки к конкретной задаче так и есть. Fesor в своем комменте поменял условия задачи (сказав, что проблем с производительностью нет, и нужна гибкость системы) — поменялось и решение.