最佳实践 | 教你用一条SQL搞定跨数据库查询难题

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
数据管理 DMS,安全协同 3个实例 3个月
推荐场景:
学生管理系统数据库
简介: 数据库拆分后,数据分布到不同的数据库实例,可达到降低数据量,增加实例数的扩容目的。然而前途是美好的,道路是曲折的。一旦涉及拆分,就逃不开“原本在同一数据库里的查询,要变成跨两个数据库实例”的查询问题。

导读

日前,某电商用户由于业务发展迅猛,访问量极速增长,导致数据库容量及性能遭遇瓶颈。为降低数据库大小,提升性能,用户决定对架构进行垂直拆分。根据不同的表来进行拆分,对应用程序的影响也更小,拆分规则也会比较简单清晰。

该用户按照会员、商品、订单,将数据垂直拆分至三个数据库,分库后数据分布到不同的数据库实例,以达到降低数据量,增加实例数的扩容目的。然而前途是美好的,道路是曲折的。一旦涉及拆分,就逃不开“原本在同一数据库里的查询,要变成跨两个数据库实例”的查询问题

单库时,系统中很多列表和详情页所需数据可以简单通过SQL join关联表查询;而拆库后,拆分后的数据可能分布在不同的节点/实例上,不能跨库使用join,此时join带来的问题就很棘手了。

_7_8
例如:业务中需要展示某个品类商品的售卖订单量,现在订单数据和商品数据分布在两个独立的数据库实例中,业务上要怎么进行关联查询?

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

是不是光想想就一个头两个大?别担心,关于数据库拆分后的业务改造难题,其实用一条SQL就可轻松搞定。具体解法如下⬇️

解决思路

经沟通,我们发现用户遇到的其实就是典型的跨数据库实例查询问题。目前,阿里云DMS已经支持跨数据库实例SQL查询的能力,用户可以通过DMS,利用一条SQL即可解决上述难题。不仅能够满足“跨库Join”这一核心诉求,还能极大地简化用户的技术方案。
_9_10
除了开篇介绍的客户案例,DMS跨数据库实例的查询功能可以解决我们业务中遇到的任意跨数据库查询的诉求。例如:跨线上库及历史库的join查询,快速获取全量数据;单元化架构下,join各个单元的数据库查询全局数据;游戏业务,可以join MySQL中的用户数据及MongoDB中的游戏装备数据等。

接下来,我们通过一个快速上手的实例,来看看用户如何写这条SQL。

商品库的信息

实例连接:198.12.13.1:3306 , 数据库名:seller
商品表名:commodity
包含部分字段的表结构:

create table commondity(
id BIGINT(20),  -- 商品ID
name varchar(100), -- 商品名称
create_time TIMESTAMP ,  -- 商品入库时间
catogary BIGINT(30), -- 商品类目
features text,  -- 商品描述
param text); -- 商品属性

订单库的信息

实例连接:198.12.13.2:3306 , 数据库名:buyer
订单表表名:order_list
包含部分字段的表结构:

create table order_list(
id BIGINT(20),  -- 订单ID
buyer_id BIGINT(30), -- 买家ID
create_time TIMESTAMP ,  -- 订单生成时间
seller_id BIGINT(30), -- 卖家ID
commodity_id BIGINT(30), -- 商品ID
status int(8) – 订单状态)

创建DBLink

在编写查询SQL之前,需要先在DMS中配置卖家库和买家库的DBLink。
_3_4

编写并运行跨库查询SQL

当DBLink配置完成后,即可开始在DMS中编写并运行SQL,实现查询某个商品的订单列表的需求。

SELECT comomndity.catogary,
       count(1)
 from buyer_db.buyer.order_list 
order,
      seller_db.seller.commondity commondity
where order.commodity_id= commondity.id
GROUP BY commondity.catogary;

这个SQL的语法完全兼容MySQL,只是在From的表名前面带上DBLink。
所以,业务方只需要使用DMS跨数据库查询SQL便可轻松解决拆库之后的跨库查询难题,业务基本无需改造。

什么是DMS跨数据库查询

SELECT * FROM oracle.dsqltest.b oracle inner join

mysql.dsqltest.a mysql on oracle.id = mysql.id
WHERE oracle.id=1

_5

DMS提供的跨数据库实例查询功能孵化于阿里巴巴集团,目前已服务超过5000名开发者,全面支撑阿里巴巴跨数据库实例的所有线上查询需求。DMS支持跨同异构数据库的在线查询,支持MySQL、SQLServer、PostgreSQL及Redis等数据源,为应用提供了一种数据全局查询的能力。用户无需通过数据汇集,即可通过标准SQL实现跨实例的交叉查询。

