學生成績管理系統數據查詢優化方法研究論文

0引言

互聯網時代，信息技術給人們的工作和生活帶來了極大的便利。一個功能完整的管理信息系統通常由兩部分組成，即前台應用程序和後台數據庫。前台應用程序通過互聯網與後台數據庫進行數據交互，包括數據的增加、刪除、修改、查詢，這就是數據庫的4個基本操作，簡稱CRUD操作。在數據庫的4個基本操作中，查詢是使用頻率最高的操作，因此查詢效率的高低直接關係到應用程序性能的好壞。大數據時代數據量增長很快，如果不注重數據查詢效率的優化，隨着數據量的逐漸增加，數據查詢效率將逐漸降低，最終將嚴重影響管理信息系統的用户體驗。因此，必須將數據查詢優化作為管理信息系統開發與維護的重要內容，貫穿系統生命週期始終。

1研究對象與方法

學生成績管理系統是一個典型的基於數據庫的管理信息系統，在教育管理中應用非常廣泛。根據數據庫設計原則，按照第三範式的要求設計數據表結構。學生成績管理系統數據庫的表結構由成績表、學生表和課程表組成。成績表中包含學期、學號、課程號、成績等字段，學生表中包含學號、姓名、學籍表等字段，課程表中包含課程號、課程名稱、學時、學分等字段。

將學生成績管理系統數據庫部署在Windows Server 2008操作系統和 SQL Server 2008數據庫管理系統上。數據查詢一般通過SQL查詢語句來實現各種查詢邏輯。在外部環境保持穩定的狀態下，數據查詢效率越高，SQL語句的執行時間越短。因此，可以通過計算SQL語句的執行時間來進行數據查詢效率比較分析。

2數據查詢效率影響因素

由於數據查詢是一個前台應用程序與後台數據庫的交互過程，涉及很多環節，因此影響數據查詢效率的因素有很多。歸納起來，影響數據查詢效率的因素主要來自物理層、數據庫層、應用層[1] 3個層面。

在物理層，影響數據查詢效率的主要因素包括服務器CPU性能、內存、硬盤、網絡、操作系統等。在數據庫層，影響數據查詢效率的主要因素包括索引、視圖、數據存儲、數據宂餘等。在應用層，影響數據查詢效率的主要因素是SQL語句的寫法和應用程序設計。

3數據查詢優化方法

3.1物理層優化方法

數據庫部署在服務器上，服務器性能的好壞直接影響查詢效率。針對物理層影響數據查詢效率的主要因素，可以採取以下優化方法：

（1）提高CPU性能。CPU是計算機負責執行指令和處理數據的核心部件。服務器性能的高低很大程度上由CPU的性能決定。數據庫的查詢操作特別依賴CPU的並行處理能力。因此，應該為數據庫服務器配置高性能的CPU。

（2）增加內存。數據查詢分為物理讀和邏輯讀，物理讀是從硬盤讀取數據到內存緩衝區，邏輯讀是直接從內存緩衝區中讀取數據。內存的'讀寫效率遠遠高於磁盤的讀寫效率，而且物理讀還會增加磁盤I/O操作。因此，為了保證數據查詢操作都能夠在內存中完成，應該儘量為數據庫服務器配置足夠多的物理內存，同時要配置相應的虛擬內存。

（3）配置多塊硬盤。數據查詢操作需要大量的I/O操作，將I/O操作儘可能平均分配在多塊硬盤上才能有效提升硬盤的並行讀寫性能。因此，應該為數據庫服務器配置多塊硬盤，避免使用單塊超大容量硬盤。

（4）提高網絡帶寬和網絡穩定性。應用程序與數據庫之間通過互聯網進行數據交互，因此應該提高數據庫服務器的網絡帶寬和網絡穩定性。

（5）提高操作系統性能。數據庫管理系統安裝在操作系統上，應該加強操作系統的管理與維護，提高操作系統性能。

3.2數據庫層優化方法

數據庫管理系統負責SQL查詢指令的執行，因此數據庫層的優化是數據查詢優化的核心，其對數據查詢效率影響最為顯著，可以採取以下優化方法：

（1）索引優化。索引是對數據庫表中一個或多個列的值預先進行結構排序。索引可以避免全表掃描，因而可顯著加快數據庫的查詢速度[2]。索引分為聚集索引和非聚集索引。與非聚集索引相比，聚集索引通常能夠提供更快的數據訪問速度。一般應考慮將頻繁查詢、連接、排序或分組的列設為索引列，其中最頻繁操作的列設為聚集索引列，避免在數據量較小的表上建立索引，同時避免在頻繁進行插入、刪除和修改操作的列上建立索引[3]。如成績表一般按學號查詢，因此將學號列設置為聚集索引列。同時根據需要將學期、課程號、成績等列設置為非聚集索引列。

（2）視圖優化。視圖是由一個或者多個表組成的虛擬表。通過連接查詢（JOIN）和聯合查詢（UNION）建立視圖，可以實現數據庫中數據的合併與分割，極大方便了數據查詢。如成績管理系統中成績表分別與學生表和課程表進行連接查詢，建立一個視圖，將學號、姓名、課程號、課程名稱、學時、學分、成績等信息放入一張虛擬表中，應用程序查詢時只需要查詢該視圖即可獲得所需數據。視圖查詢同時涉及多個物理表操作，當數據量較大時，容易產生查詢效率低下的問題。為了提高查詢效率，視圖的定義深度一般不應超過三層。若三層視圖不夠用，則應在視圖上定義臨時表，在臨時表上再定義視圖。這樣反覆交迭定義，視圖的深度就可以不受限制。既保留了視圖的便利性，又兼顧了查詢效率問題。

