如何在不影响MySQL数据库的情况下跟踪网页浏览量 [英] How to track pageviews without thrashing the MySQL DB

问题描述

我正在尝试使用以下查询来跟踪MySQL DB中的浏览量:

I am trying to track pageviews in MySQL DB using the following query:

"UPDATE $table SET pageviews = pageviews + 1 WHERE page_id = 1"

这对于低至中等流量的情况很好.但是，在高流量的情况下，对数据库的持续写入将导致较高的读/写争用，并最终导致数据库崩溃.

This is fine for low to moderate traffic. However, under high traffic, constant writes to the DB would result in high read/write contention and eventually bring down the DB.

我已经在Stackoverflow和其他地方阅读了一些QA，其中建议使用MongoDB作为替代方案.但是，该选择不可用，我必须坚持使用MySQL.而且，我无法控制引擎-MyISAM或InnoDB(由于基于行的锁定而不是表(如MyISAM)，InnoDB的性能更好).

I have read several QA's here on Stackoverflow and elsewhere, where MongoDB is suggested as an alternative. However, that choice ain't available and I must stick to MySQL. Furthermore, I do not have control over the Engine — MyISAM or InnoDB (InnoDB performs better due to row based locking instead of table, as in case of MyISAM).

考虑到上述情况，在不影响数据库(在数据库或其他方式)的情况下，跟踪网页浏览量的最佳方法是什么?我真的很感谢一个提供代码片段作为起点的答案(如果可能的话).

Considering the above scenario, what's the best posible method to track pageviews without thrashing the DB (in DB or something else)? I would really appreciate an answer that provides code fragments as a starting point (if posible).

顺便说一句，我正在使用PHP.

BTW, I am using PHP.

更新: @fire在这里有一个很好的解决方案.但是，它需要使用内存缓存.我正在寻找可以轻松实现而无需特定基础设施的东西.这是针对实际上可以在不同托管环境中使用的模块.第二个想法是，我想到的是某种基于cookie或文件日志的实现.我不确定这种实施在实践中如何运作.真的欢迎任何进一步的投入.

Update: @fire has a good solution here. However, it requires use of memcache. I am, looking at something that could be easily implemented without requiring specific infra. This is for a module that could virtually be used in different hosting environments. On a second thought things that comes to my mind are some sort of cookie or file log based implementation. I am not sure how such implementation would work in practice. Any further inputs are really welcome.

推荐答案

我将使用memcached存储计数，然后在cron上将其与数据库同步...

I would use memcached to store the count, and then sync it with the database on a cron...

// Increment
$page_id = 1;
$memcache = new Memcache();
$memcache->connect('localhost', 11211);

if (!$memcache->get('page_' . $page_id)) {
    $memcache->set('page_' . $page_id, 1);
}
else {
    $memcache->increment('page_' . $page_id, 1);
}

// Cron
if ($pageviews = $memcache->get('page_' . $page_id)) {
    $sql = "UPDATE pages SET pageviews = pageviews + " . $pageviews . " WHERE page_id = " . $page_id;
    mysql_query($sql);
    $memcache->delete('page_' . $page_id);
}

这篇关于如何在不影响MySQL数据库的情况下跟踪网页浏览量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！