javaer你還在手寫分表分庫?來看看這個框架怎麼做的 乾貨滿滿
2023-05-26 09:00:47
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一樣嗎為什麼要用你的呢。我想說第一篇只是給大家瞭解一下如何使用,後續的文章才是分表分庫的精髓相信我你一定沒看過
demo地址 https://github.com/xuejmnet/easy-sharding-test