Skip to main content

Data Operation:Database and Table Sharding with Custom Sharding Rules

Ⅰ、Scenario Description

  • Oinone's database and table sharding solution is based on integration with Sharding-JDBC. Relevant personnel should first master certain Sharding-JDBC knowledge beforehand. Sharding-JDBC
  • Before implementing database and table sharding, it is crucial to clarify the rational selection of the sharding field (also known as the balancing field). This link is of vital importance and is closely related to specific business scenarios. After determining the database and table sharding field, concessions may even need to be made at the functional level. For example, in query management, the sharding field is indispensable as a query condition; otherwise, query efficiency will significantly decrease.
  • Sharding fields are strictly prohibited from being updated. Therefore, at the code level, the update strategy setting class should be set to never update, and it should be set as readonly in the page modification settings.

Ⅱ、Configuring Sharding Strategies

  • Configure the ShardingModel to use the sharded data source pamirsSharding.
  • Complete the configuration of data sources and sharding rules for pamirsSharding:
    • pamirs.sharding.define is used for creating Oinone database tables.
    • pamirs.sharding.rule is used to configure sharding rules.
  • Properly configure data sources and sharding rules for pamirsSharding.

（Ⅰ）Specify Data Source for Model

pamirs:
  framework:
    system:
      system-ds-key: base
      system-models:
        - base.WorkerNode
    data:
      default-ds-key: pamirs
      ds-map:
        base: base
      modelDsMap:
        "[demo.ShardingModel]": pamirsSharding  # Configure the library corresponding to the model

（Ⅱ）Sharding Rule Configuration

pamirs:
  sharding:
    define:
      data-sources:
        ds: pamirs
        pamirsSharding: pamirs # Declare that the pamirsSharding library corresponds to the pamirs data source
      models:
        "[trigger.PamirsSchedule]":
          tables: 0..13
        "[demo.ShardingModel]":
          tables: 0..7
          table-separator: _
    rule:
      pamirsSharding: # Configure sharding rules for the pamirsSharding library
        actual-ds:
          - pamirs  # Declare that the pamirsSharding library corresponds to the pamirs data source
        sharding-rules:
          # Configure sharding rules, the following configuration is consistent with Sharding-JDBC configuration
          - tables:
              demo_core_sharding_model: # Configuration for demo_core_sharding_model table rules
                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

Note: For more YAML configurations, please refer to Module API.

Ⅲ、Custom Rules

  • Default rules refer to the commonly used database and table sharding strategies, such as sharding by data volume or hash algorithm. In general cases, default rules are sufficient to meet business needs.
  • However, in some complex business scenarios, default rules may fail to meet actual requirements, and custom settings are required based on specific circumstances. For example, some businesses may have specific data distribution patterns or unique query characteristics, which require customized sharding rules to optimize data access performance or meet specific business needs. In such cases, using custom rules can more effectively adapt to business requirements.

（Ⅰ）Custom Sharding Rule Examples

1、Table Sharding by Month (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("No matching data table found");
    }

    @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、Table Sharding by Modulo Based on Specific Field Extraction

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("No matching data table found");
    }

    @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() {

    }
}

Ⅳ、Using Custom Sharding Strategies

（Ⅰ）Specify Data Source for Model

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

（Ⅱ）Sharding Rule Configuration

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

Note: For more YAML configurations, please refer to Module API.

（Ⅲ）Configure Custom Rule SPI

Configure org.apache.shardingsphere.sharding.spi.ShardingAlgorithm under the resources/META-INF/services path.

pro.shushi.pamirs.demo.core.sharding.AppUserCodeShardingAlgorithm
pro.shushi.pamirs.demo.core.sharding.DateMonthShardingAlgorithm
默认页脚
Copyright © 2025 Mr.Hope