数据量大了一定要分表，分库分表Sharding-JDBC入门与项目实战

SELECT i.* FROM t_order o JOIN t_order_item i ON o.order_id=i.order_id WHERE o.order_id in (10, 11);

SELECT i.* FROM t_order_0 o JOIN t_order_item_0 i ON o.order_id=i.order_id WHERE o.order_id in (10, 11);
SELECT i.* FROM t_order_0 o JOIN t_order_item_1 i ON o.order_id=i.order_id WHERE o.order_id in (10, 11);
SELECT i.* FROM t_order_1 o JOIN t_order_item_0 i ON o.order_id=i.order_id WHERE o.order_id in (10, 11);
SELECT i.* FROM t_order_1 o JOIN t_order_item_1 i ON o.order_id=i.order_id WHERE o.order_id in (10, 11);

SELECT i.* FROM t_order_0 o JOIN t_order_item_0 i ON o.order_id=i.order_id WHERE o.order_id in (10, 11);
SELECT i.* FROM t_order_1 o JOIN t_order_item_1 i ON o.order_id=i.order_id WHERE o.order_id in (10, 11);

<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
    <version>4.0.1</version>
</dependency>

spring.shardingsphere.datasource.names=ds0,ds1

spring.shardingsphere.datasource.ds0.type=org.apache.commons.dbcp.BasicDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds0.url=jdbc:mysql://localhost:3306/ds0
spring.shardingsphere.datasource.ds0.username=root
spring.shardingsphere.datasource.ds0.password=

spring.shardingsphere.datasource.ds1.type=org.apache.commons.dbcp.BasicDataSource
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds1.url=jdbc:mysql://localhost:3306/ds1
spring.shardingsphere.datasource.ds1.username=root
spring.shardingsphere.datasource.ds1.password=

# 其它分片配置

@Configuration
@Slf4j
@EnableConfigurationProperties({
        SpringBootShardingRuleConfigurationProperties.class,
        SpringBootMasterSlaveRuleConfigurationProperties.class, SpringBootEncryptRuleConfigurationProperties.class, SpringBootPropertiesConfigurationProperties.class})
@AutoConfigureBefore(DataSourceConfiguration.class)
public class DataSourceConfig implements ApplicationContextAware {

    @Autowired
    private SpringBootShardingRuleConfigurationProperties shardingRule;

    @Autowired
    private SpringBootPropertiesConfigurationProperties props;

    private ApplicationContext applicationContext;

    @Bean("shardingDataSource")
    @Conditional(ShardingRuleCondition.class)
    public DataSource shardingDataSource() throws SQLException {
        // 获取其它方式配置的数据源
        Map<String, DruidDataSourceWrapper> beans = applicationContext.getBeansOfType(DruidDataSourceWrapper.class);
        Map<String, DataSource> dataSourceMap = new HashMap<>(4);
        beans.forEach(dataSourceMap::put);
        // 创建shardingDataSource
        return ShardingDataSourceFactory.createDataSource(dataSourceMap, new ShardingRuleConfigurationYamlSwapper().swap(shardingRule), props.getProps());
    }

    @Bean
    public SqlSessionFactory sqlSessionFactory() throws SQLException {
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        // 将shardingDataSource设置到SqlSessionFactory中
        sqlSessionFactoryBean.setDataSource(shardingDataSource());
        // 其它设置
        return sqlSessionFactoryBean.getObject();
    }
}

@Data
public class SeqShardingKeyGenerator implements ShardingKeyGenerator {

    private Properties properties = new Properties();

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

    @Override
    public synchronized Comparable<?> generateKey() {
       // 获取分布式id逻辑
    }
}

CREATE TABLE `hc_question_reply_record` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '自增ID',
  `reply_text` varchar(500) NOT NULL DEFAULT '' COMMENT '回复内容',
  `reply_wheel_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '回复时间',

  `ctime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `mtime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  INDEX `idx_reply_wheel_time` (`reply_wheel_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='回复明细记录';

SELECT count(*), date_format(ctime, '%Y-%m') AS `日期`
FROM hc_question_reply_record
GROUP BY date_format(ctime, '%Y-%m');

spring:
  # sharing-jdbc配置
  shardingsphere:
    # 数据源名称
    datasource:
      names: defaultDataSource,slaveDataSource
    sharding:
      # 主从节点配置
      master-slave-rules:
        defaultDataSource:
          # maser数据源
          master-data-source-name: defaultDataSource
          # slave数据源
          slave-data-source-names: slaveDataSource
      tables:
        # hc_question_reply_record 分库分表配置
        hc_question_reply_record:
          # 真实数据节点  hc_question_reply_record_2020_q1
          actual-data-nodes: defaultDataSource.hc_question_reply_record_$->{2020..2025}_q$->{1..4}
          # 表分片策略
          table-strategy:
            standard:
              # 分片键
              sharding-column: reply_wheel_time
              # 精确分片算法 全路径名
              preciseAlgorithmClassName: com.xx.QuestionRecordPreciseShardingAlgorithm
              # 范围分片算法，用于BETWEEN，可选。。该类需实现RangeShardingAlgorithm接口并提供无参数的构造器
              rangeAlgorithmClassName: com.xx.QuestionRecordRangeShardingAlgorithm

      # 默认分布式id生成器
      default-key-generator:
        type: SEQ
        column: id

public class QuestionRecordPreciseShardingAlgorithm implements PreciseShardingAlgorithm<Date> {
  /**
   * Sharding.
   *
   * @param availableTargetNames available data sources or tables's names
   * @param shardingValue        sharding value
   * @return sharding result for data source or table's name
   */
  @Override
  public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Date> shardingValue) {
      return ShardingUtils.quarterPreciseSharding(availableTargetNames, shardingValue);
  }
}

public class QuestionRecordRangeShardingAlgorithm implements RangeShardingAlgorithm<Date> {

  /**
   * Sharding.
   *
   * @param availableTargetNames available data sources or tables's names
   * @param shardingValue        sharding value
   * @return sharding results for data sources or tables's names
   */
  @Override
  public Collection<String> doSharding(Collection<String> availableTargetNames, RangeShardingValue<Date> shardingValue) {
      return ShardingUtils.quarterRangeSharding(availableTargetNames, shardingValue);
  }
}

@UtilityClass
public class ShardingUtils {
    public static final String QUARTER_SHARDING_PATTERN = "%s_%d_q%d";

    /**
    * logicTableName_{year}_q{quarter}
    * 按季度范围分片
    * @param availableTargetNames 可用的真实表集合
    * @param shardingValue 分片值
    * @return
    */
    public Collection<String> quarterRangeSharding(Collection<String> availableTargetNames, RangeShardingValue<Date> shardingValue) {
        // 这里就是根据范围查询条件，筛选出匹配的真实表集合
    }

    /**
    * logicTableName_{year}_q{quarter}
    * 按季度精确分片
    * @param availableTargetNames 可用的真实表集合
    * @param shardingValue 分片值
    * @return
    */
    public static String quarterPreciseSharding(Collection<String> availableTargetNames, PreciseShardingValue<Date> shardingValue) {
        // 这里就是根据等值查询条件，计算出匹配的真实表
    }
}