shardingsphere 5.1.1 分库分表

1. mysql配置：

application-dubboService = spring-csc-campaign-agent-context.xml
kafka.topics.redisAccessLog = CDC_%s_REDIS_ACCESSLOG_CHG


sharding.jdbc.datasource.sharding0.bu-id = 1-4HK8TL
sharding.jdbc.datasource.sharding1.bu-id = 1-4PH8TL
sharding.jdbc.datasource.sharding2.bu-id = 1-30TKK9
sharding.jdbc.datasource.sharding3.bu-id = 1-31DRXE5
sharding.jdbc.datasource.sharding4.bu-id = 1-30TKP7
sharding.jdbc.datasource.sharding5.bu-id = 1-30TKPA
sharding.jdbc.datasource.sharding6.bu-id = 1-30TKKQ
sharding.jdbc.datasource.sharding7.bu-id = 1-ASBI4Z

spring.shardingsphere.datasource.names = sharding0,sharding1,sharding2,sharding3,sharding4,sharding5,sharding6,sharding7

#sit--CSC_MBHK_MEMBER

spring.shardingsphere.datasource.sharding0.url = jdbc:mysql://10.95.35.226:3306/CSC_MBHK_CAMPAIGN?useUnicode=true&characterEncoding=utf-8&useCursorFetch=true&defaultFetchSize=100&allowMultiQueries=true
spring.shardingsphere.datasource.sharding0.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.sharding0.username = cdcmbsit
spring.shardingsphere.datasource.sharding0.driverClassName = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.sharding0.password = ENC(ZIeuW2I1txJcMPcxG4IjEPOS6uTzm9/O)

spring.shardingsphere.datasource.sharding1.url = jdbc:mysql://10.95.35.226:3306/CDC_WTCPH_CAMPAIGN?useUnicode=true&characterEncoding=utf-8&useCursorFetch=true&defaultFetchSize=100&allowMultiQueries=true
spring.shardingsphere.datasource.sharding1.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.sharding1.username = cdcmbphsit
spring.shardingsphere.datasource.sharding1.driverClassName = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.sharding1.password = ENC(ZB1I2sYxgaasEeaccl7Ys/9JuydUdbSP)

spring.shardingsphere.datasource.sharding2.url = jdbc:mysql://10.95.35.27:3306/CDC_WTCMY_CAMPAIGN?useUnicode=true&characterEncoding=utf-8&useCursorFetch=true&defaultFetchSize=100&allowMultiQueries=true
spring.shardingsphere.datasource.sharding2.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.sharding2.username = u_cdc_wtcmy_app
spring.shardingsphere.datasource.sharding2.driverClassName = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.sharding2.password = Asia4gCSuL&DA

spring.shardingsphere.datasource.sharding3.url = jdbc:mysql://10.95.35.27:3306/CDC_WTCID_CAMPAIGN?useUnicode=true&characterEncoding=utf-8&useCursorFetch=true&defaultFetchSize=100&allowMultiQueries=true
spring.shardingsphere.datasource.sharding3.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.sharding3.username = u_cdc_wtcid_app
spring.shardingsphere.datasource.sharding3.driverClassName = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.sharding3.password = Asia4gCSuL&DA

spring.shardingsphere.datasource.sharding4.url = jdbc:mysql://10.95.35.27:3306/CDC_WTCSG_CAMPAIGN?useUnicode=true&characterEncoding=utf-8&useCursorFetch=true&defaultFetchSize=100&allowMultiQueries=true
spring.shardingsphere.datasource.sharding4.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.sharding4.username = u_cdc_wtcsg_app
spring.shardingsphere.datasource.sharding4.driverClassName = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.sharding4.password = Asia4gCSuL&DA

spring.shardingsphere.datasource.sharding5.url = jdbc:mysql://10.95.35.27:3306/CDC_WTCTH_CAMPAIGN?useUnicode=true&characterEncoding=utf-8&useCursorFetch=true&defaultFetchSize=100&allowMultiQueries=true
spring.shardingsphere.datasource.sharding5.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.sharding5.username = u_cdc_wtcth_app
spring.shardingsphere.datasource.sharding5.driverClassName = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.sharding5.password = Asia4gCSuL&DA

