Sharding-JDBC实现分库分表+读写分离

1. 环境涉及的各个软件及版本

• centos-7.6
• mysql-5.7.31
• shardingsphere-4.1.0

2. 各个机器对应角色&作用&ip地址

角色	作用	ip
master1	mysql集群主节点1，负责写数据	192.168.8.160
master2	mysql集群主节点2，负责写数据	192.168.8.161
Slave1	mysql集群从节点，对应master1，负责读数据	192.168.8.162
Slave2	mysql集群从节点，对应master1，负责读数据	192.168.8.163
Slave3	mysql集群从节点，对应master2，负责读数据	192.168.8.164
Slave4	mysql集群从节点，对应master2，负责读数据	192.168.8.165

3. mysql主从复制环境搭建

参考：https://blog.csdn.net/rzpy_qifengxiaoyue/article/details/108665569

4. 使用Sharding-JDBC实现分库分表+读写分离

4.1 新建项目，引入pom依赖

<dependencies>

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

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

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

    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.48</version>
    </dependency>

    <dependency>
        <groupId>org.apache.shardingsphere</groupId>
        <artifactId>sharding-transaction-xa-core</artifactId>
        <version>${shardingsphere.version}</version>
    </dependency>

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

    <!--简化Java常用操作-->
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <version>1.18.8</version>
    </dependency>

</dependencies>

4.2 新建数据表及操作dao(基于jpa)

@Data
@Entity
@Table(name = "c_order")
public class COrder implements Serializable {

    @Id
    @Column(name = "id")
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long id;

    @Column(name = "is_del", columnDefinition = "COMMENT '是否被删除'")
    private Boolean isDel;

    @Column(name = "user_id", columnDefinition = "COMMENT '用户id'")
    private Integer userId;

    @Column(name = "company_id", columnDefinition = "COMMENT '公司id'")
    private Integer companyId;

    @Column(name = "publish_user_id", columnDefinition = "COMMENT 'B端⽤用户id'")
    private Integer publishUserId;

    @Column(name = "position_id", columnDefinition = "COMMENT '职位ID'")
    private long positionId;

    @Column(name = "resume_type", columnDefinition = "COMMENT '简历类型:0附件 1在线'")
    private Integer resumeType;

    @Column(name = "status", columnDefinition = "COMMENT '投递状态 WAIT-待处理理 AUTO_FILTER-⾃自动过滤 PREPARE_CONTACT-待沟通 REFUSE-拒绝 ARRANGE_INTERVIEW-通知⾯面试'")
    private String status;

    @Column(name = "create_time", columnDefinition = "COMMENT '创建时间'")
    private Date createTime;

    @Column(name = "update_time", columnDefinition = "COMMENT '处理时间'")
    private Date updateTime;
}

import com.rpp.entity.COrder;
import org.springframework.data.jpa.repository.JpaRepository;

public interface COrderRepository extends JpaRepository<COrder,Long> {
}

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

4.3 数据分片及读写分离配置

spring.shardingsphere.props.sql.show=true
#数据节点
spring.shardingsphere.datasource.names=master1,slave1,slave2,master2,slave3,slave4
#数据源配置
spring.shardingsphere.datasource.master1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.master1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.master1.jdbc-url=jdbc:mysql://192.168.8.160:3306/test
spring.shardingsphere.datasource.master1.username=root
spring.shardingsphere.datasource.master1.password=root

spring.shardingsphere.datasource.slave1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.slave1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.slave1.jdbc-url=jdbc:mysql://192.168.8.162:3306/test
spring.shardingsphere.datasource.slave1.username=root
spring.shardingsphere.datasource.slave1.password=root

spring.shardingsphere.datasource.slave2.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.slave2.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.slave2.jdbc-url=jdbc:mysql://192.168.8.163:3306/test
spring.shardingsphere.datasource.slave2.username=root
spring.shardingsphere.datasource.slave2.password=root

