跨库/跨实例 Join 连接 不是非得依赖中间件

白菜说技术
4,386 阅读7分钟

白菜Java自习室 涵盖核心知识

分库分表难题（一） 分表分页/跨库分页 难玩却不代表没有玩法
分库分表难题（二） 跨库/跨实例 Join 连接 不是非得依赖中间件

1. 数据垂直切分

某电商公司原先将会员、订单、商品等数据都存放在一个数据库实例中，但业务发展迅猛，访问量极速增长，导致数据库容量及性能遭遇瓶颈，因此 用户决定对架构进行垂直拆分，将会员、商品、订单数据垂直拆分至三个数据库实例中。此时业务上需要展示某个品类商品的售卖订单量，原本在同一数据库里的查询，要变成跨两个数据库实例的查询。业务上要怎么进行关联查询？

ea1f7ddcac171049cc8a4ef9ad0a533d11a797dd.png

跨库 Join 需求

用户首先想到的方法是，对现有业务代码进行重构，分别从两个数据库查询数据，然后在业务代码中进行 join 关联。那么问题来了，如果采用这个解决方案，业务上那么多查询改造起来，拆分难度极大，操作起来过于复杂。跨库 join 操作又没有非常高效的办法，需要从各个业务库迭代查询，查询效率也会有一定影响。

当方案被否决后，用户再次想到的方法是，要不 使用 Mycat，Sharding-jdbc 等数据库中间件来实现，当然这本身是一个可行的方案，而且也是不错的解决方案。但是如果公司系统情况复杂，改变架构困难，代码腐化严重，短时间内根本无法解决这个问题，脱离这些数据库中间件是不是就束手无策了呢？

2. MySQL FEDERATED 引擎原理

src=http___seo-1255598498.file.myqcloud.com_full_a52699110a0d49991a1408e8b507d1f2f3709586.jpg&refer=http___seo-1255598498.file.myqcloud.jfif

如图，FEDERATED 引擎的原理为：

  1. 远程服务器开启 FEDERATED 引擎支持；
  2. 本地服务器查询 FEDERATED 引擎表时，会发送查询语句到远程服务器；
  3. 远程服务器通过传过来的查询语句，查询出结果，返回给本地调用服务器；

3. MySQL 开启 FEDERATED 引擎

MySQL 的前置安装过程这里就省略了，版本是推荐 MySQL5.7。

Docker 方式安装过程可参考作者文章： Canal 解决 MySQL 和 Redis 数据同步问题

2.1. 执行命令：SHOW ENGINES;

查看 mysql 数据库中 Federated 引擎是否开启。下图所示，为未开启。

SHOW ENGINES;

image.png

2.2 修改 MySQL 配置文件，重启服务

修改 mysql 文件夹根目录的 my.ini 文件（Linux 系统修改 my.cnf 文件）：新添加一行，内容为 federated，如下图所示：（Docker 方式安装只需要修改外置配置文件后重启容器即可，阿里云、华为云 DMS 可自行寻找修改配置方式，甚至于自带跨库Link功能）

federated

docker restart (容器ID)

image.png

2.3. 再次执行命令：SHOW ENGINES;

检查 mysql 数据库中 Federated 引擎是否开启，现在我们发现已经是开启状态了。

SHOW ENGINES;

image.png

2.4. 新建两个测试用的数据库，数据库表

这里读者自行去建数据库，自由发挥：

image.png

然后 在两个数据库里分别新建两张表 prd_sku（SKU信息表）和 prd_sku_stock（SKU库存表）：

