SUM與GROUPBY語(yǔ)句的優(yōu)化-創(chuàng)新互聯(lián)

一.SUM與GROUP BY語(yǔ)句的優(yōu)化：

創(chuàng)新互聯(lián)制作網(wǎng)站網(wǎng)頁(yè)找三站合一網(wǎng)站制作公司,專注于網(wǎng)頁(yè)設(shè)計(jì),成都網(wǎng)站制作、成都網(wǎng)站設(shè)計(jì),網(wǎng)站設(shè)計(jì),企業(yè)網(wǎng)站搭建,網(wǎng)站開(kāi)發(fā),建網(wǎng)站業(yè)務(wù),680元做網(wǎng)站,已為上千余家服務(wù),創(chuàng)新互聯(lián)網(wǎng)站建設(shè)將一如既往的為我們的客戶提供最優(yōu)質(zhì)的網(wǎng)站建設(shè)、網(wǎng)絡(luò)營(yíng)銷推廣服務(wù)!

1.原語(yǔ)句為：
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語(yǔ)句中使用GROUP BY的原因是select子句中有INVENTORY_ITEM_ID這個(gè)字段。

分析上面的SQL語(yǔ)句，INVENTORY_ITEM_ID作為WHERE子句中的條件，它的值是已知的，無(wú)需從SQL中查詢出來(lái)，所以可以把這個(gè)字段從select子句中去掉，同進(jìn)去除GROUP BY子句。SQL可改寫(xiě)為：
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.執(zhí)行計(jì)劃
(1)優(yōu)化前SQL語(yǔ)句的執(zhí)行計(jì)劃 (使用160001代替?的值，目的是看執(zhí)行計(jì)劃）
----------------------------------------------------------------------------------------------
| 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)優(yōu)化前SQL語(yǔ)句的執(zhí)行計(jì)劃
--------------------------------------------------------------------------------------------
| 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.執(zhí)行計(jì)劃分析
(1) SORT(AGGREGATE)
SORT(AGGREGATE)是指在沒(méi)有GROUP BY的前提下，使用統(tǒng)計(jì)函數(shù)對(duì)全部數(shù)據(jù)對(duì)象進(jìn)行運(yùn)算時(shí)所顯示出來(lái)的執(zhí)行計(jì)劃。在使用SUM、COUNT、MIN、MAX、AVG等統(tǒng)計(jì)函數(shù)時(shí)并不執(zhí)行一般排序。而是在讀取表中數(shù)據(jù)的同時(shí)以每一行數(shù)據(jù)為對(duì)象進(jìn)行求和，AVG或者COUNT也同樣是以所讀取的每一行數(shù)據(jù)為對(duì)象進(jìn)行反復(fù)計(jì)算的。
SORT AGGREGATE做為sort的option之一比較特殊，它并不做sort，SORT AGGREGATE作用于所有的data set上。

那么MIN和MAX是如何進(jìn)行計(jì)算的呢？其方法與上面所介紹的基本相同。MIN的執(zhí)行方法為在最開(kāi)始將所讀取的第一個(gè)值記錄下來(lái)，然后將該值與下一個(gè)讀取的值進(jìn)行比較，如果比該值小則將其替換。在執(zhí)行該統(tǒng)計(jì)操作時(shí)如果可以使用索引，則能夠獲得非常好的效果。在該執(zhí)行計(jì)劃中雖然顯示的是索引全掃描，但實(shí)際上，僅僅會(huì)讀取第一個(gè)索引塊，之后并不繼續(xù)進(jìn)行掃描（因?yàn)樗饕桥判虻?，因此索引列值的最小值必然在索引的第一個(gè)塊上）。而MAX則是僅僅讀取最后一個(gè)索引塊。想要獲得這樣的執(zhí)行計(jì)劃，不能使用WHERE和GROUP BY，當(dāng)然還要求所要進(jìn)行統(tǒng)計(jì)的列必須是索引的先行列（如果是組合索引，則該列必須位于最前面），并且在SELECT-List中不能添加其他任何額外的操作要求。

