了解PHP中有多少用户在线？

Every visit to my website updates a user's individual hit counter and updates a column for time() based on their ip address and id stored in a cookie. So when coming to output the data, what's a more efficient way of my following code with less database calls, as it's essentially a copy of itself:

每次访问我的网站都会更新用户的个人点击计数器,并根据他们的IP地址和存储在cookie中的ID更新time()列。因此,在输出数据时,使用较少数据库调用的下列代码的更有效方法是什么,因为它本质上是自身的副本:

<?
$last1Min = time()-60;
$last5Mins = time()-300;
$last1Hr = time()-6000;
$last1Dy = time()-144000;
$last1Wk = time()-1008000;
$last1Mnth = time()-30240000;

//last1Min
$sql = "SELECT COUNT(*) FROM usersonline WHERE lastOnline > $last1Min";
while($rows = mysql_fetch_array(mysql_query($sql))) {
    echo "Users online in the last minute: " . $rows['COUNT(*)'] . "<br />\n";
}

//last5Mins
$sql = "SELECT COUNT(*) FROM usersonline WHERE lastOnline > $last5Mins";
while($rows = mysql_fetch_array(mysql_query($sql))) {
    echo "Users online in the last 5 minutes: " . $rows['COUNT(*)'] . "<br />\n";
}

//last1Hr
$sql = "SELECT COUNT(*) FROM usersonline WHERE lastOnline > $last1Hr";
while($rows = mysql_fetch_array(mysql_query($sql))) {
    echo "Users online in the last hour: " . $rows['COUNT(*)'] . "<br />\n";
}

//last1Dy
$sql = "SELECT COUNT(*) FROM usersonline WHERE lastOnline > $last1Dy";
while($rows = mysql_fetch_array(mysql_query($sql))) {
    echo "Users online in the last day: " . $rows['COUNT(*)'] . "<br />\n";
}

//last1Wk
$sql = "SELECT COUNT(*) FROM usersonline WHERE lastOnline > $last1Wk";
while($rows = mysql_fetch_array(mysql_query($sql))) {
    echo "Users online in the last week: " . $rows['COUNT(*)'] . "<br />\n";
}

//last1Mnth
$sql = "SELECT COUNT(*) FROM usersonline WHERE lastOnline > $last1Mnth";
while($rows = mysql_fetch_array(mysql_query($sql))) {
    echo "Users online in the last month: " . $rows['COUNT(*)'] . "<br /><br />\n";
}

If there is a more efficient way of presenting this data, I'm wanting to extend it to show not only how many users for each of these metrics is online on my entire site, but record and output the data for every page on my site.

如果有更有效的方式来呈现这些数据,我想扩展它以不仅显示每个指标在我的整个网站上有多少用户在线,而且还记录并输出我网站上每个页面的数据。

4 个解决方案

#1

SELECT 
  SUM(lastOnline <= 60) AS one_minute,
  SUM(lastOnline <= 300) AS five_minutes,
  ...
  SUM(lastOnline <= 30240000) AS one_month
FROM usersonline

Using this method, you can get everything you need in a single query with a single table scan; it doesn't get much more efficient than that. As others have mentioned, you should cache the result, as it's relatively expensive (even in this optimized form). There's no point in calculating this on every page load, especially if you're seeing multiple hits per second (which is extremely likely if you, say, hit the front page of digg)

使用此方法,您可以通过单个表扫描在单个查询中获得所需的一切;它并没有比那更有效率。正如其他人所提到的,你应该缓存结果,因为它相对昂贵(即使在这种优化的形式)。在每次页面加载时计算这一点都没有意义,特别是如果你每秒看到多次点击(如果你这么做,那么,如果你点击了digg的首页)

lastOnline <= 60 evaluates to 1 for rows where the condition is true, and 0 for rows where the condition is false; SUM() sums these 1s and zeros, giving you a count of the number of rows for which the condition is true.

对于条件为真的行,lastOnline <= 60计算结果为1,对于条件为假的行计算结果为0; SUM()将这些1和0相加,为您计算条件为真的行数。

Learned this technique from a user comment in the mysql docs a few years ago; there are similar examples elsewhere

几年前从mysql文档中的用户评论中学到了这种技术;其他地方有类似的例子

#2

Set up a cron job that calculates the correct values only once every minute/5 minutes/etcetera. Cache the result and display that instead. There's really no need to calculate these kinds of stats X times a second when they only change once a minute or once ever half hour.

设置一个cron作业,每分钟只计算一次正确的值/ 5分钟/等等。缓存结果并显示该结果。当他们每分钟只改变一次或半小时改变时,真的没有必要每秒X次计算这些类型的统计数据。

#3

Instead of calling the database for each of the times you could just make a call for all within the last month and order them by date. Then in php you can compare the date and time to see how long ago the user last logged in.

您不必每次都调用数据库,而是可以在上个月内为所有人拨打电话,并按日期排序。然后在php中,您可以比较日期和时间,以查看用户上次登录的时间。

#4

If you save your sessions to files, you could count the number of files that have been accessed in a time period. Then there'd be no database access at all.

如果将会话保存到文件,则可以计算在一段时间内访问过的文件数。然后根本就没有数据库访问权限。