经验首页 前端设计 程序设计 Java相关 移动开发 数据库/运维 软件/图像 大数据/云计算 其他经验
当前位置:技术经验 » Java相关 » Java » 查看文章
javaer你还在手写分表分库?来看看这个框架怎么做的 干货满满
来源:cnblogs  作者:薛家明  时间:2023/5/26 9:54:05  对本文有异议

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/ 官网直接下载

安装依赖

  1. <!-- https://mvnrepository.com/artifact/com.alibaba/druid -->
  2. <dependency>
  3. <groupId>com.alibaba</groupId>
  4. <artifactId>druid</artifactId>
  5. <version>1.2.15</version>
  6. </dependency>
  7. <!-- mysql驱动 -->
  8. <dependency>
  9. <groupId>mysql</groupId>
  10. <artifactId>mysql-connector-java</artifactId>
  11. <version>8.0.17</version>
  12. </dependency>
  13. <dependency>
  14. <groupId>com.easy-query</groupId>
  15. <artifactId>sql-springboot-starter</artifactId>
  16. <version>0.9.7</version>
  17. </dependency>
  18. <dependency>
  19. <groupId>org.projectlombok</groupId>
  20. <artifactId>lombok</artifactId>
  21. <version>1.18.18</version>
  22. </dependency>
  23. <dependency>
  24. <groupId>org.springframework.boot</groupId>
  25. <artifactId>spring-boot-starter-web</artifactId>
  26. </dependency>

application.yml配置

  1. server:
  2. port: 8080
  3. spring:
  4. datasource:
  5. type: com.alibaba.druid.pool.DruidDataSource
  6. driver-class-name: com.mysql.cj.jdbc.Driver
  7. url: jdbc:mysql://127.0.0.1:3306/easy-sharding-test?serverTimezone=GMT%2B8&characterEncoding=utf-8&useSSL=false&allowMultiQueries=true&rewriteBatchedStatements=true
  8. username: root
  9. password: root
  10. logging:
  11. level:
  12. com.easy.query.core: debug
  13. easy-query:
  14. enable: true
  15. name-conversion: underlined
  16. database: mysql

取模

常见的分片方式之一就是取模分片,取模分片可以让以分片键为条件的处理完美路由到对应的表,性能上来说非常非常高,但是局限性也是很大的因为无意义的id路由会导致仅支持这一个id条件而不支持其他条件的路由,只能全分片表扫描来获取对应的数据,但是他的实现和理解也是最容易的,当然后续还有基因分片一种可以部分解决仅支持id带来的问题不过也并不是非常的完美。

简单的取模分片

