SQL Server 2008 中的继承表。性能问题？
基于我在某处读到的另一个概念，我一直在脑海中思考这个想法。基本上，您有一个包含很少字段的“主”表，其他表通过外键继承该主表。以前已经做过这么多，所以没有新闻。我想做的是让数据库中的几乎每个表都继承自该主表。这样，每个表中的每个对象、每个记录、每个条目都可以有一个完全唯一的主键（因为 PK 实际上存储在主表中），并且可以简单地通过 ID 而不是通过表来引用。
另一个好处是可以轻松建立可以触及多个表的关系。例如：我有一个交易表，该表想要有一个 FK 到任何交易（库存、帐户、联系人、订单等）。事务可以只对主表有一个 FK，并通过它引用必要的数据。
我脑海中不断出现的问题是主表是否会成为瓶颈。这个东西在某一时刻实际上会拥有数百万条记录。我知道可以通过良好的表设计来处理巨大的记录集，但是限制是什么？
有没有人尝试过类似的事情，你的结果是什么？
I have this idea I've been mulling around in my head based on another concept I read somewhere. Basically you have a single "Primary" table with very few fields, other tables inherit that primary table through a foreign key. This much has been done before so its no news. What I would like to do, is to have virtually every table in the database inherit from that Primary table. This way, every object, every record, every entry in every table can have a fully unique primary key(since the PK is actually stored in the Primary table), and can be simply referenced by ID instead of by table.
Another benefit is that it becomes easy to make relationships that can touch multiple tables. For example: I have a Transaction table, and this table wants to have a FK to whatever it is a transaction for(inventory, account, contact, order, etc.). The Transaction can just have a FK to the Primary table, and the necessary piece of data is referenced through that.
The issue that keeps coming up in my head, is whether or not that Primary table will become a bottleneck. The thing is gonna have literally millions of records at one point. I know that gigantic record sets can be handled by good table design, but whats the limit?
Has anyone attempted anything similar to this, and what were your results?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
您必须考虑到该表将具有大量外键关系。如果您想从根表中删除行，这些可能会导致性能问题。 （这可能会导致删除时出现一些令人讨厌的执行计划）
因此，如果您计划删除行，那么它可能会影响性能。我最近遇到了这样的设置问题，清理它很痛苦（它引用了 120 个其他表 - 删除速度非常慢）。
为了克服这一性能问题，您可能会考虑不强制执行约束（坏计划）、不使用性能约束（坏计划），或者尝试将属于一个实体的所有数据分组到一行中，并坚持正常的规范化实践（好计划）
You have to consider that this table will have a tons of foreign key relations. These can cause performance issues, if you want to delete a row from the root table. (Which can cause some nasty execution plans on delete)
So if you plan to remove rows, then it could impact performance. I recently had issues with a setup like this, and it was a pain to clean it up (it was refferencing 120 other tables - deletes where slow as hell).
To overcome this performance issue, you might consider not enforcing contrains (Bad plan), using no contrains for performance (Bad plan), or try to group all data that belongs to one entity in one row, and stick to the normal normalization practices (Good plan)
例如，如何确定交易的 FK 实际上链接到库存、帐户、联系人或订单，而不是苹果、橙子或菠萝？
For example, How can you be sure that the transaction's FK is actually linked to an inventory, account, contact or order rather than an apple, orange or pineapple?
我认为这将是一个可怕的瓶颈。这不仅会使执行真正的 PK/FK 关系变得更加困难。它可能会造成数据完整性噩梦。我根本看不出你从哪里获得任何好处。
I think this is something that would be a horrible bottleneck. Not only that it would make enforcing the real PK/FK relationships much harder. It could create a data integrity nightmare. I don't see where you gain any benefits at all.