mysql - 优化SQL : Customers that haven't ordered for x days

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”子查询将不包括在内，仅用于测试。

这应该给我一个在特定日期有最后订单并且是时事通讯订阅者的用户列表。我特别怀疑 WHERE 子句中 NOT IN 部分和 order_count 子查询的正确性。

Answer 1

有几个问题:

A.在可索引列上使用函数

您正在通过将DATE(order_created)与某个常量进行比较来搜索订单。这是一个糟糕的想法，因为 a) DATE() 函数针对每一行 (CPU) 执行，并且 b) 数据库无法在列上使用索引(假设存在索引)

B.使用WHERE ID NOT IN (...)

使用 NOT IN (...) 几乎总是一个坏主意，因为优化器通常会遇到这种构造的问题，并且经常会导致计划错误。您几乎总是可以将其表示为带有 WHERE 条件的外连接，该条件使用连接列的 IS NULL 条件过滤未命中的情况(并增加了不需要的副作用) DISTINCT，因为只返回一次未命中)

C.太晚留下过滤掉大部分行的连接

越早通过不进行连接来屏蔽行就越好。您可以通过连接不太可能匹配的表来实现这一点，这些表位于连接表列表的前面，并且将非关键条件放入连接而不是 where 子句中以尽早排除行。无论如何，有一些优化器对此进行了优化，但我经常发现它们没有这样做

D.像瘟疫一样避免相关子查询!

您有几个相关的子查询 - 对主表的每一行执行的子查询。这确实是一个非常糟糕的主意。有时优化器可以将它们制作成联接，但为什么要依赖(希望)这一点呢？大多数相关子查询都可以表示为连接；你的例子也不异常(exception)。

考虑到上述内容，有一些具体的变化:

• o2 和 o4 是相同连接，因此可以完全省去 o4 - 只需在转换为连接后使用 o2
• DATE(order_created) = "2013-12-14" 应写为 order_created 介于“2013-12-14 00:00:00”和“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;

最后一行是如何使用 LEFT JOIN 实现与 NOT IN (...) 相同的效果

免责声明:未经测试。