使用shardingJdbc-5.x+mybatis-plus实现按月分表查询

字数统计: 2.1k字   |   阅读时长≈ 11分

使用场景

适用于单库，日志表过大的问题，如每月产生几千万条日志，还不能清理，必须存档，其它业务表反而没这么大量时，可以使用分表来解决。

可以按实际情况来决定是按年分表还是按月分表，每年产生的数据量过亿时可以按月分表。

本文使用的框架

使用spring boot 2.x + mybatis-plus + shardingjdbc5.x + druid

shardingjdbc非常坑的一个点，每个版本变一次配置，官方文档写得不清不楚，通过GITHUB找到官方示例，发现示例也很少，最后是通过网上的博文+示例+文档才搞定。

配置maven依赖

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
<properties>
  	<sharding-sphere.version>5.0.0</sharding-sphere.version>
  	<mybatis-plus.version>3.4.2</mybatis-plus.version>
  </properties>
  
  <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.3.4.RELEASE</version>
    </parent>
  
  <dependencies>
  <!-- 不能用druid-springboot-starter 会无法启动 -->
   <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.2.8</version>
        </dependency>
  
  <!-- https://mvnrepository.com/artifact/com.alibaba/druid-spring-boot-starter -->
		<!-- <dependency>
		    <groupId>com.alibaba</groupId>
		    <artifactId>druid-spring-boot-starter</artifactId>
		    <version>1.1.22</version>
		</dependency>
   -->
  <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        
        <!-- <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>${mybatis-plus.version}</version>
        </dependency> -->
        
		<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
	<dependency>
		<groupId>mysql</groupId>
		<artifactId>mysql-connector-java</artifactId>
	</dependency>
			
			
		<!-- https://mvnrepository.com/artifact/org.apache.shardingsphere/sharding-jdbc-spring-boot-starter -->
	<dependency>
		<groupId>org.apache.shardingsphere</groupId>
		<artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
		<version>5.0.0</version>
	</dependency>

	<dependency>
		 <groupId>com.baomidou</groupId>
		 <artifactId>mybatis-plus-boot-starter</artifactId>
		 <version>${mybatis-plus.version}</version>
	 </dependency>

		
		<dependency>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-starter-actuator</artifactId>
				<exclusions>
					<exclusion>
						<groupId>org.springframework.boot</groupId>
						<artifactId>spring-boot-starter</artifactId>
					</exclusion>
					<exclusion>
						<groupId>com.fasterxml.jackson.core</groupId>
						<artifactId>jackson-databind</artifactId>
					</exclusion>
				</exclusions>
			</dependency>
	  </dependencies>

配置数据源与shardingjdbc分表策略

按月分表使用单库，所以这里只配置一个数据源

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
spring.shardingsphere.mode.type=Standalone
spring.shardingsphere.mode.repository.type=File
spring.shardingsphere.mode.overwrite=true

# 配置真实数据源
spring.shardingsphere.datasource.names=db0

# 配置第 1 个数据源
spring.shardingsphere.datasource.db0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.db0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.db0.url=jdbc:mysql://localhost:3306/db0
spring.shardingsphere.datasource.db0.username=root
spring.shardingsphere.datasource.db0.password=reiner


# 打印执行的数据库以及语句 默认值: false， 
sharding.jdbc.data-source.props.sql-simple=true
#打开sql输出日志
spring.shardingsphere.props.sql-show=true

#默认的分片键
#spring.shardingsphere.rules.sharding.default-database-strategy.standard.sharding-column=order_id
#使用的分片策略
spring.shardingsphere.rules.sharding.binding-tables[0]=t_order

#配置如何生成要操作的表名，这里根据时间自动添加表后缀，如:t_order_2021_12
spring.shardingsphere.rules.sharding.tables.t_order.actual-data-nodes=db0.t_order_$->{2019..2099}_${(1..12).collect{t ->t.toString().padLeft(2,'0')}}

#分片键,按时间分表
spring.shardingsphere.rules.sharding.tables.t_order.table-strategy.standard.sharding-column=create_time
#分片算法对应的配置名称
spring.shardingsphere.rules.sharding.tables.t_order.table-strategy.standard.sharding-algorithm-name=t-order-inline

