6、个股表分库分表配置

敲代码的翠花

已于 2024-01-27 01:49:50 修改

阅读量159

点赞数 9

分类专栏： # (十)分库分表项目集成和安全框架项目：【今日指数金融】文章标签： java

于 2024-01-27 01:07:02 首次发布

本文链接：https://blog.csdn.net/weixin_44478828/article/details/135877500

版权

项目：【今日指数金融】同时被 2 个专栏收录

70 篇文章 3 订阅

订阅专栏

(十)分库分表项目集成和安全框架

10 篇文章 0 订阅

订阅专栏

本文介绍了如何在个股流水表中使用ApacheShardingSphere的分库分表策略，定义了精确匹配和范围匹配的分表算法，以及如何在stock_backend工程中配置分库分表策略，包括数据库和表级别的分片配置，以及注意事项，如避免使用函数处理分片字段和查询优化建议。

摘要由CSDN通过智能技术生成

对于个股流水表来说分库策略与大盘板块一致，所以接下来，我们只定义好分表策略即可。

6.1 定义个股表公共分表策略

在stock_common工程下将精准和范围匹配表的接口实现合并到一个算法类下：

package com.itheima.stock.sharding;

import com.google.common.collect.Range;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;
import org.apache.shardingsphere.api.sharding.standard.RangeShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.RangeShardingValue;
import org.joda.time.DateTime;
import org.joda.time.format.DateTimeFormat;

import java.util.Collection;
import java.util.Date;
import java.util.Optional;
import java.util.stream.Collectors;

/**
 * @author by itheima
 * @Date 2022/6/13
 * @Description 定义股票流水表的分片算法类：包含精准匹配表和范围匹配表
 *  因为分库是根据日期分库的，一年一个库，一个月一张表，也就是说每个库内都包含12张表，所以片键的类型是Date
 */
public class ShardingAlgorithm4StockRtInfoTable implements PreciseShardingAlgorithm<Date>, RangeShardingAlgorithm<Date> {

    /**
     * 精准匹配表的方法 cur_time 条件必须是 = 或者in
     * @param tbNames 所有可匹配表的集合 stock_rt_info_202101....stock_rt_info_202112
     *                                stock_rt_info_202201....stock_rt_info_202212
     * @param shardingValue
     * @return
     */
    @Override
    public String doSharding(Collection<String> tbNames, PreciseShardingValue<Date> shardingValue) {
        //1.思路：根据传入的日期值，获取年份字符串
        //获取分片字段的名称colume
//        String columnName = shardingValue.getColumnName();
        //获取逻辑表名称
//        String logicTableName = shardingValue.getLogicTableName();
        //获取分片值
        Date value = shardingValue.getValue();
        //获取年月组成的字符串
        String yearMonth = new DateTime(value).toString(DateTimeFormat.forPattern("yyyyMM"));
        //过滤表的名称集合，获取名称后缀与yearMonth一致的表名称
        Optional<String> optional = tbNames.stream().filter(tbName -> tbName.endsWith(yearMonth)).findFirst();
        String tbName=null;
        if (optional.isPresent()) {
            tbName=optional.get();
        }
        return tbName;
    }

