实战:线上SQL查询200秒优化到1.6秒

实际项目中出现200s的SQL少之又少，以前的文章也记录过我在线上大表创建索引导致锁表引起过服务无响应，但这次是因为SQL联查过慢引起。

发现 :初步发现慢SQL是因为在线上调用RPC接口总会出现如下异常，

feign.RetryableException: Read timed out executing POST http://xxxx
说起来今天早上也报出了一样的异常，但因为是调用异常，我这边并没有监控到，但是rpc方监控到了相关接口的响应异常，

cat监控类型：URL
cat监控项：/api/xxxx
cat监控源地址：type=URL
TP95：130000.5ms
阈值：60000.0ms
最早我在上个月反应过这个问题，但是由于排期问题就搁置了，但是近期可能实在是太慢了就专门就此问题提出了优化。

图片

原始SQL

explain SELECT
t.origin_order_code,
t1.plan_date,
t.product_code,
t.product_name,
sum( t2.plan_qty ) plan_qty,
SUM( t2.shipped_qty ) shipped_qty,
IFNULL( sum( t3.qty ), 0 ) qty,
t.nuclear_goods_num,
t.update_by,
t.update_time,
t.unit
FROM
goods t
LEFT JOIN order_header t1 ON t.origin_order_code = t1.origin_order_code
LEFT JOIN order_item t2 ON t1.order_code = t2.order_code
AND t.product_code = t2.product_code
AND t1.send_location_code = t2.location_code
LEFT JOIN tainer_detail t3 ON t.origin_order_code = t3.sale_order_code
AND t.product_code = t3.product_code
AND t.warehouse_code = t3.warehouse_code
WHERE
t.warehouse_code = ‘XXX’
AND t1.send_location_code = ‘XXX’
AND t2.location_code = ‘XXX’
GROUP BY
t.origin_order_code,
t.product_code ;
在原始SQL中首先关联条件过少，在left条件中warehousecode字段并没有建立索引，当查询条件为小表数量时扫描行数如下

图片

但当切换条件时

图片

也就是说当前主驱动表过大，且没有用到索引导致查询过慢。页面上只有初始查询条件，所以提出由前端增加日期参数，且对where条件建立索引。此时已经有明显效果提升，但切换查询条件时仍然无效果，数据库查询响应仍然在40秒以上，经过分析在left的主表驱动数据过大，调整SQL如下

SELECT
*
FROM
order_header t1
LEFT JOIN order_item t2 ON t1.order_code = t2.order_code
LEFT JOIN goods t ON t1.send_location_code = t.warehouse_code
AND t2.order_code = t.origin_order_code
AND t2.product_code = t.product_code
LEFT JOIN wms_container_detail t3 ON t2.order_code = t3.sale_order_code
AND t2.product_code = t3.product_code
WHERE
send_location_code = ‘xxx’
AND plan_date = ‘2022-08-15’
分析结论

大表做关联查询时，要保证关联字段存在索引。如果没有索引，join关联查询效率会极低

绝大部分慢查询优化，降低主查询的扫描行数(修改sql查询逻辑，修改业务查询逻辑)

例：调整预计送货时间查询条件必填，降低主查询扫描行数

单表查询，对新加字段数据量过大情况，如果未增加索引，同样会引发慢查询场景

由于此优化不在我这边，只是做记录，优化过程希望能给大家带来思路