如何操纵MySQL全文搜索的相关性，使一个字段比另一个字段更有价值？

Question

假设我有两列，关键词和内容。我在两者之间建立了全文索引。我希望在关键词中具有foo的行比在内容中具有foo的行更具相关性。我需要做什么才能使MySQL加权关键词匹配高于内容匹配？

我正在使用"match against"语法。

解决方案：

我能够通过以下方式使其工作：

SELECT *, 
CASE when Keywords like '%watermelon%' then 1 else 0 END as keywordmatch, 
CASE when Content like '%watermelon%' then 1 else 0 END as contentmatch,
MATCH (Title, Keywords, Content) AGAINST ('watermelon') AS relevance 
FROM about_data  
WHERE MATCH(Title, Keywords, Content) AGAINST ('watermelon' IN BOOLEAN MODE) 
HAVING relevance > 0  
ORDER by keywordmatch desc, contentmatch desc, relevance desc 

- Buzz

Answer 1

创建三个全文索引：

• a）在关键词列上创建一个索引
• b）在内容列上创建一个索引
• c）在关键词和内容列上都创建一个索引

然后，你的查询：

SELECT id, keyword, content,
  MATCH (keyword) AGAINST ('watermelon') AS rel1,
  MATCH (content) AGAINST ('watermelon') AS rel2
FROM table
WHERE MATCH (keyword,content) AGAINST ('watermelon')
ORDER BY (rel1*1.5)+(rel2) DESC

点在于rel1仅在keyword列上（因为你只在该列创建了索引）给出您查询的相关性。 rel2也是这样，但是适用于content列。 现在，您可以将这两个相关性分数相加，应用任何您喜欢的加权。

但是，您没有为实际搜索使用这两个索引。 为此，您使用第三个索引，该索引位于两个列上。

（关键字、内容）索引控制召回率。 即返回什么。

两个单独的索引（一个仅针对关键字，一个仅针对内容）控制您的相关性。 您可以在此应用自己的加权标准。

请注意，您可以使用任意数量的不同索引（或根据其他因素在查询时变化索引和加权标准...如果查询包含停用词，则仅搜索关键字...如果查询包含超过3个单词，则降低关键字的加权偏差等等）。

每个索引都会占用磁盘空间，因此更多的索引需要更多的磁盘。 同时，插入操作将更长时间，因为您需要更新更多的索引。

应该为您的情况进行基准测试（小心关闭mysql查询缓存以进行基准测试，否则您的结果将受到影响）。 这不是谷歌级别的高效率，但它非常容易和“开箱即用”，几乎肯定比您在查询中使用“like”要好得多。

我发现它运行得非常好。

- mintywalker

---

我似乎无法让它工作（可能是因为我没有添加第三个索引），但将 where 条件更改为：rel1> 0 OR rel2> 0 解决了我的问题，所以谢谢。 - Ultimate Gobblement

@mintywalker，排序应该不是ORDER BY (rel1*1.5)+(rel2) DESC吗？这样可以得到最高分数，从而更相关的内容排在前面。 - PanPipes

@PanPipes 是的，应该使用 DESC，因为更高的相关性是更好的匹配。 - Flame

@mintywalker 我只是想说谢谢，这个确切的查询（根据我们的模式进行了调整）在一个社区网站上已经运行了至少五年，其中包含数以万计的新闻文章和数十万注册用户（以及更多未注册的访问者）。它一直完美地满足了我们的需求，我们从来没有遇到过性能问题。 - mastazi

你能不能通过使用WHERE MATCH (keyword) AGAINST ('西瓜') OR MATCH (content) AGAINST ('西瓜')来避免重复的全文索引？ - undefined

Answer 2

实际上，使用case语句创建一对标记可能是更好的解决方案：

