在不终止服务器的情况下更新用户排名的最佳方法 [英] Best way to update user rankings without killing the server

问题描述

我有一个网站，其中以用户排名为中心，但用户数量已超过50,000，这给服务器带来了压力，无法遍历所有这些链接，每隔5分钟更新一次排名.是否有更好的方法可用于至少每5分钟轻松更新一次排名?它不一定要与php一起使用，它可以像perl脚本一样运行，或者可以使类似的东西更好地完成工作(尽管我不确定为什么会这样，只是离开我的php)选项在此处打开).

I have a website that has user ranking as a central part, but the user count has grown to over 50,000 and it is putting a strain on the server to loop through all of those to update the rank every 5 minutes. Is there a better method that can be used to easily update the ranks at least every 5 minutes? It doesn't have to be with php, it could be something that is run like a perl script or something if something like that would be able to do the job better (though I'm not sure why that would be, just leaving my options open here).

这是我目前所做的更新排名:

This is what I currently do to update ranks:

$get_users = mysql_query("SELECT id FROM users WHERE status = '1' ORDER BY month_score DESC");
$i=0;
while ($a = mysql_fetch_array($get_users)) {
    $i++;
    mysql_query("UPDATE users SET month_rank = '$i' WHERE id = '$a[id]'");
}

更新(解决方案):

这是解决方案代码，它执行和更新所有50,000行所需的时间不到1/2秒(如Tom Haigh所建议的那样，使排名为主键).

Here is the solution code, which takes less than 1/2 of a second to execute and update all 50,000 rows (make rank the primary key as suggested by Tom Haigh).

mysql_query("TRUNCATE TABLE userRanks");
mysql_query("INSERT INTO userRanks (userid) SELECT id FROM users WHERE status = '1' ORDER BY month_score DESC");
mysql_query("UPDATE users, userRanks SET users.month_rank = userRanks.rank WHERE users.id = userRanks.id");

推荐答案

使userRanks.rank为自动递增的主键.如果然后将用户ID按降序插入userRanks中，则会在每一行上增加rank列.这应该非常快.

Make userRanks.rank an autoincrementing primary key. If you then insert userids into userRanks in descending rank order it will increment the rank column on every row. This should be extremely fast.

TRUNCATE TABLE userRanks;
INSERT INTO userRanks (userid) SELECT id FROM users WHERE status = '1' ORDER BY month_score DESC;
UPDATE users, userRanks SET users.month_rank = userRanks.rank WHERE users.id = userRanks.id;

这篇关于在不终止服务器的情况下更新用户排名的最佳方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！