526互联

数据库之Sharding分库分表操作详解

发布时间 2023-07-22 23:10:23作者: 上善若泪

目录

1 分库分表

1.1 简介

Sharding有两种：Sharding-Jdbc和ShardingSphere，最早是由当当网开源，捐给了Apache，成为了Apache顶级项目，后来又升级成了ShardingSphere
具体可以参考官网：https://shardingsphere.apache.org/index_zh.html

Apache ShardingSphere 是一款分布式 SQL 事务和查询引擎，可通过数据分片、弹性伸缩、加密等能力对任意数据库进行增强。

1.2 实操准备

1.2.1 Sharding与SpringBoot 公共依赖pom

springboot使用的是2.6.6 版本

 <parent>
   	  <artifactId>spring-boot-starter-parent</artifactId>
     <groupId>org.springframework.boot</groupId>
     <version>2.6.6</version>
 </parent>
 <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.70</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.32</version>
        </dependency>

        <!-- druid连接池 -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.2.8</version>
        </dependency>
        <!--注意引入druid需要引入log4j，不然会报错-->
        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.17</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.5.0</version>
        </dependency>

        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.7.2</version>
        </dependency>
      
    </dependencies>

1.3 Sharding-Jdbc与SpringBoot

Sharding-Jdbc 4.1.1与SpringBoot整合比较简单，只用引个依赖，和写个配置文件即可

注意：如果是jdk1.8则没有任何问题，如果jdk是比较超前的话比如jdk17，同样的配置文件可能会找不到表明，必须在 jdk17 对应的表中加个下划线_，才可以避免启动失败，如果是 jdk1.8 就没有这个问题

1.3.1 pom.xml

 <!-- sharding-jdbc -->
<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
    <version>4.1.1</version>
</dependency>

1.3.2 配置文件

由于配置文件较多，所以分开写了两个

1.3.2.1 application.yml

spring:
  application:
    name: mybatis-plus-demo
  profiles:
    include: sharding_4


mybatis-plus:
  mapper-locations: 'classpath:mapper/*Mapper.xml'
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
  # 此处配置是 指定 mybatis-plus的 json处理文件
  type-handlers-package: cn.util

1.3.2.2 application-sharding_4.yml

spring:
  shardingsphere:
    datasource:
      # 指定多个数据源 用逗号分开
      names: db0
      db0:
        url: jdbc:mysql://127.0.0.1:3306/test_1?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8&useSSL=false&useInformationSchema=false&allowPublicKeyRetrieval=true
        driver-class-name: com.mysql.cj.jdbc.Driver
        type: com.alibaba.druid.pool.DruidDataSource
        username: xxxx
        password: xxxx
    sharding:
      # 指定 分表 还可以指定分库
      tables:
        user:  # 此处表是逻辑表  对应数据库中 user0,user1
          actual-data-nodes: db0.user$->{0..1}
          table-strategy:
            inline:
              sharding-column: id #指定分库的 键
              # 指定分库计算方法 此处是groovy 语法
              algorithm-expression: user$->{id % 2} 
    props:
      sql:
        show: true   # 日志显示SQL

注意：此处数据库部分报红是正常，不用担心

1.4 ShardingSphere与SpringBoot

Sharding-Jdbc 5.2.0 与SpringBoot整合比较简单，只用引个依赖，和写个配置文件即可

1.4.1 pom.xml

<dependency>
   <groupId>org.apache.shardingsphere</groupId>
   <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
   <version>5.2.0</version>
</dependency>

1.4.2 配置文件

此处配置文件大部分是从官网摘取，找到对应版本号即可查阅，比如：5.2.0https://shardingsphere.apache.org/document/5.2.0/cn/user-manual/shardingsphere-jdbc/spring-boot-starter/rules/sharding/

1.4.2.1 application.yml

spring:
  application:
    name: mybatis-plus-demo
  profiles:
    include: sharding_jdbc


mybatis-plus:
  mapper-locations: 'classpath:mapper/*Mapper.xml'
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
  # 此处配置是 指定 mybatis-plus的 json处理文件
  type-handlers-package: cn.util

1.4.2.2 application-sharding_jdbc.yml

