数据库快速更新全表数据

备注:以下更新的算法适合所有数据库,示例采用PostgreSQL,其它数据库只用修改语法即可.

题外话:软件思想很重要,不要局限于某种语言\工具\数据库,思想才是最重要的.有时候一个灵光一闪的想法说不定就是一个好的软件.

开始谈正事,在update全表数据时,常规写法下大家经常抱怨更新太慢,语句如下:

update 表 set 字段1=0,字段n=n

实际上这里由于在update前,数据库需要读取整个表,然后才开始update,所以上面的语句可以分成两步看

select * from 表update 表 set 字段1=select.字段,字段n=select.字段n where 主键=select.主键

select是一个全表扫描,update慢的原因主要就是这里了.
知道了问题所在,现在要想办法避开全表扫描,使用PRIMARY KEY扫描,并且不一次更新完,而是分批次更新,直到全表数据更新完成.

示例采用PostgreSQL数据库

1.创建测试表

drop table if exists test;create table test(    objectid bigint not null,       name text not null,     describe jsonb,    flag integer default(2) not null, /*演示要更新的字段*/    constraint pk_test_objectid primary key (objectid) with (fillfactor=60)) with (fillfactor=60);

经常更新的表建议设置fillfactor系数以提升Update和delete性能,默认为90.

2.创建随机生成中文字符函数

drop function if exists gen_random_zh(int,int);create or replace function gen_random_zh(int,int)    returns textas $$select string_agg(chr((random()*(20901-19968)+19968 )::integer) , '')  from generate_series(1,(random()*($2-$1)+$1)::integer);$$ language sql;

注意:这个函数是sql语言函数

3.导入数据函数

--$1为起始编号,每调用一次写入1000000条记录drop function if exists ins_test(bigint);create or replace function ins_test(bigint)  returns voidas $$    declare     begin        for i in 1..1000 loop            insert into test(objectid,name)                select(                     (id + (i-1)*1000)                     + ($1 - 1)            )as objectid,gen_random_zh(8,32) as name from generate_series(1,1000) as id;            --raise notice  '%', i;        end loop;    end;$$ language plpgsql;

注意:这个函数是plpgsql语言函数

4.导入1千万数据

导入1000万测试数据,为提高效率,开10个进程导入,每个写入100万.
根据机器配置不同,每次执行时间约20秒至几分钟不等.

select ins_test(1);select ins_test(1000001);select ins_test(2000001);select ins_test(3000001);select ins_test(4000001);select ins_test(5000001);select ins_test(6000001);select ins_test(7000001);select ins_test(8000001);select ins_test(9000001);

为节约时间全部导入完成后用以下命令统计表信息

analyze test;

5.更新数据

5.1常规方法

没有测试,肯定很慢

update test set flag=1;analyze test;

5.2分批层逐条更新

do $$declare v_currentid bigint;v_rec record;v_index integer;v_isok boolean;beginv_currentid := 0; v_index := 0;loopv_isok := false;v_index := v_index + 1;for v_rec in select objectid from test where objectid>v_currentid order by objectid limit 1000 loopupdate test set flag=1 where objectid=v_rec.objectid;v_isok:=true;v_currentid := v_rec.objectid;end loop;--raise notice  '%,%', v_index,v_currentid;if( false = v_isok ) thenreturn;end if;end loop;end;$$;--Time: 320023.745 msanalyze test;

6.其它

更加复杂的方法是多次执行分批次更新,就如同第3节中的方法一样.
如果你有更好的方法请发邮件给我[e-mail]
如果你要监控过程,请删除raise之前的注释.

请记住:大数据处理分片处理是最基本的思想,再此之上结合需求和算法从而高效的完成工作任务.