如果某些内容不会更改,我是否需要主键?

发布于 2024-08-17 00:36:31 字数 99 浏览 9 评论 0原文

如果我有一个网站,用户可以标记另一个用户帖子,并且该帖子无法撤消或更改,我是否需要有主键?我的所有选择都将位于 post_id 上,并带有一个 where 子句来查看用户是否已标记它。

If I had a site where a user can flag another user post and it cannot be undone or changed, do I need to have a primary key? All my selects would be on the post_id and with a where clause to see if the user already flagged it.

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(9

凉城 2024-08-24 00:36:31

在我看来,从您的其他一些帖子中,您试图避免向表中添加主键的原因是为了节省空间。

别再这样想了。

在没有先测试它们是否真正有效的情况下进行这样的非标准优化是一个坏主意。您是否运行过一些测试,表明通过省略该表上的主键可以在数据库中节省大量空间?或者你只是猜测?

使用主键并不一定意味着您将使用更多空间。根据数据库的不同,如果您省略主键,它可能会为您添加一个隐藏字段(例如,如果您在 MySQL/InnoDB 中没有 PK,它会在包含 6 字节行 ID 的合成列上添加一个隐藏的聚集索引值())。如果您确实使用主键,则不必添加新列,您只需选择一些您知道应该是唯一的现有列即可。它不会占用更多空间,只是意味着数据将以不同的顺序存储,以便于搜索。

当您添加索引时,该索引将占用额外的空间,因为索引基本上只是表中几列的副本,加上返回原始表中行的链接。还记得当你没有 PK 时数据库使用的隐藏列吗?现在它必须使用它来查找您的行,因此您也会在索引中获得它的副本。如果您使用主键,那么您可能不需要您本来添加的索引之一,因此您实际上在这里节省了空间。

除此之外,如果表上没有主键,一些有用的数据库工具将无法正常工作。在你离开后,你会惹恼所有必须维护你的数据库的人。

那么告诉我,为什么你认为不拥有一个是个好主意?

It seems to me from some of your other posts that the reason you are trying to avoid adding a primary key to your table is to save space.

Stop thinking like that.

It's a bad idea to make non-standard optimizations like this without having tested them first to see if they actually work. Have you run some tests that shows that you save a significant amount of space in your database by omitting the primary key on this table? Or are you just guessing?

Using a primary key doesn't necessarily mean that you will use more space. Depending on the database, if you omit the primary key it might add a hidden field for you anyway (for example if you don't have a PK in MySQL/InnoDB it adds a hidden clustered index on a synthetic column containing 6 byte row ID values (source)). If you do use a primary key, rather than adding a new column you can just choose some existing columns that you know should be unique anyway. It won't take up any more space, it will just mean that the data will be stored in a different order to make it easier to search.

When you add an index, that index is going to take up extra space, as an index is basically just a copy of a few columns of the table, plus a link back to the row in the original table. Remember that hidden column the database uses when you don't have a PK? Well now it has to use that to find your rows, so you'll get a copy of it in your index too. If you use a primary key then you probably don't need one of your indexes that you would have added, so you're actually saving space here.

Besides all this, some useful database tools just won't work well if you don't have a primary key on your table. You will annoy everyone that has to maintain your database after you are gone.

So tell me, why do you think it's a good idea to NOT have one?

温馨耳语 2024-08-24 00:36:31

主键与数据是否可以更改无关 - 它是整行的单个引用点,这可以使查找和/或更改数据更快。

我的所有选择都将位于 post_id 上,并带有一个 where 子句来查看用户是否已标记它。

您需要提供有关业务规则的更多信息。例如,系统是否应该支持多个用户标记同一篇帖子?

如果答案是“否”,那么我将建模一个 POST_STATUS_CODE 表,并在您的 POSTS 表中拥有该表的外键。

如果答案是“是”,那么我仍然会有一个 POST_STATUS_CODE 表,而且还有一个链接 POSTSPOST_STATUS_CODE 表的表 - 比如 <代码>POSTS_STATUS_XREF。

我有一个 post_flag 表,其中包含 post_id、user_id(标记者)和 flag_type(ATM 作为字节)。在这种情况下,我不知道 PK 如何使其更快,但我想每行会占用 4 或 8 个字节。我正在考虑索引 post_id。如果我这样做,我还应该创建 PK 吗?

至少,我将使主键成为以下组合:

  • post_id
  • user_id

原因是主键确保不能重复。

