是COMB GUID使用Rails 3.1如果我使用的GUID主键是个好主意？ [英] Is COMB GUID a good idea with Rails 3.1 if I use GUIDs for primary keys?
问题描述
我使用Rails 3.1的PostgreSQL 8.4。假设我想/需要使用GUID主键。一个潜在的缺点是索引碎片。在MS SQL，对于推荐的解决方法是使用特殊的顺次的GUID。一个方法成本以序贯是的GUID相结合的GUID，可替代6个字节的时间戳的GUID的结束的MAC地址部分。这有一些主流应用：梳子都可以原生支持NHibernate的（<一href="http://www.symbolsource.org/Public/Metadata/Default/Project/NHibernate/3.0.0.GA/Release/All/NHibernate/NHibernate/Id/GuidCombGenerator.cs"相对=nofollow> NHibernate的/ ID / GuidCombGenerator.cs ）。
我想我已经找到了如何在Rails的梳的GUID（与UUIDTools 2.1.2宝石的帮助），但它留下了一些悬而未决的问题：
- 在PostgreSQL可以从索引碎片受苦时，主键键入UUID？
- 是不成避免的，如果低6个字节的GUID是连续的？
- 是GUID作为实现低于可接受的，可靠的方法来创建Rails中连续的GUID梳子？
感谢您的想法。
create_contacts.rb 迁移
类CreateContacts＆LT; ActiveRecord的::迁移
可变形点焊了
CREATE_TABLE：联系人：ID =＆GT;假做| T |
t.column：ID，：UUID，：空=＆GT;假＃手动创建：ID与基础DB型UUID
t.string：FIRST_NAME
t.string：姓氏
t.string：电子邮件
t.timestamps
结束
执行ALTER TABLE联系人添加PRIMARY KEY（ID）;
结束
＃不能使用可逆的迁移，因为它会尝试运行执行再
DEF下降
drop_table：联系人＃也下降主键
结束
结束
/app/models/contact.rb
类联系与LT;的ActiveRecord :: Base的
需要'uuid_helper#rails 3不会自动加载，从LIB / *
包括UUIDHelper
set_primary_key：ID
结束
/lib/uuid_tools.rb
要求'uuidtools
模块UUIDHelper
高清self.included（基地）
base.class_eval做
包括InstanceMethods
attr_readonly：编号只可写上了一个新纪录
before_create：set_uuid
结束
结束
模块InstanceMethods
私人
高清set_uuid
＃MS SQL语法：CAST（CAST（NEWID（）为二进制（10））+ CAST（GETDATE（）为二进制（6））AS UNIQUEIDENTIFIER）
＃获取当前时间的对象
UTC_TIMESTAMP = Time.now.utc
＃转换与毫秒为整型：（纪元以来的秒数* 1000）+（6位数字的毫秒分数/ 1000）
utc_timestamp_with_ms_int =（utc_timestamp.tv_sec * 1000）+（utc_timestamp.tv_usec / 1000）
＃格式为十六进制，最小的12位数字，有前导零。需要注意的是12进制数字处理，以每年10889（*）。
utc_timestamp_with_ms_hexstring =％012x％utc_timestamp_with_ms_int
＃如果我们提供UUIDTOOLS与MAC地址，它会使用，而不是从系统中检索。
＃使用一个普通的前pression分割成数组，然后将：字符，所以它看起来像一个MAC地址。
UUIDTools :: UUID.mac_address =（utc_timestamp_with_ms_hexstring.scan / {2} /）加入。（：）
＃生成版本1 UUID（参见RFC 4122）。
comb_guid = UUIDTools :: UUID.timestamp_create（）。to_s
＃将generted组合GUID来.ID
self.id = comb_guid
＃（*）由6个字节的时间戳，包括毫秒处理的最大时间的说明：
＃如果utc_timestamp_with_ms_hexstring =FFFFFFFFFFFF（12 F的），然后
＃Time.at（浮动（utc_timestamp_with_ms_hexstring.hex）/ 1000）.utc.iso8601（10）=10889-08-02T05：31：50.6550292968Z。
结束
结束
结束
- 在PostgreSQL可以从索引碎片受苦时，主键键入UUID？
是的，这是可以预期的。但是，如果你要使用不会发生梳策略。该行将始终处于顺序（这是不完全正确的，但和我一起承担）。
此外，VS VARCHAR本地pgsql的UUID之间的性能的不是所有的不同。还有一点要考虑的问题。
- 是不成避免的，如果低6个字节的GUID是连续的？
在我的测试中我发现，UUID1（RFC 4122）是连续的，有已经在生成的UUID添加时间戳。但是，是的，在过去的6个字节添加时间戳会安抚的排序。这就是我所做的，无论如何，因为很明显的时间戳已经present不是为了保证。更多关于COMB 这里
- 是GUID作为实现低于可接受的，可靠的方法来创建Rails中连续的GUID梳子？
我不使用铁轨，但我会告诉你我是如何在Django做的：
进口UUID，时间
高清uuid1_comb（OBJ）：
返回uuid.uuid1（结点= INT（time.time（）* 1000））
其中，节点是一个48位的正整数标识的硬件地址。
关于您的实现，使用UUID的是，你可以放心地生成它们在数据库外的主要优势之一，所以，使用辅助类是有效的方法来做到这一点。您可以随时使用外部服务UUID生成如雪花，但它可能是premature optimizacion在这点。
I'm using Rails 3.1 with PostgreSQL 8.4. Let's assume I want/need to use GUID primary keys. One potential drawback is index fragmentation. In MS SQL, a recommended solution for that is to use special sequential GUIDs. One approach to sequential GUIDs is the COMBination GUID that substitutes a 6-byte timestamp for the MAC address portion at the end of the GUID. This has some mainstream adoption: COMBs are available natively in NHibernate (NHibernate/Id/GuidCombGenerator.cs).
I think I've figured out how to create COMB GUIDs in Rails (with the help of the UUIDTools 2.1.2 gem), but it leaves some unanswered questions:
- Does PostgreSQL suffer from index fragmentation when the PRIMARY KEY is type UUID?
- Is fragmentation avoided if the low-order 6 bytes of the GUID are sequential?
- Is the COMB GUID as implemented below an acceptable, reliable way to create sequential GUIDs in Rails?
Thanks for your thoughts.
create_contacts.rb migration
class CreateContacts < ActiveRecord::Migration
def up
create_table :contacts, :id => false do |t|
t.column :id, :uuid, :null => false # manually create :id with underlying DB type UUID
t.string :first_name
t.string :last_name
t.string :email
t.timestamps
end
execute "ALTER TABLE contacts ADD PRIMARY KEY (id);"
end
# Can't use reversible migration because it will try to run 'execute' again
def down
drop_table :contacts # also drops primary key
end
end
/app/models/contact.rb
class Contact < ActiveRecord::Base
require 'uuid_helper' #rails 3 does not autoload from lib/*
include UUIDHelper
set_primary_key :id
end
/lib/uuid_tools.rb
require 'uuidtools'
module UUIDHelper
def self.included(base)
base.class_eval do
include InstanceMethods
attr_readonly :id # writable only on a new record
before_create :set_uuid
end
end
module InstanceMethods
private
def set_uuid
# MS SQL syntax: CAST(CAST(NEWID() AS BINARY(10)) + CAST(GETDATE() AS BINARY(6)) AS UNIQUEIDENTIFIER)
# Get current Time object
utc_timestamp = Time.now.utc
# Convert to integer with milliseconds: (Seconds since Epoch * 1000) + (6-digit microsecond fraction / 1000)
utc_timestamp_with_ms_int = (utc_timestamp.tv_sec * 1000) + (utc_timestamp.tv_usec / 1000)
# Format as hex, minimum of 12 digits, with leading zero. Note that 12 hex digits handles to year 10889 (*).
utc_timestamp_with_ms_hexstring = "%012x" % utc_timestamp_with_ms_int
# If we supply UUIDTOOLS with a MAC address, it will use that rather than retrieving from system.
# Use a regular expression to split into array, then insert ":" characters so it "looks" like a MAC address.
UUIDTools::UUID.mac_address = (utc_timestamp_with_ms_hexstring.scan /.{2}/).join(":")
# Generate Version 1 UUID (see RFC 4122).
comb_guid = UUIDTools::UUID.timestamp_create().to_s
# Assign generted COMBination GUID to .id
self.id = comb_guid
# (*) A note on maximum time handled by 6-byte timestamp that includes milliseconds:
# If utc_timestamp_with_ms_hexstring = "FFFFFFFFFFFF" (12 F's), then
# Time.at(Float(utc_timestamp_with_ms_hexstring.hex)/1000).utc.iso8601(10) = "10889-08-02T05:31:50.6550292968Z".
end
end
end
- Does PostgreSQL suffer from index fragmentation when the PRIMARY KEY is type UUID?
Yes, it's to be expected. But if you're going to use the COMB strategy that won't happen. The rows will be always in order(that's not entirely true, but bear with me).
Also, the performance between native pgsql UUID vs VARCHAR is not all that different. Another point to consider.
- Is fragmentation avoided if the low-order 6 bytes of the GUID are sequential?
In my test I've found that UUID1(RFC 4122) is sequential, there's already a timestamp added in the generated uuid. But yes, adding a timestamp in the last 6 bytes will reassure that ordering. That's what I did anyway, because apparently the timestamp already present is not guarantee of order. More about COMB here
- Is the COMB GUID as implemented below an acceptable, reliable way to create sequential GUIDs in Rails?
I'm not using rails, but I'll show you how I did it in django:
import uuid, time
def uuid1_comb(obj):
return uuid.uuid1(node=int(time.time() * 1000))
Where node is a 48-bit positive integer identifying the hardware address.
About your implementation, one of the main advantages of using uuid's is that you can safely generate them outside the database, so, using a helper class is one valid way to do it. You can always use an external service for uuid generation like snowflake, but it may be premature optimizacion at this point.
这篇关于是COMB GUID使用Rails 3.1如果我使用的GUID主键是个好主意？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
