提高ＳＯＬ Ｓｅｒｖｅｒ數(shù)據(jù)庫訪問速度的方法

摘要：當(dāng)SQL Server數(shù)據(jù)庫處理10萬條以上的數(shù)據(jù)記錄并且并發(fā)用戶超過100人時(shí)，其執(zhí)行速度迅速下降。文章以學(xué)校成績錄入與管理系統(tǒng)為例介紹了幾種提高數(shù)據(jù)庫訪問速度的方法。

關(guān)鍵詞：SQL Server；訪問速度；數(shù)據(jù)庫

引言

SQL Server數(shù)據(jù)庫作為微軟在Windows平臺(tái)上開發(fā)的數(shù)據(jù)庫，已經(jīng)過多次的功能改進(jìn)，出現(xiàn)了SQL Server2000版本，雖然SQL Server2000還無法和Oracle、Informix等大型數(shù)據(jù)庫相比，但它在市場上的占有率足以說明了其先進(jìn)性。很多程序員只注重程序算法的編寫，并不對SQL Server2000數(shù)據(jù)庫處理進(jìn)行深入的考慮，從而導(dǎo)致數(shù)據(jù)庫數(shù)據(jù)或并發(fā)用戶增多時(shí)，系統(tǒng)執(zhí)行速度越來越慢，甚至導(dǎo)致死機(jī)。本文根據(jù)我校在開發(fā)成績錄入與管理系統(tǒng)過程中出現(xiàn)的問題，對如何提高SQL Server數(shù)據(jù)庫訪問速度進(jìn)行研究。

我校成績錄入與管理系統(tǒng)中主要包含的數(shù)據(jù)表為：學(xué)生表，成績表，課程表，專業(yè)表，學(xué)院表。各個(gè)學(xué)期成績表等如圖1所示(部分表以及表結(jié)構(gòu)已經(jīng)省略)。

1 合理使用視圖和分區(qū)視圖

視圖可以看成是虛擬表或存儲(chǔ)查詢，可以通過select對視圖中的數(shù)據(jù)進(jìn)行查詢，但是無法插入、更新、刪除視圖中的數(shù)據(jù)信息。很多程序員在編程過程中只使用數(shù)據(jù)表，而其它的一些操作全部利用程序來完成。過去我校的成績錄入與管理系統(tǒng)中，所有的數(shù)據(jù)操作都利用表來完成，大部分?jǐn)?shù)據(jù)交換和數(shù)據(jù)調(diào)用都通過函數(shù)來實(shí)現(xiàn)，因此隨著數(shù)據(jù)量的增大整個(gè)系統(tǒng)的執(zhí)行速度越來越慢。系統(tǒng)經(jīng)過改進(jìn)，把需要利用多個(gè)select查詢的數(shù)據(jù)創(chuàng)建成視圖，系統(tǒng)速度有了很大的提高。

以圖2所示的數(shù)據(jù)表為例，在一個(gè)頁面中想要完成學(xué)生的成績查詢，并且要知道學(xué)生的姓名和課程名稱，如果通過程序?qū)崿F(xiàn)，必須利用三次select語句才能把圖2所示三個(gè)表中所需要的字段顯示出來。這種方法大大影響了數(shù)據(jù)庫訪問的速度。若利用視圖，則只需進(jìn)行一次select查詢就可以得到想要的數(shù)據(jù)，其效率遠(yuǎn)遠(yuǎn)大于程序?qū)崿F(xiàn)的效率。

視圖是不保存任何記錄的，它存儲(chǔ)的是查詢語句，所顯示的記錄來自于數(shù)據(jù)表(可以為多個(gè)數(shù)據(jù)表)。可以依據(jù)各種查詢需要?jiǎng)?chuàng)建不同的數(shù)據(jù)表，不會(huì)因此增加數(shù)據(jù)庫的數(shù)據(jù)量。

再舉一個(gè)例子：我校每個(gè)學(xué)期都會(huì)有20萬條的成績數(shù)據(jù)，迄今為止成績錄入與管理系統(tǒng)已經(jīng)運(yùn)行了5個(gè)學(xué)期，各學(xué)期總成績就有100萬條數(shù)據(jù)，對于如此大量的數(shù)據(jù)，如何來管理，就每個(gè)程序員來說都是非常頭疼的問題。

