基于shardingJDBC的读写分离

一、读写分离的好处

减少主库读的请求数量
主库和从库使用不同的数据库引擎提升读写效率
提升数据库的可用性，蹦一两台都不要紧

二、基于mybatisPlus 和 shardingJDBC的实现

导入相关JAR包，其他mybatis和数据库驱动等jar包自行引入

<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
    <version>4.1.1</version>
</dependency>

springboot相关配置

spring:
  shardingsphere:
    props:
      # 是否打印SQL
      sql.show: true
    datasource:
      # 需要使用到的数据源
      names: ds0,ds1,ds2
      ds0:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://127.0.0.1:3307/sharding_1?serverTimezone=Asia/Shanghai&characterEncoding=utf-8&useSSl=false&useUnicode=true
        username: root
        password: Mysql@123456
      ds1:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://127.0.0.1:3307/sharding_2?serverTimezone=Asia/Shanghai&characterEncoding=utf-8&useSSl=false&useUnicode=true
        username: root
        password: Mysql@123456
      ds2:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://127.0.0.1:3307/sharding_3?serverTimezone=Asia/Shanghai&characterEncoding=utf-8&useSSl=false&useUnicode=true
        username: root
        password: Mysql@123456

    masterslave:
      # 读写分离配置
      load-balance-algorithm-type: round_robin #轮询
      # 最终的数据源名称
      name: dataSource
      # 主库数据源名称
      master-data-source-name: ds0
      # 从库数据源名称列表，多个逗号分隔
      slave-data-source-names: ds1, ds2

三个数据库中的表结构

注意：

sharding_1：无数据

sharding_2：有一条test1的数据

sharding_3：有一条test2的数据