spring:
  shardingsphere:
    datasource:
      names: db0
      db0:
        url: jdbc:mysql://127.0.0.1:3306/test_1?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8&useSSL=false&useInformationSchema=false&allowPublicKeyRetrieval=true
        driver-class-name: com.mysql.cj.jdbc.Driver
        type: com.alibaba.druid.pool.DruidDataSource
        username: xxxx
        password: xxxx
    rules:
      sharding:
        tables:
          user:
            # 数据节点配置，采用Groovy表达式
            actual-data-nodes: db0.user$->{0..1}
            table-strategy:
              standard:
                sharding-column: id
                sharding-algorithm-name: user-inline
            key-generate-strategy:
              column: id
              key-generator-name: snowflake
        sharding-algorithms:
          user-inline:
            type: INLINE
            props:
              algorithm-expression: user$->{id%2}
        key-generators:
          snowflake:
            type: SNOWFLAKE
    props:
      # 日志显示具体的SQL
      sql.show: true

注意：此处数据库部分报红是正常，不用担心

1.4.3 自定义雪花算法

1.4.3.1 实现KeyGenerateAlgorithm接口

需要实现 KeyGenerateAlgorithm 接口

package cn.util;

import cn.hutool.core.lang.Snowflake;
import cn.hutool.core.util.IdUtil;
import com.alibaba.fastjson.JSON;
import lombok.extern.slf4j.Slf4j;
import org.apache.shardingsphere.sharding.spi.KeyGenerateAlgorithm;

import java.util.Properties;


@Slf4j
public class SnowFlake implements KeyGenerateAlgorithm  {

    @Override
    public Comparable<?> generateKey() {
        Snowflake snowflake = IdUtil.createSnowflake(1, 1);
        Long id = snowflake.nextId();
        System.out.println("自定义的id" + id);
        return id;

    }
	// 返回 自定义 算法类型表示
    @Override
    public String getType() {
        return "SNOWFLAKE_MINE";
    }

    @Override
    public Properties getProps() {
        return null;
    }

    @Override
    public void init(Properties properties) {
        
    }
}

1.4.3.2 添加配置

最后还需要添加一个SPI配置（点击了解SPI机制 ），resource下面的META-INF/services生成一个文件org.apache.shardingsphere.sharding.spi.KeyGenerateAlgorithm，配置算法类的全路径。

cn.util.SnowFlake

1.4.3.3 使用自定义类型

在配置文件application-sharding_jdbc.yml中把对应的key生成规则修改为自定义的SNOWFLAKE_MINE即可
在这里插入图片描述

1.4.3.4 MybatisPlus插入失效

如果使用的是 MybatisPlus 插入时发现用的 key 还是系统自带的 雪花算法，觉得没有生效，需要把MybatisPlus 对应的实体主键对应的注解 @TableId 注释掉即可

1.5 ShardingSphere与MybatisPlus多数据源

由于ShardingSphere切换表或者库是自动的，如果想手动指定一个数据源还得用 MybatisPlus 多数据源 比较方便点
点击此处了解 springboot多数据源配置

1.5.1 pom.xml

<dependency>
     <groupId>com.baomidou</groupId>
     <artifactId>dynamic-datasource-spring-boot-starter</artifactId>
     <version>3.5.0</version>
 </dependency>

1.5.2 配置文件

1.5.2.1 application.yml

spring:
  application:
    name: mybatis-plus-demo
  profiles:
    include: mbp_dynamic,sharding_jdbc


mybatis-plus:
  mapper-locations: 'classpath:mapper/*Mapper.xml'
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
  type-handlers-package: cn.util

1.5.2.2 application-mbp_dynamic.yml

spring:
  datasource:
    dynamic:
      primary: master
      datasource:
        master:
          driver-class-name: com.mysql.cj.jdbc.Driver
          url: jdbc:mysql://127.0.0.1:3306/test_2?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8&useSSL=false&useInformationSchema=false&allowPublicKeyRetrieval=true
          type: com.alibaba.druid.pool.DruidDataSource
          username: xxxx
          password: xxxx

1.5.2.3 application-sharding_jdbc.yml

