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

问题描述

公认在 int 列上搜索表比在 string 列(例如 varchar)上搜索要快.

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

但是，如果我有一个带有 Color 列的 Shirt 表，创建一个 Color 表并且该表上的主键是 Shirt 表上的外键会更高效吗?在搜索绿色衬衫时，连接是否会抵消将衬衫上颜色列中的值设为整数而不是字符串值(例如绿色")的性能优势?

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?

推荐答案

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

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.

它应该存储在哪里应该是您的应用程序的功能.假设您有一个应用程序，其中的颜色将呈现给用户.有一天，您可能想要用西班牙语、斯瓦希里语或中文显示颜色的名称.如果是这样，拥有一个单独的表会使这种国际化更容易.更通俗地说，您可能希望阻止输入Grene"，如果是这样，拥有这样的表格会使选择列表更容易.

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屋！