    /**
     * 范围查询匹配表 关键字：between and
     * @param tbNames 所有可匹配表的集合 stock_rt_info_202101....stock_rt_info_202112
     *                                stock_rt_info_202201....stock_rt_info_202212
     * @param shardingValue
     * @return
     */
    @Override
    public Collection<String> doSharding(Collection<String> tbNames, RangeShardingValue<Date> shardingValue) {
        //获取分片字段名称
//        String columnName = shardingValue.getColumnName();
//        //获取逻辑表名称
//        String logicTableName = shardingValue.getLogicTableName();
        //1.获取范围封装对象
        Range<Date> valueRange = shardingValue.getValueRange();
        //2.1 判断是否有下限值
        if (valueRange.hasLowerBound()) {
            //获取下限日期
            Date lowerDate = valueRange.lowerEndpoint();
            //获取年份  dsNames--> ds_2021 ds_2022 ds_2023
            //获取年月组成的字符串
            String yearMonth = new DateTime(lowerDate).toString(DateTimeFormat.forPattern("yyyyMM"));
            Integer yearM = Integer.valueOf(yearMonth);
            tbNames= tbNames.stream().filter(tbName->Integer.valueOf(tbName.substring(tbName.lastIndexOf("_")+1))>=yearM)
                    .collect(Collectors.toList());
        }
        //2.2 判断是否有上限值
        if (valueRange.hasUpperBound()) {
            Date upperDate = valueRange.upperEndpoint();
            String yearMonth = new DateTime(upperDate).toString(DateTimeFormat.forPattern("yyyyMM"));
            Integer yearM = Integer.valueOf(yearMonth);
            tbNames= tbNames.stream().filter(tbName->Integer.valueOf(tbName.substring(tbName.lastIndexOf("_")+1))<=yearM)
                    .collect(Collectors.toList());
        }
        return tbNames;
    }
}

6.2 配置个股分库分表

在stock_backend工程下配置分库分表策略：

# 配置股票流水节点信息
spring.shardingsphere.sharding.tables.stock_rt_info.actual-data-nodes=ds-2021.stock_rt_info_${202101..202112},ds-2022.stock_rt_info_${202201..202212}
# 抽取公共配置类变量
common.algorithm4StockRtInfoTable=com.itheima.stock.sharding.ShardingAlgorithm4StockRtInfoTable
# 配置股票流水库分片策略
spring.shardingsphere.sharding.tables.stock_rt_info.database-strategy.standard.sharding-column=cur_time
# 精确分片算法类名称，用于 = 和 IN。该类需实现 PreciseShardingAlgorithm 接口并提供无参数的构造器
spring.shardingsphere.sharding.tables.stock_rt_info.database-strategy.standard.precise-algorithm-class-name=${common.algorithm4db}
# 范围分片算法类名称，用于 BETWEEN，可选。该类需实现 RangeShardingAlgorithm 接口并提供无参数的构造器
spring.shardingsphere.sharding.tables.stock_rt_info.database-strategy.standard.range-algorithm-class-name=${common.algorithm4db}

# 第四步：配置表的分片算法
# 因为stock_block_rt_info板块表仅仅按照年分库，并没有库内分片的操作，也就是说每个库内的表名称都一样，且只有一个，所以不需要定义分表的算法类
# 配置股票流水表的分片算法
spring.shardingsphere.sharding.tables.stock_rt_info.table-strategy.standard.sharding-column=cur_time
# 精确分片算法类名称，用于 = 和 IN。该类需实现 PreciseShardingAlgorithm 接口并提供无参数的构造器
spring.shardingsphere.sharding.tables.stock_rt_info.table-strategy.standard.precise-algorithm-class-name=${common.algorithm4StockRtInfoTable}
# 范围分片算法类名称，用于 BETWEEN，可选。该类需实现 RangeShardingAlgorithm 接口并提供无参数的构造器
spring.shardingsphere.sharding.tables.stock_rt_info.table-strategy.standard.range-algorithm-class-name=${common.algorithm4StockRtInfoTable}

6.3测试

    @Autowired
    private StockRtInfoMapper stockRtInfoMapper;

    /**
     * @Description 测试分库分表算法类
     */
    @Test
    public void testShardingDbAndTb(){
        //截止时间
        Date endTime=DateTime.parse("2022-05-22 09:30:00",DateTimeFormat.forPattern("yyyy-MM-dd HH:mm:ss")).toDate();
        //开始时间
        Date startTime=DateTime.parse("2021-01-01 09:30:00",DateTimeFormat.forPattern("yyyy-MM-dd HH:mm:ss")).toDate();
        //根据指定日期范围查询周K线数据
        List<Stock4EvrWeekDomain> infos=stockRtInfoMapper.getHalfWeekLineData("000017",startTime,endTime);
        System.out.println(infos);
    }

最终application-shrding.properties配置：

