主键的 int 和 varchar 之间的性能差异是什么 [英] what's the performance difference between int and varchar for primary keys

问题描述

我需要为需要对等复制的系统创建主键方案.所以我打算以某种方式组合一个唯一的系统 ID 和一个序列号来得到唯一的 ID.我想确保我永远不会用完 ID，所以我正在考虑使用 varchar 字段，因为如果我开始用完，我总是可以添加另一个字符.但我读过整数对此进行了更好的优化.所以我有一些问题...

I need to create a primary key scheme for a system that will need peer to peer replication. So I'm planning to combine a unique system ID and a sequential number in some way to come up with unique ID's. I want to make sure I'll never run out of ID's, so I'm thinking about using a varchar field, since I could always add another character if I start running out. But I've read that integers are better optimized for this. So I have some questions...

1) 整数真的被更好地优化了吗?如果是，varchars 和整数之间的性能差异有多大?我现在要使用火鸟.但我以后可能会换.或者可能支持多个数据库.所以如果可能的话，我正在寻找概括.

1) Are integers really better optimized? And if they are, how much of a performance difference is there between varchars and integers? I'm going to use firebird for now. But I may switch later. Or possibly support multiple db's. So I'm looking for generalizations, if that's possible.

2) 如果整数得到了明显更好的优化，为什么会这样?并且 varchars 有可能在未来迎头赶上，所以最终无论如何都无关紧要吗?

2) If integers are significantly better optimized, why is that? And is it likely that varchars will catch up in the future, so eventually it won't matter anyway?

我的 varchar 键没有任何意义，除了唯一的系统 ID 部分.但我可能想以某种方式掩盖它.此外，我计划有效地使用每个字符的所有位.例如，我不打算将整数 123 编码为字符串123".所以我认为 varchars 不需要比整数更多的空间.

My varchar keys won't have any meaning, except for the unique system ID part. But I may want to obscure that somehow. Also, I plan to efficiently use all the bits of each character. I don't, for example, plan to code the integer 123 as the character string "123". So I don't think varchars will require more space than integers.

推荐答案

对于 MySQL，根据 Alexey 这里，答案令人惊讶的是不多".他总结道:

For MySQL, according to Alexey here, the answer is surprisingly "not much". He concludes:

因此，如果您有一个应用程序并且您需要一些包含一小组可能值的表字段，我仍然建议您使用 ENUM，但现在我们可以看到性能损失可能没有那么大你期待.虽然同样很大程度上取决于您的数据和查询.

So, if you have an application and you need to have some table field with a small set of possible values, I'd still suggest you to use ENUM, but now we can see that performance hit may not be as large as you expect. Though again a lot depends on your data and queries.

这篇关于主键的 int 和 varchar 之间的性能差异是什么的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！