在出入库历史查询的sql中,包含有视图及json的比较复杂的sql,由于需要反查状态等信息,导致查个25条数据,要5秒+的时间,这是无法忍受的。经过一天的优化,现在只需要130毫秒,优化效果刚刚的。
优化过程如下:
explain这个命令相当有用,用法即:
explain select语句
1、为关联查询中的关键字段建索引
用explain之后,查看key字段空的那些表关联,为这些表字段建立索引,类型选KEY,通过这找到了一些遗漏未建立的索引,提升了1秒左右的时间。整体查询时间提升到4秒+。
2、不使用视图关联查询
在sql语句中,与一张视图有关联查询,这视图里是 产品表 union 物料表 组成的,由于视无索引且关联的字段类型不一至,导致效率很低。后不用该视图,改为直接引用join来查询,这里效率提升了1秒,整体查询时间提升到了3秒+。
3、拆分临时数据集的关联查询,分开执行后再拼接
再次查看explain的结果,发现其中有这么一段sql
LEFT JOIN (
SELECT outid, JSON_ARRAYAGG(JSON_OBJECT('from_id',from_id,'from_no',from_no,'out_amount',outamount)) in_detail
FROM(
SELECT JSON_UNQUOTE(JSON_EXTRACT(CONCAT('[',a.out_detail,']'), CONCAT('$[', num-1, '].id'))) outid,
JSON_UNQUOTE(JSON_EXTRACT(CONCAT('[',a.out_detail,']'), CONCAT('$[', num-1, '].amount'))) outamount,
a.id from_id, a.stockno from_no
FROM `stock_record` a
RIGHT JOIN sum_num b ON b.num <= JSON_LENGTH( CONCAT('[',out_detail, ']'))
WHERE a.amount>0 AND NOT ISNULL(a.out_detail)
)a GROUP BY outid
) j ON a.id=j.outid
有对json数据解析后的一个临时数据集的关联查询,效率很低啊。不能没有啊。后把该段子sql与主sql分离出来执行,发现都是毫秒级别就执行完了,放在一起就要3秒+的时间。这好办了,那就拆开来分别执行,然后对用程序对两个数据集进行拼接。
这3步做完后,从前端取后端的数据,仅需130+毫秒。完美。