（3）數據存儲優化。數據庫通常包括數據文件和日誌文件。數據文件和日誌文件默認存儲在相同的位置。由於數據文件和日誌文件的操作會產生大量的I/O，因此應將日誌文件與數據文件分別存儲在不同的硬盤上以分散I/O。通常情況下，數據庫默認只有一個主數據文件，不生成次數據文件。為了提高查詢效率，必要時可以通過定義文件組把數據庫中的一些表分開存儲在不同的數據文件裏，即增加次數據文件，同時把不同的數據文件分散存儲在不同的硬盤上[4]。此外，數據文件長期自動增長可能產生碎片，導致物理空間與數據的邏輯空間不再連續。因此，有必要定期整理數據庫碎片，以提高數據庫查詢效率。

（4）適當增加數據宂餘。按照數據庫的設計原則，數據表應該避免數據宂餘。但是，為了提高數據的查詢效率，有時需要降低範式標準，適當增加數據宂餘，達到以空間換時間的目的。數據宂餘包括字段宂餘和表宂餘。字段宂餘是通過增加宂餘字段，減少數據計算和連接查詢。如學生表中的性別和出生日期，雖然可以從身份證號中獲取，但是為了提高查詢效率，應增加性別和出生日期字段。表宂餘是通過增加宂餘表提高查詢效率。以成績表為例，運行多年的成績管理系統中保存了歷屆學生的成績信息，包括已經畢業的學生成績信息，多年累積下來，數據量不斷增加，導致查詢效率降低。此時，可以考慮建立一個數據宂餘表，其表結構與成績表的視圖一致，但是隻保存在校學生的成績信息，數據量會大大減少。對在校生的成績查詢只需要查詢宂餘表，查詢效率大大提升。成績數據宂餘表要能自動更新，以便與成績表數據保持同步和一致。可利用SQL Server 2008的代理服務功能，建立一個每天凌晨定時自動執行的作業，作業分為兩步：

step1：清空老數據，相關SQL語句為：

Truncate table cj_query

step2：插入新數據，更新宂餘表，相關SQL語句為：

INSERT INTO cj_query （xq，xh，xm，kch，kcmc，xs，xf，cj）

SELECT xq，xh，xm，kch，kcmc，xs，xf，cj

FROM cj_v /* cj_v為成績表、學生表、課程表作連接查詢建立的視圖*/

WHERE xjm = '01' /*在校學生的學籍碼為01*/

3.3應用層優化方法

應用層涉及SQL語句的編寫和應用程序的設計，其是否合理很大程度上會對數據查詢效率產生影響。針對應用層影響數據查詢效率的主要因素，可以採取以下優化方法：

（1）SQL語句寫法優化。SQL語句優化要注意的地方很多，總的原則是限制返回結果集，儘量避免全表掃描。返回結果集越大，邏輯讀數就越大，而且如果超出內存緩衝區的容量，還需要增加物理讀數，從而增加磁盤I/O操作。因此應該限制返回結果集的大小，包括行數和字段列數。全表掃描是指搜索表中的每一條記錄，直到所有符合給定條件的記錄返回為止，效率非常低下，因此應該儘量避免全表掃描。根據優化總原則， SQL語句優化方法總結如下：①避免使用 select * from table，應該用具體的字段代替“*”，不要返回任何用不到的字段；②儘量避免在where子句中使用！=、<>、not、in、or等運算符，因為這些操作可能會引起全表掃描；③儘量避免在 where 子句中對字段進行函數運算和表達式運算，這將導致數據庫放棄使用索引而進行全表掃描；④儘量避免使用子查詢，如不能避免時，應儘量減少子查詢的嵌套層次，並在子查詢中過濾掉儘可能多的行；⑤儘量避免使用外連接，因為外連接必須對左表或右表查詢所有行，應儘量使用內連接；⑥合理使用臨時表和表變量，當需要重複使用數據量較大的表中某個數據集時，應當考慮使用臨時表或表變量，這樣可以大大提高查詢效率[5]。表變量存儲在內存中，臨時表存儲在系統數據庫tempdb中。對於較小的數據集考慮使用表變量，對於大數據集，由於內存無法容納，使用表變量效率反而不高，應該使用臨時表。同時，應避免頻繁創建和刪除臨時表，以減少系統表資源的消耗。

（2）應用程序設計優化。應用程序設計有時也會影響數據查詢效率。在可能的情況下，應用程序應儘量採用分頁設計，這樣可以分批多次獲取數據集，提高單次查詢響應速度。同時，應用程序設計時還需考慮併發性，防止出現數據庫鎖死和查詢阻塞現象。此外，對於Web應用程序，還應考慮使用數據緩存和局部刷新技術，減少數據查詢次數和查詢數據量。

4結語

大數據時代，數據庫中的數據量持續增加。為了保證信息管理系統始終具有快速的響應速度和良好的用户體驗，必須深入研究數據查詢優化技術。本文以學生成績管理系統為例，從物理層、數據庫層和應用層3個方面分析了影響數據查詢效率的主要因素，給出了相應的數據查詢優化方法。實際應用中，應從多個方面綜合採取合適的優化策略，才能有效提高數據查詢效率。

參考文獻參考文獻：

[1]劉輝蘭， 陳衞東.數據查詢優化技術的研究和探討[J].中國數字醫學， 2015 （7）：7274.

[2]林勤花.關係數據庫查詢優化技術研究[J].電腦編程技巧與維護， 2014， 10（9）：3031.

[3]樊新華.關係數據庫的查詢優化技術[J].計算機與數字工程， 2009， 37（12）：188192.

[4]馮衞兵.關係數據庫的查詢優化[J].現代計算機， 2010 （1）：3033.

[5]程學先，黃愛武.關係數據庫的查詢優化技術[J].軟件導刊， 2007 （1）：7273.