ShardingJDBC实战

云Pass平台面向多租户，需要每日统计各租户的具体使用量，用于向租户展示使用量详情和汇总每月使用量，以便对租户账户进行扣款和与租户对账。平台租户30万+，各租户每天产生的各类统计信息大致在30+，每月产生的数据记录数接近千万，因此进行水平分库分表是十分必要的。

ShardingSphere-JDBC

ShardingSphere-JDBC是Apache ShardingSphere的第一个产品，也是Apache ShardingSphere的前身。定位为轻量级Java框架，在Java的JDBC层提供的额外功能。它使用客户端直连数据库，以jar包形式提供服务，无需额外部署和依赖，可理解为增强版的JDBC驱动，完全兼容JDBC和各种ORM框架。

• 适用于任何基于JDBC的ORM框架，如：JPA，Hibernate，MyBatis,Spring JDBC Template或直接使用JDBC；
• 支持任何第三方的数据库连接池，如：DBCP，C3P0，BoneCP,HikariCP等
• 支持任意实现JDBC规范的数据库，目前支持MySQL，PostgreSQL,Oracle，SQLServer以及任何可使用JDBC访问的数据库

POM依赖

    org.apache.shardingsphere
    shardingsphere-jdbc-core-spring-boot-starter


     org.springframework.boot
     spring-boot-starter-data-jpa

配置文件 application.properties

采用标准分片策略实现数据按年月分表存储

server.port=9090

spring.shardingsphere.datasource.names=ds
spring.shardingsphere.datasource.ds.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds.jdbc-url=jdbc:mysql://localhost:3306/usage?serverTimezone=GMT%2b8&useSSL=false&nullNamePatternMatchesAll=true
spring.shardingsphere.datasource.ds.username=root
spring.shardingsphere.datasource.ds.password=123456
spring.shardingsphere.datasource.ds.max-active=16
#是否开启SQL显示，默认值: false
spring.shardingsphere.props.sql.show=true

#actual-data-nodes 需要明确指定数据库中的所有实际物理表
#这里只给出了逻辑表是因为采用标准分片策略动态生成要查询的实际物理表
#此时要求每次查询都必须携带查询参数分表键st_time
#sharding jdbc在单次查询不携带分表键时会全局扫描所有实际物理表，带来的性能损耗是不被允许的

#由数据源名 + 表名组成，以小数点分隔。多个表以逗号分隔，支持inline表达式。
#缺省表示使用已知数据源与逻辑表名称生成数据节点。
#用于广播表（即每个库中都需要一个同样的表用于关联查询，多为字典表）或只分库不分表且所有库的表结构完全一致的情况
spring.shardingsphere.sharding.tables.data_usage.actual-data-nodes=ds.data_usage
#用于单分片键的标准分片场景
spring.shardingsphere.sharding.tables.data_usage.table-strategy.standard.sharding-column=st_time
#精确分片算法类名称，用于=和IN。该类需实现PreciseShardingAlgorithm接口并提供无参数的构造器
spring.shardingsphere.sharding.tables.data_usage.table-strategy.standard.precise-algorithm-class-name=com.izx.sharding.algorithm.StatisticShardingAlgorithm
#范围分片算法类名称，用于BETWEEN，可选。该类需实现RangeShardingAlgorithm接口并提供无参数的构造器
spring.shardingsphere.sharding.tables.data_usage.table-strategy.standard.range-algorithm-class-name=com.izx.sharding.algorithm.StatisticShardingAlgorithm

spring.jackson.date-format=yyyy-MM-dd HH:mm:ss
# 时区
spring.jackson.time-zone=GMT+8
# 是否返回时间戳配置
spring.jackson.serialization.write-dates-as-timestamps=false	



logging.level.org.hibernate.type.descriptor.sql.BasicBinder=trace

实现标准分片策略

