文档章节

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

redraiment
 redraiment
发布于 2016/02/02 16:37
字数 1082
阅读 521
收藏 7

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

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

分库

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

  1. users中查询id1的记录;
  2. favorites中查询user_id的链接。

利用linked table,解决方案是:

  1. 先创建users表的映射,指定使用的JDBC驱动、地址、用户名、密码和表名。
  2. 再创建favorites表的映射,参数与#1相同。
  3. 通过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等相关的处理后,最后将结果返回给调用者。因此上述的查询会被拆分成:

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

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

不足

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

此外limittop这种分页语句,在包含join的语句中也无法预测最终被选中的是哪几条数据,因此也不会发送给外部数据库。例如,select * from favorites limit 1,服务器上真正执行的是select * from favroiteslimit 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_00users_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;通过h2view查询,约330ms。性能仅相差2-3倍!

结论

经过上述测试,我认为通过h2跨数据库合表查询的方案可用于查询不复杂且性能非重要指标的场景,例如我打算下次把它用在报表系统中~

© 著作权归作者所有

redraiment

redraiment

粉丝 131
博文 23
码字总数 36572
作品 6
杭州
架构师
私信 提问
PostgreSQL 优化器代码概览

简介 PostgreSQL 的开发源自上世纪80年代,它最初是 Michael Stonebraker 等人在美国国防部支持下创建的POSTGRE项目。上世纪末,Andrew Yu 等人在它上面搭建了第一个SQL Parser,这个版本称为...

阿里云云栖社区
02/14
0
0
PostgreSQL 11 小记

关于 PostgreSQL PostgreSQL 是世界上最先进的开源数据库。 PostgreSQL 最早可追溯到 1973 年,当时加州大学伯克利分校的两位科学家,Michael Stonebraker 和 Eugene Wong 受 1970 年 Edgar ...

东明
2018/11/20
0
0
PostgreSQL 10.3, 9.6.8, 9.5.12, 9.4.17 和 9.3.22 发布

PostgreSQL 10.3, 9.6.8, 9.5.12, 9.4.17 和 9.3.22 全系列更新发布。本次更新主要是解决 CVE-2018-1058 漏洞,对该漏洞的描述为,用户可在不同的 schema 中创建 like-named 对象,而这些 sc...

局长
2018/03/02
1K
10
去 IOE,MySQL 完胜 PostgreSQL

前言 上周参加了2015年的中国数据库大会,差不多从第二届开始就每年都会北京参会,从最早的嘉宾到这次的会场主持人,也算见证了中国数据库大会的发展吧。记得最早的时候大会只有两天,分会场...

姜承尧
2015/04/21
80.2K
123
PostgreSQL JOIN limit 优化器 成本计算 改进 - mergejoin startup cost 优化

背景 PostgreSQL limit N的成本估算,是通过计算总成本A,以及估算得到的总记录数B得到: (N/B)*A 大概意思就是占比的方法计算 对于单表查询,这种方法通常来说比较适用,但是如果数据分布有...

pg小助手
2018/10/23
0
0

没有更多内容

加载失败,请刷新页面

加载更多

读书replay《maven实战》.1.20190526

前情提要 maven这个工具用了好久了,但是一直都用的迷迷糊糊的,没有对它进行过系统性的学习,只是知道一些常用的功能怎么实现,所以20190516这一天我从JD购买了徐晓斌老师所著的《maven实战...

wanxiangming
39分钟前
0
0
真实项目案例实战——【状态设计模式】使用场景

什么是状态模式 状态模式允许一个对象在其内部状态改变的时候改变其行为。这个对象看上去就像是改变了它的类一样。 状态模式应用场景 1.一个对象的行为取决于它的状态,并且它必须在运行时刻根...

须臾之余
46分钟前
1
0
Java 实现把字符串转换成整数【底层实现】

https://blog.csdn.net/zl18310999566/article/details/80263396

qimh
49分钟前
0
0
IDEA的debugger

1、win下节省内存空间 3、条件断点

一只小青蛙
今天
3
0
炸!亿级数据DB秒级平滑扩容

一步一步,娓娓道来。 一般来说,并发量大,吞吐量大的互联网分层架构是怎么样的? 数据库上层都有一个微服务,服务层记录“业务库”与“数据库实例配置”的映射关系,通过数据库连接池向数据...

编程SHA
今天
2
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部