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更加友好。

架构图

mysql select 打散_数据库开发

二、项目配置

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

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

<shardingsphere.version>4.1.1</shardingsphere.version>
<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
    <version>${shardingsphere.version}</version>
</dependency>
<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-namespace</artifactId>
    <version>${shardingsphere.version}</version>
</dependency>

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<Integer> {

	private static final String DATABASE_PREFIX = "ds";
    /**
     * Sharding.
     *
     * @param collection
     *            数据源(库)名称集合 或 真实表名称集合。
     *
     * @param preciseShardingValue
     *            分片键的值。
     *
     * @return 路由后的SQL要使用的数据源(库)的名字   或   路由后的SQL要使用的真实表的名字
     *         注:这里为: 路由后的SQL要使用的数据源(库)的名字。
     */
    @Override
    public String doSharding(Collection<String> collection, PreciseShardingValue<Integer> preciseShardingValue) {
        //log.info("DatabaseShardingAlgorithm collection:{}, preciseShardingValue: {}", collection, preciseShardingValue);
        return DATABASE_PREFIX + preciseShardingValue.getValue() % 2;
    }

//    public class DemoDatabaseShardingAlgorithm implements SingleKeyDatabaseShardingAlgorithm<Long> {
//
//    @Override
//    public String doEqualSharding(Collection<String> databaseNames, ShardingValue<Long> shardingValue) {
//
//        for (String each : databaseNames) {
//            if (each.endsWith(Long.parseLong(shardingValue.getValue().toString()) % 2 + "")) {
//                return each;
//            }
//        }
//        throw new IllegalArgumentException();
//    }
//
//    @Override
//    public Collection<String> doInSharding(Collection<String> databaseNames, ShardingValue<Long> shardingValue) {
//        Collection<String> 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<String> doBetweenSharding(Collection<String> databaseNames, ShardingValue<Long> shardingValue) {
//        Collection<String> result = new LinkedHashSet<>(databaseNames.size());
//        Range<Long> range = (Range<Long>) 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<TableRuleConfiguration> getTableRule() {
        String[] tableNameList = tables.split(",");
        String[] databaseNameList = databases.split(",");
        int databaseSize = databaseNameList.length;
        List<TableRuleConfiguration> 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<String, DataSource> roundRobinDataSourceProxy() {
        Map<String, DataSource> 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则没问题。

2、带有子查询的sql某些形式报错：Can not find owner from table. 如：select * from ( select id from test ) t GROUP BY t.id; --报错 改为：select * from ( select id from test ) t GROUP BY id; --正常 可能原因：子查询创建了临时表，查完就销毁了，因此t找不到了。

注意：某些子查询可能不报错，但是查不出数据。

官网介绍的支持的sql：SQL 支持程度

3、on duplicate key update语句中不能使用#{},${}, 需要使用values方法。

4、case when包含子查询，或者使用逻辑表名目前暂不支持。

5、count()支持，但是count ()会报错（中间多了空格）。

6、如果两个参数一个Long，一个Integer，parameterType指定为Long，会报错，不用指定最好别指定。

四、参考文档

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实现分库 + 读写分离项目Git地址：https://github.com/Yoohu/spring-boot-sharding-sphere

4、ShardingSphere之分库&分表：