ShardingSphere 读写分离配置

背景

        目前已经搭建好了mysql主从架构,参考这篇博文：Mysql主从架构搭建保姆级教程-优快云博客，需要再业务层面使用ShardingJDBC作为中间件来实现读写分离，提高数据查询效率。

        这篇文章使用 ShardingSphere 中的JDBC方式，并通过JAVA SDK形式引入spring cloud工程，实现轻量级的读写分离功能。

Sharding Sphere介绍

        Apache ShardingSphere 是一款分布式的数据库生态系统， 可以将任意数据库转换为分布式数据库，并通过数据分片、弹性伸缩、加密等能力对原有数据库进行增强。

        Apache ShardingSphere 设计哲学为 Database Plus，旨在构建异构数据库上层的标准和生态。 它关注如何充分合理地利用数据库的计算和存储能力，而并非实现一个全新的数据库。 它站在数据库的上层视角，关注它们之间的协作多于数据库自身。

思路

        maven中引入依赖，通过bean配置数据源然后在yml中配置主从数据源、负载策略等即可。

        下面直接贴代码

代码

1 pom.xml

        pom依赖如下，网络上有很多pom依赖但是经常出现yml不生效的问题。这篇文章采用javabean的形式管理，配置自己参考官网文档选用需要的进行自定义即可。

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid-spring-boot-starter</artifactId>
</dependency>

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>

<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <version>${mysql.connector.version}</version>
</dependency>

<!-- ShardingJDBC 依赖 -->
<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>shardingsphere-jdbc-core</artifactId>
    <version>${sharding.jdbc.version}</version>
</dependency>

2 yml配置

spring:
  # 读写分离主从配置
  shardingsphere:
    datasource:
      master:
        name: ds_master
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://172.16.10.113:16030/bdcp_api_service?useSSL=false&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&transformedBitIsBoolean=true&tinyInt1isBit=false&allowMultiQueries=true&serverTimezone=GMT%2B8
        username: root
        password: DQx!wBdV#7
      slave1:
        name: ds_slave_1
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://172.16.10.112:16030/bdcp_api_service?useSSL=false&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&transformedBitIsBoolean=true&tinyInt1isBit=false&allowMultiQueries=true&serverTimezone=GMT%2B8
        username: root
        password: DQx!wBdV#7
      slave2:
        name: ds_slave_2
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://172.16.10.114:16030/bdcp_api_service?useSSL=false&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&transformedBitIsBoolean=true&tinyInt1isBit=false&allowMultiQueries=true&serverTimezone=GMT%2B8
        username: root
        password: DQx!wBdV#7

3 代码配置

3.1 自定义配置文件

@Data
@NoArgsConstructor
@AllArgsConstructor
public class ApiDsProperties {

    private String name;
    private String type;
    private String driverClassName;
    private String url;
    private String username;
    private String password;

}

@Data
@RefreshScope
@Component
@ConfigurationProperties(prefix = "spring.shardingsphere.datasource")
public class ShardingSphereProperties {


    private ApiDsProperties master;

    private ApiDsProperties slave1;

    private ApiDsProperties slave2;


}

3.2 主从数据源配置

根据上述文件格式配置即可

import com.alibaba.druid.pool.DruidDataSource;
import com.dsj.prod.apiservice.biz.properties.ApiDsProperties;
import com.dsj.prod.apiservice.biz.properties.ShardingSphereProperties;
import org.apache.shardingsphere.driver.api.ShardingSphereDataSourceFactory;
import org.apache.shardingsphere.readwritesplitting.api.ReadwriteSplittingRuleConfiguration;
import org.apache.shardingsphere.readwritesplitting.api.rule.ReadwriteSplittingDataSourceRuleConfiguration;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.core.JdbcTemplate;

import javax.annotation.Resource;
import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.*;

@Configuration
public class DataSourceConfig {


    public static final String RANDOM = "random";

    @Resource
    private ShardingSphereProperties shardingSphereProperties;

