mysql - 寻找重新参与的用户的最有效方法

Question

我想获取过去 24 小时内的所有活跃用户，并查看他们之前是否与我们互动过。我有以下查询:

SELECT $                                                                                                                                                                             
    DISTINCT user_id $                                                                                                                                                               
FROM $                                                                                                                                                                               
    activity $                                                                                                                                                                
WHERE user_id in (SELECT $                                                                                                                                                           
                      DISTINCT user_id $                                                                                                                                             
                  FROM $                                                                                                                                                             
                      activity $                                                                                                                                              
                  WHERE $                                                                                                                                                            
                      date > DATE_SUB(NOW(), INTERVAL 24 HOUR)) AND $                                                                                                                
      date < DATE_SUB(NOW(), INTERVAL 24 HOUR);

我有 user_id 和日期的索引。可以提高效率吗？

Answer 1

你能试试这个吗？

SELECT DISTINCT user_id
FROM activity INNER JOIN
    (
        SELECT DISTINCT user_id
        FROM activity
        WHERE date > DATE_SUB(NOW(), INTERVAL 24 HOUR)
            AND date < DATE_SUB(NOW(), INTERVAL 24 HOUR
    ) x ON x.user_id = activity.user_id

或者

SELECT DISTINCT user_id
FROM activity a1
WHERE EXISTS (
    SELECT 1
    FROM activity a2
    WHERE a2.user_id = a1.user_id
      date > DATE_SUB(NOW(), INTERVAL 24 HOUR))
      AND date < DATE_SUB(NOW(), INTERVAL 24 HOUR
);