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

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