SQLinfo.ru - Все о MySQL

Форум пользователей MySQL

Задавайте вопросы, мы ответим

Вы не зашли.

#1 29.03.2018 18:13:12

Нечто
Участник
Зарегистрирован: 13.10.2014
Сообщений: 8

Оптимизация работы с минимальными ценами в прайс-листах магазина

Крик отчаяния.

Интернет-магазин имеет базу цен на товары.
Прайс-листов от поставщиков около двух десятков.
Количество цен в сумме около 4 миллионов.
Уникальных деталей получается около 1,5 миллионов.
Количество, разумеется, меняется при отключении/подключении поставщиков.
Например, один немецкий прайс-лист - 800 тысяч позиций.

База содержит две таблицы, одна с ценами -

`prices` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `part_name` varchar(255) NOT NULL,
  `part_models` varchar(512) NOT NULL,
  `part_num` varchar(64) NOT NULL,
  `partnum` varchar(64) NOT NULL,
  `quantity` int(11) NOT NULL,
  `price` int(11) NOT NULL,
  `manufacturer` varchar(32) NOT NULL,
  `order_num` int(1) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `order_num` (`order_num`),
  KEY `part_num` (`part_num`),
  KEY `part_models` (`part_models`),
  KEY `part_name` (`part_name`),
  KEY `partnum` (`partnum`),
  KEY `price` (`price`),
  KEY `quantity` (`quantity`),
  KEY `partnum_2` (`partnum`,`manufacturer`),
  KEY `price_part` (`partnum`,`price`),
  KEY `minsum` (`partnum`,`price`,`quantity`),
  KEY `manufacturer` (`manufacturer`)
) ENGINE=InnoDB


Поскольку на сайте должны быть отображены цены в формате "Такая-то штука продается по цене от ... рублей", существует вспомогательная таблица:

`totalsumsprices` (
  `partnum` varchar(24) NOT NULL,
  `price` int(9) DEFAULT NULL,
  `quantity` decimal(11,0) DEFAULT NULL,
  `order_num` smallint(2) DEFAULT NULL,
  KEY `partnum` (`partnum`)
) ENGINE=InnoDB


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

Проблема возникает собственно при обновлении данной таблицы.
В цикле пехапе делается следующее (во "временную" таблицу, которая потом подменяет основную):

INSERT INTO totalsumsprices0
              SELECT p.partnum, MIN(p.price), SUM(p.quantity),SUBSTRING_INDEX(GROUP_CONCAT(p.order_num ORDER BY p.price),',',1)
              FROM prices p
                LEFT JOIN pricepatterns pp
                USING(order_num)
                WHERE pp.dissite=0
              GROUP BY partnum LIMIT <...N...>,100000


Для каждого номера выбирается минимальная цена, общее доступное количество деталей по всем поставщикам, а также номер поставщика, у которого минимальная цена (условия заказчика). Основная нагрузка на базу идет собственно от SELECT. Лимит подобран опытным путем, меньшая выборка не уменьшает временные затраты, большая - увеличивает. Мастерхост эпизодически ругается, когда вместо 5-10 секунд этот запрос начинает вдруг исполняться 20 и больше (общая нагрузка на виртуальный хостинг от других пользователей видимо возрастает)...

Есть какое-то более изящное решение задачи при хотелках заказчика? Или продолжать мучиться с оптимизацией (с точки зрения хостера, но не своей)?
Ограничения на виртуальном хостинге у этого хостера известно-паршивые, но уходить оттуда опять заказчик не хочет.
Никаких LOAD DATA ... там не позволено, ограничения на выполнение пехапе тоже существенны, что делать - непонятно...

MySQL 5.6.

Отредактированно Нечто (29.03.2018 18:14:05)

Неактивен

 

#2 30.03.2018 01:00:23

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5838

Re: Оптимизация работы с минимальными ценами в прайс-листах магазина

1. уберите избыточные индексы
например, KEY `partnum` (`partnum`), излишен при наличии других индексов начинающихся на (`partnum`, ..) см FAQ №5

2. вместо
  KEY `minsum` (`partnum`,`price`,`quantity`),
попробуйте
  KEY `minsum` (`partnum`,`price`,`quantity`, order_num),

