學(xué)生成績(jī)管理系統(tǒng)數(shù)據(jù)查詢優(yōu)化方法研究論文
0引言
互聯(lián)網(wǎng)時(shí)代，信息技術(shù)給人們的工作和生活帶來了極大的便利。一個(gè)功能完整的管理信息系統(tǒng)通常由兩部分組成，即前臺(tái)應(yīng)用程序和后臺(tái)數(shù)據(jù)庫。前臺(tái)應(yīng)用程序通過互聯(lián)網(wǎng)與后臺(tái)數(shù)據(jù)庫進(jìn)行數(shù)據(jù)交互，包括數(shù)據(jù)的增加、刪除、修改、查詢，這就是數(shù)據(jù)庫的4個(gè)基本操作，簡(jiǎn)稱CRUD操作。在數(shù)據(jù)庫的4個(gè)基本操作中，查詢是使用頻率最高的操作，因此查詢效率的高低直接關(guān)系到應(yīng)用程序性能的好壞。大數(shù)據(jù)時(shí)代數(shù)據(jù)量增長(zhǎng)很快，如果不注重?cái)?shù)據(jù)查詢效率的優(yōu)化，隨著數(shù)據(jù)量的逐漸增加，數(shù)據(jù)查詢效率將逐漸降低，最終將嚴(yán)重影響管理信息系統(tǒng)的用戶體驗(yàn)。因此，必須將數(shù)據(jù)查詢優(yōu)化作為管理信息系統(tǒng)開發(fā)與維護(hù)的重要內(nèi)容，貫穿系統(tǒng)生命周期始終。
1研究對(duì)象與方法
學(xué)生成績(jī)管理系統(tǒng)是一個(gè)典型的基于數(shù)據(jù)庫的管理信息系統(tǒng)，在教育管理中應(yīng)用非常廣泛。根據(jù)數(shù)據(jù)庫設(shè)計(jì)原則，按照第三范式的要求設(shè)計(jì)數(shù)據(jù)表結(jié)構(gòu)。學(xué)生成績(jī)管理系統(tǒng)數(shù)據(jù)庫的表結(jié)構(gòu)由成績(jī)表、學(xué)生表和課程表組成。成績(jī)表中包含學(xué)期、學(xué)號(hào)、課程號(hào)、成績(jī)等字段，學(xué)生表中包含學(xué)號(hào)、姓名、學(xué)籍表等字段，課程表中包含課程號(hào)、課程名稱、學(xué)時(shí)、學(xué)分等字段。
將學(xué)生成績(jī)管理系統(tǒng)數(shù)據(jù)庫部署在Windows Server 2008操作系統(tǒng)和 SQL Server 2008數(shù)據(jù)庫管理系統(tǒng)上。數(shù)據(jù)查詢一般通過SQL查詢語句來實(shí)現(xiàn)各種查詢邏輯。在外部環(huán)境保持穩(wěn)定的狀態(tài)下，數(shù)據(jù)查詢效率越高，SQL語句的執(zhí)行時(shí)間越短。因此，可以通過計(jì)算SQL語句的執(zhí)行時(shí)間來進(jìn)行數(shù)據(jù)查詢效率比較分析。
2數(shù)據(jù)查詢效率影響因素
由于數(shù)據(jù)查詢是一個(gè)前臺(tái)應(yīng)用程序與后臺(tái)數(shù)據(jù)庫的交互過程，涉及很多環(huán)節(jié)，因此影響數(shù)據(jù)查詢效率的因素有很多。歸納起來，影響數(shù)據(jù)查詢效率的因素主要來自物理層、數(shù)據(jù)庫層、應(yīng)用層[1] 3個(gè)層面。
在物理層，影響數(shù)據(jù)查詢效率的`主要因素包括服務(wù)器CPU性能、內(nèi)存、硬盤、網(wǎng)絡(luò)、操作系統(tǒng)等。在數(shù)據(jù)庫層，影響數(shù)據(jù)查詢效率的主要因素包括索引、視圖、數(shù)據(jù)存儲(chǔ)、數(shù)據(jù)冗余等。在應(yīng)用層，影響數(shù)據(jù)查詢效率的主要因素是SQL語句的寫法和應(yīng)用程序設(shè)計(jì)。
3數(shù)據(jù)查詢優(yōu)化方法
3.1物理層優(yōu)化方法
數(shù)據(jù)庫部署在服務(wù)器上，服務(wù)器性能的好壞直接影響查詢效率。針對(duì)物理層影響數(shù)據(jù)查詢效率的主要因素，可以采取以下優(yōu)化方法：
�。�1）提高CPU性能。CPU是計(jì)算機(jī)負(fù)責(zé)執(zhí)行指令和處理數(shù)據(jù)的核心部件。服務(wù)器性能的高低很大程度上由CPU的性能決定。數(shù)據(jù)庫的查詢操作特別依賴CPU的并行處理能力。因此，應(yīng)該為數(shù)據(jù)庫服務(wù)器配置高性能的CPU。
�。�2）增加內(nèi)存。數(shù)據(jù)查詢分為物理讀和邏輯讀，物理讀是從硬盤讀取數(shù)據(jù)到內(nèi)存緩沖區(qū)，邏輯讀是直接從內(nèi)存緩沖區(qū)中讀取數(shù)據(jù)。內(nèi)存的讀寫效率遠(yuǎn)遠(yuǎn)高于磁盤的讀寫效率，而且物理讀還會(huì)增加磁盤I/O操作。因此，為了保證數(shù)據(jù)查詢操作都能夠在內(nèi)存中完成，應(yīng)該盡量為數(shù)據(jù)庫服務(wù)器配置足夠多的物理內(nèi)存，同時(shí)要配置相應(yīng)的虛擬內(nèi)存。
（3）配置多塊硬盤。數(shù)據(jù)查詢操作需要大量的I/O操作，將I/O操作盡可能平均分配在多塊硬盤上才能有效提升硬盤的并行讀寫性能。因此，應(yīng)該為數(shù)據(jù)庫服務(wù)器配置多塊硬盤，避免使用單塊超大容量硬盤。
�。�4）提高網(wǎng)絡(luò)帶寬和網(wǎng)絡(luò)穩(wěn)定性。應(yīng)用程序與數(shù)據(jù)庫之間通過互聯(lián)網(wǎng)進(jìn)行數(shù)據(jù)交互，因此應(yīng)該提高數(shù)據(jù)庫服務(wù)器的網(wǎng)絡(luò)帶寬和網(wǎng)絡(luò)穩(wěn)定性。
�。�5）提高操作系統(tǒng)性能。數(shù)據(jù)庫管理系統(tǒng)安裝在操作系統(tǒng)上，應(yīng)該加強(qiáng)操作系統(tǒng)的管理與維護(hù)，提高操作系統(tǒng)性能。
3.2數(shù)據(jù)庫層優(yōu)化方法
數(shù)據(jù)庫管理系統(tǒng)負(fù)責(zé)SQL查詢指令的執(zhí)行，因此數(shù)據(jù)庫層的優(yōu)化是數(shù)據(jù)查詢優(yōu)化的核心，其對(duì)數(shù)據(jù)查詢效率影響最為顯著，可以采取以下優(yōu)化方法：
（1）索引優(yōu)化。索引是對(duì)數(shù)據(jù)庫表中一個(gè)或多個(gè)列的值預(yù)先進(jìn)行結(jié)構(gòu)排序。索引可以避免全表掃描，因而可顯著加快數(shù)據(jù)庫的查詢速度[2]。索引分為聚集索引和非聚集索引。與非聚集索引相比，聚集索引通常能夠提供更快的數(shù)據(jù)訪問速度。一般應(yīng)考慮將頻繁查詢、連接、排序或分組的列設(shè)為索引列，其中最頻繁操作的列設(shè)為聚集索引列，避免在數(shù)據(jù)量較小的表上建立索引，同時(shí)避免在頻繁進(jìn)行插入、刪除和修改操作的列上建立索引[3]。如成績(jī)表一般按學(xué)號(hào)查詢，因此將學(xué)號(hào)列設(shè)置為聚集索引列。同時(shí)根據(jù)需要將學(xué)期、課程號(hào)、成績(jī)等列設(shè)置為非聚集索引列。
�。�2）視圖優(yōu)化。視圖是由一個(gè)或者多個(gè)表組成的虛擬表。通過連接查詢（JOIN）和聯(lián)合查詢（UNION）建立視圖，可以實(shí)現(xiàn)數(shù)據(jù)庫中數(shù)據(jù)的合并與分割，極大方便了數(shù)據(jù)查詢。如成績(jī)管理系統(tǒng)中成績(jī)表分別與學(xué)生表和課程表進(jìn)行連接查詢，建立一個(gè)視圖，將學(xué)號(hào)、姓名、課程號(hào)、課程名稱、學(xué)時(shí)、學(xué)分、成績(jī)等信息放入一張?zhí)摂M表中，應(yīng)用程序查詢時(shí)只需要查詢?cè)撘晥D即可獲得所需數(shù)據(jù)。視圖查詢同時(shí)涉及多個(gè)物理表操作，當(dāng)數(shù)據(jù)量較大時(shí)，容易產(chǎn)生查詢效率低下的問題。為了提高查詢效率，視圖的定義深度一般不應(yīng)超過三層。若三層視圖不夠用，則應(yīng)在視圖上定義臨時(shí)表，在臨時(shí)表上再定義視圖。這樣反復(fù)交迭定義，視圖的深度就可以不受限制。既保留了視圖的便利性，又兼顧了查詢效率問題。
�。�3）數(shù)據(jù)存儲(chǔ)優(yōu)化。數(shù)據(jù)庫通常包括數(shù)據(jù)文件和日志文件。數(shù)據(jù)文件和日志文件默認(rèn)存儲(chǔ)在相同的位置。由于數(shù)據(jù)文件和日志文件的操作會(huì)產(chǎn)生大量的I/O，因此應(yīng)將日志文件與數(shù)據(jù)文件分別存儲(chǔ)在不同的硬盤上以分散I/O。通常情況下，數(shù)據(jù)庫默認(rèn)只有一個(gè)主數(shù)據(jù)文件，不生成次數(shù)據(jù)文件。為了提高查詢效率，必要時(shí)可以通過定義文件組把數(shù)據(jù)庫中的一些表分開存儲(chǔ)在不同的數(shù)據(jù)文件里，即增加次數(shù)據(jù)文件，同時(shí)把不同的數(shù)據(jù)文件分散存儲(chǔ)在不同的硬盤上[4]。此外，數(shù)據(jù)文件長(zhǎng)期自動(dòng)增長(zhǎng)可能產(chǎn)生碎片，導(dǎo)致物理空間與數(shù)據(jù)的邏輯空間不再連續(xù)。因此，有必要定期整理數(shù)據(jù)庫碎片，以提高數(shù)據(jù)庫查詢效率。
�。�4）適當(dāng)增加數(shù)據(jù)冗余。按照數(shù)據(jù)庫的設(shè)計(jì)原則，數(shù)據(jù)表應(yīng)該避免數(shù)據(jù)冗余。但是，為了提高數(shù)據(jù)的查詢效率，有時(shí)需要降低范式標(biāo)準(zhǔn)，適當(dāng)增加數(shù)據(jù)冗余，達(dá)到以空間換時(shí)間的目的。數(shù)據(jù)冗余包括字段冗余和表冗余。字段冗余是通過增加冗余字段，減少數(shù)據(jù)計(jì)算和連接查詢。如學(xué)生表中的性別和出生日期，雖然可以從身份證號(hào)中獲取，但是為了提高查詢效率，應(yīng)增加性別和出生日期字段。表冗余是通過增加冗余表提高查詢效率。以成績(jī)表為例，運(yùn)行多年的成績(jī)管理系統(tǒng)中保存了歷屆學(xué)生的成績(jī)信息，包括已經(jīng)畢業(yè)的學(xué)生成績(jī)信息，多年累積下來，數(shù)據(jù)量不斷增加，導(dǎo)致查詢效率降低。此時(shí)，可以考慮建立一個(gè)數(shù)據(jù)冗余表，其表結(jié)構(gòu)與成績(jī)表的視圖一致，但是只保存在校學(xué)生的成績(jī)信息，數(shù)據(jù)量會(huì)大大減少。對(duì)在校生的成績(jī)查詢只需要查詢?nèi)哂啾�，查詢效率大大提升。成�?jī)數(shù)據(jù)冗余表要能自動(dòng)更新，以便與成績(jī)表數(shù)據(jù)保持同步和一致�？衫肧QL Server 2008的代理服務(wù)功能，建立一個(gè)每天凌晨定時(shí)自動(dòng)執(zhí)行的作業(yè)，作業(yè)分為兩步：
step1：清空老數(shù)據(jù)，相關(guān)SQL語句為：
Truncate table cj_query
step2：插入新數(shù)據(jù)，更新冗余表，相關(guān)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為成績(jī)表、學(xué)生表、課程表作連接查詢建立的視圖*/
WHERE xjm = '01' /*在校學(xué)生的學(xué)籍碼為01*/
3.3應(yīng)用層優(yōu)化方法
應(yīng)用層涉及SQL語句的編寫和應(yīng)用程序的設(shè)計(jì)，其是否合理很大程度上會(huì)對(duì)數(shù)據(jù)查詢效率產(chǎn)生影響。針對(duì)應(yīng)用層影響數(shù)據(jù)查詢效率的主要因素，可以采取以下優(yōu)化方法：
�。�1）SQL語句寫法優(yōu)化。SQL語句優(yōu)化要注意的地方很多，總的原則是限制返回結(jié)果集，盡量避免全表掃描。返回結(jié)果集越大，邏輯讀數(shù)就越大，而且如果超出內(nèi)存緩沖區(qū)的容量，還需要增加物理讀數(shù)，從而增加磁盤I/O操作。因此應(yīng)該限制返回結(jié)果集的大小，包括行數(shù)和字段列數(shù)。全表掃描是指搜索表中的每一條記錄，直到所有符合給定條件的記錄返回為止，效率非常低下，因此應(yīng)該盡量避免全表掃描。根據(jù)優(yōu)化總原則， SQL語句優(yōu)化方法總結(jié)如下：①避免使用 select * from table，應(yīng)該用具體的字段代替“*”，不要返回任何用不到的字段；②盡量避免在where子句中使用！=、<>、not、in、or等運(yùn)算符，因?yàn)檫@些操作可能會(huì)引起全表掃描；③盡量避免在 where 子句中對(duì)字段進(jìn)行函數(shù)運(yùn)算和表達(dá)式運(yùn)算，這將導(dǎo)致數(shù)據(jù)庫放棄使用索引而進(jìn)行全表掃描；④盡量避免使用子查詢，如不能避免時(shí)，應(yīng)盡量減少子查詢的嵌套層次，并在子查詢中過濾掉盡可能多的行；⑤盡量避免使用外連接，因?yàn)橥膺B接必須對(duì)左表或右表查詢所有行，應(yīng)盡量使用內(nèi)連接；⑥合理使用臨時(shí)表和表變量，當(dāng)需要重復(fù)使用數(shù)據(jù)量較大的表中某個(gè)數(shù)據(jù)集時(shí)，應(yīng)當(dāng)考慮使用臨時(shí)表或表變量，這樣可以大大提高查詢效率[5]。表變量存儲(chǔ)在內(nèi)存中，臨時(shí)表存儲(chǔ)在系統(tǒng)數(shù)據(jù)庫tempdb中。對(duì)于較小的數(shù)據(jù)集考慮使用表變量，對(duì)于大數(shù)據(jù)集，由于內(nèi)存無法容納，使用表變量效率反而不高，應(yīng)該使用臨時(shí)表。同時(shí)，應(yīng)避免頻繁創(chuàng)建和刪除臨時(shí)表，以減少系統(tǒng)表資源的消耗。
（2）應(yīng)用程序設(shè)計(jì)優(yōu)化。應(yīng)用程序設(shè)計(jì)有時(shí)也會(huì)影響數(shù)據(jù)查詢效率。在可能的情況下，應(yīng)用程序應(yīng)盡量采用分頁設(shè)計(jì)，這樣可以分批多次獲取數(shù)據(jù)集，提高單次查詢響應(yīng)速度。同時(shí)，應(yīng)用程序設(shè)計(jì)時(shí)還需考慮并發(fā)性，防止出現(xiàn)數(shù)據(jù)庫鎖死和查詢阻塞現(xiàn)象。此外，對(duì)于Web應(yīng)用程序，還應(yīng)考慮使用數(shù)據(jù)緩存和局部刷新技術(shù)，減少數(shù)據(jù)查詢次數(shù)和查詢數(shù)據(jù)量。
4結(jié)語
大數(shù)據(jù)時(shí)代，數(shù)據(jù)庫中的數(shù)據(jù)量持續(xù)增加。為了保證信息管理系統(tǒng)始終具有快速的響應(yīng)速度和良好的用戶體驗(yàn)，必須深入研究數(shù)據(jù)查詢優(yōu)化技術(shù)。本文以學(xué)生成績(jī)管理系統(tǒng)為例，從物理層、數(shù)據(jù)庫層和應(yīng)用層3個(gè)方面分析了影響數(shù)據(jù)查詢效率的主要因素，給出了相應(yīng)的數(shù)據(jù)查詢優(yōu)化方法。實(shí)際應(yīng)用中，應(yīng)從多個(gè)方面綜合采取合適的優(yōu)化策略，才能有效提高數(shù)據(jù)查詢效率。
參考文獻(xiàn)參考文獻(xiàn)：
[1]劉輝蘭， 陳衛(wèi)東.數(shù)據(jù)查詢優(yōu)化技術(shù)的研究和探討[J].中國(guó)數(shù)字醫(yī)學(xué)， 2015 （7）：7274.
[2]林勤花.關(guān)系數(shù)據(jù)庫查詢優(yōu)化技術(shù)研究[J].電腦編程技巧與維護(hù)， 2014， 10（9）：3031.
[3]樊新華.關(guān)系數(shù)據(jù)庫的查詢優(yōu)化技術(shù)[J].計(jì)算機(jī)與數(shù)字工程， 2009， 37（12）：188192.
[4]馮衛(wèi)兵.關(guān)系數(shù)據(jù)庫的查詢優(yōu)化[J].現(xiàn)代計(jì)算機(jī)， 2010 （1）：3033.
[5]程學(xué)先，黃愛武.關(guān)系數(shù)據(jù)庫的查詢優(yōu)化技術(shù)[J].軟件導(dǎo)刊， 2007 （1）：7273.
【學(xué)生成績(jī)管理系統(tǒng)數(shù)據(jù)查詢優(yōu)化方法研究論文】相關(guān)文章：
略談優(yōu)化管理提高體育生成績(jī)研究論文10-18
數(shù)據(jù)采集系統(tǒng)設(shè)計(jì)研究論文04-13
SSH的學(xué)生成績(jī)信息管理系統(tǒng)的具體研究論文10-24
優(yōu)化設(shè)計(jì)方法的數(shù)值研究論文04-16
管理系統(tǒng)畢業(yè)研究論文04-02
灌溉管網(wǎng)優(yōu)化設(shè)計(jì)方法研究論文04-16
集群存儲(chǔ)系統(tǒng)數(shù)據(jù)安全研究論文04-14