选择一种方法而不是另一种从 Mysql 获取数据的复杂性
我正在开发一个基于 PHP、Mysql、Apache 的项目。
我有一个名为通知的模块，就像 FACEBOOK 中提供的通知一样，为此我有 3 种
方法 我将每个更新插入到一个表中，记录需要通知这些更新的用户的数量，即如果假设类别 B 有一些更新并且该类别 B 包含 100 个用户，那么我将在 a 中为相应用户插入 100 行比如说，通知表。
我能做的第二种方法
是在表中插入特定类别条目，然后对每个护理 ID 进行大量 JOIN 查询，以从多个表中获取所有记录来获取记录。
第三种方式
我在每次更新时使用触发器，以便它可以用于通知用户。
我相信这三种方式在某些时候都是有问题的。
现在有人能提出更好的想法吗？或者其中哪一个是更好的选择？
我担心该网站的性能，因为它会有大量条目，
谢谢
I am working on a project based on PHP, Mysql , Apache.
I have a module called notification which is just like Notification available in FACEBOOK, for this I have 3 ways
either
I insert every update to a table, in a count of users who are needed to be notified for these updates i.e. if suppose a category B has some updates and this category B contains 100 users then I will insert 100 rows for the respective user in a say, notification table.
2nd way
I can do is just Insert a particular categories entry in table and then file a heavy JOIN query on each of these caregory ID to fetch all the records from multiple tables to fetch the records.
3rd Way
I use trigger on every updation so that it can be used for notifying the users.
I believe all three ways are problematic at some point.
Now can any one suggest any better idea or which one of these is better option?
I am concerned about the Performance of the site as it will have bulk entries
Thanks
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
这比我最初想到的要棘手一些:)
应用程序/用户行为如何？我们什么时候应该做大部分工作？是在插入数据的时候，还是在我们取数据的时候。
我必须对最常见的操作做出假设。我认为获取数据会更常见一些，因为许多用户会看到未读消息的通知，但只有一个新消息的“插入”？
因此，我会将工作放在插入新通知上，但当用户开始涌向您的应用程序时，结果仍然可能很糟糕。
但我认为模型需要在任何优化之前设置，这更重要。稍后可以通过使用反规范化、调度等来管理优化。我不会真正接触触发器，它们恕我直言，有点时髦。
我还将采取另一种方法。
用户写入一条新消息：
更新当时更新的 user_category（有很多）类别
UPDATE user_category SET last_changed = 'NOW()' wherecategory_id = ?;
如何查找用户未读消息：
用户已阅读消息
从该类别读取的所有消息
- 使用用户阅读所有消息的日期更新*user_category™（有很多）。
但有时，你无法真正摆脱实际工作。
This was a bit trickier then I first thought :)
How is the app / user behavior? When should we do the most of the work? Is it when inserting the data, or when we fetch the data.
I will have to make assumptions on what will be most common operations. I would assume that fetching data will a little more common, due to many users will see notification of unread messages, but only one 'insertion' of a new message?
Therefore I would put the work on insertion of new notices, but still it might end up badly when users starts flocking to your app.
But I think the Model needs to be set before any optimizations, it's more important. Optimizations can later be managed by using denormalisation, scheduling etc. I wouldn't really touch triggers, they are a bit funky IMHO.
I will also make another approach.
User writes a new message:
Update user_category (has many) category as updated at that time
UPDATE user_category SET last_changed = 'NOW()' where category_id = ?;
How to find users unread messages:
On user has read message
On all messages read from that category
- Update *user_category™ (has many many) with a date when user had read all the messages.
But sometimes, you can't really get away from doing the actually work.
MySQL 中的触发器支持充其量是劣质的，所以我不会朝这个方向推进，尽管这可能是一个很好的方法。
最简单的方法如下：每次登录/注销时，从“在线用户”.id 中插入/删除用户，然后让前端（无论如何都有麻烦事要做）立即询问并然后知道他知道哪些 id 在表中。
连接除了在 mysql 中相对不太智能之外，在大表上即使有索引也会很慢。
触发器，一样，然后，你会要求触发器做什么？推送到此人登录的所有内容？推送给那些感兴趣的人？这意味着又一个连接，即性能受到影响。
通知表？无缘无故地巨大且缓慢（即 100 次插入而不是 1 个 where 查询.. 嗯）。
Trigger support in MySQL is shoddy at best, so I would not push in that direction although it could have been a good way to do it.
The lightest way to do it will be the following : for every log in / log out, insert / delete user from "online users".id, then have the front-end (which has jack-shit to do anyway) ask now and then to know which of the id's he knows are in the table.
Joins, apart from being relatively not very smart in mysql, will be slow on big tables even with indexes.
Triggers, just the same and then, what would you ask the trigger to do ?? push to all that this person has logged in ? push to those interested ? this means yet another join, i.e. performance hit.
Notification table ? totally huge and slow for no reason (i.e. 100 inserts instead of one where query .. meh).