ShardingSphere+MySQL实现分库

ShardingSphere + MySQL实现分库一、ShardingSphere简介

ShardingSphere是一套开源的分布式数据库中间件解决方案组成的生态圈，它由Sharding-JDBC、Sharding-Proxy和Sharding-Sidecar（计划中）这3款相互独立的产品组成。他们均提供标准化的数据分片、分布式事务和数据库治理功能，可适用于如Java同构、异构语言、云原生等各种多样化的应用场景。

sharding-jdbc 定位为轻量级Java框架，在Java的JDBC层提供的额外服务。它使用客户端直连数据库，以jar包形式提供服务，无需额外部署和依赖，可理解为增强版的JDBC驱动，完全兼容JDBC和各种ORM框架。
sharding-proxy 定位为透明化的数据库代理端，提供封装了数据库二进制协议的服务端版本，用于完成对异构语言的支持。目前先提供MySQL/PostgreSQL版本，它可以使用任何兼容MySQL/PostgreSQL协议的访问客户端(如：MySQL Command Client, MySQL Workbench, Navicat等)操作数据，对DBA更加友好。

架构图

二、项目配置

意图简介：两个数据库：ds0、ds1，每个数据库有两张表：order、order_item，只需要根据order_id分库。

1、POM引入ShardingSphere分库分表依赖

4.1.1

    org.apache.shardingsphere
    sharding-jdbc-spring-boot-starter
    ${shardingsphere.version}


    org.apache.shardingsphere
    sharding-jdbc-spring-namespace
    ${shardingsphere.version}

2、YAML配置

Sharding-JDBC可以通过Java配置、YAML配置、Spring命名空间配置等，具体可参考ShardingSphere官网配置手册ShardingSphere官网配置手册，里面有配置详细说明。

由于只涉及到分库，不涉及分表，通过YAML配置如下数据源：

spring:
	shardingsphere:
        sharding:
            binding-tables: order,order_item
        datasource:
            names: ds0,ds1
            ds0:
                auto: true
                url: jdbc:mysql://localhost:3306/ds0?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&serverTimezone=Asia/Shanghai
                username:
                password:
                type: com.alibaba.druid.pool.DruidDataSource
                driver-class-name: com.mysql.cj.jdbc.Driver
                initial-size: 10
                max-active: 100
                min-idle: 2
                validation-query: SELECT 1
                test-on-borrow: false
                test-while-idle: true
            ds1:
                auto: true
                url: jdbc:mysql://localhost:3306/ds1?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&serverTimezone=Asia/Shanghai
                username:
                password:
                type: com.alibaba.druid.pool.DruidDataSource
                driver-class-name: com.mysql.cj.jdbc.Driver
                initial-size: 10
                max-active: 100
                min-idle: 2
                validation-query: SELECT 1
                test-on-borrow: false
                test-while-idle: true

3、Sharding分片配置相关类代码：

DataSourceConfig.java: 获取数据源
DatabaseShardingAlgorithm.java: 分库算法
MybatisConfig.java: 分库配置

3.1 DataSourceConfig.java

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import com.alibaba.druid.pool.DruidDataSource;

@Configuration
public class DataSourceConfig {

    @Bean
    @Qualifier("ds0")
    @ConfigurationProperties("spring.shardingsphere.datasource.ds0")
    public DataSource ds0DataSource() {
        return new DruidDataSource();
    }

    @Bean
    @Qualifier("ds1DataSource")
    @ConfigurationProperties("spring.shardingsphere.datasource.ds1")
    public DataSource ds1DataSource() {
        return new DruidDataSource();
    }

    ......其他数据源
}

3.2 DatabaseShardingAlgorithm.java

import java.util.Collection;

import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;

import com.jiaoyan.tiku.enumeration.DataSourceEnum;

import lombok.extern.slf4j.Slf4j;

@Slf4j
public class DatabaseShardingAlgorithm implements PreciseShardingAlgorithm {

    
    @Override
    public String doSharding(Collection collection, PreciseShardingValue preciseShardingValue) {
        //log.info("DatabaseShardingAlgorithm collection:{}, preciseShardingValue: {}", collection, preciseShardingValue);
        return preciseShardingValue.getValue() % 2;
    }

//    public class DemoDatabaseShardingAlgorithm implements SingleKeyDatabaseShardingAlgorithm {
//
//    @Override
//    public String doEqualSharding(Collection databaseNames, ShardingValue shardingValue) {
//
//        for (String each : databaseNames) {
//            if (each.endsWith(Long.parseLong(shardingValue.getValue().toString()) % 2 + "")) {
//                return each;
//            }
//        }
//        throw new IllegalArgumentException();
//    }
//
//    @Override
//    public Collection doInSharding(Collection databaseNames, ShardingValue shardingValue) {
//        Collection result = new linkedHashSet<>(databaseNames.size());
//        for (Long value : shardingValue.getValues()) {
//            for (String tableName : databaseNames) {
//                if (tableName.endsWith(value % 2 + "")) {
//                    result.add(tableName);
//                }
//            }
//        }
//        return result;
//    }
//
//    @Override
//    public Collection doBetweenSharding(Collection databaseNames, ShardingValue shardingValue) {
//        Collection result = new linkedHashSet<>(databaseNames.size());
//        Range range = (Range) shardingValue.getValueRange();
//        for (Long i = range.lowerEndpoint(); i <= range.upperEndpoint(); i++) {
//            for (String each : databaseNames) {
//                if (each.endsWith(i % 2 + "")) {
//                    result.add(each);
//                }
//            }
//        }
//        return result;
//    }
}

