有时候我们会为了改善性能,不使用web框架的ORM改为用SQL来批量插入或更新数据,然而当数据量大到一定程度后并且你的条件是跨表的,即便是裸SQL更新也要花上不少的时间和资源,这时候可以通过数据层面的循环来分页更新数据

比如你有一张表叫users,还有一张表叫做user_measurements,外键是user_id, 然后你想批量把user_measurements里面的某个字段更新到users里面,打个比方叫t_id,正常情况下你会这么做

update users
set t_id = user_measurements.t_id
from user_measurements 

由于生产环境可能时时刻刻都在读写这两张表,等锁的时间可能是及其漫长的,因此我们需要改为分页更新,但是limit在更新语句中是没法直接使用的,所以SQL如下

DO $$
    declare
        i integer;
    BEGIN
    LOOP
        EXIT when i = 0;
        raise notice 'Value: %', i;
        update users
        set t_id = tmp_users.um_tid
        from (
          select  users.id, user_measurements.t_id  as um_tid
          from users
          inner join user_measurements on user_measurements.user_id = users.id
          where users.t_id is null
          limit 1000
        )  tmp_users
        where users.id = tmp_users.id

        GET DIAGNOSTICS i = ROW_COUNT;
    END LOOP;
    END;
$$;

实测数据量40w左右,这样更新会比直接更新快数倍

接下来是关于这句SQL的一些注解

DO $$
...
$$

这个表示执行一段匿名的代码块,也可以理解为是一个执行一次的匿名函数

declare 
  i integer

declare表示要声明变量,在上面的例子里我们声明了一个整数型变量i,这个是用来判断,当每1000条数据更新成功的数据为0时,就停止这个循环

LOOP
EXIT when i = 0;
...
END LOOP

这个表示一开始一段循环,EXIT when ...;表示终止循环的条件

...
GET DIAGNOSTICS i = ROW_COUNT;

这个表示在更新完1000条数据后,将这次更新成功的数量赋值给i,这样就能判断是否退出循环了