社交应用程序的DB设计和优化注意事项 [英] DB design and optimization considerations for a social application

问题描述

通常情况下。我有一个简单的应用程序，将允许人们上传照片和跟随其他人。因此，每个用户将具有类似墙或活动馈送的东西，其中他或她看到从他/她的朋友（他或她跟随的人）上传的最新照片。

The usual case. I have a simple app that will allow people to upload photos and follow other people. As a result, every user will have something like a "wall" or an "activity feed" where he or she sees the latest photos uploaded from his/her friends (people he or she follows).

大多数功能都很容易实现。然而，当涉及到这个历史活动饲料，事情可以很容易变成一团糟，因为纯粹的性能原因。

Most of the functionalities are easy to implement. However, when it comes to this history activity feed, things can easily turn into a mess because of pure performance reasons.

我在这里遇到了以下两个难题：
i可以轻松地将活动Feed设计为数据库的规范化部分，这将节省我的写入周期，当为每个用户选择这些结果时（对于在特定时间段内上传的每张照片，选择特定数量，我关注的上传者/我关注的每个人，选择他的照片），会大大增加复杂性。

I have come to the following dilemma here: i can easily design the activity feed as a normalized part of the database, which will save me writing cycles, but will enormously increase the complexity when selecting those results for each user (for each photo uploaded within a certain time period, select a certain number, whose uploaders I am following / for each person I follow, select his photos )

优化选项可以是一系列阈值约束的引入，例如允许我基于他们最后一次上传的日期订购我关注的人，甚至排除一些，

An optimization option could be the introduction of a series of threshold constraints which, for instance would allow me to order the people I follow on the basis of the date of their last upload, even exclude some, to save cycles, and for each user, select only the 5 (for example) last uploaded photos.

第二种方法是为活动Feed引入一个完全非标准化的模式，其中每一行代表我的一个追随者的通知。这意味着，每次我上传照片，数据库将n行在这下降桶，n指的是我跟随的人数，即大量的写周期。如果我有这样的表，我可以很容易地应用一些优化技术，如聪明的索引，以及修剪比一定时间（队列）更早的条目。

The second approach is to introduce a completely denormalized schema for the activity feed, in which every row represents a notification for one of my followers. This means that every time I upload a photo, the DB will put n rows in this "drop bucket", n meaning the number of people I follow, i.e. lots of writing cycles. If I have such a table, though, I could easily apply some optimization techniques such as clever indexing, as well as pruning entries older than a certain period of time (queue).

然而，第三种方法，即使是，即使是一个较少的非规范化模式，其中服务器端应用程序将采取一些部分的复杂性从数据库。我看到一些社交应用程序，如friendfeed，严重依赖于在DB中的序列化对象，如JSON对象的存储。

Yet, a third approach that comes to mind, is even a less denormalized schema where the server side application will take some part of the complexity off the DB. I saw that some social apps such as friendfeed, heavily rely on the storage of serialized objects such as JSON objects in the DB.

我绝对仍然掌握可扩展的数据库设计的技能，所以我相信有很多事情我错过了，或还是要学习。

I am definitely still mastering the skill of scalable DB design, so I am sure that there are many things I've missed, or still to learn. I would highly appreciate it if someone could give me at least a light in the right direction.

推荐答案

我可能会开始使用标准化模式，以便您可以快速，紧凑地写入。然后使用非事务性（无锁定）读取将信息拉回，确保使用游标，以便您可以在返回结果时处理结果，而不必等待整个结果集。因为它听起来不像信息有任何特别的关键意义，你不需要真正担心的锁的关注，通常会推动你远离事务读。

I would probably start with using a normalized schema so that you can write quickly and compactly. Then use non transactional (no locking) reads to pull the information back out making sure to use a cursor so that you can process the results as they're coming back as opposed to waiting for the entire result set. Since it doesn't sound like the information has any particular critical implications you don't really need to worry about a lock of the concerns that would normally push you away from transactional reads.

这篇关于社交应用程序的DB设计和优化注意事项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！