ShardingSphere学习之水平分表

我爱海鲸 2022-12-30 17:59:03 ShardingSphere、分库分表

简介ShardingSphere、分库、分表

1、官方文档：https://shardingsphere.apache.org/document/current/cn/user-manual/shardingsphere-jdbc/yaml-config/rules/sharding/

2、在maven中搜索相关的依赖包，https://mvnrepository.com/

<!-- https://mvnrepository.com/artifact/org.apache.shardingsphere/shardingsphere-jdbc-core-spring-boot-starter -->
<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
    <version>5.2.1</version>
</dependency>

3、完整的项目截图：

4、完整的pom文件：

<?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 http://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>
        <version>2.6.0</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>

    <groupId>xyz.haijin.sharding</groupId>
    <artifactId>shardingDemo</artifactId>
    <version>1.0-SNAPSHOT</version>

    <properties>
        <mybatis-plus.version>3.5.2</mybatis-plus.version>
        <java.version>1.8</java.version>
        <msbs.version>2.2.0</msbs.version>
        <druid.version>1.2.8</druid.version>
        <mysql.version>8.0.29</mysql.version>
        <lombok.version>1.18.22</lombok.version>
        <logback.version>1.2.3</logback.version>
        <springboot.version>2.6.0</springboot.version>
        <shardingsphereSpringboot.version>5.2.1</shardingsphereSpringboot.version>
        <shardingsphere.version>5.3.0</shardingsphere.version>
        <snakeyaml.version>1.33</snakeyaml.version>
    </properties>

    <dependencies>

        <!-- https://mvnrepository.com/artifact/org.apache.shardingsphere/shardingsphere-jdbc-core-spring-boot-starter -->
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
            <version>${shardingsphereSpringboot.version}</version>
        </dependency>

        <!-- 如果不引入这个依赖可能会导致报错 -->
        <dependency>
            <groupId>org.yaml</groupId>
            <artifactId>snakeyaml</artifactId>
            <version>${snakeyaml.version}</version>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
            <version>${springboot.version}</version>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <version>${springboot.version}</version>
            <scope>test</scope>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
            <version>${springboot.version}</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/com.alibaba/druid -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>${druid.version}</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>${mysql.version}</version>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>${msbs.version}</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/com.baomidou/mybatis-plus-boot-starter -->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>${mybatis-plus.version}</version>
        </dependency>

        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-generator</artifactId>
            <version>${mybatis-plus.version}</version>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>${lombok.version}</version>
        </dependency>

        <!-- Logback依赖，还会传递 slf4j 和 logback-core -->
        <dependency>
            <groupId>ch.qos.logback</groupId>
            <artifactId>logback-classic</artifactId>
            <version>${logback.version}</version>
        </dependency>
    </dependencies>



</project>

5、创建数据库course_db，sql如下：

