Comments 140
Я видел магазин, написанный этими людьми (или их братьями) — они не знали про join, они делали 3 вложенных цикла, 1 join на 3 таблицы работал на порядок быстрее (десятичный) и это на десятке товаров, O(N^3) при построении отчёта у клиентов, наверное, давало возможность пообедать. Действительно поверишь что «PHP-программисты какие-то не такие».
А теперь давайте взглянем на проблему по другому. Предположим что у нас есть эти три таблицы. И мы хотим сделать их независимыми. То есть логика скидок отдельно, хранение картинок отдельно и т.д. Если я выводу список продуктов, я могу сделать where in запрос и склеить данные на стороне приложения.
Если у меня 3 связанные таблицы то у меня будет только 3 запроса в базу а не 3N+1. Да, склейка на стороне приложения медленее нежели join на уровне базы, и если вдруг у меня будут какие-то проблемы с производительностью — мне будет не сложно подменить конкетную выборку на чистый SQL.
Или вы будете говорить что мне обязательно нужно жертвовать гибкостью системы в угоду производительности (с которой проблем, предположим нет).
Конечно же это не означает что построение отчетов стоит делать не через SQL — как раз таки наоборот. Но не стоит говорить что всегда стоит делать всю операцию на SQL за счет джойнов.
Если у вас есть ключи, то никакой жертвы гибкости (называемой обычно продуманной архитектурой) нет.
за целостностью вполне в состоянии следить само приложение. И не размазывать ответственность между собой и хранилищем.
В котором заодно может что-то упасть в процессе или быть закрыта страница и на выходе — неконсистентные данные. Надеюсь, вы занимаетесь разве что непопулярными магазинами.
Надеюсь перейдя на личности вы потешили свое эго и чувствуете себя лучше)
Что же до остального. Не должен никто лазить в хранилище просто так. Или вы все ограничения бизнес логики реализовываете на стороне бд или у вас двойные стандарты. А целостность при падениях приложения обеспечивается использованием транзакций. Вы ведь и так их должны использовать например для защиты целостности при записи в бд. Так что снова двойные стандарты (целостность существования записей вы поддерживаете приложением а целостность отсутствия данных почему то не можете)
Надейтесь, у меня же было это абсолютно искренняя надежда, потому как получить неконсистентное состояние в какой-нибудь важной отрасли и потерять деньги (или ещё что) было бы лично мне крайне неприятно.
> двойные стандарты
Проверки, это называется двойные проверки.
Надеюсь, вы занимаетесь разве что непопулярными магазинами.
вам лучше не знать как работает амазон)
Не понятно, почему способ выборки должен влиять на появление хвостов. Или я не правильные выводы из вашего высказывания выше сделал?
Вот @Fensor пишет что не пользуется FK и у него delete from Goods, и у него нет товаров, хотя в приложении он ничего такого не планировал.
не пользуется FK
там где это можно. Тут важно разделение на контексты. А гибкость достигается за счет меньшей связанности. То есть, скажем, вместо 1 запроса и 10-и джойнов я делаю 3 запроса и в каждом 3-4 джойна. Эти выборки между собой не пересекаются и склеиваются на уровне приложения. Да, это не самый эффективный способ с точки зрения производительности, но не всегда есть возможность данные держать в одной базе. Да и в целом все это делает код немного более гибким в плане изменений.
и у него delete from Goods
где я такое писал? Продукты можно снять с продажи — это не означает удаление данных. Как никак у нас должна быть отчетность.
где я такое писал
Вам и не надо, это напишет кто-то с консоли (админ, решивший почистить тестовую базу "где только товары" и перепутавший окошки) или автор какого-нибудь модуля. Придётся делать запрет удаления в настройках доступа, но тогда или усложнение настроек (FK попроще) или нельзя удалить из корзины (или каких временных таблиц) товары и она распухает.
это напишет кто-то с консоли
Мне кажется это какие-то проблемы с процессами...
Как минимум возможность малой кровью пернести одну из таблиц в другую базу, в том числе совсем другую типа монги, а то и вообще во внешнее хранилище.
Если под "хвостами" вы подразумеваете ситуации когда я делаю связи без FK, то… меня это не парит от слова совсем. Эти хвосты легко подчищаются, а отсутствие лишних FK (не все FK хороши но и без них вообще нельзя никак) позволяет мне уменьшить количество необходимых данных для тестирования фич в изоляции.
Кроме того — я не знаю как вы, а у меня товары не могут быть удалены. Могут быть сняты с продажи. не более того.
… уж если мы говорим о гибкости и о том, что с производительностью проблем нет, надо оставлять N+1, потому что это как раз лучше всего масштабируется (в том числе, за счет того, что все N запросов можно послать параллельно, и они еще и закэшируются где-нибудь).
является бесконечным и бесполезным холиваром без привязки к конкретной задаче
А без привязки к конкретной задаче так и есть. Fesor в своем комменте поменял условия задачи (сказав, что проблем с производительностью нет, и нужна гибкость системы) — поменялось и решение.
Задача из реальности. Есть 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?
Уууу, тут куча интересных и не всегда правильных вариантов:
- В качестве одного из значений вложенный подзапрос, который возвращает через CONCAT список картинок одним полем
- Денормализация какой либо разновидности
- ДВА запроса :) Первый выводит товары и данные из таблиц, которые впихиваются в одну ячейку. Второй берёт список IDшников из первого и отправляет запрос на получение картинок по тику WHERE ID IN (1,2,3....)
Если этот коммент заинтересует больше чем одного человека, я постараюсь перебороть лень и попробую накатать статью про обходные пути в работе SQL из своего небогатого опыта.
НУ и названия картинок товаров хранить в базе как-то так себе идея. Проще генерить им путь по id в файловой системе
Тут по сути без разницы, главное из БД получить пачку информации о картинках для тех записей, о которых что-то написано.
Потому что связь ид товара -> ид картинки можно реализовать на уровне файловой системы вообще:
if file_exists($product_id+".jpg")
но это корявенько при большом каталоге.
но это корявенько при большом каталоге.
Это нормально, файловый кеш на fs чаще всего работает вообще быстрее, чем джойны в БД на больших табличках
https://habrahabr.ru/post/157613/
похоже не всегда всё радужно :)
Я говорил — генерировать путь до картинок по id товара.
А как это уже другой вопрос. Хоть md5 и делить его на части. Не важно, главное «размазать» по папкам
У нас к примеру это довольно простой метод
/AC/000/486/486193.jpeg
где первая 2-х символьная папка это вообще размазанный на физические разные сервера маппингом путь. До 256 серверов
Для MSSQL (с XML костылями).
Для остального полагаю тоже есть.
FROM product p
LEFT JOIN product_image i ON (i.product_id = p.product_id)
GROUP BY p.product_id
FROM product p
LEFT JOIN product_image i ON (i.product_id = p.product_id)
GROUP BY p.product_id
Вы не сталкивались с большой нагрузкой. Если таблица товаро очень широкая и их нужно выбрать много, то джойн тут ужасное решение. Я не говорю уже о том, что если нужно будет кроме цен получить ещё какую-то информацию типа один ко многим. Пример: 1000 товаров, у каждого 10 цен и у каждого 10 признаков. В итоге ваш джойн это всё перемножит и у вас выйдет 100000 строк.
Жили с джойнами на десятки миллионов товаров помноженные на сотни аттрибутов. Полет нормальный даже на обычном mysql
Ваш аргумент был про перформанс и join, я привел свой опыт, как контраргумент.
Кластеры master-slave, на мастер до 7 слейвов, в зависимости от страны. Машинки не помню в деталях: 24 ядра, сколько оперативной памяти — не помню (не забываем, что я разработчик, а не dba).
1 млн товаров, цены, скидки, акции, купоны, атрибуты и прочее всякое.
Время ответа сервера с результатми поиска по любому критерию — 300мс.
Amazon t2.medium
Все очень просто. Каждый запрос это потеря времени на «подготовительные» и «заключительные» операции по обращению к БД.
Для этого используется кеш БД. Данные из которого будут дергаться, пока эти самые данные неизменны.
Так теперь, для выборки любого количества товаров нужно будет выполнить всего 2 запроса
Можно через 1.
select pp.*,p.* from product_price pp, product p where pp.product_id=p.product_id;
Затем просто на самом php расстасовать.
Если уж решили заморочиться, надо привести к 6й форме, разнеся в 3 таблицы (продукты + цены + типы).
И все на вложенных циклах. Генерация страницы категории с 99 товарами создавал более 700 запросов. Кэш, конечно бы помог, но отображение к кэшу производится тоже очень часто, т.к. в циклах.
Перешли на новую платформу — такая же беда. Приходиться исправлять.
Подождите, вы правда не знаете про оператор JOIN в SQL?
Вы не ответили на мой вопрос.
Без джойнов. Для страницы, на которой тысяча товаров. Хм… как? Или ваше решение ограничено "на странице мало товаров"?
Так как же вы обойдетесь без джойнов и без запросов в цикле в случае, когда вам надо выбрать страницу в тысячу товаров, и у каждого товара — десять картинок?
второй запрос находится вне цикла.
… и как вы выберете картинки только для той тысячи товаров, которая вам нужна?
$sql = 'select * from product_price where product_id in (' . implode(',' , $product_ids ). ')';Тысяча ID внутри IN. Без параметров, без всего, ага (привет, SQL-инъекции). Оптимизатор запросов на стороне SQL-сервера взял и взорвался, потому что он такое не может.
SQL иньекции в этот вопрос не входит. И в конкретном случае не возможна. Посмотрите внимательно код.
Жил с этим на лайве под highload. Нет не выигрывает. Решение ужасное и с точки зрения performance
Есть, конечно. Например, выборка по диапазону кластерных ключей.
Просто это нужно было делать вложенным запросом и на таскать туда-сюда данные которые прекрасно высчитываются на уровне рсубд в рамках одного запроса.
А 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
Способ в статье это часто используемое решение, с ORM запросы генерируются автоматически, и в результате будут готовые объекты с нужными полями, без всякого парсинга.
Да и не уверен я, что такая группировка с конкатом будет быстрее, чем 2 простых запроса без группировок.
Вы не поверите, но
SELECT ...
FROM Products p
LEFT OUTER JOIN ProductImages pi
ON p.Id = pi.ProductId
WHERE ...Ок, пусть будет джойн. Допустим, нужно 100 товаров, к каждому по 10 картинок. Информация о товаре 1 Кб. Средняя длина int id — 6-7 цифр. Передавать по сети лишних 0.9 Мб в одну сторону, вместо того чтобы передать лишних 700 байт в другую?
Передавать по сети лишних 0.9 Мб в одну сторону, вместо того чтобы передать лишних 700 байт в другую?
Ну, учитывая, что взамен мы получаем нормальный кэшируемый план запроса, одну короткую транзакцию и стриминг — да.
Кому не нравится, переходит на следующий уровень и делает UNION.
Есть некое эмпирическое знание, что чем больше в запросе параметров, тем сложнее с ним жить движку БД (а еще запрос с двумя параметрами в IN и с тремя может оказаться для движка разными запросами). Но я не удивлюсь, если это знание устарело, впрочем. Мне и остальных аргументов достаточно (собственно, мне одного стриминга достаточно).
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.
Я за такое один раз чуть не убил вьетнамского коллегу! Когда пришел запросик с 80К idшников.
За такое надо бить по рукам.
Сильный аргумент. Спасибо.
Кто сказал, что мы использовали php?
Странно видеть:
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 -- добавили пустую колонку что бы количество колонок в запросе было тоже две - как в первом запросе
В один присест получили наименование всех Продуктов и имена всех Менеджеров.
Насчет UNION — годный совет. Скажу честно, ни разу в голову не приходило. Надо тестить, есть подозрение, что работать это будет не очень быстро.
Во-первых, нужна дополнительная колонка, которая объяснит, какую таблицу вы получили. То есть,
select 'product', product_id, product_title from product
UNION
select 'manager', name, '' from managerВо-вторых, в этом методе придется писать какие-то обертки и делать слишком много неочевидной магии (за которую, собственно, так и ненавидят PHP).
В третьих, вам всегда придется указывать конкретное количество полей и помнить, в каком порядке они находятся. Если же вам придется добавить поле хотя бы в одну колонку — переписывать придется все запросы, которые находятся в UNION.
Мне кажется, в подобном случае сделать два отдельных запроса будет более очевидно и тестируемо, а еще лучше — использовать JOIN либо кэширование manager в оперативной памяти (я уверен, что этих менеджеров даже в самой большой фирме не больше двух-трех сотен, а на деле меньше 20, поэтому нужно просто сделать один раз запрос на менеджера и закэшировать это в Redis на несколько часов).
Недостаток — «Страшный» запрос со всеми вытекающими(отладка сложная, разработка еще сложнее)
Под сомнением — производительность. Что дешевле 1 сложный запрос с юнионами, или 5 запросов к каждому источнику данных?
Преимущество — количество запросов меньше. Вроде быстрее должно быть.
Очень частое заблуждение. Меньше запросов не всегда хорошо. Иногда 100 маленьких запросов быстрее одного большого, который сделает то же самое.
По своему опыту, если нужно получить таблицу менеджеров и продуктов — получил бы менеджеров отдельно, а продукты отдельно (учитывая, что там еще обычно нужно 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 или два отдельных запроса?..
Ну или магия хранимых процедур, а это вообще отдельная тема для разговора.
Под сомнением — производительность. Что дешевле 1 сложный запрос с юнионами, или 5 запросов к каждому источнику данных?
Про то и говорю. Надо проверить на практике. Недостаток очевиден — сложно, очень сложно управлять таким монстром будет.
"вроде"? Вы проверяли?
Тут "на глаз" не работает.
Действительно антипаттерн ещё и потому, что в случае ошибки в одном из запросов деления на ноль /неверного приведения типов/ и т.п. — долго и нудно ищем, в каком из запросов это произошло
$products[(int) $product['product_id']] = $product;
Более сложный пример, отсортировать по дате, по цене, по неведомой сортировке из совсем левой таблицы, по гороскопу, по <подставить своё>.
Сейчас я вижу только то, что формируется массив с товарами. где индексы — это ID товаров. И он будет автоматом сортироваться по возрастанию индекса.
Вся проблема статьи в элементарности тех вещей которые преподносятся как некое открытие. Очень по детски материал выглядит.
n+1 проблема древняя и всем известная
join и where in естественно широко используемые ее решения, в том числе и во всяких orm.
Так называемая жадная загрузка.
У вас же это преподносится как открытие
Вы исходите из того, что если люди предлагают другое решение, они не знают вашего?
А зря. Задача "давайте выберем все строчки со всеми связанными деталями" — она реально типовая, и у нее есть типовые же решения. Ваше — одно из них, и оно имеет ощутимые (перечисленные) недостатки. Решение c n + 1 — тоже типовое, и тоже имеет недостатки (но иногда работает лучше остальных, при этом). Наконец, после этого есть еще как минимум три решения (join; два запроса, где во втором используется join для критерия; и union по двум предыдущим) — и все они тоже имеют плюсы и минусы.
«давайте выберем все строчки со всеми связанными деталями»
Нет. Исходная задача другая. Товар-Цена имеет отношение «один-ко-многим». Нужно получить массив товаров, с полями «цена» и отдать куда-нибудь дальше для вывода-обработки. При этом уберем запросы к бд из цикла. Не больше и не меньше. Условия отбора товаров я оставил за пределами поста.
В задаче "мастер-детали" делать джойн может быть очень избыточно, если в "мастере", со стороны 1 в 1..N отношении данных в одной строке сильно больше чем в "деталях". Например, блоб с картинкой. Да и приводить из реляционного вида в иерархический результаты двух запросов куда проще, чем одного джойна.
Забыли добавить, что в общем случае производительность не константа, она меняется, её нужно измерять и выявлять узкие места. Собственно даже 1+N запрсов могут не быть узким местом и даже 1+1 будет преждевременной оптимизацией. Но вот вводдить джойн просто потому что увидел 1+N или 1+1 в коде — это точно преждевременная оптимизация, причём с привязкой к конкретному хранилищу или их типу, в большинстве случаев.
— Скажу от себя. Я себя не считаю экспертом в области БД (все пути неисповедимы), но опыт достаточен. Так вот: даже при бытовых задачах, выбрать структуру запроса бывает довольно сложнее, нежели в более изощренных. Я уже не говорю про HIGHT LOAD.
Тема очень тонкая в наше время.
А по поводу кода из различных CMS, так бывает, смотришь на какую нить реализацию корзины товаров и ужасаешься. Приходилось сталкиваться с различными реализациями «корзин», когда имел дело по интеграциям различных сервисов.
Это как пример, вариантов оптимизаций масса для каждого конкретного случая. Но отдать на откуп серверу то, для чего он создан, гораздо правильнее, чем гонять запросы в цикле.
даже не знаю, есть ли смысл перечислить возможные способы как этого избежать… их просто масса…
FROM product p
LEFT JOIN product_image i ON (i.product_id = p.product_id)
GROUP BY p.product_id
image — blob? ) а на стороне апп-сервера джсон с ними парсить?
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]);
Лэвел школьник 9 класса.
Как избежать SQL запросов внутри циклов