526互联

ShardingSphere分库分表实战Demo

发布时间 2023-10-11 15:50:26作者: 请别耽误我写BUG

为什么要分库分表

从性能方面来说，一般关系型数据库采用B+树的数据结构，当数据库中的数据量很大时就会增加磁盘的IO次数，当系统使用者少的情况下可能不太明显，但是随着使用者的逐渐增多对数据库的优化需求就会越来越强烈！

从运维方面来说，一个数据库的数据量过大会增加数据更新、备份的耗时与风险，而且万一因项目或者服务器出现问题时容易造成系统的崩溃，无法使用。

分表分为‘垂直拆分’和‘水平拆分’

垂直拆分：垂直拆分又称纵向拆分，简单来说就是由一张字段比较多的表拆分为多个字段比较少的表。例如people表有id、name、gender、address等字段，如果它常用的字段只有id和name，那么就可以新建一个只有id和name字段的表出来。

水平拆分：水平方向上分担某个大数据量表的数据，例如student表有2000万条数据其中有1000万boy和1000万girl，就新建student_boy和student_girl两张表，分别存储student表中的boy数据和girl数据，那么这两个表每张表只有1000万数据。（本文用sharding-JDBC实现水平分表）

什么是ShardingSphere

ShardingSphere定位为关系型数据库中间件，旨在充分合理地在分布式的场景下利用关系型数据库的计算和存储能力，而并非实现一个全新的关系型数据库。

ShardingSphere分三个部分：Sharding-JDBC（分库分表）、Sharding-Proxy（数据库代理）、Sharding-Sidecar（云原生数据库代理）

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

1、适用于任何基于Java的ORM框架，如：JPA, Hibernate, Mybatis, Spring JDBC Template或直接使用JDBC。
2、基于任何第三方的数据库连接池，如：DBCP, C3P0, BoneCP, Druid, HikariCP等。
3、支持任意实现JDBC规范的数据库。目前支持MySQL，Oracle，SQLServer和PostgreSQL。

项目代码

新建数据库：sharding_01、sharding_02

每个库新建两张表：tb_user_0、tb_user_1

Sql：

DROP TABLE IF EXISTS `tb_user_0`;
CREATE TABLE `tb_user_0` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  `create_time` TIMESTAMP,
  `update_time` TIMESTAMP,
  `user_status` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `tb_user_1`;
