MySQL更新时间太长 [英] MySQL update taking(too) long time
问题描述
在我们的服务获得预期的增长之后，突然之间一些更新花费了非常长的时间，这些更新过去一直非常快，直到表达到大约2MM记录为止，现在每个更新大约需要40-60秒.
After some expected growth on our service all of the sudden some updates are taking extremely long time, these used to be pretty fast until the table reached about 2MM records, now they take about 40-60 seconds each.
update table1 set field1=field1+1 where id=2229230;
Query OK, 0 rows affected (42.31 sec)
Rows matched: 1 Changed: 0 Warnings: 0
以下是字段类型:
`id` bigint(20) NOT NULL auto_increment,
`field1` int(11) default '0',
分析的结果，对于上下文切换来说，这是唯一一个在结果上显示高数字的开关:
Result from the profiling, for context switches which is the only one that seems to have high numbers on the results:
mysql> show profile context switches
-> ;
+----------------------+-----------+-------------------+---------------------+
| Status | Duration | Context_voluntary | Context_involuntary |
+----------------------+-----------+-------------------+---------------------+
| (initialization) | 0.000007 | 0 | 0 |
| checking permissions | 0.000009 | 0 | 0 |
| Opening tables | 0.000045 | 0 | 0 |
| System lock | 0.000009 | 0 | 0 |
| Table lock | 0.000008 | 0 | 0 |
| init | 0.000056 | 0 | 0 |
| Updating | 46.063662 | 75487 | 14658 |
| end | 2.803943 | 5851 | 857 |
| query end | 0.000054 | 0 | 0 |
| freeing items | 0.000011 | 0 | 0 |
| closing tables | 0.000008 | 0 | 0 |
| logging slow query | 0.000265 | 2 | 1 |
+----------------------+-----------+-------------------+---------------------+
12 rows in set (0.00 sec)
该表大约有250万条记录，id是主键，并且在另一个字段(更新中未包括)上具有一个唯一索引.
The table is about 2.5 million records, the id is the primary key, and it has one unique index on another field (not included in the update).
这是一个innodb表.
It's a innodb table.
关于可能是什么原因的任何指示?
any pointers on what could be the cause ?
是否有任何特定变量可以帮助跟踪问题?
Any particular variables that could help track the issue down ?
是否有用于更新的解释"?
Is there a "explain" for updates ?
另外，我只是注意到该表也有一个:
Also I just noticed that the table also has a :
`modDate` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
说明:
+----+-------------+---------------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------+-------+---------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | table1 | const | PRIMARY | PRIMARY | 8 | const | 1 | |
+----+-------------+---------------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.02 sec)
推荐答案
如果id确实是主键，除非没有很多等于2229230的id，否则查询不会花费很长时间.请运行以下两个sql并发布结果:
There's no way that query should take a long time, if id is really the primary key (unless you have lots and lots of ids equal to 2229230?). Please run the following two sqls and post the results:
show create table table1;
explain select * from table1 where id = 2229230;
更新:只是为了完整，也要做一个
Update: just to be complete, also do a
select count(*) from table1 where id = 2229230;
这篇关于MySQL更新时间太长的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