(2) SORT(GROUP BY)
該操作是將數(shù)據(jù)行向不同分組中聚集的操作，即依據(jù)查詢語(yǔ)句中所使用的GROUP BY而進(jìn)行的相關(guān)操作，為了進(jìn)行分組就只能進(jìn)行排序，因此所需分組的數(shù)據(jù)量越大則代價(jià)就越高。

如果想通過(guò)GROUP BY 將海量表分為上千個(gè)組，在這種對(duì)超大型表執(zhí)行GROUP BY時(shí)，就可以明顯地感覺(jué)到該操作所需要的代價(jià)。當(dāng)所要排序的數(shù)據(jù)超過(guò)一定量時(shí)，其代價(jià)就會(huì)變得非常大，解決該問(wèn)題的一個(gè)方法就是使用HASH(GROUP BY)。

(3) SORT(UNIQUE)
該操作是指把查詢語(yǔ)句的輸出結(jié)果變成唯一集合的過(guò)程。出現(xiàn)該排序的情況有兩種，一種是使用了“DISTINCT”，另外一種是子查詢以提供者角色向主查詢提供其執(zhí)行結(jié)果。

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);
由于主查詢的結(jié)果必須存在于子查詢中，在這里必須要將作為“M”集合的子查詢轉(zhuǎn)換為不允許重復(fù)元素存在的“1”集合，所以執(zhí)行了SORT(UNIQUE)操作。如果該子查詢被放在主查詢之后執(zhí)行，則該排序執(zhí)行計(jì)劃就不會(huì)被顯示出來(lái)，此時(shí)顯示的是FILTER。

4.另一種不修改程序和SQL語(yǔ)句的優(yōu)化方法

表INVENTORY_ITEM_DETAIL已有INVENTORY_ITEM_ID單個(gè)字段的索引，這個(gè)表共有26個(gè)字段，SUM只對(duì)其中三個(gè)字段進(jìn)行統(tǒng)計(jì)，通過(guò)創(chuàng)建下面的一個(gè)復(fù)合索引，性能有大幅提高。
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
);

經(jīng)驗(yàn)證測(cè)試，創(chuàng)建這個(gè)索引后，取款交易響應(yīng)時(shí)間從4.5s下降到2.0s；存款交易從2.7s下降到1.6s。效果明顯。（壓力測(cè)試環(huán)境中表INVENTORY_ITEM_DETAIL的記錄數(shù)是213萬(wàn)條記錄。測(cè)試機(jī)器是虛擬機(jī)）

5.把第3，4種優(yōu)化方法同時(shí)用上，性能更好。

另外有需要云服務(wù)器可以了解下創(chuàng)新互聯(lián)cdcxhl.cn，海內(nèi)外云服務(wù)器15元起步，三天無(wú)理由+7*72小時(shí)售后在線，公司持有idc許可證，提供“云服務(wù)器、裸金屬服務(wù)器、高防服務(wù)器、香港服務(wù)器、美國(guó)服務(wù)器、虛擬主機(jī)、免備案服務(wù)器”等云主機(jī)租用服務(wù)以及企業(yè)上云的綜合解決方案，具有“安全穩(wěn)定、簡(jiǎn)單易用、服務(wù)可用性高、性價(jià)比高”等特點(diǎn)與優(yōu)勢(shì)，專為企業(yè)上云打造定制，能夠滿足用戶豐富、多元化的應(yīng)用場(chǎng)景需求。

網(wǎng)頁(yè)題目：SUM與GROUPBY語(yǔ)句的優(yōu)化-創(chuàng)新互聯(lián)
分享網(wǎng)址：http://bm7419.com/article18/dpdggp.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供手機(jī)網(wǎng)站建設(shè)、域名注冊(cè)、微信小程序、營(yíng)銷型網(wǎng)站建設(shè)、虛擬主機(jī)、App設(shè)計(jì)

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)

成都app開(kāi)發(fā)公司