PHP MySql存储数据24小时

I'm building a toplist for my site and instead of recording in and out hits and emptying the database every 24 hours I would like to record in and out hits "within the last 24 hours", this way the toplist will always look busy no matter what time you look at it, while still acurately ranking sites.

我正在为我的网站建立一个*列表,而不是每24小时记录进出点击和清空数据库,我想在“过去24小时内”记录进出点击,这样*列表总是看起来很忙无论你什么时候看它,同时仍然精确地排名网站。

So basically I'll have to add a record to the database logging an 'in' hit (for example) and the remove it around 24 hours later, or simply not consider it when ranking the sites.

所以基本上我必须在数据库中添加记录“in”命中(例如)并在24小时后删除它,或者在排名网站时根本不考虑它。

I want to know the most efficient way of doing this, I have some ideas but they all seem really resource heavy. I hope you understand what I'm trying to achieve :) Any help would be appreciated.

我想知道最有效的方法,我有一些想法,但它们看起来都非常重要。我希望你明白我想要实现的目标:)任何帮助将不胜感激。

5 个解决方案

#1

You don't necessarily have to delete "old" data all the time. You can also limit the data set to records within the last 24 hours when querying data.

您不一定要一直删除“旧”数据。您还可以在查询数据时将数据集限制为过去24小时内的记录。

WHERE
  site='xyz'
  AND hit_datetime > Now()-Interval 24 hour

see http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html

#2

I would delete the data that is older than 24 hours with a simple

我会用简单的方法删除超过24小时的数据

DELETE...WHERE hit_time < now() - interval 24 hour

The other question is - when to call it? The tradeoff is between performance and stale data. The more often you call it, the less "stale" data there will be, but the server load will grow.

另一个问题是 - 什么时候打电话?权衡是在性能和陈旧数据之间进行权衡。您调用它的次数越多,数据就越少“陈旧”,但服务器负载会增加。

I see several approaches, pick one that suits your needs most:

我看到几种方法,选择最适合您需求的方法:

Call it at the start of every script. This can be optmized by calling it only if the script will do something with the hit data. That way every script will always run with "correct" data. However this will have the maximum load.

在每个脚本的开头调用它。只有当脚本对命中数据执行某些操作时,才可以通过调用来优化此选项。这样每个脚本将始终使用“正确”数据运行。但是这将具有最大负载。

Schedule a cron job and call it once every 1h/2h/24h/etc. This way there will be a little bit of "stale" data, but the overhead will be reduced to a minimum.

安排一个cron作业,每1h / 2h / 24h /等调用一次。这样会有一些“陈旧”的数据,但开销会减少到最低限度。

Do it like PHP does it with sessions - on every script startup give it a x% (x is configurable) chance of being run. That is, take a value from 0 to 100, and if it is smaller than x, execute the DELETE.

就像PHP用会话一样 - 在每个脚本启动时给它一个x%(x是可配置的)运行机会。也就是说,取0到100之间的值,如果它小于x,则执行DELETE。

You can also invent some other schemes - for example, run it once per user session; or run it only if the execution time is evenly divisable by, say, 7; or something else. Either way you trade off performance for correctness.

您还可以发明一些其他方案 - 例如,每个用户会话运行一次;或者只有当执行时间可以被7分钟均分时才能运行它;或者是其他东西。无论哪种方式,你都要权衡性能的正确性。

#3

Write a Stored Procedure that deletes records older than 24 hours. Then write a trigger that runs on every INSERT statement and calls the SP.

编写存储过程以删除超过24小时的记录。然后编写一个在每个INSERT语句上运行的触发器并调用SP。

#4

you could store the timestamp with each "hit" and then call a query like

你可以存储每个“命中”的时间戳,然后调用像这样的查询

 $time = time()-86400;
 mysql_query("DELETE FROM xxx WHERE timestamp < $time");

or you could same thing within the SELECT statement, depends on if you still need the hits afterwards, etc

或者你在SELECT语句中可以做同样的事情,取决于你之后是否还需要点击等等

#5

If the time-constraint is not really hard (e.g. you'll loose money or are really annoying your users if the data is kept in the the db longer than 24 hours), I'd use use PHP's register_shutdown_function like this:

如果时间限制不是很难(例如,如果数据在数据库中保存的时间超过24小时,你会浪费钱或者真的让用户烦恼),我会使用PHP的register_shutdown_function,如下所示:


function cleanup() {
  // open db-connection etc.
  $query = 'DELETE FROM <yourtable> ' .
               'WHERE UNIX_TIMESTAMP(<timstampfield>) < ' . (time() - 86400);
  mysql_query($query);
  // close connection
}

register_shutdown_function('cleanup');

The above code assumes, <timestampfield> is of one of the the MYSQL-date-datatypes (TIMESTAMP, DATE, DATETIME).

上面的代码假设, 是MYSQL-date-datatypes之一(TIMESTAMP,DATE,DATETIME)。

#1