ShardingSphere学习

目录

ShardingSphere

分库分表的概念

ShardingSphere-JDBC

使用shardingsphere-jdbc实现垂直分表

水平分库

垂直分库

公共表

读写分离

---

Apache ShardingSphere 由 JDBC、Proxy 和 Sidecar（规划中）这 3 款既能够独立部署，又支持混合部署配合使用的产品组成。 它们均提供标准化的基于数据库作为存储节点的增量功能，可适用于如 Java 同构、异构语言、云原生等各种多样化的应用场景。

分库分表的概念

分库分表的两种方式：垂直切分和水平切分

垂直切分

垂直分表

将数据库中的一张表部分字段存储到一张表中，再把这张表中的另外一部分字段存储到另外一张表中，对数据列进行拆分

垂直分库

把单一数据库按照业务划分到不同的数据库，不同模块对应不同数据库

水平切分

水平分表

在同一数据库中，建立多张相同的表，表结构一致

水平分库

将数据库创建多个相同结构的数据库

ShardingSphere-JDBC

定位为轻量级 Java 框架，在 Java 的 JDBC 层提供的额外服务。 它使用客户端直连数据库，以 jar 包形式提供服务，无需额外部署和依赖，可理解为增强版的 JDBC 驱动，完全兼容 JDBC 和各种 ORM 框架。

• 适用于任何基于 JDBC 的 ORM 框架，如：JPA, Hibernate, Mybatis, Spring JDBC Template 或直接使用 JDBC；

• 支持任何第三方的数据库连接池，如：DBCP, C3P0, BoneCP, HikariCP 等；

• 支持任意实现 JDBC 规范的数据库，目前支持 MySQL，PostgreSQL，Oracle，SQLServer 以及任何可使用 JDBC 访问的数据库。

使用shardingsphere-jdbc实现垂直分表

docker安装mysql

• 查询镜像

  docker search mysql

• 获取最新镜像

  docker pull mysql:latest

• 查看镜像

  docker images

• 编写shell脚本mysql.sh

  #!/bin/bash
  docker run -itd --name mysql-test -p 3306:3306 -e MYSQL_ROOT_PASSWORD=root mysql

• 执行脚本

  ./mysql.sh

创建数据库与表结构

