Оптимизация MySQL SELECT запроса

282

22 июня 2017, 04:21

Друзья. Имеется следующий запрос:

SELECT CONCAT(p_sery, p_id) AS sery, 
(SELECT COUNT(1) FROM registry_regulations WHERE registry_id = a.p_id) AS regulations_count, 
(SELECT COUNT(1) FROM delivery WHERE p_id IN (SELECT regulation_id FROM registry_regulations WHERE registry_id = a.p_id) AND p_delivery_result = 1) AS delivery_count, 
(SELECT COUNT(1) FROM delivery WHERE p_id IN (SELECT regulation_id FROM registry_regulations WHERE registry_id = a.p_id) AND p_delivery_result = 2) AS ND1, 
(SELECT COUNT(1) FROM delivery WHERE p_id IN (SELECT regulation_id FROM registry_regulations WHERE registry_id = a.p_id) AND p_delivery_result = 3) AS ND2, 
(SELECT COUNT(1) FROM delivery WHERE p_id IN (SELECT regulation_id FROM registry_regulations WHERE registry_id = a.p_id) AND p_delivery_result = 4) AS ND3, 
(SELECT COUNT(1) FROM delivery WHERE p_id IN (SELECT regulation_id FROM registry_regulations WHERE registry_id = a.p_id) AND p_delivery_result = 5) AS ND4, 
(SELECT COUNT(1) FROM delivery WHERE p_id IN (SELECT regulation_id FROM registry_regulations WHERE registry_id = a.p_id) AND p_delivery_result = 6) AS ND5, 
(SELECT COUNT(1) FROM delivery WHERE p_id IN (SELECT regulation_id FROM registry_regulations WHERE registry_id = a.p_id) AND p_delivery_result = 7) AS ND6, 
(SELECT COUNT(1) FROM delivery WHERE p_id IN (SELECT regulation_id FROM registry_regulations WHERE registry_id = a.p_id) AND p_delivery_result = 8) AS ND7 
FROM registries AS a WHERE (SELECT STR_TO_DATE(a.p_date_created, '%Y-%m-%d') BETWEEN '2017-04-01' AND '2017-06-01');

И следующая структура таблиц:

Как можно оптимизировать данный запрос, на сервере он выполняется более 30 минут, очень много, все индексы имеются.
Результат EXPLAIN SELECT - на тестовом сервере записей в разы меньше чем на продакшн сервере:

Answer 1

Без примера данных сложно написать корректный запрос, для начала необходимо отказаться от подзапросов в SELECT. Приблизительно должно быть так:

SELECT
    CONCAT(p_sery, p_id) AS sery, 
    COUNT(DISTINCT rr.id) as regulations_count, 
    SUM(d.p_delivery_result = 1) AS delivery_count,
    SUM(d.p_delivery_result = 2) AS ND1,
    SUM(d.p_delivery_result = 3) AS ND2,
    SUM(d.p_delivery_result = 4) AS ND3,
    SUM(d.p_delivery_result = 5) AS ND4,
    SUM(d.p_delivery_result = 6) AS ND5,
    SUM(d.p_delivery_result = 7) AS ND6,
    SUM(d.p_delivery_result = 8) AS ND7,
FROM registries AS r
JOIN registry_regulations AS rr on r.p_id = rr.registry_id
JOIN delivery AS d ON d.p_id = rr.regulation_id
WHERE r.p_date_created >= '2017-04-01' AND r.p_date_created < '2017-06-01'+interval 1 day
GROUP BY CONCAT(p_sery, p_id);

Answer 2

Раз, ваш запрос не использует индексы в таблице registries по колонке p_date_created.

В вашем плане запроса это прекрасно видно:

Эта часть запроса будет выполняться для каждой строки в таблице:

WHERE (SELECT STR_TO_DATE(a.p_date_created, '%Y-%m-%d') 
       BETWEEN '2017-04-01' AND '2017-06-01');

Ускорить в таких случаях можно добавив отдельную колонку под дату и сделав под неё индекс. В вашем случае колонка уже есть, непонятно только с индексами.

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

WHERE a.p_date_created BETWEEN '2017-04-01' AND '2017-06-01'

Два, неясна ситуация с индексом p_id. Из плана запроса можно сделать вывод что индекса по этой колонке нет.

Поможет переписать запрос или вообще делать всю работу вне MySQL, делая сколько нужно отдельных запросов, кешируя по возможности всё, что можно.

ПОСЛЕДНИЕ ВОПРОСЫ

Все вопросы

ТОП-10

Все популярные вопросы

Как скопировать данные из одной ячейки в другую?

Но вот только с одним условием если ячейка в которую копируют равна nullИ так есть таблица goods в кторой есть столбцы create_at и update_at

271

MySQL

Изменить условие INNER JOIN, если будет NULL

Чисто для вашего понимания напишу рандомные таблицы:

228

MySQL

не могу понять как реализовать файловую систему sql

Не хочу сделать на сайте для пользователей файловую систему папка+файл например пользователь создал папку "home" в ней папка "user" и так далее...

240

MySQL

Как лучше хранить данные в БД?

Стоит задача создать магазин с разными предметамиЯ решил каждому предмету давать уникальный ID и если человек купил что либо то записывать...

292