shardingsphere5.0.0对商户数据进行分库分表操作

一、概述

分库分表主要依赖业务设计，全国商户数据按照省份分库是比较合理的方案，可以合理利用企业统一社会信用代码中包含的省份信息进行相关业务实现。

二、实现

引入依赖

pom.xml

        <!-- 多数据源 -->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>dynamic-datasource-spring-boot-starter</artifactId>
            <version>3.2.1</version>
        </dependency>
        <!-- 分库分表 -->
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
            <version>5.0.0</version>
        </dependency>

配置多数据源

application.yml

spring:
  datasource:
    dynamic:
      p6spy: false
      hikari:
        connection-timeout: 30000
        max-lifetime: 20000
        max-pool-size: 30
        min-idle: 10
        connection-test-query: select 1
        pool-name: mchntHikariCP
      primary: primary
      datasource:
        primary:
          username: root
          password: root
          driver-class-name: com.mysql.cj.jdbc.Driver
          url: jdbc:mysql://localhost:3307/mchnt?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&failOverReadOnly=false
  aop:
    proxy-target-class: true
  # 分库分表配置
  profiles:
    active: sharding

分库分别配置

application-sharding.yml

spring:
  # 分库分表数据源
  shardingsphere:
    # 显示sql
    props:
      sql-show: true
    datasource:
      names: db0,db1,db2,db3,db4,db5,db6,db7,db8,db9,db10,db11,db12,db13,db14,db15,db16,db17,db18,db19,db20,db21,db22,db23,db24,db25,db26,db27,db28,db29,db30,db31
      # 第一台服务器数据库
      db0:
        jdbc-url: jdbc:mysql://localhost:3307/db_mchnt_0?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&failOverReadOnly=false
        driver-class-name: com.mysql.cj.jdbc.Driver
        type: com.zaxxer.hikari.HikariDataSource
        username: root
        password: root
      db1:
        jdbc-url: jdbc:mysql://localhost:3307/db_mchnt_1?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&failOverReadOnly=false
        driver-class-name: com.mysql.cj.jdbc.Driver
        type: com.zaxxer.hikari.HikariDataSource
        username: root
        password: root
      
      ...
      
    # 数据节点
    rules:
      sharding:
        tables:
          tbl_enterprise_mtype:
            actual-data-nodes: db$->{0..31}.tbl_enterprise_mtype_$->{0..11}
            # 分库策略 - 标准分片策略
            database-strategy:
              standard:
                sharding-column: province_code
                sharding-algorithm-name: DbShardingAlgorithm
            # 分表策略
            table-strategy:
              standard:
                sharding-column: mchnt_type
                sharding-algorithm-name: MtypeTbShardingAlgorithm
            # 主键策略
            key-generate-strategy:
              column: id
              key-generator-name: id-generate-algorithm
        # 分片算法
        sharding-algorithms:
          DbShardingAlgorithm:
            type: DB
          MtypeTbShardingAlgorithm:
            type: MtypeTB
        # 主键算法
        key-generators:
          id-generate-algorithm:
            type: MYSNOWFLAKE
            props:
              worker-id: 1

SPI接口调用

在META-INF.services指定实现类的全路径名

org.apache.shardingsphere.sharding.spi.KeyGenerateAlgorithm

com.demo.toker.algorithm.SnowFlakeKeyGenerator

org.apache.shardingsphere.sharding.spi.ShardingAlgorithm

com.demo.mchnt.algorithm.DbShardingAlgorithm
com.demo.mchnt.algorithm.MtypeTbShardingAlgorithm

分库策略实现类

DbShardingAlgorithm.java

package com.demo.mchnt.algorithm;

import com.demo.mchnt.constants.ProvinceCodeEnum;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.shardingsphere.sharding.api.sharding.standard.PreciseShardingValue;
import org.apache.shardingsphere.sharding.api.sharding.standard.RangeShardingValue;
import org.apache.shardingsphere.sharding.api.sharding.standard.StandardShardingAlgorithm;

