基于某通信系統(tǒng)的數(shù)據(jù)庫性能分析及優(yōu)化

趙金鳳

文章編號：2095-6835（2017）04-0107-03

摘 要：SQL SERVER作為一種企業(yè)級的數(shù)據(jù)庫，其性能會影響軟件的增刪查改效率。而索引在數(shù)據(jù)庫表中非常關(guān)鍵，尤其是一張表只能有一個聚集索引，應(yīng)該合理利用。同時，要善于利用數(shù)據(jù)庫中的各種工具進行有針對性的分析和性能調(diào)優(yōu)。實踐表明，SQL SERVER的性能優(yōu)化是可行的，也是多方面的。

關(guān)鍵詞：SQL SERVER；聚集索引；通信系統(tǒng)；服務(wù)器

中圖分類號：TP311.13 文獻標識碼：A DOI：10.15913/j.cnki.kjycx.2017.04.107

1 軟件的背景

本文主要是基于某通信系統(tǒng)項目中系統(tǒng)狀態(tài)監(jiān)視軟件進行分析的。該軟件為C/S架構(gòu)，由客戶端軟件、服務(wù)器端軟件和數(shù)據(jù)庫軟件組成。

軟件的工作流程是：各分系統(tǒng)軟件與服務(wù)器端軟件建立TCP連接，并登錄。在運行過程中，需每秒上報一次分系統(tǒng)的狀態(tài)參數(shù)。每個分系統(tǒng)上報的狀態(tài)參數(shù)中包含各分系統(tǒng)中主要設(shè)備的工作參數(shù)和故障告警信息，服務(wù)器端軟件需接收該條狀態(tài)信息，發(fā)送至客戶端軟件顯示，并將與上一條數(shù)據(jù)相比發(fā)生變化的整條狀態(tài)數(shù)據(jù)寫入數(shù)據(jù)庫中。

在實驗室測試中，使用3個分系統(tǒng)模擬軟件分別模擬3個分系統(tǒng)，向服務(wù)器發(fā)送分系統(tǒng)狀態(tài)。當數(shù)據(jù)量累積到一年的數(shù)據(jù)量（約600萬行）時，在客戶端查詢一個月或更大范圍內(nèi)的狀態(tài)數(shù)據(jù)時，查詢數(shù)據(jù)庫的響應(yīng)時間明顯延長，在分頁顯示中點擊下一頁，等待的時間過長。針對這個情況，本文通過分析和測試該數(shù)據(jù)庫性能，提出了優(yōu)化方案，并為以后的數(shù)據(jù)庫設(shè)計提出了合理建議。

2 系統(tǒng)及軟件運行環(huán)境

服務(wù)器軟件和數(shù)據(jù)庫軟件的運行平臺分系統(tǒng)狀態(tài)監(jiān)視軟件客戶端安裝于1臺PC機，3個分系統(tǒng)軟件（分系統(tǒng)1、分系統(tǒng)2、分系統(tǒng)3）分別部署于3臺PC機。服務(wù)器軟件分別部署于2臺服務(wù)器，同時，有一臺主服務(wù)器工作，另一臺為備機。

2.1 性能原因分析

SQL SERVER調(diào)整優(yōu)化主要涉及軟件和硬件兩部分。在工作過程中，就是要調(diào)整計算機系統(tǒng)硬件各主要組成部分，因為它們是造成瓶頸的主要因素。而在SQL SERVER系統(tǒng)中，比較常見的問題出現(xiàn)在CPU、內(nèi)存和磁盤IO等部件中。但在數(shù)據(jù)庫設(shè)計中，為數(shù)據(jù)表建立索引是得到所需數(shù)據(jù)的有效方法。對于每一個查詢優(yōu)化器，要確定是否有相關(guān)的索引可以用于訪問數(shù)據(jù)。一個利用索引的訪問與全表掃描相比，可以大大縮短查詢時間。因此，本文主要從CPU、內(nèi)存、磁盤IO和為數(shù)據(jù)表建立不同索引方面進行相關(guān)測試分析。

2.2 使用性能監(jiān)視器監(jiān)視系統(tǒng)性能

2.2.1 性能監(jiān)視器的使用

