摘要：隨著數(shù)據(jù)庫(kù)數(shù)據(jù)量不斷增大，數(shù)據(jù)查詢效率問(wèn)題日益凸顯，嚴(yán)重影響管理信息系統(tǒng)的用戶體驗(yàn)。以學(xué)生成績(jī)管理系統(tǒng)為例，從物理層、數(shù)據(jù)庫(kù)層和應(yīng)用層3個(gè)方面分析了影響數(shù)據(jù)查詢效率的主要因素，有針對(duì)性地提出了相應(yīng)的數(shù)據(jù)查詢優(yōu)化方法。實(shí)際應(yīng)用結(jié)果表明，綜合采取多種優(yōu)化策略后，數(shù)據(jù)查詢效率得到了顯著提高。

關(guān)鍵詞：數(shù)據(jù)查詢；查詢效率；優(yōu)化方法

DOIDOI：10.11907/rjdk.161581

中圖分類號(hào)：TP392

文獻(xiàn)標(biāo)識(shí)碼：A文章編號(hào)文章編號(hào)：16727800（2016）009014902

基金項(xiàng)目基金項(xiàng)目：南通大學(xué)校級(jí)自然科學(xué)類科研基金一般項(xiàng)目（13Z034）；南通大學(xué)研究生教育教學(xué)改革研究與實(shí)踐項(xiàng)目（YJG14011）

作者簡(jiǎn)介作者簡(jiǎn)介：胡祖輝（1984-），男，江西婺源人，碩士，南通大學(xué)研究生院助理研究員，研究方向?yàn)楝F(xiàn)代教育管理與計(jì)算機(jī)應(yīng)用技術(shù)。

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ù)部署在Windows Server 2008操作系統(tǒng)和 SQL Server 2008數(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ù)器性能的好壞直接影響查詢效率。針對(duì)物理層影響數(shù)據(jù)查詢效率的主要因素，可以采取以下優(yōu)化方法：

（1）提高CPU性能。CPU是計(jì)算機(jī)負(fù)責(zé)執(zhí)行指令和處理數(shù)據(jù)的核心部件。服務(wù)器性能的高低很大程度上由CPU的性能決定。數(shù)據(jù)庫(kù)的查詢操作特別依賴CPU的并行處理能力。因此，應(yīng)該為數(shù)據(jù)庫(kù)服務(wù)器配置高性能的CPU。

（2）增加內(nèi)存。數(shù)據(jù)查詢分為物理讀和邏輯讀，物理讀是從硬盤(pán)讀取數(shù)據(jù)到內(nèi)存緩沖區(qū)，邏輯讀是直接從內(nèi)存緩沖區(qū)中讀取數(shù)據(jù)。內(nèi)存的讀寫(xiě)效率遠(yuǎn)遠(yuǎn)高于磁盤(pán)的讀寫(xiě)效率，而且物理讀還會(huì)增加磁盤(pán)I/O操作。因此，為了保證數(shù)據(jù)查詢操作都能夠在內(nèi)存中完成，應(yīng)該盡量為數(shù)據(jù)庫(kù)服務(wù)器配置足夠多的物理內(nèi)存，同時(shí)要配置相應(yīng)的虛擬內(nèi)存。

（3）配置多塊硬盤(pán)。數(shù)據(jù)查詢操作需要大量的I/O操作，將I/O操作盡可能平均分配在多塊硬盤(pán)上才能有效提升硬盤(pán)的并行讀寫(xiě)性能。因此，應(yīng)該為數(shù)據(jù)庫(kù)服務(wù)器配置多塊硬盤(pán)，避免使用單塊超大容量硬盤(pán)。

（4）提高網(wǎng)絡(luò)帶寬和網(wǎng)絡(luò)穩(wěn)定性。應(yīng)用程序與數(shù)據(jù)庫(kù)之間通過(guò)互聯(lián)網(wǎng)進(jìn)行數(shù)據(jù)交互，因此應(yīng)該提高數(shù)據(jù)庫(kù)服務(wù)器的網(wǎng)絡(luò)帶寬和網(wǎng)絡(luò)穩(wěn)定性。

（5）提高操作系統(tǒng)性能。數(shù)據(jù)庫(kù)管理系統(tǒng)安裝在操作系統(tǒng)上，應(yīng)該加強(qiáng)操作系統(tǒng)的管理與維護(hù)，提高操作系統(tǒng)性能。

3.2數(shù)據(jù)庫(kù)層優(yōu)化方法

數(shù)據(jù)庫(kù)管理系統(tǒng)負(fù)責(zé)SQL查詢指令的執(zhí)行，因此數(shù)據(jù)庫(kù)層的優(yōu)化是數(shù)據(jù)查詢優(yōu)化的核心，其對(duì)數(shù)據(jù)查詢效率影響最為顯著，可以采取以下優(yōu)化方法：

