09-30 02:41 阅读 132

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_2
spring.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));
    }
}