SHOW PROCESSLIST-它显示大约30多行已被锁定几秒钟,然后更新。
有没有办法加快更新速度?
例子:

+------+-----------------+-----------+----------------+---------+------+----------+-------------------------------------------------------------------------------
| Id   | User            | Host      | db             | Command | Time | State    | Info
+------+-----------------+-----------+----------------+---------+------+----------+--------------------------------------------------------------------------------
|  265 | user            | localhost | xxxxxxxxxxxxxx | Query   |   15 | Updating | UPDATE data SET status = '1', error = 'Unknown error'  WHERE f= 0xxxxx
|  269 | user            | localhost | xxxxxxxxxxxxxx | Query   |   17 | Updating | UPDATE data SET status = '1', error = 'Invalid ....'  WHERE f= 0xxx
|  280 | user            | localhost | xxxxxxxxxxxxxx | Query   |    7 | Updating | UPDATE data SET status = 1  WHERE f = 0xxxx
|  300 | user            | localhost | xxxxxxxxxxxxxx | Query   |    1 | Updating | UPDATE data SET status = '1', error = 'Unknown ....'  WHERE f= 0xx
|  314 | user            | localhost | xxxxxxxxxxxxxx | Query   |   13 | Updating | UPDATE data SET status = '1', error = 'Invalid....'  WHERE f= 0xxxx
|  327 | user            | localhost | xxxxxxxxxxxxxx | Query   |   11 | Updating | UPDATE data SET status = '1', error = 'Unknown ....'  WHERE f= 0xxxx
|  341 | user            | localhost | xxxxxxxxxxxxxx | Sleep   |    2 |          | NULL
|  350 | user            | localhost | xxxxxxxxxxxxxx | Query   |    7 | Updating | UPDATE data SET status = '1', error = 'Unknown ....'  WHERE f= 0xxx
|  360 | user            | localhost | xxxxxxxxxxxxxx | Query   |    5 | Updating | UPDATE data SET status = 1  WHERE f = 0xxxx

有很多更新-我正在使用InnoDB,有些字段是index
在数据表中,有超过500000行需要更新,而它在php cli脚本中循环(在linux的后台/进程中运行)。

最佳答案

确保f列已编制索引,以便where子句使用它。否则每次更新都会扫描整个表。
尝试将更新分组,以便一次可以用一个更新而不是一行来设置多行。
确保您没有使用autocommit。尝试在每个事务中执行多个更改。
确保你已经调谐了innodb。大多数人使用innodb_buffer_pool_sizeinnodb_log_file_sizeinnodb_io_capacity的默认值。默认值没有针对高性能进行调整。
设置innodb_flush_log_at_trx_commit=2以减少fsyncs,正如@paulo h.建议的那样。

10-06 05:11