平台各系统统计分析系统设计方案
随着公司业务的快速开拓,数据库数据量极速增长,一些关键数据表的数据没有备份归档策略,
在统计分析策略上也没有进行相应的优化,系统将会很不稳定,最终影响日常交易、运营业务运行,
同时也对新业务的开展造成了严重制约(例如商家查询和报表)。特编写手软平台所有系统进行统计
分析的基本设计规范,作为各新系统建设的参考。
基本指导原则:
分离统计库(olap)和交易库(oltp)
建立相对完备的数据归档、预处理机制,定期将统计数据归档到统计分析中间表
统计分析数据从中间表读取,避免从 oltp 都实时统计查询
1. 目前系统主要的问题及经验教训
性能问题主要来自于后台统计、结算等操作,这些 sql 的代价非常庞大,也是系统性能瓶颈所在。
系统缺少老数据的归档机制,导致一些关键表的数据量一直在增长,导致日常查询操作压力较大,
影响所有查询效率。
系统统计分析和查询操作没有预处理机制,形成中间表或临时表,应用直接查询原始表,导致数
据库压力过大。
存在查询按钮重复点击,导致同一查询同时执行,很容易导致数据库负载达到峰值。
查询页面需求不明确,存在一些不必要的信息,导致关联查询的复杂性。应用中存在无条件的查
询语句。
业务高负载时,集中在白天。这个时段的并发查询导致整体性能下降。
2. 新系统统计分析设计基本方法
基本方法:
数据归档
对历史数据定时归档,降低在线表各表的数据量。历史数据不进行删除操作。
用脚本定时完成统计分析数据或查询数据入临时表或中间表,降低查询的压力。
数据库优化和应用设计优化(数据库扁平化)
对数据采用分库、分区、分表方式来处理大数据处理。
对数据库设计优化。
对应用系统优化。
3. 数据库分离基本方法
3.1. 数据归档原则
1) 目的:
归档包括对交易数据的归档,也包括对历史数据的归档。
交易数据的归档:
将业务表与历史数据设计成不同的表来存储数据。业务表中只保留正在处理的业务数据。业务表
中保留一段时间后(视数据量和实际需求),系统定时进行归档或人工在界面操作进行归档,将业务
数据转移至历史数据表。
历史数据的归档
按照统计分析或大数据量查询要求,通过定时脚本对历史数据进行归档处理以降低统计查询的数
据量。
典型场景:对历史数据,按照日报表、周报表、月报表、年报表要求进行定时处理,形成中间数
据表,前台查询时候直接从中间数据表中查询数据。
2) 原因:
对于历史交易数据如果仍然存在在交易表中不进行清理操作,随着交易数据量的增长,将影响系
统业务处理性能。
3) 备注:
归档策略依赖于统计分析、定时脚本、查询等需求。
存放时间:电信系统一般保存 6 个月数据。
历史数据归档查询:
3.2. 数据分库原则
1) 目的:
数据库分库原则分离交易库和分析库。
2) 原因:
OLTP 系统管理当前数据,侧重于事务和查询处理,在设计上遵循 E-R 模型。
OLAP 系统管理大量历史数据,提供汇总和聚集机制.,用于数据统计分析和挖掘,在设计上采用星
型或雪花模型和面向主题的数据库设计。
目前系统并没有区分这两种系统,导致进行统计分析和大数据量查询时候对数据库资源有较大的
占用。
3) OLAP 与 OLTP 同步策略
采用 mysql 的 Master-Slave 模式来实现。交易库为 Master,统计库为 Slave。除对统计报表
相关的表进行操作外,对统计库(Slave 库)的交易数据不进行任何 insert、update、delete 操作,
需要更新相关数据,只能操作 Master 库。
3.3. 数据分区原则
目的:
为了使用户的大量的数据在读写操作和查询中速度更快,利用数据库系统提供的对表和索引进行
分区的技术,以改善大型应用系统的性能。
使用分区的优点:
增强可用性:如果表的某个分区出现故障,表在其他分区的数据仍然可用;
维护方便:如果表的某个分区出现故障,需要修复数据,只修复该分区即可
均衡 I/O:可以把不同的分区映射到磁盘以平衡 I/O,改善整个系统性能;
改善查询性能:对分区对象的查询可以仅搜索自己关心的分区,提高检索速度。
3.4. 数据分表原则
目的:
把若干个存储相同类型数据的表分成几个表分表存储,在提取数据的时候,不同的用户访问不同
的表,互不冲突,减少锁表的几率。
可通过一个原始目标的 ID 或者名称通过一定的 hash 算法计算出数据存储表的表名,然后访问相应
的表。
3.5. 统计分析处理原则
写脚本自动生成统计分析结果,存入结果表中,提供查询。
3.6. 数据库操作日志拦截
目的:
利用 hibernate 等的 interceptor 机制,拦截下数据库操作日志,以便于跟踪 sql 语句及性能调
试。
正常情况下,拦截操作日志为关闭状态。
3.7. 中间表、临时表、视图
目的
利用中间表和临时表或视图机制,降低统计分析或查询过程中过大 sql 的操作,造成对数据库排
序区过多占用,提高查询速度。
4. eSales 系统核心统计策略
通过 Quartz 来实现每日凌晨及每月 1 号凌晨业务量较小时候定时调度统计分析程序,汇总每日
的销售明细等统计数据,形成“产品日销售统计”、“产品月销售统计”、“销售员日销售统计”、“销售员
月销售统计”几个核心的统计中间表。