我们本次测试案例采用order表对其进行5表拆分:order_00,order_01,order_02,order_03,order_04,采用订单id取模进行分表
数据库脚本

  1. CREATE DATABASE IF NOT EXISTS `easy-sharding-test` CHARACTER SET 'utf8mb4';
  2. USE `easy-sharding-test`;
  3. create table order_00
  4. (
  5. id varchar(32) not null comment '主键ID'primary key,
  6. uid varchar(50) not null comment '用户id',
  7. order_no int null comment '订单号'
  8. )comment '订单表';
  9. create table order_01
  10. (
  11. id varchar(32) not null comment '主键ID'primary key,
  12. uid varchar(50) not null comment '用户id',
  13. order_no int null comment '订单号'
  14. )comment '订单表';
  15. create table order_02
  16. (
  17. id varchar(32) not null comment '主键ID'primary key,
  18. uid varchar(50) not null comment '用户id',
  19. order_no int null comment '订单号'
  20. )comment '订单表';
  21. create table order_03
  22. (
  23. id varchar(32) not null comment '主键ID'primary key,
  24. uid varchar(50) not null comment '用户id',
  25. order_no int null comment '订单号'
  26. )comment '订单表';
  27. create table order_04
  28. (
  29. id varchar(32) not null comment '主键ID'primary key,
  30. uid varchar(50) not null comment '用户id',
  31. order_no int null comment '订单号'
  32. )comment '订单表';
  1. //定义了一个对象并且设置表名和分片初始化器`shardingInitializer`,设置id为主键,并且设置id为分表建
  2. @Data
  3. @Table(value = "order",shardingInitializer = OrderShardingInitializer.class)
  4. public class OrderEntity {
  5. @Column(primaryKey = true)
  6. @ShardingTableKey
  7. private String id;
  8. private String uid;
  9. private Integer orderNo;
  10. }
  11. //编写订单取模初始化器,只需要实现两个方法,当然你也可以自己实现对应的`EntityShardingInitializer`这边是继承`easy-query`框架提供的分片取模初始化器
  12. @Component
  13. public class OrderShardingInitializer extends AbstractShardingModInitializer<OrderEntity> {
  14. /**
  15. * 设置模几我们模5就设置5
  16. * @return
  17. */
  18. @Override
  19. protected int mod() {
  20. return 5;
  21. }
  22. /**
  23. * 编写模5后的尾巴长度默认我们设置2就是左补0
  24. * @return
  25. */
  26. @Override
  27. protected int tailLength() {
  28. return 2;
  29. }
  30. }
  31. //编写分片规则`AbstractModTableRule`由框架提供取模分片路由规则,如果需要自己实现可以继承`AbstractTableRouteRule`这个抽象类
  32. @Component
  33. public class OrderTableRouteRule extends AbstractModTableRule<OrderEntity> {
  34. @Override
  35. protected int mod() {
  36. return 5;
  37. }
  38. @Override
  39. protected int tailLength() {
  40. return 2;
  41. }
  42. }

初始化工作做好了开始编写代码

新增初始化

  1. @RestController
  2. @RequestMapping("/order")
  3. @RequiredArgsConstructor(onConstructor_ = @Autowired)
  4. public class OrderController {
  5. private final EasyQuery easyQuery;
  6. @GetMapping("/init")
  7. public Object init() {
  8. ArrayList<OrderEntity> orderEntities = new ArrayList<>(100);
  9. List<String> users = Arrays.asList("xiaoming", "xiaohong", "xiaolan");
  10. for (int i = 0; i < 100; i++) {
  11. OrderEntity orderEntity = new OrderEntity();
  12. orderEntity.setId(String.valueOf(i));
  13. int i1 = i % 3;
  14. String uid = users.get(i1);
  15. orderEntity.setUid(uid);
  16. orderEntity.setOrderNo(i);
  17. orderEntities.add(orderEntity);
  18. }
  19. long l = easyQuery.insertable(orderEntities).executeRows();
  20. return "成功插入:"+l;
  21. }
  22. }

查询单条

按分片键查询

可以完美的路由到对应的数据库表和操作单表拥有一样的性能

  1. @GetMapping("/first")
  2. public Object first(@RequestParam("id") String id) {
  3. OrderEntity orderEntity = easyQuery.queryable(OrderEntity.class)
  4. .whereById(id).firstOrNull();
  5. return orderEntity;
  6. }
  7. http://localhost:8080/order/first?id=20
  8. {"id":"20","uid":"xiaolan","orderNo":20}
  9. 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
  10. ==> http-nio-8080-exec-1, name:ds0, Parameters: 20(String)
  11. <== Total: 1

日志稍微解释一下

  • http-nio-8080-exec-1表示当前语句执行的线程,默认多个分片聚合后需要再线程池中查询数据后聚合返回。
  • name:ds0 表示数据源叫做ds0,如果不分库那么这个数据源可以忽略,也可以自己指定配置文件中或者设置defaultDataSourceName

全程无需您去计算路由到哪里,并且规则和业务代码已经脱离解耦

不按分片键查询

