MySQL - 自动增量+复合主键 - 性能&诚信 [英] MySQL - autoincrement + compound primary key - performance & integrity

问题描述

我有一个数据库设计，使用复合主键来确保唯一性，哪些也是外键。

I have a database design that makes use of compound primary keys to ensure uniqueness and which are also foreign keys.

这些表然后链接到其他表相同的方法，所以最终复合键可以达到4或5列。这导致了一些相当大的JOIN，所以我想一个简单的解决方案是使用一个自动增量列，它不是主键的一部分，而是用作其他表的主键的一部分。

These tables are then linked to other tables in the same way, so that in the end the compound key can get up to 4 or 5 columns. This led to some rather large JOINs, so I thought a simple solution would be to use an autoincrement column which is not part of the primary key but which is used as part of the primary key of other table(s).

下面是一些显示一般布局的伪代码：

Here is some pseudo code showing the general layout :

CREATE TABLE Item (
id AUTO_INCREMENT,
...
PRIMARY KEY (id)
) ENGINE = InnoDB;

CREATE TABLE PriceCategory (
id AUTO_INCREMENT,
...
PRIMARY KEY (id)
)

CREATE TABLE ItemPriceCategory (
itemId,
priceCategoryId,
id AUTO_INCREMENT,
...
UNIQUE INDEX id,
PRIMARY KEY (eventId, priceCategoryId)
)

CREATE TABLE ClientType (
id AUTO_INCREMENT,
...
PRIMARY KEY (id)
)

CREATE TABLE Price (
itemPriceCategoryId,
clientTypeId,
id AUTO_INCREMENT,
...
UNIQUE INDEX id,
PRIMARY KEY (itemPriceCategoryId, clientTypeId)
)

table Purchase (
priceId,
userId,
amount,
PRIMARY KEY (priceId, userId)
)

表的名称已更改，以保护无辜;-)实际布局还有一点更深参考文献。

The names of tables have been changed to protect the innocent ;-) Also the actual layout is a little deeper in terms of references.

所以，我的问题是，这是一个可行的策略，从性能和数据完整性的角度来看？从购买表中的所有参考表格中获取所有密钥是否更好？

So, my question is, is this a viable strategy, from a performance and data integrity point of view ? Is it better to have all keys from all the referenced tables in the Purchase table ?

提前感谢。 / p>

Thanks in advance.

推荐答案

通常，主键上的建议是具有无意义，具有单列的不可变主键。自动递增整数很好。

Generally, the advice on primary keys is to have "meaningless", immutable primary keys with a single column. Auto incrementing integers are nice.

所以，我会扭转你的设计 - 你的连接表也应该有无意义的主键。例如：

So, I would reverse your design - your join tables should also have meaningless primary keys. For instance:

CREATE TABLE ItemPriceCategory (
itemId,
priceCategoryId,
id AUTO_INCREMENT,
...
PRIMARY KEY id,
UNIQUE INDEX  (eventId, priceCategoryId)
)

这样，itemPriceCategoryId列的价格是一个适当的外键，链接到ItemPriceCategory表的主键。

That way, the itemPriceCategoryId column in price is a proper foreign key, linking to the primary key of the ItemPriceCategory table.

你然后可以使用 http://dev.mysql.com/doc/refman /5.5/en/innodb-foreign-key-constraints.html 外键，以确保数据库的一致性。

You can then use http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html foreign keys to ensure the consistency of your database.

在性能方面，广义而言，这种策略应该比在连接中查询复合键更快，但是使用索引良好的数据库，您可能并没有注意到差异...

In terms of performance, broadly speaking, this strategy should be faster than querying compound keys in a join, but with a well-indexed database, you may not actually notice the difference...

这篇关于MySQL - 自动增量+复合主键 - 性能&诚信的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！