/*基于日期的标准分片算法*/
public class StatisticShardingAlgorithm
        implements PreciseShardingAlgorithm, RangeShardingAlgorithm {

    /**
     * 精确分片算法类名称，用于=和IN
     *
     * @param collection
     * @param preciseShardingValue
     * @return
     */
    @Override
    public String doSharding(Collection collection,
            PreciseShardingValue preciseShardingValue) {

        if (!CollectionUtils.isEmpty(collection)) {
            String logicTable = collection.stream().findFirst().get();
            return DataUtil.getTableByDate(logicTable, preciseShardingValue.getValue());
        } else {
            throw new IllegalArgumentException(
                    "sharding jdbc not find logic table,please check config");
        }

    }

    /**
     * 范围分片算法类名称，用于BETWEEN，可选
     *
     * @param collection
     * @param rangeShardingValue
     * @return
     */
    @Override public Collection doSharding(Collection collection,
            RangeShardingValue rangeShardingValue) {
        if (!CollectionUtils.isEmpty(collection)) {
            String logicTable = collection.stream().findFirst().get();
            Range range = rangeShardingValue.getValueRange();
            Date start = range.lowerEndpoint();
            Date end = range.upperEndpoint();

            return DataUtil.getTableSet(logicTable, start, end);
        } else {
            throw new IllegalArgumentException(
                    "sharding jdbc not find logic table,please check config");
        }

    }
}

动态创建表

        手动维护每月数据分表是不能接受的，分享一种自动复制当月表生成下一月实际物理分表的实现方式。

@Slf4j
@Component
public class InitTableScheduler implements InitializingBean {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Autowired
    private Environment environment;

    private static final Bindable> STRING_SHARDING_TABLE_MAP = Bindable
            .mapOf(String.class, ShardingTable.class);

    private Map shardingTableMap;

    //@Scheduled(cron = "：0 0 2 L * ?") 每月最后一天凌晨2点同步当月表结构至下一月分表
    @Scheduled(cron = "0 0/1 * * * ?")
    public void dauScheduler() {
        if (!CollectionUtils.isEmpty(shardingTableMap)) {
            shardingTableMap.keySet().forEach(shardingTable -> copyTable(shardingTable, 1, 0, 0));
        }
    }

    private void copyTable(String table, Integer targetMonthDistance, Integer sourceMonthDistance,
            Integer times) {
        if (times > 10) {
            return;
        }
        Instant target =
                LocalDateTime.now().plusMonths(targetMonthDistance).atZone(ZoneId.systemDefault())
                        .toInstant();
        Instant source =
                LocalDateTime.now().plusMonths(sourceMonthDistance).atZone(ZoneId.systemDefault())
                        .toInstant();

        String targetTable = DataUtil.getTableNameByDate(table, Date.from(target), false),
                sourceTable = DataUtil.getTableNameByDate(table, Date.from(source), false);
        try {
            log.info("create table start . sourceTable:{},targetTable:{}", sourceTable,
                    targetTable);
            jdbcTemplate.execute(
                    String.format("CREATE TABLE IF NOT EXISTS %s LIKE %s", targetTable,
                            sourceTable));
            log.info("create table end. sourceTable:{},targetTable:{}", sourceTable, targetTable);
        } catch (BadSqlGrammarException be) {
            if (be.getMessage().contains("already exists")) {
                log.warn("table already create.sourceTable:{},targetTable:{}", sourceTable,
                        targetTable);
                return;
            }
            copyTable(table, targetMonthDistance, sourceMonthDistance, times + 1);
        } catch (Exception e) {
            log.error("create table is failed,targetTable:{},sourceTableName:{}", targetTable,
                    sourceTable, e);
            copyTable(table, targetMonthDistance, sourceMonthDistance, times + 1);
        }
    }

    @Override public void afterPropertiesSet() throws Exception {
        shardingTableMap = Binder.get(environment)
                .bind("spring.shardingsphere.sharding.tables", STRING_SHARDING_TABLE_MAP)
                .orElse(Collections.emptyMap());
        log.info("sharding tables:{}", shardingTableMap);
    }

    @Data
    public static class ShardingTable {
        @JsonProperty("actual-data-nodes")
        private String actualDataNodes;
    }
}

只需在项目上线时初始化一次物理分表，下月将会自动复制当前月表用以生成下一月的物理分表。 

 效果演示

curl --location --request GET 'localhost:9090/statistic/range?start_at=2022-04-07&end_at=2022-06-07'

使用ShardingKey范围查询分表数据时，Sharding JDBC自动映射逻辑表至数据库物理分表中。

 示例代码

GitHub - zuotaorui/sgarding-demo