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

• • • 一、概述
    • 二、实现
    • • 引入依赖
      • 配置多数据源
      • 分库分别配置
      • SPI接口调用
      • 分库策略实现类
      • 分表策略实现类
      • 主键策略实现类

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

pom.xml

        
        
            com.baomidou
            dynamic-datasource-spring-boot-starter
            3.2.1
        
        
        
            org.apache.shardingsphere
            shardingsphere-jdbc-core-spring-boot-starter
            5.0.0
        

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

在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;


@Slf4j
public class DbShardingAlgorithm implements StandardShardingAlgorithm {

    private static final String DB_NAME_PREFIX = "db";

    @Override
    public String doSharding(Collection collection, PreciseShardingValue 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 doSharding(Collection collection, RangeShardingValue 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;


@Slf4j
public class MtypeTbShardingAlgorithm implements StandardShardingAlgorithm {

    private static final String TB_NAME_PREFIX = "tbl_enterprise_mtype_";

    @Override
    public String doSharding(Collection collection, PreciseShardingValue 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 doSharding(Collection collection, RangeShardingValue 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;


public class SnowFlakeKeyGenerator implements KeyGenerateAlgorithm {
    @Override
    public Comparable generateKey() {
        return SnowFlakeWorker.getInstance().nextId();
    }

    @Override
    public void init() {

    }

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