最近生产上发现了一个大表,因为数据太多导致新增查询都很慢,考虑先对历史数据进行归档,新数据按月分表存储。使用到的框架主要是: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版本:
org.springframework.boot spring-boot-starter-parent1.5.3.RELEASE 1.8 UTF-8 org.springframework.boot spring-boot-starter-testtest org.springframework.boot spring-boot-starter-weborg.mybatis.spring.boot mybatis-spring-boot-starter1.3.0 mysql mysql-connector-java8.0.22 runtime org.apache.shardingsphere sharding-jdbc-spring-boot-starter4.0.0-RC1 com.alibaba druid-spring-boot-starter1.1.21 org.springframework.boot spring-boot-maven-pluginorg.mybatis.generator mybatis-generator-maven-plugin1.3.2 true false mysql mysql-connector-java8.0.22
添加配置:
# 配置存放到内存中
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, RangeShardingAlgorithm { public TimeShardingAlgorithm() {} private static final DateTimeFormatter DATE_TIME_FORMATTER = DateTimeFormatter.ofPattern("yyyyMMdd"); @Override public String doSharding(Collection collection, PreciseShardingValue preciseShardingValue) { return buildShardingTable(preciseShardingValue.getLogicTableName(), preciseShardingValue.getValue()); } private String buildShardingTable(String logicTableName, String date) { StringBuffer stringBuffer = new StringBuffer(logicTableName).append("_").append(date, 0, 6); return stringBuffer.toString(); } @Override public Collection doSharding(Collection collection, RangeShardingValue rangeShardingValue) { Range 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 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;
@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);
}
@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 lists = Lists.newArrayList(log, log1, log2);
logService.insertBatch(lists);
return lists;
}
@RequestMapping("/delete")
public Object delete(Long id) {
return logService.deleteByPrimaryKey(id);
}
@RequestMapping("/select")
public Object select(Long id) {
return logService.selectByPrimaryKey(id);
}
@RequestMapping("/selects")
public Object selects(String time) {
LogExample example = new LogExample();
example.createCriteria().andTimeEqualTo(time);
return logService.selectByExample(example);
}
@RequestMapping("/selectr")
public Object selectr(String times, String timee) {
LogExample example = new LogExample();
example.createCriteria().andTimeBetween(times, timee);
return logService.selectByExample(example);
}
@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
注意,where语句要包含分片字段,才会由分片算法进行分片,如果不包含则会查询全部数据节点。其它的,表可以先建好,或者写个定时器每个月最后一天建下个月的表。



