shardingJDBC分库分表

背景

随着项目的运行很多数据库表数据压力越来越来大，例如（订单，详情等）,数据库压力越来越大，插入查询等操作异常麻烦，我们需要进行一些处理,
比如根据业务水平或者垂直分库分表

Cobar，阿里B2B团队开源，proxy层方案
TDDL，淘宝团队，client层方案
Atlas，360开源，proxy层方案
Sharding-jdbc，当当开源，client层方案，支持分库分表、读写分离、分布式id生成(我常用)
Mycat，基于Cobar改造，proxy层方案

更换高性能大容量储存数据库

oracle,polarDb等

话不多说上代码,为了方便我多个服务使用，我选择使用自定义注解common包来注入到各个服务使用
pom.xml

<dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
            <version>2.2.3.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>4.0.0-RC1</version>
        </dependency>
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis-spring</artifactId>
            <version>1.2.3</version>
            <scope>compile</scope>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.0.11</version>
            <scope>compile</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>4.1.6.RELEASE</version>
            <scope>compile</scope>
        </dependency>

自定义注解EnableCirculationSharding

@Target(ElementType.TYPE)
@Retention(RetentionPolicy.RUNTIME)
@Documented
@Inherited
@Import(ShardingDataSourceAutoConfiguration.class)
public @interface EnableCirculationSharding {

}

ShardingDataSourceAutoConfiguration

@Configuration
public class ShardingDataSourceAutoConfiguration {

    @Value("${jdbc.url}")
    String url1;
    @Value("${jdbc.username}")
    String username1;
    @Value("${jdbc.password}")
    String password1;


    @Value("${jdbc.url2}")
    String url2;
    @Value("${jdbc.username2}")
    String username2;
    @Value("${jdbc.password2}")
    String password2;

    @Value("${jdbc.url3}")
    String url3;
    @Value("${jdbc.username3}")
    String username3;
    @Value("${jdbc.password3}")
    String password3;

    @Autowired
    BottleShardingConfig bottleShardingConfig;

    @Autowired
    CaseShardingConfig caseShardingConfig;

    @Autowired
    BoxShardingConfig boxShardingConfig;

    @Autowired
    BottleDetailShardingConfig bottleDetailShardingConfig;

    @Autowired
    CaseDetailShardingConfig caseDetailShardingConfig;

    @Autowired
    BoxDetailShardingConfig boxdetailShardingConfig;


