分块更新 MySQL 表

2022-01-17 00:00:00 sql-update locking mysql

我正在尝试使用 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//

相关文章