优化以通配符开头的LIKE表达式

小编典典

sql

我在SQL Server数据库中有一个表，里面有一个地址字段（例如1 Farnham
Road，Guildford，Surrey，GU2XFF），我想在搜索字符串前后使用通配符进行搜索。

SELECT *
FROM Table
WHERE Address_Field LIKE '%nham%'

我在此表中大约有200万条记录，发现查询需要5到10s的时间，这是不理想的。我相信这是由于前面的通配符。

我想说的对，因为前面有通配符，所以任何索引都不会用于搜索操作。

无法使用全文本搜索和CONTAINS，因为我想搜索单词的后半部分（我知道您可以在下面的查询中替换Guil
*的搜索字符串，这将返回结果）。当然运行以下命令不会返回任何结果

SELECT *
FROM Table
WHERE CONTAINS(Address_Field, '"nham"')

有什么方法可以优化前面的通配符查询吗？

阅读 227

2021-05-16

共1个答案

小编典典

这是一种（不建议使用）解决方案。

创建一个表AddressSubstrings。该表的每个地址都有多个行，并且主键为table。

当您在中插入地址时table，请从每个位置插入子字符串。因此，如果要插入“ abcd”，则应插入：

以及表格中该行的唯一ID。（所有操作都可以使用触发器来完成。）

在上创建索引AddressSubstrings(AddressSubstring)。

然后，您可以将查询的短语表达为：

SELECT *
FROM Table t JOIN
     AddressSubstrings ads
     ON t.table_id = ads.table_id
WHERE ads.AddressSubstring LIKE 'nham%';

现在将有一个匹配的行以开头nham。因此，like应利用索引（并且全文索引也可以）。

如果您对解决此问题的正确
方法感兴趣，那么Postgres文档是一个合理的起点。这使用类似于上面的方法，但是使用n-gram。对于您的特定问题，n-gram唯一的问题是它们需要重新编写比较以及更改存储。

2021-05-16