MySQL MySQL更新需要（太）长时间
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1627794/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL update taking(too) long time
提问by webclimber
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.
在我们的服务出现一些预期的增长之后，突然一些更新需要非常长的时间，这些曾经非常快，直到表达到大约 2MM 记录，现在每个大约需要 40-60 秒。
update table1 set field1=field1+1 where id=2229230;
Query OK, 0 rows affected (42.31 sec)
Rows matched: 1 Changed: 0 Warnings: 0
Here are the field types:
以下是字段类型：
`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)
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).
该表大约有 250 万条记录，id 是主键，并且在另一个字段上有一个唯一索引（未包含在更新中）。
It's a innodb table.
这是一个 innodb 表。
any pointers on what could be the cause ?
关于可能是什么原因的任何指示？
Any particular variables that could help track the issue down ?
任何可以帮助追踪问题的特定变量？
Is there a "explain" for updates ?
是否有更新的“解释”？
EDIT: Also I just noticed that the table also has a :
编辑：此外，我刚刚注意到该表还有一个：
`modDate` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
Explain:
解释：
+----+-------------+---------------+-------+---------------+---------+---------+-------+------+-------+
| 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)
回答by Keith Randall
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:
如果 id 确实是主键（除非您有很多等于 2229230 的 id？），那么查询不可能花费很长时间。请运行以下两条sql并贴出结果：
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;
回答by webclimber
Ok after a few hours of tracking down this one, it seems the cause was a "duplicate index", the create table that I was doing to answer Keith, had this strange combo:
好的，经过几个小时的跟踪，似乎原因是“重复索引”，我正在回答 Keith 的创建表，有这个奇怪的组合：
- a unique key on fieldx
- a key on fieldx
- fieldx 上的唯一键
- fieldx 上的一个键
the second one is obviously redundant and useless, but after I dropped the key all updates went back to < 1 sec.
第二个显然是多余和无用的，但在我放下密钥后，所有更新都回到 < 1 秒。
+1 to Keith and Ivan as their comments help me finally track down the issue.
+1 给 Keith 和 Ivan，因为他们的评论帮助我最终找到了这个问题。
回答by Dereleased
May I recommend as well:
我也可以推荐：
OPTIMIZE TABLE table1;
Sometimes your tables just need a little love, if they've grown rapidly and you haven't optimized them in a while the indices may be a little crazy. In fact, if you've got the time (and you do), it never hurts to throw in
有时您的表只需要一点点爱，如果它们增长迅速并且您有一段时间没有优化它们，则索引可能有点疯狂。事实上，如果你有时间（而且你有时间），投入进去也没什么坏处
REPAIR TABLE table1;
回答by Kapytanhook
What helped for me was changing the engine from 'innodb' to 'myisam'. My update query on a similar size dataset went from 100 ms to 0.1 ms.
对我有帮助的是将引擎从“innodb”更改为“myisam”。我对类似大小数据集的更新查询从 100 毫秒变为 0.1 毫秒。
Be aware that changing the engine type for an existing application might have consequences as InnoDB has better data integrity and some functions your application might be depending on.
请注意，更改现有应用程序的引擎类型可能会产生后果，因为 InnoDB 具有更好的数据完整性以及您的应用程序可能依赖的某些功能。
For me it was worth losing InnoDB for a 1000 times speed increase on big datasets.
对我来说，为了在大数据集上提高 1000 倍的速度而失去 InnoDB 是值得的。
回答by John Dunne
I'd come across this same issue, which turned out to be due to a trigger on the table. Any time an update was done on my table, the trigger would update another table which is were the delay was actually caused. Adding an index on that table fixed the issue. So try checking for triggers on the table.
我遇到了同样的问题，结果是由于桌子上的触发器。任何时候在我的表上完成更新时，触发器都会更新另一个表，这是实际造成的延迟。在该表上添加索引解决了该问题。所以尝试检查表上的触发器。
回答by Piemol
Another thing to take into consideration when debugging update statements taking way too much time compared to the select statement version: are the update statements executed within a transaction?
与 select 语句版本相比，调试更新语句花费太多时间时要考虑的另一件事：更新语句是否在事务中执行？
In my case a transaction turned the update statement(s) from a blazingly fast execution time to a extremely slow one. The more rows affected the heavier the loss of performance. My statements work with user defined variables, but don't know if that part of the 'problem'.
在我的例子中，一个事务将更新语句从极快的执行时间变成了极慢的执行时间。受影响的行数越多，性能损失就越严重。我的语句适用于用户定义的变量，但不知道那部分是否是“问题”。
