Sharding-jdbc-v4.1.1实现按月份分表

首先感谢原文章博主@simians提供的思路和解决方案，本文主要是对sharding-jdbc4.1.1版本的实现，后续会升级至5.0

本文针对实际电商进销存项目中，订单数据一般根据时间可以分为冷热数据，针对此场景，实现订单表order按月分组的场景:order_202110,order_202211,order_202212
springbootv2.4x kotlin1.5.x mysql8.0 sharding-jdbc4.1.1
开发语言使用的Kotlin，不过不妨使用Java的小伙伴能清晰的理解，持续更新完善文章，有疑问可以留言

1. 使用maven在pom中添加sharding-jdbc依赖

    <!--sharding-jdbc -->
    <dependency>
        <groupId>org.apache.shardingsphere</groupId>
        <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
        <version>4.1.1</version>
    </dependency>

2. 分表需求

在实际项目中，电商订单表每个月的平均订单量稳定在300W左右，但是订单表一般有效的热数据为3个月以内，因此订单表order&订单子表（订单商品表）order_item，在实际使用的过程中，我们希望实现order_202110,order_item_202110的分表效果，再根据id或订单号能查询到订单详情的同时订单又支持前端可操作的时间范围202110~202111来进行跨表的列表查询。

3. 分表思路

1. 使用sharding-jdbc 自带的雪花算法可以生成连续递增的分布式主键id，并且可以逆推得到id的时间戳，因此在生成主键id的同时，也得到了该记录的created_at(创建时间)；

2. order在按月份进行实际的分片的过程中，并没有按照created_at作为分片键，这样会使我们只通过id去查询时，无法确定该条记录分布在哪个表里面；

3. order表将id作为分片键，即sharding-column = id；将分布式主键生成算法定为sharding-jdbc自带的雪花算法，即key-generator.column=id;key-generator.type=snowflake,如果同时实现id精准查询和时间范围查询,那么还需要我们自定义分片的算法TimeShardingAlgorithm；

4. order表在确定分片方案后，那么order_item表呢，实际需求中，对order_item的查询，仅仅只通过order_item表中的order_id进行结果集的查询，但是我们又希望order和order_item的分表后缀时间时一致的，例如一个订单和商品明细分布在order_202110和order_item_202110中，而不是有概率的出现在order_202110和order_item_202111中；

5. order_item表将order_id作为分片键，即sharding-column=order_id；order_item表中的主键id也要保证不重复，所以将分布式主键生成算法定为sharding-jdbc自带的雪花算法，即key-generator.column=id;key-generator.type=snowflake,如果实现order_id精准查询查询,那么还需要我们自定义分片的算法TimeShardingAlgorithm；

4. 分表的配置文件application-sharding.properties

sharding-jdbc 按月份分表需要自己实现。需要实现PreciseShardingAlgorithm，RangeShardingAlgorithm的两个接口。并在配置文件里添加实现路径

目前只有一个数据库，无多库的场景，为了方便我是使用的官方的properties文件，如果有yml文件的需求，可以通过百度在线转换

# 显示SQL
spring.shardingsphere.props.sql.show=true

# 数据源，单数据库
spring.shardingsphere.datasource.names=ds

spring.shardingsphere.datasource.ds.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds.jdbc-url=jdbc:mysql://127.0.0.1:3306/table_name?useUnicode=true&serverTimezone=Asia/Shanghai&useSSL=false&autoReconnect=true
spring.shardingsphere.datasource.ds.username=root
spring.shardingsphere.datasource.ds.password=root
spring.shardingsphere.datasource.ds.max-active=16

# order
#跨年分表
#spring.shardingsphere.sharding.tables.order.actual-data-nodes=ds.order_$->{2021..2025}0$->{1..9},ds.order_$->{2021..2025}$->{10..11}

#不跨年
spring.shardingsphere.sharding.tables.order.actual-data-nodes=ds.order_2021$->{10..12}
# 配置分片键
spring.shardingsphere.sharding.tables.order.table-strategy.standard.sharding-column=id
# 配置精准匹配自定义的算法
spring.shardingsphere.sharding.tables.order.table-strategy.standard.precise-algorithm-class-name=com.ice.uly.config.shading.TimeShardingAlgorithm
# 配置范围查询自定义的算法
spring.shardingsphere.sharding.tables.order.table-strategy.standard.range-algorithm-class-name=com.ice.uly.config.shading.TimeShardingAlgorithm
# 配置分布式主键生成算法
spring.shardingsphere.sharding.tables.order.key-generator.column=id
spring.shardingsphere.sharding.tables.order.key-generator.type=snowflake

# order_item
#spring.shardingsphere.sharding.tables.order_item.actual-data-nodes=ds.order_item_$->{2021..2025}0$->{1..9},ds.order_item_$->{2021..2025}$->{10..11}
spring.shardingsphere.sharding.tables.order_item.actual-data-nodes=ds.order_item_2021$->{10..12}
spring.shardingsphere.sharding.tables.order_item.table-strategy.standard.sharding-column=order_id
spring.shardingsphere.sharding.tables.order_item.table-strategy.standard.precise-algorithm-class-name=com.ice.uly.config.shading.TimeShardingAlgorithm
spring.shardingsphere.sharding.tables.order_item.table-strategy.standard.range-algorithm-class-name=com.ice.uly.config.shading.TimeShardingAlgorithm
spring.shardingsphere.sharding.tables.order_item.key-generator.column=id
spring.shardingsphere.sharding.tables.order_item.key-generator.type=snowflake

