yesworld
@yesworld

Как оптимизировать вложенные запрос mysql?

Всем привет.

Я пытаюсь разгрузить один запрос или думаю вообще переделать архитектуру хранения записей. Надеюсь вы мне поможете определиться ;)

На проекте есть 3 таблицы: Объект, к которым принадлежат Детали и Материалы. Мне нужно получить: Список объектов, у которых есть кол-во (items_count) принадлежащих к нему Детали+Материалы, а так же получить последнюю дату обновлению записи, т.е. пройтись по этим таблицам (по полю last_update) и вытащить последнюю таймстамп дату GREATEST() .

Поле status = 1 , item доступен или нет.
SELECT
	`Ob`.`id` AS `id`,
	`Ob`.`title` AS `title`,
	`Ob`.`poster` AS `poster` ,
        @count_details := (SELECT count(*) FROM game.Details D WHERE D.objectId = Ob.id AND D.status = 1 ),
	@count_materials := (SELECT count(*) FROM game.Materials Ma WHERE Ma.objectId = Ob.id AND Ma.status = 1 ),
	@count_details + @count_materials as items_count,
	@max_update_details := (SELECT MAX(D.last_update) FROM game.Details D WHERE D.objectId = Ob.id AND D.status = 1 ),
	@max_update_materials := (SELECT MAX(Ma.last_update) FROM game.Materials Ma WHERE Ma.objectId = Ob.id AND Ma.status = 1 ),
	GREATEST(
			`Ob`.`last_update`, @max_update_details, ifnull(@max_update_materials, 0 )
	) as last_update
FROM game.`Object` Ob
WHERE status=1
ORDER BY last_update DESC;


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

Я думаю, что при сохранении формы добавления/изменения Details или Materials пересчитывать их кол-во к принадлежащему объекту. И заносить в Object таблицу, в какую нибудь новую колонку, а не делать каждый раз столько вложенных запросов. Тоже самое и касается время изменения last_update.

Еще наткнулся на один вопрос, где во вложенном запросе добавлена переменная @i в качестве инкремента. Можно ли сделать, чтобы на момент вычисления максимального значения, можно было подсчитать кол-во найденных записей, (псевдокод):
SELECT MAX(D.last_update), @i := @i +1 
FROM game.Details D 
WHERE D.objectId = Ob.id;


Забыл кстати скинуть результаты Explain:
id |select_type        |table |type |possible_keys                             |key                           |key_len |ref   |rows   |Extra                                        |
---|-------------------|------|-----|------------------------------------------|------------------------------|--------|------|-------|---------------------------------------------|
1  |PRIMARY            |Ob    |ALL  |                                          |                              |        |      |139    |Using where; Using temporary; Using filesort |
5  |DEPENDENT SUBQUERY |D     |ref  |IX_Details_status_last_update             |IX_Details_status_last_update |2       |const |25909  |Using where                                  |
4  |DEPENDENT SUBQUERY |Ma    |ref  |IX_Materials_status,IX_Materials_objectId |IX_Materials_status           |2       |const |125431 |Using where                                  |
3  |DEPENDENT SUBQUERY |D     |ref  |IX_Details_status_last_update             |IX_Details_status_last_update |2       |const |25909  |Using where                                  |
2  |DEPENDENT SUBQUERY |Ma    |ref  |IX_Materials_status,IX_Materials_objectId |IX_Materials_status           |2       |const |125431 |Using where                                  |


Вот схема Таблиц:
16171431db77435cb95d8031b189c2ed.png

Спасибо за внимание!
  • Вопрос задан
  • 686 просмотров
Решения вопроса 1
longclaps
@longclaps
Вот фрагмент твоего запроса
SELECT
  `Ob`.`id` AS `id`,
  @count_details := (SELECT count(*)
                     FROM game.Details D
                     WHERE D.objectId = Ob.id AND D.status = 1)
FROM game.`Object` Ob
WHERE status = 1;

А вот эквивалентный ему (надеюсь, а если чуть напортачил - поправить нетрудно)
SELECT
  `Ob`.`id` AS `id`,
  D.count_details
FROM game.`Object` Ob
  JOIN (SELECT objectId, count(*) AS count_details
        FROM game.Details AS gD
        WHERE gD.status = 1
        GROUP BY gD.objectId) AS D ON D.objectId = `Ob`.`id`
WHERE `Ob`.status = 1;

Выглядит более громоздко, зато прозрачен для планировщика/оптимизатора, и подзапрос в нём выполняется однократно, а не на каждый чих `Ob`.`id`.
Ответ написан
Пригласить эксперта
Ответы на вопрос 2
ThunderCat
@ThunderCat
{PHP, MySql, HTML, JS, CSS} developer
на вскидку:
- Первое, не вижу тут никаких особых условий которые бы прям критично увеличивали время выборки при росте объема данных. Разве что количество во всех таблицах будет серьезно за пару миллионов записей. Три плоские таблицы - при наличии индексов это все должно выбираться за 0,0Х секунды.
- Второе - как то замудрено имхо все это выбирается, визуально это чесание правой пяткой левого уха. Половину запросов можно произвести отдельно и отдать в бэк для арифметических операций. Сделать в 2-3 запроса, но читаемость и гибкость кода повысится в разы. Хотя это как диагностика двигателя по заглядыванию в выхлопную трубу - ну виден нагар, ну бензином воняет, но пока движок не посмотришь только общие советы можешь дать. Не видя таблиц и что как связанно(и не имея пару часов на подумать/потыкать) только вышеизложенное могу посоветовать )
Ответ написан
@BorisKorobkov Куратор тега MySQL
Web developer
Этот запрос выполняется на каждую страницу.
А добавление новой детали или материала - в тысячу раз реже.
Вывод: конечно, надо делать отдельные поля в объекте и пересчитывать их mysql-триггером при добавлении/удалении детали или материала
Ответ написан
Ваш ответ на вопрос

Войдите, чтобы написать ответ

Войти через центр авторизации
Похожие вопросы
SaveTime Москва
от 160 000 ₽
Blogman Санкт-Петербург
от 90 000 до 160 000 ₽
09 апр. 2020, в 11:17
200000 руб./за проект
09 апр. 2020, в 10:58
20000 руб./за проект
09 апр. 2020, в 10:33
2500 руб./за проект