Sharding动态按月分表

     有功能需要用到按月分表,大体记录一下,只是简单测试了一下,可能会有问题。。。。。。

---

     springboot+mybatis+sharding，要是用分页的部分是单独配置的数据源,不想让主要业务也走sharding分页。

相关配置如下

mybatis配置 

// 配置扫描要使用sharding的mapper的的基本信息
@Configuration
@MapperScan(value = "com.mock.mapper", basePackages = "com.mock.mapper",
    sqlSessionFactoryRef = "mockSessionFactory")
public class MapperConfig {


}

sharding配置

// sharding的基本配置
@Configuration
public class DataSourceConfig  {

    @Autowired
    private Environment environment;
    
    // 为sharding
    @Bean("mockSessionFactory")
    @Autowired
    @Qualifier("mockSource")
    public SqlSessionFactory createSqlSessionFactory(DataSource dataSource) throws Exception {
        
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        Resource[] resources = new PathMatchingResourcePatternResolver()
            .getResources("classpath*:/mapper/**Mapper.xml");
        bean.setMapperLocations(resources);
        return bean.getObject();
    }
    
    @Bean("mockDataSource")
    public DataSource createSharedingDataSource() throws SQLException {
        // sharding规则
        ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
        
        // sharding分表规则
        TableRuleConfiguration tableConfig = new TableRuleConfiguration();
        // 设置逻辑表名(该配置可能没用,没有详细测试),在mapper.xml中可以使用逻辑表名直接查询
        tableConfig.setLogicTable("table");
        // 因为是动态分表,所以没有设置ActualDataNodes
        //tableConfig.setActualDataNodes("table");
        // 设置分表字段, TableShardingAlgorithm是具体的分表逻辑.
        ShardingStrategyConfiguration tableShardingStrategyConfig =
            new ComplexShardingStrategyConfiguration("create_time", TableShardingAlgorithm.class.getName());
        

        // 项目中使用的一个库,没有进行分库的操作         
        //shardingRuleConfig.setDefaultDatabaseShardingStrategyConfig(strategyConfig);


        // 设置分表规则
        tableConfig.setTableShardingStrategyConfig(tableShardingStrategyConfig);
        
        // 设置分表规则              
        shardingRuleConfig
                .setDefaultTableShardingStrategyConfig(tableShardingStrategyConfig);
        
        List<TableRuleConfiguration> configs = new ArrayList<>(1);
        configs.add(tableConfig);
        shardingRuleConfig.setTableRuleConfigs(configs);
        
        // 创建shardingDataSource
        return ShardingDataSourceFactory.createDataSource(createDataSourceMap(), shardingRuleConfig,
            new HashMap<String, Object>(), new Properties());
    }
    
    // 为sharding设置一个数据源,当前只用了一个
    private Map<String, DataSource> createDataSourceMap() {
        Map<String, DataSource> result = new HashMap<>();
        result.put("mockDatasource", buildDataSource());
        return result;
    }

    private DataSource buildDataSource() {
        
        //配置单独数据源,测试用的HikariCP数据源,具体看实际情况
        HikariConfig hikariConfig = new HikariConfig();
        hikariConfig.setJdbcUrl(environment.getProperty("spring.datasource.url"));
        hikariConfig.setUsername(environment.getProperty("spring.datasource.username"));
        hikariConfig.setPassword(environment.getProperty("spring.datasource.password"));
        hikariConfig.addDataSourceProperty("cachePrepStmts", "true");
        hikariConfig.addDataSourceProperty("prepStmtCacheSize", "250");
        hikariConfig.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");
        
        return new HikariDataSource(hikariConfig);
    }
    
}

  sharding的分表逻辑,sharding没有给具体实现分表逻辑,具体要查询哪张表是要自己实现的,这里就是根据传入的日期来组装成表名,比如table_2019_01。分表逻辑代码来自  OliverAAAAAA的文章

package com.fantaike.master.config;

import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Collection;
import java.util.Date;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

import org.apache.commons.lang3.StringUtils;

import com.fantaike.template.util.DateUtils;
import com.google.common.collect.Lists;
import com.google.common.collect.Range;

import io.shardingjdbc.core.api.algorithm.sharding.ListShardingValue;
import io.shardingjdbc.core.api.algorithm.sharding.PreciseShardingValue;
import io.shardingjdbc.core.api.algorithm.sharding.RangeShardingValue;
import io.shardingjdbc.core.api.algorithm.sharding.ShardingValue;
import io.shardingjdbc.core.api.algorithm.sharding.complex.ComplexKeysShardingAlgorithm;

