本篇内容介绍了“怎么实现数据库分区表+dblink异步调用并行”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！

1、创建分区表

createtablet_img(idintprimarykey,sigsignature)partitionbyhash(id);

2、创建64个分区

dolanguageplpgsql$$declareiint;beginforiin0..63loopexecuteformat('createtablet_img%spartitionoft_imgforvaluesWITH(MODULUS64,REMAINDER%s)',i,i);endloop;end;$$;

3、创建图像特征值字段索引

createindexidx_t_img_1ont_imgusinggist(sig);

4、写入4亿随机图像特征值

vitest.sql\setidrandom(1,2000000000)insertintot_imgvalues(:id,gen_rand_img_sig(10))onconflict(id)donothing;

pgbench-Mprepared-n-r-P1-f./test.sql-c64-j64-t10000000dblink 异步调用封装

1、创建dblink插件

createextensionifnotexistsdblink;

2、创建一个建立连接函数，不报错

createorreplacefunctionconn(name,--dblink名字text--连接串,URL)returnsvoidas$$declarebeginperformdblink_connect($1,$2);return;exceptionwhenothersthenreturn;end;$$languageplpgsqlstrict;

3、编写一个函数，输入参数为分区数，图像特征值。开启64个并行同时搜索每个分区，返回一条最相似的图像记录。

createorreplacefunctionparallel_img_search(v_modint,--分区数v_sigsignature,--图像特征值conntextdefaultformat('hostaddr=%sport=%suser=%sdbname=%sapplication_name=','127.0.0.1',current_setting('port'),current_user,current_database())--dblink连接)returnssetofrecordas$$declareapp_prefixtext:='abc';sqltext;ts1timestamp;beginforiin0..v_modloopperformconn(app_prefix||i,conn||app_prefix||i);performid,sigfromdblink_get_result(app_prefix||i,false)ast(idint,sigsignature);sql:=format('select*fromt_img%sorderbysig<->%Llimit1',i,v_sig);performdblink_send_query(app_prefix||i,sql);endloop;ts1:=clock_timestamp();foriin0..v_modloopreturnqueryselectid,sigfromdblink_get_result(app_prefix||i,false)ast(idint,sigsignature);endloop;raisenotice'%',clock_timestamp()-ts1;return;end;$$languageplpgsqlstrict;

4、创建一个stable函数，用于生成随机图像特征值。

createorreplacefunctionget_rand_img_sig(int)returnssignatureas$$select('('||rtrim(ltrim(array(select(random()*$1)::float4fromgenerate_series(1,16))::text,'{'),'}')||')')::signature;$$languagesqlstrictstable;

例子

postgres=#selectget_rand_img_sig(10);get_rand_img_sig------------------------------------------------------------------------------------------------------------------------------------------------------------------(3.970030,2.340900,0.946223,5.951010,6.560340,7.922950,6.646290,0.430310,7.690120,5.799870,1.337850,1.319830,3.178170,6.439380,0.925341,2.215810)(1row)Time:0.345ms

5、写入约2.98亿图像特征值。

postgres=#selectcount(*)fromt_img;count-----------297915819(1row)使用dblink异步调用并行查询64个分区

使用dblink异步调用接口，查询所有分区，耗时：394毫秒

postgres=#select*fromparallel_img_search(63,'(3.970030,2.340900,0.946223,5.951010,6.560340,7.922950,6.646290,0.430310,7.690120,5.799870,1.337850,1.319830,3.178170,6.439380,0.925341,2.215810)'::signature)ast(idint,sigsignature)orderbysig<->'(3.970030,2.340900,0.946223,5.951010,6.560340,7.922950,6.646290,0.430310,7.690120,5.799870,1.337850,1.319830,3.178170,6.439380,0.925341,2.215810)'::signaturelimit1;NOTICE:00:00:00.394257id|sig------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------1918283556|(3.122560,2.748080,1.133250,5.426950,6.626340,6.876810,7.959190,0.798523,8.638600,5.075110,1.366100,0.899454,2.980070,4.580630,0.986704,1.582110)(1row)Time:741.161ms

直接查询单个分区耗时：238毫秒

postgres=#explain(analyze,verbose,timing,costs,buffers)selectsigfromt_img48orderbysig<->'(3.970030,2.340900,0.946223,5.951010,6.560340,7.922950,6.646290,0.430310,7.690120,5.799870,1.337850,1.319830,3.178170,6.439380,0.925341,2.215810)'limit1;QUERYPLAN----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Limit(cost=0.36..0.37rows=1width=72)(actualtime=231.287..231.288rows=1loops=1)Output:id,sig,((sig<->'(3.970030,2.340900,0.946223,5.951010,6.560340,7.922950,6.646290,0.430310,7.690120,5.799870,1.337850,1.319830,3.178170,6.439380,0.925341,2.215810)'::signature))Buffers:sharedhit=11881->IndexScanusingt_img48_sig_idxonpublic.t_img48(cost=0.36..41619.32rows=4466603width=72)(actualtime=231.285..231.285rows=1loops=1)Output:id,sig,(sig<->'(3.970030,2.340900,0.946223,5.951010,6.560340,7.922950,6.646290,0.430310,7.690120,5.799870,1.337850,1.319830,3.178170,6.439380,0.925341,2.215810)'::signature)OrderBy:(t_img48.sig<->'(3.970030,2.340900,0.946223,5.951010,6.560340,7.922950,6.646290,0.430310,7.690120,5.799870,1.337850,1.319830,3.178170,6.439380,0.925341,2.215810)'::signature)Buffers:sharedhit=11881PlanningTime:0.060msExecutionTime:237.818ms(9rows)Time:238.242ms

“怎么实现数据库分区表+dblink异步调用并行”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注亿速云网站，小编将为大家输出更多高质量的实用文章！