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));
    }
}

• user_id为偶数入库es_spark，cid为偶数入course_1

  
  image.png

• user_id为偶数入库es_spark，cid为奇数入course_2

  
  image.png

• user_id为奇数入库sharding_sphere_2，cid为偶数入course_1

  
  image.png

• user_id为奇数入库sharding_sphere_2，cid为奇数入course_2

  
  image.png

# Q:

• 分库分表之后如何分页查询