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


当前回答

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

其他回答

Inserts to a table having a clustered index where the insertion occurs in the middle of the sequence DOES NOT cause the index to be rewritten. It does not cause the pages comprising the data to be rewritten. If there is room on the page where the row will go, then it is placed in that page. The single page will be reformatted to place the row in the right place in the page. When the page is full, a page split will happen, with half of the rows on the page going to one page, and half going on the other. The pages are then relinked into the linked list of pages that comprise a tables data that has the clustered index. At most, you will end up writing 2 pages of database.

是的,但除非您希望有数百万行,否则不使用基于字符串的键(因为它较慢)通常是“过早优化”。毕竟,字符串存储为大数字,而数字键通常存储为较小的数字。

不过,要注意的一件事是,如果您在任意键上聚集了索引,并且在索引中进行了大量的非顺序插入。写入的每一行都将导致索引重新写入。如果您正在进行批量插入,这确实会降低过程的速度。

Technically yes, but if a string makes sense to be the primary key then you should probably use it. This all depends on the size of the table you're making it for and the length of the string that is going to be the primary key (longer strings == harder to compare). I wouldn't necessarily use a string for a table that has millions of rows, but the amount of performance slowdown you'll get by using a string on smaller tables will be minuscule to the headaches that you can have by having an integer that doesn't mean anything in relation to the data.

使用什么作为主键并不重要,只要它是UNIQUE即可。如果您关心速度或良好的数据库设计,请使用int型,除非您计划复制数据,否则请使用GUID。

如果这是一个访问数据库或一些小应用程序,那么谁真的在乎。我认为,我们大多数开发人员之所以把旧的int或guid放在前面,是因为我们有一种方式来发展项目,并且您希望给自己留下发展的选择。

指数意味着大量的比较。

通常,字符串比整数长,并且可以应用排序规则进行比较,因此比较字符串通常比比较整数需要更多的计算量。

不过,有时使用字符串作为主键要比使用字符串与数字id表进行额外的连接更快。