javaer你还在手写分表分库?来看看这个框架怎么做的 干货满满 ...

java orm框架easy-query分库分表之分表

高并发三驾马车:分库分表、MQ、缓存。今天给大家带来的就是分库分表的干货解决方案,哪怕你不用我的框架也可以从中听到不一样的结局方案和实现。
一款支持自动分表分库的orm框架easy-query  帮助您解脱跨库带来的复杂业务代码,并且提供多种结局方案和自定义路由来实现比中间件更高性能的数据库访问。

• GITHUB  github地址
  
• GITEE  gitee地址
  

目前市面上有的分库分表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.                
2.                 <dependency>
3.                         <groupId>com.alibaba</groupId>
4.                         <artifactId>druid</artifactId>
5.                         <version>1.2.15</version>
6.                 </dependency>
7.                
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
4. spring:
6.   datasource:
7.     type: com.alibaba.druid.pool.DruidDataSource
8.     driver-class-name: com.mysql.cj.jdbc.Driver
9.     url: jdbc:mysql://127.0.0.1:3306/easy-sharding-test?serverTimezone=GMT%2B8&characterEncoding=utf-8&useSSL=false&allowMultiQueries=true&rewriteBatchedStatements=true
10.     username: root
11.     password: root
13. logging:
14.   level:
15.     com.easy.query.core: debug
17. easy-query:
18.   enable: true
19.   name-conversion: underlined
20.   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.     }
23.     /**
24.      * 编写模5后的尾巴长度默认我们设置2就是左补0
25.      * @return
26.      */
27.     @Override
28.     protected int tailLength() {
29.         return 2;
30.     }
31. }
32. //编写分片规则`AbstractModTableRule`由框架提供取模分片路由规则,如果需要自己实现可以继承`AbstractTableRouteRule`这个抽象类
33. @Component
34. public class OrderTableRouteRule extends AbstractModTableRule<OrderEntity> {
35.     @Override
36.     protected int mod() {
37.         return 5;
38.     }
40.     @Override
41.     protected int tailLength() {
42.         return 2;
43.     }
44. }

复制代码

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

1. @RestController
2. @RequestMapping("/order")
3. @RequiredArgsConstructor(onConstructor_ = @Autowired)
4. public class OrderController {
6.     private final EasyQuery easyQuery;
8.     @GetMapping("/init")
9.     public Object init() {
10.         ArrayList<OrderEntity> orderEntities = new ArrayList<>(100);
11.         List<String> users = Arrays.asList("xiaoming", "xiaohong", "xiaolan");
13.         for (int i = 0; i < 100; i++) {
14.             OrderEntity orderEntity = new OrderEntity();
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.             orderEntities.add(orderEntity);
21.         }
22.         long l = easyQuery.insertable(orderEntities).executeRows();
23.         return "成功插入:"+l;
24.     }
25. }

复制代码

查询单条

按分片键查询

可以完美的路由到对应的数据库表和操作单表拥有一样的性能
[code]    @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)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) 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 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 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 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 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) 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)