提高MySQL全文搜索查询性能

Question

我有以下MySQL查询：

SELECT p.*, MATCH (p.description) AGAINST ('random text that you can use in sample web pages or typography samples') AS score 
FROM posts p 
WHERE p.post_id <> 23 
AND MATCH (p.description) AGAINST ('random text that you can use in sample web pages or typography samples') > 0 
ORDER BY score DESC LIMIT 1

有108,000行，它需要~200 it 。有265,000行，需要~500 it 。

在性能测试(~80个并发用户)下，~18秒平均延迟。

有任何方法来提高此查询的性能吗？

解释输出：

​

​

更新

我们使用post_id、description添加了一个新的镜像description表，并通过触发器将其与posts表同步。现在，对这个新的MyISAM表的全文搜索工作~400 on (与InnoDB显示~18秒的性能负载相同)。这是一个巨大的性能提升)看起来，MyISAM对于MySQL中的全文要比InnoDB快得多。你能解释一下吗？

MySQL分析器结果：

在AWS db.t2.小型实例上进行测试

posts 原始InnoDB 表：

​

​

MyISAM镜像表与post_id，仅描述：

​

​

Answer 1

为了最大限度地提高使用这里的查询速度，这里提供了一些小技巧：

1. 避免重复排序。因为InnoDB已经根据排名对结果进行了排序。MySQL查询处理层不需要排序以获得最高匹配结果。
2. 避免逐行取取以获得匹配的计数。InnoDB提供所有匹配的记录。所有不在结果列表中的人的排名都应该是0，不需要检索。InnoDB手边有一个总数匹配的记录。没必要重新计票。
3. 覆盖索引扫描。InnoDB结果总是包含匹配记录的文档ID及其排序。因此，如果只需要文档ID和排名，就不需要转到user表来获取记录本身。
4. 提前缩小搜索结果，减少用户对表的访问。如果用户希望获得顶级N个匹配记录，则不需要从用户表中获取所有匹配记录。我们应该能够首先选择顶级N匹配的DOC ID，然后使用这些Doc ID获取相应的记录。

我不认为只查看查询本身就不能获得更快的速度，也许可以尝试删除ORDER BY部件以避免不必要的排序。为了更深入地了解这一点，可以使用MySQL内置分析器对查询进行分析。

除此之外，您还可以查看MySQL服务器的配置。看看MySQL手册的这一章，它包含了一些关于如何根据需要调整全文索引的很好的信息。

如果您已经最大限度地利用了MySQL服务器配置的功能，那么请考虑查看硬件本身--有时甚至是一个丢失的成本解决方案，比如将表移到另一个硬盘上，更快的硬盘驱动器可能会产生奇迹。

Answer 2

我对性能影响的最佳猜测是查询返回的行数。要测试这一点，只需删除order by score并查看它是否提高了性能。

如果没有，则问题是全文索引。如果是这样的话，那么问题就是order by。如果是这样的话，问题就会变得更加困难。一些想法：

• 确定一个硬件解决方案，以加快排序(获取内存中的中间文件)。
• 修改查询，使其返回更少的值。这可能涉及更改停止词列表、将查询更改为布尔模式或其他想法。
• 找到另一种预先过滤结果的方法。

Answer 3

这里的问题是WHERE p.post_id <> 23

设计您的系统时，不需要将非索引列(如post_id )添加到WHERE子句中。

基本上，MySQL将搜索全文索引列，然后过滤post_id。因此，如果全文搜索返回了大量匹配，则响应时间将不像预期的那样。