立即体验

  1. 请先登录DMS控制台
  2. 从SQL操作中,进入跨实例SQL窗口。
    _6
  3. 参考使用指南,创建DBlink,编写并运行SQL。

了解更多
阿里云数据管理DMS

相关实践学习
MySQL基础-学生管理系统数据库设计
本场景介绍如何使用DMS工具连接RDS,并使用DMS图形化工具创建数据库表。
目录
相关文章
|
2天前
|
SQL NoSQL Java
Java使用sql查询mongodb
通过使用 MongoDB Connector for BI 和 JDBC,开发者可以在 Java 中使用 SQL 语法查询 MongoDB 数据库。这种方法对于熟悉 SQL 的团队非常有帮助,能够快速实现对 MongoDB 数据的操作。同时,也需要注意到这种方法的性能和功能限制,根据具体应用场景进行选择和优化。
23 9
|
22天前
|
SQL 存储 人工智能
Vanna:开源 AI 检索生成框架,自动生成精确的 SQL 查询
Vanna 是一个开源的 Python RAG(Retrieval-Augmented Generation)框架,能够基于大型语言模型(LLMs)为数据库生成精确的 SQL 查询。Vanna 支持多种 LLMs、向量数据库和 SQL 数据库,提供高准确性查询,同时确保数据库内容安全私密,不外泄。
92 7
Vanna:开源 AI 检索生成框架,自动生成精确的 SQL 查询
|
7天前
|
SQL Oracle 数据库
使用访问指导(SQL Access Advisor)优化数据库业务负载
本文介绍了Oracle的SQL访问指导(SQL Access Advisor)的应用场景及其使用方法。访问指导通过分析给定的工作负载,提供索引、物化视图和分区等方面的优化建议,帮助DBA提升数据库性能。具体步骤包括创建访问指导任务、创建工作负载、连接工作负载至访问指导、设置任务参数、运行访问指导、查看和应用优化建议。访问指导不仅针对单条SQL语句,还能综合考虑多条SQL语句的优化效果,为DBA提供全面的决策支持。
29 11
|
22天前
|
存储 缓存 网络协议
数据库执行查询请求的过程?
客户端发起TCP连接请求,服务端通过连接器验证主机信息、用户名及密码,验证通过后创建专用进程处理交互。服务端进程缓存以减少创建和销毁线程的开销。后续步骤包括缓存查询(8.0版后移除)、语法解析、查询优化及存储引擎调用,最终返回查询结果。
27 6
|
21天前
|
SQL 关系型数据库 MySQL
MySQL导入.sql文件后数据库乱码问题
本文分析了导入.sql文件后数据库备注出现乱码的原因,包括字符集不匹配、备注内容编码问题及MySQL版本或配置问题,并提供了详细的解决步骤,如检查和统一字符集设置、修改客户端连接方式、检查MySQL配置等,确保导入过程顺利。
|
20天前
|
SQL 监控 安全
SQL Servers审核提高数据库安全性
SQL Server审核是一种追踪和审查SQL Server上所有活动的机制,旨在检测潜在威胁和漏洞,监控服务器设置的更改。审核日志记录安全问题和数据泄露的详细信息,帮助管理员追踪数据库中的特定活动,确保数据安全和合规性。SQL Server审核分为服务器级和数据库级,涵盖登录、配置变更和数据操作等事件。审核工具如EventLog Analyzer提供实时监控和即时告警,帮助快速响应安全事件。
|
1月前
|
SQL 关系型数据库 MySQL
体验使用DAS实现数据库SQL优化,完成任务可得羊羔绒加厚坐垫!
本实验介绍如何通过数据库自治服务DAS对RDS MySQL高可用实例进行SQL优化,包含购买RDS实例并创建数据库、数据导入、生成并优化慢SQL、执行优化后的SQL语句等实验步骤。完成任务,即可领取羊羔绒加厚坐垫,限量500个,先到先得。
151 12
|
29天前
|
SQL Java
使用java在未知表字段情况下通过sql查询信息
使用java在未知表字段情况下通过sql查询信息
36 8
|
27天前
|
SQL 存储 BI
gbase 8a 数据库 SQL合并类优化——不同数据统计周期合并为一条SQL语句
gbase 8a 数据库 SQL合并类优化——不同数据统计周期合并为一条SQL语句
|
27天前
|
SQL 数据库
gbase 8a 数据库 SQL优化案例-关联顺序优化
gbase 8a 数据库 SQL优化案例-关联顺序优化