假设我们在MySQL数据库中有三个表:
Imagine we have three tables in a MySQL database:
- 帖子
- 类别
- category_post
帖子和类别之间存在一对多的关系,因此单个帖子可能具有多个类别.
There is a one-to-many relationship between posts and categories so that a single post may have many categories.
category_post 表是类别和帖子之间的数据透视表,并具有以下列:
The category_post table is the pivot table between categories and posts and has the following columns:
- id(主键,自动递增,大整数)
- category_id
- post_id
我们还假设 category_post 表中有1,000,000行.
Let's also imagine that we have 1,000,000 rows in our category_post table.
我的问题是:
在 category_post 表中具有 id 列是否对性能有好处?还是只占用了额外的空间?
Is there any performance benefit to having the id column in the category_post table or does it just take up extra space?
推荐答案帖子和类别可能是多对多的,而不是一对多的.
Posts and categories is probably many-to-many, not one-to-many.
最好使用多对多关系表
CREATE TABLE a_b ( a_id ... NOT NULL, b_id ... NOT NULL, PRIMARY KEY (a_id, b_id), INDEX(b_id, a_id) -- include this if you need to go both directions ) ENGINE = InnoDB;这样,您将自动在两个方向上进行聚类"查找,并且避免了不必要的表人工ID.
With that, you automatically get "clustered" lookups both directions, and you avoid the unnecessary artificial id for the table.
(顺便说一句,N.B.,一个隐式PK是6个字节,而不是8个字节.JeremyCole在该主题上发表了一篇冗长的文章.)
(By the way, N.B., an implicit PK is 6 bytes, not 8. There is a lengthy post by Jeremy Cole on the topic.)
一对多关系不需要此额外表.而是在另一个表中有一个ID.例如,一个城市表将在其中包含该国家/地区的ID.
A one-to-many relationship does not need this extra table. Instead, have one id inside the other table. For example, a City table will have the id for the Country in it.
更多推荐
在MySQL数据透视表中使用自动递增的主键有什么好处?
发布评论