主键可以由多个列组成 - 这称为复合键。这意味着这对值是唯一的。 IE:您不能拥有多个 1, 1 值的组合,但可以拥有 1,21,3、等等(反之亦然)。尝试添加重复项将导致重复主键错误。

A primary key has nothing to do with whether data can be changed - it's a single point of reference for an entire row, which can make looking up and/or changing data faster.

All my selects would be on the post_id and with a where clause to see if the user already flagged it.

You need to provide more information about business rules. For example, should the system support more than one user flagging the same post?

If the answer is "no", then I would model a POST_STATUS_CODE table and have a foreign key to the table in your POSTS table.

If the answer is "yes", then I would still have a POST_STATUS_CODE table but also a table linking the POSTS and POST_STATUS_CODE tables - say POSTS_STATUS_XREF.

I have a post_flag table with post_id, user_id (who flagged it) and flag_type (ATM as a byte). I don't see how PK will make it faster in this case but I imagine it will take up 4 or 8 bytes per row. I was thinking about indexing post_id. If I do should I still create a PK?

At a minimum, I would make the primary key to be a combination of:

  • post_id
  • user_id

The reason being that a primary key ensures that there can't be duplicates.

A primary key can be made up of more than one column - this is called a compound key. It means that the pair of values is unique. IE: You can't have more than one combination of 1, 1 values, but you could have 1,2, 1,3, etc (and vice versa). Attempts to add duplicates will result in duplicate primary key errors.

饮惑 2024-08-24 00:36:31

主键有助于加快查找和连接速度,因此如果可以的话,拥有主键总是好的。

Primary keys help speed up lookups and joins, so it's always nice to have if you can.

離殇 2024-08-24 00:36:31

即使用户要修改行,您也不需要主键。不过,每次查询该表时,主键都会优化性能。如果您认为您的表将增长到超过一千行左右,那么设置主键将带来显着的性能提升。

不创建主键的唯一好处是,这意味着您不必创建一个主键,我想这很公平:-P

您现在可以不用费心创建一个主键。您以后可以随时添加。没什么大不了的。不要让任何人欺负您,让您认为您现在绝对必须创建主键!您很快就会发现它非常慢:-P,然后您可以在此时添加主键。如果到那时你没有太多重复项:-P

You don't need a primary key, not even if users are going to modify rows. A primary key optimizes the performance every time you query that table though. If you think your table will grow larger than about a thousand rows or so, then setting a primary key will give a noticeable performance boost.

The only advantage in not creating a primary key really is that it means you don't have to create one, which is fair enough I suppose :-P

You could just not bother creating one for now. You can always add one later. Not a big deal. Don't let anyone bully you into thinking you absolutely must create a primary key right now! You'll see it being horribly slow soon enough :-P and then you can just add the primary key at that point. If you don't have too many duplicates by then :-P

情深缘浅 2024-08-24 00:36:31

最好有一个,如果只是因为您可能需要手动删除偶尔的记录(例如重复项),并且应该有一个唯一的标识符。

Best have one, if just because you may have to delete the occasional record manually (e.g. duplicates) and one should have a unique identifier for that.

时光瘦了 2024-08-24 00:36:31

简单的答案是肯定的。每个表都应该有一个主键(至少由一列组成)。没有的话你能得到什么好处呢?

The simple answer is yes. every table should have a primary key (made of at least one column). what benefit do you get for not having one?

洛阳烟雨空心柳 2024-08-24 00:36:31

在这种情况下,您可能不需要主键就可以摆脱困境,但无论如何我都倾向于在那里放置一个主键,仅仅是因为它相对简单,并且如果需求发生变化,可以节省返工。

In such a situation, you might be able to get away without one, but I'd be inclined to throw a primary key on there anyway, simply because it's relatively simple to do and will save rework if the requirements change.

苍景流年 2024-08-24 00:36:31

软件需求可能会迅速变化。客户可能会提出新的要求。因此,拥有主键可能会很有用,因为您可以在这种情况下消除完全不必要的数据迁移。

The software requirements may change rapidly. The customer may introduce new requirements. So having a primary key may be useful because you can eliminate totally unnecessary data migrations in such a situations.

尴尬癌患者 2024-08-24 00:36:31

阅读以下内容:“当我不需要主键时不使用主键可以吗?

是的,您确实需要一个主键。

如果您认为不这样做,您也可以使用文本文件进行存储,因为这意味着您不理解它们......

Read this: "Is it OK not to use a Primary Key When I don’t Need one?"

Yes, you do need a primary key.

You may as well use text files for storage if you don't think you do because it means you don't understand them...

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文