SQL中字符串比较与int连接的性能 [英] Performance of string comparison vs int join in SQL

问题描述

可以接受的是，在int列上搜索表比在字符串列（比如varchar）上快。然而，如果我有一个Color列的衬衫表，那么创建一个Color表的主键是该表的外键衬衫表？这个连接是否会消除衬衫上的Color列中的值是一个int而不是像Green那样的字符串值的性能优势？

解决方案

与其他正在执行的操作相比，这两种方法之间的性能差别不大。如果您只有少数几种颜色（最多可达几百种），则颜色表适合大多数数据库中的单个页面。在颜色上的一个索引会使查找速度非常快，不会产生任何I / O活动（在第一次运行加载页面之后）。

字符串比较取决于数据库，但它确实涉及一个函数并从页面读取数据。所以，这不是免费的。当然，不同的数据库对于一个字符串函数可能会有不同的性能特征。

应该存储在哪里应该是应用程序的函数。假设你有一个应用程序，其颜色将呈现给用户。有一天，您可能想用西班牙文，斯瓦希里文或中文显示颜色的名称。如果是这样，有一个单独的表使这种国际化更容易。更简单的是，你可能想要阻止Grene被输入，如果是这样，有这样一个表使得选择列表更容易。另一方面，如果表现是你唯一的关心，它不会有所不同。在其他情况下，查找表实际上可能比非规格化表更快。当字符串很长时会发生这种情况，从而增加了较大表中每个记录的长度。较大的表格意味着更多页面，这会花费更长的时间加载到内存中。

It's accepted that searching a table on an int column is faster than on a string column (say varchar).

However, if I have a Shirt table with a Color column, would it be more performant to create a Color table with the primary key on that table being the foreign key on the Shirt table? Would the join negate the performance advantage of having the value in the Color column on Shirt being an int instead of a string value such as "Green" when searching for green Shirts?

解决方案

Compared to the other operations being performed, it is unlikely that there is much performance difference between the two approaches. If you have only a handful of colors (up to a few hundred), the color table fits on a single page in most databases. An index on the color would make look up quite fast and not incur any I/O activity (after the first run to load the page).

A string comparison depends on the database, but it does involve a function and reading the data from the page. So, it is not free. Different databases, of course, might have different performance characteristics for a string function.

Where it should be stored should be a function of your application. Say you have an application where the color is going to be presented to the user. You might, one day, want to show the name of the color in Spanish, Swahili, or Chinese. If so, having a separate table makes such internationalization much easier. More prosaicly, you might want to prevent "Grene" from being entered, if so, having such a table makes a selection list easier.

On the other hand, if performance is your only concern, it doesn't make a different. In other cases, it is actually possible for a lookup table to be faster than a denormalized table. This occurs when the strings are long, increasing the length of every record in a larger table. Larger tables mean more pages, which take longer to load into memory.

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