select 
...
, case when keyword like '%' + @input + '%' then 1 else 0 end as keywordmatch
, case when content like '%' + @input + '%' then 1 else 0 end as contentmatch
-- or whatever check you use for the matching
from 
   ... 
   and here the rest of your usual matching query
   ... 
order by keywordmatch desc, contentmatch desc

再次强调，这只有在所有关键字匹配都优于所有仅内容匹配时才有效。我还假设同时匹配关键字和内容是最高的排名。

- notnot

---

使用 LIKE 语句并不是进行搜索的好方法。首先，除非你分割字符串，否则只能按照精确顺序匹配。例如，在数据库中搜索 LIKE '%t-shirt red%' 将无法匹配 'Red t-shirt'。其次，使用 LIKE 会导致查询执行时间更长，因为需要进行全表扫描。 - ChrisG

@ChrisG 当在 FROM 子句中使用 LIKE 时，会进行完整的表扫描，而不是在 SELECT 中使用。 - gontard

Answer 3

只使用2个全文索引的简化版本（感谢@mintywalker）：

SELECT id, 
   MATCH (`content_ft`) AGAINST ('keyword*' IN BOOLEAN MODE) AS relevance1,  
   MATCH (`title_ft`) AGAINST ('keyword*' IN BOOLEAN MODE) AS relevance2
FROM search_table
HAVING (relevance1 + relevance2) > 0
ORDER BY (relevance1 * 1.5) + (relevance2) DESC
LIMIT 0, 1000;

这将对两个完整索引列使用keyword进行搜索，并将匹配的相关性选择到两个单独的列中。我们将排除没有匹配的项目（relevance1和relevance2都为零），并通过增加content_ft列的权重来重新排序结果。我们不需要复合全文索引。

- lubosdz

---

通过使用“HAVING”而不是WHERE（与复合或其他内容一起），您会遇到一个问题，即必须执行完整的表扫描才能获得结果。这意味着，我认为这个解决方案不太可扩展。更具体地说，在极端情况下，如果您有一个包含1000万行的表，只有999个匹配项（或任何限制的n-1），由于所有行都将在查询中返回结果，大多数尽管为0，您不仅需要加载整个表，还需要迭代所有1000万行。 - conrad10781

@conrad10781 Having子句仅对匹配的结果集进行操作。 - lubosdz

正确，但是在那个查询中，表中的每条记录都会被匹配，因为没有任何过滤器。也就是说，你正在从表中选择值，但是没有where，你正在检索所有记录，然后让它们执行过滤。为了澄清这一点，请从本地搜索中删除having语句。将返回所有记录。想象一下，在一个有1000万条记录的表上运行。运行explain，它可能会说使用temporary；使用filesort。像mintywalker的回答中的where like允许首先在服务器上对记录进行过滤。 - conrad10781

@conrad10781 是的，你说得对 - 没有 WHERE 子句它会扫描整个结果集。这个想法是为了避免复杂的全文索引，因为它可能会对密集写入造成大量开销。通过在 FROM ... HAVING 之间添加 WHERE 子句就可以轻松解决这个问题，但是然后整个查询看起来就不那么简单了，并且会重复全索引匹配。上面的查询对于小数据集（比如10k-100k条记录）可能表现良好 - 具体取决于情况。 - lubosdz

好的，所以这更多关于“在哪里”而不是应用于别名，而与GROUP BY无关，因为查询中没有GROUP BY，然后在WHERE子句中重复选择的方式是有效的：WHERE MATCH (`content_ft`) AGAINST ('keyword*' IN BOOLEAN MODE) > 0 OR MATCH (`title_ft`) AGAINST ('keyword*' IN BOOLEAN MODE) > 0 - undefined

Answer 4

在布尔模式下，MySQL支持“>”和“<”运算符，以更改单词对分配给行的相关值的贡献。我想知道这样的东西是否有效？

