代码之家 › 专栏 › 技术社区 › Mark Brittingham

在SQL Server中,按短子字符串排序是否比按整个长字段排序更有效?

sql-server

Mark Brittingham · 技术社区 · 16 年前

请考虑以下SQL Server 2005/2008查询:

Select [UID], [DESC] From SomeTable Order By [Desc];

如果DESC是一个相当长的字段(varchar(125),包含许多条目>70个字符),并且您不需要严格排序,那么这样做是否更有效:

Select [UID], [DESC] From SomeTable Order By Substring([Desc], 0, 20);

优点是所有比较都很短(最多20个字符)。缺点是它会引起子字符串调用。出于目前的目的,假设您不想在此字段上放置索引,因为这不是主键,上面的操作非常罕见。你会选择哪个选项?

注2:我问这个问题主要是出于好奇。在我的应用程序中,描述是一个索引字段,我不使用子字符串。不过,我曾考虑过使用子字符串,但我突然想到,我并不真正知道上述哪种方法更有效。

最后,还有一个额外的问题:在索引字段上使用子字符串会使优化器跳过索引吗? 真的? 放慢速度?如果使用子字符串(即使是零基),我认为优化器不够聪明,无法使用索引,但我现在有点忙,无法测试它。但是,如果你知道不同,请纠正我!

更新/澄清: 您应该假设desc字段是不为原始问题编制索引。如果它是索引的,那么答案很容易。

5 回复 | 直到 16 年前

Quassnoi 16 年前

使用A non-clustered index 暗示一种暗示 JOIN .

索引本身不包含非索引值,它只包含对 TABLE 街区。

要获取非索引值,需要扫描索引并在嵌套循环中从这些块中读取。

根据经验, INDEX SCAN WITH TABLE LOOKUP 是关于 10 比 TABLE SCAN .

如果您需要一个有序查询的所有结果,特别是作为一个部分或更复杂的查询,这意味着 nested loops ,有时执行 表扫描 并对结果进行排序。

表只需要排序一次,排序结果将被保留并重用。在这种情况下, SUBSTRING 可能更有效。

如果你需要 5% 或更少的有序结果,然后 INDEX SCAN 会更有效率,在这种情况下,您需要对整个列进行排序。

而且,索引查找总是响应性更强,因为您可以更快地得到第一行。

bdukes Jon Skeet 16 年前

你的最后一部分是完全正确的。

至于排序问题,在前20个字符的子字符串上排序是否更快。如果字符串为30个字符,则答案为“否”,如果为300个字符,则可能为“是”。我不知道边界在哪里。但它将逐个进行字符排序。如果是21个字符,就可以更快地避免子字符串的额外开销,并让它检查额外的1个字符。

您可以做的是有一个更进一步的列,它是一个截断的描述,并在此列上进行排序。

Kibbee 16 年前

你可能需要考虑的是这一点。在对字符串进行排序时,假设使用了良好的优化算法,就不必分析整个字符串来找出哪个字符串是第一个。考虑这两个字符串

F3294r02343232423
B3920490234324234

在知道第二个字符串应该排在第一位之前,您只需要分析每个字符串的第一个字符。我不确定这对您的特定数据集起了多大作用,但这是您应该考虑的事情。

作为测试,您可能希望使用完全相同的数据和索引创建表的副本,但将要排序的字段截断为20个字符,并查看由于数据量较小,速度是否有明显的提高。如果性能显著提高,您可能希望按照Robert编写的内容进行操作,并创建第二列,其中的数据已被截断,因此不必使用substring函数。

Otávio Décio 16 年前

我不这么认为。在这种情况下,调用函数将对性能造成最大的损害。是的,函数很可能使优化器避免索引。

Kristen 16 年前

您说过忽略[desc]被索引的事实,但是假设[uid]是pk,并且使用聚集索引,您的查询被[desc]上的索引“覆盖”,因此SQL将按索引顺序读取记录…因此,放置子字符串将导致它必须额外执行一步,按前20个字符排序,而它们已经按排序顺序读取了。

在索引字段上使用子字符串是否会使优化器跳过索引并真正降低速度?

通常是的,如果字段在WHERE子句中。应用于WHERE子句中的字段的任何函数都可能导致优化程序跳过索引。一般来说。