Question

微服务架构下，原先单库 join 查询已经不能满足要求，每个拆分的微服务对应一个数据库实例，而且部署在不同的服务器上。一个请求可能需要从多个数据库获取数据，所以要解决跨库查询问题。

跨库查询的方法：

• 库冗余字段：需要定期同步冗余字段数据，不然会产生脏数据。

• 聚合服务封装查询：对外提供一个 API，后台去聚合跨库查询结果。

• 表视图查询：如果涉及到不同数据库表之间的 join 查询，可以在其中某一数据库的表上建立视图（view）关系。但要求

  • 数据库需要部署在同一台服务器上

  • 数据库账户密码必须相同，也就是在同一个 schema 下

• 多数据源查询：

• 分库分表，使用数据库中间件

表格 各种数据库的跨库查询方式

	MySQL	PostgreSQL	SQLServer
同实例或库	db_name.tbl_name	schema_name.tbl_name	db_name.tbl_name
扩展		dblink、postgres_fdw	opendatasource/openrowset

MySQL 跨库查询

MySQL 跨库查询支持同是 MySQL 的数据库之间进行关联查询，有以下方式，

• 在表名前加上数据库名用于显式指定数据库，例如：db_name.tbl_name
• JOIN 语句
• UNION 语句

MySQL 跨库查询示例

SELECT database1.table1.field1,
       database2.table1.field1
FROM database1.table1,
      database.table1
WHERE database1.table1.age > 12;

PostgreSQL 跨库查询

常见三种方式

• 同一个数据库，不可 schema。可在查询时加上 schema.table_name 。
• dblink 先安装扩展，dblink_connect、JOIN dblink、dblink_disconnect
• postgres_fdw 先安装扩展，CREATE SERVER、CREATE USER MAPPING、CREATE FOREIGN TABLE

dblink 跨库访问：dblink 是 PostgreSQL 的一个模块，支持从数据库会话中连接到其他数据库。

# 安装 dblink 连接: 创建扩展
postgres=# CREATE EXTENSION dblink;
CREATE EXTENSION

# 创建 dblink 连接：dblink_connect 本地连接/远程连接
postgres=# SELECT dblink_connect('remote_dblink_test', 'dbname=remotedb hostaddr=10.9.10.24 port=5432 user=postgres password=postgres');
 dblink_connect


----------------
 OK
(1 row)

# 跨库查询：JOIN dblink
postgres=# SELECT u.id, name, create_time FROM userinfo u JOIN dblink('remote_dblink_test', 'SELECT * FROM remote_test;') AS t(id int, ival int, create_time timestamptz) ON u.id = t.id;
 id | name |          create_time


----+------+-------------------------------
  1 | Eric | 2019-05-09 15:34:42.599409+08
  2 | Tom  | 2019-05-09 15:34:42.599409+08
(2 rows)

# 关闭 dblink 连接：dblink_disconnect
postgres=# SELECT dblink_disconnect('local_dblink_test');
 dblink_disconnect


-------------------
 OK
(1 row)