函數(shù)局部掃描解決在線題庫海量數(shù)據(jù)讀取

汪磊

摘要：該文深入分析了局部掃描海量數(shù)據(jù)庫方法，實(shí)現(xiàn)了基于函數(shù)局部掃描解決在線題庫海量數(shù)據(jù)讀取的問題。給出了不同類型利用函數(shù)實(shí)現(xiàn)局部范圍掃描的SQL語句寫法，總結(jié)了函數(shù)使用需要注意的問題。

關(guān)鍵詞：函數(shù)；局部掃描；SOL；海量數(shù)據(jù)

中圖分類號(hào)：TP311 文獻(xiàn)標(biāo)識(shí)碼：A 文章編號(hào)：1009-3044（2016）29-0014-03

1概述

在數(shù)據(jù)處理過程中，最苦悶的事情是，很難找到提高大范圍數(shù)據(jù)處理執(zhí)行速度的對(duì)策，處理少量數(shù)據(jù)時(shí)，不論怎樣都能夠輕易地獲得較快的執(zhí)行速度；但對(duì)于大范圍的數(shù)據(jù)，則苦悶不已。那么，如果有完全不受數(shù)據(jù)處理范圍影響而始終能夠確?？焖賵?zhí)行的方法，則可以很輕松地應(yīng)對(duì)大范圍的數(shù)據(jù)處理了。

2問題的由來

職業(yè)院校通用在線考試平臺(tái)題庫，隨著近幾年的飛速建設(shè)，正穩(wěn)步走向大數(shù)據(jù)范圍，200余門的課程，每門課近千道試題，在組卷的過程中，準(zhǔn)確無誤的從海量數(shù)據(jù)中，查詢到符合組卷?xiàng)l件要求的試題，需要大量的對(duì)比運(yùn)算。隨著題庫數(shù)據(jù)量不斷增大，占用CUP的運(yùn)算時(shí)間也不斷增加，導(dǎo)制組卷速度的降低。

在線考試平臺(tái)能否適應(yīng)海量數(shù)據(jù)快速查詢，就是如何有效地使用SQL語句從數(shù)據(jù)庫中查詢所需要數(shù)據(jù)的問題。為了實(shí)現(xiàn)有效地使用SQL語句的目的而需要使用比較有效的方法來編寫SQL語句，通過這些有效的方法所編寫的SQL語句可以確保要求得到最大限度地滿足。由于能夠獲得相同結(jié)果的SQL的表現(xiàn)方式有很多，所以隨著所構(gòu)思的SQL的不同，處理數(shù)據(jù)的效率也存在著很大的差異。

在處理整體數(shù)據(jù)的同時(shí)尋找更加有效的方法固然重要，但如果只處理整體中的一部分也同樣可以獲得所期望的結(jié)果，應(yīng)當(dāng)沒有比它更好的方法了，也就是部分范圍掃描。也就是不用全部數(shù)據(jù)就可以獲得所期望的結(jié)果，在有的應(yīng)用中，它甚至可以將執(zhí)行效率提高到整體范圍處理的幾十陪之多。

3目前數(shù)據(jù)庫查詢過程中針對(duì)這一問題的解決方案

如果能夠有效利用局部范圍掃描，那么一直困惑不已的大范圍數(shù)據(jù)處理將不再是難題。但并非所有的情況都能夠?qū)崿F(xiàn)局部范圍掃描。目前普遍有以下兩種解決方案：

1）利用ROWNUM的局部范圍掃描

ROWNUM并不是物理存儲(chǔ)在表中的列，而是一種像SYS-DATE或USER等一樣可以被使用在任何SQL語句中的虛擬列。在SQL中無端添加ROWNUM查詢條件就好在某種程度上束縛了用戶所指定的查詢條件，一般在大部分的在線查詢中，無法預(yù)先知道滿足用戶指定查詢條件的最終結(jié)果行數(shù)。

2）利用嵌套視圖的局部范圍掃描。

運(yùn)用嵌套視圖來實(shí)現(xiàn)局部范圍掃描的原理是，將必須使用全部范圍掃描的部分捆綁在嵌套視圖中，以確保視圖之外的部分能夠以局部范圍掃描的方式來執(zhí)行。但是SQL除了嵌套視圖中的部分之外，其他部分是以局部掃描的方式執(zhí)行。盡管竭力引導(dǎo)優(yōu)化按照所期望的方式來生成執(zhí)行，但優(yōu)化器不能為SQL制定出所期望的執(zhí)行計(jì)劃。

兩種解決方案雖然在一定程度上實(shí)現(xiàn)了局部掃描，但都有著各自的局限性，很難滿足所有情況下的局部掃描。

4利用函數(shù)的局部范圍掃描

