如何为用户授权目的处理数据库中的大量记录存储? [英] How to handle massive storage of records in database for user authorization purposes?

问题描述

我正在使用Ruby on Rails 3.2.2和MySQL.我想知道在与一个类相关的数据库表中存储与实例的每个" combination "相关的其他两个类的所有记录是否是可取的"/可取的".

I am using Ruby on Rails 3.2.2 and MySQL. I would like to know if it is "advisable" / "desirable" to store in a database table related to a class all records related to two others classes for each "combination" of their instances.

也就是说，我有User和Article模型.为了存储所有用户文章授权对象，我想实现一个ArticleUserAuthorization模型，以便 给定N个用户和M条文章，就有N * M个ArticleUserAuthorization记录.

That is, I have User and Article models. In order to store all user-article authorization objects, I would like to implement a ArticleUserAuthorization model so that given N users and M articles there are N*M ArticleUserAuthorization records.

这样，我可以声明并使用ActiveRecord::Associations如下:

Making so, I can state and use ActiveRecord::Associations as the following:

class Article < ActiveRecord::Base
  has_many :user_authorizations, :class_name => 'ArticleUserAuthorization'
  has_many :users, :through => :user_authorizations
end

class User < ActiveRecord::Base
  has_many :article_authorizations, :class_name => 'ArticleUserAuthorization'
  has_many :articles, :through => :article_authorizations
end

但是，以上存储所有组合的方法将导致一个包含数十亿亿行的大型数据库表！！！此外，理想情况下，我计划在创建User或Article对象时创建 all 授权记录(也就是说，我计划创建 all 之前提到的"组合"，或者更好的是，以延迟"的批处理……无论如何，此过程都会创建其他数十亿个数据库表行！！！ (通过删除数十亿个数据库表行！！).此外，我计划在User或Article对象更新时立即读取和更新这些行.

However, the above approach of storing all combinations will result in a big database table containing billions billions billions of rows!!! Furthermore, ideally speaking, I am planning to create all authorization records when an User or an Article object is created (that is, I am planning to create all previously mentioned "combinations" at once or, better, in "delayed" batches... in any way, this process creates other billions billions of database table rows!!!) and make the viceversa when destroying (by deleting billions billions of database table rows!!!). Furthermore, I am planning to read and update those rows at once when an User or Article object is updated.

所以，我的疑问是:

• 这种方法是明智的"/理想的"吗?例如，可能会发生哪种性能问题?或者，使用大型数据库表来管理/管理数据库是一种不好的方式"/处方"?
• 我该如何/应该/应该如何处理(也许是通过重新思考"如何更好地处理用户授权)?

注意:我将使用这种方法，因为在检索User或Article对象时，为了仅检索 个授权对象"，我认为我需要由于系统不基于"admin"，"registered"等用户组，因此原子"用户授权规则(即，每个用户和商品对象有一个用户授权记录).因此，我认为ArticleUserAuthorization表的可用性避免了运行与用户授权有关的方法(注意:这些方法涉及某些MySQL查询，这些查询可能会使性能恶化-请参见ArticleUserAuthorization表，从而仅检索用户授权"对象，对每个检索到的对象进行示例授权"方法实现.

Note: I would use this approach because, in order to retrieve only "authorized objects" when retrieving User or Article objects, I think I need "atomic" user authorization rules (that is, one user authorization record for each user and article object) since the system is not based on user groups like "admin", "registered" and so on. So, I thought that the availability of a ArticleUserAuthorization table avoids to run methods related to user authorizations (note: those methods involve some MySQL querying that could worsen performance - see this my previous question for a sample "authorization" method implementation) on each retrieved object by "simply" accessing / joining the ArticleUserAuthorization table so to retrieve only "user authorized" objects.

推荐答案

问题的事实是，如果您想要每个用户文章级权限，则需要一种关联User的方法.到他们可以访问的Article.这需要 minimum ，您需要N * A(其中A是唯一许可的文章数).

The fact of the matter is that if you want article-level permissions per user then you need a way to relate Users to the Articles they can access. This neccesitates a minimum you need N*A (where A is the number of uniquely permissioned articles).

如您所建议的，使用3NF的方法是设置UsersArticles ...这将是一个非常大的表(如您所述).

The 3NF approach to this would be, as you suggested, to have a UsersArticles set... which would be a very large table (as you noted).

考虑将对该表进行大量访问... 在我看来，这是一种略微标准化的方法(甚至noSQL)更合适的情况之一.

Consider that this table would be accessed a whole lot... This seems to me like one of the situations in which a slightly denormalized approach (or even noSQL) is more appropriate.

考虑Twitter用于其用户关注者表的模型:

Consider the model that Twitter uses for their user follower tables:

关于这个问题的杰夫·阿特伍德

以及高可扩展性博客"

从这些片断中获得的样本是在Twitter上学到的教训，即从规范化的表中查询关注者会给Users表带来巨大的压力.他们的解决方案是对关注者进行非规范化处理，以便将用户关注者的信息存储在他们各自的用户设置中.

A sample from those pieces is a lesson learned at Twitter that querying followers from a normalized table puts tremendous stress on a Users table. Their solution was to denormalize followers so that a user's follower's are stored on their individual user settings.

非常规范化.一手救了他们.例如，它们将所有用户ID朋友ID一起存储，从而避免了很多昂贵的连接. -避免复杂的联接. -避免扫描大量数据.

Denormalize a lot. Single handedly saved them. For example, they store all a user IDs friend IDs together, which prevented a lot of costly joins. - Avoid complex joins. - Avoid scanning large sets of data.

我想可以使用类似的方法来提供文章权限并避免使用压力很大的UsersArticles单个表.

I imagine a similar approach could be used to serve article permissions and avoid a tremendously stressed UsersArticles single table.

这篇关于如何为用户授权目的处理数据库中的大量记录存储?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！