性能監(jiān)視器是Windows 2003的一個工具，可以同時監(jiān)視Windows 2003和SQL SERVER 2000的性能表現(xiàn)，是一個很好的性能分析工具。雖然性能監(jiān)視器提供了充足的計數(shù)器，但因為SQL SERVER 2000的性能極大地依賴于Windows 2003的性能情況，所以，在大多數(shù)情況下，也只需要監(jiān)視關(guān)鍵的幾個計數(shù)器，比如System：processor queue length，Processor：%processor time，Processor：%privileged time PhysicalDisk：Avg.Disk Queue Length，PhysicalDisk：%Disk Time，Memory：Pages/Sec，SQL SERVER緩沖管理器，緩存命中率等。只有在特殊情況下，才需要選擇其他計數(shù)器來進一步監(jiān)視。

2.2.2 監(jiān)視數(shù)據(jù)的分析

使用性能監(jiān)視器監(jiān)視主服務(wù)器24 h的性能數(shù)據(jù)，記錄了服務(wù)器在連續(xù)運行8 h的過程中不同計數(shù)器顯示的數(shù)值，而且在軟件運行過程中，測試人員不分時段的進行查詢測試。由此可以推斷，該主用服務(wù)器硬件配置已經(jīng)滿足了該服務(wù)器軟件和數(shù)據(jù)庫軟件的運行。

2.3 SQL SERVER數(shù)據(jù)庫配置

SQL SERVER 2000數(shù)據(jù)庫提供了可視化配置界面。在數(shù)據(jù)配置過程中，可配置數(shù)據(jù)庫可以使用的CPU數(shù)量、可使用的內(nèi)存大小、限制用戶連接數(shù)、用戶身份驗證方式、用戶查詢超時時間的設(shè)置等。當然，除了窗口界面的配置外，SQL SERVER也提供了數(shù)據(jù)庫控制臺命令（DBCC）來顯示、配置不同的數(shù)據(jù)庫參數(shù)，優(yōu)化數(shù)據(jù)庫性能。

設(shè)置SQL SERVER CPU使用不同數(shù)量的CPU和設(shè)置不同的內(nèi)存使用量對比查詢時間。在企業(yè)管理器中，右擊“（LOCAL）（WINDOWS NT）”屬性，點擊處理器頁，指定當前SQL SERVER要使用的處理器，最大工作線程選擇32（最小值）。點擊內(nèi)存，設(shè)置允許SQL SERVER使用的內(nèi)存量。在工作過程中，可以使用相應(yīng)的查詢語句查詢數(shù)據(jù)量（5 437 149行），即SELECT * FROM T_DSBSTATUS WHERE dsb_timestamp BETWEEN‘2015-4-10AND‘2015-5-20ORDER BY dsb_no.

由于備服務(wù)器只有2 G內(nèi)存，當加載進2張數(shù)據(jù)表，查詢第3張數(shù)據(jù)表，且只查詢1條返回數(shù)據(jù)時，時間超過了20 s。這個現(xiàn)象反映出備服務(wù)器內(nèi)存不足的問題，導(dǎo)致數(shù)據(jù)表從磁盤加載數(shù)據(jù)進內(nèi)存時，磁盤IO嚴重影響查詢時間。而在主服務(wù)器進行相同查詢時，內(nèi)存使用量已經(jīng)超過了2 G，且查詢分析器顯示執(zhí)行時間為0 ms。因此，應(yīng)判斷為該數(shù)據(jù)庫當前的數(shù)據(jù)量需要至少大于2 G的內(nèi)存才能完成查詢工作。

2.4 數(shù)據(jù)表建立索引

2.4.1 聚集索引

聚集索引（聚簇索引）是一種指明表數(shù)據(jù)物理存儲順序的索引。表數(shù)據(jù)按照指定作為聚集索引的一個或多個鍵列排序并存儲。因為數(shù)據(jù)按照一個指定的順序物理地存儲，所以，一個表只能創(chuàng)建一個聚集索引。

2.4.2 非聚集索引

非聚集索引（非聚簇索引）的葉子節(jié)點含有索引行，用于存儲確定行的位置信息。位置信息取決于表上是否存在聚集索引。如果該表有聚集索引，對于每行，該位置信息就是聚集索引的鍵值，可以作為該行的定位器。如果聚集索引不是一個唯一索引，SQL SERVER就自動地為所有相同的索引鍵值分配一個內(nèi)部值以非聚集索引方式使用。如果表上沒有聚集索引，則位置信息是一個行ID，而不是聚集索引的一個鍵值。行ID是一個指針，由文件ID、頁號和行ID組成。這個指針能夠精確地指示在哪里可以找到行。SQL SERVER允許一個表上最多可以有249個非聚集索引。

