09-29 10:21 阅读 114

ShardingJDBC实现水平分库、水平分表

ShardingJdbc是一个轻量级的java框架，是增强版的JDBC
在完成了分库分表之后，使用shardingJDBC进行数据读取
ShardingJDBC作用：简化在分库分表之后对数据库的操作
水平分库/分表与垂直分库分表的区别

6ebdf2b10a93ab01ff35df9f55002ea.jpg
model

public class Course {
    // 课程主键
    private Long cid;
    private String cname;
     // 用户主键
    private long userId;
    private int cstatus;}

# Target

# 水平分表

表1: course_1
表2: course_2
数据入表规则:

cid为偶数则数据入表course_1
cid为奇数则数据入表course_2

# 水平分库

库1: es_spark
库2: sharding_sphere_2
数据入库规则:

user_id为偶数的入库 es_spark
user_id为奇数的入库 sharding_sphere_2

# 依赖

springboot
mybatis-plus
sharding-jdbc

 <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.4.3.2</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.apache.shardingsphere/sharding-jdbc-spring-boot-starter -->
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>4.1.1</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-configuration-processor</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
    </dependencies>

# 数据源，分库分表规则配置

参考： https://shardingsphere.apache.org/document/current/cn/user-manual/shardingsphere-jdbc/configuration/spring-boot-starter/sharding
语法:

可使用 ${ expression }或$->{ expression }标识行表达式
${begin..end} 表示范围区间
${[unit1, unit2, unit_x]} 表示枚举值
${['online', 'offline']}_table${1..3} 将取笛卡尔积

ShardingJDBC内置的主键生成器

能够保证不同进程主键的不重复性，以及相同进程主键的有序性
SNOWFLAKE 雪花算法
UUID UUID.randomUUID()

# 数据源别名spring.shardingsphere.datasource.names=m1,m2# m1 数据源的具体配置spring.shardingsphere.datasource.m1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m1.jdbc-url=jdbc:mysql://xxx/es_spark
spring.shardingsphere.datasource.m1.username=x
spring.shardingsphere.datasource.m1.password=x# m2 数据源配置spring.shardingsphere.datasource.m2.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.m2.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m2.jdbc-url=jdbc:mysql://xxx:3361/sharding_sphere_2
spring.shardingsphere.datasource.m2.username=x
spring.shardingsphere.datasource.m2.password=x# 数据库的分布情况和表的分布情况 数据库.表，笛卡尔积spring.shardingsphere.sharding.tables.course.actual-data-nodes=m$->{1..2}.course_$->{1..2}# 主键的生成策略spring.shardingsphere.sharding.tables.course.key-generator.column=cid
spring.shardingsphere.sharding.tables.course.key-generator.type=SNOWFLAKE# 指定数据库的分片策略  默认的 对所有的数据的规则spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression=m$->{user_id % 2+1}# 对具体某张表的分库规则spring.shardingsphere.sharding.tables.course.database-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.tables.course.database-strategy.inline.algorithm-expression=m$->{user_id % 2+1}# 指定表的分片策略，如：cid是奇数存储到course_1，cid是偶数存储到course_2spring.shardingsphere.sharding.tables.course.table-strategy.inline.sharding-column=cid
spring.shardingsphere.sharding.tables.course.table-strategy.inline.algorithm-expression=course_$->{cid % 2+1}# 输出日志spring.shardingsphere.props.sql.show=true# 一个实体类对应两张表#spring.main.allow-bean-definition-overriding=true

# 测试

/**
 * @author futao@mysteel.com
 * @date 2021/8/31
 */@RestController@RequestMapping("/course")public class CourseController {

    @Resource
    private CourseMapper courseMapper;

    @PostMapping("/add")
    public void add() {
        for (int i = 0; i < 100; i++) {
            Course course = new Course();
            course.setCname("语文");
            course.setCstatus(i);
            course.setUserId(i);
            courseMapper.insert(course);
        }
    }

    @GetMapping()
    public Course find(Long cid) {
        return courseMapper.selectOne(Wrappers.<Course>lambdaQuery().eq(Course::getCid, cid));
    }}