通过点击量优化内容受欢迎程度的查询

Question

通过点击量优化内容受欢迎程度的查询

4

我为此进行了一些搜索，但没有找到任何东西，也许有人可以指引我正确的方向。我有一个网站，其中包含许多存储在MySQL数据库中的内容和一个通过点击量加载最受欢迎内容的PHP脚本。它通过在表中记录每个内容的点击量以及访问时间来实现此目的。然后运行选择查询以查找过去24小时，7天或最多30天内最受欢迎的内容。 cronjob将删除日志表中早于30天的任何内容。

现在我面临的问题是随着网站的增长，日志表具有100万个以上的点击记录，并且真正减慢了我的选择查询速度（10-20秒）。起初我认为问题是我在查询中使用的连接获取内容标题，URL等。但是现在我不确定了，因为在测试中删除连接并没有像我想象的那样加快查询速度。

所以我的问题是这种流行存储/选择的最佳实践是什么？是否有任何好的开源脚本可用？或者你有什么建议？

表格方案： "popularity" hit log table nid | insert_time | tid nid：内容的节点ID insert_time：时间戳（2011-06-02 04:08:45） tid：Term / category ID

"node" content table nid | title | status |（还有更多，但这些是重要的） nid：节点ID title：内容标题 status：内容是否已发布（0 = false，1 = true）

SQL

SELECT node.nid, node.title, COUNT(popularity.nid) AS count  
FROM `node` INNER JOIN `popularity` USING (nid)  
WHERE node.status = 1  
  AND  popularity.insert_time >= DATE_SUB(CURDATE(),INTERVAL 7 DAY)  
GROUP BY popularity.nid  
ORDER BY count DESC  
LIMIT 10;

- Owen

3

请发表您的表结构，以便查看需要添加索引甚至慢查询解释的地方。 - Nicola Cossu

我已经添加了表结构和缓慢的SQL查询。 - Owen

5个回答

1

实际上，您还有两个问题需要解决。

第一个问题是在统计表中插入吞吐量，这可能会比您预期的要早。

另一个问题是如何使用这些统计数据，这也是您在问题中提到的。

让我们从输入吞吐量开始。

首先，如果您这样做，请勿跟踪可能使用缓存的页面的统计信息。使用一个将自己宣传为空JavaScript或一个像素图像的php脚本，并将其包含在您要跟踪的页面上。这样做可以轻松缓存您网站的其余内容。

在电信业务中，与其针对电话通话进行实际插入计费，不如将事物放置在内存中并定期与磁盘同步。这样做可以管理巨大的吞吐量，同时保持硬盘的健康。

为了在您的端口类似地进行操作，您需要原子操作和一些内存存储。以下是一些基于memcache的伪代码，用于执行第一部分...

对于每个页面，您需要一个Memcache变量。在Memcache中，increment（）是原子性的，但add（），set（）等则不是。因此，当并发进程同时添加相同的页面时，您需要注意不要错误计数点击次数：

$ns = $memcache->get('stats-namespace');
while (!$memcache->increment("stats-$ns-$page_id")) {
  $memcache->add("stats-$ns-$page_id", 0, 1800); // garbage collect in 30 minutes
  $db->upsert('needs_stats_refresh', array($ns, $page_id)); // engine = memory
}

定期地，比如每5分钟（相应地配置超时时间），您都希望将所有这些内容同步到数据库中，而不会出现并发进程互相影响或现有点击计数的情况。为此，在进行任何操作之前，您需要增加命名空间（这为所有目的提供了现有数据的锁定），并稍微休眠一下，以便引用先前命名空间的现有进程在需要时完成：

$ns = $memcache->get('stats-namespace');
$memcache->increment('stats-namespace');
sleep(60); // allow concurrent page loads to finish

完成此操作后，您可以安全地循环遍历页面ID，相应地更新统计信息，并清理需要刷新统计信息的表。后者只需要两个字段：page_id int pkey，ns_id int)。然而，这比从脚本运行的简单选择、插入、更新和删除语句更复杂，因此要继续...

正如另一位回答者建议的那样，维护中间统计数据非常合适：存储命中批次而不是单个命中。最多，我假设您想要每小时或每15分钟处理的统计信息，因此可以处理每15分钟批量加载的小计。

更加重要的是，由于您正在使用这些总数对帖子进行排序，因此您希望存储聚合总数并对后者进行索引。（我们将在下面说明。）

一种维护总数的方法是添加触发器，在对统计信息表进行插入或更新时，根据需要调整统计总数。

在这样做时，要特别注意死锁。虽然没有两个$ns运行会混合它们各自的统计数据，但仍有可能（尽管很小）出现两个或多个进程同时启动上述“增加$ns”的步骤，并随后发出试图并发更新计数的语句。获得咨询锁是避免与此相关问题的最简单、最安全和最快速的方法。

假设您使用了咨询锁，则在更新语句中使用total = total + subtotal 是完全可以的。

在谈到锁时，请注意更新总数将需要对每个受影响的行进行独占锁定。由于您正在按它们排序，因此不希望它们一次性全部处理，因为这可能意味着保持独占锁定的时间较长。最简单的方法是将插入到统计信息中的记录分批处理（例如，每1000个），每个批次后跟一个提交。