2.5 索引查詢測試

查詢主要基于T_DSBSTATUS表。由于查詢一般基于時間范圍且按照序號排序，所以，索引分別在dsb_timestamp和dsb_no列建立進行查詢。查詢之前，使用SET STATISTICSSQL TIME ON命令打開查詢時間顯示。每次查詢之前，要先停止SQL SERVER服務(wù)器，使用Windows查看任務(wù)管理器顯示當前內(nèi)存使用量，發(fā)現(xiàn)內(nèi)存使用量明顯下降后，再重啟SQL SERVER服務(wù)器進行查詢，這樣可避免緩存中已存入相同查詢數(shù)據(jù)，影響查詢結(jié)果。

索引測試共進行了3次查詢，主要包括以下內(nèi)容。

2.5.1 相同數(shù)據(jù)不同索引的查詢

在T_DSBSTATUS表中，分別在dsb_no列和dsb_timestamp列單獨建立聚集索引和非聚集索引，使用相同的查詢語句查詢（數(shù)據(jù)量39 628行），且在查詢語句中分別使用dsb_no排序和不使用dsb_no排序比對查詢時間。

在dsb_timestamp列上建立索引時，可大幅提高查詢效率。建立聚集索引時，查詢效率更高，而在dsb_no列上建立聚集索引時，會影響查詢效率。由此可見，在查詢范圍的列上建立索引，可以有效提高查詢效率，聚集索引的查詢效率提高更明顯。

2.5.2 不同數(shù)據(jù)的不同索引查詢

在T_DSBSTATUS表中，在dsb_timestamp列分別建立聚集索引和非聚集索引?；赿sb_no列和dsb_timestamp列建立非聚集索引時，可使用查詢語句進行查詢。

在查詢語句中，對dsb_no排序會延長查詢時間。不論如何增加索引，使用排序的語句總是花費時間最長的查詢。

插入/刪除/更新的不同索引測試在dsb_timestamp列上分別建立聚集索引和非聚集索引進行比較，使用表語句進行操作：執(zhí)行插入操作，有索引時反而可能會降低速度。這是因為增加了索引操作，而更新和刪除操作首先是要按條件檢索數(shù)據(jù)，然后進行更新操作。對于一個海量數(shù)據(jù)表來說，檢索是主要的消耗，因此，這種情況下的更新和刪除操作在有索引時性能有很大的提升。

2.6 臨時數(shù)據(jù)庫的有效使用

臨時數(shù)據(jù)庫Tempdb主要用于排序、建立工作表、格式化等內(nèi)部處理和存儲用戶建立的臨時表、索引，它是可被多用戶共享的數(shù)據(jù)庫。

為了驗證查詢語句是否會使用tempdb數(shù)據(jù)庫，做了以下測試，即，設(shè)置限制，tempdb數(shù)據(jù)文件限制為9 M，日志文件限制為2 M。然后查詢相應(yīng)的語句，即SELECT * FROM T_DSBSTATUS WHERE dsb_timestamp between‘2015-5-10 AND ‘2015-6-20ORDER BY dsb_no desc.查詢結(jié)果提示，“未能為數(shù)據(jù)庫‘TEMPDB中的對象‘（SYSTEM table id：-664682958）分配空間，因為文件組‘DEFAULT已滿”。

由此可以看出，在查詢語句的查詢過程中使用了臨時數(shù)據(jù)庫，并且默認空間已經(jīng)不能滿足需要。取消tempdb數(shù)據(jù)庫文件的增長限制后，可正常查詢，并且tempdb數(shù)據(jù)庫文件會增大。

至于查詢語句何時會使用臨時數(shù)據(jù)庫，SQL SERVER指導(dǎo)手冊中給出了解釋：數(shù)據(jù)庫關(guān)系引擎有時也需要生成一個臨時工作表執(zhí)行SQL語句中指定的邏輯操作。一般為某些GROUP BY、ORDER BY或UNION查詢生成工作表。例如，如果ORDER BY子句引用的列不為任何索引所覆蓋，則關(guān)系引擎可能需要生成一個工作表，將結(jié)果集按所請求的順序排列。而臨時工作表在TEMPDB中生成，并在語句執(zhí)行完成后被自動除去。

