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

[nice2cu]

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

[nice2cu]

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

[zoharSoul]

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

[nice2cu]

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

[zoharSoul]

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

[T0m008]

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

[jenlors]

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

[ljsh093]

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

[zjsxwc]

拆 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]

@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]

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

[vovoim]

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

[xudaxian520bsz]

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]

@zjsxwc total 没取到

[zjsxwc]

@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]

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

[nice2cu]

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

[xuanbg]

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

[wengyanbin]

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]

@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]

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

[nice2cu]

@zjsxwc 好的 多谢大佬 我试试

[nice2cu]

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

[nice2cu]

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

[nice2cu]

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