# order_receiver
#spring.shardingsphere.sharding.tables.order.actual-data-nodes=ds.order_$->{2021..2025}0$->{1..9},ds.order_$->{2021..2025}$->{10..11}
spring.shardingsphere.sharding.tables.order_receiver.actual-data-nodes=ds.order_receiver_2021$->{10..12}
spring.shardingsphere.sharding.tables.order_receiver.table-strategy.standard.sharding-column=id
spring.shardingsphere.sharding.tables.order_receiver.table-strategy.standard.precise-algorithm-class-name=com.ice.uly.config.shading.TimeShardingAlgorithm
spring.shardingsphere.sharding.tables.order_receiver.table-strategy.standard.range-algorithm-class-name=com.ice.uly.config.shading.TimeShardingAlgorithm
spring.shardingsphere.sharding.tables.order_receiver.key-generator.column=id
spring.shardingsphere.sharding.tables.order_receiver.key-generator.type=snowflake

spring.shardingsphere.sharding.binding-tables=order
spring.shardingsphere.sharding.tables.order.key-generator.props.worker.id=123

5. 自定义实现按月分表的算法，当然大家可以优化此实现过程的代码，此处只提供思路

package com.ice.uly.config.shading

import com.ice.uly.common.sharing.ShardingTool
import com.ice.uly.enums.OmsErrorCodeEnum
import com.ice.uly.exception.OmsBizException
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue
import org.apache.shardingsphere.api.sharding.standard.RangeShardingAlgorithm
import org.apache.shardingsphere.api.sharding.standard.RangeShardingValue
import java.time.LocalDate
import java.time.YearMonth
import java.time.format.DateTimeFormatter


/**
 * @description: 业务表按时间分片的算法
 * @author ULY
 * @date 2021/10/8 17:25
 */
class TimeShardingAlgorithm : PreciseShardingAlgorithm<Long>, RangeShardingAlgorithm<Long> {
    private val yearAndMonth = DateTimeFormatter.ofPattern("yyyyMM")

    override fun doSharding(availableTargetNames: Collection<String>, shardingValue: PreciseShardingValue<Long>): String {
        val tableName = StringBuffer()
        tableName.append(shardingValue.logicTableName)
            .append("_").append(ShardingTool.getYearAndMonth(shardingValue.value!!))
        return tableName.toString()
    }

    override fun doSharding(availableTargetNames: MutableCollection<String>, shardingValue: RangeShardingValue<Long>): MutableCollection<String> {
        val result: MutableCollection<String> = LinkedHashSet()
        val shardingKey = shardingValue.valueRange
        val startShardingKey = shardingKey.lowerEndpoint()
        val endShardingKey = shardingKey.upperEndpoint()

        // 获取到开始时间,202101
        val startTimeStr = ShardingTool.getYearAndMonth(startShardingKey)
        // 获取结束时间,202512
        val endTimeStr = ShardingTool.getYearAndMonth(endShardingKey)

        // 获取起止日期的月初日期
        val startYearMonth = YearMonth.parse(startTimeStr, yearAndMonth)
        val endYearMonth = YearMonth.parse(endTimeStr, yearAndMonth)

        if (startYearMonth.year == endYearMonth.year) { // 范围不跨年
            var start = startTimeStr.toInt()
            val end = endTimeStr.toInt()
            while (start <= end) {
                val tableName = shardingValue.logicTableName.toString() + "_" + start
                result.add(tableName)
                start++
            }
        } else if (startYearMonth.year < endYearMonth.year) { // 范围跨年
            val startDate = LocalDate.of(startYearMonth.year, startYearMonth.monthValue, 1)
            val endDate = LocalDate.of(endYearMonth.year, endYearMonth.monthValue, 1)

            var currDate = startDate
            while (currDate.isBefore(endDate)) {
                val tableName = shardingValue.logicTableName.toString() + "_" + currDate.format(yearAndMonth)
                result.add(tableName)
                currDate = currDate.plusDays(1)
            }
            result.add(shardingValue.logicTableName.toString() + "_" + endTimeStr)
        } else {
            // 此处自定义异常可替换
            throw OmsBizException(OmsErrorCodeEnum.DB_DATA_ACCESS)
        }
        // 过滤只保留配置中可用的表
        return result.intersect(availableTargetNames).toMutableSet()
    }
}

6. sharding-jdbc自带的雪花算法的逆向获取时间戳的工具类

package com.ice.uly.common.sharing

import org.apache.shardingsphere.core.strategy.keygen.SnowflakeShardingKeyGenerator
import java.time.Instant
import java.time.LocalDateTime
import java.time.ZoneId
import java.time.format.DateTimeFormatter


/**
 * @description: TODO
 * @author ULY
 * @date 2021/10/8 17:36
 */
class ShardingTool {

    companion object {
        private val yearAndMonth = DateTimeFormatter.ofPattern("yyyyMM")
        private val year = DateTimeFormatter.ofPattern("yyyy")

        fun getYear(shardingKey: Long): String {
            val instant = Instant.ofEpochMilli(SnowflakeShardingKeyGenerator.EPOCH + (shardingKey shr 22))
            val localDateTime = LocalDateTime.ofInstant(instant, ZoneId.systemDefault())
            return year.format(localDateTime)
        }

        fun getYearAndMonth(shardingKey: Long): String {
            val instant = Instant.ofEpochMilli(SnowflakeShardingKeyGenerator.EPOCH + (shardingKey shr 22))
            val localDateTime = LocalDateTime.ofInstant(instant, ZoneId.systemDefault())
            return yearAndMonth.format(localDateTime)
        }

        fun generateKey(time: Long): Long {
            val shardingKey = time - SnowflakeShardingKeyGenerator.EPOCH shl 22
            println(shardingKey)
            println(getYearAndMonth(shardingKey))
            return shardingKey
        }
    }
}

7.使用数据库

order_202110,order_202111,order_202112
order_item_202110,order_item_202111,order_item_202112