JPA单库分表

背景

接口调用日志，每一次调用都会有一条日志记录，每天能有30万条记录入库，一个月900万记录，数据不断增多,单表数量达到五百W以上,查询新增速度不断变慢,分表势在必行

拦截器分表

创建一个拦截器继承EmptyInterceptor，重写onPrepareStatement方法，在执行sql前动态替换表名

@Slf4j
public class AutoTableNameInterceptor extends EmptyInterceptor{
    public static String LOGIC_TABLE = "t_api_usage_log"; //单库分表逻辑表名
    private static String yearAndMonth = "yyyyMM";
    @Override
    public String onPrepareStatement(String sql) {
        if(sql.contains(Constant.LOGIC_TABLE)){
            sql = sql.replaceAll(LOGIC_TABLE, getTableName(LOGIC_TABLE, new Date()));
            log.info("sql={}", sql);
        }
        return sql;
    }
    
    /**
 * 根据日期获取表名
 * @param prefix
 * @param createDate
 * @return
 */
public static String getTableName(String prefix, Date createDate){
    return prefix + "_" + DateUtil.format(createDate, yearAndMonth);
}
}

application.yml上增加拦截器配置session_scoped_interceptor

spring:
  jpa:
    open_in_view: false
    show_sql: true
    properties:
      hibernate:
        dialect: org.hibernate.dialect.MySQL5Dialect
        enable_lazy_load_no_trans: true
        session_factory:
          session_scoped_interceptor: "com.bill99.walrus.sharecontext.config.datasource.AutoTableNameInterceptor"

拦截器生效日志，表名由t_api_usage_log替换成了t_api_usage_log_202303

# Sharding-JDBC单库分表

引入Sharding-JDBC核心包

<!-- sharding-jdbc -->
<dependency>
    <groupId>com.dangdang</groupId>
    <artifactId>sharding-jdbc-core</artifactId>
    <version>1.5.4.1</version>
</dependency>

配置数据源，我选择的是创建时间来作为分表字段

```
@Configuration
public class DynamicDataSources {

    @Resource
    private TableShardingAlgorithm tableShardingAlgorithm;

    @Bean
    public DataSource dataSource() throws SQLException {
        return buildDataSource();
    }

    private DataSource buildDataSource() throws SQLException {
        //分库设置
        Map<String, DataSource> dataSourceMap = new HashMap<>(1);
        DataSource sail = new DynamicDataSource(Constant.DATASOURCE_SAIL);
        dataSourceMap.put(Constant.DATASOURCE_SAIL, sail);
        //设置默认数据库
        DataSourceRule dataSourceRule = new DataSourceRule(dataSourceMap, Constant.DATASOURCE_SAIL);
        //分表设置，大致思想就是将查询虚拟表LOGIC_TABLE根据一定规则映射到真实表中去
        TableRule orderTableRule = TableRule.builder(Constant.LOGIC_TABLE)
                .dynamic(true)   //真实表不指定，查询时必须带分表字段，否则报错
                //.actualTables(TableNameUtil.getList(Constant.LOGIC_TABLE)) // 真实表指定，查询时可不带分表字段，会全量分表查询
                .dataSourceRule(dataSourceRule)
                .build();

        //分库分表策略
        ShardingRule shardingRule = ShardingRule.builder()
                .dataSourceRule(dataSourceRule)
                .tableRules(Arrays.asList(orderTableRule))
                .tableShardingStrategy(new TableShardingStrategy("create_date", tableShardingAlgorithm)).build();
        DataSource dataSource = ShardingDataSourceFactory.createDataSource(shardingRule);
        return dataSource;
    }
}
```

编写单键分片策略，实现SingleKeyTableShardingAlgorithm

@Component
@Slf4j
public class TableShardingAlgorithm implements SingleKeyTableShardingAlgorithm<Date> {
    private static String yearAndMonth = "yyyyMM";
    /**
     * SQL中==的规则
     * @param tableNames
     * @param shardingValue
     * @return
     */
    @Override
    public String doEqualSharding(final Collection<String> tableNames, ShardingValue<Date> shardingValue) {
        log.info("shardingValue={}",shardingValue);
        return getTableName(shardingValue.getLogicTableName(), shardingValue.getValue());
    }

    /**
     * SQL中in的规则
     * @param tableNames
     * @param shardingValue
     * @return
     */
    @Override
    public Collection<String> doInSharding(final Collection<String> tableNames, ShardingValue<Date> shardingValue) {
        for (Long value : shardingValue.getValues()) { 
            result.add(getTableName(shardingValue.getLogicTableName(), value)); 
        }
        return result; 
}
    /**
     * SQL中between的规则
     * @param tableNames
     * @param shardingValue
     * @return
     */
    @Override
    public Collection<String> doBetweenSharding(final Collection<String> tableNames,
                                                final ShardingValue<Date> shardingValue) {
        log.info("shardingValue={}", shardingValue);
        Collection<String> result = new LinkedHashSet<>();
        Range<Date> range = shardingValue.getValueRange();
        int start = TableNameUtil.getDateInt(range.lowerEndpoint());
        int end = TableNameUtil.getDateInt(range.upperEndpoint());
        for (int i = start; i <= end; i++) {
            result.add(shardingValue.getLogicTableName() + "_" + i);
        }
        log.info("result useTable={}", result);
        return result;
    }
    
        /**
     * 根据日期获取表名
     * @param prefix
     * @param createDate
     * @return
     */
    public static String getTableName(String prefix, Date createDate){
        return prefix + "_" + DateUtil.format(createDate, yearAndMonth);
    }
}