QP支付后台用户存取款历史数据分表存储
QP支付后台用户存取款历史数据分表存储
问题
用户存款包含了有史以来的所有存款记录, 目前数据量已超过1000万, 后续仍会持续增加. 目前使用中反馈, 有写入,修改数据缓慢和查询页面显示刷新缓慢的问题.
解决方案
咨询客服同事,对历史存款数据的访问, 多集中在1个月内, 对更久以前的数据, 基本不访问.
可将3个月前的存款数据转储至其它数据表中, 保证活跃数据表中并不会留存太多数据, 以加快数据的访问效率.
对转储后历史数据, 虽访问少, 但仍需要保留访问入口.
切分规则
现有数据的粗略统计如下: 2018年全年的数据 约350万条 2019年现有的数据, 每月产生的数据量约85万-97万条
根据以上数据统计结果, 与开发同事协商, 将用户存款数据3个月以前的数据按月进行切分, 转存至新表.
表名命令规则: 原表名: t_u_deposit_record deposit统计值 : re_amount 过滤条件: flag = 2 and is_notify = 1 withdraw统计值 : amount 过滤条件: flag = 2 and is_notify = 1
新的活跃存款表名: t_u_deposit_record (不变)
历史数据按月切分的表名规则: t_u_deposit_record_his_XXXXMM (XXXX: 年份, 4位数字, MM: 月份, 2位数字, 如201909, 201910)
数据转储方式
通过编写mysql存储过程, 以事件的方式每天循环调用执行数据转储.
历史数据汇总统计
为加快历史数据的访问速度, 需要对转储后的历史数据进行汇总统计, 加快数据检索速度.
修改转储数据的存储过程, 对转储的数据进行增量汇总
根据customer_id进行分组
按天对re_amount和行数进行统计, 过滤条件(flag = 2 & is_notify = 1)
根据channel_code进行分组
按天对re_amount和行数进行统计, 过滤条件(flag = 2 & is_notify = 1)
deposit的chan_code字段为 : code
withdraw的chan_code字段为: type
MySQL存储过程中处理细节
错误处理
需要处理的错误码: 1146, 1058, 1062, 1072, 1103, 1106, 1109, 1117, 1149, 1292, 1091, 1054
错误处理主要通过”GET DIAGNOSTICS CONDITION 1”, 获取错误状态, 错误信息和错误的代码, 并将获取到的内容写入迁移错误日志表migrate_err_log.
定义错误处理捕获处理方式为退出, 发生错误后, 错误信息会被记录至迁移错误日志表, 同时在迁移日志表中对执行设定了限制, 无法重复执行, 因此在修复完错误后, 将对应日期的数据从历史数据表中迁移至业务数据表中, 并清理对应日期的用户和渠道统计数据, 重新手动调用迁移过程.若迁移日志只有开始的记录, 却没有完成的记录, 也按上述同样的处理方式进行处理.
单次处理数据量
目前对单次处理数据量做了一定的限定, 设定范围为[5000, 10000], 减小数据迁移的数据处理对业务运行的影响. 若后续通过监控发现仍存在一定的影响, 可进一步减小单次处理数据量.
数据迁移过程
迁移的处理步骤如下:
- 记录数据迁移开始日志至数据表
- 从业务表中获取设定量的数据, 写入临时业务数据表中
- 将数据由临时表写入特定的历史数据表(按上述规则进行计算)
- 检查临时表与写入历史表的数据是否一致, 若不一致, 则将错误信息记录至迁移错误日志, 并退出执行
- 若检查结果一致, 根据临时表中的数据删除对应存储在业务数据表中的数据
- 将当前临时表中的数据按用户汇总, 写入临时汇总表, 汇总内容为用户存取款总金额和总次数
- 将当前临时表中的数据按渠道汇总, 写入临时汇总表, 汇总内容为用户渠道存取款总金额和总次数
- 删除临时业务数据表中数据
- 重复执行上述2-8步骤, 当检测到单次写入历史数据表中数据小于设定的数量, 即认为当天数据已处理完, 退出循环.
- 更新记录迁移日志, 记录完成时间及迁移数据量
- 汇总用户临时汇总表中的数据, 写入汇总数据表
- 汇总渠道临时汇总表中的数据, 写入汇总数据表
- 清理临时数据表
设置定时循环调用
将循环定时调用过程设定至每天01:00 – 06:00 (am) 执行