（1）索引優(yōu)化。索引是對(duì)數(shù)據(jù)庫(kù)表中一個(gè)或多個(gè)列的值預(yù)先進(jìn)行結(jié)構(gòu)排序。索引可以避免全表掃描，因而可顯著加快數(shù)據(jù)庫(kù)的查詢速度[2]。索引分為聚集索引和非聚集索引。與非聚集索引相比，聚集索引通常能夠提供更快的數(shù)據(jù)訪問(wèn)速度。一般應(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è)表組成的虛擬表。通過(guò)連接查詢（JOIN）和聯(lián)合查詢（UNION）建立視圖，可以實(shí)現(xiàn)數(shù)據(jù)庫(kù)中數(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)生查詢效率低下的問(wèn)題。為了提高查詢效率，視圖的定義深度一般不應(yīng)超過(guò)三層。若三層視圖不夠用，則應(yīng)在視圖上定義臨時(shí)表，在臨時(shí)表上再定義視圖。這樣反復(fù)交迭定義，視圖的深度就可以不受限制。既保留了視圖的便利性，又兼顧了查詢效率問(wèn)題。

（3）數(shù)據(jù)存儲(chǔ)優(yōu)化。數(shù)據(jù)庫(kù)通常包括數(shù)據(jù)文件和日志文件。數(shù)據(jù)文件和日志文件默認(rèn)存儲(chǔ)在相同的位置。由于數(shù)據(jù)文件和日志文件的操作會(huì)產(chǎn)生大量的I/O，因此應(yīng)將日志文件與數(shù)據(jù)文件分別存儲(chǔ)在不同的硬盤(pán)上以分散I/O。通常情況下，數(shù)據(jù)庫(kù)默認(rèn)只有一個(gè)主數(shù)據(jù)文件，不生成次數(shù)據(jù)文件。為了提高查詢效率，必要時(shí)可以通過(guò)定義文件組把數(shù)據(jù)庫(kù)中的一些表分開(kāi)存儲(chǔ)在不同的數(shù)據(jù)文件里，即增加次數(shù)據(jù)文件，同時(shí)把不同的數(shù)據(jù)文件分散存儲(chǔ)在不同的硬盤(pán)上[4]。此外，數(shù)據(jù)文件長(zhǎng)期自動(dòng)增長(zhǎng)可能產(chǎn)生碎片，導(dǎo)致物理空間與數(shù)據(jù)的邏輯空間不再連續(xù)。因此，有必要定期整理數(shù)據(jù)庫(kù)碎片，以提高數(shù)據(jù)庫(kù)查詢效率。

（4）適當(dāng)增加數(shù)據(jù)冗余。按照數(shù)據(jù)庫(kù)的設(shè)計(jì)原則，數(shù)據(jù)表應(yīng)該避免數(shù)據(jù)冗余。但是，為了提高數(shù)據(jù)的查詢效率，有時(shí)需要降低范式標(biāo)準(zhǔn)，適當(dāng)增加數(shù)據(jù)冗余，達(dá)到以空間換時(shí)間的目的。數(shù)據(jù)冗余包括字段冗余和表冗余。字段冗余是通過(guò)增加冗余字段，減少數(shù)據(jù)計(jì)算和連接查詢。如學(xué)生表中的性別和出生日期，雖然可以從身份證號(hào)中獲取，但是為了提高查詢效率，應(yīng)增加性別和出生日期字段。表冗余是通過(guò)增加冗余表提高查詢效率。以成績(jī)表為例，運(yùn)行多年的成績(jī)管理系統(tǒng)中保存了歷屆學(xué)生的成績(jī)信息，包括已經(jīng)畢業(yè)的學(xué)生成績(jī)信息，多年累積下來(lái)，數(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語(yǔ)句為：

Truncate table cj_query

step2：插入新數(shù)據(jù)，更新冗余表，相關(guān)SQL語(yǔ)句為：

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ǔ)句的編寫(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 * 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ù)庫(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)速度和良好的用戶體驗(yàn)，必須深入研究數(shù)據(jù)查詢優(yōu)化技術(shù)。本文以學(xué)生成績(jī)管理系統(tǒng)為例，從物理層、數(shù)據(jù)庫(kù)層和應(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ù)庫(kù)查詢優(yōu)化技術(shù)研究[J].電腦編程技巧與維護(hù)， 2014， 10（9）：3031.

[3]樊新華.關(guān)系數(shù)據(jù)庫(kù)的查詢優(yōu)化技術(shù)[J].計(jì)算機(jī)與數(shù)字工程， 2009， 37（12）：188192.

[4]馮衛(wèi)兵.關(guān)系數(shù)據(jù)庫(kù)的查詢優(yōu)化[J].現(xiàn)代計(jì)算機(jī)， 2010 （1）：3033.

[5]程學(xué)先，黃愛(ài)武.關(guān)系數(shù)據(jù)庫(kù)的查詢優(yōu)化技術(shù)[J].軟件導(dǎo)刊， 2007 （1）：7273.

責(zé)任編輯（責(zé)任編輯：杜能鋼）