跳至主要內容

分表

xuejmnet2023/5/19大约 6 分钟

easy-query提供了高效,高性能的分片机制,完美的屏蔽分片带来的业务复杂度,不同于sharding-jdbc的sql的antlr解析采用自带的表达式解析性能高效,并且不同于ShardingSphere-Proxy的代理模式,导致未分片的对象也需要走代理,并且需要多次jdbc,easy-query采用客户端分片保证分片下的高性能查询结果返回,并且原生orm框架自带无需使用额外组件,更少的依赖来保证程序的健壮与可控

注意3.x后分片需要手动开启

springboot默认yml设置sharding: true
控制台请替换服务


        easyQueryClient = EasyQueryBootstrapper.defaultBuilderConfiguration()
                .setDefaultDataSource(dataSource)
                .optionConfigure(op -> {
                    op.setDeleteThrowError(false);
                    op.setExecutorCorePoolSize(1);
                    op.setExecutorMaximumPoolSize(0);
                    op.setMaxShardingQueryLimit(10);
                    op.setShardingOption(easyQueryShardingOption);
                    op.setDefaultDataSourceName("ds2020");
                    op.setThrowIfRouteNotMatch(false);
                    op.setMaxShardingRouteCount(512);
                    op.setDefaultDataSourceMergePoolSize(20);
                    op.setStartTimeJob(true);
                    op.setReverseOffsetThreshold(10);
                })
                .useDatabaseConfigure(new MySQLDatabaseConfiguration())
                //这一句
                .replaceService(EntityExpressionExecutor.class, ShardingEntityExpressionExecutor.class)
                .build();

创建表

我们以订单表为例来实现订单的简单取模分表,将订单表按5取模进行分表分为t_order_00、t_order_01....t_order_04

数据库对象

配置文件

因为分片涉及到跨表聚合所以需要设置默认数据源的连接池大小，并且设置分片可用数据源大小


server:
  port: 8081

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_sample?serverTimezone=GMT%2B8&characterEncoding=utf-8&useSSL=false&allowMultiQueries=true&rewriteBatchedStatements=true
    username: root
    password: root
    druid:
      initial-size: 10
      max-active: 100

easy-query:
  enable: true
  name-conversion: underlined
  database: mysql
  defaultDataSourceMergePoolSize: 50
  #entity映射到dto/vo使用属性匹配模式
  mapping-strategy: property_first

我们设置了最大连接数100,分片可用连接池数50

新增


@RestController
@RequestMapping("/orderShardingTable")
@RequiredArgsConstructor(onConstructor_ = @Autowired)
public class OrderShardingTableController {
    private final EasyQuery easyQuery;

    @GetMapping("/add")
    public Object add() {
        ArrayList<OrderEntity> orderEntities = new ArrayList<>(100);
        List<String> users = Arrays.asList("xiaoming", "xiaohong", "xiaolan");

        for (int i = 0; i < 10; 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);
            orderEntity.setStatus(i1);
            orderEntity.setCreateTime(LocalDateTime.now());
            orderEntities.add(orderEntity);
        }
        long l = easyQuery.insertable(orderEntities).executeRows();
        return "成功插入:"+l;
    }
}

修改

进修改时间所以只会变更时间

@GetMapping("/edit")
@EasyQueryTrack
public Object edit() {
    OrderEntity orderEntity = easyQuery.queryable(OrderEntity.class)
            .asTracking()
            .where(o->o.eq(OrderEntity::getId,"1")).firstNotNull("未找到对应的订单");
    orderEntity.setCreateTime(LocalDateTime.now());
    easyQuery.updatable(orderEntity)
            .executeRows();
    return orderEntity;
}
: ==> http-nio-8081-exec-1, name:ds0, Preparing: SELECT `id`,`uid`,`order_no`,`status`,`create_time` FROM `order_04` WHERE `id` = ? LIMIT 1
: ==> http-nio-8081-exec-1, name:ds0, Parameters: 1(String)
: <== http-nio-8081-exec-1, name:ds0, Time Elapsed: 20(ms)
: <== Total: 1
: ==> http-nio-8081-exec-1, name:ds0, Preparing: UPDATE `order_04` SET `create_time` = ? WHERE `id` = ?
: ==> http-nio-8081-exec-1, name:ds0, Parameters: 2023-09-02T15:20:13.029025(LocalDateTime),1(String)
: <== http-nio-8081-exec-1, name:ds0, Total: 1

并发更新，将订单状态为2的数据改为3,并且需要支持并发操作


@GetMapping("/concurrentEdit")
@EasyQueryTrack
public Object concurrentEdit() {
    OrderEntity orderEntity = easyQuery.queryable(OrderEntity.class)
            .asTracking()
            .where(o->o.eq(OrderEntity::getId,"2")).firstNotNull("未找到对应的订单");
    if(!Objects.equals(2,orderEntity.getStatus())){
        throw new RuntimeException("订单状态不是2");
    }
    orderEntity.setCreateTime(LocalDateTime.now());
    orderEntity.setStatus(3);
    easyQuery.updatable(orderEntity)
            .whereColumns(o->o.columnKeys().column(OrderEntity::getStatus))
            .executeRows(1,"并发修改失败");
    return orderEntity;
}

: ==> http-nio-8081-exec-1, name:ds0, Preparing: SELECT `id`,`uid`,`order_no`,`status`,`create_time` FROM `order_00` WHERE `id` = ? LIMIT 1
: ==> http-nio-8081-exec-1, name:ds0, Parameters: 2(String)
: <== http-nio-8081-exec-1, name:ds0, Time Elapsed: 5(ms)
: <== Total: 1
: ==> http-nio-8081-exec-1, name:ds0, Preparing: UPDATE `order_00` SET `status` = ?,`create_time` = ? WHERE `id` = ? AND `status` = ?
: ==> http-nio-8081-exec-1, name:ds0, Parameters: 3(Integer),2023-09-02T15:23:51.745936(LocalDateTime),2(String),2(Integer)
: <== http-nio-8081-exec-1, name:ds0, Total: 1

删除


@GetMapping("/delete")
public Object delete() {
    OrderEntity orderEntity = easyQuery.queryable(OrderEntity.class)
            .asTracking()
            .where(o->o.eq(OrderEntity::getId,"3")).firstNotNull("未找到对应的订单");
    orderEntity.setCreateTime(LocalDateTime.now());
    easyQuery.deletable(orderEntity)
            .allowDeleteStatement(true)
            .executeRows();
    return orderEntity;
}

使用分片键删除可以精确到对应的分片表

: ==> http-nio-8081-exec-3, name:ds0, Preparing: SELECT `id`,`uid`,`order_no`,`status`,`create_time` FROM `order_01` WHERE `id` = ? LIMIT 1
: ==> http-nio-8081-exec-3, name:ds0, Parameters: 3(String)
: <== http-nio-8081-exec-3, name:ds0, Time Elapsed: 6(ms)
: <== Total: 1
: ==> http-nio-8081-exec-3, name:ds0, Preparing: DELETE FROM `order_01` WHERE `id` = ?
: ==> http-nio-8081-exec-3, name:ds0, Parameters: 3(String)
: <== http-nio-8081-exec-3, name:ds0, Total: 1

贡献者

xuejiamingHoysing
默认页脚
Copyright © 2025 xuejmnet