應(yīng)用系統(tǒng)中數(shù)據(jù)庫性能優(yōu)化研究

忽佳文，張梅梅，王　影

（咸陽師范學院 計算機學院，陜西　咸陽　712000）

?

應(yīng)用系統(tǒng)中數(shù)據(jù)庫性能優(yōu)化研究

忽佳文，張梅梅，王影

（咸陽師范學院 計算機學院，陜西咸陽712000）

摘要：人們知道的重要SQLSERVER數(shù)據(jù)庫對象有索引、視圖、觸發(fā)器和存儲過程。為了起到提高系統(tǒng)安全性、提高執(zhí)行效率、方便代碼管理等效果，就需要在教務(wù)管理系統(tǒng)開發(fā)中有效應(yīng)用這些數(shù)據(jù)庫對象技術(shù)。文章首先分析了這些數(shù)據(jù)庫對象的特點和優(yōu)化策略，其次介紹了視圖、觸發(fā)器和存儲過程在教務(wù)管理系統(tǒng)中的應(yīng)用，并結(jié)合源代碼就存儲過程和觸發(fā)器技術(shù)這兩種主要技術(shù)在ASP.NET 環(huán)境中開發(fā)教務(wù)管理系統(tǒng)這一應(yīng)用提出自己的設(shè)想。

關(guān)鍵詞：存儲過程；觸發(fā)器；性能優(yōu)化；教務(wù)系統(tǒng)

凡涉及數(shù)據(jù)庫技術(shù)的計算機應(yīng)用系統(tǒng)都可以稱之為數(shù)據(jù)庫應(yīng)用系統(tǒng)。我們知道數(shù)據(jù)庫應(yīng)用系統(tǒng)的應(yīng)用非常廣泛，無論是聯(lián)機事務(wù)處理或是聯(lián)機分析處理、又或是小型單機事務(wù)處理和大型信息系統(tǒng)、再或者傳統(tǒng)的業(yè)務(wù)數(shù)據(jù)處理及計算機輔助設(shè)計/制造、圖像處理、地理信息系統(tǒng)、辦公信息系統(tǒng)、Web信息管理、數(shù)據(jù)挖掘，以及電子商務(wù)、數(shù)字化出版等等，越來越多的應(yīng)用領(lǐng)域采用數(shù)據(jù)庫技術(shù)來存儲和處理信息資源。

當前各種大型的數(shù)據(jù)庫應(yīng)用系統(tǒng)中，數(shù)據(jù)量越來越大，數(shù)據(jù)處理越來越復雜，對數(shù)據(jù)庫性能的要求也越來越高?？墒牵牢覀儞碛械能浻布Y源是有限的，這就催生出一個重要的研究課題，即是如何充分利用有限的軟硬件資源，來達到獲取最大的系統(tǒng)吞吐量及數(shù)據(jù)的強大處理能力的效果。我們知道衡量數(shù)據(jù)庫系統(tǒng)性能良好的重要指標之一就是響應(yīng)時間和吞吐量，響應(yīng)越快，吞吐量越大，系統(tǒng)性能自然就越好。本文主要對應(yīng)用系統(tǒng)中數(shù)據(jù)庫對象（視圖、存儲過程、觸發(fā)器）的優(yōu)化進行探討，并以教務(wù)管理系統(tǒng)為例進行說明。

1　數(shù)據(jù)庫對象優(yōu)化策略

1.1視圖的應(yīng)用

