我们当前的PostgreSQL数据库使用GUID作为主键,并将其存储为文本字段.
我对此的最初react 是,try 执行任何类型的最小笛卡尔连接都将是一场噩梦,即试图找到所有匹配的记录.然而,也许我对数据库索引的有限理解在这里是错误的.
我认为我们应该使用UUID,因为它们存储为GUID的二进制表示形式,而文本不是,并且在文本列上获得的索引量是最小的.
这将是一个重大的项目来改变这些,我想知道这是否值得?
我们当前的PostgreSQL数据库使用GUID作为主键,并将其存储为文本字段.
我对此的最初react 是,try 执行任何类型的最小笛卡尔连接都将是一场噩梦,即试图找到所有匹配的记录.然而,也许我对数据库索引的有限理解在这里是错误的.
我认为我们应该使用UUID,因为它们存储为GUID的二进制表示形式,而文本不是,并且在文本列上获得的索引量是最小的.
这将是一个重大的项目来改变这些,我想知道这是否值得?
正如@Kevin所提到的,要确定你的确切数据,唯一的方法是比较和对比这两种方法,但从你所描述的,我不明白为什么这会与任何其他情况不同,即字符串是表中的primary key或唯一索引的一部分.
可以预先说的是,索引可能会更大,因为它们必须存储更大的字符串值,从理论上讲,索引的比较将需要更长的时间,但我不主张过早优化,如果这样做会很痛苦的话.
根据我的经验,我在一个拥有数十亿行的表上使用md5sums,在一个唯一索引上看到了非常好的性能.我发现与查询有关的其他因素往往会导致性能问题.例如,当您最终需要查询一个非常大的表,比如数十万行时,顺序扫描最终是更好的 Select ,因此这是查询计划器 Select 的,并且可能需要更长的时间.
对于这种情况,还有其他缓解策略,比如将查询分块,然后对结果分块(例如,手动模拟在Hadoop范围内的Hive或Impala中所做的事情).
Re:您对文本索引的担忧,虽然我确信在某些情况下,数据集会产生密钥分发,导致其性能非常差,但GUI(很像md5sums、sha1等)通常应该索引得非常好,不需要顺序扫描(除非,正如我上面提到的,您查询了大量表).
影响索引性能的一个重要因素是有多少个唯一值.因此,对于一个包含大量行的表,布尔索引不太可能有帮助,因为对于索引中的任何值(true、false和可能的NULL),它基本上都会有大量的行冲突.另一方面,GUID索引可能有大量的值,并且没有冲突(从理论上来说,因为它们是GUID).
Edit in response to comment from OP:
你是说UUID guid和文本guid在索引方面是一样的吗?我们的整个表 struct 都使用带有类似guid字符串的文本字段,但我不确定Postgre是否能将其识别为guid.只是一个碰巧是唯一的字符串.
不完全一样.但是,我想说的是,在这种特殊情况下,它们应该具有非常相似的性能,我不明白为什么需要提前进行优化,尤其是考虑到你说这样做将是一项非常复杂的任务.
如果在您的特定环境中遇到性能问题,您随时可以在以后进行更改.然而,正如我前面提到的,我认为如果你遇到这种情况,还有其他事情可能会产生比更改PK数据类型更好的性能.
UUID是128位的数据类型(16字节),而text有1或4字节的开销加上字符串的实际长度.对于GUID,这将意味着一个33字节的minimum字节,但根据使用的编码可能会有很大的不同.
因此,考虑到这一点,基于文本的UUID的索引肯定会更大,因为值更大,比较两个字符串和两个数值在理论上效率更低,但在这种情况下,至少在通常情况下,不太可能产生巨大的差异.
我不会提前优化,因为这样做会产生巨大的成本,而且可能永远都不需要.如果这个时间真的到来,这座桥可以跨越(尽管我会首先考虑其他查询优化,正如我前面提到的).
至于Postgres是否知道字符串是GUID,默认情况下肯定不知道.就它而言,它只是一个独特的字符串.但在大多数情况下,这应该没问题,例如匹配行等.如果您发现自己需要一些特别需要GUID的行为(例如,一些基于非等式的比较,其中GUID比较可能不同于纯粹的词法比较),那么您可以始终将字符串强制转换为UUID,并且Postgres将在该查询期间视为该值.
e、 g.对于文本列foo
,可以执行foo::uuid
将其转换为uuid
.
还有一个模块可以生成uuid
uuid-ossp.