切换到顺序（梳）指南 - 现有数据怎么样？
我们有一个包含 500 多个表的数据库，其中几乎所有表都有一个数据类型 guid（唯一标识符）的集群 PK。
我们正在测试从通过 .NETs Guid.NewGuid() 方法生成的“正常”“随机”guid 到通过 NHibernate guid.comb 算法。这似乎运行良好，但是对于已经拥有数百万行具有“随机”主键值的客户端呢？
- 从现在开始生成的新 ID 将是连续的，他们会受益于此吗？
- 可以/应该对他们现有的数据做些什么吗？
预先感谢您对此的任何指示。
We have a database with 500+ tables, in which almost all the tables have a clustered PK that is of datatype guid (uniqueidentifier).
We are in the process of testing a switch from "normal" "random" guids generated through .NETs Guid.NewGuid() method to sequential guids generated through the NHibernate guid.comb algorithm. This seems to be working well, but what about clients that already have millions of rows with "random" primary key values?
- Will they benefit from the fact that new ids generated from now on will be sequential?
- Could/should anything be done to their existing data?
Thanks in advance for any pointers on this.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
你可以这样做，但我不确定你是否愿意这样做。我没有看到使用顺序 guid 有任何好处，事实上，不建议使用 guid 作为主键，除非涉及分布式/复制原因。您使用聚集索引吗？
话虽如此，如果您继续，我建议首先加载包含算法中的值的表。
您将会遇到外键的麻烦。您将需要在上述表中关联新旧 guid，删除外键，执行事务更新，然后重新应用外键。
我认为不值得这么麻烦，除非您完全放弃指南而采用基于整数的系统。
You could do this, but I'm not sure you would want to. I dont see any benefit in using sequential guids, in fact using guids is not recommended as a primary key unless there are distributed/replication reasons involved. Are you using a clustered index?
Having said that if you go ahead, I recommend loading a table with values from your algorithm first.
You are going to have hassles with foreign keys. You will need to associate the old and new guids in the aformentioned table, drop the foreign keys, perform a transactional update, then reapply the foreign keys.
I dont think it is worth the hassle unless you were moving away from guids altogether to say an integer based system.
这取决于表是聚集在主索引上还是聚集在另一个索引上。例如，如果您要在具有 GUID PK 和创建日期的表中创建大量新记录，则通常需要按创建日期进行聚类以优化插入操作。
另一方面，根据完成的查询，GUID 上的集群可能会更好，在这种情况下，使用顺序 GUID 可以帮助提高插入性能。我想说，如果不深入了解用法，就不可能对您的问题给出最终答案。
It depends whether the tables are clustered on the primary index or on another index. For instance, if you are creating large amounts of new records in a table with a GUID PK and a creation date, it usually makes sense to cluster by the creation date in order to optimize the insert operation.
On the other hand, depending on the queries done, a cluster on the GUID may be better, in which case using sequential GUIDs can help with the insert performance. I'd say that it isn't possible to give a final answer to your question without in-depth knowledge of the usage.
我面临着类似的问题，我认为可以通过编写应用程序来使用 NHibernate guid.comb 算法更新现有密钥来更新现有数据。为了将新键传播到相关的外键表，也许可以暂时级联更新？通过 .NET 代码执行此操作会比 SQL 脚本慢，另一个选择可能是在 SQL 中复制 guid.comb 逻辑，但不确定这是否可行。
如果您选择保留现有数据，使用 guid.comb 算法应该会有一些性能改进，插入时仍然会出现页面分割，但由于新的 guid 是顺序的而不是完全随机的，这至少会有所减少。另一个需要考虑的选项是删除 GUID 主键上的聚集索引，尽管我不确定现有查询性能会受到多少影响。
I'm facing a similar issue, I think it would be possible to update existing data by writing an application to update your existing keys using the NHibernate guid.comb algorithm. To propogate the new keys to related foreign key tables maybe it would be possible to temporarily cascade updates? Doing this through .NET code would be slower than an SQL script, another option might be to duplicate the guid.comb logic in SQL but not sure if this is possible.
If you choose to retain the existing data, using the guid.comb algorithm should have some performance improvement, there will still be page splitting when inserts occur but because new guids are sequential instead of totally random this will be at least somewhat reduced. Another option to consider would be to remove the clustered index on your GUID primary key, although I'm not sure how much existing query performance will be impacted.