spring:
  shardingsphere:
    datasource:
      names: db0
      db0:
        url: jdbc:mysql://127.0.0.1:3306/test_1?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8&useSSL=false&useInformationSchema=false&allowPublicKeyRetrieval=true
        driver-class-name: com.mysql.cj.jdbc.Driver
        type: com.alibaba.druid.pool.DruidDataSource
        username: xxxx
        password: xxxx
    rules:
      sharding:
        tables:
          user:
            # 数据节点配置，采用Groovy表达式
            actual-data-nodes: db0.user$->{0..1}
            table-strategy:
              standard:
                sharding-column: id
                sharding-algorithm-name: user-inline
            key-generate-strategy:
              column: id
              key-generator-name: snowflake
        sharding-algorithms:
          user-inline:
            type: INLINE
            props:
              algorithm-expression: user$->{id%2}
        key-generators:
          snowflake:
          # 此处采用自定义 雪花算法
            type: SNOWFLAKE_MINE
    props:
      # 日志显示具体的SQL
      sql.show: true

1.5.3 ShardingSphere与多数据源整合

package cn.util;

import com.baomidou.dynamic.datasource.DynamicRoutingDataSource;
import com.baomidou.dynamic.datasource.provider.AbstractDataSourceProvider;
import com.baomidou.dynamic.datasource.provider.DynamicDataSourceProvider;
import com.baomidou.dynamic.datasource.spring.boot.autoconfigure.DataSourceProperty;
import com.baomidou.dynamic.datasource.spring.boot.autoconfigure.DynamicDataSourceAutoConfiguration;
import com.baomidou.dynamic.datasource.spring.boot.autoconfigure.DynamicDataSourceProperties;
import lombok.extern.slf4j.Slf4j;
import org.apache.shardingsphere.driver.jdbc.adapter.AbstractDataSourceAdapter;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.SpringBootConfiguration;
import org.springframework.boot.autoconfigure.AutoConfigureBefore;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Lazy;
import org.springframework.context.annotation.Primary;

import javax.annotation.Resource;
import javax.sql.DataSource;

import java.util.Map;

@Slf4j
@Configuration
@AutoConfigureBefore({DynamicDataSourceAutoConfiguration.class, SpringBootConfiguration.class})
public class DataSourceConfiguration {

    // 分表数据源名称
    public static final String SHARDING_DATA_SOURCE_NAME = "sharding";

    //动态数据源配置项
    @Autowired
    private DynamicDataSourceProperties properties;

   
    @Lazy
    @Resource(name = "shardingSphereDataSource")
    private AbstractDataSourceAdapter shardingDataSource;

    @Bean
    public DynamicDataSourceProvider dynamicDataSourceProvider() {
        Map<String, DataSourceProperty> datasourceMap = properties.getDatasource();
        return new AbstractDataSourceProvider() {
            @Override
            public Map<String, DataSource> loadDataSources() {
                Map<String, DataSource> dataSourceMap = createDataSourceMap(datasourceMap);
                // 将 shardingjdbc 管理的数据源也交给动态数据源管理
                dataSourceMap.put(SHARDING_DATA_SOURCE_NAME, shardingDataSource);
                return dataSourceMap;
            }
        };
    }

    /**
     * 将动态数据源设置为首选的
     * 当spring存在多个数据源时, 自动注入的是首选的对象
     * 设置为主要的数据源之后，就可以支持shardingjdbc原生的配置方式了
     */
    @Bean
    @Primary
    public DataSource dataSource() {
        DynamicRoutingDataSource dataSource = new DynamicRoutingDataSource();
        dataSource.setPrimary(properties.getPrimary());
        dataSource.setStrict(properties.getStrict());
        dataSource.setStrategy(properties.getStrategy());
        dataSource.setP6spy(properties.getP6spy());
        dataSource.setSeata(properties.getSeata());
        return dataSource;
    }
}

当在使用时在需要使用Sharding的地方 使用 @DS("sharding")即可切换到Sharding

上面的 dynamicDataSourceProvider 方法也可以这样重构

@Bean
    public DynamicDataSourceProvider dynamicDataSourceProvider() {
        //Map<String, DataSourceProperty> datasourceMap = properties.getDatasource();
        return new AbstractDataSourceProvider() {
            @Override
            public Map<String, DataSource> loadDataSources() {
                // Map<String, DataSource> dataSourceMap = createDataSourceMap(datasourceMap);
                Map<String, DataSource> dataSourceMap = new HashMap<>();
                // 将 shardingjdbc 管理的数据源也交给动态数据源管理
                dataSourceMap.put(SHARDING_DATA_SOURCE_NAME, shardingDataSource);
                return dataSourceMap;
            }
        };
    }