mysql查询缓慢地试图根据第二个表中的行在一个表中查找“缺失”记录。

Question

背景：

一个电子商务网络应用程序，但与“会员”谁订购对方。当成员执行各种活动(如搜索)时，记录将被插入到“Activity”表中。如果/当成员提交订单时，会将记录插入到“order”表中。目标是找到这样的情况:一个成员执行了一个活动，但是没有在某个时间窗口(例如，一个小时)内在活动时间之后下订单。

注意:不能更改创建订单记录的代码。如果可以的话，我可以简单地“记住”这些活动，并将这些信息包含在订单记录中。然后，要找到一个成员执行某个活动但没有排序的情况很简单:只需在order表的这个列中查找一个空值(或其他一些默认值)。再一次，唉，这在我的情况下是不可能的.

表：

• Order (id，ts /*时间戳*/，sending_member_id，receiving_member_id，…)
• Member (id，name，…)
• Activity_Type (id，name，…)
• Activity_Log (id，ts，member_id，type_id，extra_info)

指数：

All appropriate indexes are in place. Specifically, an index on order.ts does exist.

我尝试过以下三个查询：

方法1

SELECT …
  FROM activity_log, 
       Member
 WHERE activity_log.member_id = member.id
   AND activity_log.type_id = 1 /* Search */
   AND activity_log.ts > [start time]
   AND activity_log.ts < [end time]
   AND NOT EXISTS (SELECT ‘x’
                     FROM order
                    WHERE order.ts >= activity_log.ts
                      AND order.ts <= activity_log.ts + 3600
                      AND order.sending_member_id = activity_log.member_id)
ORDER BY activity_log.member_id, activity_log.ts desc

方法2

SELECT …
  FROM activity_log, member
 WHERE activity_log.member_id = member.id
   AND activity_log.type_id = 1 /* Search */
   AND activity_log.ts > [start time]
   AND activity_log.ts < [end time]
   AND activity_log.member_id NOT IN (SELECT order.sending_member_id
                                        FROM order
                                       WHERE order.ts >= activity_log.ts
                                         AND order.ts <= activity_log.ts + 3600)
ORDER BY activity_log.member_id, activity_log.ts desc

方法3

   SELECT …
     FROM activity_log
     JOIN member ON activity_log.member_id = member.id
LEFT JOIN order ON order.ts >= activity_log.ts 
               AND order.ts <= activity_log.ts + 3600 
               AND activity_log.member_id = order.sending_member_id
    WHERE activity_log.type_id = 1 /* Search */
      AND activity_log.ts > [start time]
      AND activity_log.ts < [end time]
      AND order.sending_member_id IS NULL
 ORDER BY activity_log.member_id, activity_log.ts desc

即使使用第3种方法，查询也会运行20-30秒，并且不会在order.ts上使用索引。

Answer 1

然后，要找到一个成员执行某个活动但没有排序的情况很简单:只需在order表的这个列中查找一个空值(或其他一些默认值)。再一次，唉，这在我的情况下是不可能的.

如果你真的做不到，那我就不敢说没有魔法子弹了。

除了重新访问您的模式(正如您正确诊断的那样，这是正确的方法)，您最好的选择将是一个反连接(即左连接.是空的)。

在没有任何约束的情况下(例如，在最近的订单/活动日期的子集上)，您基本上是将两个表连接在一起(在您的例子中，是三个，但我怀疑您可以删除成员上的一个)。

最好的方法是将自己限制在订单和活动日志上的时间戳子集上。这将使您可以减少联接大小。要实现这一点，您可能需要引入具有左侧约束和右侧join子句的多colum索引，例如activity_log(ts，member_id)和orders(ts，member_id)，或者根据数据的不同引入列。

Answer 2

对于MySQL，选择取决于所比较的列：

• 如果为空，NOT存在和join(对NULLS进行附加检查)是在MySQL中实现反连接的最有效方法。.
• 如果不为空，，使用左联接/ NULL或NOT而不是不存在。

如果您更改数据以进行比较，则索引将毫无用处：

AND order.ts <= activity_log.ts + 3600 

在本例中，ACTIVITY_LOG.ts上的索引并不重要。考虑复合索引(单个索引，多列)。

Answer 3

FYI，我修改了上面的第三个查询，如下所示，当开始时间结束时间窗口相对于activity_log和order表中的行表示的整个时间范围很小时，它大大加快了我的查询速度。基本上，MySQL决定是否在每个表上使用索引(在ts上)是根据索引的有用性.如果我要求“所有时间”，MySQL非常正确地不使用索引。但是，如果我要求的持续时间很短，MySQL最终会使用这些索引。

SELECT …      
FROM activity_log JOIN member 
  ON activity_log.member_id = member.id 
LEFT JOIN (select * from order where order.ts > [start time] and order.ts < [end time + 3600]) orders
  ON order.ts >= activity_log.ts
    AND order.ts <= activity_log.ts + 3600
    AND activity_log.member_id = order.sending_member_id
WHERE activity_log.type_id = 1 /* Search */
AND activity_log.ts > [start time]
AND activity_log.ts < [end time]
AND order.sending_member_id IS NULL
ORDER BY activity_log.member_id, activity_log.ts desc 

我不需要新的索引endts列。

非常感谢那些回应我的人，尤其是丹尼斯，他们的评论让我想到了这个解决方案。-M