函數(shù)的典型特征就是在SQL中插入了過程化的處理內(nèi)容，這一特征具有非常重要的意義。如果能夠?qū)⑺械臄?shù)據(jù)處理都視為“集合運(yùn)算”，則只使用SQL就可以解決很多類型的數(shù)據(jù)處理問題?？墒?，由于使用過程化方式來解決問題，使用SQL處理的類型越復(fù)雜，需要使用全部范圍掃描的情況就會(huì)越多。

如何通過使用函數(shù)將這部分隱藏起來，從而避免向優(yōu)化器提供選擇使用全部范圍掃描的接口，以確保其始終能夠以局部范圍掃描方式來執(zhí)行。

1）使用標(biāo)題子查詢來實(shí)現(xiàn)局部范圍掃描的SQL轉(zhuǎn)換為使用函數(shù)來實(shí)現(xiàn)

在利用嵌套視圖的局部范圍掃描時(shí)，可以采用在SELECT-List中使用標(biāo)量子查詢，以進(jìn)一步對(duì)其優(yōu)化。

SELECT a.produet_ed，produet_name，

（SELECT SUM（stock_qty）/（：b2-：b1）

FROM PROD_STOCK b

WHERE b.produet_ed=a.produet_ed

AND b.stoek_date between：b1 and：b2）avg_stock

FROMPRODUCT a

WHERE eategory_ed=20

該方法雖然實(shí)現(xiàn)了嵌套視圖的局部范圍掃描的進(jìn)一步優(yōu)化，但由于有些版本中不支持該功能，其通用性不高，所在下面將其轉(zhuǎn)換為使用函數(shù)來實(shí)現(xiàn)。

盡管在該SQL中添加了一些復(fù)雜的過程化處理步驟，但是也沒有對(duì)局部范圍掃描造成任何不良影響。

2）利用局部范圍掃描對(duì)M集合的處理

將某個(gè)表和與其關(guān)系為1：M的表進(jìn)行連接后的結(jié)果為M集合，執(zhí)行連接的目的是為了實(shí)現(xiàn)，使用“M”邊的表檢驗(yàn)從“1”邊表中讀取的中間結(jié)果，為了將連接后的M集合簡(jiǎn)化為原來的1集合，需要執(zhí)行額外的GROUPBY、DISTINCT等操作，從而增加了執(zhí)行代價(jià)。

在不執(zhí)行表連接的情況下，可以通過使用子查詢來達(dá)到相同的目的。但子查詢過于復(fù)雜，要求執(zhí)行多過連接而使得執(zhí)速度比較低下時(shí)，就無法繼續(xù)使用這種方法。解決此類問題的最佳方法是使用函數(shù)。

利用函數(shù)既獲得了需要的結(jié)果，又實(shí)現(xiàn)了局部掃描，但WHERE中所使用的函數(shù)和SELECT-List中所用的函數(shù)雖然相同，但卻需要分別執(zhí)行。從邏輯角度來看，完全沒有重復(fù)執(zhí)行兩次。但函數(shù)的重復(fù)執(zhí)行對(duì)系統(tǒng)并不構(gòu)成負(fù)擔(dān)，則還是可以使用的。

3）特定部分的局部范圍掃描函數(shù)編寫

在實(shí)際組卷中，經(jīng)常會(huì)為了提高在線考試對(duì)大范圍題庫數(shù)據(jù)處理的執(zhí)行速度而絞盡腦汁。為了提高這種情況下的執(zhí)行速度，通常使用的方法是提前創(chuàng)建匯總表。但轉(zhuǎn)換一下思維，不將注意力集中到整體的SQL語句中，而采用分而治之的方法，以確保某些特定的部分可以按照局部范圍掃描的方式執(zhí)行，提高執(zhí)行速度。對(duì)于大數(shù)據(jù)而言，這種方法更加有效。

例如，課程題庫擁有12個(gè)章節(jié)，共計(jì)大概3000個(gè)題目，準(zhǔn)備查詢滿足條件部分題目信息，并要求按照題目的章節(jié)難易程度和題型進(jìn)行組卷。雖然在查詢之前無法知道滿足條件的題目個(gè)數(shù)，但如果按照題目難易程度和題型求匯總，則無法獲得最終結(jié)果，以及只有等所有的處理結(jié)束之后才能對(duì)其進(jìn)行組卷。但是對(duì)所要處理的數(shù)據(jù)行數(shù)進(jìn)行一下簡(jiǎn)單的計(jì)算就會(huì)發(fā)現(xiàn)，按照這種方式編寫的SQL根本不適用于在線查詢，其最少處理行數(shù)為3000*12*100=3600000行。

