当我用尽bigint生成的密钥时会发生什么?怎么处理呢? [英] What happens when I exhaust a bigint generated key? How to handle it?
问题描述
我无法想象自己对此有一个很好的答案，所以我想到了在这里提问.在我看来，我一直想知道如果 MySQL 表中的AUTO INCREMENT PRIMARY ID列用完了会发生什么情况?
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?
例如，我有一个包含两列的表. ID (auto increment, primary, BIGINT unsigned)和 DESC (VARCHAR 255).我可以肯定BIGINT很多，但是可以达到其极限.如果 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.
推荐答案
它不会用完.
最大bigint是9223372036854775807.以1000次插入/秒计算，这相当于106751991167天.如果我的数学正确的话，将近3亿年.
The max bigint is 9223372036854775807 . At 1000 inserts/second that's 106751991167 days worth. Almost 300 million years, if my maths is right.
即使使用偏移量将其分区，例如每个服务器有100个服务器具有值的专用子范围(x*100+0 ... x*100+99)，也不会用光. 10,000台机器每秒执行100,000次插入操作可能会在大约三个世纪的时间内使您到达那里.当然，数百年来，每秒的交易量比纽约证券交易所要多...
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...
如果 do 超出了所生成密钥的数据类型大小限制，则新插入将失败.在PostgreSQL中(因为您已将此PostgreSQL标记)，您将看到:
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:
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)
对于普通的serial，您将得到一个不同的错误，因为sequence始终为64位，因此您将不得不将密钥类型更改为bigint或得到一个错误.像这样的错误:
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:
regress=# SELECT setval('serialtest_id_seq', 2147483647);
regress=# INSERT INTO serialtest (dummy) VALUES ('ham');
ERROR: integer out of range
如果您确实相信您的网站有可能达到应用程序中bigint的限制，则可以使用复合键(例如，(shard_id，子键)或uuid键).
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.
尝试在新应用程序中进行处理是过早的优化.认真地说，从新应用程序到那样的增长，您会使用相同的架构吗?还是数据库引擎?甚至是代码库?
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?
您可能还担心GUID键控系统中的GUID冲突.毕竟，生日悖论意味着 GUID碰撞比您想象的更可能-令人难以置信的是，极不可能.
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.
此外，正如巴里·布朗(Barry Brown)在评论中指出的那样，您永远不会存储那么多数据.这只是交易率高得离谱的高流失率表的一个问题.在那些表中，应用程序仅需要能够将密钥重置为零，重新编号条目或采用其他应对策略.坦白说，即使是高流量的消息队列表也不会达到顶峰.
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.
请参阅:
- this IBM info on serial exhaustion
- A recent blog post on this topic
严重的是，即使您构建下一个Gootwitfacegram，也要等到第三个应用程序重写的使用日期过后，这才是问题...
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...
这篇关于当我用尽bigint生成的密钥时会发生什么?怎么处理呢?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
