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

Question

[yesworld]

Всем привет.

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

На проекте есть 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                                  |

Вот схема Таблиц:


Спасибо за внимание!

Answer 1

[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`.

Comments

[yesworld]

Если честно не совсем понял вашу выборку. :(

[longclaps]

yesworld: Если честно, не совсем понял, что непонятно )
Результат подзапроса - таблица из objectId и count_details==(количество записей с данным objectId при status = 1), этот результат джойнится с game.`Object`.

[yesworld]

longclaps: Хех.. как написал, так и получил ответ.. :)
Я не заметил objectId, у вложенного запроса. Пока мне идея нравится. :)
ща все протестирую..

[yesworld]

Вот что примерно получилось :)
НО, то ли я тупой, то ли мои лыжи не едут... Этот запрос хоть и работает быстрее, но он не все записи Object выводит. Не могу пока ошибку найти :(

SELECT
    `Ob`.`id` AS `id`,
    `Ob`.`title` AS `title`,
    (D.count_details + MA.count_materials) as count_items,
    GREATEST(MA.last_update,  Ob.last_update, D.last_update) as last_update
FROM game.`Object` Ob
  JOIN (SELECT objectId, count(*) AS count_details, Max(gD.last_update) as last_update
        FROM game.Details AS gD
        WHERE gD.status = 1
        GROUP BY gD.objectId
        ) AS D ON D.objectId = Ob.`id`
  JOIN (SELECT objectId, count(*) AS count_materials, Max(gM.last_update) as last_update
        FROM game.Materials AS gM
        WHERE gM.status = 1
        GROUP BY gM.objectId
        ) AS MA ON MA.objectId = Ob.`id`
WHERE Ob.status = 1
ORDER BY last_update DESC
;

[yesworld]

А, все, нашел, забыл LEFT! :(
Спасибо longclaps ! Очень выручил... Тут даже не выручил, а научил меня новой фишкой ;)
Думаю потом еще перепишу методы сохранения... ИМХО архитектуру нужно переделывать, такие выборки эт ненормально.

Answer 2

[ThunderCat]

на вскидку:
- Первое, не вижу тут никаких особых условий которые бы прям критично увеличивали время выборки при росте объема данных. Разве что количество во всех таблицах будет серьезно за пару миллионов записей. Три плоские таблицы - при наличии индексов это все должно выбираться за 0,0Х секунды.
- Второе - как то замудрено имхо все это выбирается, визуально это чесание правой пяткой левого уха. Половину запросов можно произвести отдельно и отдать в бэк для арифметических операций. Сделать в 2-3 запроса, но читаемость и гибкость кода повысится в разы. Хотя это как диагностика двигателя по заглядыванию в выхлопную трубу - ну виден нагар, ну бензином воняет, но пока движок не посмотришь только общие советы можешь дать. Не видя таблиц и что как связанно(и не имея пару часов на подумать/потыкать) только вышеизложенное могу посоветовать )

Comments

[yesworld]

Вся проблема в том, что иногда он действительно быстро обрабатывает запросы, но иногда этот запрос висит больше 3х минут, следовательно нджинкс кидает 504 таймаут. :(
А поднимать таймАут в конфиге больше чем 3 минуты, бредова.

Answer 3

[Boris Köln]

Этот запрос выполняется на каждую страницу.
А добавление новой детали или материала - в тысячу раз реже.
Вывод: конечно, надо делать отдельные поля в объекте и пересчитывать их mysql-триггером при добавлении/удалении детали или материала

Comments

[yesworld]

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

А триггеры, разве не медленно работают? Если честно не приходилось их использовать.

[Алексей]

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

[yesworld]

Алексей: Да я в курсе, что такое тригеры :)
Просто как-то на хабре встречал в комментах ( не помню уже какой статьи ), что триггеры замедляют работу БД. Не мои слова, и не проверял, так что согласиться и/или опровергнуть не могу.

[Алексей]

yesworld: естественно триггеры замедляют, ведь сравнивается время когда он выполняется и когда его вообще нет. Но они замедляют только в момент изменения данных, а это происходит намного реже, чем выборка из базы данных. А выборка уже берет фактически готовые подготовленные данные, и, благодаря тому, что не нужно делать вложенных запросов, то отрабатывается это намного быстрее.
С учетом того, что выборка происходит на несколько порядков чаще, чем изменение данных, то в целом система работает быстрее.

[yesworld]

Алексей: Согласен. Блин не могу решиться, переделать и сделать все по нормальному или пока так оставить.... Спасибо за ответ! Подумать нужно!