3. действительно ли нужны такие большие поля?
например, в таблице prices поле `partnum` определено как varchar(64), а во вспомогательной как varchar(24)
уменьшите размерность текстовых полей до действительно необходимого значения

Неактивен

 

#3 30.03.2018 14:50:22

Нечто
Участник
Зарегистрирован: 13.10.2014
Сообщений: 8

Re: Оптимизация работы с минимальными ценами в прайс-листах магазина

Все это, увы, полумеры, не влияющие на нагрузку сколь-нибудь заметно. Не смог нагуглить решений в принципе по данному вопросу... Например, купили движок магазина, вроде все хорошо сделано, но... Под прайс на 50 тысяч позиций, навскидку. То есть вот наличие магазина есть и все, вся структура базы, все запросы упираются в количество. Зальешь туда 500 000 и сайт умирает, даже запросы, кое-что оптимизировали, кое-что оптимизировать бесполезно, ибо там накручено 10 таблиц и все такое... Переписывал много. А у нас заказы, соответственно, цен очень много, на одну деталь разные, и вот для такого решения не видел, как поступают. Про отдельную таблицу минимальных цен вообще не встречал, неужто прямо из базы инфу берут на лету, для каждого постраничного вывода с кучкой фильтров штудируя миллионы записей?

Неактивен

 

#4 31.03.2018 00:53:08

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5838

Re: Оптимизация работы с минимальными ценами в прайс-листах магазина

Нечто написал:

Про отдельную таблицу минимальных цен вообще не встречал

это называется денормализация - встречается сплошь и рядом

Нечто написал:

Например, купили движок магазина, вроде все хорошо сделано, но... Под прайс на 50 тысяч позиций, навскидку. То есть вот наличие магазина есть и все, вся структура базы, все запросы упираются в количество. Зальешь туда 500 000 и сайт умирает

ограничения хостинга и/или кривой движок

Нечто написал:

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

ищите в чем именно причина тормозов и последовательно их устраняйте, см Поиск узких мест в производительности MySQL: ботанический определитель

Неактивен

 

#5 31.03.2018 10:59:47

Нечто
Участник
Зарегистрирован: 13.10.2014
Сообщений: 8

Re: Оптимизация работы с минимальными ценами в прайс-листах магазина

vasya написал:

денормализация

О. Спасибо.

vasya написал:

в чем именно причина тормозов и последовательно их устраняйте,[/url]

На виртуальном хостинге половина написанного неприменима...
Остальное пересмотрю, если что - придется изобретать велосипеды...
Проблема запроса

SELECT p.partnum, MIN(p.price), SUM(p.quantity),SUBSTRING_INDEX(GROUP_CONCAT(p.order_num ORDER BY p.price),',',1)
              FROM prices p
                LEFT JOIN pricepatterns pp
                USING(order_num)
                WHERE pp.dissite=0
              GROUP BY partnum

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

Отредактированно Нечто (31.03.2018 11:18:22)

Неактивен

 

#6 31.03.2018 11:54:24

klow
Старожил
Зарегистрирован: 06.12.2014
Сообщений: 411

Re: Оптимизация работы с минимальными ценами в прайс-листах магазина

LEFT JOIN не имеет смысла если есть условие WHERE pp.dissite=0
или убираем LEFT или условие.
Как вариант LEFT JOIN pricepatterns pp on p.order_num = pp.order_num and pp.dissite=0
Индексы на pp.order_num и pp.dissite есть?

Неактивен

 

#7 31.03.2018 18:38:10

Нечто
Участник
Зарегистрирован: 13.10.2014
Сообщений: 8

Re: Оптимизация работы с минимальными ценами в прайс-листах магазина

klow написал:

LEFT JOIN не имеет смысла если есть условие WHERE pp.dissite=0
или убираем LEFT или условие.
Как вариант LEFT JOIN pricepatterns pp on p.order_num = pp.order_num and pp.dissite=0
Индексы на pp.order_num и pp.dissite есть?

От джоина вообще избавился, перенеся признак dissite в ту же таблицу, что и цены. Теперь выборка происходит мгновенно, если ей не ставить LIMIT, и быстрее, если ставить...
Индексы есть на все, что только можно, видимо, надо наоборот убирать лишние... Хотя не уверен, что количество индексов отрицательно влияет на скорость SELECT'а...

