mysql–设置所有行中一列的值非常慢

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

我运行查询

UPDATE `users` SET `online` = 0


每次我的服务器启动时,使用MyISAM时都没有问题.查询通常只影响几百行.查询的执行时间很慢,平均约为1.5秒,但我可以忍受.

既然我已经改为InnoDB,那么查询可能需要几十秒才能完成.

这是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.

我能够通过将查询更改为来解决问题

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


此查询大约需要0.1秒

现在,这是我的问题.从MyISAM更改为InnoDB时,为什么时间会如此显着增加？

如果没有WHERE部分,查询怎么会这么慢？据我所知,MySQL的查询优化器非常强大,但这表明相反.什么可能导致这个非常慢的查询执行时间？

MySQL服务器版本是5.5.31-0.

解决方法:

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

另一方面,MyISAM锁定整个表.

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

但更好的是,像你一样提供一个WHERE子句,InnoDB只会为匹配的行获取锁(以及索引树中的一些间隙锁,但这超出了问题的范围)