在MySQL中更新200万行数据的最佳实践是什么？

Question

在MySQL中更新200万行数据的最佳实践是什么？因为通过主id更新，所以我必须用1来更新1。这太慢了。如下所示

UPDATE table SET col1=value1 WHERE id=1;
UPDATE table SET col1=value2 WHERE id=2;
UPDATE table SET col1=value3 WHERE id=3;

UPDATE table SET col1=valueN WHERE id=N;

Answer 1

假设table是innodb (查看SHOW CREATE TABLE table输出)，其速度慢的原因之一是innodb_buffer_pool_size不足。

我还假设它处于默认的auto_commit=1模式，这意味着每一个SQL行都是一个事务。

通常，最好避免需要更改表中的每一行的进程。

您可以将其拆分为多个线程进行更新，这样可以更快地通过列表。如果表是MyISAM，它就不会以这种方式缩放。

Answer 2

使用带有条件ON DUPLICATE KEY UPDATE的INSERT语句在一个查询中更新许多行的好方法。如果唯一索引或主键中存在重复值，则该语句将更新旧行。See documentation。

INSERT INTO table (id, col1, col2, col3) 
VALUES (%s, %s, %s, %s)
ON DUPLICATE KEY UPDATE 
    col1 = VALUES(col1), 
    col2 = VALUES(col2), 
    col3 = VALUES(col3); 

为了更新非常大的数据量，比如两百万行，试着用几千行将你的数据分成几个查询。

还要注意优化技巧：Optimizing INSERT Statements和Bulk Data Loading for InnoDB Tables