根據(jù)數(shù)據(jù)庫研究領(lǐng)域給出的視圖的最新定義：視圖是從一個或幾個基本表（或視圖）導出的表，與基本表不同之處在于它是一個虛表。它只是作為用戶查看數(shù)據(jù)庫表中數(shù)據(jù)的一種方式。視圖的定義被存放于數(shù)據(jù)庫中，而視圖對應(yīng)的數(shù)據(jù)則不存放于數(shù)據(jù)庫中，這些數(shù)據(jù)仍存放在原來的基本表中。一個視圖可以派生出其它的視圖，可以表示來自于不同的源的數(shù)據(jù)。為一個應(yīng)用程序的數(shù)據(jù)庫代碼部分定義的視圖，實質(zhì)上相當于很多表，我們可以對特定的視圖進行插入數(shù)據(jù)、修改數(shù)據(jù)和刪除數(shù)據(jù)的操作。在優(yōu)化設(shè)計中合理地創(chuàng)建視圖將變得尤為重要，其主要作用有：（1）提供一定的數(shù)據(jù)邏輯獨立性。使用視圖可避免直接調(diào)用基本表，當基本表結(jié)構(gòu)改變時，我們只需要修改視圖的定義而不用修改本子系統(tǒng)的應(yīng)用程序，這極大地為系統(tǒng)的開發(fā)提供了便利。（2）消除復雜SQL編程。用戶在實際的應(yīng)用情況中，有很多像統(tǒng)計、做報表等這類需要復雜工作量的需求，這時，為了有效降低SQL編程的復雜度，就要在多個基本表和應(yīng)用程序之間建立視圖。同時，由于SQL語句每次執(zhí)行時系統(tǒng)都要進行查詢分析和查詢檢查，但倘若我們使用了視圖，就會最大限度地提高效率，因為對視圖來說只需檢查一次，這無形中提高了系統(tǒng)的開發(fā)和運行效率。（3）安全保密作用。我們可以為用戶定義帶有權(quán)限的視圖，也就是定義他們對部分數(shù)據(jù)有相應(yīng)查看權(quán)限的虛表，用戶訪問基表是不安全的，這樣做可以讓他們以訪問虛表的方式取代直接訪問基表，從而實現(xiàn)了數(shù)據(jù)的安全控制。

1.2索引的優(yōu)化

建立索引是加快查詢速度的有效手段。為了減少甚至避免全表掃描，最大限度地減少磁盤I/O的次數(shù)以及切實有效加快表的查詢，我們通常使用索引。但是，要注意的一點是不能對任何數(shù)據(jù)表都建立索引，這是因為索引在提高查、改、刪語句的性能的同時但會大大降低插入insert語句的性能。因此，索引建立不當，反而會使數(shù)據(jù)庫運行更慢。如果索引的數(shù)量過于龐大，還會增加維護上的開銷，同時降低系統(tǒng)的性能。因此，對索引的合理使用是非常重要的。

SQL Server中的索引包括聚集索引和非聚集索引、唯一索引和非唯一索引、單列索引和復合索引。SQL Server執(zhí)行查詢時會自動選擇使用哪個索引，查詢優(yōu)化器會對可用的數(shù)據(jù)檢索方法進行成本評估，從中選擇最有效的方法。

以下給出幾個建立索引應(yīng)遵循的原則：（1）主鍵屬性建立聚集索引（一般系統(tǒng)會自動建立）。（2）存在范圍查詢和排序、分組這兩項有大量重復值且經(jīng)常進行的活動、對某屬性列訪問頻繁的情況，此時考慮建立聚集索引。（3）如果有屬性列出現(xiàn)在查詢條件或表連接條件中，這時需要建立相應(yīng)索引，如果某些屬性列一起構(gòu)成了一個集合出現(xiàn)在查詢條件或表連接條件中，則就需要建立復合索引。（4）如果一個基表需要做頻繁的插入操作，我們就需要建立索引，這時應(yīng)使用fill factor（填充因子）來減少頁分裂，這樣可以有效同高并發(fā)度以及降低死鎖的發(fā)生概率。（5）如果有索引關(guān)鍵字這類需要，那么就應(yīng)盡可能采用小數(shù)據(jù)類型的屬性列作為鍵，這樣做是為了使盡可能多的索引鍵和指針包含在每個索引頁中。采取了這種方法，便可使一個查詢必須遍歷的索引頁面大大減少。（6）對于那些頻繁更新的列來說，最好不要包含在索引列中。

1.3存儲過程和觸發(fā)器的應(yīng)用

