代理与自然键：硬数字性能差异？

本文介绍了代理与自然键：硬数字性能差异？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

代理和自然键之间有一个健康的辩论：

There's a healthy debate out there between surrogate and natural keys:

SO Post 1

SO Post 2

我的意见，这似乎与大多数（它是一个苗条的多数），是应该使用代理键，除非一个自然键是完全明显的，并保证不改变。然后你应该强制自然键的唯一性。这意味着代理键几乎所有的时间。

My opinion, which seems to be in line with the majority (it's a slim majority), is that you should use surrogate keys unless a natural key is completely obvious and guaranteed not to change. Then you should enforce uniqueness on the natural key. Which means surrogate keys almost all of the time.

两种方法的示例，从公司表开始：

Example of the two approaches, starting with a Company table:

1：代理键： ID字段，其是PK（和身份）。

1: Surrogate key: Table has an ID field which is the PK (and an identity). Company names are required to be unique by state, so there's a unique constraint there.

2：自然密钥：表使用CompanyName和State作为PK - 满足以下两个条件： PK和唯一性。

2: Natural key: Table uses CompanyName and State as the PK -- satisfies both the PK and uniqueness.

我们假设公司PK在其他10个表中使用。我的假设，没有数字支持，是代理的关键方法将在这里更快。

Let's say that the Company PK is used in 10 other tables. My hypothesis, with no numbers to back it up, is that the surrogate key approach would be much faster here.

我看到的自然键的唯一令人信服的参数是一个多对多的表，使用两个外键作为自然键。我认为在这种情况下是有道理的。但是如果你需要重构，你可能会遇到麻烦;这超出了我认为这个职位的范围。

The only convincing argument I've seen for natural key is for a many to many table that uses the two foreign keys as a natural key. I think in that case it makes sense. But you can get into trouble if you need to refactor; that's out of scope of this post I think.

有人在使用代理键 的一组表格上看到了比较 vs。使用自然键的同一组表格？环视SO和Google没有产生任何有价值的东西，只是很多理论。

Has anyone seen an article that compares performance differences on a set of tables that use surrogate keys vs. the same set of tables using natural keys? Looking around on SO and Google hasn't yielded anything worthwhile, just a lot of theorycrafting.

重要更新：我已开始构建 / strong>回答这个问题。它看起来像这样：

Important Update: I've started building a set of test tables that answer this question. It looks like this:

• PartNatural - 使用的零件表作为PK的唯一PartNumber
• PartSurrogate - 零件表，使用ID（int，identity）作为PK和在PartNumber上有唯一索引
• int，identity）as PK

每个零件都连接到一个工厂，每个工厂零件的实例都连接到一个工程师。如果任何人有这个测试台的问题，现在的时间。

Every part is joined to a plant and every instance of a part at a plant is joined to an engineer. If anyone has an issue with this testbed, now's the time.

推荐答案

使用两者！自然键防止数据库损坏（不一致可能是一个更好的词）。当为了性能目的，当右自然键（以消除重复行）将由于长度或所涉及的列数目而执行得不好时，可以添加代理键，以用作其他表中的外键，而不是自然密钥...但是自然密钥应该保留为备用密钥或唯一索引，以防止数据损坏和数据库一致性...

Use both! Natural Keys prevent database corruption (inconsistency might be a better word). When the "right" natural key, (to eliminate duplicate rows) would perform badly because of length, or number of columns involved, for performance purposes, a surrogate key can be added as well to be used as foreign keys in other tables instead of the natural key... But the natural key should remain as an alternate key or unique index to prevent data corruption and enforece database consistency...

很多hoohah在这个问题的辩论），可能是由于什么是错误的假设 - 你必须使用 主键用于其他表中的联接和外键。这是假的。您可以使用ANY 键作为其他表中外键的目标。它可以是主键，备用键，或任何唯一索引或唯一约束。对于连接，你可以使用任何连接条件，它甚至不必是一个键，或一个idex，甚至独特！ （虽然如果它不是唯一的，你会在它创建的笛卡尔积中得到多行）。

Much of the hoohah (in the "debate" on this issue), may be due to what is a false assumption - that you have to use the Primary Key for joins and Foreign Keys in other tables. THIS IS FALSE. You can use ANY key as the target for foreign keys in other tables. It can be the Primary Key, an alternate Key, or any unique index or unique constraint. And as for joins, you can use anything at all for a join condition, it doesn't even have to be a key, or an idex, or even unique !! ( although if it is not unique you will get multiple rows in the Cartesian product it creates).