分库分表与自定义分表规则

总体介绍

  • Oinone的分库分表方案是基于Sharding-JDBC的整合方案,要先具备一些Sharding-JDBC的知识。[Sharding-JDBC]https://shardingsphere.apache.org/document/current/cn/overview/

  • 做分库分表前,大家要有一个明确注意的点就是分表字段(也叫均衡字段)的选择,它是非常重要的,与业务场景非常相关。在明确了分库分表字段以后,甚至在功能上都要做一些妥协。比如分库分表字段在查询管理中做为查询条件是必须带上的,不然效率只会更低。

  • 分表字段不允许更新,所以代码里更新策略设置类永不更新,并在设置了在页面修改的时候为readonly

配置分表策略

  1. 配置ShardingModel模型走分库分表的数据源pamirsSharding
  2. 为pamirsSharding配置数据源以及sharding规则
    a. pamirs.sharding.define用于oinone的数据库表创建用
    b. pamirs.sharding.rule用于分表规则配置
  3. 为pamirsSharding配置数据源以及sharding规则

    1)指定模型对应数据源

pamirs:
  framework:
    system:
      system-ds-key: base
      system-models:
        - base.WorkerNode
    data:
      default-ds-key: pamirs
      ds-map:
        base: base
      modelDsMap:
        "[demo.ShardingModel]": pamirsSharding  #配置模型对应的库

2)分库分表规则配置

pamirs: 
  sharding:
    define:
      data-sources:
        ds: pamirs
        pamirsSharding: pamirs #申明pamirsSharding库对应的pamirs数据源
      models:
        "[trigger.PamirsSchedule]":
          tables: 0..13
        "[demo.ShardingModel]":
          tables: 0..7
          table-separator: _
    rule:
      pamirsSharding: #配置pamirsSharding库的分库分表规则
        actual-ds:
          - pamirs  #申明pamirsSharding库对应的pamirs数据源
        sharding-rules:
          # Configure sharding rule ,以下配置跟sharding-jdbc配置一致
          - tables:
              demo_core_sharding_model: #demo_core_sharding_model表规则配置
                actualDataNodes: pamirs.demo_core_sharding_model_${0..7}
                tableStrategy:
                  standard:
                    shardingColumn: user_id
                    shardingAlgorithmName: table_inline
            shardingAlgorithms:
              table_inline:
                type: INLINE
                props:
                  algorithm-expression: demo_core_sharding_model_${(Long.valueOf(user_id) % 8)}
        props:
          sql.show: true

自定义规则

  • 默认规则即通用的分库分表策略,如按照数据量、哈希等方式进行分库分表;通常默认规则是可以的。
  • 但在一些复杂的业务场景下,使用默认规则可能无法满足需求,需要根据实际情况进行自定义。例如,某些业务可能有特定的数据分布模式或者查询特点,需要定制化的分库分表规则来优化数据访问性能或者满足业务需求。在这种情况下,使用自定义规则可以更好地适应业务的需求。

自定义分表规则示例

示例1:按月份分表(DATE_MONTH )

package pro.shushi.pamirs.demo.core.sharding;

import cn.hutool.core.date.DateUtil;
import com.google.common.collect.Range;
import org.apache.shardingsphere.sharding.api.sharding.standard.PreciseShardingValue;
import org.apache.shardingsphere.sharding.api.sharding.standard.RangeShardingValue;
import org.apache.shardingsphere.sharding.api.sharding.standard.StandardShardingAlgorithm;
import org.springframework.stereotype.Component;
import pro.shushi.pamirs.meta.annotation.fun.extern.Slf4j;

import java.util.*;

/**
 * @author wangxian
 * @version 1.0
 * @description
 */
@Component
@Slf4j
public class DateMonthShardingAlgorithm implements StandardShardingAlgorithm<Date> {

    private Properties props;