存儲過程是一組實現(xiàn)特定功能的、由SQL語句和流程控制語句共同組成的一個子程序，經(jīng)過數(shù)據(jù)庫編譯和優(yōu)化后存儲在數(shù)據(jù)庫服務(wù)器中。用戶或應(yīng)用程序通過需要的存儲過程的名字及相應(yīng)入口參數(shù)用以進行調(diào)用，調(diào)用結(jié)束后即返回狀態(tài)值用來向用戶提示操作是否成功或失敗，而且能夠在遠程數(shù)據(jù)庫中運行。它的執(zhí)行速度比獨立執(zhí)行同樣的程序要快。倘若任何一個數(shù)據(jù)庫應(yīng)用程序都用到了存儲過程這一數(shù)據(jù)庫對象，我們可以說它的設(shè)計是非常良好的。下面就存儲過程的優(yōu)點主要做以下幾方面小結(jié)：（1）存儲過程可以實現(xiàn)較快的執(zhí)行速度，提高系統(tǒng)效率。存儲過程在首次執(zhí)行時查詢優(yōu)化器對其進行分析、優(yōu)化并進行編譯，然后生成最終被存在系統(tǒng)表中的執(zhí)行計劃。倘若某客戶端需要調(diào)用存儲在服務(wù)器中的存儲過程，那么他要做的是只需通過網(wǎng)絡(luò)發(fā)送該存儲過程名以及入口參數(shù)即可，剩下的就是交給數(shù)據(jù)庫服務(wù)器了，由數(shù)據(jù)庫服務(wù)器來執(zhí)行該存儲過程，無需再進行編譯，真正做到了“一次編譯多次執(zhí)行”。待數(shù)據(jù)庫服務(wù)器執(zhí)行完指定調(diào)用的存儲過程后，會將結(jié)果集數(shù)據(jù)返回給剛才調(diào)用存儲過程的客戶端應(yīng)用程序，這就避免了因在網(wǎng)上傳送大量的命令和中間結(jié)果數(shù)據(jù)而導致的低性能和網(wǎng)絡(luò)負擔。這樣做不僅可以大大提高系統(tǒng)效率，還可以達到充分利用服務(wù)器的高性能來提高運算速度的目的。（2）存儲過程允許標準組件式編程，提高系統(tǒng)可維護性。修改存儲過程是獨立的，與程序的其它部分無關(guān)。修改硬編碼SQL語句一般與修改存儲過程相比比較麻煩，并且存儲過程和組件能夠做到一次編寫多次調(diào)用，因而我們通常選擇修改的不是硬編碼SQL語句而是存儲過程。并且只要過程接口不發(fā)生變化，對應(yīng)用程序源代碼是沒有影響的影響，這也就極大地提高了程序的可編程性和可移植性。（3）存儲過程可作為一種安全機制來充分利用，增強系統(tǒng)的安全性。我們可以針對不同等級用戶創(chuàng)建帶有自定義特殊操作權(quán)限的存儲過程，并將執(zhí)行該過程的權(quán)限授予用戶，此時用戶只能通過我們所賦予特殊權(quán)限的存儲過程執(zhí)行它所規(guī)定的數(shù)據(jù)庫操作，而不能進行其它操作（例如訪問表）。例如，可以將執(zhí)行存儲過程（查詢表）的權(quán)限授予一個用戶，那么他只能執(zhí)行查詢操作且因未獲得其他權(quán)限從而不能執(zhí)行除查詢以外的其他操作。（4）存儲過程能夠減少網(wǎng)絡(luò)負荷。當客戶端向數(shù)據(jù)庫服務(wù)器發(fā)出執(zhí)行存儲過程的請求時，在網(wǎng)絡(luò)上傳送的只有執(zhí)行存儲過程的命令及入口參數(shù)，當這些命令及參數(shù)到達數(shù)據(jù)庫服務(wù)器時，服務(wù)器就會運行用戶指定的存儲過程，運行結(jié)束后將執(zhí)行結(jié)果和狀態(tài)信息一并返回客戶端。因為我們定義了存儲過程，這就避免了硬編碼SQL語句在網(wǎng)絡(luò)上的傳遞，使得客戶機與服務(wù)器的通訊量降至最小，從而最大程度上地降低了網(wǎng)絡(luò)負擔。

