一次接口超时和慢SQL优化过程背景上周有客户反馈，他们计费相关部分接口请求很慢，甚至超时。客户会在几天后演示该系统，时

背景

上周有客户反馈，他们计费相关部分接口请求很慢，甚至超时。客户会在几天后演示该系统，时间紧急，需马上处理。

现象和分析

mysql5.7， jdk8。客户环境CPU16核，64G。

直接上图，可以看出每个请求数据量在1kb左右，耗时主要在Waiting(TTFB)阶段。那主要耗时还是在后端。因涉及到的接口比较多，深入到代码逻辑分析每个接口的耗时点。

大概发现两类问题：

1，循环遍历SQL。
2，SQL查询涉及到一张xx_cost表时，耗时过长。

解决方向由于时间紧张，在不能修改原有逻辑的情况下，做三个方向的优化。

数据聚合类的接口，每层会执行大量的SQL，进行汇总返回。大部分时间在等待SQL返回，I/O密集调用，这部分代码改为并行执行。
通用数据，不会更新的历史数据，不存在动态查询和数据权限过滤的数据。这类适合缓存
SQL慢查询优化

解决方案

并行

1, 其中一个接口递归计算每个组织资源量，向上汇总到上级组织，计算到目标组织返回。当组织数量上千时，该接口很容易超时。打算用并行计算，因为是递归计算每个叶子节点的资源量在汇总，所以想到了ForkJoin的方式。文档介绍

使用ForkJoinPool改造代码，在该客户数据量的情况下，接口耗时减少50%以上。

    ForkJoinPool forkJoinPool = ForkJoinPool.commonPool();
    Map<String, Integer> quotas = forkJoinPool.invoke(
            new CompanyAllocatedQuotasParallel(orgId, orgMap, newInitQuotaMap()));
    forkJoinPool.shutdown();

class CompanyAllocatedQuotasParallel extends RecursiveTask<Map<String, Integer>>{
    //...
    protected Map<String, Integer> compute() {
        //...
        CompanyAllocatedQuotasParallel parallel = new CompanyAllocatedQuotasParallel(resultList);
        resultList.addAll(parallel.fork().join());
    }
}

还有其他接口，在没有递归的场景下，则使用Executor，需要注意线程数，判断下当前机器线程，过去有客户物理机POC时，线程数会上百，直接使用Runtime方式不合适。

int corePoolSize 
    = Integer.min(Runtime.getRuntime().availableProcessors(), monthObjs.size());

缓存

其中一个接口查询前几个月历史数据，然后汇总计算返回。这部分数据不会变更，查询也没有动态列过滤，没有细粒度权限过滤。这比较适合缓存，实现也比较简单，存入redis，注意失效时间和数据预热即可。

mysql慢查询

这部分比较麻烦，不同的场景，具体方案不一样。但方向是让SQL用索引，减少回表。

分析过程中发现慢接口都涉及了一张cost表的查询，该表数据量目前只有百万级，理论上也不会太慢。但看了mapper中。关联了多张表，很多表在当前查询不需要。导致一个查询往往超过30s。

1.删除关联表，查看explain

第一步根据返回的列先把不需要的关联表去除掉, 第二步查看explain

在已经建立索引的情况下，还是没走索引。这是因为mysql优化器对该表数据量判断的结果，所以是全表扫描。文档有解释

这也涉及到最左匹配，我们系统在数据权限过滤插件类也有类似SQL可以验证，同样的SQL，优化器根据扫描的结果判断是否走索引。

2.拆where日期范围，union all连接

为了能让SQL走索引，计划把日期范围拆小，查询条件一个月范围改为，10天范围。在用union all把结果拼接返回。类似这样的SQL。

SELECT amount FROM ( 
	SELECT 
		sum( pretax_amount ) amount, org_sid 
	FROM cost 
	WHERE pay_time >= '2022-03-01' 
		AND pay_time <= '2022-03-10' 
	GROUP BY org_sid 
	union all
	...
) A

这样做确实每条SQL走索引了，但当前数据量的情况下，耗时变长了。explain后发现 extra出现Using temporary; Using filesort 原因是，这个查询会用到系统的数据权限通用类过滤，每个查询需要加上group by org_sid。这就导致了filesort，虽然可以用order by null，减少using filesort。但using temporary是没办法规避。文档介绍

Presence of any string column with a maximum length larger than 512 (bytes for binary strings, characters for nonbinary strings) in the [SELECT]list, if [UNION]or [UNION ALL] is used.

3.java并行查询

最终还是改为用java并行查询汇总返回。经测试环境验证，接口测试时间在1s左右。

4.show profiles发现问题

给客户环境更新以后，发现接口耗时还是在20s左右，远未达到测试环境的1s。测试环境和客户环境的数据量是一个量级的情况下差距应该不会这么大。在客户环境执行一条SQL查看结果

耗时达到14秒。同等数据量在测试环境在1S内。逐排查：服务器cpu，内存，硬盘负载都正常，没问题。

走的索引，没问题。

show profiles，发现Sending data占据大部分时间。

sending data阶段并不单纯发送数据，还包括收集数据。文档介绍

Sending data The thread is reading and processing rows for a SELECT statement, and sending data to the client. Because operations occurring during this state tend to perform large amounts of disk access (reads), it is often the longest-running state over the lifetime of a given query.

频繁操作磁盘的情况下，该阶段耗时也会边长。所以查看buffer pool，默认的128M。