大屏项目MySQL架构设计及搭建实战
- 1、背景
- 2、需求分析
- 3、表结构设计
- 3.1、大屏指标表
- 3.2、维度表
- 3.3、维度数值表
- 3.4、指标维度信息表
- 3.5、指标维度结果表
- 3.6、指标执行记录表
- 3.7、业务归档表
- 3.8、业务数据表
- 4、数据函数
- 4.1、开始函数
- 4.2、完成函数
- 4.3、异常函数
- 4.4、异常监控函数
- 4.5、常规工具类函数
- 4.6、驱动函数(非常重要)
- 4.7、返回报文函数
- 5、视图
- 6、定时触发器
- 7、DEMO样例
- 7.1、提前准备业务数据
- 7.2、配置数据
- 7.3、数据生成函数
- 7.4、财务模块通用返回函数
- 7.5、性别分布指标返回函数
- 8、总结
1、背景
近期由于项目需要,需要上架一套大屏系统,但由于原大屏系统架构设计比较复杂,故重新设计一套架构。
该架构主要依赖于MySQL数据库,只需少量JAVA代码做接口处理。
2、需求分析
依据大屏的使用场景,可以分为两种调用方式:
- 1、实时获取数据,如当前系统下的使用量。
- 2、获取统计数据,如获取年维度、年月维度、年月日维度的数据。
实现方式对比如下
实现方式 | JAVA代码 | MySQL |
---|---|---|
实时数据 | 实时接口 | 视图查询、函数获取 |
统计数据 | 跑批归档 | 函数归档 |
基于数据处理方式,本次选择全权交付于MySQL处理,代码仅做接口调用获取数据使用,不处理业务数据。
3、表结构设计
写在前面:用MySQL这种关系型数据库处理大屏,本身存在一定的劣势。
数据的归档,是需要进行数据维度的下钻,这时候键值数据库就会来的比较方便。
有条件的,建议使用关系型数据库MySQL,同时数据的归档存储建议使用键值数据库。
如大屏项目数据维度较少,可以使用MySQL进行数据的归集。
3.1、大屏指标表
- 1、大屏的内容并不会仅在一个页面显示,所以在设计的时候增加了模块的概念。
- 2、将同一页面的每一个显示内容,定义为一个指标。
- 3、使用时,会牵扯到视图、函数等,故在设计表时需要体现该元素。
CREATE TABLE `t_cockpit_kpi` (`ID` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',`MODEL_CODE` varchar(50) NOT NULL COMMENT '模块代码',`MODEL_NAME` varchar(50) NOT NULL COMMENT '模块名称',`KPI_CODE` varchar(50) NOT NULL COMMENT '指标代码',`KPI_NAME` varchar(50) NOT NULL COMMENT '指标名称',`FREQUENCY` varchar(10) DEFAULT NULL COMMENT '频率:y,ym,ymd,ymdh,ymdhm',`EXECUTE_FUNCTION` varchar(100) DEFAULT NULL COMMENT '指标函数名称',`EXECUTE_VIEW` varchar(100) DEFAULT NULL COMMENT '指标视图名称',`SELECT_FUNCTION` varchar(100) DEFAULT NULL COMMENT '查询函数名称',`REMARK` varchar(300) DEFAULT NULL COMMENT '指标统计规则描述',`EXECUTE_STATUS` varchar(1) DEFAULT '0' COMMENT '函数运行状态:0未运行 1运行中',`EXECUTE_START_TIME` datetime DEFAULT NULL COMMENT '最近一次开始时间',`EXECUTE_FINISH_TIME` datetime DEFAULT NULL COMMENT '最近一次完成时间',`EXECUTE_NUMBER` int(11) DEFAULT '0' COMMENT '执行次数',`SUCCESS_NUMBER` int(11) DEFAULT '0' COMMENT '成功次数',`KPI_STATUS` varchar(1) NOT NULL DEFAULT '1' COMMENT '状态:1有效,0无效',PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='指标信息表'
3.2、维度表
- 1、原设计是将指标和维度一一对应即可,但实际情况中,可能不同的指标对应相同的维度。这就导致维度不能复用。
- 2、故设置指标和维度为 N:N的关系
CREATE TABLE `t_cockpit_rank` (`ID` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',`RANK_CODE` varchar(50) NOT NULL COMMENT '维度编码',`RANK_NAME` varchar(50) NOT NULL COMMENT '维度名称',`STATUS` char(1) NOT NULL DEFAULT '1' COMMENT '状态:1有效,0无效',PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='维度表'
3.3、维度数值表
- 1、每个维度下,都会有一些枚举值,故需维度对应的数值表。
CREATE TABLE `t_cockpit_rank_value` (`ID` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',`RANK_CODE` varchar(50) NOT NULL COMMENT '维度编码',`RANK_NAME` varchar(50) NOT NULL COMMENT '维度名称',`VALUE_CODE` varchar(50) NOT NULL COMMENT '维度数值编码',`VALUE_NAME` varchar(50) NOT NULL COMMENT '维度数值名称',`STATUS` char(1) NOT NULL DEFAULT '1' COMMENT '状态:1有效,0无效',PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='维度数值表'
3.4、指标维度信息表
- 1、每个指标都会对应不同的编码值。此处并没有采用 指标 —> 维度,维度 —> 数值 的表设计关系,而是简化了该设计,直接将 指标 —> 维度 —> 数值 做了关联关系。
- 2、在设计该表时,考虑业务数据并不能直接在大屏中体现,所以该表会将业务枚举做一层转换,以隐藏业务编码值。
CREATE TABLE `t_cockpit_kpi_rank` (`ID` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',`KPI_CODE` varchar(50) NOT NULL COMMENT '指标代码',`RANK_CODE` varchar(50) NOT NULL COMMENT '维度编码',`VALUE_CODE` varchar(50) NOT NULL COMMENT '维度数值编码',`ENUM_VALUE` varchar(50) NOT NULL COMMENT '维度对应表中枚举值',`STATUS` char(1) NOT NULL DEFAULT '1' COMMENT '状态:1有效,0无效',PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='指标维度信息表'
3.5、指标维度结果表
该表为数据最底层维度数据表,可依据此表创建对应的业务汇总表,以提高查询效率。
- 1、在设计该表时,就无法避免关系型数据库所带来的问题,如果以下钻的方式来处理,键值对数据库则更适合该数据场景。
- 2、表中定义了5个维度数值字段,以处理各维度数据,如不够可延伸该字段。
- 3、在存储时,需将数据以对应维度数值存储,以方便后续查询。
CREATE TABLE `t_cockpit_value` (`ID` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',`MODEL_CODE` varchar(50) NOT NULL COMMENT '模块代码',`KPI_CODE` varchar(50) NOT NULL COMMENT '指标代码',`KPI_DATE` date DEFAULT NULL COMMENT '数据时间,年默认1月1日,月默认1日',`KPI_TIME` datetime DEFAULT NULL COMMENT '数据日期(维度下放至时分使用)',`RANK_FIRST` varchar(50) DEFAULT NULL COMMENT '一级维度数值编码',`RANK_SECOND` varchar(50) DEFAULT NULL COMMENT '二级维度数值编码',`RANK_THIRD` varchar(50) DEFAULT NULL COMMENT '三级维度数值编码',`RANK_FOURTH` varchar(50) DEFAULT NULL COMMENT '四级维度数值编码',`RANK_FIFTH` varchar(50) DEFAULT NULL COMMENT '五级维度数值编码',`KPI_VALUE` decimal(15,2) DEFAULT NULL COMMENT '值',`KPI_TEXT` text COMMENT '文本值',`PERCENTAGE` decimal(15,2) DEFAULT NULL