Mysql 表间歇性变慢 - 为什么？
我们最近遇到了一个我以前从未见过的问题，在大约 3 个小时的时间里，我们的一个 Mysql 表变得非常慢。 该表保存论坛帖子，目前约有一百万行。 变慢的查询在我们的应用程序中是一种非常常见的查询：
SELECT * FROM `posts` WHERE (`posts`.forum_id = 1) ORDER BY posts.created_at DESC LIMIT 1;
我们在帖子表上有一个索引（forum_id，created_at），它通常允许在内存中进行此查询和排序。 但是，在这三个小时里，并没有那么多。 在此时间段内，通常瞬时查询需要 2 秒到 45 秒不等。 然后就恢复正常了。
我仔细研究了我们的慢查询日志，没有发现任何其他异常。 我查看了 New Relic（这是一个 Rails 应用程序），所有其他操作的运行速度基本上与正常情况相同。 今天我们没有收到异常数量的帖子。 我在我们的日志中找不到任何其他奇怪的东西。 当数据库仍有大量内存可供使用时，它就不会进行交换。
我想知道Mysql是否可以来回改变对于给定查询使用哪些索引的想法，并且无论出于何种原因，它今天开始决定对此查询进行几个小时的全表扫描？ 但如果这是真的，为什么它会停止进行全表扫描呢？
还有其他人遇到过间歇性缓慢的查询吗？ 或者您对于如何调试这样的问题有什么创造性的想法吗？
We recently had an issue I'd never seen before, where, for about 3 hours, one of our Mysql tables got extremely slow. This table holds forum posts, and currently has about one million rows in it. The query that became slow was a very common one in our application:
SELECT * FROM `posts` WHERE (`posts`.forum_id = 1) ORDER BY posts.created_at DESC LIMIT 1;
We have an index on the posts table on (forum_id, created_at) which normally allows this query and sort to happen in memory. But, during these three hours, notsomuch. What is normally an instantaneous query ranged from taking 2 seconds-45 seconds during this time period. Then it went back to normal.
I've pored through our slow query log and nothing else looks out of the ordinary. I've looked at New Relic (this is a Rails app) and all other actions ran essentially the same speed as normal. We didn't have an unusual number of message posts today. I can't find anything else weird in our logs. And the database wasn't swapping, when it still had gigs of memory available to use.
I'm wondering if Mysql could change its mind back and forth about which indexes to use for a given query, and for whatever reason, it started deciding to do a full table scan on this query for a few hours today? But if that were true, why would it have stopped doing the full table scans?
Has anyone else encountered an intermittently slow query that defied reason? Or do you have any creative ideas about how one might go about debugging a problem like this?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我会尝试 MySQL
EXPLAIN语句...可能值得检查 Rails 代码中的 MySQL 响应时间，如果超过阈值，则运行
EXPLAIN并在某处记录详细信息。表锁定也浮现在脑海中 - 是帖子当 SELECT 正在进行时，表由 cronjob 或大量查询更新？
希望有点帮助！
I'd try the MySQL
EXPLAINstatement...It may be worth checking the MySQL response time in your Rails code, and if it exceeds a threshold then run the
EXPLAINand log the details somewhere.Table locking also springs to mind - is the posts table updated by a cronjob or hefty query while SELECTs are going on?
Hope that helps a bit!
在我工作的一个网站上，我们最近从 MyISAM 切换到 InnoDB，并且 我们发现一些同时具有 WHERE 和 ORDER BY 子句的简单选择查询正在使用 ORDER BY 子句的索引，导致表扫描到找到几个所需的行（但是，当它最终找到全部行时，它们不需要排序！）
如链接文章中所述，如果您有一个小的 LIMIT 值，您的 ORDER BY 子句是第一个成员主键（因此文件上的数据按其排序），并且有许多结果与 WHERE 子句匹配，使用 ORDER BY 索引对于 MySQL 来说并不是一个坏主意。 但是，我认为 created_at 不是主键的第一个成员，因此在这种情况下这不是一个特别聪明的想法。
我不知道如果你没有更改任何内容，为什么 MySQL 会切换索引，但我建议你尝试在相关表上运行 ANALYZE TABLE 。 如果结果集足够小，您还可以更改查询以删除 LIMIT 和 ORDER BY 子句并在应用程序级别进行排序； 或者您可以添加 USE INDEX 提示永远不会猜错。
您还可以 将 wait_timeout 值更改为更小的值 这样，这些使用错误索引的查询就永远不会完成（但也不会滞后于所有合法查询）。 即使 wait_timeout 很小，您仍然可以交互式地运行长查询，因为有一个单独的配置参数。
On a site I work on, we recently switched to InnoDB from MyISAM, and we found that some simple select queries which had both WHERE and ORDER BY clauses were using the index for the ORDER BY clause, resulting in a table scan to find the few desired rows (but, heck, they didn't need to be sorted when it finally found them all!)
As noted in the linked article, if you have a small LIMIT value, your ORDER BY clause is the first member of the primary key (so the data on file is ordered by it), and there are many results that match your WHERE clause, using that ORDER BY index isn't a bad idea for MySQL. However, I presume created_at is not the first member of your primary key, so it's not a particularly smart idea in this case.
I don't know why MySQL would switch indexes if you haven't changed anything, but I'd suggest you try running ANALYZE TABLE on the relevant table. You might also change the query to remove the LIMIT and ORDER BY clauses and sort at the application level, provided the result set is small enough; or you could add a USE INDEX hint so it never guesses wrong.
You could also change the wait_timeout value to something smaller so that these queries that use a bad index simply never complete (but don't lag all of the legitimate queries too). You will still be able to run long queries interactively, even with a small wait_timeout, since there is a separate configuration parameter for that.