MySQL自增主键用完的问题

Question

MySQL自增主键用完的问题

mysql

6

我维护了一个带有自增主键ID的表。当我删除一条记录并重新添加一条时，新的记录不会采用先前记录的ID，而是再次递增一个。这种情况正常吗？是否建议不要更改这种行为？我只是感觉这会创建一个不可扩展的系统，因为最终可能会用尽索引。

- user391986

你正在使用哪种整数数据类型，它是有符号的还是无符号的？？http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html - Jon Black

你问这个问题是正确的，因为我曾经看到一个取消订阅通讯的表格停止工作，因为它无法处理请求。 - James P.

7个回答

15

用户niceguy07上传了一张他的小猫咪的图片，这张图片被保存为000012334.jpg。这是因为你使用主键作为文件名，而不是将不可信的用户数据放入其中（这是个好主意）。

niceguy07向他的约会对象发送了一个带有“?picture_id=12334”参数的链接。

niceguy07删除了他的小猫咪图片，然后用户fatperv08上传了一张只穿着蝙蝠侠面具的自拍照。

由于你们的数据库重复使用主键值，所以不幸的是，现在带有“?picture_id=12334”参数的链接指向了一张裸体的肥胖变态穿着蝙蝠侠面具的图片。

重复使用已删除记录的主键值是非常糟糕的做法。事实上，如果主键泄露到数据库之外并在以下情况下被使用：

URL地址
文件名
与其他数据一起转储到文件中
等等

那么这实际上是一个错误（bug）。鉴于在实际应用中经常需要完成以上所有操作，不重用主键id是个好主意...

- bobflux

1

必须点赞这个真实世界的例子。有些管理员直到为时已晚才意识到其影响。 - Luke A. Leber

最好不要在文件名中公开主键...我知道你的只是一个简单的例子，但也许最好使用时间戳+uuid（如果不仅基于created_at时间，则需要额外的列来跟踪文件名）。 - user58446

@user58446 是的，那只是一个简单的例子，旨在回答问题。您的建议提供了额外的保护措施，以防止PK猜测攻击，这很好。 - bobflux

7

没问题。根据您期望的记录数，您可能希望确保它是 bigint 类型，但在大多数情况下 int 应该足够了。

- Christopher Armstrong

2

这是正常的。不用担心ID是连续的。

- n8wrl

2

这取决于您计划拥有的记录数量以及它们被删除和添加的频率，但如果这将成为一个问题，请使用bigint主键。

还有其他选项，例如使用GUID，如果您真的担心用完行数，但我从未遇到过实际需要bigint的情况，我只是偶尔在易失性表上使用它们以确保安全。

- Kendrick

不确定MySQL是否容易出现这种情况，但GUID可能会对索引维护造成问题。也许可以考虑使用Sequential-GUID？ - n8wrl

@n8wrl - 从本质上讲，所有数据库都难以索引非常随机的值。如果您使用GUID，则必须使其连续（例如，使用COMB GUID）才能使它们可行。 - Thomas

1

数据库设计中的主键应被视为所表示信息的唯一标识符。从表中删除一个ID实际上意味着这条记录将不再存在。如果有什么东西来代替它，你就是在说这条记录被复活了。现在，在第一次删除时，你应该同时删除所有外键引用。此时，人们可能会想说既然所有痕迹都消失了，那么没有理由不能有其他东西来代替它。但是，备份呢？假设你不小心删除了记录，并且它被其他东西替换了，你将无法轻松地恢复该记录。这也可能导致回滚问题。因此，重复使用ID不仅是不必要的，而且在数据库设计理论上基本上是错误的。

- dykstrad

是的，我只是想以更全面的方式澄清这个问题的概念本质。 - dykstrad

0

在dykstrad的精彩评论中补充一点

比如说，如果主键指向价格销售点，以一罐健怡可乐为例。懒散的数据管理会直接删除旧的价格并插入新的价格。而良好的数据管理应该更新该商品的信息，在保留关键字和引用关系的前提下，因为它仍然是同一个商品/唯一的关系。

- user3126255

为什么不对dyks的回答进行评论？ - Ali Fidanli

这不是对问题的回答。一旦您拥有足够的声望，您将能够在任何帖子上发表评论；相反，提供不需要询问者澄清的答案。-来自审查 - Anders_K

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Johan · Accepted Answer

这是有意而为之的设计，数百万个数据库都有像这样的整数主键。

如果您删除了90％的插入数据，则在插入了4亿行后将用尽密钥¹⁾
如果发生这种情况，您可以执行一项操作

ALTER TABLE `test`.`table1` MODIFY COLUMN `item_id` BIGINT UNSIGNED NOT NULL
, ROW_FORMAT = DYNAMIC;

其中列item_id将是您的主键。
之后，您将永远不必担心密钥空间耗尽。

不要试图使用bigint作为主键！

它会使所有查询变慢。
它会使表变得更大。
在InnoDB上，主键包含在每个二级索引中，因此使用小型主键对于插入操作来说要快得多。
对于大多数表格，您永远不需要它。

如果您知道您的大表将具有大于整数上限的行数，则可以将其设置为bigint，但您应该仅对确实需要的表格执行此操作。特别是在InnoDB表上。

不要使用GUID，它只是浪费了很多空间，99.99％的时间没有任何理由使所有东西都变慢。

¹⁾ 使用无符号整数作为主键。