SQL Server 查找字符串LIKE vs SUBSTRING vs LEFT/RIGHT vs CHARINDEX_sqlserver like和substring那种比较方式快

link管理

链接快照平台

输入网页链接，自动生成快照
标签化管理网页链接

您是博彩个人吗？

这是我们的LIKE vs SUBSTRING vs LEFT / RIGHT vs CHARINDEX 速度测试的结果，以毫秒为单位。

获胜者以绿色突出显示。在这个速度测试中没有第二名的分数。

功能	对聚集索引列执行的时间，以毫秒为单位，超过 3 次运行：
＃记录：	50,000	500,000	5,000,000	50,000,000
1：喜欢	46, 43, 40	406, 413, 403	4016, 3986, 3996	39940, 39756, 40423
2：子串	46, 46, 46	440, 443, 443	2513, 2513, 2603	24760, 24873, 24270
3：左 / 右	40, 43, 43	406, 406, 410	2523, 2526, 2516	24713, 24770, 24823
4： CHARINDEX	10, 10, 10	56, 56, 56	590, 593, 576	5713, 5683, 5730

功能	对非聚集索引列执行的时间，以毫秒为单位，超过 3 次运行：
＃记录：	50,000	500,000	5,000,000	50,000,000
1：喜欢	43, 40, 40	70, 63, 73	680, 666, 670	7203, 6756, 6716
2：子串	103, 100, 103	256, 256, 260	2750, 2750, 2763	27076, 26940, 27053
3：左 / 右	100, 86, 100	253, 246, 250	2730, 2733, 2730	27166, 26633, 27123
4： CHARINDEX	10, 10, 10	56, 53, 60	590, 590, 586	5810, 5763, 5690

功能	对非索引列执行的时间，以毫秒为单位，超过 3 次运行：
＃记录：	50,000	500,000	5,000,000	50,000,000
1：喜欢	43, 43, 40	63, 66, 70	670, 676, 663	6790, 7153, 6726
2：子串	126, 123, 123	396, 396, 396	3016, 2810, 2933	25463, 25643, 25420
3：左 / 右	46, 46, 43	246, 250, 253	2800, 2496, 2546	24690, 24750, 24810
4： CHARINDEX	10, 6, 6	56, 56, 60	576, 590, 573	5790, 5836, 6276

功能	对 varchar 字符串执行的时间，以毫秒为单位，超过 3 次运行：
＃记录：	50,000	500,000	5,000,000	50,000,000
1：喜欢	126, 87, 75	909, 882, 822	8358, 8607, 8667	88610, 87349, 85341
2：子串	60, 45, 69	585, 582, 576	5571, 5673, 5670	57849, 54552, 56344
3：左 / 右	45, 45, 24	474, 396, 351	3945, 4044, 3990	39969, 39135, 40919
4： CHARINDEX	60, 36, 66	618, 636, 564	5766, 5937, 5904	59773, 58412, 60198

看看谁拥有它！

在查询表列以查找值时，CHARINDEX 显然是无可争议的王者。其他 3 个中的 2 个甚至没有接近我承认他们是“竞争对手”的速度。我预计 LIKE 会做得更好，尤其是在索引列上，但对 CHARINDEX 的统治感到非常惊讶。

在搜索 varchar/string 变量时，LEFT/RIGHT 位居榜首。

简而言之，当您需要在数据的开头或结尾搜索子字符串时：

对表列执行查询时，使用 CHARINDEX
在 @varchar 字符串变量中搜索时，使用 LEFT/RIGHT

我在下面为您留下了 SQL 代码，因此请随意将其用作执行您自己的性能基准测试的基础。

如果您有任何建议或其他方式，请在下面发表评论并分享知识！

SQL 源代码

