设置所有行的一列的值非常慢 [英] Setting the value of one column of all rows is very slow

问题描述

我有一个包含约35万行的表，最近我已从MyISAM存储引擎更改为InnoDB.

I have a table containing about 350 000 rows, and I have recently changed from MyISAM storage engine to InnoDB.

我运行查询

UPDATE `users` SET `online` = 0

每次我的服务器启动时，使用MyISAM时完全没有问题.该查询通常仅影响几百行.查询的执行时间很慢，平均大约1.5秒，但是我可以忍受.

every time my server starts, and there was no issues at all when using MyISAM. The query usually affects only a couple of hundred of rows. The execution time for the query was slow, about 1.5 seconds on average, but something I could live with.

现在我已更改为InnoDB，但是查询可能需要数十秒才能完成.

Now that I've changed to InnoDB, the query can, however, take tens of seconds to finish.

这是mysql-slow.log的一部分

Here's part of mysql-slow.log

# Query_time: 29.431546  Lock_time: 0.000091 Rows_sent: 0  Rows_examined: 348617
SET timestamp=1372505574;
UPDATE users SET online = 0;

此特定查询将在线值更改为200行，其他查询已经为0.

This particular query change the online value for 200 rows, others were already 0.

我能够通过将查询更改为

I was able to solve the issue by changing the query to

UPDATE `users` SET `online` = 0 WHERE `online` != 0

此查询耗时约0.1秒

现在，这是我的问题.从MyISAM更改为InnoDB时，为什么时间增加如此之大?

Now, here's my question. why does the time increase so significantly when changing from MyISAM to InnoDB?

为什么没有WHERE部分的查询如此缓慢?据我了解，MySQL的查询优化器功能非常强大，但这暗示了相反的情况.是什么原因导致查询执行时间很慢?

And how come the query is so slow without the WHERE part? As far as I've understood, MySQL's query optimiser is quite powerful, but this suggests the opposite. What could be causing this very slow query execution time?

MySQL服务器版本为5.5.31-0.

The MySQL server version is 5.5.31-0.

推荐答案

在InnoDB中，update语句锁定它们扫描的每一行.这意味着要更新您的200行，它必须创建35万行级别的锁，同时保持回滚锁，并为读取已更改值的任何事务提供先前的值(因为该事务不是提交并且更改不是最终的)

In InnoDB, update statements lock every row they scan. This means that to update your 200 rows, it have to create 350 000 row-level locks, while keeping a rollback lock at the same time and providing the previous value to any transactions that are reading the already changed value (since the transaction is not committed and the change is not final)

另一方面，MyISAM会锁定整个表.

MyISAM on the other hand locks the entire table.

因此，如果您需要更新所有行，请锁定整个表，您将获得更好的性能(不需要行级锁定)

So if you need to update all of the rows, lock the entire table, and you'll get a far better performance (you won't need the row-level locks)

但更好的是，像您一样提供WHERE子句，InnoDB将仅为匹配的行获取锁(以及索引树中的一些间隙锁，但这不在问题范围内)

But even better, provide a WHERE clause, like you did, and InnoDB will acquire locks only for the matching rows (as well as some gap-locks in the index tree, but this is out of the scope of the question)

这篇关于设置所有行的一列的值非常慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！