sharding-jdbc 使用實(shí)例

問題:

通過java編程式方式(非配置文件)完整示例,通過sharding-jdbc實(shí)現(xiàn):
1. 分庫,根據(jù)user_ip進(jìn)行簡(jiǎn)單的分庫示例
2. 分表,實(shí)現(xiàn)針對(duì)時(shí)間的分表
3. 讀寫分離,進(jìn)行主從集群配置

表結(jié)構(gòu):

image.png

本次測(cè)試一共建了4個(gè)庫:master1、master2、master1_1、master2_2,其中兩對(duì)主從庫.

  1. master1主寫庫----->master1_1 從讀庫
  2. master2主寫庫------> master2_2從寫庫
    其中測(cè)試表auth_user_log進(jìn)行分表測(cè)試,看下這個(gè)表結(jié)構(gòu):
CREATE TABLE if not exists
  master1.auth_user_log
(
  id bigint NOT NULL AUTO_INCREMENT,
  log_id VARCHAR(64),
  account VARCHAR(64),
  account_name VARCHAR(64) NOT NULL,
  application_name VARCHAR(64) NOT NULL,
  user_ip VARCHAR(32) NOT NULL,
  url VARCHAR(64) NOT NULL,
  request_body VARCHAR(2048) NOT NULL,
  reponse_body VARCHAR(2048) NOT NULL,
  invoke_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  invoke_cost INT,
  PRIMARY KEY (id),
  CONSTRAINT log_id UNIQUE (log_id)
  )
  ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用戶訪問記錄表';

我們通過

  1. master1和master2 兩個(gè)主庫來測(cè)試根據(jù)user_ip字段進(jìn)行分庫操作,
  2. 根據(jù)invoke_time進(jìn)行時(shí)間的分表測(cè)試
  3. 根據(jù)master1和master1_1進(jìn)行讀寫分離配置測(cè)試

源代碼路徑

https://gitee.com/kaiyang_taichi/demo-shariding-jdbc.git

數(shù)據(jù)庫腳本:

image.png

配置講解:

  1. 通過springboot配置了4個(gè)庫的數(shù)據(jù)源master1, master2, master1_1, master2_2
  2. 通過MasterSlaveRuleConfiguration進(jìn)行讀寫分離配置,可以配置多從庫,選擇從庫的負(fù)載策略,對(duì)于讀多寫少的業(yè)務(wù)場(chǎng)景很實(shí)用
  3. 然后針對(duì)單個(gè)表的配置通過TableRuleConfiguration進(jìn)行處理.
  4. 根據(jù)user_ip的hashcode進(jìn)行分庫,通過UserLogDbShardingAlgorithm類進(jìn)行處理
  5. 根據(jù)invoke_time字段進(jìn)行分表,通過 UserLogTableShardingAlgorithm和UserLogTableRangeShardingAlgorithm(查詢一段時(shí)間范圍內(nèi)的數(shù)據(jù)使用)配置.
@Configuration
public class ShardingDataSourceConfiguration {


    /**
     * 主寫庫1
     * @return
     */
    @Bean("master1")
    @ConfigurationProperties(prefix = "spring.datasource.master1")
    public DataSource dataSource1() {
        return DataSourceBuilder.create().build();
    }

    /**
     * 主寫庫2
     * @return
     */
    @Bean("master2")
    @ConfigurationProperties(prefix = "spring.datasource.master2")
    public DataSource dataSource2() {
        return DataSourceBuilder.create().build();
    }

    /**
     * 從讀庫1
     * @return
     */
    @Bean("master1_1")
    @ConfigurationProperties(prefix = "spring.datasource.master11")
    public DataSource dataSource1_1() {
        return DataSourceBuilder.create().build();
    }

    /**
     * 從讀庫2
     * @return
     */
    @Bean("master2_2")
    @ConfigurationProperties(prefix = "spring.datasource.master22")
    public DataSource dataSource2_2() {
        return DataSourceBuilder.create().build();
    }



