当前位置： 首页 > news >正文

项目集成sharding-jdbc

news 2024/11/14 1:23:27

目录

项目集成sharding-jdbc

1.业务分析

 2.数据库构建

3.分库分表策略

 项目配置默认数据源

一：导入sharding-jdbc依赖

 二：在application文件中编写配置

三：注释掉主配置文件中配置的数据源

注意：这里添加了spring.main.allow-bean-definition-overriding: true

四：测试

项目配置广播表（公共表）

一:application配置

二：测试

项目使用standard标准模式配置分库算法

一.分库算法类

 二.application中配置逻辑表对应的数据节点和分库算法

项目使用stardard标准模式配置分表算法

一.分表算法类

二.application配置

分库分表注意事项

项目集成sharding-jdbc

1.业务分析

我1们分析一下股票数据的预期增长情况：

表名时间周期累计数量分库分表策略
股票流水表-stock_rt_info月1(钟)x60（时）x4（天）x21(月)x1500（重点股票）约等于：750W+按年分库，按月分表
股票主营业务表-stock_business3000+公共表|广播表数据量少
数据变化频率低
各个数据库都会用到
国内大盘流水表-stock_market_index_info年1x60x4x21x12x10约等于：60W+按年分库不分表方便数据按年维护
外盘流水表-stock_outer_market_index_info年1x60x4x21x12x10约等于：60W+按年分库不分表方便数据按年维护
股票板块-stock_block_rt_into年1x60x4x21x12x60约等于：360w+按年分库不分表方便数据按年维护
系统表 -sys_log、sys_user、sys_role等数据量少单库默认数据源

当前我们选择使用cur_time日期字段作为分库分表的片键比较合适，那如果使用主键字段作为分片，会存在哪些问题呢？

  • 数据库扩容时各节点存储均衡问题
    • 股票数据的持续流入会导致前期分库的各个节点不堪重负，最终势必要进行节点扩容，而新加入的节点和旧的节点之间数据不平衡，需要重新规划，这会导致数据迁移的成本过高；
  • 股票查询条件问题
    • 股票数据多以日期作为条件查询，如果基于主键ID作为分片键，则会导致分库的全节点查询，性能开销加大；

 2.数据库构建

  • 对于股票流水表按照月维度和年维护进行库表拆分，也就是说一年会产生一个库用于后期数据归档，而每个库下则按照月份产生12张表，对应一年的数据；
  • 对于板块表和大盘数据表，我们则以年为单位，与股票流水表年份一致即可，也就是按照年分库分表；
  • 对于主营业务表，因为数据量较少，且查询都会用到，作为公共表处理；
  • 对于系统表数据量相对较少，作为默认数据源即可；

 

3.分库分表策略

经过分析发现大盘、板块、股票相关数据的分库策略是一致的，而分表策略则存在部分差异，所以我们可先定义公共的分库算法类和公共的分表算法类，对于不一致的，则个别定义即可：

表公共分库算法公共分表算法说明
stock_block_rt_inf是无
stock_market_index_info是无
stock_outer_market_index_info是无
stock_rt_info是否根据月份分表
stock_business否否公共表|广播表
系统管理相关表:sys_user等否否默认数据源

 项目配置默认数据源

说明:配置默认数据源之后，如果某个逻辑表没有对应的数据节点，就会去默认数据源下去寻找是否与自己同名的物理表，如果有就去操作默认数据源的那张表，如果没有就会报错（空数据源即没有可操作的数据源）

一：导入sharding-jdbc依赖

 <!--引入shardingjdbc依赖--><dependency><groupId>org.apache.shardingsphere</groupId><artifactId>sharding-jdbc-spring-boot-starter</artifactId></dependency>

 二：在application文件中编写配置

application-shard.properties

# 分表配置
# 数据源名称，多数据源以逗号分隔,一个数据库对应一个数据源
spring.shardingsphere.datasource.names=defdb
#让数据源连接指定的数据库
# 数据库连接池类名称
spring.shardingsphere.datasource.defdb.type=com.alibaba.druid.pool.DruidDataSource
# 数据库驱动类名
spring.shardingsphere.datasource.defdb.driver-class-name=com.mysql.jdbc.Driver
# 数据库 url 连接
spring.shardingsphere.datasource.defdb.url=jdbc:mysql://192.168.200.130:3306/stock_sys_db?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false&serverTimezone=Asia/Shanghai
# 数据库用户名
spring.shardingsphere.datasource.defdb.username=root
# 数据库密码
spring.shardingsphere.datasource.defdb.password=1234# 配置默认数据源（特点：对于不做分片处理的操作，都会直接访问默认数据源
# 未配置分片规则的表将通过默认数据源定位
spring.shardingsphere.sharding.default-data-source-name=defdb#开启sql显示到终端
spring.shardingsphere.props.sql.show=true

