SQL server數(shù)據(jù)庫性能優(yōu)化策略探析

宋國平

(吉林廣播電視大學(xué)，吉林 長春 130022)

隨著計(jì)算機(jī)網(wǎng)絡(luò)的應(yīng)用范圍的推廣，各種信息系統(tǒng)、尤其是基于互聯(lián)網(wǎng)的管理系統(tǒng)已經(jīng)在人們生產(chǎn)生活的各個(gè)領(lǐng)域得到廣泛應(yīng)用，一些規(guī)模較大、高并發(fā)、大數(shù)據(jù)訪問比較頻繁的信息數(shù)據(jù)庫系統(tǒng)的性能越發(fā)受到人們的重視。由于數(shù)據(jù)庫是信息系統(tǒng)的核心和基礎(chǔ)，因此，一個(gè)系統(tǒng)性能的優(yōu)劣在相當(dāng)程度上要受數(shù)據(jù)庫設(shè)計(jì)的影響。

SQLServer是Microsoft公司開發(fā)的大型關(guān)系型數(shù)據(jù)庫管理系統(tǒng)，是Windows平臺(tái)下的主流數(shù)據(jù)庫產(chǎn)品之一，被廣泛用于多種信息管理系統(tǒng)中，所以研究Windows平臺(tái)下的SQL Server數(shù)據(jù)庫優(yōu)化對(duì)于管理系統(tǒng)性能的提升是非常必要的。

數(shù)據(jù)庫優(yōu)化就是在一定的計(jì)算機(jī)系統(tǒng)資源下，盡可能提高響應(yīng)速度和服務(wù)質(zhì)量，它涉及數(shù)據(jù)庫設(shè)計(jì)、應(yīng)用程序設(shè)計(jì)和接口設(shè)計(jì)等多個(gè)方面，取決于應(yīng)用需求及系統(tǒng)開發(fā)人員的專業(yè)素質(zhì)、經(jīng)驗(yàn)和技巧，是一個(gè)相當(dāng)復(fù)雜的工程。數(shù)據(jù)庫設(shè)計(jì)方案是數(shù)據(jù)庫系統(tǒng)優(yōu)化的關(guān)鍵，一個(gè)合理的數(shù)據(jù)模型，不僅是系統(tǒng)正常運(yùn)行的基礎(chǔ)，也能有效降低編寫代碼和維護(hù)的難度，提高系統(tǒng)的實(shí)際運(yùn)行性能。一個(gè)優(yōu)秀的數(shù)據(jù)庫設(shè)計(jì)要在充分了解用戶需求的基礎(chǔ)上，嚴(yán)格按照軟件工程的規(guī)范和流程來進(jìn)行，要充分保留各種文檔資料，在這個(gè)大前提下，還要從以下幾個(gè)方面進(jìn)行優(yōu)化：

一、數(shù)據(jù)表設(shè)計(jì)的優(yōu)化策略

通常數(shù)據(jù)庫基本表的設(shè)計(jì)遵循以下基本原則：1、標(biāo)準(zhǔn)化和規(guī)范化。表設(shè)計(jì)標(biāo)準(zhǔn)有幾種，一般來說3NF標(biāo)準(zhǔn)在實(shí)現(xiàn)數(shù)據(jù)庫性能、系統(tǒng)擴(kuò)展、消除數(shù)據(jù)冗余和實(shí)現(xiàn)完整性方面最佳平衡。2、應(yīng)用數(shù)據(jù)驅(qū)動(dòng)。不要使用硬性編碼，而應(yīng)采用數(shù)據(jù)驅(qū)動(dòng)方式，有利于實(shí)現(xiàn)策略變更和維護(hù)，提高系統(tǒng)的可擴(kuò)展性。

在基本表設(shè)計(jì)中，索引設(shè)計(jì)和選擇十分重要，對(duì)于數(shù)據(jù)庫的整體性能影響很大，這就要求我們對(duì)每一種索引要選擇相應(yīng)的優(yōu)化策略。

1、簇索引(Clustered Index)。每個(gè)表只能有一個(gè)簇索引，用于決定表內(nèi)數(shù)據(jù)的物理順序，所以應(yīng)在非簇索引前建立。在創(chuàng)建和選擇簇索引時(shí)應(yīng)遵循以下原則：（1）包含較多完全不同數(shù)據(jù)的列；（2）返回大量結(jié)果的查詢；（3）使用“BETWEEN”及關(guān)系運(yùn)算符返回的查詢；（4）需要對(duì)單個(gè)的行進(jìn)行快速掃描的OLTP類型的應(yīng)用；（5）經(jīng)常被包含有連接或GROUPBY子句查詢進(jìn)行訪問的列。而對(duì)于經(jīng)常作更新和較寬的鍵值，則不適合選擇簇索引。