import java.util.Collection;

/**
 * 用途: 自定义 - 数据库 - 标准分片策略
 *
 * @return:
 * @author: ADAM
 * @date: 2022/2/8 上午11:25
 */
@Slf4j
public class DbShardingAlgorithm implements StandardShardingAlgorithm<String> {

    private static final String DB_NAME_PREFIX = "db";

    @Override
    public String doSharding(Collection<String> collection, PreciseShardingValue<String> preciseShardingValue) {
        String splitKey = preciseShardingValue.getValue();
        String dbSuffix = ProvinceCodeEnum.getByCode(splitKey).ordinal() + "";
        log.info("province_code:{}, 省份下标:{}", splitKey, dbSuffix);
        log.info("数据库[{}]", dbSuffix);

        String selectTableName = null;
        // 循环匹配数据源
        for (String name : collection) {
            // 获取逻辑数据源索引后缀
            String nameSuffix = name.substring(DB_NAME_PREFIX.length());
            if (nameSuffix.equals(dbSuffix)) {
                selectTableName = name;
                break;
            }
        }

        if (StringUtils.isNotBlank(selectTableName)) {
            return selectTableName;
        }
        log.error("根据分表键:{}选库异常", splitKey);
        throw new IllegalArgumentException();
    }

    @Override
    public Collection<String> doSharding(Collection<String> collection, RangeShardingValue<String> rangeShardingValue) {
        return null;
    }

    @Override
    public void init() {
    }

    @Override
    public String getType() {
        return "DB";
    }

}

分表策略实现类

MtypeTbShardingAlgorithm.java

package com.demo.mchnt.algorithm;

import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.shardingsphere.sharding.api.sharding.standard.PreciseShardingValue;
import org.apache.shardingsphere.sharding.api.sharding.standard.RangeShardingValue;
import org.apache.shardingsphere.sharding.api.sharding.standard.StandardShardingAlgorithm;

import java.util.Collection;

/**
 * 功能：自定义 - 表 - 标准分片策略
 *
 * @return:
 * @author: ADAM
 * @date: 2022/2/8 上午11:25
 */
@Slf4j
public class MtypeTbShardingAlgorithm implements StandardShardingAlgorithm<String> {

    private static final String TB_NAME_PREFIX = "tbl_enterprise_mtype_";

    @Override
    public String doSharding(Collection<String> collection, PreciseShardingValue<String> preciseShardingValue) {
        String splitKey = String.valueOf(preciseShardingValue.getValue());
        log.info("商户类型企业表[{}]", splitKey);

        String selectTableName = null;
        // 循环匹配数据源
        for (String name : collection) {
            // 获取逻辑数据源索引后缀
            String nameSuffix = name.substring(TB_NAME_PREFIX.length());
            if (nameSuffix.equals(splitKey.toString())) {
                selectTableName = name;
                break;
            }
        }

        if (StringUtils.isNotBlank(selectTableName)) {
            return selectTableName;
        }
        log.error("根据分表键:{}选表异常", splitKey);
        throw new IllegalArgumentException();
    }

    @Override
    public Collection<String> doSharding(Collection<String> collection, RangeShardingValue<String> rangeShardingValue) {
        return null;
    }

    @Override
    public void init() {
    }

    @Override
    public String getType() {
        return "MtypeTB";
    }

}

主键策略实现类

SnowFlakeKeyGenerator.java

package com.demo.mchnt.algorithm;

import org.apache.shardingsphere.sharding.spi.KeyGenerateAlgorithm;

/**
 * 用途: 自定义主键
 *
 * @author: ADAM
 * @create: 2022/02/14 06:41
 * @version: 1.0
 */
public class SnowFlakeKeyGenerator implements KeyGenerateAlgorithm {
    @Override
    public Comparable<?> generateKey() {
        return SnowFlakeWorker.getInstance().nextId();
    }

    @Override
    public void init() {

    }

    @Override
    public String getType() {
        return "MYSNOWFLAKE";
    }
}