我正在研究一种将hex
固定长度 64 格式的字符串存储到数据库的方案。显然选择是BYTEA
和CHAR(64)
。
最初的想法是强制使用有效的十六进制字符串存储它,BYTEA
这是个好主意,但我评估的影响是ENCODE
对选择查询的使用。
考虑到两个表都有几百万行,我做了一些性能基准测试;
# A file query_with_char.sql
SELECT "key" FROM table_varchar;;
# A file query_with_binary.sql
SELECT ENCODE("key", 'hex') FROM table_binary;
pgbench -c 30 -T 120 -n -f ./query_with_binary.sql -f ./query_with_char.sql -P 5 -S my_db
SQL script 1: ./query_with_binary.sql
- weight: 1 (targets 33.3% of total)
- 236 transactions (34.6% of total, tps = 1.876072)
- latency average = 8896.888 ms
- latency stddev = 2548.701 ms
SQL script 2: ./query_with_varchar.sql
- weight: 1 (targets 33.3% of total)
- 225 transactions (33.0% of total, tps = 1.788628)
- latency average = 7164.604 ms
- latency stddev = 2209.866 ms
我无法理解为什么查询的性能ENCODE
比普通字符串更快。PostgreSQL 如何能够比仅获取字符串列更快地对百万行进行编码?
有人可以解释上述测试中可能有什么问题吗?
encode()
是一个非常便宜的功能。我不希望在您的测试中有任何可衡量的影响。差异几乎可以肯定是由于
bytea
与char(64)
. 考虑:SELECT
简单查询性能的主导因素是已读取的数据页数。“最佳”数据类型?
如果您的目标是优化性能,请考虑第三种选择:
2
uuid
列要理解,请先阅读:
和:
然后考虑这个演示(在第 11 页执行,但适用于所有现代版本):
内存大小:
磁盘大小(压缩格式):
db<>在这里摆弄
33 位和 32 位之间看似微小的差异实际上可以产生 8 字节的差异,因为一些存储机制需要以 8 字节的倍数进行填充。
使用 2 个 UUID 重复您的测试。我相信它会名列前茅。