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

魏 然

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

0 引言

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

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

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

1 優(yōu)化SQL語句

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

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

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

2.1 建立合適的SQL語句

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

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

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

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

當不建立索引時，查找學工號為“21118320”和學工號為“21094100”的兩個學生信息，各查找10次，分別得出的查詢時間如表1所示。

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

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

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

圖2 以學工號建立索引的相應信息

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

(4)避免使用“＜ ＞”或“NOT”操作符?！埃迹尽笔桥懦庑缘牟僮鞣鼤瓜到y(tǒng)無法使用索引功能，而只能直接搜索表中的數(shù)據(jù)。如果要使用not運算符，應在取反的短語前面加上括號，并在短語前面加上not運算符。not運算符包含在另外一個邏輯運算符中，這就是“＜ ＞”運算符。

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

3.1 查詢條件書寫的注意事項

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

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

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

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

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

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

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

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

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

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

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

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

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

5 結(jié)束語

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

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

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

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

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

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

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

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