sharding-jdbc分库分表

1.分库分表基本概念

垂直分库：垂直分库就相当于应用中有两个主数据源，所有的业务表分布在两个数据库中，避免单库压力太大。

水平分库：水平的意思就是将一张表的数据放置不同的数据库中，数据库a有t_order,数据库b也有t_order表，两张表结构没有差异。记录依据逻辑分别插入不同数据库中。如订单id是奇数的写入数据库a的t_order表中，为偶数的写入数据库b中的t_order表中。这样做的实际意义不大，实际应用中更倾向于水平分表。

垂直分表：垂直分表是指将一个大表依据业务逻辑拆分出一部分字段，新建一张表，如t_product表拆分出一张t_product_detail表，两张表在同一个库中,所以在表结构设计过程中就可以确定的。

水平分表：水平分表同水平分库类似，但是在同一个库中的，如t_order表依据规则分为t_order_1,t_order_2，依据id奇偶性来区分。

广播表：当应用中设置了垂直分库后，一些表带着公共属性的，例如地址表t_address,有可能会在每个库中都被使用，如果仅仅在一个库中有这张表，将会非常不方便，这个表在每个库中都有而且数据实时更新，那就非常方便了，sharding-jdbc支持广播表，当在广播表中crud时候，会在所有库中更新广播表。

绑定表：当两张表都设置了水平分表后，如t_order_1,t_order_2和t_order_detail_1,t_order_detail_2后，要对t_order和t_order_detail进行left join的时候，就会分别对t_order_1 t_order_detail_1;t_order_2 t_order_detail_1,t_order_2 t_order_detail_2分别left join 查询，如果设置了关联表，那只需要进行t_order_1 t_order_detail_1和t_order_2 t_order_detail_2两次查询。

2.sharding-jdbc

sharding-sphere是由三大模块组成，我主要关注sharding-jdbc。目前已经贡献给Apache开源基金会了，官方文档也有详细的使用demo,sharding-jdbc设置主要有yml,java代码方式，我个人认为分库分表的这些配置基本上不会变化太大，我个人比较喜欢将这些配置都写在java代码上，这样做也会有缺点，当新增表时候就需要改代码，也可以设置默认的数据源来解决此问题，所以也希望能够java代码和配置文件同时生效，尝试过了，发现不能共存。

3.sharding-jdbc使用案例（详细使用demo,请参考官方demo https://github.com/sharding-s...）

a.引入依赖（版本号为4.0.0-RC3）
<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-core</artifactId>
</dependency>
<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
</dependency>
<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-core-api</artifactId>
</dependency>

b.配置数据源，数据库连接池使用的是hikari

c.sharding-jdbc核心配置类
package com.xiayu.config; //打包


import com.google.common.collect.Lists;
import com.xiayu.constants.DataSourceConstants;
import com.zaxxer.hikari.HikariDataSource;
import lombok.Setter;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.shardingsphere.api.config.masterslave.MasterSlaveRuleConfiguration;
import org.apache.shardingsphere.api.config.sharding.KeyGeneratorConfiguration;
import org.apache.shardingsphere.api.config.sharding.ShardingRuleConfiguration;
import org.apache.shardingsphere.api.config.sharding.TableRuleConfiguration;
import org.apache.shardingsphere.api.config.sharding.strategy.InlineShardingStrategyConfiguration;
import org.apache.shardingsphere.shardingjdbc.api.ShardingDataSourceFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.context.properties.EnableConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.*;
import java.util.concurrent.ConcurrentHashMap;

@Setter //必须，否则@ConfigurationProperties会注解失败
@Configuration
@ConfigurationProperties(prefix = DataSourceConstants.SHARDING_DATASOURCE_PREFIX) //指定前缀
@EnableConfigurationProperties //开启ConfigurationProperties
@MapperScan(basePackages = { DataSourceConstants.MAPPER_BASEPACKAGE }, sqlSessionFactoryRef = "shardingSqlSessionFactory") //指定mapper位置，指定sqlSessionFactoryRef
public class ShardingDatasourceConfig {

    private String url;

    private String username;

    private String password;

    public ShardingDatasourceConfig() {
    }

    private DataSource getDataSource(String datasourceName){
        HikariDataSource dataSource = new HikariDataSource();
        String jdbcUrl = String.format(url,datasourceName); //获取到真正的jdbcUrl
        dataSource.setJdbcUrl(jdbcUrl);
        dataSource.setUsername(username);
        dataSource.setPassword(password);
        dataSource.setDriverClassName(DataSourceConstants.DRIVER_CLASS_NAME);
        return dataSource;
    }

