基于SQL語句優(yōu)化的學(xué)生一卡通系統(tǒng)性能分析

魏 然

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

0 引言

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

一卡通系統(tǒng)主要分為三個(gè)部分:外部系統(tǒng)、數(shù)據(jù)中心、后臺操作系統(tǒng)。外部系統(tǒng)是一卡通的應(yīng)用子系統(tǒng)，主要通過第三方接入的方式和一卡通系統(tǒng)提供的接口相連接并交換數(shù)據(jù)，是實(shí)現(xiàn)一卡通系統(tǒng)各個(gè)功能的具體設(shè)備;數(shù)據(jù)中心存儲了一卡通系統(tǒng)中的所有數(shù)據(jù)信息，包括人員身份信息、交易流水信息等，這些數(shù)據(jù)信息是一卡通系統(tǒng)的數(shù)據(jù)源;后臺操作系統(tǒng)是管理員用來維護(hù)數(shù)據(jù)和排除外部系統(tǒng)訪問故障的內(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)用來存儲數(shù)據(jù)的大型數(shù)據(jù)庫，所有對數(shù)據(jù)的有效查詢都需要通過SQL命令以訪問數(shù)據(jù)中心的形式來實(shí)現(xiàn)。數(shù)據(jù)中心的查詢和修改是一卡通系統(tǒng)運(yùn)行的基礎(chǔ)，是系統(tǒng)最主要的工作，SQL查詢語句的性能高低直接決定了整個(gè)一卡通系統(tǒng)的性能好壞，因此要對一卡通系統(tǒng)進(jìn)行優(yōu)化，對SQL查詢語句進(jìn)行優(yōu)化就是一種可行而有效的方案。

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

1 優(yōu)化SQL語句

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

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

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

2.1 建立合適的SQL語句

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

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

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

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

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

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

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

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

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

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

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

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

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

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

語句1:where［卡賬戶余額］/10＞10

語句2:where［卡賬戶余額］＞100

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

(4)避免使用“＜ ＞”或“NOT”操作符。“＜＞”是排斥性的操作符，它會使系統(tǒng)無法使用索引功能，而只能直接搜索表中的數(shù)據(jù)。如果要使用not運(yùn)算符，應(yīng)在取反的短語前面加上括號，并在短語前面加上not運(yùn)算符。not運(yùn)算符包含在另外一個(gè)邏輯運(yùn)算符中，這就是“＜ ＞”運(yùn)算符。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

5 結(jié)束語

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

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

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

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

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

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

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

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