我不是很熟悉数据库及其工作原理。从性能的角度(插入/更新/查询),使用字符串作主键是否比整数慢?


当前回答

你为什么要用字符串作为主键?

我只需将主键设置为一个自动递增的整数字段,并在字符串字段上放置一个索引。

这样,如果您在表上进行搜索,它们应该相对较快,并且所有的连接和正常查找都不会受到速度的影响。

您还可以控制被索引的字符串字段的数量。换句话说,如果您认为这样就足够了,您可以说“只索引前5个字符”。或者如果您的数据可以相对相似,您可以索引整个字段。

其他回答

使用string作为主键的另一个问题是,由于索引不断按顺序排列,当创建一个新键时,索引必须重新排序……如果使用自动编号整数,则新键只添加到索引的末尾。

在PK列中使用整数有两个原因:

我们可以为自动递增的整数字段设置标识。 当我们创建pk时,db会创建一个索引(Cluster或Non Cluster),在数据存储到表之前对其进行排序。通过在PK上使用标识,优化器在保存记录之前不需要检查排序顺序。这提高了大表的性能。

变量太多了。这取决于表的大小,索引,字符串键域的性质…

一般来说,整数会更快。但差别大到足以让人在意吗?这很难说。

另外,你选择字符串的动机是什么?数字型的自动递增键通常也容易得多。是语义上的吗?方便?复制/断开连接问题?你的回答可能会限制你的选择。这也会让你想起你忘记的第三个“混合”选项:Guids。

从性能的角度来看-与使用整数(PK)实现的性能相比,Yes字符串(PK)将降低性能,其中PK—>主键。

From requirement standpoint - Although this is not a part of your question still I would like to mention. When we are handling huge data across different tables we generally look for the probable set of keys that can be set for a particular table. This is primarily because there are many tables and mostly each or some table would be related to the other through some relation ( a concept of Foreign Key ). Therefore we really cannot always choose an integer as a Primary Key, rather we go for a combination of 3, 4 or 5 attributes as the primary key for that tables. And those keys can be used as a foreign key when we would relate the records with some other table. This makes it useful to relate the records across different tables when required.

因此,为了优化使用-我们总是将1或2个具有1或2个字符串属性的整数组合在一起,但同样只是在需要时才这样做。

Strings are slower in joins and in real life they are very rarely really unique (even when they are supposed to be). The only advantage is that they can reduce the number of joins if you are joining to the primary table only to get the name. However, strings are also often subject to change thus creating the problem of having to fix all related records when the company name changes or the person gets married. This can be a huge performance hit and if all tables that should be related somehow are not related (this happens more often than you think), then you might have data mismatches as well. An integer that will never change through the life of the record is a far safer choice from a data integrity standpoint as well as from a performance standpoint. Natural keys are usually not so good for maintenance of the data.

我还想指出,两者的最佳方法通常是使用自递增键(或者在某些特殊情况下,使用GUID)作为PK,然后在自然键上放置唯一索引。您可以获得更快的连接,不会得到重复的记录,也不必因为公司名称更改而更新一百万个子记录。