SQL Server全文搜索，精确匹配并回退

Question

SQL Server全文搜索，精确匹配并回退

sql-server-2008full-text-searchcontainsexact-match

12

首先，似乎无法通过全文搜索获得精确匹配。当使用全文搜索方法时，这似乎是一个非常讨论的问题，并且有许多不同的解决方案可以实现所需的结果，但大多数解决方案似乎效率很低。因为我的数据库容量很大，我被迫使用全文搜索，最近不得不实施其中一种解决方案以获得更准确的结果。

由于全文搜索的工作方式，我不能使用其排名结果。例如，如果您搜索一个名为Toy Story的电影，并且还有一个名为The Story Behind Toy Story的电影，那么它会显示第二个结果而不是完全匹配，因为它在两个标题中都找到了单词Story和Toy。

我跟踪自己的排名，称之为“热度”，每次用户访问记录时，数字就会上升。我使用此数据点来加权我的结果，以帮助确定用户可能正在寻找的内容。

我也有这样的问题，有时需要退回到LIKE搜索，而不返回精确匹配。例如，搜索Goonies应该返回The Goonies（最流行的结果）。

因此，这是我当前用于实现此目的的存储过程的示例：

DECLARE @Title varchar(255)
SET @Title = '"Toy Story"'
--need to remove quotes from parameter for LIKE search
DECLARE @Title2 varchar(255)
SET @Title2 = REPLACE(@title, '"', '')

--get top 100 results using full-text search and sort them by popularity
SELECT TOP(100) id, title, popularity As Weight into #TempTable FROM movies WHERE CONTAINS(title, @Title) ORDER BY [Weight] DESC

--check if exact match can be found
IF EXISTS(select * from #TempTable where Title = @title2)
--return exact match
SELECT TOP(1) * from #TempTable where Title = @title2
ELSE
--no exact match found, try using like with wildcards
SELECT TOP(1) * from #TempTable where Title like '%' + @title2 + '%'
DROP TABLE #TEMPTABLE

这个存储过程每分钟执行大约5000次，令人惊讶的是它并没有拖垮我的服务器。但我真的很想知道是否有更有效的方法？谢谢。

- bfritz

4个回答

2

这将为您提供包含短语“Toy Story”的电影列表，按受欢迎程度排序。

SELECT
    m.[ID],
    m.[Popularity],
    k.[Rank]
FROM [dbo].[Movies] m
INNER JOIN CONTAINSTABLE([dbo].[Movies], [Title], N'"Toy Story"') as [k]
    ON m.[ID] = k.[Key]
ORDER BY m.[Popularity]

请注意，如果您搜索"The Goonies"，上面的内容也会给您返回"The Goonies Return"。

- jdl

嘿J Lo，那个方法行不通。我需要一个默认精确匹配或者如果没有找到则使用最接近的结果的东西。使用CONTAINSTABLE方法也有上述相同的问题，例如如果你搜索“Toy Story 2”，它会返回“Toy Story”作为顶部结果。这也可能是全文本无法正确索引数字或单个字符的限制。 - bfritz

我最近禁用了所有的停用词/噪声词，也许全文排名会更准确？我会重新测试并告诉你。 - bfritz

1

如果您觉得全文搜索中的模糊匹配不太喜欢，但是您喜欢性能方面。

也许这是一条路线：如果您坚持在加权匹配之前获得精确匹配，可以尝试对值进行哈希。例如，“Toy Story” -> 变成小写 -> toy story -> 哈希为4de2gs5sa（使用任何您喜欢的哈希）并在哈希上执行搜索。

- Paul

我必须使用全文搜索来获取前100个结果（有超过950k条记录和3-5k个请求每分钟），然后我可以使用不太优化的方法来搜索这些100个结果。将标题转换为HashBytes并执行精确搜索似乎不能减少当前流程中的任何步骤？ - bfritz

1

很难从这边说，你需要一个前100的结果 - 除了性能问题之外 - 如果没有全文搜索，你能否摆脱它？如果可以...哈希可能是一条好路。使用适当的索引应该可以处理950K条记录和每秒100个请求（以及更多的请求）。尽可能使索引尽可能小（使其非常容易适合内存），并对该解决方案进行压力测试。构建一组随机查询并在选择最终路径之前测量整个过程。 - Paul

0

在Oracle中，我已经使用UTL_MATCH来实现类似的目的。（http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/u_match.htm）

即使使用Jaro Winkler算法比较表1和表2的标题列可能需要一些时间，但是如果您部分连接这两个表，可以提高性能。在某些情况下，我使用Jaro Winkler比较表1中的人名与表2中的人名，但不仅限于超过某个Jaro Winkler阈值的名称，还限制了两个表之间第一个字母相同的名称。例如，我会使用Jaro Winkler将Albert与Aden、Alfonzo和Alberto进行比较，但不会将Albert和Frank进行比较（限制算法需要使用的情况数量）。

Jaro Winkler算法实际上也可能适用于电影标题。虽然您正在使用SQL服务器（无法使用utl_match包），但看起来有一个名为“SimMetrics”的免费库，其中包括Jaro Winkler算法和其他字符串比较指标。您可以在此处找到详细信息和说明：http://anastasiosyal.com/POST/2009/01/11/18.ASPX?#simmetrics。

- Brian DeMilia

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- flup · Accepted Answer

你应该使用全文检索 CONTAINSTABLE 来查找前100个 (可能是200个) 候选结果，然后按照你自己的标准对找到的结果进行排序。

听起来你想使用 ORDER BY

精确匹配短语 (=)
完全匹配短语 (LIKE)
Popularity 列的更高值
CONTAINSTABLE 的 Rank

但你可以自己调整所需的顺序。

在 SQL 中看起来像这样：

DECLARE @title varchar(255)
SET @title = '"Toy Story"'
--need to remove quotes from parameter for LIKE search
DECLARE @title2 varchar(255)
SET @title2 = REPLACE(@title, '"', '')

SELECT
    m.ID,
    m.title,
    m.Popularity,
    k.Rank
FROM Movies m
INNER JOIN CONTAINSTABLE(Movies, title, @title, 100) as [k]
    ON m.ID = k.[Key]
ORDER BY 
  CASE WHEN m.title = @title2 THEN 0 ELSE 1 END,
  CASE WHEN m.title LIKE @title2 THEN 0 ELSE 1 END,
  m.popularity desc,
  k.rank

请参考SQLFiddle。