水平分表、分库和垂直分表、分库和公共表的代码实现和讲解

淡淡的烟草味﹌ 2023-10-07 22:46 39阅读 0赞

#### 文章目录 ####

*  一、教学讲解视频
 *  二、环境准备
 *  三、水平分表
 *   *  1.概念
     *  2.代码
 *  四、水平分库
 *   *  1.概念
     *  2.代码
 *  五、垂直分表
 *   *  1.概念
     *  2.代码
 *  六、垂直分库
 *   *  1.概念
     *  2.代码
 *  七、公共表
 *   *  1.概念
     *  2.代码

## 一、教学讲解视频 ##

教学讲解视频地址：[视频地址][Link 1]

## 二、环境准备 ##

*  操作系统：Win10
 *  数据库：MySQL5.7
 *  JDK：64位 jdk1.8.0\_202
 *  应用框架：spring-boot-2.1.3.RELEASE
 *  Sharding-JDBC：sharding-jdbc-spring-boot-starter-4.0.0-RC1

`对应的pom.xml依赖代码`

<?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.1.3.RELEASE</version>
        <relativePath/> 
      </parent>
    
    
      <groupId>com.yjq.programmer</groupId>
      <artifactId>ShardingJDBC</artifactId>
      <version>1.0-SNAPSHOT</version>
    
      <name>ShardingJDBC</name>
    
      <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <maven.compiler.source>1.8</maven.compiler.source>
        <maven.compiler.target>1.8</maven.compiler.target>
      </properties>
    
      <dependencies>
        
        <dependency>
          <groupId>mysql</groupId>
          <artifactId>mysql-connector-java</artifactId>
          <version>5.1.6</version>
          <scope>runtime</scope>
        </dependency>
        <dependency>
          <groupId>org.springframework.boot</groupId>
          <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        
        <dependency>
          <groupId>org.apache.shardingsphere</groupId>
          <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
          <version>4.0.0-RC1</version>
        </dependency>
        
        <dependency>
          <groupId>com.alibaba</groupId>
          <artifactId>druid</artifactId>
          <version>1.1.19</version>
        </dependency>
        
        <dependency>
          <groupId>org.springframework.boot</groupId>
          <artifactId>spring-boot-starter-test</artifactId>
          <scope>test</scope>
        </dependency>
        
        <dependency>
          <groupId>org.mybatis.spring.boot</groupId>
          <artifactId>mybatis-spring-boot-starter</artifactId>
          <version>1.3.2</version>
        </dependency>
        
        <dependency>
          <groupId>junit</groupId>
          <artifactId>junit</artifactId>
          <version>4.12</version>
          <scope>test</scope>
        </dependency>
      </dependencies>
    
      <build>
        <pluginManagement>
          <plugins>
    
            <plugin>
              <groupId>org.springframework.boot</groupId>
              <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
    
          </plugins>
        </pluginManagement>
      </build>
    </project>

## 三、水平分表 ##

### 1.概念 ###

水平分表是在`同一个`数据库内，把同一个表的数据`按一定规则`拆分到多个`表`中。  
因此，目前我在一个数据库中准备了两个表，`t_user_1`和`t_user_2`，如下图。  
![在这里插入图片描述][b9b8ccb2a36e4028bb013590fc3e84ea.png]  
表结构：

