随着公司业务的快速开拓，数据库数据量极速增长，一些关键数据表的数据没有备份归档策略，在统计分析策略上也没有进行相应的优化，系统将会很不稳定，最终影响日常交易、运营业务运行，同时也对新业务的开展造成了严重制约（例如商家查询和报表）。特编写手软平台所有系统进行统计分析的基本设计规范，作为各新系统建设的建设。
基本指导原则：
- 分离统计库（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号凌晨业务量较小时候定时调度统计分析程序，汇总每日的销售明细等统计数据，形成“产品日销售统计”、“产品月销售统计”、“销售员日销售统计”、“销售员月销售统计”几个核心的统计中间表。
转载请注明：出家如初,成佛有余 » 平台各系统统计分析系统设计方案