魏 然

(淮陰工學(xué)院現(xiàn)代教育技術(shù)中心，江蘇淮安 223001)

0 引言

校園一卡通系統(tǒng)是指凡是涉及到現(xiàn)金、票據(jù)或者是需要識(shí)別身份的場(chǎng)合均采用智能IC卡來(lái)完成的系統(tǒng)。這種管理模式代替了院校各部門(mén)“各自為戰(zhàn)”的局面，統(tǒng)一規(guī)范化的管理和便捷的操作模式為廣大師生帶來(lái)便捷、高效的服務(wù)，與此同時(shí)也使學(xué)校建立了便捷、高效的管理模式。

一卡通系統(tǒng)主要分為三個(gè)部分:外部系統(tǒng)、數(shù)據(jù)中心、后臺(tái)操作系統(tǒng)。外部系統(tǒng)是一卡通的應(yīng)用子系統(tǒng)，主要通過(guò)第三方接入的方式和一卡通系統(tǒng)提供的接口相連接并交換數(shù)據(jù)，是實(shí)現(xiàn)一卡通系統(tǒng)各個(gè)功能的具體設(shè)備;數(shù)據(jù)中心存儲(chǔ)了一卡通系統(tǒng)中的所有數(shù)據(jù)信息，包括人員身份信息、交易流水信息等，這些數(shù)據(jù)信息是一卡通系統(tǒng)的數(shù)據(jù)源;后臺(tái)操作系統(tǒng)是管理員用來(lái)維護(hù)數(shù)據(jù)和排除外部系統(tǒng)訪問(wèn)故障的內(nèi)部系統(tǒng)，是一卡通系統(tǒng)平穩(wěn)運(yùn)行的重要保障。一卡通系統(tǒng)的結(jié)構(gòu)框圖如圖1所示。

在一卡通系統(tǒng)中，最核心的部分是數(shù)據(jù)中心，數(shù)據(jù)中心本質(zhì)上是系統(tǒng)用來(lái)存儲(chǔ)數(shù)據(jù)的大型數(shù)據(jù)庫(kù)，所有對(duì)數(shù)據(jù)的有效查詢都需要通過(guò)SQL命令以訪問(wèn)數(shù)據(jù)中心的形式來(lái)實(shí)現(xiàn)。數(shù)據(jù)中心的查詢和修改是一卡通系統(tǒng)運(yùn)行的基礎(chǔ)，是系統(tǒng)最主要的工作，SQL查詢語(yǔ)句的性能高低直接決定了整個(gè)一卡通系統(tǒng)的性能好壞，因此要對(duì)一卡通系統(tǒng)進(jìn)行優(yōu)化，對(duì)SQL查詢語(yǔ)句進(jìn)行優(yōu)化就是一種可行而有效的方案。

圖1 學(xué)生一卡通系統(tǒng)結(jié)構(gòu)框圖

1 優(yōu)化SQL語(yǔ)句

優(yōu)化SQL語(yǔ)句，就是要求在保證輸出結(jié)果正確的前提下，將運(yùn)行效率低下的SQL語(yǔ)句轉(zhuǎn)換為功能相同、性能相對(duì)高效的SQL語(yǔ)句，以達(dá)到數(shù)據(jù)查找路徑最簡(jiǎn)化的目的，同時(shí)還要使I/O的訪問(wèn)時(shí)間和CPU的工作時(shí)間保持同步。

在進(jìn)行語(yǔ)句性能優(yōu)化時(shí)，首先應(yīng)重點(diǎn)關(guān)注最有可能降低性能的因素，比如以下幾個(gè)方面:低效率的SQL執(zhí)行計(jì)劃、低效率的應(yīng)用SQL、SGA內(nèi)存結(jié)構(gòu)大小不合適、數(shù)據(jù)庫(kù)資源紊亂、文件I/O數(shù)目過(guò)多等等。效率差的SQL語(yǔ)句會(huì)導(dǎo)致系統(tǒng)出現(xiàn)上述問(wèn)題。因此，數(shù)據(jù)庫(kù)系統(tǒng)的性能主要由SQL語(yǔ)句的執(zhí)行效率決定，通過(guò)對(duì)有問(wèn)題的SQL語(yǔ)句進(jìn)行調(diào)整和優(yōu)化，可以顯著改善一個(gè)系統(tǒng)的性能。

