李北芳 黑龍江建筑職業(yè)技術(shù)學(xué)院 150008

評價系統(tǒng)的數(shù)據(jù)庫優(yōu)化查詢設(shè)計

李北芳 黑龍江建筑職業(yè)技術(shù)學(xué)院 150008

利用計算機對教學(xué)質(zhì)量進(jìn)行管理，是實現(xiàn)教學(xué)管理規(guī)范化和科學(xué)化的重要手段。本文以黑龍江省教育廳高職高專院?？茖W(xué)技術(shù)研究項目《高職院校教學(xué)質(zhì)量評價系統(tǒng)軟件的開發(fā)與應(yīng)用》項目編號：11525049為例，介紹了教學(xué)質(zhì)量評價系統(tǒng)數(shù)據(jù)庫優(yōu)化查詢的設(shè)計。

數(shù)據(jù)庫; 評價管理; 設(shè)計優(yōu)化

一、引言

數(shù)據(jù)庫是整個系統(tǒng)的核心，它的設(shè)計直接關(guān)系系統(tǒng)執(zhí)行的效率和系統(tǒng)的穩(wěn)定性。因此在軟件系統(tǒng)開發(fā)中，數(shù)據(jù)庫設(shè)計應(yīng)遵循必要的數(shù)據(jù)庫范式理論，以減少冗余、保證數(shù)據(jù)的完整性與正確性。只有在合適的數(shù)據(jù)庫產(chǎn)品上設(shè)計出合理的數(shù)據(jù)庫模型，才能降低整個系統(tǒng)的編程和維護(hù)難度，提高系統(tǒng)的實際運行效率。本文對教學(xué)質(zhì)量評價系統(tǒng)的數(shù)據(jù)查詢的優(yōu)化進(jìn)行探討。

二、基于第三范式的基本表設(shè)技

在基于表驅(qū)動的信息管理系統(tǒng)（MIS）中，基本表的設(shè)計規(guī)范是第三范式（3NF）。第三范式的基本特征是非主鍵屬性只依賴于主鍵屬性?；诘谌妒降臄?shù)據(jù)庫表設(shè)計具有很多優(yōu)點：一是消除了冗余數(shù)據(jù)，節(jié)省了磁盤存儲空間；二是有良好的數(shù)據(jù)完整性限制，即基于主外鍵的參照完整限制和基于主鍵的實體完整性限制，這使得數(shù)據(jù)容易維護(hù)，也容易移植和更新；三是數(shù)據(jù)的可逆性好，在做連接（Join）查詢或者合并表時不遺漏、也不重復(fù)；四是因消除了冗余數(shù)據(jù)（冗余列），在查詢（Select）時每個數(shù)據(jù)頁存的數(shù)據(jù)行就多，這樣就有效地減少了邏輯I/O，每個Cash存的頁面就多，也減少物理I/O；五是對大多數(shù)事務(wù)(Transaction)而言，運行性能好；六是物理設(shè)計(Physical Design)的機動性較大，能滿足日益增長的用戶需求。

在基本表設(shè)計中，表的主鍵、外鍵、索引設(shè)計占有非常重要的地位，但系統(tǒng)設(shè)計人員往往只注重于滿足用戶要求，而沒有從系統(tǒng)優(yōu)化的高度來認(rèn)識和重視它們。實際上，它們與系統(tǒng)的運行性能密切相關(guān)?，F(xiàn)在從系統(tǒng)數(shù)據(jù)庫優(yōu)化角度討論這些基本概念及其重要意義：

