一条SQL完成跨数据库实例Join查询-阿里云开发者社区

开发者社区> lyrewu> 正文

一条SQL完成跨数据库实例Join查询

简介: 本文以MySQL及Redis为例,介绍如何通过一条SQL轻松完成跨数据库实例的查询。
+关注继续查看

背景

随着业务复杂程度的提高、数据规模的增长,越来越多的公司选择对其在线业务数据库进行垂直或水平拆分,甚至选择不同的数据库类型以满足其业务需求。原本在同一数据库实例里就能实现的SQL查询,现在需要跨多个数据库实例才能完成。业务的数据被“散落”在各个地方,如何方便地对这些数据进行汇总关联查询,已经成为困扰用户的一大难题。
针对这类问题,传统的解决方案需要用户提前将所有实例的数据提前汇集到汇总库进行查询分析。这种方案不仅无法满足查询时效性,且用户还需要承担数据汇集的链路稳定性风险及数据冗余的经济成本。
为了解决跨数据库实例及时查询的难题,阿里云DMS(数据管理)推出了跨数据库实例查询服务

DMS跨数据库查询

image
跨实例查询服务支持通过标准SQL进行跨同异构数据库的实时查询。除了关系型数据库MySQL、SQLServer、PostgreSQL,还支持Redis。同时,跨实例查询服务还支持跨地域、跨云及线下IDC自建数据库及跨云厂商数据库实例间的数据实时查询,被广泛应用于多地域部署业务的全局数据查询场景。
本文以MySQL及Redis为例,介绍如何通过一条SQL轻松完成跨数据库实例的查询。

通过标准SQL查询Redis

跨实例查询服务支持通过SQL查询Redis中的任意key,同时支持跨key之间的join查询。在进行SQL查询前,您需要先在跨实例查询服务中,创建Redis实例的DBLink。接下来简单介绍创建DBLink及进行SQL查询的流程。

创建DBLink

在跨实例查询服务的控制台,创建DBLink,配置Redis实例的连接信息。
当完成DBLink创建后,需要使用这个DBLink编写查询SQL。

通过SQL查询Key

跨实例查询服务提供WEB SQL命令窗口,可以直接在命令窗口中,通过标准的SQL进行Key查询。具体支持的SQL命令可以参考使用文档。
对于每一个redis的database, DMS会自动创建6张表,分别如下:
all: 存储所有的key
string: 存储数据类型为string的可以
hash:存储数据类型为hash的key
list: 存储数据类型为list的key
set:存储数据类型为set的key
zset:存储数据类型为score set的key
每张表的表结构如下:

列名 类型 说明
Key Varchar Redis中的key名
Index Varchar 当数据类型为list/zset时,为各个元素的index;当数据类型为hash时,改字段为hash中的key名称
Value Varchar Key的value值
Score Double 表示SortedSet的分值,其他数据类型为null
Expire_time Bigint 跟redis的ttl命令一致,表示数据离过期的剩余秒数
Data_type Varchar 这个key的数据类型

此处,我们通过通过如下的select语句查询all表中的前5个key。
select * from redis_test.db0.all limit 5;
image

跨MySQL&Redis Join查询

日前接到某游戏客户跨MySQL及Redis查询的需求。该客户将用户积分排行榜存储在redis myzset中,而将用户元信息维护在MySQL user 表中。用户的某个简单诉求是:在游戏APP中能实时刷新用户的积分排行榜。

数据结构

通过redis score set存储用户积分情况,存储用户ID及score, key的名称为user_scrore, value为用户ID, score为用户积分。样例数据如下表:

key value score
user_score 100 10
user_score 200 35
user_score 300 45

通过MyQL User表存储用户的基本信息,包括user_id, user_name,province, city, gmt_create等,其中:
User_id 为用户ID, User_name 为用户名
Province 为用户所属省份, city 为用户所属市
Gmt_create 为用户第一次登陆游戏APP的时间
样例数据如下表:

User_id User_name province city Gmt_create
100 张三 浙江 杭州 2018-11-11 11:11:11
200 李四 广东省 深圳 2018-10-11 12:11:01

关联查询

通过如下SQL,可方便得查询用户的积分排行榜。
select user.user_name,zset.score from redis_test.db0.zset as zset join mysql.db0.user as user where
user.user_id=zset.value and zset.key='user_score' order by zset.score desc;
这个SQL将redis中的用户ID跟MySQL中的user_id进行关联,且只查询存储用户积分的key。

小结

由于篇幅有限,本文只是简单介绍了DMS 跨数据库查询的功能及其在Redis上的使用案例。您可以立即体验跨数据库实例查询>>
跨数据库查询的详细功能及使用场景可以参考:使用文档
教你用一条SQL搞定跨数据库查询:如何玩转跨库Join

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
MyBatis 实践 -动态SQL/关联查询
MyBatis 实践 标签: Java与存储 动态SQL 动态SQL提供了对SQL语句的灵活操作,通过表达式进行判断,对SQL进行拼接/组装.
673 0
实战课堂 | DMS企业版教你用一条SQL搞定跨实例查询
数据管理DMS企业版提供了安全、高效地管理大规模数据库的服务。面对多元的数据库实例,为了更方便地查询被“散落”在各个地方的业务数据,我们在DMS企业版中提供了跨数据库实例查询服务。
2767 0
PostgreSQL 数据库实例只读锁定(readonly) - 硬锁定,软锁定,解锁
PostgreSQL 数据库实例只读锁定(readonly) - 硬锁定,软锁定,解锁
564 0
使用Spring AOP实现MySQL数据库读写分离案例分析
使用Spring AOP实现MySQL数据库读写分离案例分析 前言 分布式环境下数据库的读写分离策略是解决数据库读写性能瓶颈的一个关键解决方案,更是最大限度了提高了应用中读取 (Read)数据的速度和并发量。
1779 0
SQL Server 查询数据库中所有的表名及行数
SELECT a.NAME ,b.rows FROM sysobjects AS a INNER JOIN sysindexes AS b ON a.id = b.id WHERE (a.type = 'u') AND ( b.indid IN ( 0 ,1 )
1447 0
Sql server 查询数据库中包含某字段的所有的表
我们有时候会需要查询数据库中包含某字段的所有的表,去进行update,这时就可以用下面的SQL来实现: select object_name(id) objName,Name as colName from syscolumns where (name like'%此次写需要查询的字段名称%')an...
960 0
获取动态SQL查询语句返回值(sp_executesql)
原文:获取动态SQL查询语句返回值(sp_executesql) 在写存储过程时经常会遇到需要拼接SQL语句的情况,一般情况下仅仅是为了执行拼接后的语句使用exec(@sql)即可。 而今天的一个存储过程却需要获取动态SQL的查询结果。
930 0
+关注
lyrewu
专注数据库领域产品输出。
9
文章
0
问答
来源圈子
更多
阿里巴巴集团数据库技术团队,负责集团内部、菜鸟、阿里音乐、蚂蚁金服等业务数据库支持。拥有国内外知名的MySQL内核开发团队,保障历年天猫双十一的数据库稳定性。同时也向阿里云业务输出DMS和DTS产品。
+ 订阅
文章排行榜
最热
最新
相关电子书
更多
《2021云上架构与运维峰会演讲合集》
立即下载
《零基础CSS入门教程》
立即下载
《零基础HTML入门教程》
立即下载