三：注释掉主配置文件中配置的数据源

# web定义
server:port: 8091
spring:profiles:active: cache,stock,mq,shard #激活其他配置文件main:allow-bean-definition-overriding: true # 配置允许容器内的bean资源被覆盖，druid的依赖会自动装配数据源，sharding也会装配数据源，# 所以要开启覆盖bean资源，让sharding配置的数据源覆盖掉druid的数据源# 配置mysql数据源
#  datasource:
#    druid:
#      username: root
#      password: 1234
#      url: jdbc:mysql://192.168.200.130:3306/stock_db?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false&serverTimezone=Asia/Shanghai
#      driver-class-name: com.mysql.jdbc.Driver
#      # 初始化时建立物理连接的个数。初始化发生在显示调用 init 方法，或者第一次 getConnection 时
#      initialSize: 6
#      # 最小连接池数量
#      minIdle: 2
#      # 最大连接池数量
#      maxActive: 20
#      # 获取连接时最大等待时间，单位毫秒。配置了 maxWait 之后，缺省启用公平锁，
#      # 并发效率会有所下降，如果需要可以通过配置 useUnfairLock 属性为 true 使用非公平锁。
#      maxWait: 60000
# 配置mybatis
mybatis:type-aliases-package: com.hhh.stock.pojo.entity  #批量给实体类取别名，方便在xml文件中使用别名mapper-locations: classpath:mapper/*.xml  #配置加载mapperXml文件资源configuration:map-underscore-to-camel-case: true # 开启驼峰映射
#    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl #通过mybatis执行的sql以日志文件输出到终端cache-enabled: false #禁止二级缓存 caffiencachelocal-cache-scope: statement # 以及缓存默认开启session
# pagehelper配置
pagehelper:helper-dialect: mysql #指定分页数据库类型（方言）reasonable: true #合理查询超过最大页，则查询最后一页

注意：这里添加了spring.main.allow-bean-definition-overriding: true

如果不添加这个语句，启动项目时就会报错，因为下面两个依赖都会自动装配一个druid数据源，导致springboot不知道要使用哪一个数据源，所以添加spring.main.allow-bean-definition-overriding: true，配置允许容器内的bean资源被覆盖即可，这样就可以去使用sharding-jdbc的数据源了

 <dependency><groupId>com.alibaba</groupId><artifactId>druid-spring-boot-starter</artifactId></dependency>
 <!--引入shardingjdbc依赖--><dependency><groupId>org.apache.shardingsphere</groupId><artifactId>sharding-jdbc-spring-boot-starter</artifactId></dependency>

四：测试

    @Autowiredprivate SysUserMapper sysUserMapper;/*** @Description 测试默认数据源的配置*/@Testpublic void testDefault(){SysUser user = sysUserMapper.selectByPrimaryKey(1237361915165020161l);System.out.println(user);}

可以发现使用了默认数据源defdb

5,23 

项目配置广播表（公共表）

前提：每个 库中都要有这张广播表，这样在java程序中对逻辑表操作时，就会操作每个库的这张广播表

一:application配置

