Оптимизация SQL: клиенты, которые не заказывали в течение x дней

Question

Я создал этот SQL для того, чтобы найти клиентов, которые не заказывали в течение X дней.

Он возвращает набор результатов, так что этот пост в основном просто для того, чтобы получить второе мнение о нем и возможные оптимизации.

SELECT o.order_id,
       o.order_status,
       o.order_created,
       o.user_id,
       i.identity_firstname,
       i.identity_email,

  (SELECT COUNT(*)
   FROM orders o2
   WHERE o2.user_id=o.user_id
     AND o2.order_status=1) AS order_count,

  (SELECT o4.order_created
   FROM orders o4
   WHERE o4.user_id=o.user_id
     AND o4.order_status=1
   ORDER BY o4.order_created DESC LIMIT 1) AS last_order
FROM orders o
INNER JOIN user_identities ui ON o.user_id=ui.user_id
INNER JOIN identities i ON ui.identity_id=i.identity_id
   AND i.identity_email!=''
INNER JOIN subscribers s ON i.identity_id=s.identity_id
  AND s.subscriber_status=1
  AND s.subsriber_type=e
  AND s.subscription_id=1
WHERE DATE(o.order_created) = "2013-12-14"
  AND o.order_status=1
  AND o.user_id NOT IN
    (SELECT o3.user_id
     FROM orders o3
     WHERE o3.user_id=o.user_id
       AND o3.order_status=1
       AND DATE(o3.order_created) > "2013-12-14")

Ребята, можете ли вы найти какие-либо потенциальные проблемы с этим SQL? Даты вставляются динамически.

Конечный SQL, который я включил в работу, будет включать в себя только o.order_id, i.identity_id и o.order_count - этот order_count должен быть корректным. Другие выбранные поля и подзапрос 'last_order' не будут включены, это только для тестирования.

Это должно дать мне список пользователей, у которых есть их последний заказ в тот определенный день, и является подписчиком информационного бюллетеня. Я особенно сомневаюсь в правильности части NOT IN в предложении WHERE и подзапросе order_count.

Answer 1

Есть несколько проблем:

А. Использование функций на индексируемых столбцах

Вы ищете заказы, сравнивая DATE(order_created) с некоторой константой. Это ужасная идея, потому что а) DATE() функция выполняется для каждой строки (ЦП) и b) база данных не может использовать индекс для столбца (при условии, что он существует)

Б. Использование WHERE ID NOT IN (...)

Используя NOT IN (...) почти всегда плохая идея, потому что оптимизаторы обычно имеют проблемы с этой конструкцией и часто ошибаются в плане. Вы почти всегда можете выразить это как внешнее соединение с WHERE условие, что фильтры для промахов с использованием IS NULL условие для объединенного столбца (и добавляет побочную выгоду от необходимости DISTINCT потому что там только одна мисс вернулась)

C. Оставляя соединения, которые отфильтровывают большие участки строк слишком поздно

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

D. Избегайте взаимосвязанных подзапросов, таких как чума!

У вас есть несколько связанных подзапросов - те, которые выполняются для каждой строки основной таблицы. Это действительно невероятно плохая идея. Опять же, иногда оптимизатор может объединить их, но зачем полагаться (надеяться) на это. Большинство коррелированных подзапросов могут быть выражены как объединение; Вы примеры не исключение.

Учитывая вышесказанное, есть некоторые конкретные изменения:

• o2 и o4 - это одно и то же соединение, поэтому с o4 можно полностью отказаться - просто используйте o2 после преобразования в соединение
• DATE(order_created) = "2013-12-14" должно быть написано как order_created between "2013-12-14 00:00:00" and "2013-12-14 23:59:59"

Этот запрос должен быть тем, что вы хотите:

SELECT
    o.order_id,
    o.order_status,
    o.order_created,
    o.user_id,
    i.identity_firstname,
    i.identity_email,
    count(o2.user_id) AS order_count,
    max(o2.order_created) AS last_order
FROM orders o
LEFT JOIN orders o2 ON o2.user_id = o.user_id AND o2.order_status=1
LEFT JOIN orders o3 ON o3.user_id = o.user_id 
    AND o3.order_status=1
    AND o3.order_created >= "2013-12-15 00:00:00"
JOIN user_identities ui ON o.user_id=ui.user_id
JOIN identities i ON ui.identity_id=i.identity_id AND i.identity_email != ''
JOIN subscribers s ON i.identity_id=s.identity_id
  AND s.subscriber_status=1
  AND s.subsriber_type=e
  AND s.subscription_id=1
WHERE o.order_created between "2013-12-14 00:00:00" and "2013-12-14 23:59:59"
AND o.order_status=1
AND o3.order_created IS NULL -- This gets only missed joins on o3
GROUP BY
    o.order_id,
    o.order_status,
    o.order_created,
    o.user_id,
    i.identity_firstname,
    i.identity_email;

Последняя строка - как вы достигаете того же NOT IN (...) используя LEFT JOIN

Отказ от ответственности: не проверено.

Answer 2

Не могу прокомментировать результаты, так как вы не опубликовали ни одного объявления таблицы или примера данных, но ваш запрос имеет 3 коррелированных подзапроса, что может привести к его плохой работе (ОК, один из них для last_order и предназначен только для тестирования).

Исключение коррелированных подзапросов и замена их объединениями даст что-то вроде этого:

SELECT o.order_id,
        o.order_status,
        o.order_created,
        o.user_id,
        i.identity_firstname,
        i.identity_email,
        Sub1.order_count,
        Sub2.last_order
FROM orders o
INNER JOIN user_identities ui ON o.user_id=ui.user_id
INNER JOIN identities i ON ui.identity_id=i.identity_id
   AND i.identity_email!=''
INNER JOIN subscribers s ON i.identity_id=s.identity_id
  AND s.subscriber_status=1
  AND s.subsriber_type=e
  AND s.subscription_id=1
LEFT OUTER JOIN
(
    SELECT user_id, COUNT(*) AS order_count
    FROM orders 
    WHERE order_status=1
    GROUP BY user_id
) Sub1
ON o.user_id = Sub1.user_id
LEFT OUTER JOIN
(
    SELECT user_id, MAX(order_created) as last_order
    FROM orders 
    WHERE order_status=1
    GROUP BY user_id
) AS Sub2
ON o.user_id = Sub2.user_id
LEFT OUTER JOIN
(
    SELECT DISTINCT user_id
    FROM orders 
    WHERE order_status=1
    AND DATE(order_created) > "2013-12-14"
) Sub3
ON o.user_id = Sub3.user_id
WHERE DATE(o.order_created) = "2013-12-14"
  AND o.order_status=1
  AND Sub3.user_id IS NULL