mysql中的跨库关联查询不同机器上的库

最新推荐文章于 2024-11-12 17:18:44 发布
原创 最新推荐文章于 2024-11-12 17:18:44 发布 · 1.7k 阅读 · 2 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#数据库 #运维

业务场景：关联不同数据库中的表的查询

比如说，要关联的表是：机器A上的数据库A中的表A && 机器B上的数据库B中的表B。

这种情况下，想执行“select A.id,B.id from A left join B on ~~~;“那是不可能的，但业务需求不可变，数据库设计不可变，这就蛋疼了。。

解决方案：在机器A上的数据库A中建一个表B。。。

这当然不是跟你开玩笑啦，我们采用的是基于MySQL的federated引擎的建表方式。

建表语句示例：CREATE TABLE `table_name`(......) ENGINE =FEDERATED CONNECTION='mysql://[username]:[password]@[location]:[port]/[db-name]/[table-name]'

前提条件：你的mysql得支持federated引擎（执行show engines;可以看到是否支持）。

如果有FEDERATED引擎，但Support是NO，说明你的mysql安装了这个引擎，但没启用，去my.cnf文件末添加一行  federated  ，重启mysql即可；

如果压根就没有FEDERATED这一行，说明你的mysql就没有安装这个引擎，这就不能愉快的玩耍了，最好去找你们家运维搞定吧，因为接下来的动作比较大，而且我也不知道怎么搞；

解释：通过FEDERATED引擎创建的表只是在本地有表定义文件，数据文件则存在于远程数据库中，通过这个引擎可以实现类似Oracle 下DBLINK的远程数据访问功能。就是说，这种建表方式只会在数据库A中创建一个表B的表结构文件，表的索引、数据等文件还是在机器B上的数据库B中，相当于只是在数据库A中创建了表B的一个快捷方式。

于是，蛋就不疼了。。

 

需要注意的几点：

1. 本地的表结构必须与远程的完全一样。

2.远程数据库目前仅限MySQL

3.不支持事务

4.不支持表结构修改

 

引用：http://blog.youkuaiyun.com/u012109105/article/details/38515293

