通知和 PHP+MySQL 设计 [英] Notification and PHP+MySQL design

问题描述

我正在制作一个有帖子和回复系统的网站.

I'm making a website that have posts and replies system.

我想做的是当有人回复时，向曾经回复(或参与)该帖子的人发送通知.

I'd like to do is when someone replies, sending notification to those who have ever replied (or involved) the post.

我的想法是创建一个名为Notification 的表，包含message 和seen (seen/unread) 字段.一旦有人回复，将记录插入Notification 表.

My thought is to create a table named Notification, contains message and seen (seen/unread) field. Once people replied, INSERT record to the Notification table.

看似简单直观，但如果参与人数较多，例如第 31 位用户回复，则有 30 位曾经回复过的人会收到通知.这将生成 30 行 SQL 记录.第 32 个用户将创建 31 个记录.那么总行数就会变成30+31=61.

It's seems easy and intuitive, but if there are lots of people involved in, for example, the 31st user replies, 30 people who have ever replied will receive notification. This will make 30 rows of SQL records. And the 32nd user will make 31 records. Then total number of rows will become 30+31=61.

我的问题是

1. 这是处理通知系统的好方法吗?
2. 如果是，如何处理重复通知(没有看到但有新回复)
3. 如上所述，这会导致巨大的服务器负载吗?

非常感谢.

推荐答案

我正在创建类似的系统.这是我的经验:

I was creating similar system. Here is my experience:

1. 我的通知表看起来像:id (int) |user_id (int) |post_id (int) |last_visited(日期时间).

1. My notification table looks like: id (int) | user_id (int) | post_id (int) | last_visited (datetime).

user_id + post_id 是一个独一无二的 复合索引.

user_id + post_id is an unique composite index.

所以当用户打开页面时，我在数据库中寻找一个条目(user_id + post_id).如果找到，则更新 last_visited 字段，如果找不到，则创建新行.

So when a user opens the page, I'm looking for an entry (user_id + post_id) in the database. If I find it, then I update the last_visited field if I don't find, then create new row.

当我需要为通知列出消息时，我只是查询在 last_visited 时间之后创建的所有消息.

When I need list messages for notification I'm just query all messages that was created after last_visited time.

我还有 cron 脚本，为关闭的帖子或被禁止的用户提供干净的通知.

Also I have cron sript that clean notification for closed posts or banned users.

至于您的问题:

1 和 2:您必须在将存储的数据量和站点性能之间找到平衡.如果你不需要存储所有这些数据，你可以按照我的方式.如果需要这些数据，您的方法会更好.

1 and 2: You have to find a balance between the amount of data that will be stored and site performance. If you don't need to store all this data you can follow my way. If this data is needed your way is better.

3:这取决于访问者的数量和其他功能.但这里有一些建议.您必须为 MySql 表使用索引以获得更好的性能.此外，您应该考虑删除无用通知的 cron 脚本.如果您每天的访问量超过 70 万，您应该考虑使用 MogoDb 或其他高性能 noSql 数据库.

3: It depends on the number of visitors and other functionality. But here is some advices. You must use indexes for MySql table for better perfomance. Also you should think about cron script that will remove useless notifications. If you have huge amount of visitors more than 700k per day you shoulf think about MogoDb or other high perfomance noSql database.

这篇关于通知和 PHP+MySQL 设计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！