# 第一步：配置默认数据源
spring.shardingsphere.datasource.names=def,ds-2021,ds-2022
# 数据库连接池类名称
spring.shardingsphere.datasource.def.type=com.alibaba.druid.pool.DruidDataSource
# 数据库驱动类名
spring.shardingsphere.datasource.def.driver-class-name=com.mysql.jdbc.Driver
# 数据库 url 连接
spring.shardingsphere.datasource.def.url=jdbc:mysql://192.168.200.132:3306/stock_sys_db?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false&serverTimezone=Asia/Shanghai
# 数据库用户名
spring.shardingsphere.datasource.def.username=root
# 数据库密码
spring.shardingsphere.datasource.def.password=root

# 数据库连接池类名称
spring.shardingsphere.datasource.ds-2021.type=com.alibaba.druid.pool.DruidDataSource
# 数据库驱动类名
spring.shardingsphere.datasource.ds-2021.driver-class-name=com.mysql.jdbc.Driver
# 数据库 url 连接
spring.shardingsphere.datasource.ds-2021.url=jdbc:mysql://192.168.200.130:3306/stock_db_2021?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false&serverTimezone=Asia/Shanghai
# 数据库用户名
spring.shardingsphere.datasource.ds-2021.username=root
# 数据库密码
spring.shardingsphere.datasource.ds-2021.password=root

# 数据库连接池类名称
spring.shardingsphere.datasource.ds-2022.type=com.alibaba.druid.pool.DruidDataSource
# 数据库驱动类名
spring.shardingsphere.datasource.ds-2022.driver-class-name=com.mysql.jdbc.Driver
# 数据库 url 连接
spring.shardingsphere.datasource.ds-2022.url=jdbc:mysql://192.168.200.131:3306/stock_db_2022?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false&serverTimezone=Asia/Shanghai
# 数据库用户名
spring.shardingsphere.datasource.ds-2022.username=root
# 数据库密码
spring.shardingsphere.datasource.ds-2022.password=root

# 第二步：配置板块表的数据节点信息
spring.shardingsphere.sharding.tables.stock_block_rt_info.actual-data-nodes=ds-${2021..2022}.stock_block_rt_info
spring.shardingsphere.sharding.tables.stock_market_index_info.actual-data-nodes=ds-${2021..2022}.stock_market_index_info
spring.shardingsphere.sharding.tables.stock_outer_market_index_info.actual-data-nodes=ds-${2021..2022}.stock_outer_market_index_info
# 配置股票流水节点信息
spring.shardingsphere.sharding.tables.stock_rt_info.actual-data-nodes=ds-2021.stock_rt_info_${202101..202112},ds-2022.stock_rt_info_${202201..202212}

# 提取公共数据库分片算法配置类
common.algorithm4db=com.itheima.stock.sharding.CommonShardingAlgorithm4Db
common.algorithm4StockRtInfoTable=com.itheima.stock.sharding.ShardingAlgorithm4StockRtInfoTable

# 第三步：配置数据库的分片算法
# 分片列名称
spring.shardingsphere.sharding.tables.stock_block_rt_info.database-strategy.standard.sharding-column=cur_time
# 精确分片算法类名称，用于 = 和 IN。该类需实现 PreciseShardingAlgorithm 接口并提供无参数的构造器
spring.shardingsphere.sharding.tables.stock_block_rt_info.database-strategy.standard.precise-algorithm-class-name=${common.algorithm4db}
# 范围分片算法类名称，用于 BETWEEN，可选。该类需实现 RangeShardingAlgorithm 接口并提供无参数的构造器
spring.shardingsphere.sharding.tables.stock_block_rt_info.database-strategy.standard.range-algorithm-class-name=${common.algorithm4db}

spring.shardingsphere.sharding.tables.stock_market_index_info.database-strategy.standard.sharding-column=cur_time
# 精确分片算法类名称，用于 = 和 IN。该类需实现 PreciseShardingAlgorithm 接口并提供无参数的构造器
spring.shardingsphere.sharding.tables.stock_market_index_info.database-strategy.standard.precise-algorithm-class-name=${common.algorithm4db}
# 范围分片算法类名称，用于 BETWEEN，可选。该类需实现 RangeShardingAlgorithm 接口并提供无参数的构造器
spring.shardingsphere.sharding.tables.stock_market_index_info.database-strategy.standard.range-algorithm-class-name=${common.algorithm4db}

