MySQL全文查询锁定表
每隔一段时间，我们就会在 MySQL 中遇到运行时间特别长的全文查询。该查询将运行很长时间，目前我看到一个运行了 50,000 秒（并且仍在运行）。在查询上使用 Kill 或 Kill Query 似乎没有任何作用。另外，客户端的命令超时时间是30秒，所以客户端已经失败并消失了，但服务器仍然继续运行查询，消耗了相当多的资源。过去，我们必须重新启动 MySQL 服务才能停止此类查询的运行。有没有其他方法可以阻止这些查询。我们正在采取措施确保查询一开始就不会运行，但它们仍然偶尔运行一次，这是一个很大的痛苦。主要是因为当查询运行时，我们无法对表执行更新。
长时间运行的查询的一个示例如下：
SELECT id,Title From Articles WHERE MATCH(ArticleText) AGAINST('+Nintendo*HD*Wii*' IN BOOLEAN MODE)
由于 * 位于搜索字符串的中间以及字符串的末尾，因此运行时间特别长。
Every once in a while we get a particularly long running full text query in MySQL. The query will run for a very long time, currently I'm seeing one that's running for 50,000 seconds (and still going). Using Kill, or Kill Query on the query seems to do nothing. Also, the command Timeout on the client side is 30 seconds, so the client has already failed and went away, but the server just keeps on running the query, using up quite a lot of resources. In the past we have had to restart the MySQL service to get such queries to stop running. Is there any other way to stop these queries. We are taking measures to ensure the queries don't run in the first place, but they still get run every once in a while, and it's a major pain. Mostly because while the queries are running, we are unable to perform updates on the table.
An example of a long running query would be something along the lines of:
SELECT id,Title From Articles WHERE MATCH(ArticleText) AGAINST('+Nintendo*HD*Wii*' IN BOOLEAN MODE)
This runs particularly long because of the * in the middle of the search string, as well as at the end of the string.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
您使用什么版本的 MySQL？您也许可以通过升级来解决此问题。
另外，该示例查询是真实的吗？我希望搜索字符串类似于“+Nintendo* +HD* +Wii*”，而不是“+NintendoHDWii*”。
我在 5.0 中从未遇到过此类问题，但在 MySQL 5.1 的早期版本中，我看到了几个与搜索字符串中的某些内容相关的非常相似的问题，导致全文查询挂起且无法终止。
这是我在 5.1.42 中看到的一个，已在 5.1.45 中修复：
http://bugs.mysql.com/bug.php?id=50556
被标记为欺骗：
http://bugs.mysql.com/bug.php?id=50351
我报告了另一个问题，当搜索字符串包含冒号时全文查询挂起。该问题在 5.1.31 中存在，并在 5.1.37 中修复。
What version of MySQL are you using? You might be able to fix this by upgrading.
Also, is that sample query real? I would expect the search string to be something like '+Nintendo* +HD* +Wii*' instead of '+NintendoHDWii*.
I've never had this type of issue in 5.0, but in earlier releases of MySQL 5.1 I have seen several very similar issues regarding certain content in the search string causing the fulltext query to hang and be unkillable.
Here's one that I saw in 5.1.42, that was fixed in 5.1.45:
http://bugs.mysql.com/bug.php?id=50556
which was marked as a dupe of:
http://bugs.mysql.com/bug.php?id=50351
I reported another issue with fulltext queries hanging when the search string contained a colon. That issue existed in 5.1.31 and was fixed in 5.1.37.