2、非簇索引（Nonclustered Index）。與簇索引不同，首先其數(shù)據(jù)存儲(chǔ)順序與索引的順序不同，其次，它只存放數(shù)據(jù)行的引用。因此，它需要存放在一個(gè)堆上或簇索引上。如果在管理系統(tǒng)中需要多種途徑來搜索數(shù)據(jù)，則非簇索引可以有效提高性能。應(yīng)在以下情況選擇創(chuàng)建非簇索引：（1）不會(huì)返回大量結(jié)果的查詢；（2）經(jīng)常在返回精確匹配的條件查詢被引用的列；（3）包含大量完全不同數(shù)據(jù)的列，并且在某個(gè)列上已建立了簇索引；（4）系統(tǒng)應(yīng)用中經(jīng)常使用分組和連接；（5）覆蓋整個(gè)表的所有列的查詢。

索引的創(chuàng)建一方面可以有效提高系統(tǒng)的性能，但也會(huì)帶來時(shí)間和空間的負(fù)面影響，因此在設(shè)計(jì)時(shí)應(yīng)從多個(gè)角度來進(jìn)行平衡，因此要杜絕建立無效索引，如果負(fù)面影響較大，則應(yīng)考慮采取臨時(shí)表等替代措施來提高系統(tǒng)的性能。

二、數(shù)據(jù)庫其它對(duì)象的存放策略

除了基本表外，數(shù)據(jù)庫中還包括許多其它對(duì)象，這些對(duì)象的存儲(chǔ)也是影響系統(tǒng)性能的重要因素。對(duì)象的存儲(chǔ)和訪問離不開I/O設(shè)備，而I/O設(shè)備通常是影響大型數(shù)據(jù)系統(tǒng)庫性能的瓶頸，所以在進(jìn)行數(shù)據(jù)庫設(shè)計(jì)時(shí)也要充分考慮這些對(duì)象的存儲(chǔ)，才能提高系統(tǒng)的性能。

（1）大型系統(tǒng)通常使用多個(gè)磁盤，要避免系統(tǒng)I/O瓶頸，就要充分利用硬件系統(tǒng)的并行I/O機(jī)制，因此數(shù)據(jù)存放應(yīng)跨越多個(gè)設(shè)備，不同性質(zhì)訪問的數(shù)據(jù)要分別存放。

（2）大型信息系統(tǒng)的事務(wù)日志是影響系統(tǒng)性能的重要環(huán)節(jié)之一，無論是從減少系統(tǒng)開銷角度，還是從系統(tǒng)障礙恢復(fù)的角度出發(fā)，都應(yīng)存放在單獨(dú)的磁盤上，即將系統(tǒng)的日志文件與數(shù)據(jù)文件分開存儲(chǔ)。

（3）將訪問頻度高低不同的表分別存儲(chǔ)，可以充分利用系統(tǒng)的并行機(jī)制，提高系統(tǒng)的I/O性能。

三、查詢分析設(shè)計(jì)的優(yōu)化策略

由于管理系統(tǒng)對(duì)數(shù)據(jù)庫的操作主要是數(shù)據(jù)查詢操作，數(shù)據(jù)庫設(shè)計(jì)離不開應(yīng)用系統(tǒng)的設(shè)計(jì)，二者是相輔相成，密不可分的。因此對(duì)應(yīng)用系統(tǒng)的設(shè)計(jì)優(yōu)化主要是對(duì)查詢語句的設(shè)計(jì)優(yōu)化，要在遵循盡量使用連接替代子查詢和簡化邏輯讀和掃描次數(shù)的大原則下，針對(duì)所要解決的問題進(jìn)行具體分析，優(yōu)化系統(tǒng)性能。

1、由于存儲(chǔ)過程的執(zhí)行效率要高于SQL語句，因此應(yīng)盡可能設(shè)計(jì)和使用存儲(chǔ)過程；

2、盡可能充分利用查詢優(yōu)化器的自動(dòng)優(yōu)化功能，查詢代碼設(shè)計(jì)要改善可能符合查詢優(yōu)化器的規(guī)則，從而有效避免全表掃描；