因此，臨時數(shù)據(jù)庫的設(shè)置也是會影響SQL SERVER性能的因素之一。為了驗證臨時數(shù)據(jù)庫對查詢性能的影響，做了以下2個測試。

設(shè)置tempdb數(shù)據(jù)庫文件，按百分比自動增長。在企業(yè)管理器中查看tempdb的tempdev文件初始分配空間為8 M，設(shè)置文件按10%自動增長。查看templog文件的初始分配空間為2 M，設(shè)置文件按10%自動增長。清理SQL SERVER緩存，進行如下查詢，即：

SET STATISTICS TIME ON

SELECT * FROM T_DSBSTATUS WHERE dsb_timestamp between ‘2015-3-10 8：00：00 AND ‘2015-6-10 17：00：00ORDER BY dsb_no asc

數(shù)據(jù)文件tempdev增長至718 M，日志文件templog增長至32 M。查詢到6 560 065行數(shù)據(jù)，耗時148 710 ms。

初始設(shè)置時，給tempdb數(shù)據(jù)庫文件比較大的空間。重啟SQL SERVER服務(wù)器，清理tempdb文件空間，設(shè)置tempdev文件為800 M，設(shè)置templog分配的空間為50 M，清理緩存，執(zhí)行之前提到的查詢步驟。此操作共查詢到6 560 065行數(shù)據(jù)，耗時119 187 ms，比第一個查詢快了近30 s。

由此可見，在默認情況下，當SQL SERVER運行時，tempdb數(shù)據(jù)庫會根據(jù)需要自動增長。與其他數(shù)據(jù)庫不同的是，每次啟動數(shù)據(jù)庫引擎時，它會重置為其初始大小。如果為tempdb數(shù)據(jù)庫定義的大小較小，則每次重新啟動SQL SERVER 時，將tempdb數(shù)據(jù)庫的大小自動增加到支持工作負荷所需的大小，那么，這一工作可能會成為系統(tǒng)處理負荷的一部分。為了避免這種開銷，可以在企業(yè)管理器中配置或者使用ALTER DATABASE命令增加 tempdb 數(shù)據(jù)庫的大小。

3 結(jié)論

由于客戶端主要基于時間段查詢，對于應(yīng)用者來說，更新和刪除的操作比較少，索引又會增加插入的時間，所以，建議數(shù)據(jù)庫在時間列上建立非聚集索引。這樣做，能適當提高查詢效率，不會嚴重影響插入操作的效率。

實驗室數(shù)據(jù)庫數(shù)據(jù)表累積了一年的數(shù)據(jù)量，在查詢時，會占用大量內(nèi)存，所以，建議將服務(wù)器內(nèi)存配置為4 G左右，避免內(nèi)存不夠成為限制數(shù)據(jù)庫效率的瓶頸，并將常查詢的數(shù)據(jù)表設(shè)置為駐留內(nèi)存，避免從磁盤讀取數(shù)據(jù)影響查詢速度。

通過對臨時數(shù)據(jù)表的測試，建議在實際應(yīng)用過程中一次性為臨時數(shù)據(jù)庫配置800 M空間容量，避免在臨時數(shù)據(jù)庫自增長過程中影響數(shù)據(jù)庫的查詢性能。

4 意見建議

4.1 建立合理的索引

從索引的結(jié)構(gòu)和原理中可以看出，雖然SQL SERVER可以通過索引訪問避免對表的掃描，減少大量的I/O操作，提高系統(tǒng)的性能，但同時也會增加系統(tǒng)的負擔，主要包括以下內(nèi)容：①創(chuàng)建和維護索引要耗費系統(tǒng)時間；②存儲索引需要額外的物理空間；③增加、刪除和修改表中數(shù)據(jù)時，要動態(tài)地維護索引，這就減慢了數(shù)據(jù)的維護速度。

4.2 維護數(shù)據(jù)庫注意事項

