征求意见：所有表的一个序列 [英] Asking for opinions : One sequence for all tables
问题描述
我们在早期的讨论中已经得出结论：自然主键是坏的，人工主键是好的
早期使用Hibernate我已经看到Hibernate默认为所有表创建了一个序列。起初我很困惑，为什么要这样做。但后来我看到了这样的优点，它使得父母和孩子的联系成为愚弄。因为没有表具有相同的主键值，所以意外地将父项与不是子节点的表相连接不会产生任何结果。
有没有人会看到这种方法的任何缺点。我只看到一个：您的数据库中不能有超过99999999999999999999999条记录。
根据数据库中实现的顺序总是碰到相同的顺序可以更好还是更糟。当只有几个或只有一个线程请求新值时，将不会出现锁定问题。但是一个坏的实现可能会导致拥塞。
另一个问题是回滚事务：序列不会被回滚（因为有人可能已经请求了更高的值）所以你可以有很大的差距，这会比你预期的快得多。 OTOH，需要一些时间才能吃2到4亿个ID（如果你只使用32位（签名）），那么在实践中很少出现问题。
最后，如果你必须，你不能轻易重置序列。但是如果您需要重新启动序列（例如，自午夜以来的记录数），可以告诉Hibernate创建/使用第二个序列。
主要优点是您可以通过ID唯一标识数据库中的任何位置的对象。这意味着您可以严格地减少您在生产系统中写入的日志信息，如果您只有ID，则仍然会找到某些内容。
Here's another one I've been thinking about lately. We have concluded in earlier discussions : 'natural primary keys are bad, artificial primary keys are good.' Working with Hibernate earlier I have seen that Hibernate default creates one sequence for all tables. At first I was puzzled by this, why would you do this. But later I saw the advantage that it makes linking parents and children fool proof. Because no tables have the same primary key value, accidentally linking a parent with a table that is not a child gives no results.
Does anyone see any downsides to this approach. I only see one : you cannot have more than 999999999999999999999999999 records in your database.
Depending on how sequences are implemented in the database, always hitting the same sequence can be better or worse. When only a few or only one thread request new values, there will be no locking issues. But a bad implementation could cause congestion.
Another problem is rolling back transactions: Sequences don't get rolled back (because someone else might have requested a higher value already), so you can have large gaps which will eat your number space much more quickly than you might expect. OTOH, it will take some time to eat 2 or 4 billion IDs (if you "only" use 32 bit (signed) ints), so it's rarely an issue in practice.
Lastly, you can't easily reset the sequence if you have to. But if you need to have a restarting sequence (say, number of records since midnight), you can tell Hibernate to create/use a second sequence.
A major advantage is that you can uniquely identify objects anywhere in the DB just by the ID. That means you can severely cut down the log information you write in the production system and still find something if you only have the ID.
这篇关于征求意见：所有表的一个序列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