認(rèn)真分析一下，找出其中必須使用全部范圍掃描的部分。如果不要求按章節(jié)難易程度組卷，則可以采用首先只對(duì)將要向界面輸出的章節(jié)總額求匯總的方法。該方法與一次性對(duì)全部章節(jié)求匯總的方法相比，可以很大程度地減少所要處理的數(shù)據(jù)量。除了求每個(gè)題型第1章的題目必須按照整體范圍進(jìn)行處理之處，沒有必要對(duì)其他部分也按照整體范圍進(jìn)行處理，因此，可以先不將12個(gè)章節(jié)的題目一次性地全部進(jìn)行處理，而首先應(yīng)當(dāng)只處理所有題型第1章的數(shù)據(jù)，然后再分別處理第一次向界面輸出題型的剩下12個(gè)章節(jié)的數(shù)據(jù)。使用這種方法，能夠在相當(dāng)大的程度上縮減所要處理的數(shù)據(jù)量。

利用函數(shù)的解決方案如下：

在上面的SQL語句中，首先匯總出滿足特定條件的第1章節(jié)題目并對(duì)其按題型進(jìn)行排序。雖然這一部分使用了全部范圍掃描，但在按照局部范圍處理時(shí)只匯總了一部題型剩下11個(gè)章節(jié)的題目。雖然最開始處理第1章節(jié)題目數(shù)據(jù)時(shí)總處理行數(shù)為500*1*100=50000行，但是如果數(shù)組批量每次取10行數(shù)據(jù)，則在處理10個(gè)代理商剩下11個(gè)章節(jié)數(shù)據(jù)時(shí)的總處理行數(shù)卻只有10*11*100=1 1000行。

在線應(yīng)用程序中，按照全部范圍掃描的方式處理50000行數(shù)據(jù)雖然需要一定的開銷，但如果創(chuàng)建一個(gè)以“SAL_DEPT+SAL_DATE”為聚簇鍵的聚簇則開銷可以得到很大程度的降低。并且在每個(gè)處理中，由于每次中處理11000行數(shù)據(jù)，所以始終可以確保獲得較快的執(zhí)行速度。因此，這個(gè)方法不用額外創(chuàng)建匯總表也能夠獲得較快的執(zhí)行速度，對(duì)大范圍數(shù)據(jù)處理是一個(gè)非常有效的解決方案。

5使用函數(shù)實(shí)現(xiàn)局部掃描過程中的注意事項(xiàng)

函數(shù)雖然可以滿足過程化數(shù)據(jù)處理的要求，但是它既然是“函數(shù)”就無法擺脫函數(shù)本身所具有的特性，它的執(zhí)行過程是從接受輸入變量開始到返回一個(gè)最終結(jié)果為止。函數(shù)的最大缺陷就是每次只返回一個(gè)值，即使想一次返回多個(gè)值，但由于函數(shù)不具備這樣的功能，所以為達(dá)到目的，函數(shù)也只能被連續(xù)不斷地執(zhí)行多次，可在某種程度上是無法忍受的，因此就要把將要從函數(shù)中輸出的多個(gè)列連接成一個(gè)列來輸出。也就是把將要輸出的多個(gè)列結(jié)合成一個(gè)列，并將其作為一個(gè)常量來返回，然后在SQL中使用SUBSTR函數(shù)將這個(gè)結(jié)合在一起的列分解為原來的多個(gè)獨(dú)立列。使用該方法有如下幾個(gè)要注意的地方。

第一，為了便于對(duì)輸出的結(jié)合列進(jìn)行分解，應(yīng)當(dāng)在結(jié)合時(shí)為其指定固定長(zhǎng)度。

此時(shí)，可以使用TO_CHAR或RPAD函數(shù)來實(shí)現(xiàn)固定列長(zhǎng)度的目的。也可以在結(jié)合時(shí)為其指定界定符，然后再根據(jù)界定符來進(jìn)行分解。

第二，為了再次分解函數(shù)返回值而需要多次描述函數(shù)時(shí)，可能會(huì)導(dǎo)致函數(shù)被執(zhí)行多次。

函數(shù)就像表或視圖一樣，是具有獨(dú)立功能的對(duì)象。所謂的獨(dú)立不僅指不需要其他對(duì)象的幫助就可以獨(dú)立地輸出結(jié)果，而且還指在不同的應(yīng)用中始終以相同的模塊出現(xiàn)，同時(shí)也實(shí)事了共享目的。所謂共享模塊是指同一模塊必須能夠被使用在多個(gè)應(yīng)用程序中。

6結(jié)束語

本文的意義就在于，基于函數(shù)使用合適的方法控制執(zhí)行計(jì)劃來實(shí)現(xiàn)部分范圍掃描的目的，從而能夠獲得執(zhí)行效率的有效提高。