批量更新MySQL表 [英] Update MySQL table in chunks

问题描述

我正在尝试使用c更新MySQL InnoDB表. 1亿行.查询需要将近一个小时，这是没有问题的.

I am trying to update a MySQL InnoDB table with c. 100 million rows. The query takes close to an hour, which is not a problem.

但是，我想将此更新分成较小的块，以便不阻止对表的访问.此更新不必是孤立的事务.

However, I'd like to split this update into smaller chunks in order not to block table access. This update does not have to be an isolated transaction.

同时，就额外的开销而言，拆分更新不应太昂贵.

At the same time, the splitting of the update should not be too expensive in terms of additional overhead.

我考虑使用 在过程中遍历表:

I considered looping through the table in a procedure using :

UPDATE TABLENAME SET NEWVAR=<expression> LIMIT batchsize, offset,

但是UPDATE在MySQL中没有偏移选项.

But UPDATE does not have an offset option in MySQL.

我知道我可以尝试更新在键上选择的数据范围以及LIMIT选项，但这对于该简单任务而言似乎相当复杂.

I understand I could try to UPDATE ranges of data that are SELECTed on a key, together with the LIMIT option, but that seems rather complicated for that simple task.

推荐答案

我结束了下面列出的过程.它可以工作，但是我不确定所有查询来识别连续范围是否有效.可以使用以下参数(示例)来调用它:

I ended up with the procedure listed below. It works but I am not sure whether it is efficient with all the queries to identify consecutive ranges. It can be called with the following arguments (example):

call chunkUpdate('SET var=0','someTable','theKey',500000);

基本上，第一个参数是更新命令(例如"set x = ..."之类的命令)，其后是mysql表名，其后是必须唯一的数字(整数)键，然后是要处理的块的大小.密钥应具有合理性能的索引.可以删除下面代码中的"n"变量和"select"语句，仅用于调试.

Basically, the first argument is the update command (e.g. something like "set x = ..."), followed by the mysql table name, followed by a numeric (integer) key that has to be unique, followed by the size of the chunks to be processed. The key should have an index for reasonable performance. The "n" variable and the "select" statements in the code below can be removed and are only for debugging.

delimiter //
CREATE PROCEDURE chunkUpdate (IN cmd VARCHAR(255), IN tab VARCHAR(255), IN ky VARCHAR(255),IN sz INT)
BEGIN
  SET @sqlgetmin = CONCAT("SELECT MIN(",ky,")-1 INTO @minkey FROM ",tab); 
  SET @sqlgetmax = CONCAT("SELECT MAX(",ky,") INTO @maxkey FROM ( SELECT ",ky," FROM ",tab," WHERE ",ky,">@minkey ORDER BY ",ky," LIMIT ",sz,") AS TMP"); 
  SET @sqlstatement = CONCAT("UPDATE ",tab," ",cmd," WHERE ",ky,">@minkey AND ",ky,"<=@maxkey");
  SET @n=1;

  PREPARE getmin from @sqlgetmin;
  PREPARE getmax from @sqlgetmax;
  PREPARE statement from @sqlstatement;

  EXECUTE getmin;

  REPEAT
    EXECUTE getmax; 
    SELECT cmd,@n AS step, @minkey AS min, @maxkey AS max;
    EXECUTE statement;
    set @minkey=@maxkey;
    set @n=@n+1;
  UNTIL @maxkey IS NULL
  END REPEAT; 
  select CONCAT(cmd, " EXECUTED IN ",@n," STEPS") AS MESSAGE;
END//

这篇关于批量更新MySQL表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！