spring.shardingsphere.datasource.sharding6.url = jdbc:mysql://10.95.35.27:3306/CDC_WTCTW_CAMPAIGN?useUnicode=true&characterEncoding=utf-8&useCursorFetch=true&defaultFetchSize=100&allowMultiQueries=true
spring.shardingsphere.datasource.sharding6.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.sharding6.username = u_cdc_wtctw_app
spring.shardingsphere.datasource.sharding6.driverClassName = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.sharding6.password = Asia4gCSuL&DA

spring.shardingsphere.datasource.sharding7.url = jdbc:mysql://10.95.35.27:3306/CDC_WWHK_CAMPAIGN?useUnicode=true&characterEncoding=utf-8&useCursorFetch=true&defaultFetchSize=100&allowMultiQueries=true
spring.shardingsphere.datasource.sharding7.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.sharding7.username = u_cdc_wwhk_app
spring.shardingsphere.datasource.sharding7.driverClassName = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.sharding7.password = Asia4gCSuL&DA


# 是否开启分表开关,生产要关闭false
sharding.table.switch = true

# sharding hint, logic tables config
# notes: 需要配置所有的的逻辑表，不然就不能分库
sharding.hint.logic.tables=  CUSTOMER_CAMPAIGN_TREATMENT,CUSTOMER_LOY_MKTG_OFFER,CUSTOMER_POS_SEG_LST,CUSTOMER_CAMPAIGN_CONTACT,CUSTOMER_LOY_CAM_SEG,CUSTOMER_CAMP_LD_WAVE,CUSTOMER_CAMPAIGN,CUSTOMER_CAMPAIGN_DCP,CUSTOMER_CAMPAIGN_GOAL,CUSTOMER_CAMPAIGN_OFFER,CUSTOMER_CAMPAIGN_OPTY,CUSTOMER_COMM_DTL,CUSTOMER_COMMUNICATION,CUSTOMER_MARKETING_OFFER,CUSTOMER_MARKETING_OFFER_PROD,CUSTOMER_MKOFFR_PROMO,CUSTOMER_POSTN,CUSTOMER_LOY_ATTRDEFN

# 默认的hint分库算法
spring.shardingsphere.rules.sharding.default-database-strategy.hint.shardingalgorithm-name=byBuHintAlgorithm


spring.shardingsphere.props.sql-show=true
# 配置分库的表，因为我们所有的表都需要分库，这里需要配置所有的表

spring.shardingsphere.rules.sharding.tables.CUSTOMER_CAMPAIGN_TREATMENT.actual-data-nodes = sharding$->{0..7}.CUSTOMER_CAMPAIGN_TREATMENT
spring.shardingsphere.rules.sharding.tables.CUSTOMER_LOY_MKTG_OFFER.actual-data-nodes = sharding$->{0..7}.CUSTOMER_LOY_MKTG_OFFER
spring.shardingsphere.rules.sharding.tables.CUSTOMER_POS_SEG_LST.actual-data-nodes = sharding$->{0..7}.CUSTOMER_POS_SEG_LST
spring.shardingsphere.rules.sharding.tables.CUSTOMER_CAMP_LD_WAVE.actual-data-nodes = sharding$->{0..7}.CUSTOMER_CAMP_LD_WAVE
spring.shardingsphere.rules.sharding.tables.CUSTOMER_CAMPAIGN.actual-data-nodes = sharding$->{0..7}.CUSTOMER_CAMPAIGN
spring.shardingsphere.rules.sharding.tables.CUSTOMER_CAMPAIGN_DCP.actual-data-nodes = sharding$->{0..7}.CUSTOMER_CAMPAIGN_DCP
spring.shardingsphere.rules.sharding.tables.CUSTOMER_CAMPAIGN_GOAL.actual-data-nodes = sharding$->{0..7}.CUSTOMER_CAMPAIGN_GOAL
spring.shardingsphere.rules.sharding.tables.CUSTOMER_CAMPAIGN_OFFER.actual-data-nodes = sharding$->{0..7}.CUSTOMER_CAMPAIGN_OFFER
spring.shardingsphere.rules.sharding.tables.CUSTOMER_CAMPAIGN_OPTY.actual-data-nodes = sharding$->{0..7}.CUSTOMER_CAMPAIGN_OPTY
spring.shardingsphere.rules.sharding.tables.CUSTOMER_COMM_DTL.actual-data-nodes = sharding$->{0..7}.CUSTOMER_COMM_DTL
spring.shardingsphere.rules.sharding.tables.CUSTOMER_COMMUNICATION.actual-data-nodes = sharding$->{0..7}.CUSTOMER_COMMUNICATION
spring.shardingsphere.rules.sharding.tables.CUSTOMER_MARKETING_OFFER.actual-data-nodes = sharding$->{0..7}.CUSTOMER_MARKETING_OFFER
spring.shardingsphere.rules.sharding.tables.CUSTOMER_MARKETING_OFFER_PROD.actual-data-nodes = sharding$->{0..7}.CUSTOMER_MARKETING_OFFER_PROD
spring.shardingsphere.rules.sharding.tables.CUSTOMER_MKOFFR_PROMO.actual-data-nodes = sharding$->{0..7}.CUSTOMER_MKOFFR_PROMO
spring.shardingsphere.rules.sharding.tables.CUSTOMER_POSTN.actual-data-nodes = sharding$->{0..7}.CUSTOMER_POSTN
spring.shardingsphere.rules.sharding.tables.CUSTOMER_LOY_ATTRDEFN.actual-data-nodes = sharding$->{0..7}.CUSTOMER_LOY_ATTRDEFN


