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

Нечто · 29.03.2018 18:13:12

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

Интернет-магазин имеет базу цен на товары.
Прайс-листов от поставщиков около двух десятков.
Количество цен в сумме около 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)

vasya · 30.03.2018 01:00:23

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)
уменьшите размерность текстовых полей до действительно необходимого значения

Нечто · 30.03.2018 14:50:22

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

vasya · 31.03.2018 00:53:08

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

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

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

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

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

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

Нечто · 31.03.2018 10:59:47

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)

klow · 31.03.2018 11:54:24

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 есть?

Нечто · 31.03.2018 18:38:10

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'а...

Нечто · 03.04.2018 09:11:04

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

Нечто · 11.04.2018 09:47:54

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

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

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

vasya · 11.04.2018 10:06:13

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

Нечто · 11.04.2018 11:59:31

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

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

Нечто · 14.04.2018 09:15:30

Для отчета, вдруг кто наткнется - сделал так.
Для каждого прайса создается отдельная таблица 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)

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

#1 29.03.2018 18:13:12

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

#2 30.03.2018 01:00:23

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

#3 30.03.2018 14:50:22

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

#4 31.03.2018 00:53:08

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

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

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

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

#5 31.03.2018 10:59:47

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

vasya написал:

vasya написал:

#6 31.03.2018 11:54:24

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

#7 31.03.2018 18:38:10

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

klow написал:

#8 03.04.2018 09:11:04

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

#9 11.04.2018 09:47:54

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

#10 11.04.2018 10:06:13

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

#11 11.04.2018 11:59:31

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

#12 14.04.2018 09:15:30

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

Board footer