lamda postgresql mysql 联合查询_跨数据库查询：MySQL inner join PostgreSQL inner join ......

昨天重温`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`的用户名和收藏中所有的链接，通常需要分两步：

1. 在`users`中查询`id`为`1`的记录；

2. 在`favorites`中查询`user_id`的链接。

利用`linked table`，解决方案是：

1. 先创建`users`表的映射，指定使用的`JDBC`驱动、地址、用户名、密码和表名。

2. 再创建`favorites`表的映射，参数与#1相同。

3. 通过`inner join`查询获得指定用户的名字与收藏夹中的链接。

```sql

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`为关键字查询，此时就无法预测与`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`的方案：

```sql

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倍！

结论

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