SQL-多对多表主键 [英] SQL - many-to-many table primary key

问题描述

在阅读有关此问题的评论后出现此问题:

This question comes up after reading a comment in this question:

数据库设计

创建多对多表时，应该在两个外键列上创建复合主键，还是创建自动增量替代的"ID"主键，然后将索引放在两个FK列上(也许是唯一的约束)?在每种情况下插入新记录/重新索引对性能有何影响?

When you create a many-to-many table, should you create a composite primary key on the two foreign key columns, or create a auto-increment surrogate "ID" primary key, and just put indexes on your two FK columns (and maybe a unique constraint)? What are the implications on performance for inserting new records/re-indexing in each case?

基本上，这个:

PartDevice
----------
PartID (PK/FK)
DeviceID (PK/FK)

vs.这个:

PartDevice
----------
ID (PK/auto-increment)
PartID (FK)
DeviceID (FK)

评论者说:

将两个ID设为PK意味着 表在磁盘上进行了物理排序 以该顺序.所以如果我们插入 (第1部分/设备1)，(第1部分/设备2)， (第2部分/设备3)，然后是(第1部分/设备3) 数据库将不得不打破 分开桌子并插入最后一个 在条目2和3之间.对于许多 记录，这变得非常成问题 因为涉及改组数百个， 成千上万条记录 每次添加一个.相比之下， 自动递增的PK允许新 记录要添加到末尾.

making the two IDs the PK means the table is physically sorted on the disk in that order. So if we insert (Part1/Device1), (Part1/Device2), (Part2/Device3), then (Part 1/Device3) the database will have to break the table apart and insert the last one between entries 2 and 3. For many records, this becomes very problematic as it involves shuffling hundreds, thousands, or millions of records every time one is added. By contrast, an autoincrementing PK allows the new records to be tacked on to the end.

我问的原因是因为我一直倾向于在没有代理自动增量列的情况下执行复合主键，但是我不确定代理键实际上是否性能更高.

The reason I'm asking is because I've always been inclined to do the composite primary key with no surrogate auto-increment column, but I'm not sure if the surrogate key is actually more performant.

推荐答案

对于简单的两列多对多映射，使用代理密钥没有真正的优势.保证在(col1,col2)上具有主键是唯一的(假定引用表中的col1和col2值是唯一的)，并且在(col2,col1)上具有单独的索引将捕获相反顺序执行速度更快的情况.代理人是在浪费空间.

With a simple two-column many-to-many mapping, I see no real advantage to having a surrogate key. Having a primary key on (col1,col2) is guaranteed unique (assuming your col1 and col2 values in the referenced tables are unique) and a separate index on (col2,col1) will catch those cases where the opposite order would execute faster. The surrogate is a waste of space.

您将不需要在各个列上建立索引，因为该表只能用于将两个引用的表连接在一起.

You won't need indexes on the individual columns since the table should only ever be used to join the two referenced tables together.

在我看来，您在问题中提及的评论不值得其使用的电子.听起来好像作者认为该表存储在数组中，而不是一种性能极高的平衡多向树结构.

That comment you refer to in the question is not worth the electrons it uses, in my opinion. It sounds like the author thinks the table is stored in an array rather than an extremely high performance balanced multi-way tree structure.

从一开始，就不必存储或获取排序后的表，只需索引即可.而且索引不会被顺序地存储，它会以一种有效的方式存储，以便能够快速检索.

For a start, it's never necessary to store or get at the table sorted, just the index. And the index won't be stored sequentially, it'll be stored in an efficient manner to be able to be retrieved quickly.

此外，绝大多数数据库表的读取时间远少于写入的时间.这样一来，您在选择端所做的任何事情都将比在插入端进行的任何事情都更具相关性.

In addition, the vast majority of database tables are read far more often than written. That makes anything you do on the select side far more relevant than anything on the insert side.

这篇关于SQL-多对多表主键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！