MySQL Query слишком длинный и тратит много времени
Я новичок в стеке потока
Мой запрос занимает много времени (более 5 минут).
Может ли этот запрос стать быстрее?
Пожалуйста! Помоги мне! Я понятия не имею 🙁
У меня 2 стола Аутлет и продажи:
1. Выход: таблица с уникальными данными
— id_outlet
— name_outlet
2. Продажи: как деталь розетки (1 розетка с большим количеством данных)
— id_outlet
— msisdn (1 выход, имеющий более 1 msisdn)
— Дата
Итак, мне нужно получить данные:
1. Филиал
2. Количество Аутлетов
3. Количество активных торговых точек (эти данные я получаю при проверке существующих торговых точек в этом месяце)
3. Подсчет msisdn
4. Количество точек обратной проверки (я получаю эти данные из проверки существующей точки в прошлом месяце, но не существует в этом месяце)
5. Номер нового активного выхода (я получаю эти данные от проверки существующего выхода в этом месяце, но не существует в прошлом месяце)
6. Количество неактивных розеток (я получаю эти данные из проверки, что розетка не существует в прошлом месяце и в этом месяце)
7. Номер консистентной торговой точки (эти данные я получаю при проверке существующей торговой точки в этом и в прошлом месяце)
Это мой запрос:
SELECT branch br, COUNT(DISTINCT id_outlet) AS tot_outlet,
(SELECT COUNT(DISTINCT(id_outlet))
FROM outlet o
WHERE ".$outlet['status'][$i]." AND active=1 AND branch = br
AND id_outlet IN (SELECT DISTINCT id_outlet FROM sales WHERE DATE LIKE '".$thisMonth."%')) AS aktif,
(SELECT COUNT(msisdn) FROM sales s, outlet o
WHERE ".$outlet['status'][$i]." and s.date LIKE '".$thisMonth."%' AND o.active=1 AND s.id_outlet = o.id_outlet
AND s.branch=br) AS supply,
(SELECT COUNT(DISTINCT(id_outlet))
FROM outlet o
WHERE ".$outlet['status'][$i]." AND active=1 AND branch = br
AND id_outlet IN (SELECT DISTINCT id_outlet FROM sales WHERE DATE LIKE '".$lastMonth."%')
AND id_outlet NOT IN (SELECT DISTINCT id_outlet FROM sales WHERE DATE LIKE '".$thisMonth."%')) AS back_checking,
(SELECT COUNT(DISTINCT(id_outlet))
FROM outlet o
WHERE ".$outlet['status'][$i]." AND active=1 AND branch = br
AND id_outlet NOT IN (SELECT DISTINCT id_outlet FROM sales WHERE DATE LIKE '".$lastMonth."%')
AND id_outlet IN (SELECT DISTINCT id_outlet FROM sales WHERE DATE LIKE '".$thisMonth."%')) AS new_aktif,
(SELECT COUNT(DISTINCT(id_outlet))
FROM outlet o
WHERE ".$outlet['status'][$i]." AND active=1 AND branch = br
AND id_outlet NOT IN (SELECT DISTINCT id_outlet FROM sales WHERE DATE LIKE '".$lastMonth."%')
AND id_outlet NOT IN (SELECT DISTINCT id_outlet FROM sales WHERE DATE LIKE '".$thisMonth."%')) AS non_aktif,
(SELECT COUNT(DISTINCT(id_outlet))
FROM outlet o
WHERE ".$outlet['status'][$i]." AND active=1 AND branch = br
AND id_outlet IN (SELECT DISTINCT id_outlet FROM sales WHERE DATE LIKE '".$lastMonth."%')
AND id_outlet IN (SELECT DISTINCT id_outlet FROM sales WHERE DATE LIKE '".$thisMonth."%')) AS konsisten
FROM outlet o
WHERE ".$outlet['status'][$i]." AND active=1
GROUP BY branch
Решение
Избегая использования подзапросов внутри подзапросов, вы сможете сделать что-то вроде этого:
SELECT branch br, COUNT(DISTINCT id_outlet) AS tot_outlet,
(SELECT COUNT(DISTINCT(id_outlet))
FROM outlet o
INNER JOIN sales s2 ON o.id_outlet = s2.id_outlet AND s2.DATE LIKE '".$thisMonth."%'
WHERE ".$outlet['status'][$i]."AND o.active=1
AND o.branch = br) AS aktif,
(SELECT COUNT(msisdn) FROM sales s, outlet o
WHERE ".$outlet['status'][$i]."and s.date LIKE '".$thisMonth."%'
AND o.active=1
AND s.id_outlet = o.id_outlet
AND s.branch=br) AS supply,
(SELECT COUNT(DISTINCT(id_outlet))
FROM outlet o
INNER JOIN sales s1 ON o.id_outlet = s1.id_outlet AND s1.DATE LIKE '".$lastMonth."%'
LEFT OUTER JOIN sales s2 ON o.id_outlet = s2.id_outlet AND s2.DATE LIKE '".$thisMonth."%'
WHERE ".$outlet['status'][$i]."AND o.active=1
AND o.branch = br
AND s2.id_outlet IS NULL) AS back_checking,
(SELECT COUNT(DISTINCT(id_outlet))
FROM outlet o
LEFT OUTER JOIN sales s1 ON o.id_outlet = s1.id_outlet AND s1.DATE LIKE '".$lastMonth."%'
INNER JOIN sales s2 ON o.id_outlet = s2.id_outlet AND s2.DATE LIKE '".$thisMonth."%'
WHERE ".$outlet['status'][$i]."AND o.active=1
AND o.branch = br
AND s1.id_outlet IS NULL) AS new_aktif,
(SELECT COUNT(DISTINCT(o.id_outlet))
FROM outlet o
LEFT OUTER JOIN sales s1 ON o.id_outlet = s1.id_outlet AND s1.DATE LIKE '".$lastMonth."%'
LEFT OUTER JOIN sales s2 ON o.id_outlet = s2.id_outlet AND s2.DATE LIKE '".$thisMonth."%'
WHERE ".$outlet['status'][$i]."AND o.active=1
AND o.branch = br
AND s1.id_outlet IS NULL
AND s2.id_outlet IS NULL) AS non_aktif,
(SELECT COUNT(DISTINCT(o.id_outlet))
FROM outlet o
INNER JOIN sales s1 ON o.id_outlet = s1.id_outlet AND s1.DATE LIKE '".$lastMonth."%'
INNER JOIN sales s2 ON o.id_outlet = s2.id_outlet AND s2.DATE LIKE '".$thisMonth."%'
WHERE ".$outlet['status'][$i]." AND o.active=1 AND o.branch = br) AS konsisten
FROM outlet o
WHERE ".$outlet['status'][$i]." AND active=1
GROUP BY branch
Это все еще использует несколько подзапросов в SELECT, но в зависимости от предложения WHERE (т. Е. Значения $ outlet [‘status’] [$ i]) это может быть не так уж плохо.
Я знаю, что вы говорите, что сделали это, но это дало неправильные результаты, но без примеров данных и макетов таблиц я не могу проверить, как или почему это должно быть неправильно.
Можно сделать это, поместив объединения в основной запрос, по крайней мере, для замены последних 4 подзапросов:
SELECT branch br, COUNT(DISTINCT id_outlet) AS tot_outlet,
(SELECT COUNT(DISTINCT(id_outlet))
FROM outlet o
INNER JOIN sales s2 ON o.id_outlet = s2.id_outlet AND s2.DATE LIKE '".$thisMonth."%'
WHERE ".$outlet['status'][$i]."AND o.active=1
AND o.branch = br) AS aktif,
(SELECT COUNT(msisdn) FROM sales s, outlet o
WHERE ".$outlet['status'][$i]."and s.date LIKE '".$thisMonth."%'
AND o.active=1
AND s.id_outlet = o.id_outlet
AND s.branch=br) AS supply,
COUNT(DISTINCT IF(s1.id_outlet IS NOT NULL AND s2.id_outlet IS NULL, o.id_outlet, NULL) AS back_checking
COUNT(DISTINCT IF(s1.id_outlet IS NULL AND s2.id_outlet IS NOT NULL, o.id_outlet, NULL) AS new_aktif
COUNT(DISTINCT IF(s1.id_outlet IS NULL AND s2.id_outlet IS NULL, o.id_outlet, NULL) AS non_aktif
COUNT(DISTINCT IF(s1.id_outlet IS NOT NULL AND s2.id_outlet IS NOT NULL, o.id_outlet, NULL) AS konsisten
FROM outlet o
LEFT OUTER JOIN sales s1 ON o.id_outlet = s1.id_outlet AND s1.DATE LIKE '".$lastMonth."%'
LEFT OUTER JOIN sales s2 ON o.id_outlet = s2.id_outlet AND s2.DATE LIKE '".$thisMonth."%'
WHERE ".$outlet['status'][$i]." AND active=1
GROUP BY branch
Это полагается на COUNT (некоторое выражение), считая только ненулевые значения.
РЕДАКТИРОВАТЬ.
Один из вариантов — изменить вложенные подзапросы на не связанные запросы, к которым вы присоединяетесь. Затем они выполняются только один раз, а не один раз для возвращаемой строки. Недостатком является то, что объединение с результатом подзапроса не будет использовать индексы, поэтому объединение может быть не таким быстрым. Что более эффективно, зависит от объема данных.
Взяв мой предыдущий запрос и изменив поля atkif и supply для использования не связанных между собой подзапросов, я получаю следующее:
SELECT o.branch br,
COUNT(DISTINCT o.id_outlet) AS tot_outlet,
COALESCE(sub_aktif.aktif, 0) AS aktif,
COALESCE(sub_supply.supply, 0) AS supply,
COUNT(DISTINCT IF(s1.id_outlet IS NOT NULL AND s2.id_outlet IS NULL, o.id_outlet, NULL)) AS back_checking,
COUNT(DISTINCT IF(s1.id_outlet IS NULL AND s2.id_outlet IS NOT NULL, o.id_outlet, NULL)) AS new_aktif,
COUNT(DISTINCT IF(s1.id_outlet IS NULL AND s2.id_outlet IS NULL, o.id_outlet, NULL)) AS non_aktif,
COUNT(DISTINCT IF(s1.id_outlet IS NOT NULL AND s2.id_outlet IS NOT NULL, o.id_outlet, NULL)) AS konsisten
FROM outlet o
LEFT OUTER JOIN
(
SELECT o.branch, COUNT(DISTINCT(o.id_outlet)) AS aktif
FROM outlet o
INNER JOIN sales s
ON o.id_outlet = s.id_outlet
WHERE (o.STATUS LIKE 'MALL%' OR o.STATUS LIKE 'STREET%' OR o.STATUS LIKE 'TOP TEN%' OR o.STATUS LIKE 'LIFESTYLE%')
AND s.DATE LIKE '2014-10%' AND o.active=1
GROUP BY o.branch
) sub_aktif
ON sub_aktif.branch = o.branch
LEFT OUTER JOIN
(
SELECT s.branch, COUNT(msisdn) AS supply
FROM sales s
INNER JOIN outlet o
ON s.id_outlet = o.id_outlet
WHERE (o.STATUS LIKE 'MALL%' OR o.STATUS LIKE 'STREET%' OR o.STATUS LIKE 'TOP TEN%' OR o.STATUS LIKE 'LIFESTYLE%')
AND s.date LIKE '2014-10%' AND o.active=1
GROUP BY s.branch
) sub_supply
ON sub_supply.branch = o.branch
LEFT OUTER JOIN sales s1 ON o.id_outlet = s1.id_outlet AND s1.DATE LIKE '2014-09%'
LEFT OUTER JOIN sales s2 ON o.id_outlet = s2.id_outlet AND s2.DATE LIKE '2014-10%'
WHERE (o.STATUS LIKE 'MALL%' OR o.STATUS LIKE 'STREET%' OR o.STATUS LIKE 'TOP TEN%' OR o.STATUS LIKE 'LIFESTYLE%')
AND active=1
GROUP BY o.branch ;
Изменение исходного запроса, чтобы сделать это для всех полей, дает следующее:
SELECT o.branch br, COUNT(DISTINCT id_outlet) AS tot_outlet,
COALESCE(sub_aktif.aktif, 0) AS aktif,
COALESCE(sub_supply.supply, 0) AS supply,
COALESCE(sub_back_checking.back_checking, 0) AS back_checking,
COALESCE(sub_new_aktif.new_aktif, 0) AS new_aktif,
COALESCE(sub_non_aktif.non_aktif, 0) AS non_aktif,
COALESCE(sub_konsisten.konsisten, 0) AS konsisten
FROM outlet o
LEFT OUTER JOIN
(
SELECT o.branch, COUNT(DISTINCT(o.id_outlet)) AS aktif
FROM outlet o
INNER JOIN sales s
ON o.id_outlet = s.id_outlet
WHERE (o.STATUS LIKE 'MALL%' OR o.STATUS LIKE 'STREET%' OR o.STATUS LIKE 'TOP TEN%' OR o.STATUS LIKE 'LIFESTYLE%')
AND s.DATE LIKE '2014-10%' AND o.active=1
GROUP BY o.branch
) sub_aktif
ON sub_aktif.branch = o.branch
LEFT OUTER JOIN
(
SELECT s.branch, COUNT(msisdn) AS supply
FROM sales s
INNER JOIN outlet o
ON s.id_outlet = o.id_outlet
WHERE (o.STATUS LIKE 'MALL%' OR o.STATUS LIKE 'STREET%' OR o.STATUS LIKE 'TOP TEN%' OR o.STATUS LIKE 'LIFESTYLE%')
AND s.date LIKE '2014-10%' AND o.active=1
GROUP BY s.branch
) sub_supply
ON sub_supply.branch = o.branch
LEFT OUTER JOIN
(
SELECT o.branch, COUNT(DISTINCT(o.id_outlet)) AS back_checking
FROM outlet o
INNER JOIN sales s1
ON s1.id_outlet = o.id_outlet AND s1.DATE LIKE '2014-09%'
LEFT OUTER JOIN sales s2
ON s2.id_outlet = o.id_outlet AND s2.DATE LIKE '2014-10%'
WHERE (o.STATUS LIKE 'MALL%' OR o.STATUS LIKE 'STREET%' OR o.STATUS LIKE 'TOP TEN%' OR o.STATUS LIKE 'LIFESTYLE%') AND active=1
AND s2.id_outlet IS NULL
GROUP BY o.branch
) sub_back_checking
ON sub_back_checking.branch = o.branch
LEFT OUTER JOIN
(
SELECT o.branch, COUNT(DISTINCT(o.id_outlet)) AS new_aktif
FROM outlet o
LEFT OUTER JOIN sales s1
ON s1.id_outlet = o.id_outlet AND s1.DATE LIKE '2014-09%'
INNER JOIN sales s2
ON s2.id_outlet = o.id_outlet AND s2.DATE LIKE '2014-10%'
WHERE (o.STATUS LIKE 'MALL%' OR o.STATUS LIKE 'STREET%' OR o.STATUS LIKE 'TOP TEN%' OR o.STATUS LIKE 'LIFESTYLE%') AND active=1
AND s1.id_outlet IS NULL
GROUP BY o.branch
) sub_new_aktif
ON sub_new_aktif.branch = o.branch
LEFT OUTER JOIN
(
SELECT o.branch, COUNT(DISTINCT(o.id_outlet)) AS non_aktif
FROM outlet o
LEFT OUTER JOIN sales s1
ON s1.id_outlet = o.id_outlet AND s1.DATE LIKE '2014-09%'
LEFT OUTER JOIN sales s2
ON s2.id_outlet = o.id_outlet AND s2.DATE LIKE '2014-10%'
WHERE (o.STATUS LIKE 'MALL%' OR o.STATUS LIKE 'STREET%' OR o.STATUS LIKE 'TOP TEN%' OR o.STATUS LIKE 'LIFESTYLE%') AND active=1
AND s1.id_outlet IS NULL
AND s2.id_outlet IS NULL
GROUP BY o.branch
) sub_non_aktif
ON sub_non_aktif.branch = o.branch
LEFT OUTER JOIN
(
SELECT o.branch, COUNT(DISTINCT(o.id_outlet)) AS konsisten
FROM outlet o
INNER JOIN sales s1
ON s1.id_outlet = o.id_outlet AND s1.DATE LIKE '2014-09%'
INNER JOIN sales s2
ON s2.id_outlet = o.id_outlet AND s2.DATE LIKE '2014-10%'
WHERE (o.STATUS LIKE 'MALL%' OR o.STATUS LIKE 'STREET%' OR o.STATUS LIKE 'TOP TEN%' OR o.STATUS LIKE 'LIFESTYLE%') AND active=1
GROUP BY o.branch
) sub_konsisten
ON sub_konsisten.branch = o.branch
WHERE (o.STATUS LIKE 'MALL%' OR o.STATUS LIKE 'STREET%' OR o.STATUS LIKE 'TOP TEN%' OR o.STATUS LIKE 'LIFESTYLE%') AND active=1
GROUP BY o.branch;
Sql скрипка здесь: —
Другие решения
Несколько шагов, чтобы диагностировать это самостоятельно.
Объясните свой запрос.
Чтобы объяснить ваш запрос, это довольно просто; вам просто нужно добавить EXPLAIN напротив SELECT часть.
Запуск этого;
EXPLAIN SELECT branch br, COUNT(DISTINCT id_outlet) AS tot_outlet,
(SELECT COUNT(DISTINCT(id_outlet))
FROM outlet o
WHERE ".$outlet['status'][$i]." AND active=1 AND branch = br
AND id_outlet IN (SELECT DISTINCT id_outlet FROM sales WHERE DATE LIKE '".$thisMonth."%')) AS aktif,
(SELECT SUM(featurephone) FROM outlet o
WHERE ".$outlet['status'][$i]."AND active=1 AND branch = br) AS feature,
(SELECT SUM(smartphone) FROM outlet o
WHERE ".$outlet['status'][$i]."AND active=1 AND branch = br) AS smart,
(SELECT COUNT(msisdn) FROM sales s, outlet o
WHERE ".$outlet['status'][$i]." and s.date LIKE '".$thisMonth."%' AND o.active=1 AND s.id_outlet = o.id_outlet
AND s.branch=br) AS supply,
(SELECT COUNT(DISTINCT(id_outlet))
FROM outlet o
WHERE ".$outlet['status'][$i]." AND active=1 AND branch = br
AND id_outlet IN (SELECT DISTINCT id_outlet FROM sales WHERE DATE LIKE '".$lastMonth."%')
AND id_outlet NOT IN (SELECT DISTINCT id_outlet FROM sales WHERE DATE LIKE '".$thisMonth."%')) AS back_checking,
(SELECT COUNT(DISTINCT(id_outlet))
FROM outlet o
WHERE ".$outlet['status'][$i]." AND active=1 AND branch = br
AND id_outlet NOT IN (SELECT DISTINCT id_outlet FROM sales WHERE DATE LIKE '".$lastMonth."%')
AND id_outlet IN (SELECT DISTINCT id_outlet FROM sales WHERE DATE LIKE '".$thisMonth."%')) AS new_aktif,
(SELECT COUNT(DISTINCT(id_outlet))
FROM outlet o
WHERE ".$outlet['status'][$i]." AND active=1 AND branch = br
AND id_outlet NOT IN (SELECT DISTINCT id_outlet FROM sales WHERE DATE LIKE '".$lastMonth."%')
AND id_outlet NOT IN (SELECT DISTINCT id_outlet FROM sales WHERE DATE LIKE '".$thisMonth."%')) AS non_aktif,
(SELECT COUNT(DISTINCT(id_outlet))
FROM outlet o
WHERE ".$outlet['status'][$i]." AND active=1 AND branch = br
AND id_outlet IN (SELECT DISTINCT id_outlet FROM sales WHERE DATE LIKE '".$lastMonth."%')
AND id_outlet IN (SELECT DISTINCT id_outlet FROM sales WHERE DATE LIKE '".$thisMonth."%')) AS konsisten
FROM outlet o
WHERE ".$outlet['status'][$i]." AND active=1
GROUP BY branch
Это даст вывод, похожий на этот:
Чтобы кратко объяснить, что вы должны искать. Главное на что посмотреть type, possible_keys а также key, Основное существо type,
Значение Type — это то, что говорит вам, как он находит запрос. Список (в порядке от лучшего к худшему): system, const, eq_ref, ref, fulltext, ref_or_null, unique_subquery, index_subquery, range, index, all, Вы не хотите ничего, что все; и, скорее всего, смотрят в основном на ref и eq_ref. Ваши могут иметь подзапросы из-за структуры запроса.
Чтобы это исправить, я смотрю на структуру таблицы и запрос. Все, что получит выгоду от наличия в отсортированном списке (что в сущности является индексом), индексируется. Для меня это означает, что я буду индексировать любые идентификаторы внешних ключей (даже если вы не используете внешние ключи, настройте индекс); любые атрибуты, такие как удаленный, видимый или любой другой.
Чтобы сделать это просто, я индексирую большинство вещей, которые сравниваются в where часть запроса. Это хорошее начало; оттуда вы действительно должны оптимизировать вещи, потому что индексы могут использовать ОДНО объем памяти, но это хорошее начало для повышения производительности.