CREATE TABLE `test` (
  `id` bigint NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `age` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

java代码（就按照正常的写）

@Service
public class TestServiceImpl extends ServiceImpl<TestMapper, Test> implements ITestService {

    /**
     * TEST1 用于测试 主库的写入
     */
    @Override
    @Transactional
    public void test1() {
        for (int i = 0; i < 10; i++) {
            Test test = new Test();

            test.setAge(i);
            test.setName("名字呀" + i);

            super.save(test);
        }
    }

    /**
     * TEST2 用于测试 从库的读取
     */
    @Override
    public void test2() {
        System.out.println(super.list());
    }
}

@SpringBootApplication
@MapperScan(value = "com.example.shardingjdbc.dao")
public class ShardingJdbcApplication implements CommandLineRunner {

    public static void main(String[] args) {
        SpringApplication.run(ShardingJdbcApplication.class, args);
    }

    @Autowired
    ITestService iTestService;

    @Override
    public void run(String... args) throws Exception {
        iTestService.test1();
        iTestService.test2();
        iTestService.test2();
    }
}

运行结果

2023-08-29 10:53:12.760  INFO 348 --- [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO test  ( id, name, age )  VALUES  ( ?, ?, ? )
2023-08-29 10:53:12.760  INFO 348 --- [           main] ShardingSphere-SQL                       : SQLStatement: CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@5529ff44, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@14447be)
2023-08-29 10:53:12.760  INFO 348 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds0 ::: INSERT INTO test  ( id, name, age )  VALUES  ( ?, ?, ? )
2023-08-29 10:53:12.769  INFO 348 --- [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO test  ( id, name, age )  VALUES  ( ?, ?, ? )
2023-08-29 10:53:12.769  INFO 348 --- [           main] ShardingSphere-SQL                       : SQLStatement: CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@5529ff44, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@6393bf8b)
2023-08-29 10:53:12.769  INFO 348 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds0 ::: INSERT INTO test  ( id, name, age )  VALUES  ( ?, ?, ? )
2023-08-29 10:53:12.774  INFO 348 --- [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO test  ( id, name, age )  VALUES  ( ?, ?, ? )
2023-08-29 10:53:12.774  INFO 348 --- [           main] ShardingSphere-SQL                       : SQLStatement: CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@5529ff44, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@1f87607c)
2023-08-29 10:53:12.774  INFO 348 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds0 ::: INSERT INTO test  ( id, name, age )  VALUES  ( ?, ?, ? )
2023-08-29 10:53:12.777  INFO 348 --- [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO test  ( id, name, age )  VALUES  ( ?, ?, ? )
2023-08-29 10:53:12.777  INFO 348 --- [           main] ShardingSphere-SQL                       : SQLStatement: CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@5529ff44, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@6ddee60f)
2023-08-29 10:53:12.777  INFO 348 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds0 ::: INSERT INTO test  ( id, name, age )  VALUES  ( ?, ?, ? )
2023-08-29 10:53:12.779  INFO 348 --- [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO test  ( id, name, age )  VALUES  ( ?, ?, ? )
2023-08-29 10:53:12.779  INFO 348 --- [           main] ShardingSphere-SQL                       : SQLStatement: CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@5529ff44, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@4aa2877c)
2023-08-29 10:53:12.779  INFO 348 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds0 ::: INSERT INTO test  ( id, name, age )  VALUES  ( ?, ?, ? )
2023-08-29 10:53:12.782  INFO 348 --- [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO test  ( id, name, age )  VALUES  ( ?, ?, ? )
2023-08-29 10:53:12.782  INFO 348 --- [           main] ShardingSphere-SQL                       : SQLStatement: CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@5529ff44, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@ec7b5de)
2023-08-29 10:53:12.782  INFO 348 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds0 ::: INSERT INTO test  ( id, name, age )  VALUES  ( ?, ?, ? )
2023-08-29 10:53:12.784  INFO 348 --- [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO test  ( id, name, age )  VALUES  ( ?, ?, ? )
2023-08-29 10:53:12.785  INFO 348 --- [           main] ShardingSphere-SQL                       : SQLStatement: CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@5529ff44, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@373e6b9d)
2023-08-29 10:53:12.785  INFO 348 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds0 ::: INSERT INTO test  ( id, name, age )  VALUES  ( ?, ?, ? )
2023-08-29 10:53:12.787  INFO 348 --- [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO test  ( id, name, age )  VALUES  ( ?, ?, ? )
2023-08-29 10:53:12.787  INFO 348 --- [           main] ShardingSphere-SQL                       : SQLStatement: CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@5529ff44, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@10244722)
2023-08-29 10:53:12.787  INFO 348 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds0 ::: INSERT INTO test  ( id, name, age )  VALUES  ( ?, ?, ? )
2023-08-29 10:53:12.795  INFO 348 --- [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO test  ( id, name, age )  VALUES  ( ?, ?, ? )
2023-08-29 10:53:12.795  INFO 348 --- [           main] ShardingSphere-SQL                       : SQLStatement: CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@5529ff44, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@33bb3f86)
2023-08-29 10:53:12.795  INFO 348 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds0 ::: INSERT INTO test  ( id, name, age )  VALUES  ( ?, ?, ? )
2023-08-29 10:53:12.797  INFO 348 --- [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO test  ( id, name, age )  VALUES  ( ?, ?, ? )
2023-08-29 10:53:12.797  INFO 348 --- [           main] ShardingSphere-SQL                       : SQLStatement: CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@5529ff44, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@6ac4c3f7)
2023-08-29 10:53:12.797  INFO 348 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds0 ::: INSERT INTO test  ( id, name, age )  VALUES  ( ?, ?, ? )
2023-08-29 10:53:12.882  INFO 348 --- [           main] ShardingSphere-SQL                       : Logic SQL: SELECT  id,name,age  FROM test
2023-08-29 10:53:12.882  INFO 348 --- [           main] ShardingSphere-SQL                       : SQLStatement: SelectStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.SelectStatement@741741d0, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@5f9f3e58), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@5f9f3e58, projectionsContext=ProjectionsContext(startIndex=8, stopIndex=18, distinctRow=false, projections=[ColumnProjection(owner=null, name=id, alias=Optional.empty), ColumnProjection(owner=null, name=name, alias=Optional.empty), ColumnProjection(owner=null, name=age, alias=Optional.empty)]), groupByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.groupby.GroupByContext@3c19592c, orderByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.orderby.OrderByContext@60e1d87c, paginationContext=org.apache.shardingsphere.sql.parser.binder.segment.select.pagination.PaginationContext@2eb60c71, containsSubquery=false)
2023-08-29 10:53:12.883  INFO 348 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds1 ::: SELECT  id,name,age  FROM test
[Test(id=1, name=TEST1, age=null)]
2023-08-29 10:53:12.889  INFO 348 --- [           main] ShardingSphere-SQL                       : Logic SQL: SELECT  id,name,age  FROM test
2023-08-29 10:53:12.889  INFO 348 --- [           main] ShardingSphere-SQL                       : SQLStatement: SelectStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.SelectStatement@741741d0, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@76437e9b), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@76437e9b, projectionsContext=ProjectionsContext(startIndex=8, stopIndex=18, distinctRow=false, projections=[ColumnProjection(owner=null, name=id, alias=Optional.empty), ColumnProjection(owner=null, name=name, alias=Optional.empty), ColumnProjection(owner=null, name=age, alias=Optional.empty)]), groupByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.groupby.GroupByContext@236ae13d, orderByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.orderby.OrderByContext@193eb1ba, paginationContext=org.apache.shardingsphere.sql.parser.binder.segment.select.pagination.PaginationContext@2be818da, containsSubquery=false)
2023-08-29 10:53:12.889  INFO 348 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds2 ::: SELECT  id,name,age  FROM test
[Test(id=2, name=TEST2, age=null)]

Menu

Share

基于shardingJDBC的读写分离

一、读写分离的好处

二、基于mybatisPlus 和 shardingJDBC的实现

JAVA SpringBoot Spring AI 对接 OLLAMA，实现流式返回和function calling

XXL-JOB环境搭建以及简单使用

基于llama-factory微调LLAMA3-8B-CHINESE

基于OLLAMA部署大模型（Qwen2.5）

elasticsearch的安装于索引

certbot + 阿里云+自动DNS验证的永久免费证书（可泛型）

基于shardingJDBC的读写分离

jenkins部署东方通应用

基于LLAMA.CPP量化部署大模型

elasticsearch mapping