CREATE TABLE `prd_sku`  (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(180) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT 'SKU名称',
  `code` varchar(180) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT 'SKU编码',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;

CREATE TABLE `prd_sku_stock`  (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `sku_id` int(11) NOT NULL DEFAULT 0 COMMENT 'SKU的ID',
  `stock` int(11) NOT NULL DEFAULT 0 COMMENT 'SKU的库存',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;

image.png

1625714588(1).jpg

我们先在表里边添加点数据进去：

INSERT INTO `prd_sku` VALUES (1, '黄色S码', 'Y00S00');
INSERT INTO `prd_sku` VALUES (2, '红色M码', 'R00M00');

INSERT INTO `prd_sku_stock` VALUES (1, 1, 96);
INSERT INTO `prd_sku_stock` VALUES (2, 2, 98);

image.png

1625722939(1).jpg

2.5. 在数据库 A 中建立远程表连接数据库 B

CREATE TABLE `mycat_db1`.`prd_sku_stock`  (
  `id` int(0) NOT NULL AUTO_INCREMENT,
  `sku_id` int(0) NOT NULL,
  `stock` int(0) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE = FEDERATED CONNECTION = 'mysql://root:88021120@localhost:3306/mycat_db2/prd_sku_stock';
  • root:88021120 远程数据库的账号和密码
  • localhost:3306 远程数据库的ip和端口
  • mycat_db2 远程数据库的名称
  • prd_sku_stock 远程数据库的表名称

image.png

这里有几个特别需要注意的点：

  1. 连接表的字段必须和真实表一致，允许是子集；
  2. 真实表数据更新连接表自动会更新，反之亦然；
  3. 真实表结构改动，连接表不会跟着改动；
  4. 删除连接表，是真实表不会被跟着删除；
  5. 连接表无法用 ALTER 语句改结构，所以如果想同步需求可以删除重新建；

2.6. 在数据库 A 中执行 join 查询，查看结果

SELECT * FROM prd_sku t1, prd_sku_stock t2 WHERE t1.id=t2.sku_id;

1625724361(1).jpg

3. 阿里云 DMS 跨实例查询服务

阿里云 DMS（数据管理）跨实例查询服务，不仅覆盖了异构数据源关联查询的场景，还解决了跨 region、跨云的数据库关联查询的难题。不仅如此，还对查询性能进行了大幅优化，使得大部分查询能在毫秒级完成。用户无需通过数据汇集，即可通过标准的 SQL 实现跨实例的交叉查询。

9a753e8b1cfea42ac10d0f998fede02bd3160bd1.png

DBLink

熟悉 Oracle 的人应该知道，我们可以在当前登录的 Oracle 上，建立一个 DBLink 指向另一个远程的Oracle 数据库表。在跨实例查询服务中，重新定义了 DBLink 的概念，它是一个指向用户的任意数据库实例的虚拟连接，是数据库实例的别名。例如，对于 MySQL 来说，DBLink 和 ip/port 一一对应。借助 DBLink，即可实现对任意数据源的 SQL 访问。

5195fb64ba5d07fc26656b4276350f91e59bee3b-.png

兼容标准 SQL

通过标准的 SQL 语句，即可实现跨实例查询。同时跨实例查询服务高度兼容 MySQL，支持 MySQL 协议，以及各种常用函数和语法。您可通过 JDBC/ODBC 驱动连接到跨实例查询服务；也可以使用各种 MySQL GUI 工具来管理各种数据源。

Serverless 架构

跨实例查询是无服务器化的在线数据库关联查询服务。用户无需预购计算资源、无需维护资源、没有运维和升级成本，随时随地使用。

高性能低延迟

跨实例查询服务底层基于强大的 MPP 计算引擎，持续不断地对 SQL 查询进行优化，包括 pushdown、join 算法、执行计划缓存、Meta 缓存、本地调度、连接池等技术。目前单表查询以及跨实例的多表关联查询，都能在毫秒级完成。

支持多种关系型数据库

目前已支持 MySQL、SQLServer、PostgreSQL 等多种关系型数据库。

支持 SQL 方式访问 NoSQL

除了关系型数据库之外，跨实例查询还支持以 SQL 方式访问 Redis 等 NoSQL 数据库。由于支持了 SQL 语法，也可以实现 RDBMS 和 NoSQL 之间关联查询。是的，你没看错，一条 SQL 就能实现 MySQL 和 Redis 之间的关联查询。

b194fc291eb55a2c2ae56393f472e38c536cb1dd-.png

支持跨地域以及混合云查询

企业发展到一定阶段，用户量、业务量不断攀升，原来的单机房容量已经不能满足业务发展的需求，再结合容灾、高可用等因素，通常会选择跨 region 部署，也叫单元化部署。同时，不少企业也需要将业务拓展到海外，通过本地就近部署，为国外用户提供更好的体验。类似这种水平拆分带来的问题就是，如何对全局的业务数据进行统一的汇总关联查询。

借助 DMS 跨实例查询服务，无论您的数据库实例部署在阿里云的哪个 region，无需跨 region 的数据迁移，即可实现所有 region 数据的统一查询。除了阿里云 RDS，也支持用户部署在阿里云 ECS 上的各种数据库。不仅如此，如果您的数据库部署在本地 IDC 机房，甚至其他云厂商，都可以通过跨实例查询服务，实现这些混合云场景的跨实例关联查询。

df2dbc3dfecf734a1f1090f6f801eda649a3c5f4-.png

分库分表难题（一） 分表分页/跨库分页 难玩却不代表没有玩法
分库分表难题（二） 跨库/跨实例 Join 连接 不是非得依赖中间件

avatar
文章
阅读
粉丝