“每张表"上自动递增键的优缺点 [英] Pros and Cons of autoincrement keys on "every table"

问题描述

我们公司正在讨论是否要在我们数据库的每个表上放置一个自动增量键.

We are having a rather long discussion in our company about whether or not to put an autoincrement key on EVERY table in our database.

我可以理解把一个放在有 FK 引用的桌子上，但我有点不喜欢把这样的键放在我们的每一张桌子上，即使这些键永远不会被使用.

I can understand putting one on tables that would have a FK reference to, but I kind-of dislike putting such keys on each and every one of our tables, even though the keys would never be used.

除了占用额外空间和稍微减慢一切(我们有一些包含数亿条记录的表)之外，请帮助解决将自动增量键放在每个表上的利弊.

Please help with pros and cons for putting autoincrement keys on every table apart from taking extra space and slowing everything a little bit (we have some tables with hundreds of millions of records).

谢谢

推荐答案

我假设几乎所有的表都会有一个主键——这只是一个问题，该键是由一个或多个自然键组成还是单个自动递增代理键.如果您不使用主键，那么在几乎所有表上使用它们通常会获得很多优势.

I'm assuming that almost all tables will have a primary key - and it's just a question of whether that key consists of one or more natural keys or a single auto-incrementing surrogate key. If you aren't using primary keys then you will generally get a lot of advantages of using them on almost all tables.

所以，这里有一些优点 &代理键的缺点.首先，优点:

So, here are some pros & cons of surrogate keys. First off, the pros:

• 最重要的是:它们允许更改自然键.一个简单的例子，一个人的表应该有一个主键 person_id 而不是 last_name, first_name.
• 读取性能 - 非常小的索引扫描速度更快.但是，这仅在您实际上通过代理键限制查询时才有用.因此，对查找表有用，对主表不太有用.
• 简单 - 如果命名得当，它会使数据库易于学习和学习使用.
• 容量 - 如果您要设计数据仓库事实表之类的东西 - 维度上的代理键可以让您保留一个非常窄的事实表 - 这会带来巨大的容量改进.

• Most importantly: they allow the natural keys to change. Trivial example, a table of persons should have a primary key of person_id rather than last_name, first_name.
• Read performance - very small indexes are faster to scan. However, this is only helpful if you're actually constraining your query by the surrogate key. So, good for lookup tables, not so good for primary tables.
• Simplicity - if named appropriately, it makes the database easy to learn & use.
• Capacity - if you're designing something like a data warehouse fact table - surrogate keys on your dimensions allow you to keep a very narrow fact table - which results in huge capacity improvements.

缺点:

• 它们不会阻止自然值的重复.因此，您通常仍然需要逻辑键上的唯一约束(索引).
• 写入性能.使用额外的索引，您将进一步减慢插入、更新和删除的速度.
• 简单性 - 对于几乎从不更改的小型数据表，它们是不必要的.例如，如果您需要国家/地区列表，则可以使用 ISO 国家/地区列表.它包括有意义的缩写.这比代理键更好，因为它既小又有用.

一般来说，代理键很有用，只要记住缺点，在适当的时候不要犹豫使用自然键.

In general, surrogate keys are useful, just keep in mind the cons and don't hesitate to use natural keys when appropriate.

这篇关于“每张表"上自动递增键的优缺点的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！