logo标签列表

使用多列主键的利弊是什么？

sqlprimary-keycomposite-primary-key
25

我想看一个例子:

  • 何时使用合适
  • 何时不合适

选用不同的数据库会影响以上示例吗？

8个回答
46

这似乎是一个与代理键有关的问题，它们始终是自增长数字或GUID，因此是单列，而自然键通常需要多个信息才能真正唯一。如果您能够拥有一个只有一列的自然键，那么显然这个问题就无意义了。

有些人坚持只使用其中之一。花足够的时间在生产数据库上工作，你会发现没有独立于上下文的最佳实践。

其中一些答案使用SQL Server术语，但概念通常适用于所有DBMS产品：

使用单列代理键的原因：

  • 聚集索引。当数据库仅需附加时，聚集索引始终表现最佳-否则，DB必须执行页面分裂。请注意，仅适用于键是顺序的，即自动增量序列或顺序GUID的情况。对于任意GUID，性能可能会更差。

  • 关系。如果您的键是3、4、5列长，包括字符类型和其他非紧凑数据，则如果您必须在其他20个表中为此键创建外键关系，则会浪费巨大的空间，从而降低性能。

  • 唯一性。有时您没有真正的自然键。也许您的表是某种日志，并且可能会在相同时间得到两个相同的事件。或者您的真实键类似于已物化的路径，只能在行插入后才能确定。无论哪种方式，您始终希望聚集索引和/或主键是唯一的，因此如果没有其他真正唯一的信息，您别无选择，只能使用代理键。

  • 兼容性。 大多数人永远不必处理这个问题，但如果自然键包含像 hierarchyid 这样的内容，可能有些系统甚至无法读取它。在这种情况下，您必须为这些应用程序创建一个简单的自动生成的替代键。即使自然键中没有任何“奇怪”的数据，一些数据库库也很难处理多列主键，尽管这个问题正在迅速消失。

    • 使用多列自然键的原因

    • 存储。 很多人从未处理过大型数据库，因此不需要考虑这个因素。但当一个表具有数十亿或数万亿行时，您将希望尽可能地减少该表中的绝对最小数据量。

    • 复制。 是的，您可以使用 GUID 或顺序 GUID。 但 GUID 也有它们自己的权衡之处，如果您不能或不想出于某些原因使用 GUID，则多列自然键是复制方案的更好选择，因为它本质上是全局唯一的 - 也就是说，您不需要特殊算法使其唯一，它是根据定义唯一的。这使得分布式架构的推理非常容易。

    • 插入/更新性能。 替代键并不是免费的。 如果您有一组唯一且频繁查询的列，因此需要在这些列上创建覆盖索引，则索引最终几乎与表一样大，这浪费空间并需要在做任何修改时更新第二个索引。如果您可以只在一个表上有一个索引（聚集索引），您应该这么做！

    这就是我现在想到的。如果我突然想起其他内容，我会进行更新。

    简洁而有用。我通常使用自然键并避免使用代理键，但我需要一个表来存储用户日志，并在阅读了你有用的回答后决定使用它。谢谢朋友。 - QMaster
    我刚刚进行了一项测试。其中我有自动递增主键+2个索引，没有主键但有2个索引，以及3列的组合作为主键和1个索引。我的列包括整数、字符串和日期时间。我发现将这3列组合作为主键时性能最佳。 - Furkan Gözükara
    6
    您几乎总是需要一个主键，所以我假设选择是在现有的两个列中选择作为主键，还是创建一个新的自动递增的主键，并将普通唯一约束放在这两个列上。
    当您需要一个两列主键时：
    - 如果您有一个中间表引用了另外两个表，并且它仅由两个外键组成，即多对多关系，则没有必要添加额外的列来作为主键。使用已有的两列作为主键即可。
    当您需要一个自动递增的主键时：
    - 如果您从另一个表引用一个表，则希望目标表的主键很小，因为该数据将作为外键在引用表中重复出现。您还希望比较速度快。 - 您向表中添加的每个索引都包含聚集键的副本（通常与主键相同）。如果聚集键比必须的大，那么该表上的每个索引也将比必须的大。
    每个添加到表中的索引都包含主键的副本。如果您的主键比必要的大，那么该表上的每个索引也将比必要的大。如果一个表上没有主键引用，那么在该表上创建的索引将不会引用任何主键。您是否知道这是真的，还是只是假设呢？ - Evan Carroll
    每个索引都包含一个聚簇键，该键可以是主键也可能不是（通常是）。 - Aaronaught
    如果在主键之前创建索引会怎样？我认为索引指向行是显而易见的，但是你认为行指针仅仅是主键吗？主键索引指向什么？我认为这个说法根本不正确。 - Evan Carroll
    @Evan：如果你没有聚集键（可能是主键，也可能不是），那么每个非聚集索引都包含一个空的副本，因此该语句仍然成立。 ;) - Aaronaught
    @Aaronaught：关于聚焦键与主键的问题，你提出了很好的观点，你说得很对——我在这里做了一个可能不正确的假设。 - Mark Byers
    @Evan Carroll，“你知道这是真的，还是只是假设？”在Aaronaught的纠正之后，我知道对于某些数据库来说这是真的。显然，它可能取决于数据库，并且可能有一种不同的方式进行操作，但我不知道有任何其他方式可以操作数据库。这里是SQL Server文档的链接：如果表具有聚集索引或索引位于索引视图上，则行定位器是该行的聚集索引键。http://msdn.microsoft.com/en-us/library/ms177484.aspx - Mark Byers
    5

    我认为通常情况下（至少从应用程序开发者的角度来看），将主键设置为自动生成的键并在多个列上创建唯一约束和索引更好。

    • 使用单个自动生成的主键，您可以轻松地从其他表中添加对该表的引用。
    • 自动生成的主键与ORM库更简单地配合使用。
    • 此外，如果将来您的唯一性约束发生更改，则无需更改现有的主键。

    我曾经遇到过几种令人头痛的情况，因为数据库管理员认为多列主键总是足够的，而未来的要求变化证明了这是不正确的。

    对于代码的未来性，这是一个很好的观点。支持未来更改的灵活性非常重要。 - CodeToad
    1
    我刚刚进行了3个场景的测试，让我展示一下全部。
    以下是示例查询及其相对运行时间。这些表具有完全相同的数据，超过750k行。
    性能最佳的情况是将3列包括为主键索引，即tblUserTestIp。

    enter image description here

    这里是所有三个表的结构和索引。
    CREATE TABLE [dbo].[tblUserIPLogs](
    [UserId] [int] NOT NULL,
    [LoggedIpAdress] [varchar](15) NOT NULL,
    [LoginDate] [datetime] NOT NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[tblUserTestIp](
    [UserId] [int] NOT NULL,
    [LoggedIpAdress] [varchar](15) NOT NULL,
    [LoginDate] [datetime] NOT NULL,
 CONSTRAINT [PK_tblUserTestIp] PRIMARY KEY CLUSTERED 
(
    [UserId] ASC,
    [LoggedIpAdress] ASC,
    [LoginDate] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]


CREATE TABLE [dbo].[tblUserTestIpUnique](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [UserId] [int] NOT NULL,
    [LoggedIpAdress] [varchar](15) NOT NULL,
    [LoginDate] [datetime] NOT NULL,
 CONSTRAINT [PK_tblUserTestIpUnique] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]



CREATE NONCLUSTERED INDEX [index1] ON [dbo].[tblUserIPLogs]
(
    [UserId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO

CREATE NONCLUSTERED INDEX [index2] ON [dbo].[tblUserIPLogs]
(
    [LoggedIpAdress] ASC
)
INCLUDE([UserId]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO

CREATE NONCLUSTERED INDEX [index3] ON [dbo].[tblUserTestIp]
(
    [LoggedIpAdress] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [index1] ON [dbo].[tblUserTestIpUnique]
(
    [UserId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO


CREATE NONCLUSTERED INDEX [index2] ON [dbo].[tblUserTestIpUnique]
(
    [LoggedIpAdress] ASC
)
INCLUDE([UserId]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO


ALTER TABLE [dbo].[tblUserIPLogs] ADD  CONSTRAINT [DF_tblUserIPLogs_LoggedIpAdress]  DEFAULT ('null') FOR [LoggedIpAdress]
GO
ALTER TABLE [dbo].[tblUserIPLogs] ADD  CONSTRAINT [DF_tblUserIPLogs_LoginDate]  DEFAULT (sysutcdatetime()) FOR [LoginDate]
GO
ALTER TABLE [dbo].[tblUserTestIp] ADD  CONSTRAINT [DF_tblUserIPLogs_LoggedIpAdress2]  DEFAULT ('null') FOR [LoggedIpAdress]
GO
ALTER TABLE [dbo].[tblUserTestIp] ADD  CONSTRAINT [DF_tblUserIPLogs_LoginDate2]  DEFAULT (sysutcdatetime()) FOR [LoginDate]
GO
ALTER TABLE [dbo].[tblUserTestIpUnique] ADD  CONSTRAINT [DF_tblUserIPLogs_LoggedIpAdress3]  DEFAULT ('null') FOR [LoggedIpAdress]
GO
ALTER TABLE [dbo].[tblUserTestIpUnique] ADD  CONSTRAINT [DF_tblUserIPLogs_LoginDate3]  DEFAULT (sysutcdatetime()) FOR [LoginDate]
GO
    尽管实际性能没有差别，但我选择给它一个+1，因为它展示了在现实世界情况下做出这些决策的复杂性，稍微不同的情况会使任何选择更好或更糟。 - Collector
    1

    一些例子...

    适当的：

    • 在大多数情况下，实现大多数多对多关系时使用 OLTP 系统。

    不适当的：

    • 在 OLAP 系统中的维度表中 - 您希望使您的维度键尽可能小，以便您的事实表尽可能小（和快速）。

    • 在您不确定组合是否唯一时。尽管这是一个相当糟糕的例子，但“人员”表将是多列 PK 的不良选择。

    0

    一个适用的例子是当你有一个连接不同表格的外键字段链接表。

    一般来说，尽可能使用现有的、标识字段作为主键是个好主意。如果你没有自然的id字段，而且你需要组合很多字段才能得到唯一的PK，那么使用自动编号可能更好。主键超过两个字段会变得混乱。

    0
    我们发现，在应用程序中使用多列索引和键可以显著提高性能。这使我们能够在最常见的查询上创建索引，而主表甚至不需要访问，因为整个选择子句可以在索引中完成。然而，这取决于您的应用程序和数据集。
    请注意，这并不适用于所有数据库的通用建议。例如，在Teradata上，仅当查询中使用索引内的所有列时，多列索引才会被使用，因为Teradata使用哈希进行索引。 - lins314159
    是的，这是在一个拥有数亿行数据的企业系统上。这就是为什么我说我们的应用程序对于大多数应用程序来说，您可能不会获得我们所拥有的好处。我们的索引是由IBM的DB2工程师进行调整以获得最大的收益。 - rerun
    是的，但如果你有一个五列主键，任何来自子表的JOIN都会变得非常混乱！它需要五个条件才能建立JOIN..... 来自地狱的JOINs！ - marc_s
    marc_s：你假设该关键字在这些子表中被使用。但这并不一定是这样的。关键字并不意味着与外键相同。 - nvogel
    -2
    有时候，复合自然键是很直观的。例如，假设您有一个公司表（PK 是 ComapnyId），其中包含一些公司详细信息的列。您还需要存储公司历史上的 CEO 名称。自然不变量是一个公司在任何时候只能有一个 CEO。因此，创建一个 CompanyCeo 表是很直观的，它具有由 CompanyId（Company 表中的 FK）+ FromDate 组成的复合 PK。该表中的其他列可能是 ToDate 和 CeoName。这样，您可以保证只有一个 CEO 可以在特定日期开始。
    网页内容由stack overflow 提供, 点击上面的
    可以查看英文原文，
    原文链接