    @Bean
    public DataSource dataSource() throws SQLException {
        Map<String, DataSource> dataSourceMap = createDataSource();
        // 配置读写分离规则
        ReadwriteSplittingDataSourceRuleConfiguration dataSourceRuleConfig = new ReadwriteSplittingDataSourceRuleConfiguration(
                RANDOM,
                null,//禁用自动感知主库
                shardingSphereProperties.getMaster().getName(),
                Arrays.asList(shardingSphereProperties.getSlave1().getName(), shardingSphereProperties.getSlave2().getName()),
                "ROUND_ROBIN"
        );
        Properties props = new Properties();
        // 打印sql
        props.setProperty("sql-show", Boolean.TRUE.toString());

        ReadwriteSplittingRuleConfiguration ruleConfig = new ReadwriteSplittingRuleConfiguration(Collections.singleton(dataSourceRuleConfig), Collections.emptyMap());
        return ShardingSphereDataSourceFactory.createDataSource( dataSourceMap, Collections.singleton(ruleConfig), props);
    }


    /**
     * @return {@link Map< String, DataSource>}
     * @description: 创建数据源
     * @version v1.0.0
     * @since 12:59 PM 2023/12/5
     **/
    private Map<String, DataSource> createDataSource() {
        Map<String, DataSource> dataSourceMap = new HashMap<>();
        // 一主两从
        ApiDsProperties master = shardingSphereProperties.getMaster();
        ApiDsProperties slave1 = shardingSphereProperties.getSlave1();
        ApiDsProperties slave2 = shardingSphereProperties.getSlave2();

        DruidDataSource masterDataSource = new DruidDataSource();
        masterDataSource.setUsername(master.getUsername());
        masterDataSource.setPassword(master.getPassword());
        masterDataSource.setDriverClassName(master.getDriverClassName());
        masterDataSource.setUrl(master.getUrl());
        dataSourceMap.put(master.getName(), masterDataSource);

        DruidDataSource slave1DataSource = new DruidDataSource();
        slave1DataSource.setUsername(slave1.getUsername());
        slave1DataSource.setPassword(slave1.getPassword());
        slave1DataSource.setDriverClassName(slave1.getDriverClassName());
        slave1DataSource.setUrl(slave1.getUrl());
        dataSourceMap.put(slave1.getName(), slave1DataSource);

        DruidDataSource slave2DataSource = new DruidDataSource();
        slave2DataSource.setUsername(slave2.getUsername());
        slave2DataSource.setPassword(slave2.getPassword());
        slave2DataSource.setDriverClassName(slave2.getDriverClassName());
        slave2DataSource.setUrl(slave2.getUrl());
        dataSourceMap.put(slave2.getName(), slave2DataSource);
        return dataSourceMap;
    }


    @Bean
    public JdbcTemplate jdbcTemplate(DataSource dataSource) {
        return new JdbcTemplate(dataSource);
    }
}

测试

这里使用jdbctemplate进行读取测试。测试的环境如下：

ds_master 主库

ds_slave_1 从库1

ds_slave_1 从库2

测试表 test_table

测试库 使用自己新建的

-- 创建测试库
CREATE DATABASE bdcp_api_service;

-- 创建测试表
CREATE TABLE `test_table` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- 模拟插入几条数据
insert into test_table (name) values ('data1');
insert into test_table (name) values ('data2');
insert into test_table (name) values ('data3');

测试端点代码：

/**
 * 读写分离测试控制器
 *
 * @title 读写分离测试控制器
 * @package com.dsj.prod.apiService.controller
 */
@Api(value = "读写分离测试控制器", tags = "读写分离测试控制器")
@Slf4j
@RestController
@RequestMapping("/test-read-write-separation")
public class TestReadWriteSeparationController {

    @Resource
    private JdbcTemplate jdbcTemplate;

    @GetMapping("/read-data")
    public R<Object> readData() throws SQLException {
        String sql = "select * from test_table";
        List<Map<String, Object>> resultList = jdbcTemplate.queryForList(sql);
        log.info("读数据成功: {}", resultList);

        return R.ok(resultList);
    }

    @PostMapping("/write-data")
    public R<Object> writeData(String name) throws SQLException {
        // 需要预编译
        String sql = "insert into test_table (name) values (?)";
        jdbcTemplate.update(sql, name);
        log.info("写数据成功");

        return R.ok("写数据成功");
    }

}

可以看到打印的日志，读取走从库，轮询。写入走主库。

        当然可以进行更加多样的配置，比如双主双从，或者配置读取的其他策略的比如权重、随机等。推荐参考官方文档查看后续更多的内容。

官网文档：

概览 :: ShardingSphere