    @Override
    public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Date> preciseShardingValue) {
        Date date = preciseShardingValue.getValue();
        String suffix = "_" + (DateUtil.month(date) + 1);
        for (String tableName : availableTargetNames) {
            if (tableName.endsWith(suffix)) {
                return tableName;
            }
        }
        throw new IllegalArgumentException("未找到匹配的数据表");
    }

    @Override
    public Collection<String> doSharding(Collection<String> availableTargetNames, RangeShardingValue<Date> rangeShardingValue) {
        List<String> list = new ArrayList<>();
        log.info(rangeShardingValue.toString());
        Range<Date> valueRange = rangeShardingValue.getValueRange();
        Date lowerDate = valueRange.lowerEndpoint();
        Date upperDate = valueRange.upperEndpoint();
        Integer begin = DateUtil.month(lowerDate) + 1;
        Integer end = DateUtil.month(upperDate) + 1;
        TreeSet<String> suffixList = ShardingUtils.getSuffixListForRange(begin, end);
        for (String tableName : availableTargetNames) {
            if (containTableName(suffixList, tableName)) {
                list.add(tableName);
            }
        }
        return list;
    }

    private boolean containTableName(Set<String> suffixList, String tableName) {
        boolean flag = false;
        for (String s : suffixList) {
            if (tableName.endsWith(s)) {
                flag = true;
                break;
            }
        }
        return flag;
    }

    @Override
    public void init() {

    }

    @Override
    public String getType() {
        return "DATE_MONTH";
    }

    @Override
    public Properties getProps() {
        return this.props;
    }

    @Override
    public void setProps(Properties properties) {
        this.props = props;
    }
}

示例2:按特定字段截取去取模分表

package pro.shushi.pamirs.demo.core.sharding;

import org.apache.shardingsphere.sharding.api.sharding.standard.PreciseShardingValue;
import org.apache.shardingsphere.sharding.api.sharding.standard.RangeShardingValue;
import org.apache.shardingsphere.sharding.api.sharding.standard.StandardShardingAlgorithm;
import org.springframework.stereotype.Component;
import pro.shushi.pamirs.meta.annotation.fun.extern.Slf4j;

import java.util.Collection;
import java.util.Properties;

/**
 * @author wangxian
 * @version 1.0
 * @description
 */
@Component
@Slf4j
public class AppUserCodeShardingAlgorithm implements StandardShardingAlgorithm<String> {

    private Properties props;

    @Override
    public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<String> preciseShardingValue) {
        String appUserCode = preciseShardingValue.getValue();
        String suffix = "_" + Long.parseLong(appUserCode.substring(1)) % 21;
        for (String tableName : availableTargetNames) {
            if (tableName.endsWith(suffix)) {
                return tableName;
            }
        }
        throw new IllegalArgumentException("未找到匹配的数据表");
    }

    @Override
    public Collection<String> doSharding(final Collection<String> availableTargetNames, final RangeShardingValue<String> shardingValue) {
        return availableTargetNames;
    }

    @Override
    public String getType() {
        return "APP_USER_CODE_TYPE";
    }

    @Override
    public Properties getProps() {
        return this.props;
    }

    @Override
    public void setProps(Properties properties) {
        this.props = props;
    }

    @Override
    public void init() {

    }
}

使用自定义分表策略

1)指定模型对应数据源

pamirs:
  framework:
    system:
      system-ds-key: base
      system-models:
        - base.WorkerNode
    data:
      default-ds-key: pamirs_biz
      ds-map:
        base: base
        demo_core: pamirs
      modelDsMap:
        "[demo.record.MsgRecode]": pamirsSharding

2)分库分表规则配置

pamirs:
  sharding:
    define:
      data-sources:
        ds: pamirs
        pamirsSharding: pamirs
      models:
        "[trigger.PamirsSchedule]":
          tables: 0..13
        "[demo.record.MsgRecode]":
          tables: 0..20
          table-separator: _
    rule:
      pamirsSharding:
        actual-ds:
          - pamirs
        sharding-rules:
          - tables:
              demo_core_record_msg_recode:
                actualDataNodes: pamirs.demo_core_record_msg_recode_${0..20}
                tableStrategy:
                  standard:
                    shardingColumn: app_user_code
                    shardingAlgorithmName: app_user_code_table_algorithm
            shardingAlgorithms:
              app_user_code_table_algorithm:
                type: APP_USER_CODE_TYPE
                props:
                  strategy: STANDARD
                  algorithmClassName:
                    pro.shushi.pamirs.demo.core.sharding.AppUserCodeShardingAlgorithm

