1. 云图网首页
  2. 技术专区
  3. 数据库

PL/Proxy之二: Dynamic Records

• 数据库

关于 PL/Proxy 搭建参考 PL/Proxy 之一: 使用外部表 SQL/MED搭建 PL/Proxy ， 本文紧接着上篇 blog 介绍 PL/Proxy 使用方面， 由于 PL/Proxy 使用通过函数封装，如果每张表的插入，查询操作都单独写函数太烦琐，这里介绍传递 SQL 的用法。

回顾下环境信息,数据库节点信息

1
2
3
 
proxy  
db0  
db1

备注: proxy 库为 proxy 节点, db0, db1 为数据节点，均在同一台虚拟机的一个 PostgreSQL 实例上。

创建三类函数

1
2
3
 
func_ddl(sql text)  : 传递 ddl 函数  
func_dml(sql text) 传递 dml 函数  
func_query(sql text) 传递  查询sql 函数

备注：当然仅这三个函数不足以包含所有数据库操作。

一 Proxy 节点执行

DDL 函数

1
2
3
4
5
 
CREATE OR REPLACE FUNCTION func_ddl(sql text)  
RETURNS SETOF text AS $$  
CLUSTER 'proxy_srv';  
 RUN ON ALL;  
$$ LANGUAGE plproxy;

DML 函数

1
2
3
4
5
 
CREATE OR REPLACE FUNCTION func_dml(sql text)  
RETURNS SETOF text AS $$  
CLUSTER 'proxy_srv';  
 RUN ON ANY;  
$$ LANGUAGE plproxy;

query 函数

1
2
3
4
5
 
CREATE OR REPLACE FUNCTION func_query(sql text)  
RETURNS SETOF RECORD AS $$  
CLUSTER 'proxy_srv';  
 RUN ON ALL;  
$$ LANGUAGE plproxy;

二 db0,db1 数据节点执行

DDL 函数

1
2
3
4
5
6
7
 
CREATE OR REPLACE FUNCTION func_ddl(sql text)  
RETURNS integer AS $$  
begin  
execute sql;  
return  1;  
end;  
$$ LANGUAGE plpgsql;

DML 函数

1
2
3
4
5
6
7
 
CREATE OR REPLACE FUNCTION func_dml(sql text)  
RETURNS integer AS $$  
begin  
execute sql;  
return  1;  
end;  
$$ LANGUAGE plpgsql;

query 函数

1
2
3
4
5
6
7
8
9
10
11
12
 
CREATE OR REPLACE FUNCTION func_query(sql text)  
RETURNS SETOF RECORD AS $$  
DECLARE  
 rec RECORD;  
BEGIN  
 FOR rec IN EXECUTE sql  
 LOOP  
 RETURN NEXT rec;  
 END LOOP;  
 RETURN;  
END;  
$$ LANGUAGE plpgsql;

三 测试

DDL 函数测试 ，proxy 结点执行

1
2
3
4
5
6
7
8
 