3、必要時(shí)采用臨時(shí)表，避免使用大量排序操作；

4、因?yàn)橛螛?biāo)的使用對(duì)系統(tǒng)性能有較大影響，所以要有限制地加以使用；

5、在使用了Where子句的查詢代碼中盡可能少使用復(fù)雜的表達(dá)式和“或”邏輯運(yùn)算進(jìn)行連接；

6、除非必要，要杜絕向客戶端返回大量數(shù)據(jù)，減少網(wǎng)絡(luò)數(shù)據(jù)傳輸量；

7、充分使用批處理語句和對(duì)重復(fù)操作使用參數(shù)數(shù)組。

四、基本表擴(kuò)展設(shè)計(jì)優(yōu)化

盡管基于3NF設(shè)計(jì)的數(shù)據(jù)庫表應(yīng)用范圍很廣，有著很強(qiáng)的優(yōu)越性。但是在有些實(shí)際應(yīng)用中存在不利于系統(tǒng)優(yōu)化的因素，因此，需要根據(jù)具體的應(yīng)用、對(duì)于規(guī)模非常大的數(shù)據(jù)庫、請(qǐng)求訪問的頻度等各個(gè)因素，對(duì)部分表進(jìn)行必要的行與列的分割、根據(jù)實(shí)際情況存儲(chǔ)冗余數(shù)據(jù)和衍生列、進(jìn)行相關(guān)分割表合并處理。這些措施雖然會(huì)破壞了3NF、增加部分系統(tǒng)開銷，但能夠充分利用設(shè)備的物理特性和Cache技術(shù)，可在一定程度上優(yōu)化整個(gè)系統(tǒng)的性能。

五、鎖的優(yōu)化策略

鎖是多用戶條件下系統(tǒng)實(shí)現(xiàn)并行處理的一個(gè)重要機(jī)制，應(yīng)用這一機(jī)制能夠有效保持?jǐn)?shù)據(jù)的一致性和完整性。但是鎖的應(yīng)用在一定程序上會(huì)影響系統(tǒng)的性能，因此如何減少和杜絕死鎖是數(shù)據(jù)庫系統(tǒng)設(shè)計(jì)和應(yīng)用設(shè)計(jì)必須研究的一個(gè)課題。

在應(yīng)用程序設(shè)計(jì)時(shí)使用鎖應(yīng)遵循以下幾個(gè)原則：

1、根據(jù)實(shí)際問題對(duì)長事務(wù)進(jìn)行分解，從而有效減少排它鎖的占用時(shí)間；

2、充分利用批處理語句，減少在事務(wù)執(zhí)行過程中的交互，避免人為控制事務(wù)的長短；

對(duì)于訪問頻繁、并發(fā)度較高的數(shù)據(jù)庫應(yīng)用系統(tǒng)，可采用相應(yīng)的優(yōu)化措施從而避免死鎖，如：對(duì)每個(gè)查詢使用超時(shí)機(jī)制、使用帶約束的連接、適度平衡使用行鎖、頁鎖及表鎖等，從而有效避免死鎖。

總之，數(shù)據(jù)庫系統(tǒng)的優(yōu)化是一個(gè)復(fù)雜的系統(tǒng)工程，涉及的因素非常多，在此不可能全部探討?？傮w來說，都要從結(jié)構(gòu)設(shè)計(jì)優(yōu)化和行為設(shè)計(jì)優(yōu)化兩個(gè)視角加以探討，結(jié)構(gòu)設(shè)計(jì)是設(shè)計(jì)數(shù)據(jù)庫框架或數(shù)據(jù)庫結(jié)構(gòu)，行為設(shè)計(jì)則是設(shè)計(jì)應(yīng)用程序、事務(wù)處理等。對(duì)數(shù)據(jù)庫的優(yōu)化應(yīng)從以上兩個(gè)方面入手，分析問題需求，采用多種措施相結(jié)合的策略，這樣才能有效實(shí)現(xiàn)SQLServer數(shù)據(jù)庫的優(yōu)化，保證系統(tǒng)高效、可靠的運(yùn)行，從而提高整個(gè)信息系統(tǒng)的性能。

[1]徐海蔚.MicrosoftSQLServer企業(yè)級(jí)平臺(tái)管理實(shí)踐[J].電子工業(yè)出版社,2010,(2).

[2]（美）RobertVieira.SQLServer2008編程入門經(jīng)典（第三版）[M].清華大學(xué)出版社,2010,1.