2 優(yōu)化SQL語(yǔ)句的基本原則

2.1 建立合適的SQL語(yǔ)句

在一個(gè)數(shù)據(jù)庫(kù)中，如果有數(shù)條查詢語(yǔ)句頻繁執(zhí)行，并且這幾條查詢語(yǔ)句的執(zhí)行效率較低，那么這個(gè)數(shù)據(jù)庫(kù)一定是繁瑣而失敗的，因此在建立數(shù)據(jù)庫(kù)時(shí)，必須要使用合適的SQL語(yǔ)句，以保證數(shù)據(jù)庫(kù)具有較高的執(zhí)行效率。

首先，需要建立的是索引結(jié)構(gòu)，一個(gè)好的索引是優(yōu)化查詢實(shí)現(xiàn)的首要前提，使用索引的根本目的是為了提高查詢效率，使用索引時(shí)必須遵循相應(yīng)的原則，并且用戶表空間不能與索引建立在同一磁盤(pán)上，因?yàn)檫@樣會(huì)加劇I/O之間的競(jìng)爭(zhēng)，從而降低I/O的性能。

例如:在一個(gè)有2萬(wàn)2千多條學(xué)生信息的“一卡通”系統(tǒng)中，查找某一個(gè)學(xué)生的“一卡通”號(hào)，編寫(xiě)的SQL語(yǔ)句如下:

SELECT［帳號(hào)］，［學(xué)工號(hào)］，［姓名］，［性別］，［部門(mén)］，［證件號(hào)］，［卡賬戶余額］，［卡內(nèi)余額］，［更新日期］，［有效日期］，［卡狀態(tài)］，［掛失標(biāo)志］，［凍結(jié)標(biāo)志］，［卡號(hào)］

當(dāng)不建立索引時(shí)，查找學(xué)工號(hào)為“21118320”和學(xué)工號(hào)為“21094100”的兩個(gè)學(xué)生信息，各查找10次，分別得出的查詢時(shí)間如表1所示。

表1 不使用索引的查詢時(shí)間統(tǒng)計(jì)(單位:ms)

在表1中，由于語(yǔ)句3的精度不夠，只能精確到1毫秒，因此0毫秒的含義是小于1毫秒，并不是不需要時(shí)間。

在建立學(xué)工號(hào)字段的索引后，對(duì)學(xué)工號(hào)建立聚合索引，然后在統(tǒng)計(jì)信息中查看索引的相應(yīng)信息，如圖2所示。

圖2 以學(xué)工號(hào)建立索引的相應(yīng)信息

重新執(zhí)行2.1節(jié)中的SQL語(yǔ)句，得到如表2所示的結(jié)果。

表2 使用學(xué)工號(hào)索引的查詢時(shí)間統(tǒng)計(jì)(單位:ms)

通過(guò)比較表1和表2可以看出，使用了聚合索引后，查詢速度有了較大的提高。

其次，要避免過(guò)多層次的嵌套查詢語(yǔ)句出現(xiàn)。對(duì)于查詢過(guò)程來(lái)說(shuō)，查詢嵌套層次越多，效率越低。比如一個(gè)字段同時(shí)在主查詢和Where子句的查詢中出現(xiàn)，那么當(dāng)主查詢中某個(gè)字段的值改變后，子查詢必須重新查詢一次。因此應(yīng)盡量避免出現(xiàn)上述情況，若不可避免，則應(yīng)在子查詢中縮減盡可能多的行。

再次，要避免條件判斷字段語(yǔ)句在表達(dá)式中被使用。例如要查詢卡賬戶余額大于100的人員，比較以下兩種語(yǔ)句的性能:

語(yǔ)句1:where［卡賬戶余額］/10＞10

語(yǔ)句2:where［卡賬戶余額］＞100

兩種語(yǔ)句都是在有索引的條件下執(zhí)行的，索引是建立在學(xué)工號(hào)字段上的聚合索引，同樣各運(yùn)行10次，其結(jié)果見(jiàn)表3。

表3 兩種語(yǔ)句的查詢時(shí)間統(tǒng)計(jì)(單位:ms)

從表3中可以看出，語(yǔ)句1運(yùn)行的效率較低，原因是語(yǔ)句1中where條件后面的語(yǔ)句對(duì)表達(dá)式中的條件字段進(jìn)行了操作，因此在使用SQL語(yǔ)句掃描的時(shí)候就必須要進(jìn)行全表掃描，此時(shí)索引沒(méi)有被使用，因此效率比較低。任何對(duì)列的操作都會(huì)導(dǎo)致全表掃描，因此在查詢的時(shí)候應(yīng)該盡量將操作移動(dòng)到等號(hào)的右邊進(jìn)行。

最后，需要?jiǎng)?chuàng)建并使用臨時(shí)表。在表的一個(gè)子集中進(jìn)行排序并創(chuàng)建臨時(shí)表，也能實(shí)現(xiàn)加速查詢，這樣可以在某些特定的情況下避免多重排序操作。但所創(chuàng)建的臨時(shí)表的行要比主表的行少，要求按照物理順序來(lái)排序，這樣就減少了輸入和輸出的個(gè)數(shù)，降低了查詢的工作量，提高了效率，而且臨時(shí)表的創(chuàng)建并不會(huì)反映主表的修改情況。

2.2 提高SQL語(yǔ)句的執(zhí)行效率

在編寫(xiě)SQL語(yǔ)句的過(guò)程中，相同的查詢盡量保持格式一致，包括字母的大小寫(xiě)、標(biāo)點(diǎn)符號(hào)、換行的位置等都要一致，同時(shí)還要注意四個(gè)方面的問(wèn)題，以提高SQL語(yǔ)句的執(zhí)行效率。

(1)編寫(xiě)SQL語(yǔ)句時(shí)應(yīng)避免使用不兼容的數(shù)據(jù)類(lèi)型。數(shù)據(jù)類(lèi)型的不兼容可能使優(yōu)化器無(wú)法執(zhí)行一些本來(lái)可以進(jìn)行的優(yōu)化操作，因此在編程時(shí)應(yīng)將數(shù)據(jù)類(lèi)型轉(zhuǎn)化成相互兼容的類(lèi)型，而不是在運(yùn)行時(shí)再轉(zhuǎn)化。

對(duì)于上述例子，在執(zhí)行查詢:where［卡賬戶余額］＞100時(shí)，這里的卡賬戶的數(shù)據(jù)類(lèi)型是float型，如果改成char型，那執(zhí)行情況就會(huì)有所不同，因?yàn)閮?yōu)化是用char類(lèi)型的和float類(lèi)型的字段進(jìn)行比較，優(yōu)化器很難對(duì)其進(jìn)行區(qū)別和優(yōu)化，所以應(yīng)當(dāng)在編程的時(shí)候?qū)⑵湓O(shè)定為同一種格式。

(2)使用where語(yǔ)句。在where查詢語(yǔ)句中，有兩種格式的子查詢。

第一種是使用in操作符，where F1 in(select*from…where…);第二種是使用exist操作符，where F1 in(select* from…where…)。在 where語(yǔ)句中，要盡量使用exist而不是使用in，因?yàn)槭褂胑xist操作符的時(shí)候，數(shù)據(jù)庫(kù)系統(tǒng)會(huì)先運(yùn)行主查詢，然后運(yùn)行子查詢，直到找到第一個(gè)匹配項(xiàng)，這樣就節(jié)省了時(shí)間。

