一、背景:
最近公司生产环境有个分页查询的接口,查询一个月的数据大约耗时5.8s,这是用户忍受不了的!生产环境数据库单表数据量9000w+,使用的是阿里云polarDB。
接口大体功能如下:
1、聚合查询,sql简化后如下,其中create_time与trade_price都已建立索引,独绝了回表可能(耗时5800ms):
select sum(trade_price) AS price
from pl_finance
where create_time >= '2020-06-01 00:00:00'
AND create_time < '2020-07-01 00:00:00';
2、分页查询,sql如下(耗时1300s):
select pl.*
from pl_finance pl
where pl.create_time >= '2019-08-01 14:08:03'
AND pl.create_time < '2019-09-01 16:12:03'
order by pl.create_time DESC
LIMIT 100,50;
3、根据步骤2分也查询返回的结果去调用外部接口对数据进行加工(耗时50s)
4、对步骤1聚合查询结果、步骤3分页数据组装并返回(内存运算,耗时忽略)
至此整个接口耗时为:5800ms+1300ms+50ms=7150ms,时序图如下:
二、优化
1、
异步化:将步骤1的聚合查询改为异步查询,至此接口耗时为5800ms,时序图如下:
2、
多线程:由时序图可看出耗时部分主要发生在步骤一的聚合查询;聚合查询的时间跨度为1个月,这里的优化点就是将sql按时间维度切割成多个sql,然后由程序并发查询,最后在程序里做结果集合并。(比如现在聚合查询的sql时间跨度为一个月,按天拆成30个sql,每个sql查询一天,30个sql并发执行,最后在程序里结果集合并)分割后的单条sql耗时约1800ms,至此接口耗时为1800ms,时序图如下:
3、
缓存:如果聚合查询的sql时间条件不变的话,完全可以将聚合查询结果缓存起来,读取缓存耗时10ms(redis 网络通信耗时),至此接口耗时为sql分页查询1300ms+调用外部接口50ms=1350ms,时序图如下:
4、
去除分页组件:这里的sql分页查询使用的是MyBatisPlus分装过的分页查询组件;组件每次查询是都会先count下总数,在分页查询具体总数,count耗时约1250ms,而真正的分页查询耗时仅40ms,时序图如下:
当分页查询的时间条件不变的话,count查询结果完全可以缓存起来,读取缓存耗时约10ms,至此接口耗时为:
读取count缓存10ms + sql分页查询40ms + 调用外部接口50ms = 100ms,时序图如下:
5、
场景优化:如果查询条件每次都改变的话,走缓存的场景很低,接口耗时依然严重;楼主能想到办法就是将耗时严重的聚合查询步骤单独拎出一个接口,在前台做一个单独的按钮,当用户真正去需要聚合查询的结果时,再去点击按钮处罚聚合查询,时序图如下:
分页查询时序图:
聚合查询时序图:
6、
限流:考虑到聚合查询耗时严重会对数据库的性能造成影响,这里对单拎出的聚合查询接口做了限流。
三、总结
接口性能优化的常用手段有异步化、并行查询、多线程、缓存、场景优化、限流;在数据库层面可以做分库分表或采用高性能数据库(polarDB、ADB)。