# 分表配置
# 数据源名称，多数据源以逗号分隔,一个数据库对应一个数据源
spring.shardingsphere.datasource.names=defdb,ds-2022,ds-2023,ds-2024
#让数据源连接指定的数据库
# 数据库连接池类名称
spring.shardingsphere.datasource.defdb.type=com.alibaba.druid.pool.DruidDataSource
# 数据库驱动类名
spring.shardingsphere.datasource.defdb.driver-class-name=com.mysql.jdbc.Driver
# 数据库 url 连接
spring.shardingsphere.datasource.defdb.url=jdbc:mysql://192.168.200.130:3306/stock_sys_db?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false&serverTimezone=Asia/Shanghai
# 数据库用户名
spring.shardingsphere.datasource.defdb.username=root
# 数据库密码
spring.shardingsphere.datasource.defdb.password=1234#让数据源连接指定的数据库
# 数据库连接池类名称
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.130: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=1234
#让数据源连接指定的数据库
# 数据库连接池类名称
spring.shardingsphere.datasource.ds-2023.type=com.alibaba.druid.pool.DruidDataSource
# 数据库驱动类名
spring.shardingsphere.datasource.ds-2023.driver-class-name=com.mysql.jdbc.Driver
# 数据库 url 连接
spring.shardingsphere.datasource.ds-2023.url=jdbc:mysql://192.168.200.130:3306/stock_db_2023?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false&serverTimezone=Asia/Shanghai
# 数据库用户名
spring.shardingsphere.datasource.ds-2023.username=root
# 数据库密码
spring.shardingsphere.datasource.ds-2023.password=1234#让数据源连接指定的数据库
# 数据库连接池类名称
spring.shardingsphere.datasource.ds-2024.type=com.alibaba.druid.pool.DruidDataSource
# 数据库驱动类名
spring.shardingsphere.datasource.ds-2024.driver-class-name=com.mysql.jdbc.Driver
# 数据库 url 连接
spring.shardingsphere.datasource.ds-2024.url=jdbc:mysql://192.168.200.130:3306/stock_db_2024?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false&serverTimezone=Asia/Shanghai
# 数据库用户名
spring.shardingsphere.datasource.ds-2024.username=root
# 数据库密码
spring.shardingsphere.datasource.ds-2024.password=1234# 指定stock_business为公共表，多个公共表以逗号间隔
spring.shardingsphere.sharding.broadcast‐tables=stock_business# 配置默认数据源（特点：对于不做分片处理的操作，都会直接访问默认数据源
# 未配置分片规则的表将通过默认数据源定位
spring.shardingsphere.sharding.default-data-source-name=defdb#开启sql显示到终端
spring.shardingsphere.props.sql.show=true