spring.shardingsphere.sharding.tables.stock_outer_market_index_info.database-strategy.standard.sharding-column=cur_time
# 精确分片算法类名称，用于 = 和 IN。该类需实现 PreciseShardingAlgorithm 接口并提供无参数的构造器
spring.shardingsphere.sharding.tables.stock_outer_market_index_info.database-strategy.standard.precise-algorithm-class-name=${common.algorithm4db}
# 范围分片算法类名称，用于 BETWEEN，可选。该类需实现 RangeShardingAlgorithm 接口并提供无参数的构造器
spring.shardingsphere.sharding.tables.stock_outer_market_index_info.database-strategy.standard.range-algorithm-class-name=${common.algorithm4db}

spring.shardingsphere.sharding.tables.stock_rt_info.database-strategy.standard.sharding-column=cur_time
# 精确分片算法类名称，用于 = 和 IN。该类需实现 PreciseShardingAlgorithm 接口并提供无参数的构造器
spring.shardingsphere.sharding.tables.stock_rt_info.database-strategy.standard.precise-algorithm-class-name=${common.algorithm4db}
# 范围分片算法类名称，用于 BETWEEN，可选。该类需实现 RangeShardingAlgorithm 接口并提供无参数的构造器
spring.shardingsphere.sharding.tables.stock_rt_info.database-strategy.standard.range-algorithm-class-name=${common.algorithm4db}

# 第四步：配置表的分片算法
# 因为stock_block_rt_info板块表仅仅按照年分库，并没有库内分片的操作，也就是说每个库内的表名称都一样，且只有一个，所以不需要定义分表的算法类
# 配置股票流水表的分片算法
spring.shardingsphere.sharding.tables.stock_rt_info.table-strategy.standard.sharding-column=cur_time
# 精确分片算法类名称，用于 = 和 IN。该类需实现 PreciseShardingAlgorithm 接口并提供无参数的构造器
spring.shardingsphere.sharding.tables.stock_rt_info.table-strategy.standard.precise-algorithm-class-name=${common.algorithm4StockRtInfoTable}
# 范围分片算法类名称，用于 BETWEEN，可选。该类需实现 RangeShardingAlgorithm 接口并提供无参数的构造器
spring.shardingsphere.sharding.tables.stock_rt_info.table-strategy.standard.range-algorithm-class-name=${common.algorithm4StockRtInfoTable}

# 指定默认数据源
# 未配置分片规则的表将通过默认数据源定位
spring.shardingsphere.sharding.default-data-source-name=def
# 配置广播表
spring.shardingsphere.sharding.broadcast-tables=stock_business

# 是否开启 SQL 显示，默认值: false
spring.shardingsphere.props.sql.show=true

7、股票任务采集工程集成

对于stock_job工程，同样升级到分库分表的环境，只需将stock_backend的配置复制一份即可；

（略）

8、分库分表注意事项

基于sharding-jdbc实践分库分表注意事项：

条件查询时分片字段不要使用函数处理，否则分片算法失效，导致全节点查询
- 举例：select * from stock_rt_info where date_format(cur_time,‘%Y%m%d’)='20220910' ,函数会造成sharding的分片失效，导致全节点查询；
- 同时在索引角度看，如果查询的分片字段使用函数，会导致索引失效，导致查询性能较低；
条件查询时尽量使用符合sharding分片条件的关键字
- 精准查询尽量使用in =,而范围查询尽量使用between ；
sharding-jdbc对嵌套查询处理不友好
- 如果嵌套查询的话，那么最好子查询的条件只命中单张表。如果子查询的条件关联了多张表，那么交易分步骤拆分实现；
- 示例：我们项目中的K线统计中，需要将SQL拆分，然后分步骤实现；