3.3 MybatisConfig.java

import java.sql.SQLException;
import java.util.List;
import java.util.Map;
import java.util.Properties;

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

import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.shardingsphere.api.config.sharding.ShardingRuleConfiguration;
import org.apache.shardingsphere.api.config.sharding.TableRuleConfiguration;
import org.apache.shardingsphere.api.config.sharding.strategy.StandardShardingStrategyConfiguration;
import org.apache.shardingsphere.shardingjdbc.api.ShardingDataSourceFactory;
import org.assertj.core.util.Lists;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.autoconfigure.AutoConfigureAfter;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.ResourceLoader;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.core.io.support.ResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

import com.google.common.collect.Maps;
import com.jiaoyan.tiku.enumeration.DataSourceEnum;

@Configuration
@ComponentScan(basePackageClasses = MybatisConfig.class)
@MapperScan(basePackages = "com.shopping.dao", sqlSessionTemplateRef = "sqlSessionTemplate")
@EnableTransactionManagement
@AutoConfigureAfter({DataSourceConfig.class})
public class MybatisConfig {

    private static final String MAPPER_LOCATION = "classpath:mapper*.xml";
    private static final String TYPE_ALIAS_PACKAGE = "com.shopping.dao";
    private static final String MYBATIS_CONFIG_LOCATION = "classpath:mybatis/mybatis.xml";
    private static final String DATAbase_PREFIX = "ds";
    private static final String ORDER_ID = "order_id";

    @Resource(name = "ds0DataSource")
    private DataSource ds0DataSource;
    @Resource(name = "ds1DataSource")
    private DataSource ds1DataSource;
    
    @Autowired
    private ResourceLoader resourceLoader;

    @Value("${spring.shardingsphere.sharding.binding-tables}")
    private String tables;

    @Value("${spring.shardingsphere.datasource.names}")
    private String databases;

    @Bean
    public DataSource dataSource() throws SQLException {
        ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
        shardingRuleConfig.getBindingTableGroups().add(tables);
        shardingRuleConfig.getTableRuleConfigs().addAll(getTableRule());
        shardingRuleConfig.setDefaultDatabaseShardingStrategyConfig(
                new StandardShardingStrategyConfiguration(ORDER_ID, new DatabaseShardingAlgorithm()));
        return ShardingDataSourceFactory.createDataSource(roundRobinDataSourceProxy(), shardingRuleConfig, new Properties());
    }

    private List getTableRule() {
        String[] tableNameList = tables.split(",");
        String[] databaseNameList = databases.split(",");
        int databaseSize = databaseNameList.length;
        List tableRuleList = Lists.newArrayList();
        for (String tableName : tableNameList) {
            tableRuleList.add(getTableRule(tableName, databaseSize));
        }
        return tableRuleList;
    }

    private TableRuleConfiguration getTableRule(String tableName, int databaseSize) {
        // ds${0..1}.tableName
        return new TableRuleConfiguration(tableName, DATAbase_PREFIX + "${0.." + (databaseSize - 1) + "}." + tableName);
    }

    public Map roundRobinDataSourceProxy() {
        Map targetDataSourceMap = Maps.newHashMap();
        targetDataSourceMap.put(DATAbase_PREFIX + DataSourceEnum.ds0.getIndex(), ds0DataSource);
        targetDataSourceMap.put(DATAbase_PREFIX + DataSourceEnum.ds1.getIndex(), ds1DataSource);
        return targetDataSourceMap;
    }

    @Bean
    public DataSourceTransactionManager transactionManager() throws SQLException {
        return new DataSourceTransactionManager(dataSource());
    }

    @Bean
    public SqlSessionTemplate sqlSessionTemplate(SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }

    @Bean
    public SqlSessionFactory sqlSessionFactory() throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setTypeAliasesPackage(TYPE_ALIAS_PACKAGE);
        bean.setConfigLocation(resourceLoader.getResource(MYBATIS_CONFIG_LOCATION));
        bean.setDataSource(dataSource());
        bean.setFailFast(true);
        //添加XML目录
        ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
        try {
            bean.setMapperLocations(resolver.getResources(MAPPER_LOCATION));
            return bean.getObject();
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }
}

4、启动类配置

因为自定义了DataSource，需要排除SpringBoot默认提供的数据源：

@SpringBootApplication(exclude = {DataSourceAutoConfiguration.class})

三、避坑指南

1、Java -version 1.8.0_291版本，启动报错，换成1.8.0_211则没问题。

四、参考文档

1、ShardingSphere配置手册：https://shardingsphere.apache.org/document/legacy/4.x/document/cn/manual/sharding-jdbc/configuration/

2、shardingSphere-example项目Git地址：https://github.com/geomonlin/incubator-shardingsphere-example

3、ShardingSphere之分库&分表：https://blog.csdn.net/justry_deng/article/details/90815986

ShardingSphere+MySQL实现分库

Java相关栏目本月热门文章