MySQL跨实例数据查询项目实践指南

无界猴

6个月前更新

029269

一、跨实例查询方案概述

在MySQL环境中实现跨实例数据查询主要有以下几种方案：

数据库链接方案：FEDERATED引擎、DBLink等
ETL工具方案：Kettle、DataX等
中间件方案：ShardingSphere、MyCat等
应用层方案：多数据源+程序合并
数据同步方案：主从复制、CDC工具

二、FEDERATED引擎实现跨实例查询

1. 启用FEDERATED引擎

-- 检查是否支持
SHOW ENGINES;

-- 如果未启用，在my.cnf中添加

[mysqld]

federated

2. 创建FEDERATED表

CREATE TABLE federated_table (
    id     INT(20) NOT NULL AUTO_INCREMENT,
    name   VARCHAR(32) NOT NULL DEFAULT '',
    PRIMARY KEY (id)
)
ENGINE=FEDERATED
CONNECTION='mysql://username:password@remote_host:3306/database/original_table';

3. 使用限制与注意事项

仅支持基本的SELECT/INSERT/UPDATE/DELETE操作
不支持事务
性能较差，不适合高频查询
远程表必须有主键或唯一索引

三、使用ShardingSphere实现跨库查询

1. 部署ShardingSphere-Proxy

# 下载最新版本
wget https://archive.apache.org/dist/shardingsphere/5.1.2/apache-shardingsphere-5.1.2-shardingsphere-proxy-bin.tar.gz

# 配置server.yaml和config-sharding.yaml

2. 配置数据源

dataSources:
  ds_0:
    url: jdbc:mysql://instance1:3306/db1
    username: root
    password: password
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50

  ds_1:
    url: jdbc:mysql://instance2:3306/db2
    username: root
    password: password
    # 其他配置...

3. 配置分片规则

rules:
- !SHARDING
  tables:
    t_order:
      actualDataNodes: ds_0.t_order_${0..1}, ds_1.t_order_${2..3}
      # 其他分片规则...

4. 执行跨实例查询

-- 通过Proxy执行跨实例JOIN查询
SELECT a.*, b.* 
FROM ds_0.db1.table1 a 
JOIN ds_1.db2.table2 b ON a.id = b.ref_id;

四、应用层多数据源方案

1. Spring Boot多数据源配置

@Configuration
public class DataSourceConfig {

    @Bean
    @Primary
    @ConfigurationProperties(prefix="spring.datasource.primary")
    public DataSource primaryDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean
    @ConfigurationProperties(prefix="spring.datasource.secondary")
    public DataSource secondaryDataSource() {
        return DataSourceBuilder.create().build();
    }
}

2. 多数据源事务管理

@Bean
public PlatformTransactionManager transactionManager(
    @Qualifier("primaryDataSource") DataSource primaryDataSource,
    @Qualifier("secondaryDataSource") DataSource secondaryDataSource) {

    Map<Object, PlatformTransactionManager> managers = new HashMap<>();
    managers.put("primary", new DataSourceTransactionManager(primaryDataSource));
    managers.put("secondary", new DataSourceTransactionManager(secondaryDataSource));

    return new ChainedTransactionManager(
        managers.get("primary"),
        managers.get("secondary")
    );
}

3. 数据合并查询示例

public List<CombinedResult> queryCrossInstanceData() {
    // 查询第一个实例
    List<DataA> listA = jdbcTemplatePrimary.query(
        "SELECT * FROM table_a", new DataARowMapper());

    // 查询第二个实例
    List<DataB> listB = jdbcTemplateSecondary.query(
        "SELECT * FROM table_b", new DataBRowMapper());

    // 应用层合并数据
    return listA.stream()
        .flatMap(a -> listB.stream()
            .filter(b -> a.getId().equals(b.getRefId()))
            .map(b -> new CombinedResult(a, b)))
        .collect(Collectors.toList());
}

五、性能优化建议

索引优化：确保关联字段有索引
查询下推：尽量将过滤条件放到原始查询中
数据缓存：对不常变的数据使用Redis缓存
异步查询：并行执行多个实例查询
数据冗余：考虑将必要数据同步到查询主库
查询限制：添加LIMIT避免大数据量传输

六、监控与维护

慢查询监控：设置long_query_time监控跨实例查询
连接池监控：监控各实例连接池状态
数据一致性检查：定期校验关键数据一致性
查询日志分析：记录和分析跨实例查询模式

七、方案选型对比

方案	适用场景	优点	缺点
FEDERATED	简单查询、低频访问	配置简单，无需中间件	性能差，功能有限
ShardingSphere	复杂查询、分布式环境	功能强大，支持分布式事务	需要部署中间件
应用层多数据源	定制化需求、灵活查询	完全控制，灵活性高	开发成本高
ETL工具	定时批量数据处理	适合大数据量处理	实时性差