學生成績管理系統資料查詢最佳化方法研究論文
學生成績管理系統資料查詢最佳化方法研究論文
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.