    @Bean("shardingDataSource")
    @Primary
    public DataSource shardingDataSource() throws SQLException {

        ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
        shardingRuleConfig.getTableRuleConfigs().add(getUserTableRuleConfiguration());//表进行分库并进行分表
        shardingRuleConfig.getTableRuleConfigs().add(getUserDetailTableRuleConfiguration()); //表进行分库并进行分表
        shardingRuleConfig.getTableRuleConfigs().add(new TableRuleConfiguration("t_order","ds_1.t_order"));//垂直分库
        shardingRuleConfig.getTableRuleConfigs().add(new TableRuleConfiguration("t_business","ds_2.t_business"));//垂直分库
        shardingRuleConfig.getBindingTableGroups().add("t_user, t_user_detail"); //绑定表
        shardingRuleConfig.setMasterSlaveRuleConfigs(getMasterSlaveRuleConfigurations()); //主从设置 一主多从
        shardingRuleConfig.setDefaultDataSourceName("ds_1"); //设置默认的数据源
        shardingRuleConfig.getBroadcastTables().add("t_address"); //设置广播表
        DataSource dataSource = ShardingDataSourceFactory.createDataSource(getDataSourceMap(), shardingRuleConfig,new Properties());
        return dataSource;
    }

    private TableRuleConfiguration getUserTableRuleConfiguration(){
        TableRuleConfiguration userTableRuleConfiguration = new TableRuleConfiguration("t_user","ds_${1..2}.t_user_${1..2}");
        userTableRuleConfiguration.setKeyGeneratorConfig(new KeyGeneratorConfiguration("SNOWFLAKE", "id", getProperties()));
        userTableRuleConfiguration.setDatabaseShardingStrategyConfig(new InlineShardingStrategyConfiguration("id", "ds_${id % 2 + 1}"));
        userTableRuleConfiguration.setTableShardingStrategyConfig(new InlineShardingStrategyConfiguration("id", "t_user_${id % 2 + 1}"));
        return userTableRuleConfiguration;
    }

    private TableRuleConfiguration getUserDetailTableRuleConfiguration(){
        TableRuleConfiguration userDetailTableRuleConfiguration = new TableRuleConfiguration("t_user_detail","ds_${1..2}.t_user_detatil_${1..2}");
        userDetailTableRuleConfiguration.setKeyGeneratorConfig(new KeyGeneratorConfiguration("SNOWFLAKE", "id", getProperties()));
        userDetailTableRuleConfiguration.setDatabaseShardingStrategyConfig(new InlineShardingStrategyConfiguration("id", "ds_${id % 2 + 1}"));
        userDetailTableRuleConfiguration.setTableShardingStrategyConfig(new InlineShardingStrategyConfiguration("id", "t_user_detail_${id % 2 + 1}"));
        return userDetailTableRuleConfiguration;
    }

    private Properties getProperties() {
        Properties result = new Properties();
        result.setProperty("worker.id", "123");
        return result;
    }

    private Map<String,DataSource> getDataSourceMap(){
        Map<String,DataSource> dataSourceMap = new HashMap<>();
        DataSource primaryDatasourceOne = getDataSource("1");
        DataSource firstSlaveDatasourceOne = getDataSource("2");
        DataSource secondSlaveDatasourceOne = getDataSource("3");
        DataSource primaryDatasourceTwo = getDataSource("4");
        DataSource firstSlaveDatasourceTwo = getDataSource("5");
        DataSource secondSlaveDatasourceTwo = getDataSource("6");

        dataSourceMap.put("ds_master_1",primaryDatasourceOne);
        dataSourceMap.put("ds_slave_1_1",firstSlaveDatasourceOne);
        dataSourceMap.put("ds_slave_1_2",secondSlaveDatasourceOne);

        dataSourceMap.put("ds_master_2",primaryDatasourceTwo);
        dataSourceMap.put("ds_slave_2_1",firstSlaveDatasourceTwo);
        dataSourceMap.put("ds_slave_2_2",secondSlaveDatasourceTwo);
        return dataSourceMap;
    }