spring.shardingsphere.datasource.master2.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.master2.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.master2.jdbc-url=jdbc:mysql://192.168.8.161:3306/test
spring.shardingsphere.datasource.master2.username=root
spring.shardingsphere.datasource.master2.password=root

spring.shardingsphere.datasource.slave3.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.slave3.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.slave3.jdbc-url=jdbc:mysql://192.168.8.164:3306/test
spring.shardingsphere.datasource.slave3.username=root
spring.shardingsphere.datasource.slave3.password=root

spring.shardingsphere.datasource.slave4.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.slave4.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.slave4.jdbc-url=jdbc:mysql://192.168.8.165:3306/test
spring.shardingsphere.datasource.slave4.username=root
spring.shardingsphere.datasource.slave4.password=root

# 库分片规则
spring.shardingsphere.sharding.tables.c_order.database-strategy.inline.sharding-column=company_id
spring.shardingsphere.sharding.tables.c_order.database-strategy.inline.algorithm-expression=ds$->{company_id % 2}
# 真实数据节点
spring.shardingsphere.sharding.tables.c_order.actual-data-nodes=ds$->{0..1}.c_order$->{0..1}
# 表分片规则
spring.shardingsphere.sharding.tables.c_order.table-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.tables.c_order.table-strategy.inline.algorithm-expression=c_order$->{user_id % 2}
# 主键生成策略
spring.shardingsphere.sharding.tables.c_order.key-generator.column=id
spring.shardingsphere.sharding.tables.c_order.key-generator.type=SNOWFLAKE
#主从配置, 两个主库，每个主库分别对应两个从库
spring.shardingsphere.sharding.master-slave-rules.ds0.master-data-source-name=master1
spring.shardingsphere.sharding.master-slave-rules.ds0.slave-data-source-names=slave1, slave2
spring.shardingsphere.sharding.master-slave-rules.ds1.master-data-source-name=master2
spring.shardingsphere.sharding.master-slave-rules.ds1.slave-data-source-names=slave3, slave4

4.4 测试

• 测试之前需要根据上述配置在六个mysql服务上新建test库及c_order0，c_order1表，因为Sharding-JDBC只是帮助我们实现往哪个库哪张表写数据或者读数据，库和表需要我们自己建立。因为分库及分表都是拿2取模，所以需要建立两张表。

• 编写测试类

@RunWith(SpringRunner.class)
@SpringBootTest(classes = TestApplication.class)
public class TestShardingDatabase {

    @Autowired
    COrderRepository cOrderRepository;

    @Test
    public void testAdd() {
        for (int i = 1; i <= 20; i++) {

            Random random = new Random();
            int companyId = random.nextInt(100);
            int userId = random.nextInt(100);

            COrder order = new COrder();
            order.setIsDel(false);
            order.setCompanyId(companyId);
            order.setUserId(userId);
            order.setPositionId(3242342);
            order.setPublishUserId(1111);
            order.setResumeType(1);
            order.setStatus("AUTO");
            order.setCreateTime(new Date());
            order.setUpdateTime(new Date());
            cOrderRepository.save(order);
        }
    }

    @Test
    public void testFind() {
        List<COrder> list = cOrderRepository.findAll();
        list.forEach(order -> {
            System.out.println(order.toString());
        });
    }

}

• 测试写数据
  根据上面示例，循环写入20条数据，根据company_id%2分库，user_id%2分表，正确的结果应该company_id是偶数则分到master1,奇数分到master2，user_id是偶数则分到c_order0,奇数分到c_order1，测试数据如下

从库的数据次数只贴一个

数据从哪写入可以看一下sql执行日志，如下

从上面可以看出，分库分表，主从复制没有问题，以及数据写入也没有问题。下面测试从哪读取，以验证读写分离。可以通过查看sql日志，或者把从库的数据改成跟主库不一致，看看是不是从slave读取的数据。

• 测试读数据

从上面的日志可以明显看出是从slave读取的数据，读写分离没有问题。