Неактивен

 

#8 03.04.2018 09:11:04

Нечто
Участник
Зарегистрирован: 13.10.2014
Сообщений: 8

Re: Оптимизация работы с минимальными ценами в прайс-листах магазина

Оптимизация на VPS: убираем лишние индексы, убираем джоин, сокращаем размер полей, блаблабла
Оптимизация на вирт. хостинге: шо? запрос тормозит? напишем цикл на пехапе, чтобы вместо одного запроса INSERT SELECT было несколько тысяч простых INSERT!!! ... PROFIT.
Вчера гневного письма хостера о нагрузке не было. Просто теперь база несколько раз в день в эвей уходит. Грехи мои тяжкие.

Неактивен

 

#9 11.04.2018 09:47:54

Нечто
Участник
Зарегистрирован: 13.10.2014
Сообщений: 8

Re: Оптимизация работы с минимальными ценами в прайс-листах магазина

Узнал о представлениях. Рассчитывал, что, если создать каждый прайс отдельной таблицей (что гарантирует независимое обновление и мгновенную очистку), то по ним можно создать VIEW "как бы единой таблицы с ценами". Однако даже на двух мелких таблицах VIEW нещадно тупит, а добавишь чуть больше - и падает, видимо пытаясь создать временную таблицу MyISAM с индексами -

26 - Incorrect key file for table '/tmp/#sql_4e9_1.MYI'; try to repair it

Отредактированно Нечто (11.04.2018 09:48:12)

Неактивен

 

#10 11.04.2018 10:06:13

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5838

Re: Оптимизация работы с минимальными ценами в прайс-листах магазина

Представления (VIEW) в MySQL с производительностью плохо сочетаются, если алгоритм TEMPTABLE

Неактивен

 

#11 11.04.2018 11:59:31

Нечто
Участник
Зарегистрирован: 13.10.2014
Сообщений: 8

Re: Оптимизация работы с минимальными ценами в прайс-листах магазина

Да вообще плохо все выходит...
Теперь думаю над тем, как бы создать таблицу минимальных цен и суммарного количества деталей, если иметь несколько десятков таблиц, отдельную для каждого прайс-листа...
Около полутора миллионов артикулов, в одном прайсе могут идти под разными ценами, прайсы от 50 тыс. до 1 млн. записей...
Перекладываешь заботу на мускул - долгие запросы и падает, перекладываешь заботу на пехапе - выжирает память и падает...
Самое простое решение, конечно, вообще не показывать на сайте минимальные цены, но так очень хочется заказчику...

Отредактированно Нечто (11.04.2018 12:01:33)

Неактивен

 

#12 14.04.2018 09:15:30

Нечто
Участник
Зарегистрирован: 13.10.2014
Сообщений: 8

Re: Оптимизация работы с минимальными ценами в прайс-листах магазина

Для отчета, вдруг кто наткнется - сделал так.
Для каждого прайса создается отдельная таблица priceNNN. Соответственно, заливка делается в отдельную priceNNN_upload и по готовности таблица заменяет основную. Таблица с минимальными ценами по прайсу и общим количеством считается на основании списка самых ходовых позиций (около 70 тысяч), из которого выбираются все артикулы и одним запросом выбираются данные из всех прайсов. Запрос готовится циклом по номерам включенных прайсов. Аналогично потом подменяет основную.


INSERT INTO totalsumsprices_count (partnum,price,quantity,order_num)
SELECT partnum,MIN(price),SUM(quantity),SUBSTRING_INDEX(GROUP_CONCAT(order_num ORDER BY price),',',1) FROM
(
SELECT partnum,price,quantity,NNN order_num FROM price... WHERE partnum IN (...)
UNION
SELECT partnum,price,quantity,NNN order_num FROM price... WHERE partnum IN (...)
...
)
t1 GROUP BY partnum
 


Запрос вызовет вопросы при расширении прайсов из числа деталей, но пока работает и общий принцип устраивает.

Отредактированно Нечто (14.04.2018 09:16:48)

Неактивен

 

Board footer

Работает на PunBB
© Copyright 2002–2008 Rickard Andersson