二：测试

    /*** @Description 测试广播表*/@Testpublic void testBroadCast(){StockBusiness pojo = StockBusiness.builder().stockCode("90000").stockName("900000").blockLabel("900000").blockName("900000").business("900000").updateTime(new Date()).build();stockBusinessMapper.insert(pojo);//stockBusinessMapper.deleteByPrimaryKey("90000");}

可以发现对四个数据源的sys_business广播表都进行了操作 

项目使用standard标准模式配置分库算法

一.分库算法类

/*** 定义公共的分库算法类：个股，大盘，板块都需要此分库算法* 泛型是分片键的类型，分片键是cur_time为datetime类型，java中为Date类型*/
public class CommonAlg4Db implements PreciseShardingAlgorithm<Date>, RangeShardingAlgorithm<Date> {/*** 精准查询的分库算法（条件为 = in）* select * from stock_block_rt_info where cur_time=xxx* @param dsNames 根据逻辑表对应的数据节点获取数据源*                ds-2022,ds-2023,ds-2024* @param preciseShardingValue 逻辑表名称，分片键名称，条件值*/@Overridepublic String doSharding(Collection<String> dsNames, PreciseShardingValue<Date> preciseShardingValue) {//获取逻辑表名称String logicTableName = preciseShardingValue.getLogicTableName();//获取分片键名称String columnName = preciseShardingValue.getColumnName();//获取条件值Date curTime = preciseShardingValue.getValue();//获取条件值的年,并转换成String类型String year=new DateTime(curTime).getYear()+"";//编写分库算法，来决定要返回的数据源Optional<String> result = dsNames.stream().filter(dsName -> dsName.endsWith(year)).findFirst();//返回数据源，如果没有匹配的数据源，就返回默认值nullreturn result.orElse(null);}/*** 范围查询的算法，条件为(between and)* select * from stock_block_rt_info where cur_time between xxx and xxx;* @param dsNames 根据逻辑表对应的数据节点获取数据源*                ds-2022,ds-2023,ds-2024* @param rangeShardingValue 逻辑表名称，分片键名称，条件值*/@Overridepublic Collection<String> doSharding(Collection<String> dsNames, RangeShardingValue<Date> rangeShardingValue) {//获取逻辑表的名称String logicTableName = rangeShardingValue.getLogicTableName();//获取分片键的名称String columnName = rangeShardingValue.getColumnName();//获取条件值的范围Range<Date> valueRange = rangeShardingValue.getValueRange();//判断是否有下限if(valueRange.hasLowerBound()){//获取下限的年份int startYear = new DateTime(valueRange.lowerEndpoint()).getYear();//ds-2022,ds-2023,ds-2024//编写分库算法，找出年份>=startYear的数据源,以-为分割符，取出年份并转换成int类型，然后过滤出>=startYear的数据源，并收集起来dsNames=dsNames.stream().filter(dsName->Integer.parseInt(dsName.split("-")[1])>=startYear).collect(Collectors.toList());}//判断是否有上限if(valueRange.hasUpperBound()){//获取上限的年份int endYear = new DateTime(valueRange.upperEndpoint()).getYear();//ds-2022,ds-2023,ds-2024//编写分库算法，找出年份<=endYear的数据源,以-为分割符，取出年份并转换成int类型，然后过滤出<=endYear的数据源，并收集起来dsNames=dsNames.stream().filter(dsName->Integer.parseInt(dsName.split("-")[1])<=endYear).collect(Collectors.toList());}//返回数据源return dsNames;}
}

 二.application中配置逻辑表对应的数据节点和分库算法

# 逻辑表对应的配置数据节点
# 由数据源名 + 表名组成，以小数点分隔。多个表以逗号分隔，支持 inline 表达式
spring.shardingsphere.sharding.tables.stock_outer_market_index_info.actual-data-nodes=ds-${2022..2024}.stock_outer_market_index_info
spring.shardingsphere.sharding.tables.stock_market_index_info.actual-data-nodes=ds-${2022..2024}.stock_market_index_info
spring.shardingsphere.sharding.tables.stock_block_rt_info.actual-data-nodes=ds-${2022..2024}.stock_block_rt_info
spring.shardingsphere.sharding.tables.stock_rt_info.actual-data-nodes=ds-2022.stock_rt_info_${202201..202212},ds-2023.stock_rt_info_${202301..202312},ds-2024.stock_rt_info_${202401..202412}#使用标准标准方式分表
common.algorithm.db=com.hhh.stock.sharding.CommonAlg4Db# stock_rt_info使用cur_time作为分库的分片键
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.algorithm.db}
# 范围分片算法类名称，用于 BETWEEN，可选。该类需实现 RangeShardingAlgorithm 接口并提供无参数的构造器
spring.shardingsphere.sharding.tables.stock_rt_info.database-strategy.standard.range-algorithm-class-name=${common.algorithm.db}# stock_outer_market_index_info使用cur_time作为分库的分片键
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.algorithm.db}
# 范围分片算法类名称，用于 BETWEEN，可选。该类需实现 RangeShardingAlgorithm 接口并提供无参数的构造器
spring.shardingsphere.sharding.tables.stock_outer_market_index_info.database-strategy.standard.range-algorithm-class-name=${common.algorithm.db}# stock_market_index_info使用cur_time作为分库的分片键
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.algorithm.db}
# 范围分片算法类名称，用于 BETWEEN，可选。该类需实现 RangeShardingAlgorithm 接口并提供无参数的构造器
spring.shardingsphere.sharding.tables.stock_market_index_info.database-strategy.standard.range-algorithm-class-name=${common.algorithm.db}# stock_block_rt_info使用cur_time作为分库的分片键
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.algorithm.db}
# 范围分片算法类名称，用于 BETWEEN，可选。该类需实现 RangeShardingAlgorithm 接口并提供无参数的构造器
spring.shardingsphere.sharding.tables.stock_block_rt_info.database-strategy.standard.range-algorithm-class-name=${common.algorithm.db}

项目使用stardard标准模式配置分表算法

一.分表算法类