    private List<MasterSlaveRuleConfiguration> getMasterSlaveRuleConfigurations(){

        MasterSlaveRuleConfiguration masterSlaveRuleConfigOne = new MasterSlaveRuleConfiguration("ds_1", "ds_master_1", Arrays.asList("ds_slave_1_1", "ds_slave_1_2"));
        MasterSlaveRuleConfiguration masterSlaveRuleConfigTwo = new MasterSlaveRuleConfiguration("ds_2", "ds_master_2", Arrays.asList("ds_slave_2_1", "ds_slave_2_2"));

        return Lists.newArrayList(masterSlaveRuleConfigOne,masterSlaveRuleConfigTwo);
    }

    @Bean(name = "primaryTransactionManager")
    public DataSourceTransactionManager transactionManager(@Qualifier("shardingDataSource")DataSource shardingDataSource) throws SQLException{
        return new DataSourceTransactionManager(shardingDataSource);
    }

    @Bean(name = "shardingSqlSessionFactory")
    public SqlSessionFactory primarySqlSessionFactory(@Qualifier("shardingDataSource") DataSource primaryDataSource) throws Exception{
        final SqlSessionFactoryBean sessionFactoryBean = new SqlSessionFactoryBean();
        sessionFactoryBean.setDataSource(primaryDataSource);
        return sessionFactoryBean.getObject();
    }
}

配置的基本思路
主从配置
a.获取数据源，如果设置两主三从，那就要获取6个数据源。
b.将数据源封装成map对象，key为数据源名称（在MasterSlaveRuleConfiguration中指定是主还是从），value为dataSource
c.创建MasterSlaveRuleConfigration对象，配置数据源之间主从关系
MasterSlaveRuleConfiguration masterSlaveRuleConfigOne = new MasterSlaveRuleConfiguration("ds_1", "ds_master_1", Arrays.asList("ds_slave_1_1", "ds_slave_1_2"));
ds_1为配置的名称，ds_master_1为主数据源的名称，ds_slave_1_1，ds_slave_1_2为从数据源的名称
d.将MasterSlaveRuleConfiguration转换成list
e.在ShardingRuleConfiguration中设置masterSlaveRuleConfigs为d步骤中的list即可
shardingRuleConfig.setMasterSlaveRuleConfigs(getMasterSlaveRuleConfigurations()); //主从设置 一主多从
主从数据库之间数据同步交由数据库去实现

水平分表设置
TableRuleConfiguration userTableRuleConfiguration = new TableRuleConfiguration("t_user","ds_${1..2}.t_user_${1..2}"); //指定逻辑表与物理表
userTableRuleConfiguration.setKeyGeneratorConfig(new KeyGeneratorConfiguration("SNOWFLAKE", "id", getProperties())); //id生成使用雪花算法
userTableRuleConfiguration.setDatabaseShardingStrategyConfig(new InlineShardingStrategyConfiguration("id", "ds_${id % 2 + 1}")); //定义分库规则
userTableRuleConfiguration.setTableShardingStrategyConfig(new InlineShardingStrategyConfiguration("id", "t_user_${id % 2 + 1}")); //定义分表规则
返回userTableRuleConfiguration,在ShardingRuleConfiguration中tableRuleConfigs中添加该对象，如果还想水平分别的表，增加类似的配置即可。

绑定表配置
假如两张表都进行了水平分表，那么就可以设置绑定关系了
a.直接在ShardingRuleConfigurations 中设置两张逻辑表的绑定关系即可
shardingRuleConfig.getBindingTableGroups().add("t_user, t_user_detail"); //绑定表

广播表
shardingRuleConfig.getBroadcastTables().add("t_address"); //将地址表设置广播表

设置默认的数据源
当表设计一个库中，类似没有使用sharding-jdbc的效果，defaultDataSourceName，在ShardingRuleConfigurations中配置默认的数据源即可
shardingRuleConfig.setDefaultDataSourceName("ds_1"); //设置默认的数据源
ds_1代表MasterSlaveRuleConfiguration配置的名称。

ShardingRuleConfigurations中还有这些属性
ShardingStrategyConfiguration defaultDatabaseShardingStrategyConfig;
ShardingStrategyConfiguration defaultTableShardingStrategyConfig;
KeyGeneratorConfiguration defaultKeyGeneratorConfig;
EncryptRuleConfiguration encryptRuleConfig;

4.sharding-jdbc基本原理

sharding-jdbc基本原理
1.解析: 解析定义的sql语句
2.路由：依据定义的规则修改部分sql
3.改写: 拼接成可执行的sql
4.执行: 执行sql,获取到结果集
5.归并结果：将所有结果集合并，返回结果

未完待续...