#分表配置
spring.shardingsphere.rules.sharding.tables.CUSTOMER_CAMPAIGN_CONTACT.logic-table=CUSTOMER_CAMPAIGN_CONTACT
spring.shardingsphere.rules.sharding.tables.CUSTOMER_CAMPAIGN_CONTACT.actual-data-nodes = sharding$->{0..1}.CUSTOMER_CAMPAIGN_CONTACT,sharding$->{2..7}.CUSTOMER_CAMPAIGN_CONTACT_$->{0..63},sharding$->{2..7}.CUSTOMER_CAMPAIGN_CONTACT

spring.shardingsphere.rules.sharding.tables.CUSTOMER_CAMPAIGN_CONTACT.table-strategy.standard.sharding-column=CON_PER_ID
spring.shardingsphere.rules.sharding.tables.CUSTOMER_CAMPAIGN_CONTACT.table-strategy.standard.sharding-algorithm-name=hashModShardingAlgorithm

spring.shardingsphere.rules.sharding.tables.CUSTOMER_LOY_CAM_SEG.logic-table=CUSTOMER_LOY_CAM_SEG
spring.shardingsphere.rules.sharding.tables.CUSTOMER_LOY_CAM_SEG.actual-data-nodes = sharding$->{0..1}.CUSTOMER_LOY_CAM_SEG,sharding$->{2..7}.CUSTOMER_LOY_CAM_SEG_$->{0..63},sharding$->{2..7}.CUSTOMER_LOY_CAM_SEG
spring.shardingsphere.rules.sharding.tables.CUSTOMER_LOY_CAM_SEG.table-strategy.standard.sharding-column=CONTACT_ID
spring.shardingsphere.rules.sharding.tables.CUSTOMER_LOY_CAM_SEG.table-strategy.standard.sharding-algorithm-name=hashModShardingAlgorithm

# 分片算法配置
spring.shardingsphere.rules.sharding.sharding-algorithms.byBuHintAlgorithm.type= CLASS_BASED
spring.shardingsphere.rules.sharding.sharding-algorithms.byBuHintAlgorithm.props.strategy= HINT
spring.shardingsphere.rules.sharding.sharding-algorithms.byBuHintAlgorithm.props.algorithmClassName= com.aswatson.csc.campaign.conf.HintAlgorithm
spring.shardingsphere.rules.sharding.sharding-algorithms.byBuHintAlgorithm.props.buDbMapping = {"1-4HK8TL":"sharding0","1-4PH8TL":"sharding1","1-30TKK9":"sharding2","1-31DRXE5":"sharding3","1-30TKP7":"sharding4","1-30TKPA":"sharding5","1-30TKKQ":"sharding6","1-ASBI4Z":"sharding7"}

# 分片算法配置
spring.shardingsphere.rules.sharding.sharding-algorithms.hashModShardingAlgorithm.type= CLASS_BASED
spring.shardingsphere.rules.sharding.sharding-algorithms.hashModShardingAlgorithm.props.strategy= STANDARD
spring.shardingsphere.rules.sharding.sharding-algorithms.hashModShardingAlgorithm.props.algorithmClassName= com.aswatson.csc.campaign.common.sharding.HashModShardingAlgorithm


spring.main.allow-bean-definition-overriding = true

2.  分库的doSharding配置类：