    @Bean(name = "shardDataSource")
    @Primary
    public DataSource shardDataSource(@Qualifier("master1") DataSource dataSource,
        @Qualifier("master2") DataSource dataSource2,@Qualifier("master1_1") DataSource dataSource3,
        @Qualifier("master2_2") DataSource dataSource4) throws SQLException {

        ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();

        List<MasterSlaveRuleConfiguration> configurationList = new ArrayList<>();

        //1.讀寫分離配置一，master1為寫庫1，master1_1為讀庫1，這兩個(gè)庫將會(huì)在數(shù)據(jù)庫層面進(jìn)行配置主從數(shù)據(jù)同步
        MasterSlaveRuleConfiguration masterSlaveRuleConfiguration = new MasterSlaveRuleConfiguration();
       // 一組主從庫分組
        masterSlaveRuleConfiguration.setName("master1Config");
      //主庫名
        masterSlaveRuleConfiguration.setMasterDataSourceName("master1");
        List<String> slaves = new ArrayList<>();
        slaves.add("master1_1");
    //從庫列表
        masterSlaveRuleConfiguration.setSlaveDataSourceNames(slaves);
   //從庫的負(fù)載選擇策略        masterSlaveRuleConfiguration.setLoadBalanceAlgorithmType(MasterSlaveLoadBalanceAlgorithmType.ROUND_ROBIN);
        configurationList.add(masterSlaveRuleConfiguration);

        //2.讀寫分離配置二，master1為寫庫2，master2_2為讀庫2，這兩個(gè)庫將會(huì)在數(shù)據(jù)庫層面進(jìn)行配置主從數(shù)據(jù)同步
        MasterSlaveRuleConfiguration masterSlaveRuleConfiguration2 = new MasterSlaveRuleConfiguration();
        masterSlaveRuleConfiguration2.setName("master2Config");
        masterSlaveRuleConfiguration2.setMasterDataSourceName("master2");
        List<String> slaves2 = new ArrayList<>();
        slaves2.add("master2_2");
        masterSlaveRuleConfiguration2.setSlaveDataSourceNames(slaves2);
        masterSlaveRuleConfiguration2.setLoadBalanceAlgorithmType(MasterSlaveLoadBalanceAlgorithmType.ROUND_ROBIN);
        configurationList.add(masterSlaveRuleConfiguration2);

        //3，將讀寫分離配置加到主配置類中
        shardingRuleConfig.setMasterSlaveRuleConfigs(configurationList);

        // 4。將分庫分表規(guī)則加入到主配置類中
        shardingRuleConfig.getTableRuleConfigs().add(getUserLogTableRuleConfiguration());

        // 打印SQL
        Properties props = new Properties();
        props.put("sql.show", true);

        Map<String, DataSource> map = new HashMap<>();
        map.put("master1", dataSource);
        map.put("master2", dataSource2);
        map.put("master1_1", dataSource3);
        map.put("master2_2", dataSource4);
        return new ShardingDataSource(shardingRuleConfig.build(map),
            new ConcurrentHashMap(), props);
    }


    /**
     * 配置用戶日志讀分庫分表策略
     * 1。 根據(jù)user_ip的hashcode進(jìn)行分庫，寫入不同主庫
     *          目前主寫庫有兩個(gè)：master1，master2
     * 2。根據(jù)invoke_time進(jìn)行分表，寫入不同的子表
     *          目前子表有：
     *          auth_user_log，默認(rèn)表
     *          auth_user_log_201903，19年第三季度表
     *          auth_user_log_201904，19年第四季度表
     * @return
     */
    @Bean
    TableRuleConfiguration getUserLogTableRuleConfiguration() {
        TableRuleConfiguration orderTableRuleConfig = new TableRuleConfiguration();

        orderTableRuleConfig.setLogicTable("auth_user_log");
        orderTableRuleConfig.setLogicIndex("invoke_time");

        //設(shè)置數(shù)據(jù)庫分庫策略，根據(jù)ip的hash值，按2的倍數(shù)分庫
        orderTableRuleConfig.setDatabaseShardingStrategyConfig(
            new StandardShardingStrategyConfiguration("user_ip", UserLogDbShardingAlgorithm.class.getName()));

        // 設(shè)置分表策略
        orderTableRuleConfig.setTableShardingStrategyConfig(
            new StandardShardingStrategyConfiguration("invoke_time", UserLogTableShardingAlgorithm.class.getName(),
                UserLogTableRangeShardingAlgorithm.class.getName()));

        // 設(shè)置數(shù)據(jù)節(jié)點(diǎn)，格式為dbxx.tablexx。這里的名稱要和map的別名一致。下面兩種方式都可以
        orderTableRuleConfig.setActualDataNodes(UserLogTableRangeShardingAlgorithm.toUserLogDbConfigString());

        return orderTableRuleConfig;
    }


    @Bean(name = "transactionManager")
    public DataSourceTransactionManager registerPowerTransactionManager(
        @Qualifier("shardDataSource") DataSource shardDataSource) throws Throwable {
        DataSourceTransactionManager dataSourceTransactionManager = new DataSourceTransactionManager();
        dataSourceTransactionManager.setDataSource(shardDataSource);
        return dataSourceTransactionManager;
    }

    //
    @Bean(name = "sqlSessionFactory")
    public SqlSessionFactoryBean sqlSessionFactoryBean(@Qualifier("shardDataSource") DataSource shardDataSource)
        throws Throwable {
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(shardDataSource);
        //mapper
        ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
        Resource[] mapperResources = resolver.getResources("classpath:mapper/*Mapper.xml");
        sqlSessionFactoryBean.setMapperLocations(mapperResources);
        //mybatis-config
        ResourceLoader loader = new DefaultResourceLoader();
        Resource configLocation = loader.getResource("classpath:mybatis-config.xml");
        sqlSessionFactoryBean.setConfigLocation(configLocation);
        return sqlSessionFactoryBean;
    }

}