#spring.shardingsphere.rules.sharding.tables.t_order.key-generate-strategy.column=order_id #不分库，不需要
#ID生成 雪花算法
#spring.shardingsphere.rules.sharding.tables.t_order.key-generate-strategy.key-generator-name=SNOWFLAKE
#配置分片规则,按时间类型分片
spring.shardingsphere.rules.sharding.sharding-algorithms.t-order-inline.type=INTERVAL
#时间格式
spring.shardingsphere.rules.sharding.sharding-algorithms.t-order-inline.props.datetime-pattern=yyyy-MM-dd HH:mm:ss
#最小的分片时间
spring.shardingsphere.rules.sharding.sharding-algorithms.t-order-inline.props.datetime-lower=2021-01-01 00:00:00
#表的后缀
spring.shardingsphere.rules.sharding.sharding-algorithms.t-order-inline.props.sharding-suffix-pattern=yyyy_MM
#按月分表
spring.shardingsphere.rules.sharding.sharding-algorithms.t-order-inline.props.datetime-interval-unit=MONTHS


spring.shardingsphere.rules.sharding.key-generators.snowflake.type=SNOWFLAKE

spring.shardingsphere.rules.sharding.key-generators.snowflake.props.worker-id=123


mybatis plus的配置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
server:
  port: 7999

spring:
  application:
    name: sharding-test

logging: 
  level: 
    root: info
#  org: 
#    springframework: 
#      web: DEBUG
  file:
    name: ./logs/${spring.application.name}-log.log

mybatis-plus:
  global-config:
    db-config:
      logic-delete-field: IS_DELETE  # 全局逻辑删除的实体字段名(since 3.3.0,配置后可以忽略不配置步骤2)
      logic-delete-value: 1 # 逻辑已删除值(默认为 1)
      logic-not-delete-value: 0 # 逻辑未删除值(默认为 0)
#      capital-mode: true

  typeAliasesPackage: com.reiner.sharding.test.model
# spring boot集成mybatis的方式打印sql 
  configuration:
    cache: true 
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

记得加上mybatis plus扫描注解：@MapperScan(value = "com.reiner.sharding.test.mapper")

java测试代码

控制器代码：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78

import java.beans.PropertyEditorSupport;
import java.util.Date;
import java.util.List;
import java.util.UUID;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.propertyeditors.CustomDateEditor;
import org.springframework.beans.propertyeditors.CustomNumberEditor;
import org.springframework.beans.propertyeditors.StringTrimmerEditor;
import org.springframework.util.StringUtils;
import org.springframework.web.bind.WebDataBinder;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.InitBinder;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.reiner.sharding.test.config.DateFormater;
import com.reiner.sharding.test.mapper.OrderMapper;
import com.reiner.sharding.test.model.Order;


@RestController
@RequestMapping("orders")
public class OrderController {
	
	protected Logger logger =LoggerFactory.getLogger(getClass());

	@Autowired
	OrderMapper orderMapper;
	
	@InitBinder 
	public void initBinder(WebDataBinder binder){
		//解除spring mvc list参数限制长度问题
        binder.setAutoGrowCollectionLimit(1024);
		binder.registerCustomEditor(String.class,new StringTrimmerEditor(true));
		//必须有，将JSON日期字符串格式化成DATE日期
        binder.registerCustomEditor(Date.class,
                new CustomDateEditor(new DateFormater(logger), true));
		binder.registerCustomEditor(long.class, new CustomNumberEditor(Long.class, true));
		binder.registerCustomEditor(double.class,new CustomNumberEditor(Double.class,true));
		binder.registerCustomEditor(float.class, new CustomNumberEditor(Float.class,true));
	} 
	
	/**
	 * @Description 按时间分片的表查询必须带时间
	 * @author reiner
	 * @param startTime
	 * @param endTime
	 * @return
	 * @date 2021年12月15日
	 */
	@GetMapping
	public List<Order> orders(Date startTime,Date endTime) {
		QueryWrapper<Order> wrapper = new QueryWrapper<>();
		wrapper.ge(startTime!=null,"CREATE_TIME", startTime);
		wrapper.le(endTime!=null,"CREATE_TIME", endTime);
		List<Order> list = orderMapper.selectList(wrapper);
		return list;
	}
	