• 建表语句

  CREATE TABLE `student_0` (
    `id` bigint NOT NULL,
    `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
    `age` int NOT NULL,
    `address` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
  CREATE TABLE `student_1` (
    `id` bigint NOT NULL,
    `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
    `age` int NOT NULL,
    `address` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

创建项目

• 导入依赖

  <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-web</artifactId>
  </dependency>
  
  <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter</artifactId>
  </dependency>
  
  <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <scope>runtime</scope>
  </dependency>
  <dependency>
      <groupId>org.projectlombok</groupId>
      <artifactId>lombok</artifactId>
      <optional>true</optional>
  </dependency>
  <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-test</artifactId>
      <scope>test</scope>
  </dependency>
  <!-- https://mvnrepository.com/artifact/com.baomidou/mybatis-plus-boot-starter -->
  <dependency>
      <groupId>com.baomidou</groupId>
      <artifactId>mybatis-plus-boot-starter</artifactId>
      <version>3.4.3.4</version>
  </dependency>
  
  <dependency>
      <groupId>com.baomidou</groupId>
      <artifactId>mybatis-plus-generator</artifactId>
      <version>3.4.1</version>
  </dependency>
  
  <dependency>
      <groupId>org.freemarker</groupId>
      <artifactId>freemarker</artifactId>
      <version>2.3.31</version>
  </dependency>
  
  <dependency>
      <groupId>com.alibaba</groupId>
      <artifactId>druid</artifactId>
      <version>1.2.6</version>
  </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>

• 创建实体类与对应实体的mapper 文件     

@Data
@AllArgsConstructor
@NoArgsConstructor
@Accessors(chain = true)
public class Student {

    private static final long serialVersionUID = 1L;

    private Long id;

    private String name;

    private Integer age;

    private String address;


}

@Mapper
public interface StudentMapper extends BaseMapper<Student> {

}

• 配置文件进行配置

  spring:
    shardingsphere:
      datasource:
        names: ds1
        ds1:
          type: com.alibaba.druid.pool.DruidDataSource
          driverClassName: com.mysql.cj.jdbc.Driver
          url: jdbc:mysql://localhost:3306/shardingsphere?serverTimezone=GMT%2B8
          username: 
          password: 
      sharding:
        tables:
          student:
            actual-data-nodes: ds1.student_$->{0..1}
            key-generator:
              column: id
              type: SNOWFLAKE
            table-strategy:  #分表策略
              inline:
                sharding-column: age
                algorithm-expression: student_$->{age % 2} # age为奇数存储在student_1 为偶数存储在student_0

• 测试类

  @Test
  void contextLoads() {
      for (int i = 1; i < 11; i++) {
          studentMapper.insert(new Student().setName("zhangsan"+i).setAge(18+i).setAddress("beijing"+i));
      }
  }
  
  @Test
  void testSelect(){
  
      studentMapper.selectById(1469979914989998081L);
  
      studentMapper.selectById(1469979919918305282L);
  }

  

  

         

水平分库

创建数据库与表结构

配置文件

spring:
  shardingsphere:
    datasource:
      names: ds0,ds1   #配置多个数据源
      ds0:
        type: com.alibaba.druid.pool.DruidDataSource
        driverClassName: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://172.17.98.247:3306/shardingsphere?serverTimezone=GMT%2B8
        username: root
        password: root
      ds1:
        type: com.alibaba.druid.pool.DruidDataSource
        driverClassName: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://172.17.98.247:3306/shardingsphere1?serverTimezone=GMT%2B8
        username: root
        password: root
    sharding:
      tables:
        student:
          actual-data-nodes: ds$->{0..1}.student_$->{0..1}
          key-generator:
            column: id
            type: SNOWFLAKE
          database‐strategy:
            inline:
              sharding-column: id
              algorithm-expression: ds$->{id % 2} # id为奇数存储在ds1，偶数存储在ds0
            #分表策略
          table-strategy:
            inline:
              sharding-column: age
              algorithm-expression: student_$->{age % 2} # age为奇数存储在student_1 为偶数存储在student_0

垂直分库

创建数据库与表结构

CREATE DATABASE `user_db` CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';
DROP TABLE IF EXISTS `t_user`;
CREATE TABLE `t_user` (
`user_id` bigint(20) NOT NULL COMMENT '用户id',
`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '用户姓名',
`age` int(3) DEFAULT NULL COMMENT '年龄',
PRIMARY KEY (`user_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

实体类与mapper文件

@Data
@AllArgsConstructor
@NoArgsConstructor
@Accessors(chain = true)
public class User {

    private Long userId;

    private String name;

    private Integer age;

}

@Mapper
public interface UserMapper extends BaseMapper<User> {

}

配置

spring:
  shardingsphere:
    datasource:
      names: ds0,ds1,ds2   #配置多个数据源
      ds0:
        type: com.alibaba.druid.pool.DruidDataSource
        driverClassName: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://172.17.98.247:3306/shardingsphere?serverTimezone=GMT%2B8
        username: root
        password: root
      ds1:
        type: com.alibaba.druid.pool.DruidDataSource
        driverClassName: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://172.17.98.247:3306/shardingsphere1?serverTimezone=GMT%2B8
        username: root
        password: root
      ds2:
        type: com.alibaba.druid.pool.DruidDataSource
        driverClassName: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://172.17.98.247:3306/user_db?serverTimezone=GMT%2B8
        username: root
        password: root
    sharding:
      tables:
        student:
          actual-data-nodes: ds$->{0..1}.student_$->{0..1}
          key-generator:
            column: id
            type: SNOWFLAKE
          database‐strategy:
            inline:
              sharding-column: id
              algorithm-expression: ds$->{id % 2} # id为奇数存储在ds1，偶数存储在ds0
            #分表策略
          table-strategy:
            inline:
              sharding-column: age
              algorithm-expression: student_$->{age % 2} # age为奇数存储在student_1 为偶数存储在student_0
        user:
          actual-data-nodes: ds$->{2}.t_user
          key-generator:
            column: user_id
            type: SNOWFLAKE
          table-strategy:
            inline:
              sharding-column: user_id
              algorithm-expression: t_user

测试

@Test
public void testVerticalAvgDb(){
    userMapper.insert(new User().setName("lisi").setAge(100));
}

公共表

公共表属于系统中数据量较小，变动少，而且属于高频联合查询的依 赖表。参数表、数据字典表等属于此类型。可 以将这类表在每个数据库都保存一份，所有更新操作都同时发 送到所有分库执行。

表结构

每个数据库中都创建t_dict

CREATE TABLE `t_dict` (
`dict_id` bigint(20) NOT NULL COMMENT '字典id',
`type` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '字典类型',
`code` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '字典编码',
`value` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '字典值',
PRIMARY KEY (`dict_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

实体与mapper

@Data
@Accessors(chain = true)
public class Dict {

    private Long dictId;

    private String type;

    private String code;

    private String value;

}

@Mapper
public interface DictMapper extends BaseMapper<Dict> {

    @Insert("insert into t_dict(dict_id,type,code,value) value(#{dictId},#{type},#{code},#{value})")
    int insertDict(Dict dict);

}

测试

@Test
public void testBroadcastTable(){
    dictMapper.insertDict(new Dict().setDictId(1L).setCode("123").setType("test").setValue("value"));
}

读写分离

主库负责写，从库负责读

docker搭建一主一从

配置主库

• 编写master和salve的容器创建shel脚本 

#!/bin/bash
docker run -itd --name mysql-master -p 3307:3306 -e MYSQL_ROOT_PASSWORD=root mysql

  

#!/bin/bash
docker run -itd --name mysql-slave -p 3308:3306 -e MYSQL_ROOT_PASSWORD=root mysql

• 进入主库容器安装vim

docker exec -it mysql-master /bin/bash
apt-get update
apt-get install vim

• 编辑my.cnf文件

vim /etc/mysql/my.cnf

#开启日志
log‐bin = mysql‐bin
#设置服务id，主从不能一致
server‐id = 1

•  重启主库

docker restart mysql-master

• 创建数据同步用户

CREATE USER 'slave'@'%' IDENTIFIED BY 'slave';

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%';

FLUSH PRIVILEGES;

配置从库

• 进入从容器

  docker exec -it mysql-slave /bin/bash

• 编辑my.cnf

  vim /etc/mysql/my.cnf

  #开启日志
  log‐bin = mysql‐slave-bin
  #设置服务id，主从不能一致
  server‐id = 2
  ​
  read_only = 1

• 重启从库

  docker restart mysql-slave

• 在主库执行

  show master status;

  

• 在从库执行

  CHANGE MASTER TO
  master_host = 'mysql-master',
  master_port = 3306,
  master_user = 'slave',
  master_password = 'slave',
  master_log_file = 'mysql-bin.000001',
  master_log_pos = 741;

  注意master_user和master+password配置为同步数据的账号，master_log_file和master_log_file配置为主库一致

  当同一服务器不同的mysql容器时，host填容器名，端口填容器内部端口

• 从库查看主从同步状态

show slave status; #查看主从同步的状态

•  开启主从同步

start slave; 

 

• 停止主从同步

  stop slave; # 停止主从同步
  reset slave; # 重启主从同步

项目配置文件

spring:
  shardingsphere:
    datasource:
      names: ds0,ds1   #配置多个数据源
      ds0:
        type: com.alibaba.druid.pool.DruidDataSource
        driverClassName: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://172.17.98.247:3307/user_db?serverTimezone=GMT%2B8
        username: root
        password: root
      ds1:
        type: com.alibaba.druid.pool.DruidDataSource
        driverClassName: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://172.17.98.247:3308/user_db?serverTimezone=GMT%2B8
        username: root
        password: root
    sharding:
      master-slave-rules:
        db0:
          master‐data‐source‐name: ds0
          slave‐data‐source‐names: ds1
      tables:
        student:
          actual-data-nodes: db0.student_$->{0..1}
          key-generator:
            column: id
            type: SNOWFLAKE
            #分表策略
          table-strategy:
            inline:
              sharding-column: id
              algorithm-expression: student_$->{id % 2} 

/**
 * 主从同步，读写分离,写测试
 */
@Test
public void testMasterAndSlaveInsert(){
    studentMapper.insert(new Student().setName("zhangsan").setAge(18).setAddress("beijing"));
    log.info("end");
}
​
/**
 * 主从同步，读写分离,读测试
 */
@Test
public void testMasterAndSlaveSelect(){
    Student student = studentMapper.selectById(1472467246867087361L);
    log.info(student.toString());
}

测试结果

 

 

demo地址，需要自取