字符串作为主键的性能损失? [英] performance penalty of strings as primary keys?

问题描述

使用字符串作为主键而不是bigints等会导致性能下降吗?字符串比较比整数比较昂贵得多，但是另一方面，我可以想象DBMS在内部将计算哈希键以减少代价.

What would be the performance penalty of using strings as primary keys instead of bigints etc.? String comparison is much more expensive than integer comparison, but on the other hand I can imagine that internally a DBMS will compute hash keys to reduce the penalty.

我正在处理的应用程序使用字符串作为几张表中的主键(MySQL).改变这一点并不容易，我想知道可以从性能上获得什么来证明这项工作是合理的.

An application that I work on uses strings as primary keys in several tables (MySQL). It is not trivial to change this, and I'd like to know what can be gained performance wise to justify the work.

推荐答案

另一方面，我可以想象 在内部，DBMS将计算哈希 减少惩罚的关键.

on the other hand I can imagine that internally a DBMS will compute hash keys to reduce the penalty.

数据库需要使用密钥维护 B树(或类似的结构)，以便对其进行排序.

The DB needs to maintain a B-Tree (or a similar structure) with the key in a way to have them ordered.

如果密钥被散列并存储在B树中，则可以快速检查密钥的唯一性 －仍然可以有效地查找密钥.但是您将无法有效搜索数据的范围(例如，使用LIKE)，因为B-Tree不再根据String值进行排序.

If the key is hashed and stored it in the B-Tree that would be fine to check rapidly the uniqueness of the key -- the key can still be looked up efficiently. But you would not be able to search efficient for range of data (e.g. with LIKE) because the B-Tree is no more ordered according to the String value.

因此，我认为大多数DB确实将字符串存储在B-Tree中，这可能(1)占用比数字值更多的空间，并且(2)要求B-Tree成为重新平衡 (如果按任意顺序插入键(没有像数字pk那样增加值的概念).

So I think most DB really store the String in the B-Tree, which can (1) take more space than numeric values and (2) require the B-Tree to be re-balanced if keys are inserted in arbitrary order (no notion of increasing value as with numeric pk).

在实践中，惩罚的范围从微不足道到巨大.这完全取决于用法，行数，字符串键的平均大小，连接表的查询等.

The penalty in practice can range from insignificant to huge. It all depends on the usage, the number of rows, the average size of the string key, the queries which join table, etc.

这篇关于字符串作为主键的性能损失?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！