（1）主鍵(Primary Key)：主鍵被用于復(fù)雜的SQL語句時，頻繁地在數(shù)據(jù)訪問中被用到。一個表只有一個主鍵。主鍵應(yīng)該有固定值（不能為Null或缺省值，要有相對穩(wěn)定性），不含代碼信息，易訪問。把常用（眾所周知）的列作為主鍵才有意義。短主鍵最佳（小于25bytes），主鍵的長短影響索引的大小，索引的大小影響索引頁的大小，從而影響磁盤I/O。主鍵分為自然主鍵和人為主鍵。自然主鍵由實體的屬性構(gòu)成，自然主鍵可以是復(fù)合性的，在形成復(fù)合主鍵時，主鍵列不能太多，復(fù)合主鍵使得Join*作復(fù)雜化、也增加了外鍵表的大小。人為主鍵是在沒有合適的自然屬性鍵、或自然屬性復(fù)雜或靈敏度高時，人為形成的。人為主鍵一般是整型值（滿足最小化要求），沒有實際意義，也略微增加了表的大??；但減少了把它作為外鍵的表的大小。

（2）外鍵（Fo reig n Key）：外鍵的作用是建立關(guān)系型數(shù)據(jù)庫中表之間的關(guān)系（參照完整性），主鍵只能從獨立的實體遷移到非獨立的實體，成為后者的一個屬性，被稱為外鍵。

（3）索引(Index)：利用索引優(yōu)化系統(tǒng)性能是顯而易見的，對所有常用于查詢中的Where子句的列和所有用于排序的列創(chuàng)建索引，可以避免整表掃描或訪問，在不改變表的物理結(jié)構(gòu)的情況下，直接訪問特定的數(shù)據(jù)列，這樣減少數(shù)據(jù)存取時間；利用索引可以優(yōu)化或排除耗時的分類*作；把數(shù)據(jù)分散到不同的頁面上，就分散了插入的數(shù)據(jù)；主鍵自動建立了唯一索引，因此唯一索引也能確保數(shù)據(jù)的唯一性（即實體完整性）；索引碼越小，定位就越直接；新建的索引效能最好，因此定期更新索引非常必要。索引也有代價：有空間開銷，建立它也要花費時間，在進(jìn)行Insert、Delete和Update*作時，也有維護(hù)代價。索引有兩種：聚族索引和非聚族索引。一個表只能有一個聚族索引，可有多個非聚族索引。使用聚族索引查詢數(shù)據(jù)要比使用非聚族索引快。在建索引前，應(yīng)利用數(shù)據(jù)庫系統(tǒng)函數(shù)估算索引的大小。

① 聚族索引（Clustered Index）：聚族索引的數(shù)據(jù)頁按物理有序儲存，占用空間小。選擇策略是，被用于Where子句的列：包括范圍查詢、模糊查詢或高度重復(fù)的列（連續(xù)磁盤掃描）；被用于連接Join*作的列；被用于Order by和Group by子句的列。聚族索引不利于插入*作，另外沒有必要用主鍵建聚族索引。

② 非聚族索引（Non clustered In dex）：與聚族索引相比，占用空間大，而且效率低。選擇策略是，被用于Where子句的列：包括范圍查詢、模糊查詢（在沒有聚族索引時）、主鍵或外鍵列、點（指針類）或小范圍（返回的結(jié)果域小于整表數(shù)據(jù)的20%）查詢；被用于連接Join*作的列、主鍵列（范圍查詢）；被用于Order by和Group by子句的列；需要被覆蓋的列。對只讀表建多個非聚族索引有利。索引也有其弊端，一是創(chuàng)建索引要耗費時間，二是索引要占有大量磁盤空間，三是增加了維護(hù)代價（在修改帶索引的數(shù)據(jù)列時索引會減緩修改速度）。那么，在哪種情況下不建索引呢？對于小表（數(shù)據(jù)小于5頁）、小到中表（不直接訪問單行數(shù)據(jù)或結(jié)果集不用排序）、單值域（返回值密集）、索引列值太長（大于20bitys）、容易變化的列、高度重復(fù)的列、Null值列，對沒有被用于Where子語句和Join查詢的列都不能建索引。另外，對主要用于數(shù)據(jù)錄入的，盡可能少建索引。當(dāng)然，也要防止建立無效索引，當(dāng)Where語句中多于5個條件時，維護(hù)索引的開銷大于索引的效益，這時，建立臨時表存儲有關(guān)數(shù)據(jù)更有效。

