sharding-jdbc 实现按月分表

则好

7673人浏览 · 2022-06-11 20:15:20

则好 · 2022-06-11 20:15:20 发布

最近生产上发现了一个大表，因为数据太多导致新增查询都很慢，考虑先对历史数据进行归档，新数据按月分表存储。使用到的框架主要是：sharding-jdbc、spring boot、mybatis、durid，先建个demo简单实践下。

首先，准备一个分片的表

CREATE TABLE `t_log` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `log` varchar(256) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `time` varchar(12) DEFAULT NULL,
  `created_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

接着，创建一个新的项目，引入相关依赖，这用的是sharding-jdbc 4.0.0-RC1版本：

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>1.5.3.RELEASE</version>
        <relativePath/>
    </parent>

    <properties>
        <java.version>1.8</java.version>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.3.0</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.22</version>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>4.0.0-RC1</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.21</version>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
            <plugin>
                <groupId>org.mybatis.generator</groupId>
                <artifactId>mybatis-generator-maven-plugin</artifactId>
                <version>1.3.2</version>
                <configuration>
                    <verbose>true</verbose>
                    <overwrite>false</overwrite>
                </configuration>
                <dependencies>
                    <dependency>
                        <groupId>mysql</groupId>
                        <artifactId>mysql-connector-java</artifactId>
                        <version>8.0.22</version>
                    </dependency>
                </dependencies>
            </plugin>
        </plugins>
    </build>

添加配置：

# 配置存放到内存中
spring.shardingsphere.mode.type=Memory
# 打印sql日志
spring.shardingsphere.props.sql.show=true
# 配置数据源
spring.shardingsphere.datasource.names=ds
spring.shardingsphere.datasource.ds.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds.url=jdbc:mysql://192.168.3.4:3306/sharding?autoReconnect=true&useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&useSSL=false
spring.shardingsphere.datasource.ds.username=root
spring.shardingsphere.datasource.ds.password=root

# 配置数据节点，这里是按月分表，时间范围设置在202201 ~ 210012
spring.shardingsphere.sharding.tables.t_log.actual-data-nodes=ds.t_log_$->{2022..2100}0$->{1..9},ds.t_log_$->{2022..2100}1$->{0..2}
# 使用标准分片策略，配置分片字段
spring.shardingsphere.sharding.tables.t_log.table-strategy.standard.sharding-column=time
# 配置精确、范围查询分片算法
spring.shardingsphere.sharding.tables.t_log.table-strategy.standard.precise-algorithm-class-name=com.example.springboot.algorithm.TimeShardingAlgorithm
spring.shardingsphere.sharding.tables.t_log.table-strategy.standard.range-algorithm-class-name=com.example.springboot.algorithm.TimeShardingAlgorithm
# 配置主键以及生成算法
spring.shardingsphere.sharding.tables.t_log.key-generator.column=id
spring.shardingsphere.sharding.tables.t_log.key-generator.type=SNOWFLAKE

这里要注意下precise-algorithm是配置精确分片算法实现类（如=、in），range-algorithm是配置范围分片算法实现类（如between and），实现如下：

package com.example.springboot.algorithm;

import com.google.common.collect.Range;
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.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.Collection;

/**
 * 分片算法，按月分片
 */
public class TimeShardingAlgorithm implements PreciseShardingAlgorithm<String>, RangeShardingAlgorithm<String> {

    /**
     * 需要空构造方法
     */
    public TimeShardingAlgorithm() {}

    /**
     * 时间格式
     */
    private static final DateTimeFormatter DATE_TIME_FORMATTER = DateTimeFormatter.ofPattern("yyyyMMdd");

    /**
     * 精确分片
     * @param collection
     * @param preciseShardingValue
     * @return
     */
    @Override
    public String doSharding(Collection<String> collection, PreciseShardingValue<String> preciseShardingValue) {
        return buildShardingTable(preciseShardingValue.getLogicTableName(), preciseShardingValue.getValue());
    }

    /**
     * 构建分片后的表名
     * @param logicTableName
     * @param date
     * @return
     */
    private String buildShardingTable(String logicTableName, String date) {
        StringBuffer stringBuffer = new StringBuffer(logicTableName).append("_").append(date, 0, 6);
        return stringBuffer.toString();
    }

    /**
     * 范围分片
     * @param collection
     * @param rangeShardingValue
     * @return
     */
    @Override
    public Collection<String> doSharding(Collection<String> collection, RangeShardingValue<String> rangeShardingValue) {
        Range<String> valueRange = rangeShardingValue.getValueRange();
        String lower = valueRange.lowerEndpoint();
        String upper = valueRange.upperEndpoint();

        LocalDate start = LocalDate.parse(lower, DATE_TIME_FORMATTER);
        LocalDate end = LocalDate.parse(upper, DATE_TIME_FORMATTER);

        Collection<String> tables = new ArrayList<>();
        while (start.compareTo(end) <= 0) {
            tables.add(buildShardingTable(rangeShardingValue.getLogicTableName(), start.format(DATE_TIME_FORMATTER)));
            start = start.plusMonths(1L);
        }
        
        // collection配置的数据节点表，这里是排除不存在配置中的表
        collection.retainAll(tables);
        return collection;
    }

}

添加一个controller进行测试：

package com.example.springboot.controller;

import com.example.springboot.bean.log.Log;
import com.example.springboot.bean.log.LogExample;
import com.example.springboot.service.ILogService;
import com.google.common.collect.Lists;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

@RestController
public class LonController {

    @Autowired
    private ILogService logService;

    /**
     * 测试新增
     * @param logStr
     * @return
     */
    @RequestMapping("/insert")
    public Object insert(String logStr) {
        Log log = new Log();
        log.setTime(new SimpleDateFormat("yyyyMMdd").format(new Date()));
        log.setLog(logStr);
        log.setCreatedTime(new Date());
        return logService.insertSelective(log);
    }

    /**
     * 测试批量新增
     * @return
     */
    @RequestMapping("/inserts")
    public Object insert() {
        Log log = new Log();
//        log.setTime(new SimpleDateFormat("yyyyMMdd").format(new Date()));
        log.setTime("202203");
        log.setLog("inserts 1");
        log.setCreatedTime(new Date());
        Log log1 = new Log();
//        log1.setTime(new SimpleDateFormat("yyyyMMdd").format(new Date()));
        log1.setTime("202202");
        log1.setLog("inserts 2");
        log1.setCreatedTime(new Date());
        Log log2 = new Log();
        log2.setTime("202202");
        log2.setLog("inserts 3");
        log2.setCreatedTime(new Date());
        List<Log> lists = Lists.newArrayList(log, log1, log2);
        logService.insertBatch(lists);
        return lists;
    }

    /**
     * 测试删除
     * @param id
     * @return
     */
    @RequestMapping("/delete")
    public Object delete(Long id) {
        return logService.deleteByPrimaryKey(id);
    }

    /**
     * 测试查询
     * @param id
     * @return
     */
    @RequestMapping("/select")
    public Object select(Long id) {
        return logService.selectByPrimaryKey(id);
    }

    /**
     * 按分片字段查询
     * @param time
     * @return
     */
    @RequestMapping("/selects")
    public Object selects(String time) {
        LogExample example = new LogExample();
        example.createCriteria().andTimeEqualTo(time);
        return logService.selectByExample(example);
    }

    /**
     * 范围查询
     * @param times
     * @param timee
     * @return
     */
    @RequestMapping("/selectr")
    public Object selectr(String times, String timee) {
        LogExample example = new LogExample();
        example.createCriteria().andTimeBetween(times, timee);
        return logService.selectByExample(example);
    }

    /**
     * 更新
     * @param id
     * @return
     */
    @RequestMapping("/update")
    public Object update(Long id) {
        Log log = new Log();
        log.setId(id);
        log.setTime(new SimpleDateFormat("yyyyMMdd").format(new Date()));
        log.setLog("updated");
        log.setCreatedTime(new Date());
        return logService.updateByPrimaryKeySelective(log);
    }

}

最后调用接口进行测试，控制台会打印逻辑SQL以及实际上执行的SQL，如下：

http://127.0.0.1:8081/insert?logStr=test