使用Sharding—JDBC实现数据分片

使用Sharding-JDBC实现数据分片

注：有关sharding-jdbc这里我就不详细介绍了，可以去官网看相关的介绍

地址： https://shardingsphere.apache.org/document/current/cn/overview/
学习：https://www.bilibili.com/video/BV1LK411s7RX?p=8

1、搭建环境

​ （1）技术：SpringBoot2.2.1+MyBatisPlus+Sharding-JDBC+Druid连接池

​ （2）引入需要的依赖

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <!--Springboot的版本2.2.1-->
        <version>2.2.1.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.wl</groupId>
    <artifactId>shardingjdbcdemo</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>shardingjdbcdemo</name>
    <description>Demo project for Spring Boot</description>

    <properties>
        <java.version>1.8</java.version>
    </properties>
	<!--引入需要的依赖-->
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>

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

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.20</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.13</version>
        </dependency>
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>4.0.0-RC1</version>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.0.5</version>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

2、实现分库分表

创建两个数据库，每个数据库中的两张表，表结构是一样的

分库规则：userid为偶数添加edv_db_1数据库,为奇数添加到数据到edv_db_2

分表规则：cid为偶数添加course_1表,为奇数添加到数据到course_2

CREATE DATABASE `edv_db_1`

CREATE TABLE `course_1`  (
  `cid` bigint(20) NOT NULL,
  `cname` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `user_id` bigint(20) NOT NULL,
  `cstatus` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  PRIMARY KEY (`cid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
CREATE TABLE `course_2`  (
  `cid` bigint(20) NOT NULL,
  `cname` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `user_id` bigint(20) NOT NULL,
  `cstatus` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  PRIMARY KEY (`cid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

CREATE DATABASE `edv_db_2`

CREATE TABLE `course_1`  (
  `cid` bigint(20) NOT NULL,
  `cname` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `user_id` bigint(20) NOT NULL,
  `cstatus` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  PRIMARY KEY (`cid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
CREATE TABLE `course_2`  (
  `cid` bigint(20) NOT NULL,
  `cname` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `user_id` bigint(20) NOT NULL,
  `cstatus` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  PRIMARY KEY (`cid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

3、编写代码实现对分库分表后数据的操作

创建实体类和映射类

@Data
public class Course {
    private Long cid;
    private String cname;
    private Long userId;
    private String cstatus;

}

使用mp操作

@Repository
public interface CourseMapper extends BaseMapper<Course> {

}

4、配置 Sharding-JDBC 分片策略

在项目 application.properties 配置文件中进行配置

#shardingjdbcd 的分篇策略
#配置数据源
spring.shardingsphere.datasource.names=ds0,ds1

#配置第一个数据源具体内容 包括连接池 驱动  地址 用户名和密码
spring.shardingsphere.datasource.ds0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds0.url=jdbc:mysql://localhost:3306/edv_db_1?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone = GMT
spring.shardingsphere.datasource.ds0.username=root
spring.shardingsphere.datasource.ds0.password=123456

#配置第二个数据源具体内容 包括连接池 驱动  地址 用户名和密码
spring.shardingsphere.datasource.ds1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds1.url=jdbc:mysql://localhost:3306/edv_db_2?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone = GMT
spring.shardingsphere.datasource.ds1.username=root
spring.shardingsphere.datasource.ds1.password=123456
        
#指定数据库的分布情况，数据库里面的表的分布情况
# ds0  ds1  course_1 course_2
spring.shardingsphere.sharding.tables.course.actual-data-nodes=ds$->{0..1}.course$->{1..2}

#course表中的主键生成规则
spring.shardingsphere.sharding.tables.course.key-generator.column=cid
spring.shardingsphere.sharding.tables.course.key-generator.type=SNOWFLAKE

#配置表的分片策略，如果cid 是偶数就存存入course1中 奇数就存course2中
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}

#配置数据库的分片策略，如果user_id是偶数就存存入dev_db_1中 奇数就存dev_db_2中
#spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column=user_id
#spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression=ds$->{user_id % 2}

spring.shardingsphere.sharding.tables.course.database-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.tables.course.database-strategy.inline.algorithm-expression=ds$->{user_id % 2}
#开启sql日志输出
spring.shardingsphere.props.sql.show=true

#一个实体类对应两张表 覆盖
spring.main.allow-bean-definition-overriding=true

5、编写测试代码

   	@Autowired
    private CourseMapper courseMapper;
	@Test
    void addCourseDb() {
      for (int i = 1; i <=10 ; i++) {
            Course course=new Course();
            course.setCname("javademo"+i);
            course.setCstatus("Normer1");
            //根据userid分库
            course.setUserId(100L+i);
            courseMapper.insert(course);
       }
    }
	@Test
    void queryCourseDb(){
        QueryWrapper<Course> wrapper =new QueryWrapper<>();
        wrapper.eq("cid",474502611626622977L);
        wrapper.eq("user_id",100L);
        Course course = courseMapper.selectOne(wrapper);
        System.out.println(course);

    }

6、测试结果