SQL-varchar与int的性能 [英] SQL - performance in varchar vs. int

问题描述

我有一个表，该表的数据类型为primary key.还有另一个表，其数据类型为foreign key作为varchar.

I have a table which has a primary key with varchar data type. And another table with foreign key as varchar datatype.

我正在使用这对varchar数据类型进行join语句.尽管我要处理的行数很少(例如，奇怪的行)，但它占用的是60ms.但是，当最终部署该系统时，它将有大约数千行.

I am making a join statement using this pair of varchar datatype. Though I am dealing with few number of rows (say hunderd rows), it is taking 60ms. But when the system will finally be deployed, it will have around thousands of rows.

我阅读了 Performance of string comparison vs int join in SQL ，得出结论，SQL查询的性能取决于DB及其处理的行数.

I read Performance of string comparison vs int join in SQL, and concluded that the performance of SQL Query depend upon DB and number of rows it is dealing with.

但是当我处理大量数据时，这会很重要吗?

But when I am dealing with a very large amount of data, would it matter much?

是否应该在table和join table中都创建一个具有number数据类型的新列，以减少SQL查询所花费的时间??

Should I create a new column with a number datatype in both the table and join the table to reduce the time taken by the SQL Query.?

推荐答案

是否应该在两个表中都创建一个具有数字数据类型的新列，然后联接该表以减少SQL查询所花费的时间??

Should I create a new column with a number datatype in both the table and join the table to reduce the time taken by the SQL Query.?

如果您可以轻松更改数据库的设计，那么可以，您的主键应该是整数.除非有充分的理由将FK用作varchar，否则它们也应该是整数.

If you're in a position where you can change the design of the database with ease then yes, your Primary Key should be an integer. Unless there is a really good reason to have an FK as a varchar, then they should be integers as well.

如果无法更改PK或FK字段，请确保已正确索引它们.但这最终将成为瓶颈.

If you can't change the PK or FK fields, then make sure they're indexed properly. This will eventually become a bottleneck though.

这篇关于SQL-varchar与int的性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！