【MySQL高性能】1.ShardingSphere-Jdbc搭建读写分离

简介

Sharding-JDBC是当当网2016年开源的数据库中间件，可用于读写分离和分片。3.x版本更名成ShardingSphere，2019年进入Apache基金会。它由Sharding-JDBC、Sharding-Proxy和Sharding-Sidecar（计划中）这3款相互独立的产品组成。他们均提供标准化的数据分片、分布式事务和数据库治理功能，可适用于如Java同构、异构语言、容器、云原生等各种多样化的应用场景

ShardingSphere Jdbc：轻量级Java框架，本文主要描述这块。

ShardingSphere Proxy：数据库代理端，类似MyCat。

ShardingSphere Sidecar：云原生数据库代理。

参考文献：

ShardingJdbc 官网：https://shardingsphere.apache.org/index_zh.html

ShardingSphere Jdbc与MyCat的区别

ShardingSphere Jdbc是数据库客户端框架，而MyCat是数据库服务。本质在于MyCat需要额外安装中间件，ShardingSphere Jdbc是开发代码层面，就好比SpringCloud负载均衡和Nginx负载均衡的区别。

与SpringBoot整合读写分离

读写分离需要建立在MySQL主从复制的基础上，上遍已经有详细的教程。

主节点：192.168.126.156:3306

从节点：192.168.126.156:3307

pom.xml配置

	<dependencies>
        <dependency>
            <groupId>org.springframework.bootgroupId>
            <artifactId>spring-boot-starter-webartifactId>
        dependency>
        
        <dependency>
            <groupId>org.springframework.bootgroupId>
            <artifactId>spring-boot-starter-testartifactId>
        dependency>
        
        <dependency>
            <groupId>org.apache.shardingspheregroupId>
            <artifactId>shardingsphere-jdbc-core-spring-boot-starterartifactId>
            <version>5.1.0version>
        dependency>
        
        <dependency>
            <groupId>org.springframework.bootgroupId>
            <artifactId>spring-boot-starter-jdbcartifactId>
        dependency>
        
        <dependency>
            <groupId>mysqlgroupId>
            <artifactId>mysql-connector-javaartifactId>
        dependency>
        <dependency>
            <groupId>org.projectlombokgroupId>
            <artifactId>lombokartifactId>
        dependency>
    dependencies>

application.yml

spring:
  shardingsphere:
    datasource:
      # 数据源名称，多数据源以逗号分隔
      names: master-write,slave-read
      master-write:
        # 数据库配置
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://192.168.126.156:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai
        username: root
        password: 123456
      slave-read:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://192.168.126.156:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai
        username: root
        password: 123456
    # 规则配置
    rules:
      readwrite-splitting:
        load-balancers:
          ms:
            type: ROUND_ROBIN
            props:
              workId: 1
        data-sources:
          ms:
            type: Static
            load-balancer-name: round-robin
            props:
              # 主节点写
              write-data-source-name: master-write
              # 从节点读
              read-data-source-names: slave-read
    # 属性配置
    props:
      # 展示修改以后的sql语句
      sql-show: true

测试类

import com.terry.App;
import lombok.extern.java.Log;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.jdbc.core.JdbcTemplate;

import java.util.List;
import java.util.Map;

/**
 * ShardingSphere-Jdbc 增删改查测试
 * @author terry
 * @version 1.0
 * @date 2022/4/25 23:01
 */
@SpringBootTest(classes = App.class)
@Log
public class TestShardingSphereJdbc {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Test
    public void testQuery(){
        List<Map<String, Object>> maps = jdbcTemplate.queryForList(" SELECT * FROM t_user ");
        log.info("数据库数据：" + maps);
    }

    @Test
    public void testInsert(){
        jdbcTemplate.update("INSERT INTO t_user(name) VALUES(?)", "张三");
    }
}

启动类

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
/**
 * shardingsphere jdbc测试
 * @author terry
 * @version 1.0
 * @date 2022/5/14 15:40
 */
@SpringBootApplication
public class App {
    public static void main(String[] args) {
        SpringApplication.run(App.class, args);
    }
}

运行打印输出：

testInsert:
Actual SQL: master-write ::: INSERT INTO t_user(name) VALUES(?) ::: [张三]

testQuery:
Actual SQL: slave-read :::  SELECT * FROM t_user 
 数据库数据：[{id=1, name=null}, {id=2, name=null}, {id=3, name=张三}]

可以看到读走从库，写走主库。