對此可以引入分區(qū)視圖來解決。分區(qū)視圖是指將一個(gè)或多個(gè)服務(wù)器上的數(shù)據(jù)表數(shù)據(jù)合并，通過定義check約束，將搜索范圍限制到這些表上。分區(qū)視圖的關(guān)鍵是check約束，如果不定義check約束，查詢分析器必須搜索所有的表。具體到我校的成績錄入與管理系統(tǒng)的處理上，我們?yōu)槊總€(gè)學(xué)期單獨(dú)創(chuàng)建一個(gè)表，表名為成績[學(xué)期][學(xué)年]例如：表名為成績20061。可以利用SQL中設(shè)計(jì)視圖功能創(chuàng)建各個(gè)學(xué)期表。

在成績表中需要注意，一定要建立學(xué)年、學(xué)期列的check約束。接著定義一個(gè)視圖把以前所有學(xué)期的數(shù)據(jù)使用union all連接起來作為單個(gè)的結(jié)果集。別出此語句中的搜索條件，將其搜索范圍限制在這個(gè)表上。這種方法的使用可以大大提高SQL Server數(shù)據(jù)庫的訪問速度。

2 使用存儲(chǔ)過程提高數(shù)據(jù)處理速度

存儲(chǔ)過程最大的優(yōu)點(diǎn)是將存儲(chǔ)處理翻譯成可執(zhí)行碼保存在系統(tǒng)表內(nèi)，當(dāng)作數(shù)據(jù)庫的對象之一。由于存儲(chǔ)過程已事先被翻譯成可執(zhí)行碼，可以直接執(zhí)行，所以其執(zhí)行速度會(huì)很快。對一些功能固定的需求，把它寫成存儲(chǔ)過程可提高SQL Server數(shù)據(jù)處理的速度。以我校成績錄入與管理系統(tǒng)為例：我校每學(xué)期成績表中數(shù)據(jù)都在20萬條以上，而且把各個(gè)學(xué)期的數(shù)據(jù)連接到一起形成了總成績視圖，數(shù)據(jù)量超過100萬條，要操作這樣龐大的數(shù)據(jù)庫，如果使用方法不當(dāng)，就會(huì)嚴(yán)重地降低執(zhí)行速度。我們考慮將經(jīng)常使用的功能全部寫成存儲(chǔ)過程來提高數(shù)據(jù)庫訪問的速度，如：學(xué)期數(shù)據(jù)導(dǎo)入、成績數(shù)據(jù)備份、總成績查詢等。結(jié)果表明執(zhí)行速度有很大的提高。

對于學(xué)期成績數(shù)據(jù)導(dǎo)入功能說明如下：在每學(xué)期成績庫中有20多萬條數(shù)據(jù)，每學(xué)期成績錄入結(jié)束以后都要進(jìn)行學(xué)期成績數(shù)據(jù)導(dǎo)入。以前使用insert語句進(jìn)行程序?qū)?，?zhí)行速度很慢，甚至有時(shí)慢得無法執(zhí)行，后來通過一個(gè)簡單的存儲(chǔ)過程很快地解決了問題，20萬條數(shù)據(jù)導(dǎo)入，執(zhí)行時(shí)間在10秒左右。

我校教務(wù)教學(xué)管理中使用學(xué)分制，通過學(xué)分績的高低來反映一個(gè)學(xué)生學(xué)習(xí)成績的好壞。計(jì)算學(xué)分績的公式是((學(xué)生成績—50)／10)*課程的學(xué)分。在學(xué)分和學(xué)分績插入操作種需要進(jìn)行判斷：考試成績不及格，則學(xué)分和學(xué)分績都為0；考試成績在60分以上，則計(jì)算出相應(yīng)的學(xué)分和學(xué)分績。最后通過“delete from成績表”清空成績表中的成績數(shù)據(jù)，以便下學(xué)期正常進(jìn)行成績錄入工作。合理地建立存儲(chǔ)過程可以有效地提高數(shù)據(jù)庫訪問速度；還可以把一些程序算法封裝到存儲(chǔ)過程中來提高軟件產(chǎn)品的可維護(hù)性，即使是條件發(fā)生改變時(shí)，也不需要修改客戶端的應(yīng)用程序，只要對數(shù)據(jù)庫端編寫的存儲(chǔ)過程進(jìn)行修改即可。