    @Bean
    @Primary
    public DataSource shardingDataSource() throws SQLException {
        // 配置真实数据源
        Map<String, DataSource> dataSourceMap = new HashMap<String, DataSource>(3);
        // 配置第一个数据源
        DruidDataSource dataSource1 = new DruidDataSource();
        dataSource1.setDriverClassName("com.mysql.jdbc.Driver");
        dataSource1.setUrl(url1);
        dataSource1.setUsername(username2);
        dataSource1.setPassword(password3);
        dataSourceMap.put("ds1", dataSource1);
        DruidDataSource dataSource2 = new DruidDataSource();
        dataSource2.setDriverClassName("com.mysql.jdbc.Driver");
        dataSource2.setUrl(url2);
        dataSource2.setUsername(username2);
        dataSource2.setPassword(password2);
        dataSourceMap.put("ds2", dataSource2);

        DruidDataSource dataSource3 = new DruidDataSource();
        dataSource3.setDriverClassName("com.mysql.jdbc.Driver");
        dataSource3.setUrl(url3);
        dataSource3.setUsername(username3);
        dataSource3.setPassword(password3);
        dataSourceMap.put("ds3", dataSource3);
        // 添加表配置
        ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
        shardingRuleConfig.setDefaultDataSourceName("ds1");
        shardingRuleConfig.setDefaultTableShardingStrategyConfig(this.noneShardingStrategyConfiguration());
        shardingRuleConfig.getTableRuleConfigs().add(getBottleConfig());
        shardingRuleConfig.getTableRuleConfigs().add(getBoxConfig());
        shardingRuleConfig.getTableRuleConfigs().add(getCaseConfig());
        shardingRuleConfig.getTableRuleConfigs().add(getBottleDetailConfig());
        shardingRuleConfig.getTableRuleConfigs().add(getCaseDetailConfig());
        shardingRuleConfig.getTableRuleConfigs().add(getBoxDetailConfig());
        Properties properties = new Properties();
        properties.put("sql.show", "false");
        // 获取数据源对象
        DataSource dataSource = ShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRuleConfig, properties);
        return dataSource;
    }

    // 事务管理器
    @Bean
    public DataSourceTransactionManager transactionManager(DataSource shardingDataSource) {
        return new DataSourceTransactionManager(shardingDataSource);
    }


    NoneShardingStrategyConfiguration noneShardingStrategyConfiguration() {
        NoneShardingStrategyConfiguration configurer = new NoneShardingStrategyConfiguration();
        return configurer;
    }

    TableRuleConfiguration getBottleConfig() {
        TableRuleConfiguration result = new TableRuleConfiguration("_bottle", "ds${1}._bottle_$->{0..9}_$->{0..9}_$->{0..9}");
        ComplexShardingStrategyConfiguration complexShardingStrategyConfiguration = new ComplexShardingStrategyConfiguration("_barcode", bottleShardingConfig);
        result.setTableShardingStrategyConfig(complexShardingStrategyConfiguration);
        return result;
    }

    TableRuleConfiguration getBottleDetailConfig() {
        TableRuleConfiguration result = new TableRuleConfiguration("_bottle_detail", "ds${1}._bottle_detail_$->{0..9}_$->{0..9}_$->{0..9}_1");
        ComplexShardingStrategyConfiguration complexShardingStrategyConfiguration = new ComplexShardingStrategyConfiguration("_barcode", bottleDetailShardingConfig);
        result.setTableShardingStrategyConfig(complexShardingStrategyConfiguration);
        return result;
    }
    TableRuleConfiguration getBoxConfig() {
        TableRuleConfiguration result = new TableRuleConfiguration("_box", "ds${3}._box_$->{0..9}_$->{0..9}");
        ComplexShardingStrategyConfiguration complexShardingStrategyConfiguration = new ComplexShardingStrategyConfiguration("_barcode", boxShardingConfig);
        result.setTableShardingStrategyConfig(complexShardingStrategyConfiguration);
        return result;
    }
    TableRuleConfiguration getBoxDetailConfig() {
        TableRuleConfiguration result = new TableRuleConfiguration("_box_detail", "ds${3}._box_detail_$->{0..9}_$->{0..9}_1");
        ComplexShardingStrategyConfiguration complexShardingStrategyConfiguration = new ComplexShardingStrategyConfiguration("_barcode", boxdetailShardingConfig);
        result.setTableShardingStrategyConfig(complexShardingStrategyConfiguration);
        return result;
    }
    TableRuleConfiguration getCaseConfig() {
        TableRuleConfiguration result = new TableRuleConfiguration("_casket", "ds${2}._casket_$->{0..9}_$->{0..9}_$->{0..9}");
        ComplexShardingStrategyConfiguration complexShardingStrategyConfiguration = new ComplexShardingStrategyConfiguration("_barcode", caseShardingConfig);
        result.setTableShardingStrategyConfig(complexShardingStrategyConfiguration);
        return result;
    }
    TableRuleConfiguration getCaseDetailConfig() {
        TableRuleConfiguration result = new TableRuleConfiguration("_casket_detail", "ds${2}._casket_detail_$->{0..9}_$->{0..9}_$->{0..9}_1");
        ComplexShardingStrategyConfiguration complexShardingStrategyConfiguration = new ComplexShardingStrategyConfiguration("_barcode", caseDetailShardingConfig);
        result.setTableShardingStrategyConfig(complexShardingStrategyConfiguration);
        return result;
    }
}

分库分表策略这里我只贴出一个策略其他根据自己业务需求修改即可,我的策略是根据唯一索引后三位分表

@Component
public class BottleShardingConfig implements ComplexKeysShardingAlgorithm<String> {
    public Collection<String> doSharding(Collection<String> collection, ComplexKeysShardingValue<String> complexKeysShardingValue) {

        Map<String, Collection<String>> columnNameAndShardingValuesMap = complexKeysShardingValue.getColumnNameAndShardingValuesMap();
        List<String> barcodeList = null;

        if (columnNameAndShardingValuesMap.containsKey("_barcode")) {
            barcodeList = (List<String>) columnNameAndShardingValuesMap.get("_barcode");
        }
        String logicTableName = complexKeysShardingValue.getLogicTableName();
        Set set = null;
        if(!CollectionUtils.isEmpty(barcodeList)){
            set =  new HashSet();
            String s = barcodeList.get(0);
            String s1 = s.substring(s.length() - 1);

            String s2 = "";
            s2 = s.substring(s.length() -2,s.length() - 1);

            String s3 = "";
            s3 = s.substring(s.length() -3,s.length() - 2);
            if(BarcodeMap.fall_map.containsKey(s1) && BarcodeMap.fall_map.containsKey(s2) && BarcodeMap.fall_map.containsKey(s3)){
                set.add(logicTableName+"_"+BarcodeMap.fall_map.get(s3)+"_"+BarcodeMap.fall_map.get(s2)+"_"+BarcodeMap.fall_map.get(s1));
            }else{
                return null;
            }
        }
        return set;
    }

}