我不是很熟悉数据库及其工作原理。从性能的角度(插入/更新/查询),使用字符串作主键是否比整数慢?
当前回答
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.
其他回答
指数意味着大量的比较。
通常,字符串比整数长,并且可以应用排序规则进行比较,因此比较字符串通常比比较整数需要更多的计算量。
不过,有时使用字符串作为主键要比使用字符串与数字id表进行额外的连接更快。
你为什么要用字符串作为主键?
我只需将主键设置为一个自动递增的整数字段,并在字符串字段上放置一个索引。
这样,如果您在表上进行搜索,它们应该相对较快,并且所有的连接和正常查找都不会受到速度的影响。
您还可以控制被索引的字符串字段的数量。换句话说,如果您认为这样就足够了,您可以说“只索引前5个字符”。或者如果您的数据可以相对相似,您可以索引整个字段。
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.
是的,但除非您希望有数百万行,否则不使用基于字符串的键(因为它较慢)通常是“过早优化”。毕竟,字符串存储为大数字,而数字键通常存储为较小的数字。
不过,要注意的一件事是,如果您在任意键上聚集了索引,并且在索引中进行了大量的非顺序插入。写入的每一行都将导致索引重新写入。如果您正在进行批量插入,这确实会降低过程的速度。
推荐文章
- 如何在Ruby On Rails中使用NuoDB手动执行SQL命令
- 查询JSON类型内的数组元素
- 确定记录是否存在的最快方法
- Printf与std::字符串?
- 获得PostgreSQL数据库中当前连接数的正确查询
- 不区分大小写的“in”
- 在SQL选择语句Order By 1的目的是什么?
- MySQL数据库表中的最大记录数
- 我如何得到一个字符串的前n个字符而不检查大小或出界?
- 从现有模式生成表关系图(SQL Server)
- 如何在PHP中截断字符串最接近于一定数量的字符?
- 我如何循环通过一组记录在SQL Server?
- HyperLogLog算法是如何工作的?
- 数据库和模式的区别
- Ruby数组到字符串的转换