CREATE TABLE `t_user_1` (
      `id` bigint(20) NOT NULL,
      `name` varchar(100) DEFAULT NULL,
      `sex` int(2) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

### 2.代码 ###

①我们先来看下我们的SpringBoot的`配置文件`代码。

server.port=8081
    #1800s
    server.servlet.session.timeout=1800
    spring.jackson.time-zone=GMT+8
    spring.jackson.date-format=yyyy-MM-dd HH:mm:ss
    
    #定义数据源
    spring.shardingsphere.datasource.names=m1
    
    spring.shardingsphere.datasource.m1.url=jdbc:mysql://127.0.0.1:3306/db_user1?serverTimezone=GMT%2b8&useUnicode=true&characterEncoding=utf8
    spring.shardingsphere.datasource.m1.username=root
    spring.shardingsphere.datasource.m1.password=
    spring.shardingsphere.datasource.m1.driver‐class‐name=com.mysql.jdbc.Driver
    spring.shardingsphere.datasource.m1.type=com.alibaba.druid.pool.DruidDataSource
    
    # 指定t_user表的数据分布情况，配置数据节点
    spring.shardingsphere.sharding.tables.t_user.actual‐data‐nodes=m1.t_user_$->{
        1..2}
    
    # 指定t_user表的主键生成策略为SNOWFLAKE
    spring.shardingsphere.sharding.tables.t_user.key‐generator.column=id
    spring.shardingsphere.sharding.tables.t_user.key‐generator.type=SNOWFLAKE
    
    # 指定t_user表的分表策略，分表策略包括分片键和分片算法
    spring.shardingsphere.sharding.tables.t_user.table‐strategy.inline.sharding‐column=id
    spring.shardingsphere.sharding.tables.t_user.table‐strategy.inline.algorithm‐expression=t_user_$->{
        id % 2 + 1}
    
    
    # 控制台日志配置
    logging.level.root=info
    logging.level.com.yjq.programmer.dao=debug
    
    # 打开sql输出日志
    spring.shardingsphere.props.sql.show=true
    
    #mapper文件扫描路径
    mybatis.mapper-locations=classpath*:mappers/**/*.xml

`配置文件代码说明`

*  首先定义数据源m1，并对m1进行实际的参数配置。
 *  指定t\_user表的数据分布情况，他分布在m1.t\_user\_1，m1.t\_user\_2。
 *  指定t\_user表的主键生成策略为SNOWFLAKE，SNOWFLAKE是一种分布式自增算法，保证id全局唯一。
 *  定义t\_user分表策略，id为偶数的数据落在t\_user\_1，为奇数的落在t\_user\_2，所以分表策略的表达式为t\_user\_$->\{id% 2 + 1\}。

**踩坑注意！** 如果启动项目有如下报错，可能是配置文件中的`->`没有用英文类型的。  
![在这里插入图片描述][1b3e0aca06df43c4bb2671eabc7a2e9d.png]  
![在这里插入图片描述][9a9e406ab2bd4e3db3c55aac18a9b178.png]

②然后接下来就写我们的`dao`层、`mapper`层和`单元测试`的代码，去测试我们的水平分表情况下`插入`和`查询`的结果。

`dao`层

public interface UserDao {
        
    
        int insertUser(User user);
    
        List<User> selectUser();
    }

`mapper`层

<?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.yjq.programmer.dao.UserDao">
    
        <insert id="insertUser" parameterType="com.yjq.programmer.entity.User">
            insert into t_user(name) values (#{name})
        </insert>
    
    
        <select id="selectUser" resultType="com.yjq.programmer.entity.User">
            select * from t_user
        </select>
    </mapper>

`单元测试`

@Test
    public void testShardingJDBCInsert() {
        
        User user = new User();
        for(int i=0; i<10; i++) {
        
            user.setName("小明" + i);
            if(userDao.insertUser(user) == 1) {
        
                logger.info("插入成功！");
            } else {
        
                logger.info("插入失败！");
            }
        }
    }
    
    
    @Test
    public void testShardingJDBCSelect() {
        
        List<User> userList = userDao.selectUser();
        logger.info("查询结果：{}", JSONObject.toJSONString(userList));
    }

③结果说明  
`插入`  
id为奇数的被插入到t\_user\_2表，为偶数的被插入到t\_user\_1表，达到预期目标。  
`查询`  
sharding-jdbc分别去不同的表检索数据，达到预期目标；**如果有传入id进行查询，sharding-jdbc也会根据t\_user的分表策略去不同的表检索数据**。

## 四、水平分库 ##

### 1.概念 ###

水平分库是把同一个表的数据按`一定规则`拆分到不同的`数据库`中，每个库可以放在不同的服务器上。  
现在，我在`水平分表`的基础上多加了一个db\_user2的数据库。  
![在这里插入图片描述][80d1a3af2700414b9a0850ae9172034a.png]  
然后两个数据库中的表结构是一致的，表结构和上面水平分表用的保持一样。  
![在这里插入图片描述][9354ea1b1af043238412581e0769b3dd.png]

### 2.代码 ###

①我们先来看下我们的SpringBoot的`配置文件`代码。

server.port=8081
    #1800s
    server.servlet.session.timeout=1800
    spring.jackson.time-zone=GMT+8
    spring.jackson.date-format=yyyy-MM-dd HH:mm:ss
    
    #定义数据源
    spring.shardingsphere.datasource.names=m1,m2
    
    spring.shardingsphere.datasource.m1.url=jdbc:mysql://127.0.0.1:3306/db_user1?serverTimezone=GMT%2b8&useUnicode=true&characterEncoding=utf8
    spring.shardingsphere.datasource.m1.username=root
    spring.shardingsphere.datasource.m1.password=
    spring.shardingsphere.datasource.m1.driver‐class‐name=com.mysql.jdbc.Driver
    spring.shardingsphere.datasource.m1.type=com.alibaba.druid.pool.DruidDataSource
    
    spring.shardingsphere.datasource.m2.url=jdbc:mysql://127.0.0.1:3306/db_user2?serverTimezone=GMT%2b8&useUnicode=true&characterEncoding=utf8
    spring.shardingsphere.datasource.m2.username=root
    spring.shardingsphere.datasource.m2.password=
    spring.shardingsphere.datasource.m2.driver‐class‐name=com.mysql.jdbc.Driver
    spring.shardingsphere.datasource.m2.type=com.alibaba.druid.pool.DruidDataSource
    
    # 分库策略
    spring.shardingsphere.sharding.tables.t_user.database‐strategy.inline.sharding‐column=sex
    spring.shardingsphere.sharding.tables.t_user.database‐strategy.inline.algorithm‐expression=m$->{
        sex % 2 + 1}
    
    # 指定t_user表的数据分布情况，配置数据节点
    spring.shardingsphere.sharding.tables.t_user.actual‐data‐nodes=m$->{
        1..2}.t_user_$->{
        1..2}
    
    # 指定t_user表的主键生成策略为SNOWFLAKE
    spring.shardingsphere.sharding.tables.t_user.key‐generator.column=id
    spring.shardingsphere.sharding.tables.t_user.key‐generator.type=SNOWFLAKE
    
    # 指定t_user表的分表策略，分表策略包括分片键和分片算法
    spring.shardingsphere.sharding.tables.t_user.table‐strategy.inline.sharding‐column=id
    spring.shardingsphere.sharding.tables.t_user.table‐strategy.inline.algorithm‐expression=t_user_$->{
        id % 2 + 1}
    
    
    # 控制台日志配置
    logging.level.root=info
    logging.level.com.yjq.programmer.dao=debug
    
    # 打开sql输出日志
    spring.shardingsphere.props.sql.show=true
    
    #mapper文件扫描路径
    mybatis.mapper-locations=classpath*:mappers/**/*.xml

`配置文件代码说明`

*  配置了两个数据源，分配指向两个不同的数据库db\_user1和db\_user2。
 *  配置分库策略，sex字段为偶数的数据落在m1数据源，为奇数的落在m2数据源，所以分库策略的表达式为m$->\{sex % 2 + 1\}。
 *  配置分表策略，分表策略和上面水平分表保持一致。
 *  也就是当有数据来时，先根据sex字段判断落入哪个数据源，然后再根据id字段来判断落入哪个表中。

②然后接下来就写我们的`dao`层、`mapper`层和`单元测试`的代码，去测试我们的水平分表情况下`插入`和`查询`的结果。

`dao`层

public interface UserDao {
        
    
        int insertUser(User user);
    
        List<User> selectUser(User user);
    }

`mapper`层

<?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.yjq.programmer.dao.UserDao">
    
        <insert id="insertUser" parameterType="com.yjq.programmer.entity.User">
            insert into t_user(name, sex) values (#{name}, #{sex})
        </insert>
    
    
        <select id="selectUser" parameterType="com.yjq.programmer.entity.User" resultType="com.yjq.programmer.entity.User">
            select * from t_user t where t.sex = #{sex} and t.id = #{id}
        </select>
    </mapper>

`单元测试`

@Test
    public void testShardingJDBCInsert() {
        
        User user = new User();
        for(int i=0; i<10; i++) {
        
            user.setName("小明" + i);
            user.setSex(1);
            if(userDao.insertUser(user) == 1) {
        
                logger.info("插入成功！");
            } else {
        
                logger.info("插入失败！");
            }
        }
    }
    
    
    @Test
    public void testShardingJDBCSelect() {
        
        User user = new User();
        user.setSex(1);
        user.setId(821357967667363840L);
        List<User> userList = userDao.selectUser(user);
        logger.info("查询结果：{}", JSONObject.toJSONString(userList));
    }

③结果说明  
`插入`  
sex字段为奇数的数据落入m2数据源，为偶数的落入m1数据源。同时id字段值为奇数的，插入t\_user\_2表中，为偶数的插入t\_user\_1表中，达到预期目标。  
`查询`  
sharding-jdbc分别去不同的表检索数据，达到预期目标；**如果有传入sex进行查询，sharding-jdbc会根据t\_user的分库策略去锁定查哪个库，如果有传入id进行查询，sharding-jdbc会根据t\_user的分表策略去锁定查哪个表**。

## 五、垂直分表 ##

### 1.概念 ###

垂直分表一般就是把表的结构进行改造，关于如何改造，可以浏览我的另一篇博客：  
[分库分表：垂直分库、垂直分表、水平分库、水平分表四个概念][Link 2]  
**大致的思路就是：将一个表按照字段分成多表，每个表存储其中一部分字段。**

### 2.代码 ###

无代码，垂直分表属于表结构设计层面。

## 六、垂直分库 ##

### 1.概念 ###

垂直分库就是在`垂直分表`把表进行分类后，放到`不同的数据库`中。每个库可以放在不同的服务器上，它的核心理念是`专库专用`。关于如何改造，同样可以浏览我的另一篇博客：  
[分库分表：垂直分库、垂直分表、水平分库、水平分表四个概念][Link 2]

### 2.代码 ###

无代码，垂直分库属于数据库设计层面。

## 七、公共表 ##

### 1.概念 ###

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

### 2.代码 ###

①只需要在SpringBoot的`配置文件`中加入下面这行来指明公共表就行。  
`如果有多个公共表，用逗号拼接就行`

#公共表设置
    spring.shardingsphere.sharding.broadcast‐tables=t_dict

②然后接下来就写我们的`dao`层、`mapper`层和`单元测试`的代码，去测试我们的公共表的`插入`的结果。  
`dao`层

public interface DictDao {
        
    
        int insertDict(Dict dict);
    }

`mapper`层

`单元测试`

@Test
    public void testShardingJDBCInsertDict() {
        
        Dict dict = new Dict();
        dict.setId(1);
        dict.setName("字典名称");
        if(dictDao.insertDict(dict) == 1) {
        
            logger.info("插入成功！");
        } else {
        
            logger.info("插入失败！");
        }
    }

③结果说明  
`插入`  
插入的数据在`每个库中的对应的公共表`中都能看到，达到预期目标。

[Link 1]: https://www.bilibili.com/video/BV1tx4y1371w
[b9b8ccb2a36e4028bb013590fc3e84ea.png]: https://img-blog.csdnimg.cn/b9b8ccb2a36e4028bb013590fc3e84ea.png
[1b3e0aca06df43c4bb2671eabc7a2e9d.png]: https://img-blog.csdnimg.cn/1b3e0aca06df43c4bb2671eabc7a2e9d.png
[9a9e406ab2bd4e3db3c55aac18a9b178.png]: https://img-blog.csdnimg.cn/9a9e406ab2bd4e3db3c55aac18a9b178.png
[80d1a3af2700414b9a0850ae9172034a.png]: https://img-blog.csdnimg.cn/80d1a3af2700414b9a0850ae9172034a.png
[9354ea1b1af043238412581e0769b3dd.png]: https://img-blog.csdnimg.cn/9354ea1b1af043238412581e0769b3dd.png
[Link 2]: https://blog.csdn.net/dgfdhgghd/article/details/128426013