day10-项目集成sharding-jdbc-今日指数

项目集成sharding-jdbc

目标

1.掌握shardingjdbc标准分片模式；
2.理解项目分库分表的设计思路；
3.理解分库分表代码实现流程；

第一章 项目分库分表实现

1、股票数据预期增长分析

​ 由于今日指数是偏向海量数据分析的产品，而股票的数据会随着时间的积累越来越多，这会严重影响查询性能，所以合理的分库分表规划就迫在眉睫了。

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

表名	时间周期	累计数量	分库分表策略
股票流水表-stock_rt_info	月	1(钟)x60（时）x4（天）x21(月)x1500（重点股票）约等于：750W+	按年分库，按月分表
股票主营业务表-stock_business		3000+	公共表|广播表	数据量少 数据变化频率低 各个数据库都会用到
国内大盘流水表-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、股票数据库表拆分规划

2.1 数据分库分表规划

基于上一小节的分析，我们得出一些结论：

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

整体架构如下：

数据详见：讲义\v-2\资料\今日指数分库分表SQL脚本

综上，我们以日期时间字段cur_time作为库表的分偏键，分库分表的逻辑存在一定的复杂性，采用标准分片策略比较合适。

2.2 分库分表策略规划

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

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

3、默认数据源配置

3.1 工程依赖准备

在stock_common工程导入sharding-jdbc依赖：

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

3.2 配置默认数据源

系统管理相关的表数据量较少，无需分库分表，所以可作为sharding-jdbc的默认数据源；

在stock_backend工程配置application-sharding.properties：

# 数据源名称，多数据源以逗号分隔
# 注意事项：数据源名称如果存在多个单词，不要使用小驼峰，建议使用中划线间隔(不要使用下划线间隔符）
spring.shardingsphere.datasource.names=df
# 配置默认数据源
# 数据库连接池类名称
spring.shardingsphere.datasource.df.type=com.alibaba.druid.pool.DruidDataSource
# 数据库驱动类名
spring.shardingsphere.datasource.df.driver-class-name=com.mysql.jdbc.Driver
# 数据库 url 连接
spring.shardingsphere.datasource.df.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.df.username=root
# 数据库密码
spring.shardingsphere.datasource.df.password=root
# 配置默认数据源
spring.shardingsphere.sharding.default-data-source-name=df
# 是否开启 SQL 显示，默认值: false
spring.shardingsphere.props.sql.show=true

主配置文件application.yml激活application-sharding.properties配置：

spring.profiles.active=sharding

同时在application.yml主配置文件中注释掉原有数据源信息！！

3.3 默认数据源效果测试

@SpringBootTest
public class TestSharding {
   
   
    @Autowired
    private SysUserMapper sysUserMapper;
    /**
     * 测试默认数据源
     */
    @Test
    public void testDefaultDs(){
   
   
        SysUser user = sysUserMapper.selectByPrimaryKey("1237365636208922624");
        System.out.println(user);
    }
}

测试时发现报异常错误：

所以在主配置文件中，配置bean允许被覆盖：

spring.main.allow-bean-definition-overriding=true

最终效果：

4、广播表配置

对于stock_business业务表作为广播表处理：

在application-sharding.properties配置其它数据源：

# 第一步：配置shardingjdbc
# 数据源名称，多数据源以逗号分隔(datasource名称不要使用特殊符号)
spring.shardingsphere.datasource.names=ds-2021,ds-2022,df
# 数据库连接池类名称
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.datasource.df.type=com.alibaba.druid.pool.DruidDataSource
# 数据库驱动类名
spring.shardingsphere.datasource.df.driver-class-name=com.mysql.jdbc.Driver
# 数据库 url 连接
spring.shardingsphere.datasource.df.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.df.username=root
# 数据库密码
spring.shardingsphere.datasource.df.password=root

# 配置广播表，如果有多个，以逗号间隔
spring.shardingsphere.sharding.broadcast-tables=stock_business
# 配置默认数据源
spring.shardingsphere.sharding.default-data-source-name=df

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

测试广播表：

@Autowired
private StockBusinessMapper stockBusinessMapper;
/**
     * @Description 测试广播表
     */
@Test
public void testBroadCastTable(){
   
   
  List<StockBusiness> all = stockBusinessMapper.getAll();
  System.out.println(all);
}

5、大盘板块分库分表实现

大盘板块分库分表思路分析：

• 对于stock_block_rt_info等相关表一年产出的数据量不大，所以对这类表只做分库处理，而库内无需做分表处理；
• 大盘板块相关表的分库策略是相同的，所以我们可将分库分表算法抽取出来作为公共算法类，同时库内没有做分表处理，所以无需定义分表策略；
• 主业务工程和定时任务工程都需要分库或分表的算法类，所以我们在common工程下维护；

5.1 定义公共分库算法类

在stock_common工程下定义公共分库算法类：

package com.itheima.stock.sharding;

import com.google.common.collect.Range;
import org.apache.shardingsphere.api.sharding.standard.<