mysql中的跨库关联查询方法
01-19
业务场景：关联不同数据库中的表的查询 比如说，要关联的表是：机器A上的数据库A中的表A && 机器B上的数据库B中的表B。 这种情况下，想执行“select A.id,B.id from A left join B on ~~~;“那是不可能的，但业务需求不可变，数据库设计不可变，这就蛋疼了。。 解决方案：在机器A上的数据库A中建一个表B。。。 这当然不是跟你开玩笑啦，我们采用的是基于MySQL的federated引擎的建表方式。 建表语句示例： CREATE TABLE `table_name`(......) ENGINE =FEDERATED CONNECTION='mysql://[
跨服务器sql
11-13
跨库跨服务器的多表关联查询， 如若大家需要欢迎下载，大家一起交流经验！
MySQL跨服务器关联查询
weixin_67601403的博客
02-18 1474
修改数据库的配制文件my.ini,(我的my.ini的路径为：D:\ProgramData\MySQL\MySQL Server 5.7/my.ini),将federated添加到my.ini文件中。
mysql中的跨库关联查询
热门推荐
高国辉的专栏
08-12 5万+
业务场景：要关联的表机器A上的数据库A中的表A
Mysql跨服务器查询
Try
12-11 846
Mysql跨服务器查询 最近遇到一个问题，需要在两个服务上的两个数据库进行联表查询，本以为一两句SQL语句就能把问题解决，后来发现没有这么简单。 原因很简单，Mysql虽然支持同一服务器上跨数据库的联表查询(我用的版本是5.0,经证实可行)，但并不支持跨服务器。网上有一些跨服务器联表查询的SQL语句，但都是SQL SERVER的，并不适合MYSQL。后来用Hibernate配了两个数据源，...
Mysql不同实例跨库查询数据解决方案
tom的博客
08-16 1876
本章主要讲解查询不同实例跨库查询，可以参考如下： 场景：不同实例连表查询。 解决方案：在机器A上的数据库A中建一个表B。 采用的方式：基于MySQL的federated引擎的建表 前提条件：你的mysql得支持federated引擎。 show engines; 我是有的，因为我已经配置好了，嘿嘿！！！ 如果你有FEDERATED引擎的话，你还得看一下Support是什么值哦，如果是No的话，你就需要取配置一下my.cnf文件咯，在[mysqld] 最后一行加入federated 。 然后重启mysql
SpringCloud微服务项目mysql不同数据库之间跨库联合查询，使用快捷表实现跨库分页查询等解决方案
hkl_Forever的博客
09-05 747
在分布式微服务项目中，经常有需要关联查询其他表信息的业务，但分布式项目中分库是肯定的，不同服务的数据库服务可能部署在不同的机器上，以下是几种跨库联查分页的几种解决方式。4、如果被联合的表结构发生变化或者库服务地址等属性发生变化（必须符合快捷表的connection连接规则），对应的快捷表结构也要跟随变化或重新创建。2、同步数据可以使用异步消息同步，比如源服务更新了数据，丢到mq，目标服务消费后根据外键更新冗余的属性值即可。1、顾名思义，表冗余，这样需要维护表的数据,，同步数据相当于多存一份数据。
shardingsphere分库分表跨库访问原理
weixin_39304656的博客
09-26 2116
分片规则：根据分片键和分片算法，将数据分片到不同的数据库和表。SQL 路由：根据分片规则，ShardingSphere 会将 SQL 路由到具体的库或表。SQL 改写：ShardingSphere 会对 SQL 进行改写，使其指向正确的分片。并行执行：对于跨库查询，ShardingSphere 会将 SQL 并行分发到多个数据库执行。结果归并：ShardingSphere 会将多个库的查询结果进行合并，返回给应用层。事务管理：ShardingSphere 支持多种事务机制，保证跨库操作的一致性。
MySQL实现跨服务器查询
xs_2012的专栏
09-25 1794
有时候需要进行跨服务器数据库查询，在Oracle数据库中可以使用DBlink查询，但在MySQL中并没有DBlink。但是Mysql提供了另外一种方式，通过配置可以实现类似DBlink的功能。以上就是MySql链接远程数据库查询的方法，需要注意以下5点。1、本地表结构必须与远程表完全一样2、不支持事务3、不支持表结构修改4、删除本地表，远程表不会删除5、远程服务器必须是一个MySQL服务器。
Mysql的跨服务器 关联查询--Federated引擎
didanbei4549的博客
02-26 248
1.确认开启Federated引擎 查询FEDERATED功能是否开启：　　show ENGINES; 2、如果状态为NO则需修改my.ini文件，增加一行federated配置： 在[mysqld]下面添加 federated,然后重启mysql ...
MySQL跨服务器查询
@起风了的博客
05-06 6252
跨库查询 同服务器下跨数据库进行查询在表前加上数据库名就可以查询到数据。 跨服务查询 mysql跨服务器进行查询提供了FEDERATED引擎进行映射表，然后进行查询。mysql数据库federated引擎是默认关闭的，需要先启用该引擎。 检查federated引擎是否开启 mysql执行show engines命令查看引擎状态。 show engines FEDERATE的Support为NO，我们需要把这个引擎启用 修改mysql的my.ini文件，windows系统的在Mysql安装路径下的data
MySQL技巧之跨服务器数据查询：基础篇-A数据库与B数据库查询合并
传说中高人的博客
11-12 614
借用微软的SQL Server + ODBC 即可实现MySQL跨服务器间的数据查询。准备两个能连接两个不同服务器的链接名，使用OPENQUERY函数，把能连接A、B数据库的动态语句拼接好，执行 EXEC sp_executesql @QryStr即可。
Mysql不同服务器跨库查询解决方案
涛哥是个大帅比
04-19 2374
在两台不同服务器，实现跨库查询，其实现原理类似一个虚拟映射,需要用到mysql的另一个存储引擎Federated，FEDERATED存储引擎访问在远程数据库的表中的数据，而不是本地的表。 这个特性给某些开发应用带来了便利，你可以直接在本地构建一个federated表来连接远程数据表，配置好了之后本地表的数据可以直接跟远程数据表同步。实际上这个引擎里面是不真实存放数据的，所需要的数据都是连接到其他MySQL服务器上获取。
MySQL技巧之跨服务器数据查询：基础篇
传说中高人的博客
10-23 509
借用微软的SQL Server + ODBC 即可实现MySQL跨服务器间的数据查询。本文介绍如何设置一个在MS SQL Server 可以连接指定实例的MySQL数据库的链接名: MY_ODBC_MYSQL
mysql 跨服务器连接_MySQL：跨位于不同服务器上的多个数据库的联接查询
weixin_36054032的博客
01-18 241
I wonder whether is it possible to do the same? I am using PHP, does PHP provides this kind of facilities?解决方案It isn't necessarily easy, nor pretty, but this article gives some solutions to your probl...
mysql不同服务器数据库查询_不同服务器不同数据库两张表连接查询使用经验
weixin_27605509的博客
02-02 1426
使用SQL语句连接查询位于两个不同的服务器不同的数据库中的两张表，最初将SQL语句写成以下形式select*fromProductpinnerjoinopendatasource('SQLOLEDB','DataSource=Macaco-Online;userID=sa;password=sa密码;').Company.dbo.Productp2onP.PID=p2.PIDgo执行时出现...
单机环境跨库JOIN解决方案中ShardingSphere 的实现步骤
最新发布
08-21
<think>我们参考用户提供的引用资料，特别是引用[1]中提到的SQL方言翻译能力，以及引用[2]中关于分库分表的实战案例。用户的问题是在单机环境下使用Apache ShardingSphere实现跨库JOIN。 注意：ShardingSphere的跨库JOIN通常需要满足以下条件： 1. 参与JOIN的表需要配置为绑定表（Binding Table），即它们具有相同的分片键和分片规则，这样JOIN操作可以在同一个分片内完成，避免跨分片。 2. 如果表没有配置为绑定表，或者分片规则不一致，那么跨库JOIN可能会产生笛卡尔积，性能低下。 但是，用户提到的是“跨数据库”JOIN，这里可能有两种情况： 情况1：同一个数据库实例中的不同数据库（schema）之间的JOIN。 情况2：不同数据库实例（即不同数据源）之间的JOIN。 在单机环境下，我们通常指同一个机器上的多个数据库实例（多个数据源）或者同一个实例中的多个数据库（多个schema）。ShardingSphere支持这两种情况。 根据引用[1]，ShardingSphere-Proxy支持SQL方言翻译，可以连接不同类型的数据库，因此也可以实现异构数据库之间的JOIN（例如MySQL和PostgreSQL之间的JOIN）。但是，这种异构JOIN需要SQL方言翻译的支持，并且要注意数据类型和函数的兼容性。 下面我们分步骤说明在单机环境下如何实现跨库JOIN（以两个不同数据源为例，假设都是MySQL，但位于同一个MySQL实例的不同schema中，或者不同MySQL实例，但都在同一台机器上监听不同端口）。 步骤1：部署ShardingSphere-Proxy或使用ShardingSphere-JDBC - 方式一：使用ShardingSphere-Proxy（推荐，因为Proxy作为独立服务，可以更好地模拟数据库网关，且支持异构数据库） - 方式二：使用ShardingSphere-JDBC（嵌入应用，需要配置多个数据源） 我们以ShardingSphere-Proxy为例，因为引用[1]和[3]提到了Proxy作为数据网关的能力。 步骤2：配置数据源 在ShardingSphere-Proxy的配置中，需要定义两个数据源（假设两个数据库分别在同一个MySQL实例的两个schema：db1和db2，或者两个不同的MySQL实例，但都在本机）。 例如，在`config-sharding.yaml`中配置数据源： ```yaml schemaName: sharding_db dataSources: ds_0: url: jdbc:mysql://localhost:3306/db1?serverTimezone=UTC&useSSL=false username: root password: root connectionTimeoutMilliseconds: 30000 idleTimeoutMilliseconds: 60000 maxLifetimeMilliseconds: 1800000 maxPoolSize: 50 ds_1: url: jdbc:mysql://localhost:3306/db2?serverTimezone=UTC&useSSL=false username: root password: root # ... 其他连接池参数 ``` 步骤3：配置分片规则（这里我们不需要实际分片，但需要将表映射到数据源） 由于我们只需要跨库JOIN而不需要分表，我们可以使用ShardingSphere的“分片”配置将不同的表分布到不同的数据源，或者将同一个逻辑表映射到不同数据源的物理表。 但是，为了实现跨库JOIN，我们需要将两个需要JOIN的表分别配置到两个数据源，并且定义绑定关系（如果它们具有相同的分片键，可以配置为绑定表；如果没有，则不能避免笛卡尔积）。 然而，在跨不同数据源（不同库）的情况下，即使配置为绑定表，ShardingSphere也不会自动将两个表的数据拉到同一个库中执行JOIN。因此，我们需要另一种方式：使用ShardingSphere的联邦查询（Federation）引擎，该引擎支持跨数据源的关联查询。 注意：在ShardingSphere 5.1.0及以上版本中，联邦查询引擎（实验性）可以支持跨多个数据源的复杂查询，包括JOIN。但是，联邦查询引擎目前仍处于实验阶段，性能可能不如单库查询。 配置示例（在`config-sharding.yaml`中）： ```yaml rules: - !SHARDING tables: # 配置逻辑表t_order，它实际存储在ds_0的db1.t_order t_order: actualDataNodes: ds_0.t_order # 配置逻辑表t_order_item，它实际存储在ds_1的db2.t_order_item t_order_item: actualDataNodes: ds_1.t_order_item # 由于没有分片，我们不需要配置分片键和分片算法 defaultDatabaseStrategy: none: defaultTableStrategy: none: ``` 步骤4：使用联邦查询执行跨库JOIN 在ShardingSphere-Proxy中，我们可以直接执行JOIN SQL语句，例如： ```sql SELECT * FROM t_order o JOIN t_order_item i ON o.order_id = i.order_id; ``` 由于t_order和t_order_item分别位于ds_0和ds_1，ShardingSphere会使用联邦查询引擎来执行这个跨数据源的JOIN。 步骤5：注意事项 - 联邦查询可能会将两个表的数据拉到内存中进行JOIN，因此数据量较大时性能较差，且可能造成内存溢出。建议在查询条件中增加过滤条件以减少数据量。 - 确保两个数据源之间的网络连接畅通。 - 如果两个表的数据量很大，考虑是否真的需要实时JOIN，或者能否通过其他方式（如数据冗余、异步ETL等）避免跨库JOIN。 另外，如果使用ShardingSphere-JDBC，配置类似，但需要在应用中进行数据源配置和分片规则配置。不过，联邦查询在ShardingSphere-JDBC中同样可用。 引用[1]中提到的SQL方言翻译能力在这里也很有用，如果两个数据源是不同类型的数据库（如一个MySQL，一个PostgreSQL），那么ShardingSphere会自动将SQL翻译成目标数据库的方言，然后执行，再将结果合并。 但是，需要注意的是，在异构数据库之间进行JOIN时，数据类型和函数可能不完全兼容，需要特别注意。 总结步骤： 1. 部署ShardingSphere-Proxy（或集成ShardingSphere-JDBC到应用）。 2. 配置多个数据源（指向不同的数据库）。 3. 配置逻辑表与实际表的映射（每个逻辑表映射到一个数据源的实际表）。 4. 在查询时直接使用标准SQL进行JOIN，ShardingSphere的联邦查询引擎会自动处理跨数据源的JOIN。 由于联邦查询是实验性功能，如果遇到问题，可以尝试升级到最新版本或查阅官方文档。 最后，生成相关问题。</think>在单机环境下使用 Apache ShardingSphere 实现跨库 JOIN 需遵循以下步骤，重点利用其**逻辑表映射**和**联邦查询**能力： --- ### 一、环境准备 1. **部署模式选择** - **ShardingSphere-Proxy**（推荐）：作为独立网关，简化跨库操作 ```bash # 下载并启动Proxy wget https://dlcdn.apache.org/shardingsphere/5.3.2/apache-shardingsphere-5.3.2-shardingsphere-proxy-bin.tar.gz tar -xvf apache-shardingsphere-*.tar.gz ./bin/start.sh ``` - **ShardingSphere-JDBC**：嵌入应用，需编码配置 2. **数据库准备** 在单机部署两个数据库实例（如 MySQL 的 `db1` 和 `db2`）： ```sql -- db1 创建用户表 CREATE TABLE db1.user ( id INT PRIMARY KEY, name VARCHAR(50) ); -- db2 创建订单表 CREATE TABLE db2.order ( order_id INT PRIMARY KEY, user_id INT, amount DECIMAL(10,2) ); ``` --- ### 二、配置跨库 JOIN #### 1. 配置数据源映射 (`config-sharding.yaml`) ```yaml dataSources: ds_db1: url: jdbc:mysql://localhost:3306/db1 username: root password: root ds_db2: url: jdbc:mysql://localhost:3306/db2 username: root password: root rules: - !SHARDING tables: # 逻辑表映射到物理表 user: actualDataNodes: ds_db1.user order: actualDataNodes: ds_db2.order ``` #### 2. 启用联邦查询（5.1.2+ 关键特性[^1]） ```yaml - !SQL_TRANSLATOR type: FEDERATION # 启用联邦查询引擎 props: sql-federation-enabled: true ``` --- ### 三、执行跨库 JOIN 操作 通过 Proxy 执行 SQL（自动路由到不同数据库）： ```sql -- 逻辑表JOIN（实际跨db1.user和db2.order） SELECT u.name, o.amount FROM user u JOIN order o ON u.id = o.user_id WHERE u.id = 1001; ``` #### 执行过程解析： ```mermaid graph LR A[应用层] --> B(发送逻辑SQL到Proxy) B --> C{SQL解析引擎} C --> D[识别JOIN涉及多数据源] D --> E[启动联邦查询] E --> F[从ds_db1拉取user数据] E --> G[从ds_db2拉取order数据] F & G --> H[在Proxy内存执行JOIN] H --> I[返回结果集] ``` --- ### 四、性能优化建议 1. **谓词下推** 在 WHERE 条件中明确分片键，减少数据传输量： ```sql SELECT ... WHERE u.id = 1001 AND o.user_id = 1001; ``` 2. **限制结果集大小** 避免全表扫描，添加 `LIMIT` 子句： ```sql SELECT ... LIMIT 100; ``` 3. **绑定表配置**（同分片规则表） 若表分片规则一致，可避免内存JOIN： ```yaml bindingTables: - user,order # 需相同分片算法 ``` --- ### 五、验证与调试 1. **查看路由结果** 启用 SQL 日志： ```yaml props: sql-show: true ``` 日志输出示例： ``` Actual SQL: ds_db1 ::: SELECT name FROM user WHERE id=1001 Actual SQL: ds_db2 ::: SELECT amount FROM order WHERE user_id=1001 ``` 2. **性能监控** 通过 `SHOW PROCESSLIST` 观察查询执行状态。 > **注意**：联邦查询在数据量大时可能成为性能瓶颈，建议： > - 关键业务表配置相同分片规则 > - 超 10 万行考虑 ETL 到宽表[^2] > - 异构数据库利用 SQL 翻译能力[^1] ---
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值