KingabseES kingbase_fdw 跨库关联查询
背景
我们在做综合应用项目的时候,通常会面临客户的每个应用系统使用各自的数据库,或者存放在不同的服务器。查询报表可能使用多个应用数据,这样就需要跨库读取数据表或视图。
KINGBASE_FDW 是一种外部访问接口,它可以被用来访问存储在外部的数据。想要使用fdw访问数据需要先确保:网络通,数据库访问配置(sys_hba.conf)正常,同时远端数据库的用户必须有表的相关权限。
简述KINGBASE_FDW
-
创建扩展 kingbase_fdw
create extension kingbase_fdw;
-
创建远程Server
create server srv_test foreign data wrapper kingbase_fdw options(host '127.0.0.1',port '54321',dbname 'test');
-
创建User maping
create user mapping for kingbase server srv_test options (user 'kingbase', password '123456');
-
创建外部表
create foreign table public.test_tab01 ( id integer , c1 text ) server srv_test options (schema_name 'public', table_name 'tab01');
-
测试外部表
- 读取全部数据
explain analyze select * from public.test_tab01; Foreign Scan on test_tab01 (cost=100.00..150.95 rows=1365 width=36) (actual time=0.230..509.699 rows=1000000 loops=1) Planning Time: 0.041 ms Execution Time: 532.775 ms
- 支持索引
explain analyze select * from public.test_tab01 where id = 1000 ; Foreign Scan on test_tab01 (cost=100.00..150.95 rows=1365 width=36) (actual time=0.230..0.440 rows=1 loops=1) Planning Time: 0.041 ms Execution Time: 0.485 ms
本地与外部表的关联查询
-
本地表与外部表的关联查询
首先将外部表的数据缓存到本地,然后与本地表进行hash join。
explain analyze select * from test02 a join public.tab01_db b on a.id = b.id where a.id <= 1000; Hash Join (cost=153.85..40186.87 rows=1081 width=37) (actual time=0.421..568.423 rows=1000 loops=1) Hash Cond: (b.id = a.id) -> Foreign Scan on tab01_db b (cost=100.00..37508.00 rows=1000000 width=25) (actual time=0.211..512.571 rows=1000000 loops=1) -> Hash (cost=40.34..40.34 rows=1081 width=12) (actual time=0.202..0.203 rows=1000 loops=1) Buckets: 2048 Batches: 1 Memory Usage: 59kB -> Index Scan using test02_pkey on test02 a (cost=0.42..40.34 rows=1081 width=12) (actual time=0.011..0.120 rows=1000 loops=1) Index Cond: (id <= 1000) Planning Time: 0.168 ms Execution Time: 568.608 ms
-
使用lateral改写查询
Nested Loop 虽然没有读取外部表的全部数据,但多次通过session读取外部表,使得执行时间较长。如果loops数值更大,则执行时间远超整体读取数据方式。explain analyze select * from test02 a join lateral ( select * from public.tab01_db b where a.id = b.id limit all) b on true where a.id <= 1000; Nested Loop (cost=100.42..21628731.58 rows=1081 width=37) (actual time=0.224..102.295 rows=1000 loops=1) -> Index Scan using test02_pkey on test02 a (cost=0.42..40.34 rows=1081 width=12) (actual time=0.012..0.354 rows=1000 loops=1) Index Cond: (id <= 1000) -> Foreign Scan on tab01_db b (cost=100.00..20008.02 rows=1 width=25) (actual time=0.084..0.084 rows=1 loops=1000) Planning Time: 0.138 ms Execution Time: 102.522 ms
-
利用数组改写查询
利用数组,可以一次性读取所需外部表的少量数据,即避免读取多余数据量,又防止多次建立session而产生的执行时长。由于是通过session读取外部表数据,cpu_tuple_cost默认值0.01会影响执行计划的正确性,建议设置cpu_tuple_cost值0.2 以上。explain analyze with a as (select * from test02 where id <= 1000), ids as ( select array(select id from a) ids), b as ( select b.* from tab01_db b, ids where id = any (ids)) select /*+set(cpu_tuple_cost 0.2)*/ * from a, b where a.id = b.id; Hash Join (cost=813.27..220724.94 rows=1 width=37) (actual time=1.045..2.281 rows=1000 loops=1) Hash Cond: (b.id = a.id) CTE a -> Index Scan using test02_pkey on test02 (cost=0.42..40.34 rows=1081 width=12) (actual time=0.011..0.122 rows=1000 loops=1) Index Cond: (id <= 1000) InitPlan 2 (returns $1) -> CTE Scan on a a_1 (cost=0.00..21.62 rows=1081 width=4) (actual time=0.013..0.246 rows=1000 loops=1) -> Foreign Scan on tab01_db b (cost=100.00..220010.10 rows=10 width=25) (actual time=0.953..2.048 rows=1000 loops=1) -> Hash (cost=432.40..432.40 rows=1081 width=12) (actual time=0.086..0.086 rows=1000 loops=1) Buckets: 2048 Batches: 1 Memory Usage: 59kB -> CTE Scan on a (cost=0.00..432.40 rows=1081 width=12) (actual time=0.000..0.037 rows=1000 loops=1) Planning Time: 0.192 ms Execution Time: 2.471 ms
总结
外部表在查询中,属于“黑盒”,所以必须通过lateral、CTE或子查询,使得外部表可以接受过滤条件。
综合考虑查询脚本的复杂程度,建议需要外部表数据时,极少量则使用lateral,少于30%使用数组方式,多于30%使用整体方式。
KINGBASE研究院