催收列表查询优化
催收列表查询优化
场景
描述
针对催收列表页的查询,在数据量上来之后,查询耗时变长,有的请求最长可能超过一分钟 ### 背景
表规模:千万级别,关联级别表:千万级别,单个业务员实时催收的订单:100 ~2000 不等。
数据库配置:高可用主从架构。1主一从,4核8G 配置 #### 查询选项根据债务人信息查询
- 根据订单号查询
- 根据部门ID查询、根据业务员查询(管理员)
- 根据逾期天数查询
- 根据逾期日期查询
- 根据待还金额查询
- 根据权限过滤
- 是否有效
- 根据状态 - 催收中、待开始、已结清 #### 难点 - 列表页输出的字段较多:债务人信息、案件信息等超过15个字段的数据 - 列表页需要统计命中单子的总借款金额、总还款金额、总代还金额 - 有一些查询没有命中索引,例如,逾期日期字段 - 数据需要聚合第三方接口返回的数据 分页查询 ```sql select flow.*, debtor.identity, debtor.xxx from workflow as flow join on debt on debt.case_id = flow.case_id join on debtor on debtor.debt_id = debt.id order by flow.created_time where flow.urge_start > '2020-12-12 12:00:00' ``` ## 任务 - 优化催收列表页查询 - 导致页面响应慢的原因 - 确认页面加载慢的具体原因 这可以通过Chrome DevTool工具提供的”network->all->waterfall”可以非常直观的看出耗时严重的地方。 针对这个任务,耗时主要是在后端列表数据接口请求上,占到耗时的90%。 确认后端慢的原因 通过在模拟的业务请求,分析请求链路耗时(70% 在SQL查询上,30% 在外部API请求上),定位后端查询请求慢的原因 这里主要有几个地方可以进行优化: - 数据库查询 - select * - 查询时无索引字段 - 针对逾期日期的查询 - 数据聚合慢 - 每一条记录都会调用一次上游接口
方案
- 数据库查询优化
- 减少不必要的查询
- 首页添加默认选项 - 默认显示固定逾期天数的单子
- 慢SQL优化
- 定位慢SQL MySQL的配置参数中有一些参数可以控制慢sql日志信息。 ``` slow_query_log = ON # 开启或关闭慢查询 long_query_time = 5 #慢查询记录的阈值,超过此时间会被记录到日志中 slow_query_log_file = /opt/soft/mysql/log/slow.log # 日志路径 log_queries_not_using_indexes=on # 是否记录未使用索引的SQL
``` 阿里云RDS提供了关于慢SQL查询的接口,可以通过接口查看具体DB下慢SQL信息 我们公司由运维部门提供的Gaea平台,可以直观的查到某个数据库下相关慢查询。提供的信息包括:SQL抽样模式、平均查询耗时、第一次和最后一次出现的时间、hostname、查询次数、总耗时、详细的sql语句、explain结果 优化方式
- 先运行看看是否真的很慢,注意设置~SQL_NO_CACHE~
- 确定各个字段的区分度
- explain 执行语句,查看执行计划,确定结果和上一条是否一致
- 了解业务方使用场景
- 添加索引时遵守一定的规则 重复上面的步骤
添加索引的规则
- 最左前缀匹配规则,mysql 会一直向右匹配知道遇到匹配规则,(>、<、between、like)就停止匹配,比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引,d是用不到索引的,如果建立(a,b,d,c)的索引则都可以用到,a,b,d的顺序可以任意调整。
- 尽量选择区分度高的字段做索引,区分度的公式是~count(distinct col)/count(*)~,表示字段不重复的比例,比例越大我们扫描的记录数越少
- 不要在索引列上添加函数或者计算
- 尽量的扩展索引,不要新建索引。比如表中已经有a的索引,现在要加(a,b)的索引,那么只需要修改原来的索引即可
- 不要在频繁更新的字段建索引
分页查询优化
催收列表页的数据有一个基准,就是催收主体,对应到数据库中的表就是workflow,每一个workflow 都是一个案件,案件描述是案件维度的数据,workflow描述的催收维度的数据,所以flowid可以作为分页查询的标签,这样可以直接避免使用offset的方式,因为offset 很难优化。
聚合数据的优化 场景:一般都是管理员、组长、逾期日期90天+的业务员问题比较严重,因为数据权限更大,聚合数据更多。 分析: 这种聚合数据一般都不作为对账数据使用,都是用来作为一个大概的估算。 特别是90天+的单子,债务人还款意愿特别低,数据发生变更的概率也特别低,所以,直接缓存查询结果为30分钟。 方案:将查询结果进行缓存,针对不同逾期天数的单子设置不同的失效时间,例如:90天+的单子为30分钟,30天~60天的单子缓存15分钟等。 上游接口调用优化 方案1: 采用Gevent并行查询接口,将返回的数据和数据库查询返回的数据进行聚合,返回结果。需要注意上游接口异常的问题,需要重试。 优点:
- 将串行变成了并行查询,提供了响应时间
- 保证数据准确性。 缺点:
- 仍然有待提高的空间
方案2: 引入阿里云MNS,订阅上游数据变更通知,下游收到通知后拉取上游数据,缓存结果到数据库中,这样就不用每次查询都调用上游接口数据了。 优点: 实际接口查询中,不再需要查询上游接口,直接查询数据库就可以满足要求 缺点:
- 引入了消息队列,增加了复杂度
- 如果上游手动刷数据,下游就感知不到数据变更,导致数据不一致
结果
后端列表页接口P95响应时间都在1秒以内,基本满足业务员操作的需求。