当我耗尽一个bigint生成的键时会发生什么?如何处理它?

I can't imagine for myself a good answer for this, so I thought of asking it here. In my mind, I'm always wondering what will happen if the AUTO INCREMENT PRIMARY ID column in my MySQL table exhausted?

我想不出一个好的答案，所以我想在这里问一下。我一直在想，如果MySQL表中的自动增量主ID列用完会发生什么?

Say for example, I have a table which has two columns. An ID (auto increment, primary, BIGINT unsigned) and DESC (VARCHAR 255). I know for sure BIGINT is a lot, but it can reach its limit. How do I handle the scenario wherein in case the ID reach its limit? Do I need another server? If then how can I sync it? Is this the right way? Any insights friends.

例如，我有一个表，它有两列。ID(自动增量、主数据、未签名的数据)和DESC (VARCHAR 255)。我肯定BIGINT很多，但是它可以达到它的极限。如果ID达到极限，我该如何处理这种情况?我需要另一台服务器吗?如果是这样，我如何同步它?这条路对吗?任何见解的朋友。

4 个解决方案

#1

It won't run out.

它不会耗尽。

The max bigint is 9223372036854775807 . At 1000 inserts/second that's 106751991167 days worth. Almost 300 million years, if my maths is right.

max bigint是9223372036854775807。1000个插入/秒，106751991167天。差不多有3亿年了，如果我的数学是对的。

Even if you partition it out, using offsets where say 100 servers each have a dedicated sub-range of the values (x*100+0 ... x*100+99), you're not going to run out. 10,000 machines doing 100,000 inserts/second might get you there in about three centuries. Of course, that's more transactions per second than the New York Stock Exchange for hundreds of years solid...

即使您将其分区，使用偏移量，也就是说100个服务器都有一个值的专用子范围(x*100+0……)x*100+99，你不会用完的。一万台机器每秒钟做十万次插入，大概三个世纪就能完成。当然，这比纽约证券交易所几百年来每秒的交易量还要多。

If you do exceed the data type size limit of the generated key, new inserts will fail. In PostgreSQL (since you've tagged this PostgreSQL) with a bigserial you'll see:

如果您确实超出了生成的键的数据类型大小限制，那么新的插入将失败。在PostgreSQL(因为您已经标记了这个PostgreSQL)中，您将看到一个bigserial:

CREATE TABLE bigserialtest ( id bigserial primary key, dummy text );
SELECT setval('bigserialtest_id_seq', 9223372036854775807);
INSERT INTO bigserialtest ( dummy ) VALUES ('spam');

ERROR:  nextval: reached maximum value of sequence "bigserialtest_id_seq" (9223372036854775807)

For an ordinary serial you'll get a different error, because the sequence is always 64-bit, so you'll reach the point where you have to change the key type to bigint or get an error like:

对于一个普通的串行，你会得到一个不同的错误，因为序列总是64位的，所以你会到达一个点，你必须把键类型改成bigint或者得到一个错误，比如:

regress=# SELECT setval('serialtest_id_seq', 2147483647);
regress=# INSERT INTO serialtest (dummy) VALUES ('ham');
ERROR:  integer out of range

If you truly believe that it's possible for your site to reach the limit on a bigint in your application, you could use a composite key - say (shard_id, subkey) - or a uuid key.

如果您真的相信您的站点可以在您的应用程序中达到bigint的限制，您可以使用组合键(shard_id、subkey)或uuid键。

Trying to deal with this in a new application is premature optimization. Seriously, from a new application to that kind of growth, will you be using the same schema? Or database engine? Or even codebase?

在新的应用程序中尝试处理这个问题是不成熟的优化。说真的，从一个新的应用程序到那种增长，你会使用相同的模式吗?或数据库引擎?甚至代码库?

You might as well worry about GUID collisions in GUID keyed systems. After all, the birthday paradox means that GUID collisions are more likely than you think - at incredibly, insanely unlikely.

您可能还需要担心GUID键控系统中的GUID冲突。毕竟，“生日悖论”意味着GUID碰撞的可能性比你想象的要大——令人难以置信的是，极其不可能。

Furthermore, as Barry Brown points out in the comments, you'll never store that much data. This is only a concern for high churn tables with insanely high transaction rates. In those tables, the application just needs to be capable of coping with the key being reset to zero, entries renumbered, or other coping strategies. Honestly, though, even a high traffic message queue table isn't going to top out.

此外，正如Barry Brown在评论中指出的，你永远不会存储那么多的数据。这只是对高流动表和高得惊人的交易率的关注。在这些表中，应用程序只需要能够处理被重置为零的密钥、重新编号的条目或其他应对策略。不过，老实说，即使是一个高流量的消息队列表也不可能达到顶点。

See:

看到的:

this IBM info on serial exhaustion
这是IBM关于串行耗尽的信息
A recent blog post on this topic
最近一篇关于这个话题的博客文章

Seriously, even if you build the next Gootwitfacegram, this won't be a problem until way past the use-by date of your third application rewrite...

认真地说，即使您构建了下一个Gootwitfacegram，直到您的第三个应用程序重写之后，这才会成为一个问题……

4 个解决方案

#1

更多相关文章

随机推荐