优化接口响应速度,提高sql执行时间

实习记录

今天发现了有一个接口相应时间太长,在发现接口速度执行太长的时候,可以查看是否有异常sql,也就是执行时间太长的sql,一开始的响应时长如下:
除了第一次响应需要4秒外,后续还需要2秒左右

我通过查看控制台的日志,忘记截图了,sql如下:

SELECT DISTINCT
	asa.id,
	`year`,
	avi.chapter AS period,
	subject_id,
	asa.balance_direction,
	asa.subject_balance_direction,
	subject_import_money AS money
FROM
	act_voucher_subject_amount AS asa
LEFT JOIN act_voucher_import AS avi ON avi.id = asa.voucher_id
WHERE
	asa.is_deleted = 0
and avi.is_deleted = 0
AND asa.balance_direction = asa.subject_balance_direction
AND avi.is_template = 0
AND avi.temp_id IS NULL
AND avi.client_id = 1705064918767923201
AND avi.`year` BETWEEN 2023
AND 2023

用navicat跑的结果

执行时间需要200ms,这里我尝试了几种方法,通过explain查看执行计划,尝试了为client_id添加索引都没有好的效果,于是就试着删除几个字段来查看时间

发现以下两个筛选条件随便去掉一个,都能极大的提高查询速度

avi.is_deleted = 0
avi.client_id = 1705064918767923201

去掉client_id
去掉is_deleted

我调试的接口共有三条类似的sql,就不列举了,操作如上,也是去掉一个where

然后根据业务情况,因为后续可能会有很多客户存在,所以我在这里选择了去掉is_deleted这个条件,通过向实体类添加一个字段,修改后的字段

以及新的查询语句如下,多查询一个is_deleted出来,后面在代码中筛选

SELECT DISTINCT
	asa.id,
	`year`,
	avi.chapter AS period,
	subject_id,
	balance_direction,
	subject_balance_direction,
	subject_import_money AS money,
	avi.is_deleted
FROM
	act_voucher_subject_amount AS asa
INNER JOIN act_voucher_import AS avi ON avi.id = asa.voucher_id
WHERE
	asa.is_deleted = 0
AND avi.is_template = 0
AND balance_direction = 1
AND avi.temp_id IS NULL
AND avi.client_id = 1705064918767923201
AND avi.`year` BETWEEN 2023
AND 2023

执行时间为:8ms

再通过代码将is_delete筛选出来以达到同样的效果

优化后查看控制台的sql执行时间

优化后接口响应时间如下:
平均响应时长快了1秒,速度优化了50%

总结

在遇到接口响应时间太长的时候,可以看看是否能够优化sql的执行速度,之后的逻辑交给代码来执行处理,会大大提高响应速度 另外,代码的逻辑也很重要,千万千万不能在循环里面使用sql语句!