怎么实现数据库分区表+dblink异步调用并行

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

分区表 + dblink 异步调用 并行

1、创建分区表

create table t_img (id int primary key, sig signature) partition by hash (id);

2、创建64个分区

do language plpgsql $$  declare    i int;  begin    for i in 0..63     loop      execute format('create table t_img%s partition of t_img for values WITH (MODULUS 64, REMAINDER %s)', i, i);     end loop;  end;  $$;

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

create index idx_t_img_1 on t_img using gist(sig);

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

vi test.sql  \set id random(1,2000000000)  insert into t_img values (:id, gen_rand_img_sig(10)) on conflict(id) do nothing;

pgbench -M prepared -n -r -P 1 -f ./test.sql -c 64 -j 64 -t 10000000

dblink 异步调用封装

1、创建dblink插件

create extension if not exists dblink;

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

create or replace function conn(          name,   -- dblink名字          text    -- 连接串,URL        ) returns void as $$          declare          begin            perform dblink_connect($1, $2);           return;          exception when others then            return;          end;          $$ language plpgsql strict;

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

create or replace function parallel_img_search(    v_mod int,   -- 分区数    v_sig signature,  -- 图像特征值    conn text default format('hostaddr=%s port=%s user=%s dbname=%s application_name=', '127.0.0.1', current_setting('port'), current_user, current_database())  -- dblink连接  )  returns setof record as  $$  declare    app_prefix text := 'abc';       sql text;    ts1 timestamp;  begin    for i in 0..v_mod loop      perform conn(app_prefix||i,  conn||app_prefix||i);       perform id,sig from dblink_get_result(app_prefix||i, false) as t(id int, sig signature);       sql := format('select * from t_img%s order by sig <-> %L limit 1', i, v_sig);      perform dblink_send_query(app_prefix||i, sql);       end loop;      ts1 := clock_timestamp();    for i in 0..v_mod loop      return query select id,sig from dblink_get_result(app_prefix||i, false) as t(id int, sig signature);      end loop;    raise notice '%', clock_timestamp()-ts1;        return;  end;  $$ language plpgsql strict;

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

create or replace function get_rand_img_sig(int) returns signature as $$    select ('('||rtrim(ltrim(array(select (random()*$1)::float4 from generate_series(1,16))::text,'{'),'}')||')')::signature;  $$ language sql strict stable;

例子

postgres=# select get_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)  (1 row)    Time: 0.345 ms

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

postgres=# select count(*) from t_img;     count     -----------   297915819  (1 row)

使用dblink异步调用并行查询64个分区

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

postgres=# select * from  parallel_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) as t (id int, sig signature) order by 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 limit 1;      NOTICE:  00:00:00.394257       id     |                                                                               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)  (1 row)    Time: 741.161 ms

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

postgres=# explain (analyze,verbose,timing,costs,buffers) select sig from t_img48 order by 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)' limit 1;                                                                                                      QUERY PLAN                                                                                                       ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------   Limit  (cost=0.36..0.37 rows=1 width=72) (actual time=231.287..231.288 rows=1 loops=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: shared hit=11881     ->  Index Scan using t_img48_sig_idx on public.t_img48  (cost=0.36..41619.32 rows=4466603 width=72) (actual time=231.285..231.285 rows=1 loops=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)           Order By: (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: shared hit=11881   Planning Time: 0.060 ms   Execution Time: 237.818 ms  (9 rows)    Time: 238.242 ms

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