对于中间统计数据（每月、每周），在您的统计表中添加一些布尔字段（MySQL 中的 bit 或 tinyint）。让每个字段存储它们是否要计入每月、每周、每日等统计数据。同时，在这些字段上设置触发器，以便它们增加或减少适用总计在您的 stat_totals 表中。

最后，请考虑一下您想要实际计数存储在哪里。它需要是一个索引字段，而后者将被大量更新。通常，您会希望将其存储在自己的表中，而不是页面表中，以避免在页面表中混杂着（更大的）无效行。

假设您已经完成了上述所有步骤，那么您的最终查询将变为：

select p.*
from pages p join stat_totals s using (page_id)
order by s.weekly_total desc limit 10

如果在weekly_total上建立索引，速度应该足够快。

最后，让我们不要忘记最明显的一点：如果您一遍又一遍地运行这些相同的total/monthly/weekly等查询，它们的结果也应该放入memcache中。

- Denis de Bernardy

0

你可以添加索引并尝试调整SQL，但真正的解决方案是缓存结果。

你只需要每天计算最近7/30天的流量一次。

过去24小时可以每小时计算一次吗？

即使你每5分钟计算一次，这仍然比为每个用户的每个点击运行（昂贵的）查询要节省大量开销。

- David Chan

我考虑过缓存结果，但我认为将来可能需要这样做，但现在我认为查询可以更好地优化。 - Owen

0

RRDtool

许多工具/系统不会构建自己的日志记录和日志聚合，而是使用RRDtool（循环数据库工具）来高效处理时间序列数据。RRDtools还配备了强大的图形子系统，并且（根据Wikipedia）有PHP和其他语言的绑定。

从您的问题中我可以推断出，您不需要任何特殊和花哨的分析，RRDtool可以有效地完成您所需的工作，而无需实现和调整自己的系统。

- alienhard

0

你可以在后台进行一些“聚合”操作，例如通过一个定时任务。以下是一些可能有用的建议（没有特定顺序）：

1. 创建一个每小时结果的表。 这意味着您仍然可以创建所需的统计信息，但可以将数据量减少到每页每月（24*7*4 = 约672条记录）。

您的表可能类似于以下内容：

hourly_results (
nid 整数,
start_time 日期时间,
amount 整数
)

在将它们解析到聚合表中之后，您可以将其删除。

2.使用结果缓存（memcache，apc） 您可以轻松地将结果（不应每分钟更改一次，而应每小时更改一次？）存储在{{link1：memcache数据库}}中（您可以从cronjob更新），使用{{link2：apc用户缓存}}（您无法从cronjob更新），或者如果内存不足，则通过序列化对象/结果使用{{link3：文件缓存}}。

3.优化您的数据库 10秒钟是很长的时间。尝试找出数据库发生了什么。它是否用完了内存？您需要更多的索引吗？

- Arend

“尝试找出数据库发生了什么问题。”-- 呃...他发布的查询正在将整个表连接/分组在一起，其中一个表有数百万行。唯一可能的情况就是很慢了 :-) - Denis de Bernardy

是的，你可以争论它很慢，那就是它的特点。这可能正好相反于优化。以下是一些入门的地方：http://www.xaprb.com/blog/2006/04/30/how-to-optimize-subqueries-and-joins-in-mysql/。http://forge.mysql.com，http://20bits.com/articles/10-tips-for-optimizing-mysql-queries-that-dont-suck/ /wiki/Top10SQLPerformanceTips http://www.mysqlperformanceblog.com/2007/04/06/using-delayed-join-to-optimize-count-and-limit-queries/ - Arend

1

你可能想多了解一下数据库如何决定是否使用索引。简而言之，查询规划器会倾向于在小数据集上使用索引，在大数据集上使用位图索引，在查询大多数数据集时不使用索引。在这种情况下，他正在将两个完整的表连接在一起，因此不需要使用索引。 - Denis de Bernardy

好的，我知道了。谢谢您分享您的专业知识。根据这个知识，我可以推测，不加入这个表可能会带来很大的性能提升？我只是想指出它可能会很慢，而且从结构上来说确实如此，但我也相信通过更深入地研究您的数据库，可能会有改进的机会。我绝对不是这个领域的专家，您是正确的。也许您有一些专业知识可以分享？ - Arend

是的，使用索引的总字段和无连接会更快。然而，在这种特定情况下，将索引的总数移入单独的表中是更可取的，因为不这样做会在各个地方生成大量的死行。（请参见我对问题的回答以获取详细步骤。） - Denis de Bernardy

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- cusimar9 · Accepted Answer

我们最近遇到了一个类似的情况，这是我们解决它的方法。我们决定不关心某件事情确切发生的时间，只关心它发生的日期。然后我们做了以下几点：

每个记录都有一个“总点击量”记录，每次发生事件时会增加
一个日志表记录了每个记录每天的“总点击量”（在cron工作中）
通过选择日志表中给定日期之间的差异，我们可以快速推断出两个日期之间的“点击量”。

这样做的好处是您的日志表的大小仅为NumRecords * NumDays，对于我们来说非常小。此外，对于此日志表的任何查询都非常快。

缺点是您失去了按时间推断点击量的能力，但如果您不需要这个功能，那么考虑这种方法可能值得。