提高MySQL InnoDB全文搜索性能

Question

1. 背景：数据库：MySQL 8.0，InnoDB引擎；表大小：约2M行2G数据；FULL_TEXT索引列：句子(TEXT数据类型(https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html

2. 使用SQL LIKE的旧查询：

   SELECT * FROM books WHERE sentence LIKE '%This is a sample search input string%' and author_id = 5 and publisher_id = 23;

3. 使用MySQL FULL_TEXT搜索的新查询：

   SELECT * FROM books WHERE MATCH (sentence) AGAINST ('This is a sample search input string') and author_id = 5 and publisher_id = 23 LIMIT 1;

4. 问题：我预计使用LIKE到FULL_TEXT(match…against(会大大提高搜索速度。但根据我的测试，情况并非如此：对于<10个单词，全文搜索比LIKE快；对于大约25个单词的输入字符串，完整文本搜索可能需要3秒以上才能返回，这与LIKE类似。字符串越长，全文搜索的速度越差，可能需要超过15秒。

5. 分析查询：https://dev.mysql.com/doc/refman/8.0/en/show-profile.html通过观察分析结果；FULLTEXT初始化"；

6. 我尝试过的优化并没有带来速度的提高：

6.1重写试图将其他索引与全文索引一起使用的查询：

select * from books as b1 join books b2 on b1.author_id = b2.author_id and b1.publisher_id = b2.publisher_id WHERE b2.author_id = 5 and b2.publisher = 23 and MATCH (b1.source) AGAINST ('Sample input string') LIMIT 1;

6.2只选择document_id而不是整个记录：

SELECT id FROM books WHERE MATCH (sentence) AGAINST ('This is a sample search input string') and author_id = 5 and publisher_id = 23 LIMIT 1;

7. 问题：还有其他方法可以提高搜索速度吗？根据本文件：https://dev.mysql.com/doc/refman/8.0/en/fulltext-fine-tuning.html我可以尝试添加更多的停止字，运行OPTIMIZETABLE，将当前表移动到新表，或者升级硬件。但我不确定是否值得尝试这些方法

Answer 1

• 抛出短词和任何停止词

• 在其余部分加上前缀"+"并加上"+"；在升压模式中"；

  在增强模式下，反对("+样本+搜索+输入+字符串"(

如果你需要与你的like完全相同的单词，那么就进行

WHERE MATCH(..) AGAINST(...)
AND ... LIKE '%This is a sample search input string%'
AND ...

FULLTEXT搜索会很快，但可能会找到以其他顺序包含单词的文档。LIKE会很快，因为它只检查几行(FT发现的那些行(。