当我们的查询为非分片键查询那么会导致路由需要进行全分片扫描然后来获取对应的数据进行判断哪个时我们要的

  1. @GetMapping("/firstByUid")
  2. public Object firstByUid(@RequestParam("uid") String uid) {
  3. OrderEntity orderEntity = easyQuery.queryable(OrderEntity.class)
  4. .where(o->o.eq(OrderEntity::getUid,uid)).firstOrNull();
  5. return orderEntity;
  6. }
  7. http://localhost:8080/order/firstByUid?uid=xiaoming
  8. {"id":"18","uid":"xiaoming","orderNo":18}
  9. //这边把日志精简了一下可以看到他是开启了5个线程进行分片查询
  10. ==> SHARDING_EXECUTOR_1, name:ds0, Preparing: SELECT t.`id`,t.`uid`,t.`order_no` FROM `order_00` t WHERE t.`uid` = ? LIMIT 1
  11. ==> SHARDING_EXECUTOR_5, name:ds0, Preparing: SELECT t.`id`,t.`uid`,t.`order_no` FROM `order_03` t WHERE t.`uid` = ? LIMIT 1
  12. ==> SHARDING_EXECUTOR_4, name:ds0, Preparing: SELECT t.`id`,t.`uid`,t.`order_no` FROM `order_04` t WHERE t.`uid` = ? LIMIT 1
  13. ==> SHARDING_EXECUTOR_3, name:ds0, Preparing: SELECT t.`id`,t.`uid`,t.`order_no` FROM `order_02` t WHERE t.`uid` = ? LIMIT 1
  14. ==> SHARDING_EXECUTOR_2, name:ds0, Preparing: SELECT t.`id`,t.`uid`,t.`order_no` FROM `order_01` t WHERE t.`uid` = ? LIMIT 1
  15. ==> SHARDING_EXECUTOR_3, name:ds0, Parameters: xiaoming(String)
  16. ==> SHARDING_EXECUTOR_4, name:ds0, Parameters: xiaoming(String)
  17. ==> SHARDING_EXECUTOR_5, name:ds0, Parameters: xiaoming(String)
  18. ==> SHARDING_EXECUTOR_1, name:ds0, Parameters: xiaoming(String)
  19. ==> SHARDING_EXECUTOR_2, name:ds0, Parameters: xiaoming(String)
  20. <== Total: 1

因为uid不是分片键所以在分片查询的时候需要遍历所有的表然后返回对应的数据,可能有同学会问就这?当然这只是简单演示后续下一篇我会给出具体的优化方案来进行处理。

分页查询

