sql - 优化 SQL 以确定每个用户的唯一页面 View

Question

我需要确定用户是否已经访问过某个页面，以跟踪唯一页面浏览量。

我已经实现了一些 HTTP header 缓存，但现在我需要优化 SQL 查询。

访问是独一无二的，当:

• 对:page_id + user_id 在visit 表中找到
• 或对:找到page_id + session_id
• 或者:page_id + [ip + useragent] -(这个是另外讨论的话题，是应该只有ip还是ip+用户代理)

所以我有一个跟踪用户访问的表:

visit:
    page_id
    user_id
    session_id
    useragent
    ip
    created_at
    updated_at

现在，在每次用户访问(未命中缓存)时，我将更新一行(如果存在)。如果有任何受影响的行，我将插入新的访问表。

这是一个或两个查询(假设缓存可以工作，主要是两个查询)，但行数以某种方式受到限制。也许最好存储所有访问，然后清理数据库，例如一个月？

问题是:

• 应该如何构建visit 表(键、索引、与user 和page_views 表的关系)。一些重要的字段可能为空(例如user_id)，那么索引呢？我需要多列主键吗？
• 哪个是查找唯一用户最快的 sql 查询？
• 这种做法明智吗？

我使用 PostgreSQL 和 PDO(Doctrine ORM)。我所有的 session 都存储在同一个数据库中。

Answer 1

我个人不会将它放在请求-响应路径中。我会将原始数据记录在表中(或将其推送到队列中)并让后台任务/线程/cron 作业处理它。

队列(或消息传递表)应该只包含 pageid、userip、sessionid、useragen、ip。

只要后台任务能够跟上，绝对时间现在就不那么重要了。由于单个线程现在将完成繁重的工作，因此在更新唯一页面浏览表时不会产生冲突锁。