分庫配置講解:

  1. 簡(jiǎn)單的規(guī)則模擬
  2. 注意: 匹配配置的數(shù)據(jù)源別名,此處注意,當(dāng)配置主從集群的時(shí)候,這塊dbNmame是主從組的組名,如果不配置集群組時(shí),這里配置的是兩個(gè)庫自己的別名,如此時(shí)沒有主從的話,此處匹配master1和master2 
public class UserLogDbShardingAlgorithm implements PreciseShardingAlgorithm<String> {


    private static final String DB1 = "master1Config";

    private static final String DB2 = "master2Config";


    @Override
    public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<String> shardingValue) {
        String dbNmame;
    //簡(jiǎn)單的規(guī)則模擬場(chǎng)景,具體算法根據(jù)實(shí)際處理
        String ip = shardingValue.getValue();
        int i = ip.hashCode();
        dbNmame = i % 2 == 0 ? DB1 : DB2;
    // 匹配配置的數(shù)據(jù)源別名,此處注意,當(dāng)配置主從集群的時(shí)候,這塊dbNmame是主從組的組名,如果不配置集群組時(shí),這里配置的是兩個(gè)庫自己的別名,如此時(shí)沒有主從的話,此處匹配master1和master2
        for (String each : availableTargetNames) {
            if (each.equals(dbNmame)) {
                return each;
            }
        }

        throw new IllegalArgumentException();
    }
}

分表規(guī)則講解:

  1. 示例為RangeShardingAlgorithm類型配置進(jìn)行范圍時(shí)間段分表配置,簡(jiǎn)單equey處理邏輯類似,只是shardingValue只有一個(gè)值
  2. 我們的處理是根據(jù)傳入時(shí)間的月份進(jìn)行按季度匹配對(duì)應(yīng)表名
  3. 且此處通過dbConfig進(jìn)行配置相關(guān)所有表名,此處一樣注意master1Config此處為主從集群組名,當(dāng)不需要主從時(shí),此處配置為兩個(gè)主庫名即可
public class UserLogTableRangeShardingAlgorithm implements RangeShardingAlgorithm<Timestamp> {


    private static final String TblUserLogName = "auth_user_log";

    public static final Map<Integer, String> dbConfig = new HashMap<>();

    static {
        dbConfig.put(0, "master1Config." + TblUserLogName+",master2Config."+TblUserLogName);
        dbConfig.put(2019, "master1Config.auth_user_log_201903,master1Config.auth_user_log_201904,master2Config.auth_user_log_201903,master2Config.auth_user_log_201904");
    }

    public static String toUserLogDbConfigString() {
        Collection<String> values = dbConfig.values();
        if (values.size() > 0) {
            StringBuilder stringBuilder = new StringBuilder();
            values.stream().forEach(value -> stringBuilder.append(value).append(","));
            return stringBuilder.substring(0, stringBuilder.length() - 1);
        }
        return TblUserLogName;
    }

    @Override
    public Collection<String> doSharding(Collection<String> collection,
        RangeShardingValue<Timestamp> shardingValue) {
        List<String> result = Arrays.asList(TblUserLogName);
        Range<Timestamp> valueRange = shardingValue.getValueRange();
        Timestamp start = valueRange.lowerEndpoint();
        Timestamp end = valueRange.upperEndpoint();

        shardingTableNames(result, start);
        shardingTableNames(result, end);

        result.retainAll(collection);

        if (CollectionUtils.isEmpty(result)) {
            throw new IllegalArgumentException();
        }

        return result;
    }

/**
*根據(jù)時(shí)間進(jìn)行分季度分表配置
**/
    private void shardingTableNames(List<String> result, Timestamp start) {
        String tb_name;
        Calendar c = Calendar.getInstance();
        c.setTimeInMillis(start.getTime());
        int year = c.get(Calendar.YEAR);
        int month = c.get(Calendar.MONTH);
        if (dbConfig.keySet().contains(year)) {
            if (month >= 1 && month <= 3) {
                tb_name = TblUserLogName + "_" + year + "01";
                result.add(tb_name);
            }
            if (month >= 4 && month <= 6) {
                tb_name = TblUserLogName + "_" + year + "02";
                result.add(tb_name);
            }
            if (month >= 7 && month <= 9) {
                tb_name = TblUserLogName + "_" + year + "03";
                result.add(tb_name);
            }
            if (month >= 10 && month <= 12) {
                tb_name = TblUserLogName + "_" + year + "04";
                result.add(tb_name);
            }
        }
    }


}

總結(jié)

到此分庫、分表、讀寫分離的具體配置說明就說完了.shariding-jdbc通過客戶端在datasource層進(jìn)行封裝,在sql的執(zhí)行過程中進(jìn)行二次封裝.實(shí)現(xiàn)分庫分表規(guī)則,簡(jiǎn)單實(shí)用.不想mycat等需要運(yùn)維配置代理進(jìn)行維護(hù)處理.

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請(qǐng)結(jié)合常識(shí)與多方信息審慎甄別。
平臺(tái)聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡(jiǎn)書系信息發(fā)布平臺(tái)，僅提供信息存儲(chǔ)服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容