Архетиктура БД большого проекта

[hoipy]

Привет!
Делаю проект аналог price.ru и.д. суть проекта сравнение цен интернет магазинов.
Столкнулся с такой проблемой.

Есть таблица
catalog - каталог товаров (8к записей)
id|section_id|name

shop - магазины (1к записей)
id|name|active

offers - предложения (уже 10мил, записей)
shop_id|catalog_id|price
В таблице offers - предложения магазинов из xml, у 1 магазина может быть несколько предложений к 1 товару. Есть товары где все 1000 магазинов продают.


city - города (в которые доставляет товары магазин) 85к записей
shop_id|city_id
Тут хранится ID магазина и ID города, т.е. магазин сам выбирает куда доставляет товар


Нужно получить товары которые есть в городе "1"
Делаю так

SELECT catalog.id, catalog.name FROM catalog
INNER JOIN (
  SELECT catalog_id
  FROM offers, shop, city
  WHERE
    offers.shop_id=shop.id
    AND shop.active='yes'
    AND city.shop_id=shop.id
    AND city.city_id=1
  GROUP BY catalog_id
) AS offer ON(offer.catalog_id=catalog.id)
LIMIT 20

Запрос выполняется долго, что наверно логично, пока он сгруппирует все... а это запрос для выборки на самом сайте.

Мне нужно выбрать все товары на 1 стр 20 шт, которые продают магазины и доставляют в определенный город и с условием что магазин активный


Куда копать? может архитектура БД не правильная? Может кто-то имел опыт создания подобного проекта, подскажите.

 

[grigori]

вопрос настолько прямой, честный, грамотный, конкретный, с кодом, прямо вот хочется помочь как-то ... и не знаю что ответить!
hoipy, нет ответа, просто нет, это высшая математика.
Неправильно все - и архитектура, и запрос, и уровень сложности решения относительно уровня вопроса.
Посоветовать изучить SQL и научиться оптимизировать запросы ... у тебя есть 2 месяца?

 

[hoipy]

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

 

[grigori]

1. mysql очень плохо работает с подзапросами - он берет всю табличку на 10 млн записей, прокручивает ее через временный файл на диске несколько раз, это надо писать иначе, и вариантов много
2. ты не расписал индексы в таблицах, а в mysql надо уметь их готовить

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

я бы на твоем месте нанял того, кто это сделает

 

[hoipy]

1. как иначе? можешь озвучить пару вариантов (хотя бы в двух словах)
2.
Индексы: catalog
PRIMARY KEY (`id`),

Индексы: shop
PRIMARY KEY (`id`),
KEY `is_active` (`is_active`),

Индексы: offers
KEY `shop_id` (`shop_id`),
KEY `catalog_id` (`catalog_id`,`shop_id`),

Индексы: city
UNIQUE KEY `shop_id` (`shop_id`,`city_id`)

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

С explain сижу уже сутки, выжал все что смог, максимум выжал это 3сек, что много...

 

[WMix]

много товаров будет, вернее предложений?

 

[grigori]

WMix, уже 10 миллионов
а потом еще приедут характеристики с eav

 

[grigori]

Нужно получить товары которые есть в городе "1"

например, так:

SELECT DISTINCT c.id, c.name
FROM catalog c INNER JOIN offers o ON o.catalog_id=c.id
INNER JOIN shop s ON o.shop_id=s.id
INNER JOIN city ...
WHERE s.active='yes'
AND city.city_id=1

KEY `catalog_id` (`catalog_id`,`shop_id`),

скорее всего нужен обратный по `shop_id`,`catalog_id` потому что where по статусу магазина

Индексы: shop
PRIMARY KEY (`id`),
KEY `is_active` (`is_active`),

тут лучше композитный по id, active (надеюсь, active enum)

Индексы: catalog
PRIMARY KEY (`id`),

можно попробовать композитный по id, name, чтобы mysql сгруппировало по нему, и может быть придется дописать force index

если 90% товаров в default city, можно попробовать
create temporary table select distinct offers.catalog_id from city inner join shops ... offers ... limit ..., по ней построить индекс, и уже эту маленькую табличку заджойнить на catalog,
тут не угадать, надо пробовать на реальных данных с учетом объема оперативки,
а если offers будет еще расти - надо партицировать

много чего надо пробовать, а я роман тут писать не хочу, прости

 

[WMix]

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

 

[grigori]

надо смотреть бизнес-логику, если там real-time каталог с заказами - хрен ты что закешируешь

короче, это называется DBA, профессия такая, и она оплачивается

 

[WMix]

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

 

[grigori]

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

 

[WMix]

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

 

[флоппик]

1. mysql очень плохо работает с подзапросами

говорят, в 5.6 стало получше с этим, не?

 

[fixxxer]

Картинка для медитации:

 

[grigori]

+ все это подразумевает, что база настроена корректно, что маловероятно с учетом подзапроса в первом вопросе