在SQL Server中存儲過程分為五類：系統(tǒng)存儲過程、本地存儲過程（用戶創(chuàng)建的存儲過程）、臨時存儲過程、遠程存儲過程、擴展存儲過程。本文提到的存儲過程一般來說是指用戶自行創(chuàng)建、且是本地的存儲過程，用以完成某一特定功能的存儲過程。在SQL Server存儲過程中，支持輸入（Input）、輸出參數(shù)（Output），也支持返回值參數(shù)（Return Value）。返回值參數(shù)通過Return語句返回，而且必須返回INT型的數(shù)據(jù)，RETURN語句將終止SQL Server存儲過程。

使用CREATE PROCEDURE創(chuàng)建存儲過程的語法形式如下：

可以將本文提到的另一種數(shù)據(jù)庫對象——觸發(fā)器看成是一種特殊的存儲過程，它的特殊之處在于當滿足某個特定條件時，會自動觸發(fā)執(zhí)行，是一種自動觸發(fā)的存儲過程，較存儲過程而言用戶是不能直接調(diào)用它的。任何用戶對表的增、刪、改等操作均由服務(wù)器自動激活與之對應(yīng)的觸發(fā)器。這樣，就可以解決高級形式的業(yè)務(wù)規(guī)則和復雜行為限制。

主鍵、外鍵和CHECK所不能保證的復雜的參照完整性和數(shù)據(jù)一致性若用到SQL Server數(shù)據(jù)庫觸發(fā)器就能夠?qū)崿F(xiàn)。SQL Server數(shù)據(jù)庫觸發(fā)器支持2種類型的觸發(fā)器：（1）after觸發(fā)器（之后觸發(fā)）：對于此類觸發(fā)器來說，一旦執(zhí)行了某一諸如insert、update、delete的操作，觸發(fā)器此時被立即觸發(fā)，并且它只能被定義在表上?？煞譃閕nsert插入觸發(fā)器、update更新觸發(fā)器、delete刪除觸發(fā)器。在先前我們規(guī)定有約束條件，如果操作違反了這類條件，結(jié)果是導致事務(wù)的回滾，觸發(fā)器就不會被觸發(fā)執(zhí)行。（2）instead of 觸發(fā)器 （之前觸發(fā)）：該類觸發(fā)器意味著并不執(zhí)行其定義的操作（insert、update、delete）而僅執(zhí)行觸發(fā)器本身。既可以在表上定義instead of觸發(fā)器，也可以在視圖上定義。

用CREATE TRIGGER命令創(chuàng)建觸發(fā)器，其定義語句為：

2　教務(wù)管理系統(tǒng)的設(shè)計與實現(xiàn)

下面以一個教務(wù)管理系統(tǒng)的設(shè)計開發(fā)來說明數(shù)據(jù)庫對象的優(yōu)化使用。

2.1數(shù)據(jù)表及關(guān)系

教務(wù)管理系統(tǒng)數(shù)據(jù)庫命名為TeachingManage，主要包括以下數(shù)據(jù)表。數(shù)據(jù)表之間的關(guān)系圖如圖1所示。

院系表（數(shù)據(jù)庫表名：tb_Department）：存放各院系相關(guān)數(shù)據(jù)。

專業(yè)表（數(shù)據(jù)庫表名：tb_Major）：存放各院系專業(yè)相關(guān)數(shù)據(jù)。

教室表（數(shù)據(jù)庫表名：tb_Classroom）：存放學校教室相關(guān)數(shù)據(jù)。

課程表（數(shù)據(jù)庫表名：tb_Course）：存放學校開設(shè)課程相關(guān)數(shù)據(jù)。