/*** 个股流水表的分表算法，使用cur_time分片，泛型是Date类型*/
public class CommonAlg4Tb implements PreciseShardingAlgorithm<Date>, RangeShardingAlgorithm<Date> {/*** 精准查询的分库算法（条件为 = in）* select * from stock_rt_info where cur_time=xxx* @param tbNames 根据逻辑表对应的数据节点获取物理表*                stock_rt_info_202201,stock_rt_info_202212* @param preciseShardingValue 逻辑表名称，分片键名称，条件值*/@Overridepublic String doSharding(Collection<String> tbNames, PreciseShardingValue<Date> preciseShardingValue) {//获取逻辑表String logicTableName = preciseShardingValue.getLogicTableName();//获取分片键名称String columnName = preciseShardingValue.getColumnName();//获取条件值Date curDate = preciseShardingValue.getValue();//获取年月String yearMonth = new DateTime(curDate).toString(DateTimeFormat.forPattern("yyyyMM"));//编写分表算法来找出特定时间的物理表Optional<String> result = tbNames.stream().filter(tbName -> tbName.endsWith(yearMonth)).findFirst();return result.orElse(null);}/*** 范围查询的算法，条件为(between and)* select * from stock_block_rt_info where cur_time between xxx and xxx;* @param tbNames 根据逻辑表对应的数据节点获取物理表*                stock_rt_info_202201,stock_rt_info_202212* @param rangeShardingValue 逻辑表名称，分片键名称，条件值*/@Overridepublic Collection<String> doSharding(Collection<String> tbNames, RangeShardingValue<Date> rangeShardingValue) {//获取条件值Range<Date> valueRange = rangeShardingValue.getValueRange();if(valueRange.hasLowerBound()){int startYearMonth=Integer.parseInt(new DateTime(valueRange.lowerEndpoint()).toString(DateTimeFormat.forPattern("yyyyMM")));//编写分表算法来找出特定时间的物理表//stock_rt_info_202201,通过最后一个_来再加1获取起始索引，来截取yearMonthtbNames=tbNames.stream().filter(tbName->Integer.parseInt(tbName.substring(tbName.lastIndexOf("_")+1))>=startYearMonth).collect(Collectors.toList());}if(valueRange.hasUpperBound()){int endYearMonth=Integer.parseInt(new DateTime(valueRange.upperEndpoint()).toString(DateTimeFormat.forPattern("yyyyMM")));//编写分表算法来找出特定时间的物理表//stock_rt_info_202201,通过最后一个_来再加1获取起始索引，来截取yearMonthtbNames=tbNames.stream().filter(tbName->Integer.parseInt(tbName.substring(tbName.lastIndexOf("_")+1))<=endYearMonth).collect(Collectors.toList());}return tbNames;}
}

二.application配置

#使用标准方式分表
common.algorithm.tb=com.hhh.stock.sharding.CommonAlg4Tb
# stock_rt_info使用cur_time作为分表的分片键
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.algorithm.tb}
# 范围分片算法类名称，用于 BETWEEN，可选。该类需实现 RangeShardingAlgorithm 接口并提供无参数的构造器
spring.shardingsphere.sharding.tables.stock_rt_info.table-strategy.standard.range-algorithm-class-name=${common.algorithm.tb}

分库分表注意事项

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

  • 条件查询时分片字段不要使用函数处理，否则分片算法失效，导致全节点查询

    • 举例：select * from stock_rt_info where date_format(cur_time,‘%Y%m%d’)='20220910' ,函数会造成sharding的分片失效，导致全节点查询；
    • 同时在索引角度看，如果查询的分片字段使用函数，会导致索引失效，导致查询性能较低；

  • 条件查询时尽量使用符合sharding分片条件的关键字

    • 精准查询尽量使用in =,而范围查询尽量使用between ；
    • between和(in =)不要一起使用

  • sharding-jdbc对嵌套查询处理不友好

    • 如果嵌套查询的话，那么最好子查询的条件只命中单张表。如果子查询的条件关联了多张表，那么交易分步骤拆分实现；
    • 示例：我们项目中的K线统计中，需要将SQL拆分，然后分步骤实现；

 


http://www.mrgr.cn/news/33693.html

相关文章：

  • 【数据结构 | C++】字符串关键字的散列映射
  • vue内置方法总结
  • SQL练习(2)
  • 【系统设计】理解带宽延迟积（BDP）、吞吐量、延时（RTT）与TCP发送窗口的关系：优化网络性能的关键
  • Gitlab-执行器为Kubetnetes时的注意事项，解决DNS解析问题
  • ServletContext介绍
  • 【鼠标滚轮专用芯片】KTH57913D 霍尔位置传感器
  • 作用域与作用域链
  • fas sklxj siaoj oisaj
  • 【系统架构设计师】论文模板：快速写好一篇架构设计师论文
  • Rabbitmq消息队列，安装，使用，三种工作模式
  • Vue工程师面试题
  • re题(39)BUUCTF-[FlareOn3]Challenge1
  • DNF Decouple and Feedback Network for Seeing in the Dark
  • 【LLM论文日更】| 俄罗斯套娃嵌入模型
  • 中国人工智能企业崛起：4000家背后的机遇与挑战
  • 感知算法引入时序模型的优势
  • 企业级-pdf预览-前后端
  • 4--SpringBoot项目中分类管理
  • 【小bug】使用 RestTemplate 工具从 JSON 数据反序列化为 Java 对象时报类型转换异常
  • python编程，把所有子目录和文件输出到文本文件
  • C++面向对象：多态！
  • ComfyUI生成头像
  • spring揭秘22-springmvc01-概述
  • 基于51单片机的手环设计仿真
  • 数据结构与算法——Java实现 10.习题——删除有序链表重复节点