INT和VARCHAR主键之间在真实性能上有区别吗? [英] Is there a REAL performance difference between INT and VARCHAR primary keys?
问题描述
在MySQL中使用INT与VARCHAR作为主键之间是否存在可测量的性能差异?我想将VARCHAR用作参考列表的主键(想想美国的州代码，国家代码)，并且同事不会花INT AUTO_INCREMENT作为所有表的主键.
Is there a measurable performance difference between using INT vs. VARCHAR as a primary key in MySQL? I'd like to use VARCHAR as the primary key for reference lists (think US States, Country Codes) and a coworker won't budge on the INT AUTO_INCREMENT as a primary key for all tables.
我的论据，详细的此处是INT和VARCHAR之间的性能差异可以忽略不计，因为每个INT外键引用都需要一个JOIN才能使引用有意义，所以VARCHAR键将直接呈现该信息.
My argument, as detailed here, is that the performance difference between INT and VARCHAR is negligible, since every INT foreign key reference will require a JOIN to make sense of the reference, a VARCHAR key will directly present the information.
那么，有没有人对此特殊用例以及与之相关的性能问题有经验?
So, does anyone have experience with this particular use-case and the performance concerns associated with it?
推荐答案
You make a good point that you can avoid some number of joined queries by using what's called a natural key instead of a surrogate key. Only you can assess if the benefit of this is significant in your application.
也就是说，您可以测量应用程序中最重要的查询，因为它们要处理大量数据或执行得非常频繁，因此查询必须快速进行.如果这些查询从消除联接中受益，并且不因使用varchar主键而受苦，则可以这样做.
That is, you can measure the queries in your application that are the most important to be speedy, because they work with large volumes of data or they are executed very frequently. If these queries benefit from eliminating a join, and do not suffer by using a varchar primary key, then do it.
不要对数据库中的所有表使用任何一种策略.在某些情况下，自然键可能会更好，但在其他情况下，替代键会更好.
Don't use either strategy for all tables in your database. It's likely that in some cases, a natural key is better, but in other cases a surrogate key is better.
其他人指出，在实践中，很少有自然密钥永远不会更改或具有重复项，因此代理密钥通常是值得的.
Other folks make a good point that it's rare in practice for a natural key to never change or have duplicates, so surrogate keys are usually worthwhile.
这篇关于INT和VARCHAR主键之间在真实性能上有区别吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