3 索引設(shè)計(jì)與優(yōu)化

目前很多軟件為方便使用者學(xué)習(xí)也在幫助文檔中添加索引功能，微軟的MSDN是最典型的例子之一。在MSDN中專門有一項(xiàng)索引服務(wù)，很多程序員可能熟悉而且經(jīng)常使用這個(gè)功能。通過索引查詢信息不但方便而且能大大縮短查找信息的時(shí)間。在SQL Server數(shù)據(jù)庫設(shè)計(jì)中也采用了這種概念，為了加快數(shù)據(jù)庫訪問的速度，可以對某些常用的查詢條件創(chuàng)建索引。

在我校成績錄入與管理系統(tǒng)開發(fā)的過程中，解決數(shù)據(jù)庫訪問速度是一個(gè)關(guān)鍵問題。以往，期末考試結(jié)束以后，很多教師集中進(jìn)行學(xué)生成績錄入，并行用戶往往都在100人以上，而且對數(shù)據(jù)庫反復(fù)進(jìn)行插入、更新、刪除數(shù)據(jù)等操作，在這種情況下，程序執(zhí)行速度非常慢，錄入一條成績數(shù)據(jù)往往需要1分多鐘，而且有時(shí)還出現(xiàn)一些莫名其妙的錯(cuò)誤，這使開發(fā)人員十分困惑。經(jīng)過對數(shù)據(jù)庫各個(gè)表的仔細(xì)核查，并對每個(gè)表相應(yīng)的字段建立索引后，問題完全得到解決，經(jīng)過測試在線用戶100人時(shí)，錄入一條成績數(shù)據(jù)大約2～3秒，系統(tǒng)處理速度得到很大提高。下面是建立索引的一個(gè)具體例子：在學(xué)生表中經(jīng)常用到的字段只有一個(gè)：學(xué)號(hào)，我們可以對學(xué)號(hào)建立索引，建立方法如下create index sid on學(xué)生表(學(xué)號(hào))。

提高數(shù)據(jù)庫訪問速度，不但要使用索引，而且還要優(yōu)化索引；但是不能盲目使用索引，更不能對所有的字段都加上索引，這樣不但不會(huì)提高速度，反而會(huì)因?yàn)檫^多的索引導(dǎo)致表掃描增多，增大查詢造成的I／O開銷。常用的索引主要分為兩種：聚集索引、非聚集索引。在建立索引過程中要根據(jù)具體情況來選擇不同的索引。聚集索引是表中存儲(chǔ)的數(shù)據(jù)按照索引的順序存儲(chǔ)，檢索效率比普通索引要高，但對數(shù)據(jù)新增、修改、刪除的影響比較大。這就是說聚集索引適合固定表，如學(xué)生表、專業(yè)表、課程表、學(xué)院表，而不適合要不斷新增、修改、刪除成績信息的成績表。非聚集索引和聚集索引相反，不影響表中的數(shù)據(jù)存儲(chǔ)順序，檢索效率比聚集索引低，但是對新增、修改、刪除的影響很小，在成績表中就必須建立非聚集索引了。

4 結(jié)束語

在SQL Server數(shù)據(jù)庫中合理有效地利用以上提到方法固然能夠提高訪問速度，但是在具體的程序、數(shù)據(jù)庫開發(fā)過程中還要不斷的改進(jìn)這些方法，并且還要注意在實(shí)際項(xiàng)目中的靈活應(yīng)用。提高SQL Server數(shù)據(jù)庫訪問速度的方法還有很多。在具體數(shù)據(jù)庫開發(fā)過程中需要程序員們不斷地優(yōu)化數(shù)據(jù)庫的操作，提升其性能，使得SQL Server數(shù)據(jù)庫潛能得到全部發(fā)揮。希望本文能夠起到拋磚引玉的作用，并讓更多的程序員來關(guān)心數(shù)據(jù)庫操作的效率問題。