SQL中字符串比较与int联接的性能

如果我理解正确,您会问这两个查询中哪一个更快:

SELECT * FROM shirt where color = 'Green'

对

SELECT shirt.* FROM shirt s INNER JOIN colors c 
       ON s.colorid = c.colorid 
       WHERE c.color = 'Green'

它在很大程度上取决于数据库(好吧……可能很大程度上依赖于它是否正确优化,如果不是全部的话,大多数都应该优化),但颜色表中的查找应该可以忽略不计,然后剩余的执行可以使用整数查找值,并且应该更快。大部分处理最终将相当于 SELECT * from shirt WHERE colorid=N 然而,我怀疑除非桌子很大,否则你不会注意到速度的差异。决策可能应该基于哪种设计最有意义(可能是标准化的设计)。

除了性能之外,创建一个单独的“颜色”表可以使您的设计更好地规范化。因此,在未来的某一天,当有人决定“深蓝”现在应该被称为“海军蓝”时,你会更新颜色表中的一行,而不是更新衬衫表中的许多行。

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

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

它应该存储在哪里应该是应用程序的一个功能。假设您有一个应用程序,其中的颜色将呈现给用户。有一天,你可能想用西班牙语、斯瓦希里语或中文显示颜色的名称。如果是这样的话,拥有一个单独的表会使这种国际化更加容易。更重要的是,你可能想防止输入“Grene”,如果是这样的话,有这样一个表会让选择列表更容易。

另一方面,如果性能是你唯一关心的问题,那也没什么不同。在其他情况下,查找表实际上可能 更快 而不是非规范化的表。当字符串很长时,就会发生这种情况,从而增加较大表中每个记录的长度。较大的表意味着更多的页面,加载到内存中需要更长的时间。

DBMS有机会在值数量有限的情况下优化标记。不过,我不知道如何告诉sQL这样做。它可能会明白的。

如果报告性能是一个严重问题,则启动数据仓库。。

正如Joe所指出的,您希望数据库尽可能规范化。如果您有一个单独的报告功能,这可能会导致性能问题,那么您应该对第二个只读模式运行周期性转换(或设置规则以实时构建)。第一种是OLTP,第二种是OLAP(“数据仓库”);如果你想认真对待你的数据,这些都是重要的概念。

如果知道这件事很重要,那就测试一下。

如果没有人给你答案,最好的方法就是自己测试。

(1) 制作2个数据库

(2) 每个人都有你的两张桌子的测试

(3) 在数据库中,只连接字符串“color”,并将其用于FK;其他按int('colorID')联接

用200万个伪行填充每个行。每次运行多个查询,计时第一次运行和平均运行。

在你的开发机器上使用一个实例来消除网络的影响。

您还应该在每种类型的测试之前启动和停止实例;这些东西会故意留在内存中,这样SQL就可以更快地交付它,但很可能,这会使您的测试结果偏离实际操作——在实际操作中,这些东西可能不再存在于内存或缓存中。

这实际上取决于查询优化器。您的颜色表将非常小,因此可能基于数据库统计信息和查询计划,它可能会完全加载到内存中,因此您不仅最终否定了联接的性能成本,而且实际上可能会更快。这显然取决于您正在使用的dbms,但一些dbms可以提示以特殊方式处理表。

“颜色”表的另一个+1是,如果需要更改颜色名称,则只需要1次更新,而不是更改每次出现的字符串值。