提高MySQL全文搜索查询的性能 [英] Improve performance on MySQL fulltext search query
问题描述
我有以下MySQL查询:
I have a following MySQL query:
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行，耗时〜200ms .具有265,000行，它需要〜500ms .
With 108,000 rows, it takes ~200ms. With 265,000 rows, it takes ~500ms.
在性能测试下(〜80个并发用户)，它显示了〜18秒的平均延迟.
Under performance testing(~80 concurrent users) it shows ~18sec average latency.
有什么方法可以改善此查询的性能吗?
Is any way to improve performance for this query ?
说明输出:
已更新
我们用post_id，description添加了一个新的镜像MyISAM表，并通过触发器将其与posts表同步.现在，在此新的MyISAM表上进行全文搜索的时间为〜400ms (具有与InnoDB显示〜18sec 相同的性能负载.这极大地提高了性能)在MySQL中使用全文本要比InnoDB快得多.你能解释一下吗?
We have added one new mirror MyISAM table with post_id, description and synchronized it with posts table via triggers. Now, fulltext search on this new MyISAM table works ~400ms(with the same performance load where InnoDB shows ~18sec.. this is a huge performance boost) Look like MyISAM is much more quicker for fulltext in MySQL than InnoDB. Could you please explain it ?
MySQL分析器结果:
在 AWS RDS db.t2.small 实例上进行了测试
InnoDB原始posts表:
Original InnoDB posts table:
带有post_id的MyISAM镜像表，仅用于描述:
推荐答案
此处是一些提示，以期在InnoDB中最大程度地寻找此类查询:
Here are a few tips what to look for in order to maximise the speed of such queries with InnoDB:
-
避免多余的排序.由于InnoDB已经根据排名对结果进行了排序. MySQL查询处理层不需要 排序以获得最匹配的结果.
Avoid redundant sorting. Since InnoDB already sorted the result according to ranking. MySQL Query Processing layer does not need to sort to get top matching results.
避免逐行获取以获取匹配计数. InnoDB提供所有匹配的记录.所有不在结果列表中的人 应该都具有0的排名，并且无需检索.和InnoDB 手上有总的匹配记录数.无需重新计数.
Avoid row by row fetching to get the matching count. InnoDB provides all the matching records. All those not in the result list should all have ranking of 0, and no need to be retrieved. And InnoDB has a count of total matching records on hand. No need to recount.
覆盖的索引扫描. InnoDB结果始终包含匹配记录的文档ID及其排名.因此，如果仅文档ID和 需要排名，无需转到用户表来获取 记录自己.
Covered index scan. InnoDB results always contains the matching records' Document ID and their ranking. So if only the Document ID and ranking is needed, there is no need to go to user table to fetch the record itself.
及早缩小搜索结果，减少用户表访问.如果用户想获取前N条匹配记录，则无需获取 用户表中的所有匹配记录.我们应该首先 选择前N个匹配的DOC ID，然后仅获取对应的 这些文档ID的记录.
Narrow the search result early, reduce the user table access. If the user wants to get top N matching records, we do not need to fetch all matching records from user table. We should be able to first select TOP N matching DOC IDs, and then only fetch corresponding records with these Doc IDs.
我认为仅查看查询本身无法获得更快的速度，也许尝试删除ORDER BY部分以避免不必要的排序.要对此进行更深入的研究，也许可以使用 MySQL内置分析器来对查询进行分析.
I don't think you cannot get that much faster looking only at the query itself, maybe try removing the ORDER BY part to avoid unnecessary sorting. To dig deeper into this, maybe profile the query using MySQLs inbuild profiler.
除此之外，您可能会调查MySQL服务器的配置.看看 MySQL手册的这一章，其中包含有关如何根据您的需要调整全文索引的一些很好的信息.
Other than that, you might look into the configuration of your MySQL server. Have a look at this chapter of the MySQL manual, it contains some good informations on how to tune the fulltext index to your needs.
如果您已经最大限度地利用了MySQL服务器配置的功能，则可以考虑研究硬件本身-有时甚至是成本降低的解决方案，例如将表移动到另一个更快的硬盘驱动器，也可以发挥作用.
If you've already maximized the capabilities of your MySQL server configuration, then consider looking at the hardware itself - sometimes even a lost cost solution like moving the tables to another, faster hard drive can work wonders.
这篇关于提高MySQL全文搜索查询的性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
