mysql - 在同一查询中选择和更新

Question

我有两个表用户和用户登录历史记录。我需要报告特定用户登录系统的时间。该表包含数百万行数据。因此，运行嵌套查询来获取用户的登录次数会花费大量时间。

我正在尝试遍历所有用户并更新登录列。如何在一个查询中执行此操作？

架构是这样的:

用户表:

• id INT(10)
• 用户名 VARCHAR(7)
• 登录 INT(10)

user_logs 表:

• id INT(10)
• 用户标识 INT(10)
• login_date DATETIME(19)

http://sqlfiddle.com/#!9/dc4149

我正在运行这个查询

UPDATE users u
SET u.logins = (SELECT COUNT(*) 
                FROM user_logs 
                WHERE userid = u.id) 
LIMIT 1

这是行不通的。

有什么方法可以遍历用户并更新他们各自的登录计数？

我尝试使用 PHP 执行此操作，但因为表格非常大。一个接一个地执行此操作需要很长时间。

我可以通过命令行执行此操作吗？

Answer 1

更新应该花费很长时间，特别是如果您对两个表都有适当的索引。

试试这个:

UPDATE users u
INNER JOIN(SELECT ul.userid,count(1) as cnt FROM user_logs ul GROUP BY ul.userid) u2
 ON(u2.userid = u.id)
SET u.logins = u2.cnt

然后确保您具有以下索引:

users - (id,logins)
user_logins - (userid)

如果这没有帮助 - 尝试分两步执行此操作，使用子查询结果构建一个派生表，并通过它进行更新:

CREATE TABLE temp_for_update AS(
SELECT ul.userid,count(1) as cnt
FROM user_logs ul 
GROUP BY ul.userid);

CREATE INDEX YourIndex
ON temp_for_update (userid,cnt);

UPDATE users u
INNER JOIN temp_for_update u2
 ON(u2.userid = u.id)
SET u.logins = u2.cnt

这肯定会更快。