配置自定义规则SPI

分库分表规则SPI

在resources/META-INF/services 配置 org.apache.shardingsphere.sharding.spi.ShardingAlgorithm

pro.shushi.pamirs.demo.core.sharding.AppUserCodeShardingAlgorithm
pro.shushi.pamirs.demo.core.sharding.DateMonthShardingAlgorithm

Oinone社区 作者:望闲原创文章,如若转载,请注明出处:https://doc.oinone.top/backend/7155.html

访问Oinone官网:https://www.oinone.top获取数式Oinone低代码应用平台体验

Like (0)
望闲's avatar望闲数式管理员
Previous 2024年5月9日 pm3:56
Next 2024年5月13日 pm7:06

相关推荐

  • 【HighGo】后端部署使用HighGo数据库

    HighGo数据库配置 驱动配置 jdbc仓库 https://mvnrepository.com/artifact/com.highgo/HgdbJdbc Maven配置(6.0.1版本可用) <highgo.version>6.0.1.jre8</highgo.version> <dependency> <groupId>com.highgo</groupId> <artifactId>HgdbJdbc</artifactId> <version>${highgo.version}</version> </dependency> JDBC连接配置 pamirs: datasource: base: type: com.alibaba.druid.pool.DruidDataSource driverClassName: com.highgo.jdbc.Driver url: jdbc:highgo://127.0.0.1:5866/oio_base?currentSchema=base,utl_file username: xxxxxx password: xxxxxx initialSize: 5 maxActive: 200 minIdle: 5 maxWait: 60000 timeBetweenEvictionRunsMillis: 60000 testWhileIdle: true testOnBorrow: false testOnReturn: false poolPreparedStatements: true asyncInit: true 连接url配置 官方文档 https://www.highgo.com/document/zh-cn/application/jdbc.html url格式 jdbc:highgo://ip:端口号/数据库名?currentSchema=schema1,schema2 在jdbc连接配置时,${database}和${schema}必须完整配置,不可缺省。 jdbc指定schema时可以在currentSchema后指定多个schema,中间用,分隔,第一个schema为业务库表存放的主schema。 highgo数据库6.0版本里每个数据库默认会带一个utl_file的schema,该模式与文件访问功能有关,需要带在jdbc的schema中,但不能放在第一个。 其他连接参数如需配置,可自行查阅相关资料进行调优。 方言配置 pamirs方言配置 pamirs: dialect: ds: base: type: HighGoDB version: 6 major-version: 6.0.1 biz_data: type: HighGoDB version: 6 major-version: 6.0.1 数据库版本 type version majorVersion 6.0.x HighGo 6 6.0.1 PS:由于方言开发环境为6.0.1版本,其他类似版本(6.0.x)原则上不会出现太大差异,如出现其他版本无法正常支持的,可在文档下方留言。 schedule方言配置 pamirs: event: enabled: true schedule: enabled: true dialect: type: HighGoDB version: 6 major-version: 6.0.1 其他配置 逻辑删除的值配置 pamirs: mapper: global: table-info: logic-delete-value: (EXTRACT(epoch FROM CURRENT_TIMESTAMP) * 1000000 + EXTRACT(MICROSECONDS FROM CURRENT_TIMESTAMP))::bigint Highgo数据库用户初始化及授权 — init oio_base user (user name can be modified by oneself) CREATE USER oio_base WITH PASSWORD 'Test@12345678'; — if using automatic database and schema creation, this is very important. ALTER USER oio_base CREATEDB; SELECT * FROM pg_roles; — if using highgo database, this authorization is required. GRANT CREATE ON DATABASE highgo TO oio_base;

    2025年7月10日
    98800
  • Excel导入扩展点-整体导入(批量导入)

    1、【导入】在有些场景,需要获取Excel导入的整体数据,进行批量的操作或者校验 可以通过实现导入扩展点的方式实现,入参data是导入Excel的数据列表;业务可以根据实际情况进行数据校验 1)Excel模板定义,需要设置setEachImport(false) 2)导入扩展点API定义 pro.shushi.pamirs.file.api.extpoint.ExcelImportDataExtPoint#importData 3)示例代码参考: pro.shushi.pamirs.translate.extpoint.ResourceTranslationImportExtPoint#importData @Slf4j @Component @Ext(ExcelImportTask.class) public class ResourceTranslationImportExtPoint extends AbstractExcelImportDataExtPointImpl<List<ResourceTranslationItem>> { @Override //TODO 表达式,可以自定义,比如可以支持1个模型的多个【导入名称】的不同模板 @ExtPoint.Implement(expression = "importContext.definitionContext.model==\"" + ResourceTranslation.MODEL_MODEL + "\"") public Boolean importData(ExcelImportContext importContext, List<ResourceTranslationItem> dataList) { //TODO dataList就是excel导入那个sheet的所有内容 return true; } } 2、【导入】逐行导入的时候做事务控制 在模板中定义中增加事务的定义,并设置异常后回滚。参加示例代码: excel模板定义 @Component public class DemoItemImportTemplate implements ExcelTemplateInit { public static final String TEMPLATE_NAME = "商品导入模板"; @Override public List<ExcelWorkbookDefinition> generator() { //定义事务(导入处理中,只操作单个表的不需要事务定义。) //是否定义事务根据实际业务逻辑确定。比如:有些场景在导入前需要删除数据后在进行导入就需要定义事务 InitializationUtil.addTxConfig(DemoItem.MODEL_MODEL, ExcelDefinitionContext.EXCEL_TX_CONFIG_PREFIX + TEMPLATE_NAME); return Collections.singletonList( ExcelHelper.fixedHeader(DemoItem.MODEL_MODEL, TEMPLATE_NAME) .setType(ExcelTemplateTypeEnum.IMPORT) .createSheet("商品导入-sheet1") .createBlock(DemoItem.MODEL_MODEL) .addUnique(DemoItem.MODEL_MODEL,"name") .addColumn("name","名称") .addColumn("description","描述") .addColumn("itemPrice","单价") .addColumn("inventoryQuantity","库存") .build().setEachImport(true) //TODO 设置异常后回滚的标识,这个地方会回滚事务 .setHasErrorRollback(true) .setExcelImportMode(ExcelImportModeEnum.SINGLE_MODEL) ); } } 导入逻辑处理 @Slf4j @Component @Ext(ExcelImportTask.class) public class DemoItemImportExtPoint extends AbstractExcelImportDataExtPointImpl<DemoItem> implements ExcelImportDataExtPoint<DemoItem> { @Autowired private DemoItemService demoItemService; @Override @ExtPoint.Implement(expression = "importContext.definitionContext.model == \"" + DemoItem.MODEL_MODEL + "\"") public Boolean importData(ExcelImportContext importContext, DemoItem data) { ExcelImportTask importTask = importContext.getImportTask(); try { DemoItemImportTask hrExcelImportTask = new DemoItemImportTask().queryById(importTask.getId()); String publishUserName = Optional.ofNullable(hrExcelImportTask).map(DemoItemImportTask::getPublishUserName).orElse(null); data.setPublishUserName(publishUserName); demoItemService.create(data); } catch(PamirsException e) { log.error("导入异常", e); } catch (Exception e) { log.error("导入异常", e); } return Boolean.TRUE; } }

    2023年12月7日
    2.3K00
  • 如何自定义Excel导入功能

    介绍 在平台提供的默认导入功能无法满足业务需求的时候,我们可以自定义导入功能,以满足业务中个性化的需求。 功能示例 下面以导入文件的时候加入发布人的字段作为示例讲解。 继承平台的导入任务模型,加上需要在导入的弹窗视图需要展示的字段 package pro.shushi.pamirs.demo.api.model; import pro.shushi.pamirs.file.api.model.ExcelImportTask; import pro.shushi.pamirs.meta.annotation.Field; import pro.shushi.pamirs.meta.annotation.Model; @Model.model(DemoItemImportTask.MODEL_MODEL) @Model(displayName = "商品-Excel导入任务") public class DemoItemImportTask extends ExcelImportTask { public static final String MODEL_MODEL = "demo.DemoItemImportTask"; // 自定义显示的字段 @Field.String @Field(displayName = "发布人") private String publishUserName; } 编写自定义导入弹窗视图的数据初始化方法和导入提交的action package pro.shushi.pamirs.demo.core.action; import org.springframework.stereotype.Component; import pro.shushi.pamirs.boot.base.resource.PamirsFile; import pro.shushi.pamirs.demo.api.model.DemoItemImportTask; import pro.shushi.pamirs.file.api.action.ExcelImportTaskAction; import pro.shushi.pamirs.file.api.config.FileProperties; import pro.shushi.pamirs.file.api.model.ExcelWorkbookDefinition; import pro.shushi.pamirs.file.api.service.ExcelFileService; import pro.shushi.pamirs.meta.annotation.Action; import pro.shushi.pamirs.meta.annotation.Function; import pro.shushi.pamirs.meta.annotation.Model; import pro.shushi.pamirs.meta.annotation.fun.extern.Slf4j; import pro.shushi.pamirs.meta.enmu.ActionContextTypeEnum; import pro.shushi.pamirs.meta.enmu.FunctionOpenEnum; import pro.shushi.pamirs.meta.enmu.FunctionTypeEnum; import pro.shushi.pamirs.meta.enmu.ViewTypeEnum; @Slf4j @Component @Model.model(DemoItemImportTask.MODEL_MODEL) public class DemoItemExcelImportTaskAction extends ExcelImportTaskAction { public DemoItemExcelImportTaskAction(FileProperties fileProperties, ExcelFileService excelFileService) { super(fileProperties, excelFileService); } @Action(displayName = "导入", contextType = ActionContextTypeEnum.CONTEXT_FREE, bindingType = {ViewTypeEnum.TABLE}) public DemoItemImportTask createImportTask(DemoItemImportTask data) { if (data.getWorkbookDefinitionId() != null) { ExcelWorkbookDefinition workbookDefinition = new ExcelWorkbookDefinition(); workbookDefinition.setId(data.getWorkbookDefinitionId()); data.setWorkbookDefinition(workbookDefinition); } Object fileId = data.get_d().get("fileId"); if (fileId != null) { PamirsFile pamirsFile = new PamirsFile().queryById(Long.valueOf(fileId.toString())); data.setFile(pamirsFile); } super.createImportTask(data); return data; } /** * @param data * @return */ @Function(openLevel = FunctionOpenEnum.API) @Function.Advanced(type = FunctionTypeEnum.QUERY) public DemoItemImportTask construct(DemoItemImportTask data) { data.construct(); return data; } } 编写导入的单行数据处理逻辑,此处可以拿到导入弹窗内自定义的字段提交的值,然后根据这些值处理自定义逻辑,此处演示代码就是将导入后的商品的发布人都设置为自定义导入视图填的发布人信息 package pro.shushi.pamirs.demo.core.excel.extPoint; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Component; import pro.shushi.pamirs.demo.api.model.DemoItem; import pro.shushi.pamirs.demo.api.model.DemoItemImportTask; import pro.shushi.pamirs.demo.api.service.DemoItemService; import pro.shushi.pamirs.file.api.context.ExcelImportContext; import pro.shushi.pamirs.file.api.extpoint.AbstractExcelImportDataExtPointImpl; import pro.shushi.pamirs.file.api.extpoint.ExcelImportDataExtPoint;…

    2023年11月22日
    1.8K00
  • 如何重写获取首页的方法

    介绍 用户登录成功后或者访问网页不带任何路由参数的时候前端会请求全局的首页的视图动作viewAction配置,然后跳转到该视图动作viewAction 方案 我们可以通过在该方法的后置hook自定义获取首页的逻辑,下面以按角色跳转不同首页的需求示例 package pro.shushi.pamirs.demo.core.hook; import org.apache.commons.collections4.CollectionUtils; import org.springframework.stereotype.Component; import pro.shushi.pamirs.auth.api.model.AuthRole; import pro.shushi.pamirs.boot.base.enmu.BaseExpEnumerate; import pro.shushi.pamirs.boot.base.model.ViewAction; import pro.shushi.pamirs.boot.web.loader.PageLoadAction; import pro.shushi.pamirs.demo.api.model.DemoItemCategory; import pro.shushi.pamirs.demo.api.model.DemoItemLabel; import pro.shushi.pamirs.meta.annotation.Hook; import pro.shushi.pamirs.meta.api.CommonApiFactory; import pro.shushi.pamirs.meta.api.core.faas.HookAfter; import pro.shushi.pamirs.meta.api.dto.fun.Function; import pro.shushi.pamirs.meta.api.session.PamirsSession; import pro.shushi.pamirs.meta.common.exception.PamirsException; import pro.shushi.pamirs.user.api.model.PamirsUser; import java.util.List; import java.util.stream.Collectors; @Component public class DemoHomepageHook implements HookAfter { private static final String TEST_ROLE_CODE_01 = "ROLE_1211"; private static final String TEST_ROLE_CODE_02 = "ROLE_1211_1"; @Override @Hook(module = {"base"}, model = {ViewAction.MODEL_MODEL}, fun = {"homepage"}) public Object run(Function function, Object ret) { if (ret == null) { return null; } ViewAction viewAction = getViewActionByCurrentRole(); if (viewAction != null) { ViewAction retNew = CommonApiFactory.getApi(PageLoadAction.class).load(viewAction); ViewAction viewActionRet = (ViewAction) ((Object[]) ret)[0]; viewActionRet.set_d(retNew.get_d()); } return ret; } protected ViewAction getViewActionByCurrentRole() { try { PamirsUser user = new PamirsUser(); user.setId(PamirsSession.getUserId()); user.fieldQuery(PamirsUser::getRoles); List<AuthRole> roles = user.getRoles(); if (CollectionUtils.isNotEmpty(roles)) { List<String> roleCodes = roles.stream().map(AuthRole::getCode).collect(Collectors.toList()); if (roleCodes.contains(TEST_ROLE_CODE_01)) { return new ViewAction().setModel(DemoItemCategory.MODEL_MODEL).setName("DemoMenus_ItemPMenu_DemoItemAndCateMenu_DemoItemCategoryMenu").queryOne(); } else if (roleCodes.contains(TEST_ROLE_CODE_02)) { return new ViewAction().setModel(DemoItemLabel.MODEL_MODEL).setName("DemoMenus_ItemPMenu_DemoItemAndCateMenu_DemoItemLabelMenu").queryOne(); } } } catch (PamirsException exception) { if (PamirsSession.getUserId() == null) { throw PamirsException.construct(BaseExpEnumerate.BASE_USER_NOT_LOGIN_ERROR, exception.getCause()).errThrow(); } else { throw exception; } } return null; } }

    2024年7月6日
    3.3K00
  • Excel导入导出模板翻译

    导出翻译项 与翻译的导出全部翻译项类似,只是该操作目前没有加入到页面交互中,需要通过工具发起后端服务请求,拿到导入导出翻译Excel模版,添加模版翻译项。(查看路径:文件–导出任务) mutation { excelExportTaskMutation { createExportTask( data: { workbookDefinition: { model: "file.ExcelWorkbookDefinition" name: "excelLocationTemplate" } } ) { name } } } variables: { "path": "/file", "lang": "en-US" } 参数说明:(不在以下说明范围内的参数无需修改) variables.lang参数:用于指定翻译项的目标语言编码,与【资源】-【语言】中的编码一致。 导入翻译项 mutation { excelImportTaskMutation { createImportTask( data: { workbookDefinition: { model: "file.ExcelWorkbookDefinition" name: "excelLocationTemplate" } file: { url: "翻译项URL链接" } } ) { name } } } variables: { "path": "/file" } 参数说明: 将翻译项URL链接改为实际可访问的文件链接即可,可通过页面中任意文件上传的组件获取。

    2024年12月5日
    1.8K00

Leave a Reply

Please Login to Comment