mysql - 全文搜索 Innodb 失败，MyIsam 返回结果

Question

我已将表从 myisam 升级到 innodb，但性能不一样。 innodb 在应该存在某种关系时返回 0 分数。 myisam 表返回相同术语的匹配项(我保留了旧表的副本，因此我仍然可以运行相同的查询)。

SELECT MATCH (COLUMNS) AGAINST ('+"Term Ex"' IN BOOLEAN MODE) as score
FROM table_myisam
where id = 1;

返回:

+-------+
| score |
+-------+
|     1 |
+-------+

但是:

SELECT MATCH (COLUMNS) AGAINST ('+"Term Ex"' IN BOOLEAN MODE) as score
FROM table
where id = 1;

返回:

+-------+
| score |
+-------+
|     0 |
+-------+

我认为 ex 可能没有被索引，因为 innodb_ft_min_token_size 被设置为 3。我将其降低到 1 并优化了表格，但这没有任何影响。列内容有 99 个字符长，因此我推测由于 innodb_ft_max_token_size 而未对整个列进行索引。我也将其增加到 150 并再次运行优化，但结果还是一样。

这些表之间的唯一区别是引擎和字符集。此表使用 utf8，myisam 表使用 latin1。

有没有人看到过这些行为，或者对如何解决它有建议？

更新:我将 ft_stopword_file="" 添加到我的 my.cnf 并再次运行 OPTIMIZE TABLE 表。这次我得到了

optimize | note | Table does not support optimize, doing recreate + analyze instead

此更改后查询有效。 Ex 不是停用词，所以不确定为什么会有所不同。

失败的新查询是:

SELECT MATCH (Columns) AGAINST ('+Term +Ex +in' IN BOOLEAN MODE) as score FROM Table where id = 1;

+-------+
| score |
+-------+
|     0 |
+-------+

in 导致失败，但这是我表格中的下一个词。

SELECT MATCH (Columns) AGAINST ('+Term +Ex' IN BOOLEAN MODE) as score FROM Table where id = 1;

+--------------------+
| score              |
+--------------------+
| 219.30206298828125 |
+--------------------+

我还尝试了 CREATE TABLE my_stopwords(value VARCHAR(30)) ENGINE = INNODB;，然后用 innodb_ft_server_stopword_table='db/my_stopwords 更新了 my.cnf '。我重新启动并运行:

show variables like 'innodb_ft_server_stopword_table';

带回:

+---------------------------------+---------------------------+
| Variable_name                   | Value                     |
+---------------------------------+---------------------------+
| innodb_ft_server_stopword_table | 'db/my_stopwords'; |
+---------------------------------+---------------------------+

所以我认为 in 现在不会导致查询失败，但它会继续。我还再次尝试了 OPTIMIZE TABLE table 甚至 ALTER TABLE table DROP INDEX ... 和 ALTER TABLE table ADD FULLTEXT KEY ... 都没有产生了影响。

第二次更新问题在于停用词。

$userinput = preg_replace('/\b(a|about|an|are|as|at|be|by|com|de|en|for|from|how|i|in|is|it|la|of|on|or|that|the|this|to|was|what|when|where|who|will|with|und|the|www)\b/', '', $userinput);

解决了这个问题，但对我来说这不是一个好的解决方案。我想要一个避免停用词在 mysql 中破坏它的解决方案。

停用词表数据:

CREATE TABLE `my_stopwords` (
  `value` varchar(30) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1

和

Name: my_stopwords
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2019-04-09 17:39:55
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: 
        Comment: 

Answer 1

MyISAM 的 FULLTEXT 和 InnoDB 之间有几个区别。我认为您被“短”词和/或停用词的处理所困扰。 MyISAM 将显示行，但 InnoDB 将失败。

我在使用 FT 时(以及切换到 InnoDB 之后)所做的是过滤用户的输入以避免短词。这需要额外的努力，但能让我得到想要的行。我的情况略有不同，因为生成的查询是这样的。请注意，我添加了 + 来要求单词，但不是短于 3 的单词(我的 ft_min_token_size 是 3)。这些搜索是针对build a table 和build the table:

WHERE match(description) AGAINST('+build* a +table*' IN BOOLEAN MODE)
WHERE match(description) AGAINST('+build* +the* +table*' IN BOOLEAN MODE)

(尾随的 * 可能是多余的；我没有对此进行调查。)

另一种方法

由于 FT 在处理非短、非停用词时非常有效，因此分两个阶段进行搜索，每个阶段都是可选的:要搜索“长词”，执行

WHERE MATCH(d) AGAINST ('+long +word' IN BOOLEAN MODE)
  AND d REGEXP '[[:<:]]a[[:>:]]'

第一部分通过查找“long”和“word”(如words)快速减少可能的行。第二部分确保字符串中也有一个 word a。 REGEXP 成本很高，但只会应用于那些通过第一次测试的行。

只搜索“长词”:

WHERE MATCH(d) AGAINST ('+long +word' IN BOOLEAN MODE)

只搜索单词“a”:

WHERE d REGEXP '[[:<:]]a[[:>:]]'

警告:这种情况会很慢。

注意:我的示例允许单词以任何顺序出现在字符串中的任何位置。也就是说，这个字符串将匹配我的所有示例:“她渴望他的一句话。”