	@GetMapping("add")
	public String add() {
		Order o = new Order();
		//o.setOrderId(UUID.randomUUID().toString());
		o.setTitle("order title");
		o.setUserId(1);
		o.setCreateTime(new Date());
		if(orderMapper.insert(o)>0) {
			return "OK";
		}
		return "FAILED";
	}
}

附上控制器中时间参数转换器：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91

import java.text.DateFormat;
import java.text.FieldPosition;
import java.text.ParsePosition;
import java.text.SimpleDateFormat;
import java.util.Date;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.expression.ParseException;
import org.springframework.util.StringUtils;

/**
 * @date:   2021-12-25
 * @author reiner
 * @Description: 日期转换类，目前用于spring mvc接收json日期的转换
 */
public class DateFormater extends DateFormat {

	Logger logger;
	
	public DateFormater(Logger logger) {
		this.logger=logger;
	}
	
	public DateFormater() {
		this.logger=LoggerFactory.getLogger(getClass());
	}
	
	/**
	 * 
	 */
	private static final long serialVersionUID = -4876634368991167714L;


	private SimpleDateFormat dateTimeFormat = new SimpleDateFormat("yyy-MM-dd HH:mm:ss");
	
	private SimpleDateFormat dateFormat = new SimpleDateFormat("yyy-MM-dd");

	@Override
	public StringBuffer format(Date date, StringBuffer toAppendTo, FieldPosition fieldPosition) {
		return dateTimeFormat.format(date, toAppendTo, fieldPosition);
	}

	@Override
	public Date parse(String source, ParsePosition pos) {
		Date date = null;
		try {
			if(!StringUtils.isEmpty(source)) {
				if(source.indexOf(":")!=-1) {
					date = dateTimeFormat.parse(source,pos);
				}else {
					date = dateFormat.parse(source, pos);
				}
			}
		} catch (Exception e) {
			logger.error("日期转换异常，异常参考信息：{},{}",e.getMessage(),source);
		}

		return date;
	}

	@Override
	public Date parse(String source) throws ParseException, java.text.ParseException {
		Date date = null;
		try {
			if(!StringUtils.isEmpty(source)) {
				if(source.indexOf(":")!=-1) {
					date = dateTimeFormat.parse(source);
				}else {
					date = dateFormat.parse(source);
				}
			}
			
		} catch (Exception e) {
			logger.error("日期转换异常，异常参考信息：{},{}",e.getMessage(),source);
			date = dateFormat.parse(source);
		}
		return date;
	}

	@Override
	public Object clone() {
	     return new DateFormater(logger);
	}

	@Override
	public String toString() {
		// TODO Auto-generated method stub
		return super.toString();
	}

调用http://localhost:7999/orders?startTime=2021-11-01&endTime=2021-11-30 ,shardingjdbc会自动根据参数变换表名，如startTime=2021-11-01，则查询的表名为：t_order_2021-11。

插入同理，会根据createTime字段自动选择要插入的表，同时查多个月份的数据shardingjdbc会自动查询多张表。

测试用的表结构：

1
2
3
4
5
6
7
CREATE TABLE `db0`.`Untitled`  (
  `order_id` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `user_id` int(11) NOT NULL,
  `title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `create_time` datetime NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`order_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

每月创建表

这一步不能忘，shardingjdbc不会自动创建表，因此需要我们自己写一个定时任务自动按月或者年创建表，如按月则获取当前时间并取名：t_order_2021_12

下一篇将记录一下如何使用shardingjdbc分表分库。

扫一扫，分享到微信

tag:

    缺失模块。
    1、请确保node版本大于6.2
    2、在博客根目录（注意不是yilia-plus根目录）执行以下命令：
    npm i hexo-generator-json-content --save

    3、在根目录_config.yml里添加配置： 

      jsonContent:
    meta: false
    pages: false
    posts:
      title: true
      date: true
      path: true
      text: false
      raw: false
      content: false
      slug: false
      updated: false
      comments: false
      link: false
      permalink: false
      excerpt: false
      categories: false
      tags: true

关于我：多年工作经验，熟悉微高并发服务架构设计与开发，有AI客户机器人开发相关经验。
项目或内推机会请联系邮箱： [email protected]
Update By 2023-6-14