數(shù)據(jù)庫經(jīng)過長期運行，臨時數(shù)據(jù)庫文件增加至一定程度后，有可能會占滿磁盤空間。當需再次查詢時，臨時數(shù)據(jù)庫沒有更多的空間增長，數(shù)據(jù)庫便會報錯。因此，為了避免出現(xiàn)這種問題，在不停止數(shù)據(jù)庫服務(wù)器運行的情況下，數(shù)據(jù)庫維護人員可以使用DBCC SHRINKDATABASE語句收縮臨時數(shù)據(jù)庫和數(shù)據(jù)庫日志文件。

對于需要長期操作的表，可使用控制臺命令DBCC PINTABLE將表駐留內(nèi)存。這樣可以避免從磁盤和高速緩存中不斷切換該表。但是，這個操作會占用內(nèi)存，且不會響應(yīng)其他程序從內(nèi)存中換出，所以，應(yīng)在確保內(nèi)存使用量充足的情況下進行該操作。

5 結(jié)束語

本文列出了一些SQL SERVER數(shù)據(jù)庫優(yōu)化方法，實際上，因為影響查詢性能的因素很多，所以，不可能找到一個通用的優(yōu)化方法。在開發(fā)和設(shè)計過程中，針對數(shù)據(jù)庫的運行情況，有針對性地調(diào)整，以最小的開支和良好的執(zhí)行性能訪問數(shù)據(jù)庫為目標，根據(jù)具體的開發(fā)環(huán)境和應(yīng)用需要，綜合考慮各方面因素，正確評價各種方法的優(yōu)點和缺點，從中選擇最佳方案。

參考文獻

[1]王利.SQL SERVER數(shù)據(jù)庫性能調(diào)整與優(yōu)化[D].成都：電子科技大學(xué)，2007.

[2]周序，管麗娜，白海波，等.SQL Server 2000中文版入門與提高[M].北京：清華大學(xué)出版社，2001.

[3]何益斌，高景昌，楊亞紅，等.Microsoft SQL Server的索引結(jié)構(gòu)及其優(yōu)化[J].吉林大學(xué)學(xué)報（信息科學(xué)版），2001，19（3）.

[4]Ryan K.S tephens，Ronald R.Pl ew.數(shù)據(jù)庫設(shè)計[M].何玉潔，譯.北京：機械工業(yè)出版社，2001.

[5]Robert Vi eira.SQL Server 2005高級程序設(shè)計[M].董明，譯.北京：人民郵電出版社，2008.

[6]Microsoft.SQL SERVER 2000企業(yè)版的安裝、配置和管理[M].北京：高等教育出版社，2003.

[7]許平格.數(shù)據(jù)庫管理系統(tǒng)中查詢優(yōu)化的設(shè)計和實現(xiàn)[D].杭州：浙江大學(xué)，2005.

[8]杜軍平，黃杰.SQL Server 2000數(shù)據(jù)庫開發(fā)[M].北京：機械工業(yè)出版社，2001.

[9]莊成三，洪玫，楊秋輝.數(shù)據(jù)庫系統(tǒng)原理及其應(yīng)用[M].北京：電子工業(yè)出版社，2000.

[10]Jeffrey D.Ullman，Jennifer Widom.數(shù)據(jù)庫系統(tǒng)基礎(chǔ)教程[M].北京：清華大學(xué)出版社，1999.

[11]楊正洪，鄭齊健，鄭齊心.SQL SERVER 7 關(guān)系數(shù)據(jù)庫系統(tǒng)管理與開發(fā)指南[M].北京：機械工業(yè)出版社，2000.

[12]宋靜靜，賈智平.一種嵌入式實時數(shù)據(jù)庫系統(tǒng)查詢優(yōu)化算法[J].計算機工程，2007，33（11）.

[13]趙松濤.SQL Server 2005系統(tǒng)管理實錄[M].北京：電子工業(yè)出版社，2006.

[14]沈兆陽.SQL Server 2000 OLAP解決方案[M].北京：清華大學(xué)出版社，2001.

[15]朱德利.SQL Server 2005數(shù)據(jù)挖掘與商業(yè)智能完全解決方案[M].北京：電子工業(yè)出版社，2007.

[16]W.H.Inmon.數(shù)據(jù)倉庫[M].北京：機械工業(yè)出版社，2003.

[17]馬宏鵬，趙新，李明，等.數(shù)據(jù)倉庫原形系統(tǒng)設(shè)計[J].計算機工程與應(yīng)用，2000，36（11）.

本文部分參考文獻因著錄項目不全被刪除。

〔編輯：白潔〕