print CAST ( GETDATE ( ) as varchar ) + ' Started while loop creating temptable data.' SET NOCOUNT ON , CAST ( DATEDIFF ( MILLISECOND , @ START_TIME , @ END_TIME ) as varchar ) + ' milliseconds' as [ Time to Run ] FROM # table2 WHERE SUBSTRING ( aClusteredIndexedColumn , 1 , LEN ( @ WhatToLookFor ) ) = @ WhatToLookFor SUBSTRING ( aClusteredIndexedColumn , LEN ( aClusteredIndexedColumn ) - LEN ( @ WhatToLookFor ) + 1 , LEN ( @ WhatToLookFor ) ) = @ WhatToLookFor SET @ END_TIME = GETDATE ( ) print 'Finished' , CAST ( DATEDIFF ( MILLISECOND , @ START_TIME , @ END_TIME ) as varchar ) + ' milliseconds' as [ Time to Run ] FROM # table2 Truncate Table # table2 , CAST ( DATEDIFF ( MILLISECOND , @ START_TIME , @ END_TIME ) as varchar ) + ' milliseconds' as [ Time to Run ] FROM # table2 Truncate Table # table2 WHERE CHARINDEX ( @ WhatToLookFor , SUBSTRING ( aClusteredIndexedColumn , 1 , LEN ( @ WhatToLookFor ) ) , 0 ) > 0 CHARINDEX ( @ WhatToLookFor , SUBSTRING ( aClusteredIndexedColumn , LEN ( aClusteredIndexedColumn ) - LEN ( @ WhatToLookFor ) + 1 , LEN ( @ WhatToLookFor ) ) , 0 ) > 0 , CAST ( DATEDIFF ( MILLISECOND , @ START_TIME , @ END_TIME ) as varchar ) + ' milliseconds' as [ Time to Run ] FROM # table2 Truncate Table # table2 WHERE SUBSTRING ( aNonClusteredIndexedColumn , 1 , LEN ( @ WhatToLookFor ) ) = @ WhatToLookFor SUBSTRING ( aNonClusteredIndexedColumn , LEN ( aNonClusteredIndexedColumn ) - LEN ( @ WhatToLookFor ) + 1 , LEN ( @ WhatToLookFor ) ) = @ WhatToLookFor , CAST ( DATEDIFF ( MILLISECOND , @ START_TIME , @ END_TIME ) as varchar ) + ' milliseconds' as [ Time to Run ] FROM # table2 Truncate table # table2 WHERE LEFT ( aNonClusteredIndexedColumn , LEN ( @ WhatToLookFor ) ) = @ WhatToLookFor RIGHT ( aNonClusteredIndexedColumn , LEN ( @ WhatToLookFor ) ) = @ WhatToLookFor , CAST ( DATEDIFF ( MILLISECOND , @ START_TIME , @ END_TIME ) as varchar ) + ' milliseconds' as [ Time to Run ] FROM # table2 Truncate table # table2 WHERE CHARINDEX ( @ WhatToLookFor , SUBSTRING ( aNonClusteredIndexedColumn , 1 , LEN ( @ WhatToLookFor ) ) , 0 ) > 0 CHARINDEX ( @ WhatToLookFor , SUBSTRING ( aNonClusteredIndexedColumn , LEN ( aNonClusteredIndexedColumn ) - LEN ( @ WhatToLookFor ) + 1 , LEN ( @ WhatToLookFor ) ) , 0 ) > 0 , CAST ( DATEDIFF ( MILLISECOND , @ START_TIME , @ END_TIME ) as varchar ) + ' milliseconds' as [ Time to Run ] FROM # table2 Truncate table # table2 , CAST ( DATEDIFF ( MILLISECOND , @ START_TIME , @ END_TIME ) as varchar ) + ' milliseconds' as [ Time to Run ] FROM # table2 Truncate table # table2 WHERE SUBSTRING ( aNonIndexedColumn , 1 , LEN ( @ WhatToLookFor ) ) = @ WhatToLookFor SUBSTRING ( aNonIndexedColumn , LEN ( aNonIndexedColumn ) - LEN ( @ WhatToLookFor ) + 1 , LEN ( @ WhatToLookFor ) ) = @ WhatToLookFor , CAST ( DATEDIFF ( MILLISECOND , @ START_TIME , @ END_TIME ) as varchar ) + ' milliseconds' as [ Time to Run ] FROM # table2 Truncate table # table2 , CAST ( DATEDIFF ( MILLISECOND , @ START_TIME , @ END_TIME ) as varchar ) + ' milliseconds' as [ Time to Run ] FROM # table2 Truncate table # table2 CHARINDEX ( @ WhatToLookFor , SUBSTRING ( aNonIndexedColumn , 1 , LEN ( @ WhatToLookFor ) ) , 0 ) > 0 CHARINDEX ( @ WhatToLookFor , SUBSTRING ( aNonIndexedColumn , LEN ( aNonIndexedColumn ) - LEN ( @ WhatToLookFor ) + 1 , LEN ( @ WhatToLookFor ) ) , 0 ) > 0 , CAST ( DATEDIFF ( MILLISECOND , @ START_TIME , @ END_TIME ) as varchar ) + ' milliseconds' as [ Time to Run ] FROM # table2 Truncate table # table2 SUBSTRING ( @ tempString , LEN ( @ tempString ) - LEN ( @ WhatToLookFor ) + 1 , LEN ( @ WhatToLookFor ) ) = @ WhatToLookFor ) BEGIN SET @ END_TIME = GETDATE ( ) SET @ SubstringMatches = @ SubstringMatches + 1 BEGIN SET @ END_TIME = GETDATE ( ) SET @ SubstringTimeDiff = @ SubstringTimeDiff + DATEDIFF ( MILLISECOND , @ START_TIME , @ END_TIME ) if ( CHARINDEX ( @ WhatToLookFor , SUBSTRING ( @ tempString , 1 , LEN ( @ WhatToLookFor ) ) , 0 ) > 0 CHARINDEX ( @ WhatToLookFor , SUBSTRING ( @ tempString , LEN ( @ tempString ) - LEN ( @ WhatToLookFor ) + 1 , LEN ( @ WhatToLookFor ) ) , 0 ) > 0 ) BEGIN SET @ END_TIME = GETDATE ( ) SET @ CharindexMatches = @ CharindexMatches + 1 BEGIN SET @ END_TIME = GETDATE ( ) SET @ CharindexTimeDiff = @ CharindexTimeDiff + DATEDIFF ( MILLISECOND , @ START_TIME , @ END_TIME ) 首先学习两个函数1. substring 返回字符、binary、text 或 image 表达式的一部分。基本语法: SUBSTRING ( expression , start , length ) expression: 字符串 、二进制 字符串 、text、image、列或包含列的表达式start:整数，指定子串的开始位置注: SQL 中”1″表示 字符串 中的第一个字符,而.NET中”0″表示第一个字符length:整数，指定子串的长度（要返回的字符数或字节数） 2.pat index 返回指定表达式中某模式第一次出现的起始位置；如果在全部有效的文本和字符数据类型中没有找到该模式，则返回零中每个 字符串 从第 3 个字符开始的 4 个字符。在第二个示例中，我们计算了每个 字符串 的长度，然后提取了从倒数第二个字符开始的 3 个字符。在第三个示例中，我们提取了从第 5 个字符开始到 字符串 末尾的所有字符，因为我们没有指定。函数用于从一个 字符串 中提取子 字符串 。这个函数接受三个参数：源 字符串 、开始位置和子 字符串 的长度。参数指定的范围超出了 字符串 的实际长度，那么函数将返回从。参数的值小于 1，或者大于 字符串 的长度，那么。函数将返回一个空 字符串 。位置到 字符串 末尾的所有字符。在第一个示例中，我们提取了。 start_location 可选 —指定从父 字符串 开始查找的位置，默认位置从1开始。获取子 字符串 ，由中的第位置开始，选出接下去的个字元。获取 字符串 1.2.3.4中字符"."最后一次出现的位置。expression1 必需 —要查找的子 字符串 。expression2 必需 —父 字符串 。截取最后一个小数点前面的子 字符串 。获取某字符第一次出现的位置。将 字符串 从尾部到头部排序。一、 sql server 提供了三种常用截取 字符串 方法， LEFT ()、 RIGHT ()、 SUBSTRING () 1、 LEFT ()函数语法： LEFT ( char acter,integer) 注释：参数1：要截取的 字符串 ，参数2：截取字符个数说明：返回从 字符串 左边开始指定个数的字符select LEFT (' SqlServer _2014',3)结果： Sql 2、 RIGHT ()函数语法： RIGHT ( char ... 返回一个从start Index 开始到结束的子 字符串 ，或返回一个从start Index 开始，长度为length的子 字符串 。语句：select substring ('abcdef',2,3) 结果：bcd JS中substr和 substring 的用法和区别如果想要在 Microsoft SQL Server 中查找某个字符在 字符串 中第 N 次出现的位置，可以使用 CHAR INDEX 函数。该函数接受三个参数：要查找的字符(必需) 要搜索的 字符串 (必需) 开始搜索的位置(可选) 它会返回所查找字符在 字符串 中的位置，如果字符不存在，则返回 0。举个例子，如果你想查找 字符串 'abcdef' 中字符 'c' 第二次出现的位置，可以使用以下查询： SQL 中的 substring 函数是用来抓出一个栏位资料中的其中一部分。这个函数的名称在不同的资料库中不完全一样： My SQL : SUBSTR( ), SUBSTRING ( ) Oracle: SUBSTR( ) SQL Server : SUBSTRING ( ) 最常用到的方式如下 (在这里我们用 SUBSTR( ) 为例)： 1. SQL 中有两种参数不同的方法《1》两个参数 //查询table中*str字段从pos位置到结尾*的数据 select SUBSTR (str, pos) from tab 这样的语句将使用索引，而 substr 如“where substr(name,1,2) = 'JO';如前所述，Oracle 在使用“like 子句”时可能会做出次优猜测，因为缺乏有助于基数估计的统计信息。我有带有 substr 和 like 值的 SQL ，我想知道优化器出于性能原因将如何处理 like 和 substr。就个人而言，我发现使用 substr 子句最适合大型生产查询，前提是您构建基于函数的索引并在目标列上部署扩展的优化器统计信息。具有基于函数的索引或类似语句的 substr？