时间戳

首页 > 解决方案 > Mysql 在 FULLTEXT 索引具有相同数据的两台服务器之间显示不同的结果

mysql - Mysql 在 FULLTEXT 索引具有相同数据的两台服务器之间显示不同的结果

问题描述

我有一个带有 Mysql 5.7.19（在 Windows 10 Pro French 上）的本地环境和一个带有 Mysql 5.7.31（Ubuntu Linux 16.04.5）的产品服务器。

数据从 Prod 同步到本地 ENV。我在 3 列上有一个全文索引和一个简单的请求：

SELECT MATCH (r0_.title, r0_.description, r0_.tag_text)
       AGAINST ('+poulet* +carotte*' IN BOOLEAN MODE) AS sclr_0,
       r0_.id AS id_1, r0_.title AS title_2, r0_.description AS description_3,
       r0_.url AS url_4, r0_.image AS image_5, r0_.slug AS slug_6, r0_.click AS click_7, r0_.tag_text AS tag_text_8, r0_.active AS active_9, r0_.created_at AS created_at_10, r0_.updated_at AS updated_at_11
    FROM recipe r0_
    WHERE r0_.active = 1
    HAVING sclr_0 >= 1
    ORDER BY sclr_0 DESC;

在本地环境 => 98 个结果
上 在产品环境 => 0 个结果

创建架构：

CREATE TABLE `recipe` (
  `id` int(11) NOT NULL,
  `blog_id` int(11) NOT NULL,
  `title` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `description` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `url` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `image` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `slug` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `click` smallint(6) NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  `tag_text` varchar(1000) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `active` tinyint(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `recipe`
  ADD PRIMARY KEY (`id`),
  ADD KEY `IDX_DA88B137DAE07E97` (`blog_id`),
  ADD KEY `IDX_DA88B1374B1EFC02` (`active`),
  ADD KEY `IDX_DA88B1378B8E8428` (`created_at`);
ALTER TABLE `recipe` ADD FULLTEXT KEY `IDX_DA88B1372B36786B6DE44026D5841871`
        (`title`,`description`,`tag_text`);

更多关于 Prod 的数据实际上是因为新配方但没有结果。

标签： mysqlindexingfull-text-searchresultsetfulltext-index

解决方案

相关性分数是根据完整表格的内容计算的：

InnoDB 使用“词频-逆文档频率”(TF-IDF) 加权系统的变体来对给定全文搜索查询的文档相关性进行排名。TF-IDF 权重基于一个单词在文档中出现的频率，抵消了该单词在集合中所有文档中出现的频率。换句话说，一个词在文档中出现的频率越高，而该词在文档集合中出现的频率越低，文档的排名就越高。

这里的“文档”表示单行，“文档集合”表示所有行。该手册包含确切的公式，但重要的是：由于与本地相比，您在 prod 上的食谱更多，因此得分会有所不同。例如，如果您添加了更多包含胡萝卜的食谱，则分数会下降，如果您添加的食谱不包含您的搜索词，则分数会上升。

这与单个结果本身实际上有多好完全无关！炖鸡肉胡萝卜非常适合您的搜索，但如果您的数据库中也有胡萝卜蛋糕的食谱，则绝对分数会有所不同。

因此，分数本身的绝对值通常不是一个很好的过滤标准，例如使用 your where score > 1，而是作为一种对获得的结果进行排序的方式，例如使用order by score desc，通常包括 a limit。

您不太可能为您的分数找到一个好的绝对最小值（0 除外），这通常是有意义的：

  • 如果你现在发现一个不错的价值，如果胡萝卜变得更受欢迎并且你为它们添加食谱（类似于你在 prod 上的经验），那么 2 周内它可能太高了。反之亦然，如果您使用特定值 1 来消除不需要的、较低分数的结果，如果您添加与胡萝卜无关的食谱，它们可能会在 2 周内重新出现 - 不是因为那些不需要的结果突然变得更好，而是因为它们变成了更罕见。
  • 如果您找到适合包含胡萝卜的搜索的好值，则它可能不是其他搜索词的好值。例如，如果您搜索一种常见的成分，可能是“糖”，您仍然会期望结果包含“糖”，即使绝对值会很低 - 只是因为它比胡萝卜更常用。

但是一个经常使用这个词的食谱sugar（因为它可能是一种重要的成分，也许是焦糖的食谱）将比那些只提到一次（“添加一些糖”）的食谱得分更高，因此您可以使用该值相对于彼此对结果进行排序。

推荐阅读