/**
 * @ClassName:TableShardingAlgorithm
 * @Description: 复杂分片
 * @author 哈哈
 * @date 我也不会
 */
public class TableShardingAlgorithm implements ComplexKeysShardingAlgorithm{

    /**
     *  @param availableTargetNames :available data sources or tables's names
     *  @param shardingValues : sharding values
     *  @return sharding results for data sources or tables's names
     */
    @SuppressWarnings("unchecked")
    @Override
    public Collection<String> doSharding(Collection<String> availableTargetNames,
        Collection<ShardingValue> shardingValues) {
        
        // 返回表名集合
        Collection<String> routTables = new HashSet<>();
        
        if (shardingValues != null) {
            
            for (ShardingValue shardingValue : shardingValues) {
                
                // 相等比较时
                if (shardingValue instanceof PreciseShardingValue) {
                    PreciseShardingValue<Date> preciseShardingValue = (PreciseShardingValue<Date>) shardingValue;
                    
                    Date value = preciseShardingValue.getValue();
                    String routTable = getRoutTable(preciseShardingValue.getLogicTableName(), value);
                    if (StringUtils.isNotBlank(routTable)) {
                        routTables.add(routTable);
                    }
                } else if (shardingValue instanceof RangeShardingValue) {
                    // 查询范围比较时
                    
                    RangeShardingValue<Date> rangeShardingValue = (RangeShardingValue<Date>) shardingValue;
                    Range<Date> valueRange = rangeShardingValue.getValueRange();
                    Date lowerEnd = valueRange.lowerEndpoint();
                    Date upperEnd = valueRange.upperEndpoint();
 
                    Collection<String> tables = getRoutTable(shardingValue.getLogicTableName(), lowerEnd, upperEnd);
                    if (tables != null && !tables.isEmpty()) {
                        routTables.addAll(tables);
                    }
                    
                } else if (shardingValue instanceof ListShardingValue) {
                    // 多个参数
                    
                    ListShardingValue<Date> rangeShardingValue = (ListShardingValue<Date>) shardingValue;
                    Collection<Date> values = rangeShardingValue.getValues();
                    for (Date date : values) {
                        String routTable = getRoutTable(shardingValue.getLogicTableName(), date);
                        if (StringUtils.isNotBlank(routTable)) {
                            routTables.add(routTable);
                        }
                    }
                    
                }
                
                if (routTables != null && !routTables.isEmpty()) {
                    return routTables;
                }
                
            }
        }
        
        throw new UnsupportedOperationException();
    }
    
    // 根据传入的时间获取表名
    private String getRoutTable(String logicTable, Date keyValue) {
        if (keyValue != null) {
            String formatDate = DateUtils.getMonth(keyValue);
            return logicTable + "_" +formatDate;
        }
        return null;
    }

    private static List<String> getRangeNameList(Date start, Date end) {
        List<String> result = Lists.newArrayList();
        Calendar dd = Calendar.getInstance();// 定义日期实例

        dd.setTime(start);// 设置日期起始时间

        while (dd.getTime().before(end)) {// 判断是否到结束日期

            SimpleDateFormat sdf = new SimpleDateFormat("_yyyy_MM");

            String str = sdf.format(dd.getTime());

            result.add(str);

            dd.add(Calendar.MONTH, 1);// 进行当前日期月份加1
        }
        return result;
    }

    private Collection<String> getRoutTable(String logicTable, Date lowerEnd, Date upperEnd) {
        Set<String> routTables = new HashSet<>();
        if (lowerEnd != null && upperEnd != null) {
            List<String> rangeNameList = getRangeNameList(lowerEnd, upperEnd);
            for (String string : rangeNameList) {
                routTables.add(logicTable + string);
            }
        }
        return routTables;
    }
}

以上是基本配置

---

关于动态建表,临时做法是在每个月月初的时候定时创建下个月的表,在项目启动的时候检测是否有该月和下个月的表,如果没有的话就创建,临时没想到别的办法。

注意:

     在使用的时候,出现过因为没有传递参数,导致没有走sharding的分表策略,直接去查逻辑表了,临时认为是sharding会根据参数看是否需要分表.

    mapper {

      xxx select();

      xxx select( @Param("date") Date date); 

  }

    mockMapper.select(); 不会走分表

    mockMapper.select(new Date());  sql中要不要用没有具体测试   

    select * from table where crerate_time > #{date}

     

pom应用sharding

         <dependency>
            <groupId>io.shardingjdbc</groupId>
            <artifactId>sharding-jdbc-core</artifactId>
            <version>2.0.3</version>
        </dependency>

以上,临时记一下,有问题欢迎指出，反正我也不看。