（4）查詢優(yōu)化規(guī)則：在訪問數(shù)據(jù)庫表的數(shù)據(jù)(Access Data)時，要盡可能避免排序（Sort）、連接(Join)和相關(guān)子查詢*作。經(jīng)驗告訴我們，在優(yōu)化查詢時，必須做到：

① 盡可能少的行；

② 避免排序或為盡可能少的行排序，若要做大量數(shù)據(jù)排序，最好將相關(guān)數(shù)據(jù)放在臨時表中*作；用簡單的鍵（列）排序，如整型或短字符串排序；

③ 避免表內(nèi)的相關(guān)子查詢；

④ 避免在Where子句中使用復(fù)雜的表達(dá)式或非起始的子字符串、用長字符串連接；

⑤ 在Where子句中多使用“與”（And）連接，少使用“或”(Or)連接；

⑥ 利用臨時數(shù)據(jù)庫。在查詢多表、有多個連接、查詢復(fù)雜、數(shù)據(jù)要過濾時，可以建臨時表（索引）以減少I/O。但缺點是增加了空間開銷。

除非每個列都有索引支持，否則在有連接的查詢時分別找出兩個動態(tài)索引，放在工作表中重新排序。

三、數(shù)據(jù)庫優(yōu)化代碼的方法

數(shù)據(jù)庫在運行中，數(shù)據(jù)量在幾萬條紀(jì)錄以上，為了加快查詢速度，我們對數(shù)據(jù)庫進(jìn)行了優(yōu)化，優(yōu)化代碼如下：

sp_configure "total memory",32768/*根據(jù)內(nèi)存大小，修改此腳本,此處給Sybase64M內(nèi)存*/

通過優(yōu)化，數(shù)據(jù)庫的查詢速度大大加快，滿足了多臺計算機同時工作的要求。

四 小結(jié)

任何對列的操作都將導(dǎo)致表掃描，它包括數(shù)據(jù)庫函數(shù)、計算表達(dá)式等等，查詢時要盡可能將操作移至等號右邊。in、or子句常會使用工作表，使索引失效；如果不產(chǎn)生大量重復(fù)值，可以考慮把子句拆開；拆開的子句中應(yīng)該包含索引。要善于使用存儲過程，它使SQL變得更加靈活和高效。從以上這些例子可以看出，SQL優(yōu)化的實質(zhì)就是在結(jié)果正確的前提下，用優(yōu)化器可以識別的語句，充分利用索引，減少表掃描的I/O次數(shù)，盡量避免表搜索的發(fā)生。其實SQL的性能優(yōu)化是一個復(fù)雜的過程，上述這些只是在應(yīng)用層次的一種體現(xiàn)，深入研究還會涉及數(shù)據(jù)庫層的資源配置、網(wǎng)絡(luò)層的流量控制以及操作系統(tǒng)層的總體設(shè)計。

[1] 張厚粲，劉昕著.考試改革與標(biāo)準(zhǔn)參照測驗.遼寧教育出版社.1992年9月版

[2] 薛理銀 編著.教育信息處理原理.北京師范大學(xué)出版社.1996年3月版

[3] 陸建芳,韓莉莉.構(gòu)建地方文獻(xiàn)數(shù)據(jù)庫的思考.圖館館論壇 .2001(2)

[4] 曾燕 . 信息管理系統(tǒng)的開發(fā)與應(yīng)用.現(xiàn)代圖書情報技術(shù).2004（4）

10.3969/j.issn.1001-8972.2010.13.042

本文為黑龍江省教育廳高職高專院?？茖W(xué)技術(shù)研究項目《高職院校教學(xué)質(zhì)量評價系統(tǒng)軟件的開發(fā)與應(yīng)用》項目編號：11525049

李北芳（1962年6月）、女、大學(xué)本科、副教授、電氣自動化、數(shù)據(jù)庫方向。