學生表（數(shù)據(jù)庫表名：tb_Student）：存放學生相關(guān)數(shù)據(jù)。

教師表（數(shù)據(jù)庫表名：tb_Teacher）：存放教師相關(guān)數(shù)據(jù)。

開課表（數(shù)據(jù)庫表名：tb_PlanCourse）：存放教師在某教室開設(shè)課程的相關(guān)數(shù)據(jù)。

選課表（數(shù)據(jù)庫表名：tb_Grade）：存放學生選修某課程的成績數(shù)據(jù)。

2.2創(chuàng)建視圖、存儲過程、觸發(fā)器

為每個院系學生用CREATE VIEW創(chuàng)建每個院系學生自己的視圖，將該視圖的使用權(quán)限賦予給相關(guān)院系的管理人員，這樣可以有效地保護數(shù)據(jù)的安全性，而且還可以降低SQL編程的復雜度，提高系統(tǒng)運行性能。

CREATE VIEW V_Compu_College --創(chuàng)建信息工程學院的學生視圖

AS

SELECT StudentID， SName， SCome， SSex，SIdentityCardNumber， SPassword， STelephone，SNativePlac， MajorID， ClassName， TotalCredits

FROM tb_Student

WHERE ClassName = '信息工程學院'

WITH CHECK OPTION

所有對數(shù)據(jù)庫的操作包括對數(shù)據(jù)庫數(shù)據(jù)增刪改查的操作，都用存儲過程實現(xiàn)以提高系統(tǒng)效率，比如，下面存儲過程pro_SelectNotificationByTitle實現(xiàn)了按標題進行查詢，其中@nTitle是參數(shù)，可以實現(xiàn)對標題的模糊查詢。系統(tǒng)在服務(wù)器端進行查詢，將查詢結(jié)果反饋到用戶端界面：

圖1　數(shù)據(jù)表之間的關(guān)系

觸發(fā)器可以有效地實現(xiàn)復雜的業(yè)務(wù)規(guī)則和行為限制。教務(wù)管理系統(tǒng)中有一項重要功能就是學生選課，可以創(chuàng)建觸發(fā)器統(tǒng)計選課人數(shù)并判斷選課人數(shù)是否超過限選人數(shù)，如果選課人數(shù)沒有超過限選人數(shù)，則該生可以選修該課程；否則該生不能選修該課程。

2.3創(chuàng)建應(yīng)用程序類及頁面文件

本系統(tǒng)在Visual Studio2010集成環(huán)境下實現(xiàn)。在開發(fā)過程中，我們采用ASP.NET分層開發(fā)思想，即將應(yīng)用程序結(jié)構(gòu)劃分為三層獨立的包，包括用戶表示層、業(yè)務(wù)邏輯層、數(shù)據(jù)訪問層。首先建立一個ASP.NET動態(tài)網(wǎng)站，命名為TeachingManageSystem（教務(wù)管理系統(tǒng)）。下面以一個查詢?yōu)槔f明存儲過程的調(diào)用。

在網(wǎng)站中創(chuàng)建一個頁面，以查詢關(guān)鍵字operName為形參，在頁面代碼中調(diào)用a d m in .cs類中的方法SelectNotificationByTitle。

admin.cs類中包含了管理員對數(shù)據(jù)的所有操作，在該類中實現(xiàn)存儲過程的構(gòu)造，并調(diào)用SqlHelper.cs類文件中的方法ExecuteStoredProcedure執(zhí)行存儲過程。

public static DataTable SelectNotificationByTitl e（string nTitle）--業(yè)務(wù)邏輯層，構(gòu)造存儲過程

在SqlHelper.cs類文件中實現(xiàn)對數(shù)據(jù)庫的連接、數(shù)據(jù)庫命令的執(zhí)行及數(shù)據(jù)結(jié)果的返回。SqlHelper.cs類文件中ExecuteStoredProcedure方法實現(xiàn)各個存儲過程。

3　結(jié)語

