Springboot2.x +JPA 集成 Apache ShardingSphere 分表+读写分离

分库分表背景:
数据库性能瓶颈：主要分为按照业务来划分或者按照数据量来划分。
拆分方式：
水平拆分(每个表的结构都一样)：订单表数据量大，我们可以水平拆分 ，分成order表1、order表2、order表3 。。。
垂直拆分：一个多字段的表拆分成多个表
例如：order订单表和oderItem订单详情表
一个订单会购买多件商品，因此，订单order表中会只有一条数据，orderItem订单项表会对应这个订单购买的多件商品。

文章目录

一、基础准备

1. 技术选型

组件/框架	版本
spring-boot	2.4.3
jpa	2.4.3
shardingsphere	5.0.0-alpha
mysql	5.7.3
hikari	3.4.5

2. 搭建mysql主从复制服务器

​​基于Docker的Mysql主从复制搭建_mysql5.7.x​​

1. 引入 Maven 依赖

<dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
            <version>5.0.0-alpha</version>
        </dependency>

2. 规则配置

spring.shardingsphere.datasource.names=primary-ds,replica-ds-0

spring.shardingsphere.datasource.common.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.common.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.common.username=root
spring.shardingsphere.datasource.common.password=123456

spring.shardingsphere.datasource.primary-ds.jdbc-url=jdbc:mysql://192.168.43.202:3339/ds0?serverTimezone=UTC&useSSL=false
spring.shardingsphere.datasource.replica-ds-0.jdbc-url=jdbc:mysql://192.168.43.202:3340/ds0?serverTimezone=UTC&useSSL=false


spring.shardingsphere.rules.replica-query.data-sources.ds0.primary-data-source-name=primary-ds
spring.shardingsphere.rules.replica-query.data-sources.ds0.replica-data-source-names=replica-ds-0
spring.shardingsphere.rules.replica-query.data-sources.ds0.load-balancer-name=round-robin

# 负载均衡算法配置
spring.shardingsphere.rules.replica-query.load-balancers.round-robin.type=ROUND_ROBIN
spring.shardingsphere.rules.replica-query.load-balancers.round-robin.props.default=0

spring.shardingsphere.props.sql-show=true

# 配置 t_order 表规则
spring.shardingsphere.rules.sharding.tables.t_order.actual-data-nodes=ds0.t_order_$->{0..1}

# 配置分表策略
spring.shardingsphere.rules.sharding.tables.t_order.table-strategy.standard.sharding-column=order_id
spring.shardingsphere.rules.sharding.tables.t_order.table-strategy.standard.sharding-algorithm-name=table-inline

# 配置 分片算法
spring.shardingsphere.rules.sharding.sharding-algorithms.table-inline.type=INLINE
spring.shardingsphere.rules.sharding.sharding-algorithms.table-inline.props.algorithm-expression=t_order_$->{order_id % 2}

# 分布式序列策略配置
spring.shardingsphere.rules.sharding.tables.t_order.key-generate-strategy.column=order_id
spring.shardingsphere.rules.sharding.tables.t_order.key-generate-strategy.key-generator-name=snowflake

# 分布式序列算法配置
spring.shardingsphere.rules.sharding.key-generators.snowflake.type=SNOWFLAKE
spring.shardingsphere.rules.sharding.key-generators.snowflake.props.worker-id=123

3. 实体

package com.gblfy.distributedsharding.entity;

import lombok.Data;

import javax.persistence.*;

@Data
@Entity
@Table(name = "t_order")
public class OrderEntity {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long orderId;

    private Integer userId;
}

4. 接口

package com.gblfy.distributedsharding.mapper;

import com.gblfy.distributedsharding.entity.OrderEntity;
import org.springframework.data.jpa.repository.JpaRepository;

import java.util.List;

public interface OrderMapper extends JpaRepository<OrderEntity, Long> {

    OrderEntity findByOrderId(Long orderId);

    List<OrderEntity> findByUserId(Integer userId);
}

5. 表结构

在master主库执行

CREATE DATABASE ds0;
use ds0;
CREATE TABLE `t_order_0` (
  `order_id` bigint(20) unsigned NOT NULL,
  `user_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `t_order_1` (
  `order_id` bigint(20) unsigned NOT NULL,
  `user_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

6. 测试类

package com.gblfy.distributedsharding;

import com.gblfy.distributedsharding.entity.OrderEntity;
import com.gblfy.distributedsharding.mapper.OrderMapper;
import org.apache.shardingsphere.infra.hint.HintManager;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.util.Random;

@SpringBootTest
class DistributedShardingApplicationTests {

    @Autowired
    private OrderMapper orderMapper;

    @Test
    void insert() {
       for (int i = 0; i <20 ; i++) {
            OrderEntity entity = new OrderEntity();
            entity.setUserId(new Random().nextInt(999));
            orderMapper.save(entity);
        }
    }

    @Test
    void findByOrderId() {
        //在主库负责增删改查 从库负责查询 场景中 ，存在刚把数据写入主库中，
        // 为来得急同步从库，因此会导致从库没有查询的数据，但实际，数据在主库是存在的，
        // 从库负责查询只是为了环节主库的数据库查询的压力，因此，在特殊场景，需要从主库
        // 查询数据，可以通过配置五主库中查询数据
        HintManager.getInstance().setPrimaryRouteOnly();
        orderMapper.findByOrderId(570271967295811584L);
    }

    @Test
    void findByUserId() {
        orderMapper.findByUserId(556);
    }

    @Test
    void updateByOrderId() {
        OrderEntity byOrderId = orderMapper.findByOrderId(570279923689172992L);
        byOrderId.setUserId(1000);
        orderMapper.save(byOrderId);
    }
}

7. 完整pom

<dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
            <version>5.0.0-alpha</version>
        </dependency>

从库查询