sql 一条 GROUP BY 导致四千万的扫表。

SELECT
	TableT2.OrserSN,TableT2.OrderNO,TableT1.SNum
FROM
	(
		SELECT
			orderSn,
			orderNo,
			SUM (num) AS SNum
		FROM
			A
	    	GROUP BY
			orderSn,
			orderNo
	) TableT1
INNER JOIN TableT2 ON TableT1.orderSn = TbaleT2.OrderSn
AND TableT1.orderNo = TableT2.OrderNo
WHERE
	TableT2.C2 = 123;

后面还有很多表。其实 A 表就 20 十多万条数据，但是在这个查询里面有了一个 GROUP BY 却扫表四千多万次。一个简单的查询强行用了几分钟。试过几种优化方案，结果都不是很满意。业务不等人，那边都打开不了，最后发现其实这里他尝试在流水查询做了一个不必要的统计。也没有去深入研究，就把查询和统计分开。没有那一个子查询时间缩短到了 146 毫秒内。现在有时间了开始思考怎么用最优的方案去优化，大家有遇到类似的情况么？

第 1 条附言 · 2016-11-10 09:47:36 +08:00

从现在的优化来说用临时表可能是最好的方案。

第 2 条附言 · 2016-11-16 10:55:50 +08:00

现在找到一种非常标准的解决方法.

with a as
(
select OrderSn,OrderNo,SUM(num) Snum from jy_order GROUP BY OrderSn,OrderNo
)
select * from a inner join B ON a.orderSn=B.orderSn

解释如下
'''
WITH AS 短语，也叫做子查询部分（ subquery factoring ），可以让你做很多事情，定义一个 SQL 片断，该 SQL 片断会被整个 SQL 语句所用到。有的时候，是为了让 SQL 语句的可读性更高些，也有可能是在 UNION ALL 的不同部分，作为提供数据的部分。
特别对于 UNION ALL 比较有用。因为 UNION ALL 的每个部分可能相同，但是如果每个部分都去执行一遍的话，则成本太高，所以可以使用 WITH AS 短语，则只要执行一遍即可。如果 WITH AS 短语所定义的表名被调用两次以上，则优化器会自动将 WITH AS 短语所获取的数据放入一个 TEMP 表里，如果只是被调用一次，则不会。而提示 materialize 则是强制将 WITH AS 短语里的数据放入一个全局临时表里。很多查询通过这种方法都可以提高速度。
'''

14 条回复 • 2016-11-10 12:30:35 +08:00

cowman

2016-11-09 23:17:52 +08:00 via iPad

group by 的结果生成到临时表里

eyp82

2016-11-10 01:39:06 +08:00

首先你得说一下你用的是什么数据库, 什么版本.
根据数据库不同, 需要看看这条 SQL 的执行计划, 看有什么可疑的地方, 比如 Logical reads 特别高的步骤之类. 是否该走索引的地方走了全表扫描或者反之, 又或者 join 的方式不对.
还要看看每个表的数据量, 统计信息收集了没有, 是否过期, 等等.
-----
以上纯属满嘴跑火车, 遁走...

tjxjj

2016-11-10 03:15:59 +08:00

sql 本身没啥问题，纯粹设计问题

订单本身可以分成头。行，只是需要头信息的时候只扫描头表就行

一般系统都是这么设计的。

msg7086

2016-11-10 04:11:00 +08:00 via Android

有时候全交给数据库干是会变慢的…

jackyspy

2016-11-10 08:04:22 +08:00

如果 TableT2.C2 = 123 筛选结果非常小的，并且 A 有索引的情况下，还是先关联后 group 快一些。
不过子查询结果数据库应该自动缓存了，速度也不应该慢。
具体要看看执行计划

mcfog

2016-11-10 08:10:31 +08:00 via Android

这 sql 还叫简单吗…子查询， group by ， join 三大杀器一起来，对着主库做这样的查询就是找死…

一般而言订单交易数据在数据库里重复出现三四份都很正常的，主表用户维度索引 /分表，至少一套离线表商家 /商品类目维度索引 /分表，然后一些累积总量的统计表，再来点主从什么的

ebony0319

2016-11-10 08:52:12 +08:00

@eyp82 我网上查了一下其实都有这种情况，在 inner join 一个有 group by 的子表的时候。而过联接不是很多差异不是很明显，但是数据多，联表多的情况下问题就来了。我现在没有看到一个特别好的优化情况。
@cowman 这种情况我觉得是在必须统计的情况下这可能是一个比较不错的方案。

iam36

2016-11-10 08:56:23 +08:00

先做表行过滤，再做表关联。分两句或三句写
看起来就一张表做自关联（你那个 from a ）？是的话都不用关联，直接分组就好了。

zjsxwc

2016-11-10 10:03:36 +08:00

同意先对 group by 创建临时表

FifiLyu

2016-11-10 10:27:19 +08:00

最近，刚好在分析公司系统的性能问题。你这个 SQL 和我们开发写的 SQL 相比，算是简单的。我这儿的 SQL 有的 100 到 200 行。我只能呵呵哒！

但，单独来说，你这个 SQL 算是比较复杂的好吧！你现在别去分析什么原因导致查询慢。先把你的 SQL 拆分为多个可以优化或能使用索引 SQL 再说。

记住：
1. 能用 PHP 或 Java 等等代码循环的查找到数据的，就别用 SQL 去查。
2. SQL 能有多简单就用多简单。

ebony0319

2016-11-10 10:48:37 +08:00

@FifiLyu 麻烦你去看一下我的历史帖子。就是那个 update 那个。我这里是举了一个例子。这语句有六个 GROUP BY ， 10 个联结。

gainsurier

2016-11-10 11:11:05 +08:00 via Android

子查询别在 for 子句里啊，二十万×二十万的笛卡尔乘积不是闹着玩的。

ebony0319

2016-11-10 12:05:56 +08:00

@gainsurier 四千万原来这样来的哇？！

FifiLyu

2016-11-10 12:30:35 +08:00

@ebony0319 但是，这个并不表示不能拆分。只是需要考虑重构的成本问题，值不值得。有好的开发规范，从一开始就该避免类似的 SQL 。这样到了后期才好作优化。

只是实现业务功能，不考虑性能，到了后期是需要付出更多的代价的。