简单的 MySQL 查询花费的时间太长
环境：Rails 2.3.11、MySQL 5.1 (InnoDB)
我的 Rails 应用程序偶尔会遇到简单查询花费太长时间才能完成的问题，并成为整个应用程序更新能力的瓶颈。这些查询通常与论坛、网站流量最高的部分以及（迄今为止）更新最多的部分相关。下面是从 MySQL 慢速日志中提取的示例查询的示例：
# Query_time: 46.900202 Lock_time: 0.000030 Rows_sent: 0 Rows_examined: 0
SET timestamp=1302172666;
UPDATE `forum_topics`
SET `views` = 153, `updated_at` = '2011-04-07 10:36:59'
WHERE `id` = 1213305;
这是一个非常简单的查询，应该非常快，尽管在本例中它花了近 47 秒才能完成。该服务器上的平均负载永远不会超过 2，因此这不是问题。其他一些兴趣点是：
views和updated_at都不是 已编入索引。- 虽然自动增量值可能 是1.2M，实际上只有 该表中有 70K 条记录。
- 慢查询日志中90%以上的查询都与此类似。
我在这里寻找的是有关解决此问题的后续步骤的一些建议。
谢谢。
PS Schema/索引如下：
CREATE TABLE `forum_topics` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`forum_category_id` int(11) DEFAULT NULL,
`title` varchar(255) NOT NULL,
`sticky` tinyint(1) DEFAULT '0',
`views` int(11) DEFAULT '0',
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL,
`last_post_created_at` datetime DEFAULT NULL,
`slug` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `index_forum_topics_on_created_at` (`created_at`),
KEY `index_forum_topics_on_forum_category_id` (`forum_category_id`),
KEY `index_forum_topics_on_sticky` (`sticky`)
) ENGINE=InnoDB AUTO_INCREMENT=1215414 DEFAULT CHARSET=utf8;
Environment: Rails 2.3.11, MySQL 5.1 (InnoDB)
My Rails app has been sporadically encountering issues with simple queries taking far too long to complete and bottlenecking the entire application's ability to be updated. These queries typically are related to the forum, the highest traffic section of the site and the one with (by far) the most updates. Here is an example of a sample query pulled from the MySQL slow log:
# Query_time: 46.900202 Lock_time: 0.000030 Rows_sent: 0 Rows_examined: 0
SET timestamp=1302172666;
UPDATE `forum_topics`
SET `views` = 153, `updated_at` = '2011-04-07 10:36:59'
WHERE `id` = 1213305;
This is a very simple query and should be extremely fast, though in this case it took almost 47 seconds to complete. The load average on this server never exceeds 2, so that is not the issue. Some other points of interest are:
- Neither
viewsnorupdated_atare
indexed. - While the auto-increment value may
be 1.2M, there are actually only
70K records in this table. - Over 90% of all queries in the slow query log are similar to this one.
What I am looking for here are some recommendations regarding next steps to take to resolve this issue.
Thanks.
P.S. Schema/indexes are as follows:
CREATE TABLE `forum_topics` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`forum_category_id` int(11) DEFAULT NULL,
`title` varchar(255) NOT NULL,
`sticky` tinyint(1) DEFAULT '0',
`views` int(11) DEFAULT '0',
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL,
`last_post_created_at` datetime DEFAULT NULL,
`slug` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `index_forum_topics_on_created_at` (`created_at`),
KEY `index_forum_topics_on_forum_category_id` (`forum_category_id`),
KEY `index_forum_topics_on_sticky` (`sticky`)
) ENGINE=InnoDB AUTO_INCREMENT=1215414 DEFAULT CHARSET=utf8;
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（7）
1) 可能存在硬件错误 - 磁盘重置。
2) id key 是否已建立索引？
3）通讯问题。
4）时间戳是局部变量吗？前面不应该有“@”吗？
1) Perhaps there is a hardware error - disk reset.
2) Is the id key indexed?
3) communications problem.
4) is timestamp a local variable? should it not have an '@' before it?
许多数据库产品突然停止运行的原因有很多，MySQL 并不是唯一的原因。以下是一些示例：
您需要从存储中加载表描述（forum_topics 上没有可用的表缓存条目）。此操作是序列化的 - 您可能会在另一个表上的另一个用户后面排队。
您正在使用查询缓存，并且查询缓存（可能）是碎片化的。
InnoDB 当前正在执行诸如扩展数据文件大小之类的活动，这会导致短暂的暂停（也许您将 auto_extend_increment 设置为高？）
这些只是示例 - 当然还有更多原因。您需要做的是使用分析工具，例如穷人的分析器。它将为您提供有关查询被阻止的确切位置的堆栈跟踪。在 bugs.mysql.com 中搜索堆栈跟踪，或者尝试在 Percona 论坛 等网站上提问。
There are a lot of causes for sudden stalls in many database products - and MySQL is not alone. Here are a few examples:
You need to load the table description from storage (no free table cache entries on forum_topics). This operation is serialized - you could be queued behind another user on another table.
You are using the query cache, and the query cache is (possibly) fragmented.
InnoDB is currently performing an activity like extending the size of a data file, which causes a brief pause (maybe you have auto_extend_increment set to high?)
These are just examples - there are certainly more causes. What you need to do is use a profiling tool such as poor man's profiler. It will give you a stack trace as to exactly where the query is blocked. Search bugs.mysql.com for the stack trace, or try asking on something like the Percona forums.
尝试将
LIMIT添加到您的查询中。认为这应该是这样的：Try to add
LIMITto your query. Think this should look this way:where 子句通常是长时间运行查询的罪魁祸首。我可以看到，在此查询中，仅引用了“id”列。我会检查它是否已索引。如果是，我会检查它是否正确索引。如果索引已经存在，我什至会尝试重新索引“id”列
The where clause is usually the culprit in long running queries. I can see that in this query, only the 'id' column is being referenced. I would check if it is indexed. If it is, I'd check if it is indexed properly. I would even try re-indexing the 'id' column if an index is already present on it
对表运行优化可能会有所帮助，特别是当磁盘上仍有大量已删除的行时。
除此之外，该查询的 EXPLAIN 揭示了它的运行方式（好吧，该查询的选择版本）
Running optimize on the table might help, especially if there's a lot of deleted rows still on disk.
Beyond that, what does EXPLAIN on that query reveal about how it's running (well, the select version of that query)
这里没有足够的信息来准确告诉您查询执行缓慢的原因，但是：
如果这是一个 MYISAM 表，这些更新可能需要很长时间，因为 MYISAM 缺乏表上的行级锁定。这会影响您的查询性能，因为您需要运行的每个 SELECT 都会获得表上的 READ LOCK（不允许进一步写入）。此外，MYISAM 表上的 UPDATE 需要另一个锁，这会阻止所有其他 UPDATES/INSERTS/SELECTS 运行。因此，如果您运行了 10 个这样的查询，它们不会同时运行，并且必须等待彼此完成。
我不认为索引是你的问题，正如你提到的自动增量并使用 ID 字段进行更新，所以我确信这是你的主键。
There's not really enough information here to tell you exactly why your query is slowly performing, but:
If this is a MYISAM table, these updates could take a very long time because MYISAM lacks row-level-locking on tables. This affects your query performance because every SELECT you need to run obtains a READ LOCK on the table (not allowing further writes). Also, an UPDATE on a MYISAM table requires another lock, which blocks all other UPDATES/INSERTS/SELECTS from running. So if you had 10 of these queries running, they're not running concurrently, and have to wait for each other to be finished.
I don't think indexes are your problem here, as you mentioned Auto-Increment and use the ID field for your update, so I'm sure that's your primary key.
这有点旧，但我只是偶然发现它，也许对其他人有帮助...
对于这样的问题，您可以使用内部分析器：
如果您的问题随机发生，则另一个进程可能已锁定表。您可以使用
SHOW FULL PROCESSLIST和SHOW OPEN TABLES来查看这一点。另外，对于大表，您可能会遇到缓冲区的内存问题 - 有很多关于此的好页面。 http://dev.mysql.com/doc/refman/5.1 /en/innodb-tuning.html 和 http ://www.mysqlperformanceblog.com/2007/11/01/innodb-performance-optimization-basics/是一个很好的开始
This is a little old but I just stumbled upon it, and maybe it´s helpful for others...
With problems like this you can use the internal profiler:
If your problem occurs randomly, another process might have locked the table. You can use
SHOW FULL PROCESSLISTandSHOW OPEN TABLESto see this.Also, with big tables you can run into memory problems for the buffers - there are a lot of good pages about this. http://dev.mysql.com/doc/refman/5.1/en/innodb-tuning.html and http://www.mysqlperformanceblog.com/2007/11/01/innodb-performance-optimization-basics/ are a great start