(3)避免順序存取。在嵌套查詢中，對(duì)表的順序存取，查詢效率可能會(huì)受到很大的影響。比如采用順序存取策略，一個(gè)嵌套N層的查詢，如果每層都查詢M行，那么這個(gè)查詢就要查詢(M)N行數(shù)據(jù)。避免這種情況的主要方法是對(duì)連接的列進(jìn)行索引，利用索引路徑處理查詢。

還有一種可行的方法，就是使用并集(UNION命令)來(lái)避免順序存取，比較如下兩組語(yǔ)句:

按照同樣的方法各執(zhí)行10次，表4給出了是否使用并集的查詢時(shí)間統(tǒng)計(jì)數(shù)據(jù)。

表4 是否使用并集的查詢時(shí)間統(tǒng)計(jì)(單位:ms)

根據(jù)表4的數(shù)據(jù)，第2組語(yǔ)句的性能略優(yōu)于第 1組語(yǔ)句，雖然在學(xué)工號(hào)和卡賬戶余額上都建有索引，但是由于第1組語(yǔ)句要檢索的是分離行的集合，所以還要順序掃描整個(gè)表。由于實(shí)際采用的數(shù)據(jù)庫(kù)容量是2萬(wàn)多條記錄，如果數(shù)據(jù)量達(dá)到數(shù)十萬(wàn)或者數(shù)百萬(wàn)，將會(huì)更好地體現(xiàn)性能的優(yōu)越性。

(4)避免使用“＜ ＞”或“NOT”操作符?！埃迹尽笔桥懦庑缘牟僮鞣?，它會(huì)使系統(tǒng)無(wú)法使用索引功能，而只能直接搜索表中的數(shù)據(jù)。如果要使用not運(yùn)算符，應(yīng)在取反的短語(yǔ)前面加上括號(hào)，并在短語(yǔ)前面加上not運(yùn)算符。not運(yùn)算符包含在另外一個(gè)邏輯運(yùn)算符中，這就是“＜ ＞”運(yùn)算符。

3 SQL語(yǔ)句優(yōu)化的注意事項(xiàng)

3.1 查詢條件書(shū)寫(xiě)的注意事項(xiàng)

在編寫(xiě)SQL語(yǔ)句時(shí)，應(yīng)注意在搜索子句的字段名時(shí)要避免使用函數(shù)、算術(shù)操作符和其它的表達(dá)式，避免使用不兼容的數(shù)據(jù)類(lèi)型。在使用復(fù)合索引的第一個(gè)字段名時(shí)，應(yīng)給優(yōu)化器提供盡可能多的查詢條件，建議使用索引的檢索條件。

3.2 SQL編碼的應(yīng)用調(diào)優(yōu)

SQL編碼的應(yīng)用調(diào)優(yōu)需注意六個(gè)方面的問(wèn)題。

(1)對(duì)于頻繁進(jìn)行更新和插入的表，比如狀態(tài)監(jiān)控表和日志表，使用行級(jí)鎖;

(2)對(duì)于頻繁進(jìn)行更新和插入的數(shù)據(jù)量表，比如日志表，要考慮所建索引的數(shù)量，避免建過(guò)多的索引(會(huì)降低插入和更新操作的執(zhí)行效率，并且占用大量的存儲(chǔ)空間，可能比原表還大);

(3)對(duì)于字段較多并且包含 memo、text、image等記錄長(zhǎng)度較大的數(shù)據(jù)表，在查詢時(shí)要考慮執(zhí)行效率，比如對(duì)于批量數(shù)據(jù)查詢，只需返回有限的幾個(gè)字段(減少I(mǎi)/O和執(zhí)行時(shí)間)，對(duì)于數(shù)據(jù)的精確查詢則返回全部字段的值;

(4)在應(yīng)用中對(duì)業(yè)務(wù)的處理引入事務(wù)機(jī)制和異常處理機(jī)制，保證業(yè)務(wù)處理的完整性;

