mysql 的 sql 优化，佬们有啥建议，被监控出慢查询

SELECT a.status, a.contract_id, a.product_id, b.num AS total , SUM(a.product_num - a.contract_outstock_num) AS sumNum FROM tableA a, tableB b
WHERE a.corpid = ? AND a.del = ? AND b.corpid = ? AND b.del = ? AND a.product_id = b.id AND a.is_produce = ? AND a.type = ?
GROUP BY a.contract_id, a.product_id
HAVING sumNum > total
ORDER BY a.id, a.product_id, a.contract_id

uct

and

odu

sumnum

24 replies • 2023-08-08 11:28:43 +08:00

nice2cu

Aug 4, 2023

索引该建的都建了，但由于数据量比较大，还是慢，有啥建议吗

zoharSoul

Aug 4, 2023

申请个从库在上面跑
你这慢就对了

nice2cu

Aug 4, 2023

@zoharSoul 这个如果拆成俩 sql ，在内存去计算过滤，感觉也不太好，如果其中 sql 查出的数据量比较大的话

zoharSoul

Aug 4, 2023

@nice2cu #3 所以就别拆.
申请个从库在上面跑好了

T0m008

Aug 4, 2023

可以建临时表的话，就先建一个临时表，把这个 sql 拆开，会快很多

jenlors

Aug 4, 2023

explain 贴一下，另外你这没用 join 吧

ljsh093

Aug 4, 2023

用 join ，再看下能不能把 having 去了

zjsxwc

Aug 4, 2023

拆 2 个 sql 不行吗？

1. select b.id from B where b.corpid = ? AND b.del = ?
得到 b 的 id 集合 bIdList

2. select a.x,a.y..,SUM() as sumNum from a where a.corpid = ? AND a.del = ? AND a.product_id IN (bIdList) GROUP BY a.contract_id, a.product_id
HAVING sumNum > total
ORDER BY a.id, a.product_id, a.contract_id

zjsxwc

Aug 4, 2023

@zjsxwc #6 合成一个 sql

select a.x,a.y..,SUM() as sumNum from a where a.corpid = ? AND a.del = ? AND a.product_id IN (

select b.id from B where b.corpid = ? AND b.del = ?

) GROUP BY a.contract_id, a.product_id
HAVING sumNum > total
ORDER BY a.id, a.product_id, a.contract_id

wander555

Aug 4, 2023

group by 的原因，去重最好先查出来再去

vovoim

Aug 4, 2023

上个 vespa engine ，在 vespa engine 上做统计查询

xudaxian520bsz

Aug 4, 2023

SELECT a.status, a.contract_id, a.product_id, b.num AS total, SUM(a.product_num - a.contract_outstock_num) AS sumNum
FROM tableA a
INNER JOIN tableB b ON a.product_id = b.id
WHERE a.corpid = ? AND a.del = ? AND b.corpid = ? AND b.del = ? AND a.is_produce = ? AND a.type = ?
GROUP BY a.contract_id, a.product_id
HAVING EXISTS(SELECT 1 FROM tableB WHERE id = a.product_id AND num < a.product_num - a.contract_outstock_num)
ORDER BY a.id, a.product_id, a.contract_id

nice2cu

Aug 4, 2023

@zjsxwc total 没取到

zjsxwc

Aug 4, 2023

@nice2cu 确实我漏了 total ，

还是拆 2 个 sql ，但需要加一步客户端程序自己过滤。

1. select b.id,b.num as total from B where b.corpid = ? AND b.del = ?
得到 b 的 id 集合 bIdList 与 b.id 对应的 total 数据 map 比如 totalByBId = {
<bId> : total
}

2. select a.x,a.y..,SUM() as sumNum from a where a.corpid = ? AND a.del = ? AND a.product_id IN (bIdList) GROUP BY a.contract_id, a.product_id
ORDER BY a.id, a.product_id, a.contract_id

对 2 的结果通过 totalByBId 过滤出满足的结果。

nice2cu

Aug 4, 2023

@xudaxian520bsz num < a.product_num - a.contract_outstock_num 这个条件 sumNum > total 是不是意思不一样了

nice2cu

Aug 4, 2023

@zjsxwc 可能第一个查询的数据会很多加到内存算感觉会是个隐患

xuanbg

Aug 5, 2023

数据量大是没办法优化的。。。

wengyanbin

Aug 5, 2023

select t.*,b.num from (select a.status, a.contract_id, a.product_id, sum() from table a where a.corpid = ? AND a.del = ? group by a.contract_id, a.product_id) as t,table b where t.product_id=b.id and t.sumNum>b.num

zjsxwc

Aug 5, 2023 via Android

@nice2cu 不知道用 left join 会不会好一点，起码不会和你最开始直接 from 两个表搞笛卡尔积的性能那么差。

SELECT a.status, a.contract_id, a.product_id, total , SUM(a.product_num - a.contract_outstock_num) AS sumNum

FROM tableA a
LEFT JOIN ( select B.id as bid, B.num as total from B where B.corpid = ? AND B.del = ? ) tb
ON tb.bid=a.product_id

WHERE a.corpid = ? AND a.del = ? AND a.is_produce = ? AND a.type = ?

GROUP BY a.contract_id, a.product_id
HAVING sumNum > total
ORDER BY a.id, a.product_id, a.contract_id

xchaoinfo

Aug 6, 2023 via Android

同意 @zjsxwc 的思路，改成子查询然后 id 去重，速度应该提高很多

nice2cu

Aug 7, 2023

@zjsxwc 好的多谢大佬我试试

nice2cu

Aug 7, 2023

@zjsxwc 生产试了下从 1.7s 优化到了 0.2s 感谢大佬

nice2cu

Aug 7, 2023

俩表数据量分别是 2000w
4000w

nice2cu

Aug 8, 2023

@zjsxwc 佬，再问个问题现在我们生产开启了查询缓存
比如我有个 sql a 执行时间 2s
sql b 是基于 sql a 我加了个 and 1==1 执行时间才几毫秒
两个 sql 的结果是一样的这是为啥呀
现在验证不了优化的时间了