一.SUM与GROUP BY语句的优化：

创新互联制作网站网页找三站合一网站制作公司,专注于网页设计,成都网站制作、成都网站设计,网站设计,企业网站搭建,网站开发,建网站业务,680元做网站,已为上千余家服务,创新互联网站建设将一如既往的为我们的客户提供最优质的网站建设、网络营销推广服务!

1.原语句为：
SELECT IID.INVENTORY_ITEM_ID, SUM(IID.AVAILABLE_TO_PROMISE_DIFF), SUM(IID.QUANTITY_ON_HAND_DIFF), SUM(IID.ACCOUNTING_QUANTITY_DIFF)
FROM BOSENT.INVENTORY_ITEM_DETAIL IID
WHERE ((IID.INVENTORY_ITEM_ID = '?'))
GROUP BY IID.INVENTORY_ITEM_ID

SQL语句中使用GROUP BY的原因是select子句中有INVENTORY_ITEM_ID这个字段。

分析上面的SQL语句，INVENTORY_ITEM_ID作为WHERE子句中的条件，它的值是已知的，无需从SQL中查询出来，所以可以把这个字段从select子句中去掉，同进去除GROUP BY子句。SQL可改写为：
SELECT SUM(IID.AVAILABLE_TO_PROMISE_DIFF), SUM(IID.QUANTITY_ON_HAND_DIFF), SUM(IID.ACCOUNTING_QUANTITY_DIFF)
FROM BOSENT.INVENTORY_ITEM_DETAIL IID
WHERE ((IID.INVENTORY_ITEM_ID = '?'))

2.执行计划
(1)优化前SQL语句的执行计划 (使用160001代替?的值，目的是看执行计划）
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 19 | 8008 (1)| 00:01:37 |
| 1 | SORT GROUP BY NOSORT| | 1 | 19 | 8008 (1)| 00:01:37 |
*| 2 | TABLE ACCESS FULL | INVENTORY_ITEM_DETAIL | 670K| 12M| 8008 (1)| 00:01:37 |
----------------------------------------------------------------------------------------------**

Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("IID"."INVENTORY_ITEM_ID"='160001')

(2)优化前SQL语句的执行计划
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 19 | 8008 (1)| 00:01:37 |
| 1 | SORT AGGREGATE | | 1 | 19 | | |
|* 2 | TABLE ACCESS FULL| INVENTORY_ITEM_DETAIL | 670K| 12M| 8008 (1)| 00:01:37 |
--------------------------------------------------------------------------------------------****

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("IID"."INVENTORY_ITEM_ID"='160001')

3.执行计划分析
(1) SORT(AGGREGATE)
SORT(AGGREGATE)是指在没有GROUP BY的前提下，使用统计函数对全部数据对象进行运算时所显示出来的执行计划。在使用SUM、COUNT、MIN、MAX、AVG等统计函数时并不执行一般排序。而是在读取表中数据的同时以每一行数据为对象进行求和，AVG或者COUNT也同样是以所读取的每一行数据为对象进行反复计算的。
SORT AGGREGATE做为sort的option之一比较特殊，它并不做sort，SORT AGGREGATE作用于所有的data set上。

那么MIN和MAX是如何进行计算的呢？其方法与上面所介绍的基本相同。MIN的执行方法为在最开始将所读取的第一个值记录下来，然后将该值与下一个读取的值进行比较，如果比该值小则将其替换。在执行该统计操作时如果可以使用索引，则能够获得非常好的效果。在该执行计划中虽然显示的是索引全扫描，但实际上，仅仅会读取第一个索引块，之后并不继续进行扫描（因为索引是排序的，因此索引列值的最小值必然在索引的第一个块上）。而MAX则是仅仅读取最后一个索引块。想要获得这样的执行计划，不能使用WHERE和GROUP BY，当然还要求所要进行统计的列必须是索引的先行列（如果是组合索引，则该列必须位于最前面），并且在SELECT-List中不能添加其他任何额外的操作要求。

(2) SORT(GROUP BY)
该操作是将数据行向不同分组中聚集的操作，即依据查询语句中所使用的GROUP BY而进行的相关操作，为了进行分组就只能进行排序，因此所需分组的数据量越大则代价就越高。

如果想通过GROUP BY 将海量表分为上千个组，在这种对超大型表执行GROUP BY时，就可以明显地感觉到该操作所需要的代价。当所要排序的数据超过一定量时，其代价就会变得非常大，解决该问题的一个方法就是使用HASH(GROUP BY)。

(3) SORT(UNIQUE)
该操作是指把查询语句的输出结果变成唯一集合的过程。出现该排序的情况有两种，一种是使用了“DISTINCT”，另外一种是子查询以提供者角色向主查询提供其执行结果。

SELECT order_id,order_date
FROM orders
WHERE order_id in (SELECT order_id
FROM order_item
WHERE item_id = :b1
AND order_qty>100);
由于主查询的结果必须存在于子查询中，在这里必须要将作为“M”集合的子查询转换为不允许重复元素存在的“1”集合，所以执行了SORT(UNIQUE)操作。如果该子查询被放在主查询之后执行，则该排序执行计划就不会被显示出来，此时显示的是FILTER。

4.另一种不修改程序和SQL语句的优化方法

表INVENTORY_ITEM_DETAIL已有INVENTORY_ITEM_ID单个字段的索引，这个表共有26个字段，SUM只对其中三个字段进行统计，通过创建下面的一个复合索引，性能有大幅提高。
CREATE INDEX
I_INVENTORY_ITEM_DETAIL_XX
ON
INVENTORY_ITEM_DETAIL
(
INVENTORY_ITEM_ID,AVAILABLE_TO_PROMISE_DIFF,QUANTITY_ON_HAND_DIFF,ACCOUNTING_QUANTITY_DIFF
);

经验证测试，创建这个索引后，取款交易响应时间从4.5s下降到2.0s；存款交易从2.7s下降到1.6s。效果明显。（压力测试环境中表INVENTORY_ITEM_DETAIL的记录数是213万条记录。测试机器是虚拟机）

5.把第3，4种优化方法同时用上，性能更好。

---

另外有需要云服务器可以了解下创新互联cdcxhl.cn，海内外云服务器15元起步，三天无理由+7*72小时售后在线，公司持有idc许可证，提供“云服务器、裸金属服务器、高防服务器、香港服务器、美国服务器、虚拟主机、免备案服务器”等云主机租用服务以及企业上云的综合解决方案，具有“安全稳定、简单易用、服务可用性高、性价比高”等特点与优势，专为企业上云打造定制，能够满足用户丰富、多元化的应用场景需求。

文章名称：SUM与GROUPBY语句的优化-创新互联
地址分享：http://lswzjz.com/article/dpdggp.html