學(xué)生成績管理系統(tǒng)數(shù)據(jù)查詢優(yōu)化方法研究-2019年文檔

學(xué)生成績管理系統(tǒng)數(shù)據(jù)查詢優(yōu)化方法研究DOIDOI：10.11907/rjdk.161581基金項目基金項目：南通大學(xué)校級自然科學(xué)類科研基金一般項目（13Z034）；南通大學(xué)研究生教育教學(xué)改革研究與實踐項目（YJG14011）0引言互聯(lián)網(wǎng)時代，信息技術(shù)給人們的工作和生活帶來了極大的便利。一個功能完整的管理信息系統(tǒng)通常由兩部分組成，即前臺應(yīng)用程序和后臺數(shù)據(jù)庫。前臺應(yīng)用程序通過互聯(lián)網(wǎng)與后臺數(shù)據(jù)庫進(jìn)行數(shù)據(jù)交互，包括數(shù)據(jù)的增加、刪除、修改、查詢，這就是數(shù)據(jù)庫的4個基本操作，簡稱CRUD操作。在數(shù)據(jù)庫的4個基本操作中，查詢是使用頻率最高的操作，因此查詢效率的高低直接關(guān)系到應(yīng)用程序性能的好壞。大數(shù)據(jù)時代數(shù)據(jù)量增長很快，如果不注重數(shù)據(jù)查詢效率的優(yōu)化，隨著數(shù)據(jù)量的逐漸增加，數(shù)據(jù)查詢效率將逐漸降低，最終將嚴(yán)重影響管理信息系統(tǒng)的用戶體驗。因此，必須將數(shù)據(jù)查詢優(yōu)化作為管理信息系統(tǒng)開發(fā)與維護(hù)的重要內(nèi)容，貫穿系統(tǒng)生命周期始終。1研究對象與方法學(xué)生成績管理系統(tǒng)是一個典型的基于數(shù)據(jù)庫的管理信息系統(tǒng)，在教育管理中應(yīng)用非常廣泛。根據(jù)數(shù)據(jù)庫設(shè)計原則，按照第三范式的要求設(shè)計數(shù)據(jù)表結(jié)構(gòu)。學(xué)生成績管理系統(tǒng)數(shù)據(jù)庫的表結(jié)構(gòu)由成績表、學(xué)生表和課程表組成。成績表中包含學(xué)期、學(xué)號、課程號、成績等字段，學(xué)生表中包含學(xué)號、姓名、學(xué)籍表等字段，課程表中包含課程號、課程名稱、學(xué)時、學(xué)分等字段。將學(xué)生成績管理系統(tǒng)數(shù)據(jù)庫部署在WindowsServer2008操作系統(tǒng)和SQLServer2008數(shù)據(jù)庫管理系統(tǒng)上。數(shù)據(jù)查詢一般通過SQL查詢語句來實現(xiàn)各種查詢邏輯。在外部環(huán)境保持穩(wěn)定的狀態(tài)下，數(shù)據(jù)查詢效率越高，SQL語句的執(zhí)行時間越短。因此，可以通過計算SQL語句的執(zhí)行時間來進(jìn)行數(shù)據(jù)查詢效率比較分析。2數(shù)據(jù)查詢效率影響因素由于數(shù)據(jù)查詢是一個前臺應(yīng)用程序與后臺數(shù)據(jù)庫的交互過程，涉及很多環(huán)節(jié)，因此影響數(shù)據(jù)查詢效率的因素有很多。歸納起來，影響數(shù)據(jù)查詢效率的因素主要來自物理層、數(shù)據(jù)庫層、應(yīng)用層[1]3個層面。在物理層，影響數(shù)據(jù)查詢效率的主要因素包括服務(wù)器CPU性能、內(nèi)存、硬盤、網(wǎng)絡(luò)、操作系統(tǒng)等。在數(shù)據(jù)庫層，影響數(shù)據(jù)查詢效率的主要因素包括索引、視圖、數(shù)據(jù)存儲、數(shù)據(jù)冗余等。在應(yīng)用層，影響數(shù)據(jù)查詢效率的主要因素是SQL語句的寫法和應(yīng)用程序設(shè)計。3數(shù)據(jù)查詢優(yōu)化方法3.1物理層優(yōu)化方法數(shù)據(jù)庫部署在服務(wù)器上，服務(wù)器性能的好壞直接影響查詢效斷增加，導(dǎo)致查詢效率降低。此時，可以考慮建立一個數(shù)據(jù)冗余表，其表結(jié)構(gòu)與成績表的視圖一致，但是只保存在校學(xué)生的成績信息，數(shù)據(jù)量會大大減少。對在校生的成績查詢只需要查詢?nèi)哂啾?，查詢效率大大提升。成績?shù)據(jù)冗余表要能自動更新，以便與成績表數(shù)據(jù)保持同步和一致?？衫肧QLServer2008的代理服務(wù)功能，建立一個每天凌晨定時自動執(zhí)行的作業(yè)，作業(yè)分為兩步：step1：清空老數(shù)據(jù)，相關(guān)SQL語句為：Truncatetablecj_querystep2：插入新數(shù)據(jù)，更新冗余表，相關(guān)SQL語句為：INSERTINTOcj_query（xq，xh，xm，kch，kcmc，xs，xf，cj）SELECTxq，xh，xm，kch，kcmc，xs，xf，cjFROMcj_v/*cj_v為成績表、學(xué)生表、課程表作連接查詢建立的視圖*/WHERExjm='01'/*在校學(xué)生的學(xué)籍碼為01*/3.3應(yīng)用層優(yōu)化方法應(yīng)用層涉及SQL語句的編寫和應(yīng)用程序的設(shè)計，其是否合理很大程度上會對數(shù)據(jù)查詢效率產(chǎn)生影響。針對應(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*fromtable，應(yīng)該用具體的字段代替“*”，不要返回任何用不到的字段；②盡量避免在where子句中使用！=、、not、in、or等運(yùn)算符，因為這些操作可能會引起全表掃描；③盡量避免在where子句中對字段進(jìn)行函數(shù)運(yùn)算和表達(dá)式運(yùn)算，這將導(dǎo)致數(shù)據(jù)庫放棄使用索引而進(jìn)行全表掃描；④盡量避免使用子查詢，如不能避免時，應(yīng)盡量減少子查詢的嵌套層次，并在子查詢中過濾掉盡可能多的行；⑤盡量避免使用外連接，因為外連接必須對左表或右表查詢所有行，應(yīng)盡量使用內(nèi)連接；⑥合理使用臨時表和表變量，當(dāng)需要重復(fù)使用數(shù)據(jù)量較大的表中某個數(shù)據(jù)集時，應(yīng)當(dāng)考慮使用臨時表或表變量，這樣可以大大提高查詢效率[5]。表變量存儲在內(nèi)存中，臨時表存儲在系統(tǒng)數(shù)據(jù)庫tempdb中。對于較小的數(shù)據(jù)集考慮使用表變量，對于大數(shù)據(jù)集，由于內(nèi)存無法容納，使用表變量效率反而不高，應(yīng)該使用臨時表。同時，應(yīng)避免頻繁創(chuàng)建和刪除臨時表，以減少系統(tǒng)表資源的消耗。（2）應(yīng)用程序設(shè)計優(yōu)化。應(yīng)用程序設(shè)計有時也會影響數(shù)據(jù)查詢效率。在可能的情況下，應(yīng)用程序應(yīng)盡量采用分頁設(shè)計，這樣可以分批多次獲取數(shù)據(jù)集，提高單次查詢響應(yīng)速度。同時，應(yīng)用程序設(shè)計時還需考慮并發(fā)性，防止出現(xiàn)數(shù)據(jù)庫鎖死和查詢阻塞現(xiàn)象。此外，對于Web應(yīng)用程序，還應(yīng)考慮使用數(shù)據(jù)緩存和局部刷新技術(shù)，減少數(shù)據(jù)查詢次數(shù)和查詢數(shù)據(jù)量。4結(jié)語大數(shù)據(jù)時代，數(shù)據(jù)庫中的數(shù)據(jù)量持續(xù)增加。為了保證信息管理系統(tǒng)始終具有快速的響應(yīng)速度