php - 最后一个事件早于 X 天的 Mysql 返回 ID

Question

我有一个日志标签，其中存储了每个用户事件。

UserActivityTable(大约 1500 万条记录)

  id    userID    category           value                 timestamp 
    1        2         Visit          homepage          2018-02-21 13:13:54
    1        2         Visit          page2             2018-02-18 13:13:45
    1        2         Visit          page1             2018-02-15 13:13:30
    1        3         Visit          homepage          2018-02-01 13:13:12

通过 SQL 查询，如果用户设置为“事件”，我需要获取最后一次事件早于 X 天(比如说 30 天)的所有用户 ID

用户(大约 15k 用户)

id     Groups     Active   Name    Mails ...
2      Customer    1       Hans
3      Customer    0       Wurst

如果我让所有用户都处于事件状态(大约 5k)，而不是尝试到达最后一个事件，我会遇到超时(我认为查询不是 perfomant)如果我将它限制为 5，则没有问题。

我的尝试。

1 选择所有事件的用户，而不是使用 foreach 函数获取最后一个事件，如果它超过 30 天，我将它写在新数组中，最后我使用该数组设置用户表中的事件为假。

直到最近 2-3 个月还不错，但现在我们有很多新用户，功能无法处理。

有没有一种干净的方法可以在一个 sql 查询中获取所有这些东西？

Answer 1

您可以使用以下查询获取Users:

SELECT `userID`, MAX(`timestamp`) AS lastActive FROM `UserActivityTable` 
WHERE `userID` IN (
    SELECT `id` FROM `Users` WHERE `Active` = 1
) GROUP BY `userID` HAVING lastActive < DATE_SUB(NOW(), INTERVAL 30 DAY)

索引

• 你应该使用 PRIMARY KEY Users 表上的索引。
• 你应该使用 FOREIGN KEY UserActivityTable 表上的索引。
• 要加快上面的查询，您可以在 timestamp 列上创建列索引。

您可以使用以下内容创建 INDEX在 timestamp 列上:

CREATE INDEX index_timestamp ON `UserActivityTable` (`timestamp`);

---

您还可以使用单个查询来UPDATEUsers 表上的active 状态:

UPDATE `Users` SET `active` = EXISTS (
    SELECT `userID` FROM `UserActivityTable` WHERE `UserActivityTable`.`userID` = `Users`.`id` GROUP BY `UserActivityTable`.`userID` HAVING MAX(`UserActivityTable`.`timestamp`) > DATE_SUB(NOW(), INTERVAL 30 DAY)
)