(5)考慮主外鍵約束條件的使用，使用觸發(fā)器或事務(wù)處理方式來(lái)保證參照?qǐng)?zhí)行的完整性;

(6)對(duì)于數(shù)據(jù)量較小的表，可以將數(shù)據(jù)一次送到內(nèi)存中，然后再進(jìn)行計(jì)算和查找，比如采用樹(shù)形結(jié)構(gòu)。

4 SQL相關(guān)參數(shù)的設(shè)置

首先，需要注意的是TIMED_STATXSTICS參數(shù)，該參數(shù)用于收集操作系統(tǒng)的計(jì)時(shí)信息，這些信息可被用來(lái)優(yōu)化數(shù)據(jù)庫(kù)和SQL語(yǔ)句，默認(rèn)值為FALSE。為了減少因操作系統(tǒng)請(qǐng)求時(shí)間過(guò)長(zhǎng)而引起的中斷，同時(shí)又能夠得到定時(shí)信息，可將其設(shè)置為T(mén)RUE。

其次，要關(guān)注的是USER_DUMP_DEST參數(shù)，一旦設(shè)為會(huì)話，則激活了SQL_TRACE，Oracle就會(huì)在Udump管理區(qū)創(chuàng)建調(diào)試跟蹤文件，文件的目標(biāo)位置由初始化參數(shù)USER_DUMP_DEST來(lái)確定。將參數(shù)USER_DUMP_DEST的值設(shè)為oracleadminDemoDBudump。

最后，需要注意的是PARTITION_VEIW_ENABLED參數(shù)，若該參數(shù)值為T(mén)RUE，那么優(yōu)化器會(huì)跳過(guò)(或剪除)分區(qū)視圖中未被請(qǐng)求的分區(qū)，默認(rèn)為FALSE。為更好地發(fā)揮分區(qū)表的性能，加快查詢速度，將其設(shè)置為T(mén)RUE。

5 結(jié)束語(yǔ)

在學(xué)生一卡通應(yīng)用系統(tǒng)中，通過(guò)建立索引的方法優(yōu)化SQL語(yǔ)句是提高系統(tǒng)性能的一種有效方法，此外還需要合理的使用嵌套查詢語(yǔ)句以及在編寫(xiě)SQL語(yǔ)句的時(shí)候遵守語(yǔ)法規(guī)范，并設(shè)置好SQL的相關(guān)參數(shù)。通過(guò)這幾個(gè)方面的優(yōu)化，一卡通系統(tǒng)的性能將會(huì)得到極大的提高。

［1］楊昌堯.數(shù)據(jù)庫(kù)查詢優(yōu)化方法研究［J］.電腦知識(shí)與技術(shù)，2011，7(17):4011-4012.

［2］張欣.基于SQL標(biāo)準(zhǔn)的關(guān)系數(shù)據(jù)庫(kù)查詢優(yōu)化技術(shù)研究［J］.煤炭技術(shù)，2011，30(12):284-286.

［3］周建鴻.海量數(shù)據(jù)庫(kù)的查詢優(yōu)化研究及實(shí)現(xiàn)［J］.西南民族大學(xué)學(xué)報(bào)，2010，36(4):620-623.

［4］邢政科.數(shù)據(jù)庫(kù)優(yōu)化技術(shù)的分析與探討［J］.電腦知識(shí)與技術(shù)，2011，7(20):4794-4795.

［5］張?jiān)娪?，陳汶濱.基于索引的SQL語(yǔ)句優(yōu)化方法探討［J］.計(jì)算機(jī)與現(xiàn)代化，2009(3):134-136.

［6］李展?jié)?，曹英?基于Oracle數(shù)據(jù)庫(kù)的SQL語(yǔ)句優(yōu)化［J］.微型機(jī)與應(yīng)用，2009，30(21):11-13.

［7］盤(pán)青梅.大型表SQL查詢的優(yōu)化與用戶編寫(xiě)策略［J］.太原城市職業(yè)技術(shù)學(xué)院學(xué)報(bào)，2011(8):171-172.