CREATE TABLE `course_1` (
  `cid` bigint(20) NOT NULL,
  `cname` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `user_id` bigint(20) DEFAULT NULL,
  `cstatus` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  PRIMARY KEY (`cid`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `course_2` (
  `cid` bigint(20) NOT NULL,
  `cname` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `user_id` bigint(20) DEFAULT NULL,
  `cstatus` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  PRIMARY KEY (`cid`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

6、application.yaml配置文件，如下：

spring:
  main:
    allow-bean-definition-overriding: true
  shardingsphere:
    datasource:
      # 配置数据源，给数据源起名称,多个数据源时使用逗号分隔
      names: m1
      m1:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://192.168.152.141:3306/course_db?useUnicode=true&characterEncoding=utf8
        username: root
        password: 123456
      #默认数据源，未分片的表默认执行库
      sharding:
        default-data-source-name: m1
    rules:
      sharding:
        # 分片算法配置
        shardingAlgorithms:
          # 这里对应shardingAlgorithmName: table-inline
          table-inline:
            type: INLINE
            props:
              sharding-count: 10
              # cid为奇数时将数据存到course_2这张表中，当cid为偶数时，将数据存到course_1这种表中
              algorithm-expression: course_$->{cid % 2 + 1}
        # 分布式序列算法配置
        keyGenerators:
          # 这里对应keyGeneratorName: snowflake
          snowflake:
            type: SNOWFLAKE
        tables:
          # 表名
          course:
            # 数据表名
            actualDataNodes: m1.course_$->{1..2}
            tableStrategy:
              standard:
                shardingColumn: cid
                shardingAlgorithmName: table-inline
            keyGenerateStrategy:
              column: order_id
              keyGeneratorName: snowflake
    # 打开sql输出日志
    props:
      sql-show: true
logging:
  config: classpath:logback-spring.xml
  level:
    org:
      springframework:
        boot:
          autoconfigure:
            logging: INFO

server:
  port: 8082

mybatis-plus:
  mapper-locations: classpath:xml/*.xml
#  configuration:
#    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

7、logback-spring.xml配置，如下：

<?xml version="1.0" encoding="UTF-8"?>
<!-- scan:当此属性设置为true时，配置文件如果发生改变，将会被重新加载，默认值为true -->
<!-- scanPeriod:设置监测配置文件是否有修改的时间间隔，如果没有给出时间单位，默认单位是毫秒。当scan为true时，此属性生效。
	 默认的时间间隔为1分钟。 -->
<!-- debug:当此属性设置为true时，将打印出logback内部日志信息，实时查看logback运行状态。默认值为false。 -->
<configuration  scan="true" scanPeriod="60 seconds" debug="true">

    <property name="CONSOLE_LOG_PATTERN"
              value="%d{yyyy-MM-dd HH:mm:ss.SSS} |-[%-5p] in %logger.%M[line-%L] -%m%n"/>
    <property name="log.path" value="D:/log" />

    <!-- 输出到控制台 -->
    <appender name="CONSOLE" class="ch.qos.logback.core.ConsoleAppender">
        <!-- Threshold=即最低日志级别，此appender输出大于等于对应级别的日志
             (当然还要满足root中定义的最低级别)
        -->
        <filter class="ch.qos.logback.classic.filter.ThresholdFilter">
            <level>info</level>
        </filter>

        <encoder>
            <!-- 日志格式(引用变量) -->
            <Pattern>${CONSOLE_LOG_PATTERN}</Pattern>
            <!-- 设置字符集 -->
            <charset>UTF-8</charset>
        </encoder>
    </appender>
    <root level="info">
        <appender-ref ref="CONSOLE"/>
    </root>
</configuration>

8、Course类：

package xyz.haijin.sharding.entity;

import com.baomidou.mybatisplus.annotation.TableId;
import lombok.Data;

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

9、CourseMapper类：

package xyz.haijin.sharding.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.springframework.stereotype.Repository;
import xyz.haijin.sharding.entity.Course;

@Repository
public interface CourseMapper extends BaseMapper<Course> {

}

10、ICourseService类：

package xyz.haijin.sharding.service;

import com.baomidou.mybatisplus.extension.service.IService;
import xyz.haijin.sharding.entity.Course;

/**
 * @auther haijin
 * @create 2022-12-30 12:15:46
 * @describe 服务类
 */
public interface ICourseService extends IService<Course> {

        }

11、CourseServiceImpl类：

package xyz.haijin.sharding.service.impl;

import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import org.springframework.stereotype.Service;
import xyz.haijin.sharding.entity.Course;
import xyz.haijin.sharding.mapper.CourseMapper;
import xyz.haijin.sharding.service.ICourseService;

/**
 * @auther haijin
 * @create 2022-12-30 12:15:46
 * @describe 服务实现类
 */
@Service
        public class CourseServiceImpl extends ServiceImpl<CourseMapper, Course> implements ICourseService {

        }

12、ShardingjdbcdemoApplication类：

package xyz.haijin.sharding;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
@MapperScan("xyz.haijin.sharding.mapper")
public class ShardingjdbcdemoApplication {

    public static void main(String[] args) {
        SpringApplication.run(ShardingjdbcdemoApplication.class, args);
    }

}

13、ShardingjdbcdemoApplicationTests 接下来就是测试的重点了，我们一个一个来：

先查看完整的类：

package xyz.haijin.sharding;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import lombok.extern.slf4j.Slf4j;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import xyz.haijin.sharding.entity.Course;
import xyz.haijin.sharding.mapper.CourseMapper;
import xyz.haijin.sharding.service.ICourseService;

import java.util.ArrayList;
import java.util.List;

@SpringBootTest(classes = ShardingjdbcdemoApplication.class)
@Slf4j
public class ShardingjdbcdemoApplicationTests {

    //注入mapper
    @Autowired
    private CourseMapper courseMapper;

    @Autowired
    private ICourseService courseService;

}

1）首先我们测试一条数据：

  @Test
    public void addCourseDb() {
        Course course = new Course();
        course.setCname("test");
        course.setUserId(111L);
        course.setCstatus("normal");
        courseMapper.insert(course);
    }

插入的结果：

从图中我们可以看到，主键为偶数，所以数据最后插入的表是course_1的表

2）我们根据id查询一条数据

    @Test
    public void findCourseDb() {
        QueryWrapper<Course>  wrapper = new QueryWrapper<>();
        //设置cid值
        wrapper.eq("cid",1608687771247972355L);
        Course course = courseMapper.selectOne(wrapper);
        System.out.println(course);
    }

如图：

3）批量插入数据

    @Test
    public void addCourse() {
        List<Course> list = new ArrayList<>();
        for(int i=1;i<=10;i++) {
            Course course = new Course();
            course.setCname("java"+i);
            course.setUserId(100L);
            course.setCstatus("Normal"+i);
            list.add(course);
        }
        courseService.saveBatch(list);
    }

如图：

4）查询其他的数据库

    public void findCourse() {
        QueryWrapper<Course>  wrapper = new QueryWrapper<>();
        wrapper.eq("cname","test");
        Course course = courseMapper.selectOne(wrapper);
        System.out.println(course);
    }

如图：

你好：我的2025

上一篇：ShardingSphere学习之简介

下一篇：ShardingSphere学习之分页

ShardingSphere、分库分表

最近更新

最近更新

最近更新

Copyright 2025 Inc. AllRights Reserved. Design by 我爱海鲸 湘ICP备19022671号-1