SQL Servere 通过LIKE在另一个字符串中查找字符串

我们经常需要在另一个字符串中搜索字符串。对于匹配需要精确的情况，我们可以使用带有通配符的LIKE运算符来指定模式。虽然这满足需求，但在较大的数据集上查询会非常慢，因为它需要SQL Server在遍历所有行时检查每个字符串。通过给字段添加索引可以大大加快操作速度，但是索引也有其局限性。

本文虫虫给大家介绍了模糊搜索LIKE在开头(%abcd)或结尾搜索（abcd%），而是在字符串中的任何位置搜索（%abcd%）的情况。

为了说明这个问题，首先我们构建一个简单的数据表作为实例：

CREATE TABLE dbo.String

(

StringId INT NOT NULL PRIMARY KEY IDENTITY(1, 1) ,

String VARCHAR(20) NOT NULL

);

我们使用大小5M行的样本数据集填充String表，其中String列中只有20个字符长度，StringID为一个整数，非空（NOT NULL），主键（PRIMARY KEY）。

我们用他来测试不同查询的性能比对：

不用索引

要查找String列中以字符序列'abcd'开头的字符串数，我们使用以下查询：

SELECT COUNT(1)

FROM dbo.String

WHERE String LIKE 'abcd%';

该查询在14秒内执行。

要查找包含'abcd'序列的所有字符串的计数，查询为：

SELECT COUNT(1)

FROM dbo.String

WHERE String LIKE '%abcd%';

执行时间也一样的。因为SQL Server必须执行全表扫描并在任何一种情况下检查每个字符串的匹配项。

索引优化

加速查询的常用的技术给该字段String创建索引：

CREATE NONCLUSTERED INDEX ix_nc_test ON dbo.String (String);

在构建索引之后，使用'abcd%'谓词执行查询在1ms内得到执行。第二个查询不会走索引，所以不能得到加速。

因为索引从第一个字符排序到最后一个字符，并且查找匹配字符串的操作类似于我们之前看到的操作。如果LIKE谓词是'%abcd'并且我们想要搜索以'abcd'字符结尾的字符串，我们可以使用另一个优化，对该字段穿件反向索引。

但是，正向和反向索引好像都帮不到我们原始的需求（实际上是模糊查询），如果查询字符可能出现在我们表中的字符串可能任何的位置。

构建临时表

这是一个普遍的问题，如果我们对字符串长度有长短限制，我们可以牺牲存储空间和通过来剪切字符串，构造临时表，通过'abcdefgh'这样的字符串中构造八个子字符串并存储它们以下列方式在临时表，包含StringID和拆分的子段：

abcdefgh

bcdefgh

cdefgh

defgh

efgh

fgh

gh

h

执行此操作后，我们可以索引列并使用后置通配符执行LIKE搜索。这会帮我们找到搜索的字符串，如果该字串出现在这个字符串中的话。

当然，这意味着对于长度为3的字符串，我们在新表中得到3行。随着字符串长度的增加，存储它所需的行数也会几何增加。比如，要存储长度为n的字符串所需的字符总数（以及空间）等于等差数列：n + (n-1) + (n-2) + … + 2 + 1,其值为(n*(n+1))/2。对于长度为100的字符串，这将变为5050个字符。对于长度为20的字符串，必须存储的字符数为210.所以这个过程会导致需要更多的资源处理查询。

对于较长的字符串，使用这种临时表查询性能可能无法证明这些资源的开销。考虑到诸如系统的整体吞吐量之类的其他因素，可以将此技术的应用限制为诸如电子邮件，用户名，短文章和类似的字符串，等等对较短字段。

下面我们在一个StringSplit临时表中测试性能，这个表为填充了字段分割后String表中的数据，其中字符串按照上面所述分割方法进行拆分（StringId对于每个子字符串都是相同的）：

CREATE TABLE dbo.StringsSplit

(

StringId INT NOT NULL ,

StringSplit VARCHAR(20) NOT NULL

);

5M的原始表处理完成后表中的行计数为100M。我们在StringSplit列上创建聚簇索引：

CREATE CLUSTERED INDEX ix_c_test ON dbo.StringSplit (StringSplit);

构建好后，现在我们可以通过以下询查询：

SELECT COUNT(StringId)

FROM dbo.String

WHERE String LIKE '%abcd%';

SELECT COUNT(DISTINCT StringId)

FROM dbo.StringSplit

WHERE StringSplit LIKE 'abcd%';

第一个查询在14s完成，就像之前一样。第二个查询用了大概250ms。 正如预期的一样，我们使用查询计划表明这种差异，我们在第一个上看到了完整的索引扫描（因为我们创建的索引在字符串的开头搜索）和而第二个是聚簇索引搜索。

虽然这个方法可能有些局限性能，不适用于任何场景。但是我们成功使用这种方法，可以对特定的数据集做优化将其执行时间从十几秒秒以上优化到不到1秒，性能提高非常客观。

注意，本文虫虫以SQL Server为例，但是对其他RDB，比如Mysql、Oracle等也都是适用的。