本文在教務(wù)管理系統(tǒng)的開發(fā)過程中，為了達到提高系統(tǒng)的開發(fā)效率以及方便地維護程序這樣的效果，以視圖和存儲過程等方式，將與數(shù)據(jù)庫相關(guān)的增、刪、改、查等的操作寫到數(shù)據(jù)庫組件中，而通過數(shù)據(jù)控件調(diào)用視圖或存儲過程來實現(xiàn)數(shù)據(jù)呈現(xiàn)。一旦維護人員有了想修改某些與數(shù)據(jù)庫相關(guān)的功能模塊的想法后，那么他便可以直接在后臺數(shù)據(jù)庫修改相應(yīng)的視圖和存儲過程即可，無須改程序代碼，無須重新編譯，真正做到“一次編譯到處運行”。如果在教務(wù)系統(tǒng)軟件開發(fā)過程甚至于其他應(yīng)用到數(shù)據(jù)庫技術(shù)的系統(tǒng)開發(fā)過程中綜合運用存儲過程和視圖，將會起到一系列諸如軟件設(shè)計極大便利、軟件高度安全可靠、軟件運行效率高等的驚人效果。SQLSERVER數(shù)據(jù)庫對象除了存儲過程和視圖以外，另有觸發(fā)器和索引等，如果能在今后應(yīng)用程序開發(fā)過程中，綜合運用之前提到的數(shù)據(jù)庫組件，也將會帶來意想不到的“一石多鳥”的效果。

［參考文獻］

［1］李俊.基于Asp.net的B2C電子商務(wù)系統(tǒng)設(shè)計與實現(xiàn)［D］.重慶：重慶大學，2012.

［2］吳伶琳，楊正校.SQL Server 2005數(shù)據(jù)庫基礎(chǔ)［M］.大連：大連理工大學出版社，2010.

［3］歐陽昉.網(wǎng)上商城購物系統(tǒng)研究與開發(fā)［D］.成都：電子科技大學，2007.

［4］王珊，薩師煊.數(shù)據(jù)庫系統(tǒng)概論［M］.5版.北京：高等教育出版社，2015.

［5］孟憲虎.大型數(shù)據(jù)庫管理系統(tǒng)技術(shù)應(yīng)用與實例分析［M］.北京：電子工業(yè)出版社，2011.

［6］劉衛(wèi)國，熊擁軍.數(shù)據(jù)庫技術(shù)與應(yīng)用----SQL Server2005［M］.北京：清華大學出版社，2012.

［7］李春芬，吳英斌.存儲過程在勘探設(shè)備管理系統(tǒng)中的應(yīng)用［J］.物探裝備，2011（5）：327-330.

［8］陳曉丹，王娟.存儲過程在信息發(fā)布系統(tǒng)中的研究與應(yīng)用［J］.武漢工程職業(yè)技術(shù)學院學報，2010（2）：36-38.

［9］吳伶琳.存儲過程在網(wǎng)上商城系統(tǒng)開發(fā)中的應(yīng)用［J］.計算機時代，2013（2）：31-33.

Research on Database Performance Optimization in Application System

Hu Jiawen, Zhang Meimei, Wang Ying
(School of Computer Science, Xianyang Normal University, Xianyang712000, China)

Abstract：As we all know， indexes， views， triggers and stored procedures are important database objects in SQLSERVER. In order to play important effects， such as to improve system security， improve the effciency， easy code management etc， the effective application of these database objects technology needs to be used in Educational Management System. Firstly， the paper analyzes the characteristics and optimization strategies of these database objects, secondly it introduces the applications of views, triggers and stored procedures in educational management system. Finally， put forward our own ideas for stored procedures and triggers technology in Educational Administration System in ASP.NET environment combined with the source code.

Key words：stored procedure; trigger; performance optimization; educational management system

基金項目：咸陽師范學院科研項目；項目編號：12XSYK069。陜西省大學生創(chuàng)新創(chuàng)業(yè)訓練計劃項目；項目編號：2015004。

作者簡介：忽佳文（1995-），男，河南鄭州，本科。