package com.aswatson.csc.campaign.conf;


import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import java.util.Properties;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;

import org.apache.shardingsphere.sharding.api.sharding.hint.HintShardingAlgorithm;
import org.apache.shardingsphere.sharding.api.sharding.hint.HintShardingValue;
import org.springframework.context.annotation.Configuration;

import java.util.Collection;
import java.util.Collections;
import java.util.Map;

@Slf4j
@Configuration
public class HintAlgorithm implements HintShardingAlgorithm{

    private Properties props;
    private static Map shardingBuMapping = Collections.emptyMap();

    public static void setShardingBuMapping(Map<String, String> shardingBuMapping) {
        HintAlgorithm.shardingBuMapping = shardingBuMapping;
    }

    @Override
    public Collection<String> doSharding(Collection collection, HintShardingValue hintShardingValue) {
        for (Object value : hintShardingValue.getValues()) {
            String availableTargetName = String.valueOf(shardingBuMapping.get(value.toString()));
            if (StringUtils.isNotEmpty(availableTargetName)) {
                return Collections.singletonList(availableTargetName);
            }
        }
        log.error("unknown sharding value and datasource mapping,sharding value={},all datasource mapping={}",
            hintShardingValue, shardingBuMapping, new IllegalArgumentException());
        return Collections.emptyList();

    }

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

    @Override public void init(Properties properties) {
        this.props = properties;
        initShardingBuMapping();
    }

    private void initShardingBuMapping() {
        String buDbMapping = props.getProperty("buDbMapping", "");
        if (StringUtils.isEmpty(buDbMapping)) {
            log.error("initShardingBuMapping fail because apollo config, buDbMapping not exist={}", buDbMapping,
                new IllegalArgumentException());
        }
        JSONObject jsonObject = JSON.parseObject(buDbMapping);
        shardingBuMapping = jsonObject.getInnerMap();
    }

}

3.  分表的doSharding配置类：

package com.aswatson.csc.campaign.common.sharding;

import com.ctrip.framework.apollo.Config;
import com.ctrip.framework.apollo.ConfigService;

import java.util.Arrays;
import java.util.Collection;
import java.util.Properties;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.shardingsphere.infra.hint.HintManager;
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;

/**
 * @Author Tim
 * @Date 2022/6/30 11:52
 */
@Slf4j
public class HashModShardingAlgorithm implements StandardShardingAlgorithm {

    // 通过Apollo client获取是否需要分表开关，不需要分表，通过availableTargetNames 截取 "_num"前的表
    public static String shardingTableSwitch = "sharding.table.switch";
    public static String nameSpace = "cdc-common";

    @Override
    public String doSharding(Collection availableTargetNames, PreciseShardingValue shardingValue) {
        Comparable value = shardingValue.getValue();
        int hashValue = value.hashCode();
        int availableTargetNamesLen = availableTargetNames.size();
        // 因为我们配置了是否需要分表的开关，不分表就返回逻辑表，如果availableTargetNames不包含这个逻辑表就会抛异常，
        // 因此我们配置这个分表的实际表，最后加了这个逻辑表
        if (availableTargetNamesLen > 1) {
            availableTargetNamesLen -= 1;
        }
        int targetNamesIdx = Math.abs(hashValue) % (availableTargetNamesLen);
        Object[] availableTargetNamesArr = availableTargetNames.toArray(new Object[0]);
        String tableName = String.valueOf(availableTargetNamesArr[targetNamesIdx]);
        log.info("doSharding-tableName: {}", tableName);
        return getActualTable(tableName);
    }

    private String getActualTable(String tableName) {
        Config config = ConfigService.getConfig(nameSpace);
//        String tableShardingSwitchBuIds = config.getProperty(shardingTableSwitchBuIds, "");
        boolean tableShardingSwitch = Boolean.parseBoolean(config.getProperty(shardingTableSwitch, "false"));
//        String[] tableShardingSwitchBuIdArrays = StringUtils.split(tableShardingSwitchBuIds, ",");
        if (tableShardingSwitch) {
            return tableName;
        }
        String actualTable = tableName.replaceAll("\\d+","");
        log.info("getActualTable-tableName: {}, reason: tableShardingSwitch|{}",
            actualTable, tableShardingSwitch);
        return actualTable.substring(0, actualTable.length()-1);

    }

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

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

    @Override
    public void init(Properties properties) {

    }
}