proxy=>  select func_ddl('create table tbl_proxy1(id int4,name text,create_time timestamp(0) without time zone default clock_timestamp())');  
NOTICE: PL/Proxy: dropping stale conn  
NOTICE: PL/Proxy: dropping stale conn  
func_ddl   
----------  
1  
1  
(2 rows

db0,db1 验证

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 
db0=> /dt tbl_proxy1   
 List of relations  
Schema  | Name |  Type |  Owner    
--------+------------+-------+-------  
public  | tbl_proxy1 | table | proxy  
(1 row)  
  
db0=> /c db1  
You are now connected to database "db1"  as user "proxy".  
  
db1=> /dt tbl_proxy1   
 List of relations  
Schema  | Name |  Type |  Owner    
--------+------------+-------+-------  
public  | tbl_proxy1 | table | proxy  
(1 row)

DML 函数测试
proxy 节点

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
 
proxy=>  select func_dml('insert into tbl_proxy1 (id,name) values(1,''a'')');  
func_dml   
----------  
1  
(1 row)  
  
proxy=>  select func_dml('insert into tbl_proxy1 (id,name) values(2,''b'')');  
func_dml   
----------  
1  
(1 row)  
  
proxy=>  select func_dml('insert into tbl_proxy1 (id,name) values(3,''c'')');  
func_dml   
----------  
1  
(1 row)

数据节点验证

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
db1=>  select  *  from tbl_proxy1 ;  
id | name | create_time   
----+------+---------------------  
 1  | a |  2014-09-18  18:45:08  
 2  | b |  2014-09-18  18:45:55  
(2 rows)  
  
db1=> /c db0  
You are now connected to database "db0"  as user "proxy".  
  
db0=>  select  *  from tbl_proxy1 ;  
id | name | create_time   
----+------+---------------------  
 3  | c |  2014-09-18  18:45:58  
(1 row)

query 测试

1
2
3
4
5
6
7
 
proxy=> SELECT * FROM func_query('SELECT * FROM tbl_proxy1;') AS (id integer, name text,create_time timestamp(0) without time zone );  
id | name | create_time   
----+------+---------------------  
 2  | b |  2014-09-18  18:55:13  
 3  | c |  2014-09-18  18:55:17  
 1  | a |  2014-09-18  18:55:07  
(3 rows)

备注：这里需要定义返回列类型.

四 性能分析

我们想插入 10 万数据简单测试这种模式性能，由于 generate_series()函数调用后数据只落在一个数据节点，这里通过写个 shell 脚本插入 10 万数据。

生成 insert 语句的脚本

1
2
3
4
5
6
 
[pg93@db1 script]$ cat insert.sh  
#!/bin/bash  
  
for  ((i=1;i<=100000;i++));  do  
echo "select func_dml('insert into tbl_proxy1(id,name) values(${i},''${i}_test'')');";  
done

备注：插入 10 万数据。

清理 db0,db1 数据

1
2
3
4
5
6
 
proxy=>  select func_ddl('truncate table tbl_proxy1;');  
func_ddl   
----------  
1  
1  
(2 rows)

导入数据

1
 
[pg93@db1 script]$ ./insert.sh | psql proxy proxy > insert.out

数据验证

1
2
3
4
5
6
7
8
9
10
11
12
13
 
db1=>  select count(*)  from tbl_proxy1;  
count   
-------  
50020  
(1 row)  
  
db1=> /c db0  
You are now connected to database "db0"  as user "proxy".  
db0=>  select count(*)  from tbl_proxy1;  
count   
-------  
49980  
(1 row)

备注：数据分布较均匀。

创建索引

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
 
proxy=>  select func_ddl('create unique index uk_proxy_id on tbl_proxy1 using btree (id);');  
func_ddl   
----------  
1  
1  
(2 rows)  
  
db0=> /d tbl_proxy1   
 Table  "public.tbl_proxy1"  
 Column | Type | Modifiers   
-------------+--------------------------------+---------------------------  
id | integer |    
name | text |    
create_time | timestamp(0) without time zone |  default clock_timestamp()  
Indexes:  
 "uk_proxy_id" UNIQUE, btree (id)  
  
db0=> /c db1  
You are now connected to database "db1"  as user "proxy".  

db1=> /d tbl_proxy1   
 Table  "public.tbl_proxy1"  
 Column | Type | Modifiers   
-------------+--------------------------------+---------------------------  
id | integer |    
name | text |    
create_time | timestamp(0) without time zone |  default clock_timestamp()  
Indexes:  
 "uk_proxy_id" UNIQUE, btree (id)

备注： 索引已在数据节点创建。

proxy 节点上查询

1
2
3
4
5
6
 
proxy=> explain analyze SELECT * FROM func_query('SELECT id,name FROM tbl_proxy1 where id=1;') AS (id integer, name text);  
QUERY PLAN   
--------------------------------------------------------------------------------------------------------------  
Function  Scan on func_query (cost=0.25..10.25 rows=1000 width=36)  (actual time=1.803..1.805 rows=1 loops=1)  
Total runtime:  1.847 ms  
(2 rows)

数据节点查询

1
2
3
4
5
6
7
 
db1=> explain analyze SELECT id,name FROM tbl_proxy1 where id=1;  
QUERY PLAN   
-------------------------------------------------------------------------------------------------------------------------  
Index  Scan  using uk_proxy_id on tbl_proxy1 (cost=0.29..4.31 rows=1 width=14)  (actual time=0.032..0.034 rows=1 loops=1)  
 Index  Cond:  (id =  1)  
Total runtime:  0.086 ms  
(3 rows)

备注: 同样的查询， proxy 节点查询需要 1.847 ms, 数据节点查询仅需 0.086 ms，性能差别较大，所以从这个测试来看，传递 SQL 的这三个函数在性能方面会有下降，不建议使用这种传递 SQL 的方式，建议函数调用方式。

五 参考

原创文章，作者：254126420，如若转载，请注明出处：https://blog.ytso.com/239584.html

赞 (0)
0
上一篇 2022年2月12日
下一篇 2022年2月12日

相关推荐

发表回复

登录后才能评论