跨数据库查询：MySQL inner join PostgreSQL inner join ...

最新推荐文章于 2022-10-08 22:11:01 发布

??yy

最新推荐文章于 2022-10-08 22:11:01 发布

阅读量295

点赞数

文章标签：数据库 python

原文链接：https://my.oschina.net/redraiment/blog/613078

版权

2019独角兽企业重金招聘Python工程师标准>>>

昨天重温h2 database的文档时，看到一个一直被我无视的命令create linked table！仔细研究后发现这绝对是一个NB的功能：可实现跨不同类型数据库的连接查询！

按照官方文档的介绍，create linked table可以创建一张表，链接到任何支持JDBC的外部数据库中的表。执行简单查询（无join语句）时，会自动将查询语句发送给外部数据库；如果有join语句，这查询语句会被自动翻译成相应的简单查询语句，再发送给外部数据库。

分库

例如，MySQL中有用户表users (id, name)，PostgreSQL中有收藏夹表favorites (id, user_id, name, link)。要求查询获得id为1的用户名和收藏中所有的链接，通常需要分两步：

在users中查询id为1的记录；
在favorites中查询user_id的链接。

利用linked table，解决方案是：

先创建users表的映射，指定使用的JDBC驱动、地址、用户名、密码和表名。
再创建favorites表的映射，参数与#1相同。
通过inner join查询获得指定用户的名字与收藏夹中的链接。

create linked table users ('com.mysql.jdbc.Driver', 'jdbc:mysql://host/database_name', 'username', 'password', 'users');
create linked table favorites ('org.postgresql.Driver', 'jdbc:postgresql://host/database_name', 'username', 'password', 'favorites');
select users.name, favorites.link from favorites inner join users on favorites.user_id = users.id where user.id = 1;

原理

根据官网的介绍，我YY了一下linked table底层的实现原理，应该是先把查询翻译成简单查询语句后，再从远程数据库获取数据并存入临时表中，在本地做join等相关的处理后，最后将结果返回给调用者。因此上述的查询会被拆分成：

select id, name from users where id = 1
select user_id, link from favorites

并分别发送到相应的外部数据库上执行。

不足

从上述的原理可以看出这种解决方案的一个不足之处：favorites表的查询语句没有带上条件语句，即全表查询。因为自动生成简单查询语句时，只能从原始语句中提取每张表自己相关的条件语句，如果改用name为关键字查询，此时就无法预测与favorites表join时user_id的范围。

此外limit或top这种分页语句，在包含join的语句中也无法预测最终被选中的是哪几条数据，因此也不会发送给外部数据库。例如，select * from favorites limit 1，服务器上真正执行的是select * from favroites，limit 1这个操作是在h2的内存中实现的，如果远程favorites表非常大，这条语句执行会非常慢，甚至Out of Memory。

对于上例，解决方法是在原始语句中为favorites也添加相应的过滤条件：select users.name, favorites.link from favorites inner join users on favorites.user_id = users.id where user.id = 1 and favorites.user_id = 1。但这显然不是万能的，只能在所有表都确定范围的情况下才能使用。

分表

遗留系统中还会遇到许多采用物理分表设计的数据库，例如把users拆分成users_00、users_01等一百张表，而不是使用分区，查询前不得不先动态计算表名。

在尝试了合并分库后，我又测试了通过view合并拆分的linked table的方案：

create linked table users_00 ('com.mysql.jdbc.Driver', 'jdbc:mysql://host/database_name', 'username', 'password', 'users_00');
create linked table users_99 ('com.mysql.jdbc.Driver', 'jdbc:mysql://host/database_name', 'username', 'password', 'users_01');
...
create linked table users_99 ('com.mysql.jdbc.Driver', 'jdbc:mysql://host/database_name', 'username', 'password', 'users_99');

create view users (id, name) as
  select id, name from users_00
union all
  select id, name from users_01
...
union all
  select id, name from users_99;

我在线上条了一个分100张的表，每张表的数据量约200万行。测试了一条比较复杂的查询，结果是直接使用union all在原始库中查询，执行时间月130ms；通过h2的view查询，约330ms。性能仅相差2-3倍！