分片后的分页查询是分片下的一个难点,这边框架自带功能,分片后分页之所以难是因为如果是自行实现业务代码会变得非常复杂,有一种非常简易的方式就是把分页重写pageIndex永远为1,然后全部取到内存后在进行stream过滤,但是带来的另一个问题就是pageIndex不能便宜过大不然内存会完全存不下导致内存爆炸,并且如果翻页到最后几页那将是灾难性的,给程序带来极其不稳定,但是easy-query提供了和sharding-sphere一样的分片聚合方式并且因为靠近业务的关系所以可以有效的优化深度分页pageIndex过大

  1. @GetMapping("/page")
  2. public Object page(@RequestParam("pageIndex") Integer pageIndex,@RequestParam("pageSize") Integer pageSize) {
  3. EasyPageResult<OrderEntity> pageResult = easyQuery.queryable(OrderEntity.class)
  4. .orderByAsc(o -> o.column(OrderEntity::getOrderNo))
  5. .toPageResult(pageIndex, pageSize);
  6. return pageResult;
  7. }
  8. http://localhost:8080/order/page?pageIndex=1&pageSize=10
  9. {"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}]}
  10. ==> SHARDING_EXECUTOR_3, name:ds0, Preparing: SELECT COUNT(1) FROM `order_02` t
  11. ==> SHARDING_EXECUTOR_2, name:ds0, Preparing: SELECT COUNT(1) FROM `order_03` t
  12. ==> SHARDING_EXECUTOR_5, name:ds0, Preparing: SELECT COUNT(1) FROM `order_04` t
  13. ==> SHARDING_EXECUTOR_1, name:ds0, Preparing: SELECT COUNT(1) FROM `order_01` t
  14. ==> SHARDING_EXECUTOR_4, name:ds0, Preparing: SELECT COUNT(1) FROM `order_00` t
  15. <== Total: 1
  16. ==> 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
  17. ==> 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
  18. ==> 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
  19. ==> 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
  20. ==> 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
  21. <== Total: 10

这边可以看到一行代码实现分页,下面是第二页

  1. http://localhost:8080/order/page?pageIndex=2&pageSize=10
  2. {"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}]}
  3. ==> SHARDING_EXECUTOR_9, name:ds0, Preparing: SELECT COUNT(1) FROM `order_02` t
  4. ==> SHARDING_EXECUTOR_8, name:ds0, Preparing: SELECT COUNT(1) FROM `order_01` t
  5. ==> SHARDING_EXECUTOR_10, name:ds0, Preparing: SELECT COUNT(1) FROM `order_04` t
  6. ==> SHARDING_EXECUTOR_7, name:ds0, Preparing: SELECT COUNT(1) FROM `order_03` t
  7. ==> SHARDING_EXECUTOR_6, name:ds0, Preparing: SELECT COUNT(1) FROM `order_00` t
  8. <== Total: 1
  9. ==> 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
  10. ==> 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
  11. ==> 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
  12. ==> 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
  13. ==> 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
  14. <== Total: 10

按时间分表

这边我们简单还是以order订单为例,按月进行分片假设我们从2022年1月到2023年5月一共17个月表名为t_order_202201t_order_202202t_order_202203...t_order_202304t_order_202305

数据库脚本

  1. create table t_order_202201
  2. (
  3. id varchar(32) not null comment '主键ID'primary key,
  4. uid varchar(50) not null comment '用户id',
  5. order_no int not null comment '订单号',
  6. create_time datetime not null comment '创建时间'
  7. )comment '订单表';
  8. create table t_order_202202
  9. (
  10. id varchar(32) not null comment '主键ID'primary key,
  11. uid varchar(50) not null comment '用户id',
  12. order_no int not null comment '订单号',
  13. create_time datetime not null comment '创建时间'
  14. )comment '订单表';
  15. ....
  16. create table t_order_202304
  17. (
  18. id varchar(32) not null comment '主键ID'primary key,
  19. uid varchar(50) not null comment '用户id',
  20. order_no int not null comment '订单号',
  21. create_time datetime not null comment '创建时间'
  22. )comment '订单表';
  23. create table t_order_202305
  24. (
  25. id varchar(32) not null comment '主键ID'primary key,
  26. uid varchar(50) not null comment '用户id',
  27. order_no int not null comment '订单号',
  28. create_time datetime not null comment '创建时间'
  29. )comment '订单表';
  1. @Data
  2. @Table(value = "t_order",shardingInitializer = OrderByMonthShardingInitializer.class)
  3. public class OrderByMonthEntity {
  4. @Column(primaryKey = true)
  5. private String id;
  6. private String uid;
  7. private Integer orderNo;
  8. /**
  9. * 分片键改为时间
  10. */
  11. @ShardingTableKey
  12. private LocalDateTime createTime;
  13. }
  14. //路由规则可以直接继承AbstractShardingMonthInitializer也可以自己实现
  15. @Component
  16. public class OrderByMonthShardingInitializer extends AbstractShardingMonthInitializer<OrderByMonthEntity> {
  17. /**
  18. * 开始时间不可以使用LocalDateTime.now()因为会导致每次启动开始时间都不一样
  19. * @return
  20. */
  21. @Override
  22. protected LocalDateTime getBeginTime() {
  23. return LocalDateTime.of(2022,1,1,0,0);
  24. }
  25. /**
  26. * 如果不设置那么就是当前时间,用于程序启动后自动计算应该有的表包括最后时间
  27. * @return
  28. */
  29. @Override
  30. protected LocalDateTime getEndTime() {
  31. return LocalDateTime.of(2023,5,31,0,0);
  32. }
  33. @Override
  34. public void configure0(ShardingEntityBuilder<OrderByMonthEntity> builder) {
  35. //后续用来实现优化分表
  36. }
  37. }
  38. //按月分片路由规则也可以自己实现因为框架已经封装好了所以可以用框架自带的
  39. @Component
  40. public class OrderByMonthTableRouteRule extends AbstractMonthTableRule<OrderByMonthEntity> {
  41. @Override
  42. protected LocalDateTime convertLocalDateTime(Object shardingValue) {
  43. return (LocalDateTime)shardingValue;
  44. }
  45. }

初始化

  1. @RestController
  2. @RequestMapping("/orderMonth")
  3. @RequiredArgsConstructor(onConstructor_ = @Autowired)
  4. public class OrderMonthController {
  5. private final EasyQuery easyQuery;
  6. @GetMapping("/init")
  7. public Object init() {
  8. ArrayList<OrderByMonthEntity> orderEntities = new ArrayList<>(100);
  9. List<String> users = Arrays.asList("xiaoming", "xiaohong", "xiaolan");
  10. LocalDateTime beginTime=LocalDateTime.of(2022,1,1,0,0);
  11. LocalDateTime endTime=LocalDateTime.of(2023,5,31,0,0);
  12. int i=0;
  13. while(!beginTime.isAfter(endTime)){
  14. OrderByMonthEntity orderEntity = new OrderByMonthEntity();
  15. orderEntity.setId(String.valueOf(i));
  16. int i1 = i % 3;
  17. String uid = users.get(i1);
  18. orderEntity.setUid(uid);
  19. orderEntity.setOrderNo(i);
  20. orderEntity.setCreateTime(beginTime);
  21. orderEntities.add(orderEntity);
  22. beginTime=beginTime.plusDays(1);
  23. i++;
  24. }
  25. long l = easyQuery.insertable(orderEntities).executeRows();
  26. return "成功插入:"+l;
  27. }
  28. }
  29. http://localhost:8080/orderMonth/init
  30. 成功插入:516

获取第一条数据

  1. @GetMapping("/first")
  2. public Object first(@RequestParam("id") String id) {
  3. OrderEntity orderEntity = easyQuery.queryable(OrderEntity.class)
  4. .whereById(id).firstOrNull();
  5. return orderEntity;
  6. }
  7. http://localhost:8080/orderMonth/first?id=11
  8. {"id":"11","uid":"xiaolan","orderNo":11,"createTime":"2022-01-12T00:00:00"}
  9. //以每5组一个次并发执行聚合
  10. ==> 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
  11. ==> SHARDING_EXECUTOR_1, name:ds0, Parameters: 11(String)
  12. ==> 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
  13. ==> SHARDING_EXECUTOR_2, name:ds0, Parameters: 11(String)
  14. ==> 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
  15. ==> SHARDING_EXECUTOR_3, name:ds0, Parameters: 11(String)
  16. ==> 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
  17. ==> SHARDING_EXECUTOR_4, name:ds0, Parameters: 11(String)
  18. ==> 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
  19. ==> SHARDING_EXECUTOR_5, name:ds0, Parameters: 11(String)
  20. ==> 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
  21. ==> 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
  22. ==> 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
  23. ==> SHARDING_EXECUTOR_1, name:ds0, Parameters: 11(String)
  24. ==> SHARDING_EXECUTOR_2, name:ds0, Parameters: 11(String)
  25. ==> 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
  26. ==> 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
  27. ==> SHARDING_EXECUTOR_5, name:ds0, Parameters: 11(String)
  28. ==> SHARDING_EXECUTOR_3, name:ds0, Parameters: 11(String)
  29. ==> SHARDING_EXECUTOR_4, name:ds0, Parameters: 11(String)
  30. ==> 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
  31. ==> 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
  32. ==> 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
  33. ==> SHARDING_EXECUTOR_5, name:ds0, Parameters: 11(String)
  34. ==> 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
  35. ==> SHARDING_EXECUTOR_3, name:ds0, Parameters: 11(String)
  36. ==> SHARDING_EXECUTOR_2, name:ds0, Parameters: 11(String)
  37. ==> SHARDING_EXECUTOR_4, name:ds0, Parameters: 11(String)
  38. ==> 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
  39. ==> SHARDING_EXECUTOR_1, name:ds0, Parameters: 11(String)
  40. ==> 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
  41. ==> 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
  42. ==> SHARDING_EXECUTOR_2, name:ds0, Parameters: 11(String)
  43. ==> SHARDING_EXECUTOR_5, name:ds0, Parameters: 11(String)
  44. <== Total: 1

获取范围内的数据

  1. @GetMapping("/range")
  2. public Object first() {
  3. List<OrderByMonthEntity> list = easyQuery.queryable(OrderByMonthEntity.class)
  4. .where(o -> o.rangeClosed(OrderByMonthEntity::getCreateTime, LocalDateTime.of(2022, 3, 1, 0, 0), LocalDateTime.of(2022, 9, 1, 0, 0)))
  5. .toList();
  6. return list;
  7. }
  8. http://localhost:8080/orderMonth/range
  9. [{"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"}]
  10. //可以精准定位到对应的分片路由上获取数据
  11. ==> 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` <= ?
  12. ==> 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` <= ?
  13. ==> 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` <= ?
  14. ==> 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` <= ?
  15. ==> 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` <= ?
  16. ==> SHARDING_EXECUTOR_4, name:ds0, Parameters: 2022-03-01T00:00(LocalDateTime),2022-09-01T00:00(LocalDateTime)
  17. ==> SHARDING_EXECUTOR_3, name:ds0, Parameters: 2022-03-01T00:00(LocalDateTime),2022-09-01T00:00(LocalDateTime)
  18. ==> SHARDING_EXECUTOR_2, name:ds0, Parameters: 2022-03-01T00:00(LocalDateTime),2022-09-01T00:00(LocalDateTime)
  19. ==> SHARDING_EXECUTOR_5, name:ds0, Parameters: 2022-03-01T00:00(LocalDateTime),2022-09-01T00:00(LocalDateTime)
  20. ==> SHARDING_EXECUTOR_1, name:ds0, Parameters: 2022-03-01T00:00(LocalDateTime),2022-09-01T00:00(LocalDateTime)
  21. ==> 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` <= ?
  22. ==> 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` <= ?
  23. ==> SHARDING_EXECUTOR_4, name:ds0, Parameters: 2022-03-01T00:00(LocalDateTime),2022-09-01T00:00(LocalDateTime)
  24. ==> SHARDING_EXECUTOR_2, name:ds0, Parameters: 2022-03-01T00:00(LocalDateTime),2022-09-01T00:00(LocalDateTime)
  25. <== Total: 185

最后

目前为止你已经看到了easy-query对于分片的便捷性,但是本章只是开胃小菜,相信了解分库分表的小伙伴肯定会说就这?不是和sharding-jdbc一样吗为什么要用你的呢。我想说第一篇只是给大家了解一下如何使用,后续的文章才是分表分库的精髓相信我你一定没看过

demo地址 https://github.com/xuejmnet/easy-sharding-test

原文链接:https://www.cnblogs.com/xuejiaming/p/17424414.html

 友情链接:直通硅谷  点职佳  北美留学生论坛

本站QQ群:前端 618073944 | Java 606181507 | Python 626812652 | C/C++ 612253063 | 微信 634508462 | 苹果 692586424 | C#/.net 182808419 | PHP 305140648 | 运维 608723728

W3xue 的所有内容仅供测试,对任何法律问题及风险不承担任何责任。通过使用本站内容随之而来的风险与本站无关。
关于我们  |  意见建议  |  捐助我们  |  报错有奖  |  广告合作、友情链接(目前9元/月)请联系QQ:27243702 沸活量
皖ICP备17017327号-2 皖公网安备34020702000426号