Mikel
来源： javaer你还在手写分表分库?来看看这个框架怎么做的 干货满满 – 薛家明 – 博客园
java orm框架easy-query分库分表之分表
高并发三驾马车:分库分表、MQ、缓存。今天给大家带来的就是分库分表的干货解决方案,哪怕你不用我的框架也可以从中听到不一样的结局方案和实现。
一款支持自动分表分库的orm框架easy-query 帮助您解脱跨库带来的复杂业务代码,并且提供多种结局方案和自定义路由来实现比中间件更高性能的数据库访问。
目前市面上有的分库分表JAVA组件有很多：中间件代理有:sharding-sphere(proxy),mycat 客户端JDBC:sharding-sphere（jdbc）等等，中间件因为代理了一层会导致所有的SQL执行都要经过中间件，性能会大大折扣,但是因为中间部署可以提供更加省的连接池，客户端无需代理，仅需对SQL进行分析即可实现，但是越靠近客户的模式可以优化的性能越高，所以本次带来的框架可以提供前所未有的分片规则自由和前所未有的便捷高性能。
本文 demo地址 https://github.com/xuejmnet/easy-sharding-test
怎么样的orm算是支持分表分库
首先orm是否支持分表分库不仅仅是看框架是否支持动态修改表名，让数据正确存入对应的表或者修改对应的数据，这些说实话都是最最简单的实现，真正需要支持分库分表那么需要orm实现复杂的跨表聚合查询，这才是分表分库的精髓，很显然目前的orm很少有支持的。接下来我将给大家演示基于springboot3.x的分表分库演示,取模分片和时间分片。本章我们主要以使用为主后面下一章我们来讲解优化方案,包括原理解析,后续有更多的关于分表分库的经验是博主多年下来的实战经验分享给大家保证大家的happy coding。
初始化项目
进入 https://start.spring.io/ 官网直接下载
安装依赖
<!-- https://mvnrepository.com/artifact/com.alibaba/druid -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.2.15</version>
</dependency>
<!-- mysql驱动 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.17</version>
</dependency>
<dependency>
<groupId>com.easy-query</groupId>
<artifactId>sql-springboot-starter</artifactId>
<version>0.9.7</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.18</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
application.yml配置
server:
port: 8080
spring:
datasource:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://127.0.0.1:3306/easy-sharding-test?serverTimezone=GMT%2B8&characterEncoding=utf-8&useSSL=false&allowMultiQueries=true&rewriteBatchedStatements=true
username: root
password: root
logging:
level:
com.easy.query.core: debug
easy-query:
enable: true
name-conversion: underlined
database: mysql
取模
常见的分片方式之一就是取模分片,取模分片可以让以分片键为条件的处理完美路由到对应的表,性能上来说非常非常高，但是局限性也是很大的因为无意义的id路由会导致仅支持这一个id条件而不支持其他条件的路由，只能全分片表扫描来获取对应的数据，但是他的实现和理解也是最容易的，当然后续还有基因分片一种可以部分解决仅支持id带来的问题不过也并不是非常的完美。
简单的取模分片
我们本次测试案例采用order表对其进行5表拆分:order_00,order_01,order_02,order_03,order_04,采用订单id取模进行分表
数据库脚本
CREATE DATABASE IF NOT EXISTS `easy-sharding-test` CHARACTER SET 'utf8mb4';
USE `easy-sharding-test`;
create table order_00
(
id varchar(32) not null comment '主键ID'primary key,
uid varchar(50) not null comment '用户id',
order_no int null comment '订单号'
)comment '订单表';
create table order_01
(
id varchar(32) not null comment '主键ID'primary key,
uid varchar(50) not null comment '用户id',
order_no int null comment '订单号'
)comment '订单表';
create table order_02
(
id varchar(32) not null comment '主键ID'primary key,
uid varchar(50) not null comment '用户id',
order_no int null comment '订单号'
)comment '订单表';
create table order_03
(
id varchar(32) not null comment '主键ID'primary key,
uid varchar(50) not null comment '用户id',
order_no int null comment '订单号'
)comment '订单表';
create table order_04
(
id varchar(32) not null comment '主键ID'primary key,
uid varchar(50) not null comment '用户id',
order_no int null comment '订单号'
)comment '订单表';
//定义了一个对象并且设置表名和分片初始化器`shardingInitializer`,设置id为主键，并且设置id为分表建
@Data
@Table(value = "order",shardingInitializer = OrderShardingInitializer.class)
public class OrderEntity {
@Column(primaryKey = true)
@ShardingTableKey
private String id;
private String uid;
private Integer orderNo;
}
//编写订单取模初始化器,只需要实现两个方法，当然你也可以自己实现对应的`EntityShardingInitializer`这边是继承`easy-query`框架提供的分片取模初始化器
@Component
public class OrderShardingInitializer extends AbstractShardingModInitializer<OrderEntity> {
/**
* 设置模几我们模5就设置5
* @return
*/
@Override
protected int mod() {
return 5;
}
/**
* 编写模5后的尾巴长度默认我们设置2就是左补0
* @return
*/
@Override
protected int tailLength() {
return 2;
}
}
//编写分片规则`AbstractModTableRule`由框架提供取模分片路由规则,如果需要自己实现可以继承`AbstractTableRouteRule`这个抽象类
@Component
public class OrderTableRouteRule extends AbstractModTableRule<OrderEntity> {
@Override
protected int mod() {
return 5;
}
@Override
protected int tailLength() {
return 2;
}
}
初始化工作做好了开始编写代码
新增初始化
@RestController
@RequestMapping("/order")
@RequiredArgsConstructor(onConstructor_ = @Autowired)
public class OrderController {
private final EasyQuery easyQuery;
@GetMapping("/init")
public Object init() {
ArrayList<OrderEntity> orderEntities = new ArrayList<>(100);
List<String> users = Arrays.asList("xiaoming", "xiaohong", "xiaolan");
for (int i = 0; i < 100; i++) {
OrderEntity orderEntity = new OrderEntity();
orderEntity.setId(String.valueOf(i));
int i1 = i % 3;
String uid = users.get(i1);
orderEntity.setUid(uid);
orderEntity.setOrderNo(i);
orderEntities.add(orderEntity);
}
long l = easyQuery.insertable(orderEntities).executeRows();
return "成功插入:"+l;
}
}
查询单条
按分片键查询
可以完美的路由到对应的数据库表和操作单表拥有一样的性能
@GetMapping("/first")
public Object first(@RequestParam("id") String id) {
OrderEntity orderEntity = easyQuery.queryable(OrderEntity.class)
.whereById(id).firstOrNull();
return orderEntity;
}
http://localhost:8080/order/first?id=20
{"id":"20","uid":"xiaolan","orderNo":20}
http-nio-8080-exec-1, name:ds0, Preparing: SELECT t.`id`,t.`uid`,t.`order_no` FROM `order_03` t WHERE t.`id` = ? LIMIT 1
==> http-nio-8080-exec-1, name:ds0, Parameters: 20(String)
<== Total: 1
日志稍微解释一下
- http-nio-8080-exec-1表示当前语句执行的线程,默认多个分片聚合后需要再线程池中查询数据后聚合返回。
- name:ds0 表示数据源叫做ds0，如果不分库那么这个数据源可以忽略,也可以自己指定配置文件中或者设置
defaultDataSourceName
全程无需您去计算路由到哪里,并且规则和业务代码已经脱离解耦
不按分片键查询
当我们的查询为非分片键查询那么会导致路由需要进行全分片扫描然后来获取对应的数据进行判断哪个时我们要的
@GetMapping("/firstByUid")
public Object firstByUid(@RequestParam("uid") String uid) {
OrderEntity orderEntity = easyQuery.queryable(OrderEntity.class)
.where(o->o.eq(OrderEntity::getUid,uid)).firstOrNull();
return orderEntity;
}
http://localhost:8080/order/firstByUid?uid=xiaoming
{"id":"18","uid":"xiaoming","orderNo":18}
//这边把日志精简了一下可以看到他是开启了5个线程进行分片查询
==> SHARDING_EXECUTOR_1, name:ds0, Preparing: SELECT t.`id`,t.`uid`,t.`order_no` FROM `order_00` t WHERE t.`uid` = ? LIMIT 1
==> SHARDING_EXECUTOR_5, name:ds0, Preparing: SELECT t.`id`,t.`uid`,t.`order_no` FROM `order_03` t WHERE t.`uid` = ? LIMIT 1
==> SHARDING_EXECUTOR_4, name:ds0, Preparing: SELECT t.`id`,t.`uid`,t.`order_no` FROM `order_04` t WHERE t.`uid` = ? LIMIT 1
==> SHARDING_EXECUTOR_3, name:ds0, Preparing: SELECT t.`id`,t.`uid`,t.`order_no` FROM `order_02` t WHERE t.`uid` = ? LIMIT 1
==> SHARDING_EXECUTOR_2, name:ds0, Preparing: SELECT t.`id`,t.`uid`,t.`order_no` FROM `order_01` t WHERE t.`uid` = ? LIMIT 1
==> SHARDING_EXECUTOR_3, name:ds0, Parameters: xiaoming(String)
==> SHARDING_EXECUTOR_4, name:ds0, Parameters: xiaoming(String)
==> SHARDING_EXECUTOR_5, name:ds0, Parameters: xiaoming(String)
==> SHARDING_EXECUTOR_1, name:ds0, Parameters: xiaoming(String)
==> SHARDING_EXECUTOR_2, name:ds0, Parameters: xiaoming(String)
<== Total: 1
因为uid不是分片键所以在分片查询的时候需要遍历所有的表然后返回对应的数据，可能有同学会问就这?当然这只是简单演示后续下一篇我会给出具体的优化方案来进行处理。
分页查询
分片后的分页查询是分片下的一个难点,这边框架自带功能，分片后分页之所以难是因为如果是自行实现业务代码会变得非常复杂，有一种非常简易的方式就是把分页重写pageIndex永远为1，然后全部取到内存后在进行stream过滤，但是带来的另一个问题就是pageIndex不能便宜过大不然内存会完全存不下导致内存爆炸，并且如果翻页到最后几页那将是灾难性的，给程序带来极其不稳定,但是easy-query提供了和sharding-sphere一样的分片聚合方式并且因为靠近业务的关系所以可以有效的优化深度分页pageIndex过大
@GetMapping("/page")
public Object page(@RequestParam("pageIndex") Integer pageIndex,@RequestParam("pageSize") Integer pageSize) {
EasyPageResult<OrderEntity> pageResult = easyQuery.queryable(OrderEntity.class)
.orderByAsc(o -> o.column(OrderEntity::getOrderNo))
.toPageResult(pageIndex, pageSize);
return pageResult;
}
http://localhost:8080/order/page?pageIndex=1&pageSize=10
{"total":100,"data":[{"id":"0","uid":"xiaoming","orderNo":0},{"id":"1","uid":"xiaohong","orderNo":1},{"id":"2","uid":"xiaolan","orderNo":2},{"id":"3","uid":"xiaoming","orderNo":3},{"id":"4","uid":"xiaohong","orderNo":4},{"id":"5","uid":"xiaolan","orderNo":5},{"id":"6","uid":"xiaoming","orderNo":6},{"id":"7","uid":"xiaohong","orderNo":7},{"id":"8","uid":"xiaolan","orderNo":8},{"id":"9","uid":"xiaoming","orderNo":9}]}
==> SHARDING_EXECUTOR_3, name:ds0, Preparing: SELECT COUNT(1) FROM `order_02` t
==> SHARDING_EXECUTOR_2, name:ds0, Preparing: SELECT COUNT(1) FROM `order_03` t
==> SHARDING_EXECUTOR_5, name:ds0, Preparing: SELECT COUNT(1) FROM `order_04` t
==> SHARDING_EXECUTOR_1, name:ds0, Preparing: SELECT COUNT(1) FROM `order_01` t
==> SHARDING_EXECUTOR_4, name:ds0, Preparing: SELECT COUNT(1) FROM `order_00` t
<== Total: 1
==> SHARDING_EXECUTOR_5, name:ds0, Preparing: SELECT t.`id`,t.`uid`,t.`order_no` FROM `order_04` t ORDER BY t.`order_no` ASC LIMIT 10
==> SHARDING_EXECUTOR_2, name:ds0, Preparing: SELECT t.`id`,t.`uid`,t.`order_no` FROM `order_03` t ORDER BY t.`order_no` ASC LIMIT 10
==> SHARDING_EXECUTOR_4, name:ds0, Preparing: SELECT t.`id`,t.`uid`,t.`order_no` FROM `order_00` t ORDER BY t.`order_no` ASC LIMIT 10
==> SHARDING_EXECUTOR_1, name:ds0, Preparing: SELECT t.`id`,t.`uid`,t.`order_no` FROM `order_01` t ORDER BY t.`order_no` ASC LIMIT 10
==> SHARDING_EXECUTOR_3, name:ds0, Preparing: SELECT t.`id`,t.`uid`,t.`order_no` FROM `order_02` t ORDER BY t.`order_no` ASC LIMIT 10
<== Total: 10
这边可以看到一行代码实现分页，下面是第二页
http://localhost:8080/order/page?pageIndex=2&pageSize=10
{"total":100,"data":[{"id":"10","uid":"xiaohong","orderNo":10},{"id":"11","uid":"xiaolan","orderNo":11},{"id":"12","uid":"xiaoming","orderNo":12},{"id":"13","uid":"xiaohong","orderNo":13},{"id":"14","uid":"xiaolan","orderNo":14},{"id":"15","uid":"xiaoming","orderNo":15},{"id":"16","uid":"xiaohong","orderNo":16},{"id":"17","uid":"xiaolan","orderNo":17},{"id":"18","uid":"xiaoming","orderNo":18},{"id":"19","uid":"xiaohong","orderNo":19}]}
==> SHARDING_EXECUTOR_9, name:ds0, Preparing: SELECT COUNT(1) FROM `order_02` t
==> SHARDING_EXECUTOR_8, name:ds0, Preparing: SELECT COUNT(1) FROM `order_01` t
==> SHARDING_EXECUTOR_10, name:ds0, Preparing: SELECT COUNT(1) FROM `order_04` t
==> SHARDING_EXECUTOR_7, name:ds0, Preparing: SELECT COUNT(1) FROM `order_03` t
==> SHARDING_EXECUTOR_6, name:ds0, Preparing: SELECT COUNT(1) FROM `order_00` t
<== Total: 1
==> SHARDING_EXECUTOR_9, name:ds0, Preparing: SELECT t.`id`,t.`uid`,t.`order_no` FROM `order_01` t ORDER BY t.`order_no` ASC LIMIT 20
==> SHARDING_EXECUTOR_8, name:ds0, Preparing: SELECT t.`id`,t.`uid`,t.`order_no` FROM `order_03` t ORDER BY t.`order_no` ASC LIMIT 20
==> SHARDING_EXECUTOR_10, name:ds0, Preparing: SELECT t.`id`,t.`uid`,t.`order_no` FROM `order_04` t ORDER BY t.`order_no` ASC LIMIT 20
==> SHARDING_EXECUTOR_6, name:ds0, Preparing: SELECT t.`id`,t.`uid`,t.`order_no` FROM `order_02` t ORDER BY t.`order_no` ASC LIMIT 20
==> SHARDING_EXECUTOR_7, name:ds0, Preparing: SELECT t.`id`,t.`uid`,t.`order_no` FROM `order_00` t ORDER BY t.`order_no` ASC LIMIT 20
<== Total: 10
按时间分表
这边我们简单还是以order订单为例,按月进行分片假设我们从2022年1月到2023年5月一共17个月表名为t_order_202201、t_order_202202、t_order_202203…t_order_202304、t_order_202305
数据库脚本
create table t_order_202201
(
id varchar(32) not null comment '主键ID'primary key,
uid varchar(50) not null comment '用户id',
order_no int not null comment '订单号',
create_time datetime not null comment '创建时间'
)comment '订单表';
create table t_order_202202
(
id varchar(32) not null comment '主键ID'primary key,
uid varchar(50) not null comment '用户id',
order_no int not null comment '订单号',
create_time datetime not null comment '创建时间'
)comment '订单表';
....
create table t_order_202304
(
id varchar(32) not null comment '主键ID'primary key,
uid varchar(50) not null comment '用户id',
order_no int not null comment '订单号',
create_time datetime not null comment '创建时间'
)comment '订单表';
create table t_order_202305
(
id varchar(32) not null comment '主键ID'primary key,
uid varchar(50) not null comment '用户id',
order_no int not null comment '订单号',
create_time datetime not null comment '创建时间'
)comment '订单表';
@Data
@Table(value = "t_order",shardingInitializer = OrderByMonthShardingInitializer.class)
public class OrderByMonthEntity {
@Column(primaryKey = true)
private String id;
private String uid;
private Integer orderNo;
/**
* 分片键改为时间
*/
@ShardingTableKey
private LocalDateTime createTime;
}
//路由规则可以直接继承AbstractShardingMonthInitializer也可以自己实现
@Component
public class OrderByMonthShardingInitializer extends AbstractShardingMonthInitializer<OrderByMonthEntity> {
/**
* 开始时间不可以使用LocalDateTime.now()因为会导致每次启动开始时间都不一样
* @return
*/
@Override
protected LocalDateTime getBeginTime() {
return LocalDateTime.of(2022,1,1,0,0);
}
/**
* 如果不设置那么就是当前时间,用于程序启动后自动计算应该有的表包括最后时间
* @return
*/
@Override
protected LocalDateTime getEndTime() {
return LocalDateTime.of(2023,5,31,0,0);
}
@Override
public void configure0(ShardingEntityBuilder<OrderByMonthEntity> builder) {
//后续用来实现优化分表
}
}
//按月分片路由规则也可以自己实现因为框架已经封装好了所以可以用框架自带的
@Component
public class OrderByMonthTableRouteRule extends AbstractMonthTableRule<OrderByMonthEntity> {
@Override
protected LocalDateTime convertLocalDateTime(Object shardingValue) {
return (LocalDateTime)shardingValue;
}
}
初始化
@RestController
@RequestMapping("/orderMonth")
@RequiredArgsConstructor(onConstructor_ = @Autowired)
public class OrderMonthController {
private final EasyQuery easyQuery;
@GetMapping("/init")
public Object init() {
ArrayList<OrderByMonthEntity> orderEntities = new ArrayList<>(100);
List<String> users = Arrays.asList("xiaoming", "xiaohong", "xiaolan");
LocalDateTime beginTime=LocalDateTime.of(2022,1,1,0,0);
LocalDateTime endTime=LocalDateTime.of(2023,5,31,0,0);
int i=0;
while(!beginTime.isAfter(endTime)){
OrderByMonthEntity orderEntity = new OrderByMonthEntity();
orderEntity.setId(String.valueOf(i));
int i1 = i % 3;
String uid = users.get(i1);
orderEntity.setUid(uid);
orderEntity.setOrderNo(i);
orderEntity.setCreateTime(beginTime);
orderEntities.add(orderEntity);
beginTime=beginTime.plusDays(1);
i++;
}
long l = easyQuery.insertable(orderEntities).executeRows();
return "成功插入:"+l;
}
}
http://localhost:8080/orderMonth/init
成功插入:516
获取第一条数据
@GetMapping("/first")
public Object first(@RequestParam("id") String id) {
OrderEntity orderEntity = easyQuery.queryable(OrderEntity.class)
.whereById(id).firstOrNull();
return orderEntity;
}
http://localhost:8080/orderMonth/first?id=11
{"id":"11","uid":"xiaolan","orderNo":11,"createTime":"2022-01-12T00:00:00"}
//以每5组一个次并发执行聚合
==> SHARDING_EXECUTOR_1, name:ds0, Preparing: SELECT t.`id`,t.`uid`,t.`order_no`,t.`create_time` FROM `t_order_202205` t WHERE t.`id` = ? LIMIT 1
==> SHARDING_EXECUTOR_1, name:ds0, Parameters: 11(String)
==> SHARDING_EXECUTOR_2, name:ds0, Preparing: SELECT t.`id`,t.`uid`,t.`order_no`,t.`create_time` FROM `t_order_202207` t WHERE t.`id` = ? LIMIT 1
==> SHARDING_EXECUTOR_2, name:ds0, Parameters: 11(String)
==> SHARDING_EXECUTOR_3, name:ds0, Preparing: SELECT t.`id`,t.`uid`,t.`order_no`,t.`create_time` FROM `t_order_202303` t WHERE t.`id` = ? LIMIT 1
==> SHARDING_EXECUTOR_3, name:ds0, Parameters: 11(String)
==> SHARDING_EXECUTOR_4, name:ds0, Preparing: SELECT t.`id`,t.`uid`,t.`order_no`,t.`create_time` FROM `t_order_202212` t WHERE t.`id` = ? LIMIT 1
==> SHARDING_EXECUTOR_4, name:ds0, Parameters: 11(String)
==> SHARDING_EXECUTOR_5, name:ds0, Preparing: SELECT t.`id`,t.`uid`,t.`order_no`,t.`create_time` FROM `t_order_202302` t WHERE t.`id` = ? LIMIT 1
==> SHARDING_EXECUTOR_5, name:ds0, Parameters: 11(String)
==> SHARDING_EXECUTOR_1, name:ds0, Preparing: SELECT t.`id`,t.`uid`,t.`order_no`,t.`create_time` FROM `t_order_202304` t WHERE t.`id` = ? LIMIT 1
==> SHARDING_EXECUTOR_5, name:ds0, Preparing: SELECT t.`id`,t.`uid`,t.`order_no`,t.`create_time` FROM `t_order_202206` t WHERE t.`id` = ? LIMIT 1
==> SHARDING_EXECUTOR_2, name:ds0, Preparing: SELECT t.`id`,t.`uid`,t.`order_no`,t.`create_time` FROM `t_order_202305` t WHERE t.`id` = ? LIMIT 1
==> SHARDING_EXECUTOR_1, name:ds0, Parameters: 11(String)
==> SHARDING_EXECUTOR_2, name:ds0, Parameters: 11(String)
==> SHARDING_EXECUTOR_4, name:ds0, Preparing: SELECT t.`id`,t.`uid`,t.`order_no`,t.`create_time` FROM `t_order_202209` t WHERE t.`id` = ? LIMIT 1
==> SHARDING_EXECUTOR_3, name:ds0, Preparing: SELECT t.`id`,t.`uid`,t.`order_no`,t.`create_time` FROM `t_order_202204` t WHERE t.`id` = ? LIMIT 1
==> SHARDING_EXECUTOR_5, name:ds0, Parameters: 11(String)
==> SHARDING_EXECUTOR_3, name:ds0, Parameters: 11(String)
==> SHARDING_EXECUTOR_4, name:ds0, Parameters: 11(String)
==> SHARDING_EXECUTOR_2, name:ds0, Preparing: SELECT t.`id`,t.`uid`,t.`order_no`,t.`create_time` FROM `t_order_202208` t WHERE t.`id` = ? LIMIT 1
==> SHARDING_EXECUTOR_5, name:ds0, Preparing: SELECT t.`id`,t.`uid`,t.`order_no`,t.`create_time` FROM `t_order_202201` t WHERE t.`id` = ? LIMIT 1
==> SHARDING_EXECUTOR_3, name:ds0, Preparing: SELECT t.`id`,t.`uid`,t.`order_no`,t.`create_time` FROM `t_order_202210` t WHERE t.`id` = ? LIMIT 1
==> SHARDING_EXECUTOR_5, name:ds0, Parameters: 11(String)
==> SHARDING_EXECUTOR_4, name:ds0, Preparing: SELECT t.`id`,t.`uid`,t.`order_no`,t.`create_time` FROM `t_order_202202` t WHERE t.`id` = ? LIMIT 1
==> SHARDING_EXECUTOR_3, name:ds0, Parameters: 11(String)
==> SHARDING_EXECUTOR_2, name:ds0, Parameters: 11(String)
==> SHARDING_EXECUTOR_4, name:ds0, Parameters: 11(String)
==> SHARDING_EXECUTOR_1, name:ds0, Preparing: SELECT t.`id`,t.`uid`,t.`order_no`,t.`create_time` FROM `t_order_202211` t WHERE t.`id` = ? LIMIT 1
==> SHARDING_EXECUTOR_1, name:ds0, Parameters: 11(String)
==> SHARDING_EXECUTOR_2, name:ds0, Preparing: SELECT t.`id`,t.`uid`,t.`order_no`,t.`create_time` FROM `t_order_202203` t WHERE t.`id` = ? LIMIT 1
==> SHARDING_EXECUTOR_5, name:ds0, Preparing: SELECT t.`id`,t.`uid`,t.`order_no`,t.`create_time` FROM `t_order_202301` t WHERE t.`id` = ? LIMIT 1
==> SHARDING_EXECUTOR_2, name:ds0, Parameters: 11(String)
==> SHARDING_EXECUTOR_5, name:ds0, Parameters: 11(String)
<== Total: 1
获取范围内的数据
@GetMapping("/range")
public Object first() {
List<OrderByMonthEntity> list = easyQuery.queryable(OrderByMonthEntity.class)
.where(o -> o.rangeClosed(OrderByMonthEntity::getCreateTime, LocalDateTime.of(2022, 3, 1, 0, 0), LocalDateTime.of(2022, 9, 1, 0, 0)))
.toList();
return list;
}
http://localhost:8080/orderMonth/range
[{"id":"181","uid":"xiaohong","orderNo":181,"createTime":"2022-07-01T00:00:00"},{"id":"182","uid":"xiaolan","orderNo":182,"createTime":"2022-07-02T00:00:00"},{"id":"183","uid":"xiaoming","orderNo":183,"createTime":"2022-07-03T00:00:00"},...........,{"id":"239","uid":"xiaolan","orderNo":239,"createTime":"2022-08-28T00:00:00"},{"id":"240","uid":"xiaoming","orderNo":240,"createTime":"2022-08-29T00:00:00"},{"id":"241","uid":"xiaohong","orderNo":241,"createTime":"2022-08-30T00:00:00"},{"id":"242","uid":"xiaolan","orderNo":242,"createTime":"2022-08-31T00:00:00"}]
//可以精准定位到对应的分片路由上获取数据
==> SHARDING_EXECUTOR_1, name:ds0, Preparing: SELECT t.`id`,t.`uid`,t.`order_no`,t.`create_time` FROM `t_order_202207` t WHERE t.`create_time` >= ? AND t.`create_time` <= ?
==> SHARDING_EXECUTOR_5, name:ds0, Preparing: SELECT t.`id`,t.`uid`,t.`order_no`,t.`create_time` FROM `t_order_202209` t WHERE t.`create_time` >= ? AND t.`create_time` <= ?
==> SHARDING_EXECUTOR_2, name:ds0, Preparing: SELECT t.`id`,t.`uid`,t.`order_no`,t.`create_time` FROM `t_order_202206` t WHERE t.`create_time` >= ? AND t.`create_time` <= ?
==> SHARDING_EXECUTOR_4, name:ds0, Preparing: SELECT t.`id`,t.`uid`,t.`order_no`,t.`create_time` FROM `t_order_202203` t WHERE t.`create_time` >= ? AND t.`create_time` <= ?
==> SHARDING_EXECUTOR_3, name:ds0, Preparing: SELECT t.`id`,t.`uid`,t.`order_no`,t.`create_time` FROM `t_order_202205` t WHERE t.`create_time` >= ? AND t.`create_time` <= ?
==> SHARDING_EXECUTOR_4, name:ds0, Parameters: 2022-03-01T00:00(LocalDateTime),2022-09-01T00:00(LocalDateTime)
==> SHARDING_EXECUTOR_3, name:ds0, Parameters: 2022-03-01T00:00(LocalDateTime),2022-09-01T00:00(LocalDateTime)
==> SHARDING_EXECUTOR_2, name:ds0, Parameters: 2022-03-01T00:00(LocalDateTime),2022-09-01T00:00(LocalDateTime)
==> SHARDING_EXECUTOR_5, name:ds0, Parameters: 2022-03-01T00:00(LocalDateTime),2022-09-01T00:00(LocalDateTime)
==> SHARDING_EXECUTOR_1, name:ds0, Parameters: 2022-03-01T00:00(LocalDateTime),2022-09-01T00:00(LocalDateTime)
==> SHARDING_EXECUTOR_4, name:ds0, Preparing: SELECT t.`id`,t.`uid`,t.`order_no`,t.`create_time` FROM `t_order_202208` t WHERE t.`create_time` >= ? AND t.`create_time` <= ?
==> SHARDING_EXECUTOR_2, name:ds0, Preparing: SELECT t.`id`,t.`uid`,t.`order_no`,t.`create_time` FROM `t_order_202204` t WHERE t.`create_time` >= ? AND t.`create_time` <= ?
==> SHARDING_EXECUTOR_4, name:ds0, Parameters: 2022-03-01T00:00(LocalDateTime),2022-09-01T00:00(LocalDateTime)
==> SHARDING_EXECUTOR_2, name:ds0, Parameters: 2022-03-01T00:00(LocalDateTime),2022-09-01T00:00(LocalDateTime)
<== Total: 185
最后
目前为止你已经看到了easy-query对于分片的便捷性，但是本章只是开胃小菜,相信了解分库分表的小伙伴肯定会说就这？不是和sharding-jdbc一样吗为什么要用你的呢。我想说第一篇只是给大家了解一下如何使用,后续的文章才是分表分库的精髓相信我你一定没看过
