Почему запрос в базу выполняется больше 15 минут?

Question

[Александр]

В магазине на престашопе очень дого начала загружаться страница заказчиков
при детальном рассмотрении выяснилось что проблема в медленном ответе базы.
Запрос на получение 50 строк с несколькими подзапросами может выполняться до 30 минут
Включил профилирование, выяснил, что львиную долю времени отжирает запрос в базу
Скопировав и выполнив SQL запрос в консоли - получил то же время
Получается что проблема не в скрипте, а в работе базы данных

сам запрос выглядит таким образом

SELECT SQL_NO_CACHE a.`id_customer`,
                    `firstname`,
                    `lastname`,
                    `email`,
                    a.`active` AS `active`,
                    `newsletter`,
                    `optin`
        ,
                    a.date_add,
                    gl.name    as title,
                    (
                        SELECT SUM(total_paid_real / conversion_rate)
                        FROM ps_orders o
                        WHERE o.id_customer = a.id_customer
                          AND o.id_shop IN (1)
                          AND o.valid = 1
                    )          as total_spent,
                    (
                        SELECT c.date_add
                        FROM ps_guest g
                                 LEFT JOIN ps_connections c ON c.id_guest = g.id_guest
                        WHERE g.id_customer = a.id_customer
                        ORDER BY c.date_add DESC
                        LIMIT 1
                    )          as connect
FROM `ps_customer` a
         LEFT JOIN ps_gender_lang gl ON (a.id_gender = gl.id_gender AND gl.id_lang = 1)
WHERE 1
  AND a.`deleted` = 0
ORDER BY `date_add` DESC
LIMIT 0, 50

я включил логгирование медленных запросов
но там ничто не вызывает подозрения

# Query_time: 0.000470  Lock_time: 0.000154  Rows_sent: 1  Rows_examined: 3
# Query_time: 0.000520  Lock_time: 0.000119  Rows_sent: 1  Rows_examined: 4
# Query_time: 0.002848  Lock_time: 0.000083  Rows_sent: 849  Rows_examined: 864
# Query_time: 0.000613  Lock_time: 0.000199  Rows_sent: 1  Rows_examined: 3
# Query_time: 0.000687  Lock_time: 0.000154  Rows_sent: 1  Rows_examined: 4
# Query_time: 0.005767  Lock_time: 0.000113  Rows_sent: 849  Rows_examined: 864
# Query_time: 0.000467  Lock_time: 0.000149  Rows_sent: 1  Rows_examined: 3
# Query_time: 0.000497  Lock_time: 0.000112  Rows_sent: 1  Rows_examined: 4
# Query_time: 0.009653  Lock_time: 0.006193  Rows_sent: 849  Rows_examined: 864
# Query_time: 0.000448  Lock_time: 0.000142  Rows_sent: 1  Rows_examined: 3
# Query_time: 0.000734  Lock_time: 0.000177  Rows_sent: 1  Rows_examined: 4
# Query_time: 0.005696  Lock_time: 0.000115  Rows_sent: 849  Rows_examined: 864

получается что тормоза возникают между запросами
что это моголо бы быть и как это можно отследить, не пойму
получаю результат запроса в консоли 50 rows in set (17 min 24.94 sec)

Answer 1

[Zolg]

Ну так и спросите об этом саму базу:
https://dev.mysql.com/doc/refman/5.5/en/explain.html
Wb умеет и диаграмки строить
https://dev.mysql.com/doc/workbench/en/wb-performa...

Comments

[Александр]

EXPLAIN отрабатывает нормально, вот результат
сорри, я с mysql не силен, не приходилось отлаживать
могу писать просты запросы, джойнить, сортировать ...но это впервые для меня, не знаю с какой стороны подойти

[Zolg]

Explain-то отрабатывает нормально (куда он денется). Но документацию на (то как интерпретировать результаты) explain почитайте (и статейки на хабре были). В using filesort по 5M строк нормального мало

[Александр]

Zolg, что то не понял откуда 5М строк
в легком шоке
у меня нет в базе столько

[Дмитрий]

Александр, В ps_connections сколько записей?

[Александр]

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

[Zolg]

Попробуйте
CREATE INDEX idx_date_add ON ps_connections(date_add);

CREATE INDEX idx_date_add ON ps_customer(date_add);

CREATE INDEX idx_deleted ON ps_customer(deleted);

И скажите, насколько полегчало. И как изменился explain

[Дмитрий]

Александр, Все ж я бы поправил запрос в соответствии с примером в моем ответе и проверил что нужные поля проиндексированы, а то статистика опять наберется и снова все будет тормозить

[Александр]

я просто удалил данные из ps_guests
и все взлетело
CREATE INDEX idx_date_add ON ps_connections(date_add);
сделать не могу, там тоже полмилиона строк
тоже удалю все, оставлю за последний месяц два, а так все в топку - сайту несколько лет - там такое накопилось и его никто не смотрит

[Zolg]

Почему не можете? Ну не за минуту индекс построится, да, но решит^W скроет проблему это лучше, чем удаление данных (которые потом опять накопятся)

[Zolg]

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

[Александр]

да, там есть свой посторитель запросов
то есть, если я сейчас создам индексы
то в будущем они будут создаваться автоматом?

[Zolg]

Те индексы, которые создадите естественно будут обновляться

[Дмитрий]

Zolg, Это сути не меняет, фреймворк тоже человек писал =)

Answer 2

[Дмитрий]

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

(
                        SELECT SUM(total_paid_real / conversion_rate)
                        FROM ps_orders o
                        WHERE o.id_customer = a.id_customer
                          AND o.id_shop IN (1)
                          AND o.valid = 1
                    )          as total_spent,

Попробуйте сначала перенести таблицу для этого поля в раздел FROM и потом уже соединять с таблицей a

SELECT SQL_NO_CACHE a.`id_customer`,
                    `firstname`,
                    `lastname`,
                    `email`,
                    a.`active` AS `active`,
                    `newsletter`,
                    `optin`,
                    a.date_add,
                    gl.name    as title,
                    total_spents.total_spent
FROM `ps_customer` a
         LEFT JOIN ps_gender_lang gl ON (a.id_gender = gl.id_gender AND gl.id_lang = 1)
         LEFT JOIN (
                        SELECT o.id_customer, SUM(total_paid_real / conversion_rate) as total_spent
                        FROM ps_orders o
                        WHERE o.id_customer IN (SELECT id_customer FROM `ps_customer` a WHERE a.deleted = 0)
                          AND o.id_shop IN (1)
                          AND o.valid = 1
                       GROUP BY o.id_customer
                    )     total_spents ON a.id_customer = total_spents.id_customer,
WHERE 1
  AND a.`deleted` = 0
ORDER BY `date_add` DESC
LIMIT 0, 50

То же самое и с другим подобным полем

Comments

[Александр]

у меня нет опыта с базами, но то что я увидел в коде движка, меня повергло в ужас :)