CREATE TABLE `tb_user_1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  `create_time` TIMESTAMP,
  `update_time` TIMESTAMP,
  `user_status` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;
  • 项目结构：

  •  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>

    <groupId>com.emo</groupId>
    <artifactId>my-sharding</artifactId>
    <version>1.0-SNAPSHOT</version>
    <properties>
        <java.version>1.8</java.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
            <version>2.5.4</version>
        </dependency>
        <!--mybatis -->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.2.0</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
            <version>2.5.4</version>
            <scope>runtime</scope>
            <optional>true</optional>
        </dependency>
        <!--druid-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.20</version>
        </dependency>
        <!--mysql-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.46</version>
        </dependency>
        <!--shardingsphere-->
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>4.0.0-RC1</version>
        </dependency>
        <!--mybatis-->
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.7</version>
        </dependency>
        <!--AOP-->
        <dependency>
            <groupId>org.aspectj</groupId>
            <artifactId>aspectjweaver</artifactId>
            <version>1.9.6</version>
        </dependency>
        <!--fastjson-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.54</version>
        </dependency>
    </dependencies>

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

</project>
  • tb_user（需要与配置文件中的规则‘tb_user’一样，字段也要和数据库保持一直，不然sharding-jdbc可能会识别不出从而报错）
public class tb_user {
    public int id;
    public String username;
    public String password;
    public Date create_time;
    public Date update_time;
    public int user_status;

    public tb_user() {
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public Date getCreate_time() {
        return create_time;
    }

    public void setCreate_time(Date create_time) {
        this.create_time = create_time;
    }

    public Date getUpdate_time() {
        return update_time;
    }

    public void setUpdate_time(Date update_time) {
        this.update_time = update_time;
    }

    public int getUser_status() {
        return user_status;
    }

    public void setUser_status(int user_status) {
        this.user_status = user_status;
    }

    @Override
    public String toString() {
        return "tb_user{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", password='" + password + '\'' +
                ", create_time=" + create_time +
                ", update_time=" + update_time +
                ", user_status=" + user_status +
                '}';
    }
}
  • myShardingApplication
@SpringBootApplication
public class myShardingApplication {
    public static void main(String[] args) {
        SpringApplication.run(myShardingApplication.class, args);
    }
}
  • UserController
@RestController
public class UserController {

    @Autowired
    public UserService userService;

    @RequestMapping("query/users")
    public String getUsers(){
        return userService.getUsers();
    }

    @RequestMapping("add/users")
    public String addUsers(){
        return userService.addUsers();
    }
}
  • UserService
public interface UserService {
    String getUsers();

    String addUsers();
}
  • UserServiceImpl
@Service
public class UserServiceImpl implements UserService {
    @Autowired
    public UserMapper userMapper;

    public String getUsers() {
        List<tb_user> users = userMapper.getUsers();
        JSONObject.toJSONString(users);
        return JSONObject.toJSONString(users);
    }

    public String addUsers() {
        for (int i = 0; i < 10; i++) {
            tb_user user = new tb_user();
            user.setId(i + 1);
            user.setUsername("zhangsan" + i);
            user.setPassword("123456");
            user.setCreate_time(new Date());
            user.setUpdate_time(new Date());
            user.setUser_status(1);
            userMapper.addUser(user);
        }
        for (int i = 0; i < 10; i++) {
            tb_user user = new tb_user();
            user.setId(i + 1);
            user.setUsername("zhangsan" + i);
            user.setPassword("123456");
            user.setCreate_time(new Date());
            user.setUpdate_time(new Date());
            user.setUser_status(2);
            userMapper.addUser(user);
        }
        return "success";
    }
}
  • UserMapper
@Mapper
public interface UserMapper {
    List<tb_user> getUsers();

    void addUser(tb_user user);
}

 

  • UserMapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.code.mapper.UserMapper">

    <select id="getUsers" resultType="java.util.HashMap">
        SELECT
           *
        FROM
            tb_user order by id asc
    </select>

    <insert id="addUser" keyProperty="id" parameterType="com.code.bean.tb_user">
        insert into tb_user (id,username,password,create_time,update_time,user_status)
         values (#{id},#{username},#{password},#{create_time},#{update_time},#{user_status})
    </insert>

</mapper>
  • application.yml
# 端口号
server:
  port: 8080
# mapper扫描和别名扫描
mybatis:
  mapper-locations: classpath:mapper/*.xml
  type-aliases-package: com.code.bean
  • application.properties（主要配置sharding-jdbc）
# sharding-jdbc分片策略
#配置数据源，给数据源起名称
spring.shardingsphere.datasource.names=ds0,ds1
#一个实体类对应多张表，覆盖
spring.main.allow-bean-definition-overriding=true
# 配置数据源具体内容，包含连接池，驱动地址，用户名和密码
spring.shardingsphere.datasource.ds0.type=com.alibaba.druid.pool.DruidDataSource
#jdk1.8以下 com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds0.url=jdbc:mysql://localhost:3306/sharding_01?useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC
spring.shardingsphere.datasource.ds0.username=root
spring.shardingsphere.datasource.ds0.password=root
spring.shardingsphere.datasource.ds1.type=com.alibaba.druid.pool.DruidDataSource
#jdk1.8以下 com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds1.url=jdbc:mysql://localhost:3306/sharding_02?useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC
spring.shardingsphere.datasource.ds1.username=root
spring.shardingsphere.datasource.ds1.password=root

#分库策略
# 指定到具体表的库规则
spring.shardingsphere.sharding.tables.tb_user.database-strategy.inline.sharding-column=user_status
spring.shardingsphere.sharding.tables.tb_user.database-strategy.inline.algorithm-expression=ds$->{user_status % 2}
# 数据库中所有表的规则
spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column=user_status
spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression=ds$->{user_status % 2}

#指定tb_user表分布情况，配置表在哪个数据库里面，表名称都是什么 ds0.tb_user_0、ds0.tb_user_1、ds1.tb_user_0、ds1.tb_user_1
spring.shardingsphere.sharding.tables.tb_user.actual-data-nodes=ds$->{0..1}.tb_user_$->{0..1}
# 指定tb_user表里面主键id生成策略 snowflake
#spring.shardingsphere.sharding.tables.tb_user.key-generator.column=id
#spring.shardingsphere.sharding.tables.tb_user.key-generator.type=snowflake
# 指定分片策略 约定id值偶数添加到tb_user_1表，如果id是奇数添加到tb_user_0表
spring.shardingsphere.sharding.tables.tb_user.table-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.tb_user.table-strategy.inline.algorithm-expression=tb_user_$->{id % 2}
#打开sql输出日志
spring.shardingsphere.props.sql.show=true

验证

初始时数据库表中并无数据

启动项目并访问：http://localhost:8080/add/users （其实是访问项目中的addUsers方法）

返回“success”，增添数据成功：

sharding_01.tb_user_0

sharding_01.tb_user_1

sharding_02.tb_user_0

sharding_02.tb_user_1

访问：http://localhost:8080/query/users （访问项目中的getUsers方法）