› MySQL 5.5 Community Server
› MySQL 5.6 Community Server
› Percona Configuration Wizard
› XtraBackup 搭建主从复制
Great Sites on MySQL
› Percona
› MySQL Performance Blog
› Severalnines
推荐管理工具
› Sequel Pro
› phpMyAdmin
推荐书目
› MySQL Cookbook
MySQL 相关项目
› MariaDB
› Drizzle
参考文档
› http://mysql-python.sourceforge.net/MySQLdb.html
MySQL 相关项目
› MariaDB
› Drizzle
参考文档
› http://mysql-python.sourceforge.net/MySQLdb.html
这是一个创建于 512 天前的主题，其中的信息可能已经有所发展或是发生改变。
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
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
 |
1
nice2cu OP 索引该建的都建了，但由于数据量比较大，还是慢，有啥建议吗
|
 |
2
zoharSoul 2023-08-04 14:18:35 +08:00  1
申请个从库在上面跑
你这慢就对了 |
|
3
nice2cu OP @zoharSoul 这个如果拆成俩 sql ，在内存去计算过滤，感觉也不太好，如果其中 sql 查出的数据量比较大的话
|
 |
5
T0m008 2023-08-04 14:36:22 +08:00
可以建临时表的话，就先建一个临时表，把这个 sql 拆开，会快很多
|
 |
6
jenlors 2023-08-04 14:54:21 +08:00
explain 贴一下，另外你这没用 join 吧
|
 |
7
ljsh093 2023-08-04 16:18:01 +08:00
用 join ，再看下能不能把 having 去了
|
 |
8
zjsxwc 2023-08-04 16:25:01 +08:00
|
|
9
zjsxwc 2023-08-04 16:27:26 +08:00
|
 |
10
wander555 2023-08-04 16:28:21 +08:00
group by 的原因，去重最好先查出来再去
|
 |
11
vovoim 2023-08-04 16:30:30 +08:00
上个 vespa engine ，在 vespa engine 上做统计查询
|
 |
12
xudaxian520bsz 2023-08-04 16:50:29 +08:00 1
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 |
|
14
zjsxwc 2023-08-04 17:23:21 +08:00 1
@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 过滤出满足的结果。 |
|
15
nice2cu OP @xudaxian520bsz num < a.product_num - a.contract_outstock_num 这个条件 sumNum > total 是不是意思不一样了
|
 |
17
xuanbg 2023-08-05 08:59:56 +08:00 1
数据量大是没办法优化的。。。
|
 |
18
wengyanbin 2023-08-05 11:33:19 +08:00
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
|
|
19
zjsxwc 2023-08-05 12:39:43 +08:00 via Android 1
@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 |
|
23
nice2cu OP 俩表数据量分别是 2000w
4000w |
Select Language