SELECT *, 
MATCH (Keywords) AGAINST ('>watermelon' IN BOOLEAN MODE) AS relStrong, 
MATCH (Title,Keywords,Content) AGAINST ('<watermelon' IN BOOLEAN MODE) AS relWeak 
FROM about_data  
WHERE MATCH(Title, Keywords, Content) AGAINST ('watermelon' IN BOOLEAN MODE) 
ORDER by (relStrong+relWeak) desc

- Tom

Answer 5

我需要类似的东西并使用了 OP 的解决方案，但我注意到全文检索不能匹配部分单词。因此，如果“西瓜”在关键字或内容中作为一个单词的一部分（例如西瓜销售经理），它不会被匹配并且不会包含在结果中，因为 WHERE MATCH 限制了这种情况。 所以我稍微调整了一下 OP 的查询语句：

SELECT *, 
CASE WHEN Keywords LIKE '%watermelon%' THEN 1 ELSE 0 END AS keywordmatch, 
CASE WHEN Content LIKE '%watermelon%' THEN 1 ELSE 0 END AS contentmatch,
MATCH (Title, Keywords, Content) AGAINST ('watermelon') AS relevance 
FROM about_data  
WHERE (Keywords LIKE '%watermelon%' OR 
  Title LIKE '%watermelon%' OR 
  MATCH(Title, Keywords, Content) AGAINST ('watermelon' IN BOOLEAN MODE)) 
HAVING (keywordmatch > 0 OR contentmatch > 0 OR relevance > 0)  
ORDER BY keywordmatch DESC, contentmatch DESC, relevance DESC

希望这能有所帮助。

- dasplann

Answer 6

那要看你具体是什么意思：

我想让关键字中有foo的行比内容中有foo的行更相关。

如果你的意思是希望关键字中有foo的行在任何内容中有foo的行之前出现，那么我会进行两个单独的查询，一个查询关键字，然后（可能是懒加载，只有在请求时才查询）另一个查询内容。

- Davide

Answer 7

我几年前做过这个，但没有使用完整的文本索引。我现在没有代码（之前的雇主），但我还记得这种技术。

简而言之，我从每一列中选择了一个“权重”。例如：

select table.id, keyword_relevance + content_relevance as relevance from table
   left join
      (select id, 1 as keyword_relevance from table_name where keyword match) a
   on table.id = a.id
   left join
      (select id, 0.75 as content_relevance from table_name where content match) b
   on table.id = b.id

请原谅这里可能有一些糟糕的SQL语句，因为我已经好几年没有写过了，而且我是凭记忆在做这个...

希望这可以帮到你！

J.Js

- Justin James

Answer 8

据我所知，MySQL全文搜索不支持此功能，但您可以通过在关键字字段中重复该单词几次来实现该效果。例如，不要使用关键字“foo bar”，而是使用“foo bar foo bar foo bar”，这样“foo”和“bar”在关键字列中变得同等重要，并且由于它们出现多次，它们对MySQL更加相关。

我们在我们的网站上使用此方法，效果很好。

- Mr AJL

Answer 9

如果指标只是所有关键词匹配都比所有内容匹配更“有价值”，那么您可以使用带有行计数的联合。 大致如此。

select *
from (
   select row_number() over(order by blahblah) as row, t.*
   from thetable t
   where keyword match

   union

   select row_number() over(order by blahblah) + @@rowcount + 1 as row, t.*
   from thetable t
   where content match
)
order by row

如果你想对每一行应用实际权重，那么比这更复杂的情况，我就不知道该怎么帮忙了。

- notnot

---

我尝试了这个，结果出现了语法错误。我不认为我知道在 order by blahblah 位置应该放什么。有建议吗？ - Buzz

抱歉，这不是一个复制粘贴的示例。在over子句中的order by是你应用行号的顺序，所以它应该是你通常按结果排序的方式。 - notnot

现在我想一想，这个将会复制匹配关键字和内容的记录。 - notnot

我找不到任何方法使其工作。事实上，我认为mysql不支持row_number。 - Buzz