淺談如何優(yōu)化SQL Server數(shù)據(jù)庫

趙灼

[摘 要] 本文從數(shù)據(jù)庫系統(tǒng)優(yōu)化的目標(biāo)、原則入手，通過實(shí)例研討SQL Server數(shù)據(jù)庫優(yōu)化的解決方法，以提高數(shù)據(jù)庫系統(tǒng)的運(yùn)行效能。

[關(guān)鍵詞] 數(shù)據(jù)庫； 優(yōu)化； SQL Server

doi ： 10 . 3969 / j . issn . 1673 - 0194 . 2014 . 03. 059

[中圖分類號] TP392 [文獻(xiàn)標(biāo)識碼] A [文章編號] 1673 - 0194（2014）03- 0125- 03

數(shù)據(jù)庫技術(shù)是計算機(jī)科學(xué)技術(shù)發(fā)展最快、應(yīng)用最廣泛的領(lǐng)域之一，在信息管理自動化程度日益提高的今天，數(shù)據(jù)庫技術(shù)已經(jīng)成為現(xiàn)代計算機(jī)信息系統(tǒng)和應(yīng)用系統(tǒng)的基礎(chǔ)與核心。近年來，隨著多媒體技術(shù)、空間數(shù)據(jù)庫技術(shù)和計算機(jī)網(wǎng)絡(luò)的飛速發(fā)展，數(shù)據(jù)庫系統(tǒng)的發(fā)展十分迅速，應(yīng)用領(lǐng)域越來越廣，要保障在這些領(lǐng)域中應(yīng)用的信息系統(tǒng)高效、正常運(yùn)行，必須處理好數(shù)據(jù)庫性能優(yōu)化問題。大多數(shù)數(shù)據(jù)庫在運(yùn)行一段時間后都會產(chǎn)生一定的性能問題，而數(shù)據(jù)庫系統(tǒng)的性能決定了數(shù)據(jù)庫的可用性和生命力。本文主要探討SQL Server數(shù)據(jù)庫性能優(yōu)化方面的問題。

1 數(shù)據(jù)庫優(yōu)化的目標(biāo)和基本原則

數(shù)據(jù)庫優(yōu)化的目標(biāo)是避免磁盤I/O瓶頸、減少CPU利用率，減少資源競爭。一般來說應(yīng)該遵循以下原則：

（1） 合理設(shè)計數(shù)據(jù)庫，關(guān)鍵字段建立索引。

（2） 合理的數(shù)據(jù)庫對象放置策略。

（3） SQL語句語法的優(yōu)化。

（4） 合理使用外部工具，使SQL變得更加靈活和高效。

（5） 清理刪除日志，備份數(shù)據(jù)庫和清除垃圾數(shù)據(jù)。

2 數(shù)據(jù)庫優(yōu)化的實(shí)現(xiàn)方法

2.1 數(shù)據(jù)庫設(shè)計的優(yōu)化

設(shè)計階段是決定系統(tǒng)性能的關(guān)鍵階段，而且關(guān)系到以后幾乎所有性能調(diào)優(yōu)的過程數(shù)據(jù)庫設(shè)計。

2.1.1 索引設(shè)計

索引需要根據(jù)預(yù)計的數(shù)據(jù)量和查詢來設(shè)計，可能與將來實(shí)際使用會有所區(qū)別。關(guān)于索引，應(yīng)該注意以下幾個方面：

（1） 根據(jù)數(shù)據(jù)量決定哪些表需要增加索引，數(shù)據(jù)量小的可以只有主鍵。

（2） 根據(jù)使用頻率決定哪些字段需要建立索引，選擇經(jīng)常作為連接條件、篩選條件、聚合查詢、排序的字段作為索引的候選字段。

（3） 把經(jīng)常出現(xiàn)的字段組臺在—起，組成組合索引，組合索引的宇段順序與主鍵一樣，也需要把最常用的字段放在前面，把重復(fù)率低的字段放在前面。

（4） 一個表不要加太多索引。

（5） 要注意索引的維護(hù)，周期性重建索引，重新編譯存儲過程。

2.1.2 字段的設(shè)計

字段是數(shù)據(jù)庫最基本的單位，其設(shè)計對性能的影響很大：

（1） 數(shù)據(jù)類型盡量用數(shù)字型。

（2） 數(shù)據(jù)類型盡量小（在滿足可預(yù)見未來需求的前提下）。

（3） 盡量不要允許NULL（可以用NOT NULL+DEFAULT代替）。

（4） 少用TEXT和IMAGE。

（5） 自增字段要慎用，不利于數(shù)據(jù)遷移。

（6） 數(shù)據(jù)庫物理存儲和環(huán)境的設(shè)計。在設(shè)計階段，可以對數(shù)據(jù)庫的物理存儲、操作系統(tǒng)環(huán)境、網(wǎng)絡(luò)環(huán)境進(jìn)行必要的設(shè)計，使系統(tǒng)在將來能適應(yīng)比較多的用戶并發(fā)和比較大的數(shù)據(jù)量。這里需要注意文件組的作用，使用文件組可以有效地將I/O操作分散到不同的物理硬盤，提高并發(fā)能力。

2.2 合理的數(shù)據(jù)庫對象放置策略

數(shù)據(jù)庫對象放置策略是均勻地把數(shù)據(jù)分布在系統(tǒng)磁盤中，平衡I/O訪問，避免I/O瓶頸。

（1） 訪問分散到不同的磁盤，使用戶數(shù)據(jù)盡可能跨越多個設(shè)備，多個I/O運(yùn)轉(zhuǎn)，避免I/O競爭，克服訪問瓶頸，分別放置隨機(jī)訪問和連續(xù)訪問數(shù)據(jù)。

（2） 分離系統(tǒng)數(shù)據(jù)庫I/O和應(yīng)用數(shù)據(jù)庫I/O。把系統(tǒng)審計表和臨時庫表放在不忙的磁盤上。

（3） 把事務(wù)日志放在單獨(dú)的磁盤上，減少磁盤I/O開銷。

（4） 把頻繁訪問的“活性”表放在不同的磁盤上；把頻繁用的表、頻繁做Join操作的表分別放在單獨(dú)的磁盤上，甚至把頻繁訪問的表的字段放在不同的磁盤上，把訪問分散到不同的磁盤上，避免I/O爭奪。

（5） 利用段分離頻繁訪問的表及其索引（非聚族的）分離文本和圖像數(shù)據(jù)。段的目的是平衡I/O，避免瓶頸，增加吞吐量，實(shí)現(xiàn)并行掃描，提高并發(fā)度，最大化磁盤的吞吐量。利用邏輯段功能，分別放置“活性”表及其非聚族索引以平衡I/O。當(dāng)然最好利用系統(tǒng)的默認(rèn)段。另外，利用段可以使備份和恢復(fù)數(shù)據(jù)更加靈活，使系統(tǒng)授權(quán)更加靈活。

2.3 編碼階段的優(yōu)化

下面羅列一些編程階段需要注意的事項(xiàng)。

2.3.1 只返回需要的數(shù)據(jù)

返回數(shù)據(jù)到客戶端至少需要數(shù)據(jù)庫提取數(shù)據(jù)、網(wǎng)絡(luò)傳輸數(shù)據(jù)、客戶端接收數(shù)據(jù)以及客戶端處理數(shù)據(jù)等環(huán)節(jié)，如果返回不需要的數(shù)據(jù)，就會增加服務(wù)器、網(wǎng)絡(luò)和客戶端的無效勞動，其害處是顯而易見的，避免這類事件需要注意：

（1） 橫向來看，不要寫SELECT *的語句，而是選擇需要的字段。

（2） 縱向來看，合理寫WHERE子句，不要寫沒有WHERE的SQL語句。

（3） 注意SELECT INTO后的WHERE子句，因?yàn)镾ELECT INTO把數(shù)據(jù)插入到臨時表，這個過程會鎖定一些系統(tǒng)表，如果這個WHERE子句返回的數(shù)據(jù)過多或者速度太慢，會造成系統(tǒng)表長期鎖定，堵塞其他進(jìn)程。

（4） 對于聚合查詢，可以用HAVING子句進(jìn)一步限定返回的行。

2.3.2 盡量少做重復(fù)的工作

這一點(diǎn)和上一點(diǎn)的目的是一樣的，就是盡量減少無效工作，但是這一點(diǎn)的側(cè)重點(diǎn)在客戶端程序，需要注意的事項(xiàng)如下：

（1） 控制同一語句的多次執(zhí)行，特別是一些基礎(chǔ)數(shù)據(jù)的多次執(zhí)行是很多程序員很少注意的。

（2） 減少多次的數(shù)據(jù)轉(zhuǎn)換，也許需要數(shù)據(jù)轉(zhuǎn)換是設(shè)計的問題，但是減少次數(shù)是程序員可以做到的。

（3） 杜絕不必要的子查詢和連接表，子查詢在執(zhí)行計劃一般解釋成外連接，多余的連接表帶來額外的開銷。

（4） 合并對同一表同一條件的多次UPDATE，比如：

UPDATE EMPLOYEE SET FNAME=′ZHUOXI′ WHERE EMP_ID=′790113′

UPDATE EMPLOYEE SET LNAME=′ZHAO WHERE EMP_ID=′790113′

這兩個語句應(yīng)該合并成以下一個語句：

UPDATE EMPLOYEE SET FNAME=′ZHUOXI′，LNAME=′ZHAO′WHERE EMP_ID='790113'

（5） UPDATE操作不要拆成DELETE操作+INSERT操作的形式，雖然功能相同，但是性能差別是很大的。

（6） 不要寫一些沒有意義的查詢，比如 SELECT * FROM EMPLOYEE WHERE 1=2。

2.3.3 注意事務(wù)和鎖

事務(wù)是數(shù)據(jù)庫應(yīng)用中和重要的工具，它有原子性、一致性、隔離性、持久性這4個屬性，很多操作我們都需要利用事務(wù)來保證數(shù)據(jù)的正確性。在使用事務(wù)中我們需要做到盡量避免死鎖、盡量減少阻塞。具體需要特別注意以下方面：

（1） 事務(wù)操作過程要盡量小，能拆分的事務(wù)要拆分開來。

（2） 事務(wù)操作過程不應(yīng)該有交互，因?yàn)榻换サ却臅r候，事務(wù)并未結(jié)束，可能鎖定了很多資源。

（3） 事務(wù)操作過程要按同一順序訪問對象。

（4） 提高事務(wù)中每個語句的效率，利用索引和其他方法提高每個語句的效率可以有效地減少整個事務(wù)的執(zhí)行時間。

（5） 盡量不要指定鎖類型和索引，SQL Server允許我們自己指定語句使用的鎖類型和索引，但是一般情況下，SQL Server優(yōu)化器選擇的鎖類型和索引在當(dāng)前數(shù)據(jù)量和查詢條件下是最優(yōu)的，我們指定的可能只是在目前情況下更優(yōu)，但是數(shù)據(jù)量和數(shù)據(jù)分布在將來是會變化的。

（6） 查詢時可以用較低的隔離級別，特別是報表查詢的時候，可以選擇最低的隔離級別（未提交讀）。

2.3.4 注意臨時表和表變量的用法

在復(fù)雜系統(tǒng)中，臨時表和表變量很難避免，關(guān)于臨時表和表變量的用法，需要注意：

（1） 如果語句很復(fù)雜，連接太多，可以考慮用臨時表和表變量分步完成。

（2） 如果需要多次用到一個大表的同一部分?jǐn)?shù)據(jù)，考慮用臨時表和表變量暫存這部分?jǐn)?shù)據(jù)。

（3） 如果需要綜合多個表的數(shù)據(jù)，形成一個結(jié)果，可以考慮用臨時表和表變量分步匯總這多個表的數(shù)據(jù)。

（4） 其他情況下，應(yīng)該控制臨時表和表變量的使用。

（5） 關(guān)于臨時表和表變量的選擇，很多說法是表變量在內(nèi)存，速度快，應(yīng)該首選表變量，但是在實(shí)際使用中發(fā)現(xiàn)，這個選擇主要考慮需要放在臨時表的數(shù)據(jù)量，在數(shù)據(jù)量較多的情況下，臨時表的速度反而更快。

2.4 利用SQL Server新增功能和自帶工具優(yōu)化數(shù)據(jù)庫

SQL Server數(shù)據(jù)庫本身自帶了一些好的數(shù)據(jù)庫管理工具，熟練運(yùn)用這些工具能夠提高優(yōu)化工作的效率。如在SQL Server 2005中，SQL Server Profiler和數(shù)據(jù)庫引擎優(yōu)化顧問是比較常用的工具，SQL Server Profiler能夠查找并診斷運(yùn)行慢的查詢，監(jiān)視SQL Server的性能以優(yōu)化工作負(fù)荷。分析數(shù)據(jù)庫的工作負(fù)荷效果后，數(shù)據(jù)庫引擎優(yōu)化顧問會提供在SQL Server數(shù)據(jù)庫中添加、刪除或修改物理設(shè)計結(jié)構(gòu)的建議，而且可以使用數(shù)據(jù)庫引擎優(yōu)化顧問進(jìn)行探索性分析，在不用首先實(shí)現(xiàn)這些結(jié)構(gòu)的情況下評估它們對數(shù)據(jù)庫的影響。

3 數(shù)據(jù)庫優(yōu)化技術(shù)在政務(wù)辦公系統(tǒng)中具體應(yīng)用實(shí)例

下面，筆者以常見的政務(wù)辦公系統(tǒng)為例，詳細(xì)介紹數(shù)據(jù)庫優(yōu)化的常規(guī)步驟。目前，絕大多數(shù)政務(wù)辦公系統(tǒng)都是瀏覽器/服務(wù)器（Browser/Server）模式的應(yīng)用程序，大量的數(shù)據(jù)處理和邏輯運(yùn)算通過數(shù)據(jù)庫和應(yīng)用服務(wù)器進(jìn)行，要找出數(shù)據(jù)庫服務(wù)器的瓶頸，就要從數(shù)據(jù)庫服務(wù)器硬件配置情況、服務(wù)器性能狀態(tài)和SQL數(shù)據(jù)庫性能與設(shè)計等方面同時著手，一般常用工具為微軟Windows NT提供的性能監(jiān)視器（Performance Monitor）和SQL事件探查器（SQL Server Profiler）。通過使用這兩種監(jiān)測工具，采取各種可能影響服務(wù)器和數(shù)據(jù)庫的指標(biāo)，發(fā)現(xiàn)政務(wù)辦公系統(tǒng)一般主要有以下常見問題：

（1） 數(shù)據(jù)庫日志和操作日志記錄表數(shù)據(jù)量大，數(shù)據(jù)增長速度過快。主要的業(yè)務(wù)數(shù)據(jù)表數(shù)據(jù)量巨大，個別表數(shù)據(jù)量達(dá)到千萬級別。

（2） 數(shù)據(jù)庫查詢響應(yīng)時間慢。通過SQL事件探查器（SQL Server Profiler）對數(shù)據(jù)庫的運(yùn)行腳本進(jìn)行幾個周期的監(jiān)剝，以3分鐘為一個周期，每次獲取5 000條左右SQL語句，一般會找到一些運(yùn)行時間超過5秒的查詢語句，這就是查詢中的瓶頸。

（3） 通過SQL事件探查器（SQL Server Profiler）監(jiān)測，如發(fā)現(xiàn)部分查詢效率低，查詢語句語法可以進(jìn)行進(jìn)一步的修改。

（4） 通過性能監(jiān)視器（Performance Monitor）監(jiān)視相應(yīng)硬件的負(fù)載Memory：PageFaults/sec計數(shù)器，如發(fā)現(xiàn)該值偶爾走高，表明當(dāng)時有線程競爭內(nèi)存；如在高峰期該值持續(xù)很高，表明數(shù)據(jù)庫服務(wù)器內(nèi)存可能是瓶頸。

（5） 通過性能監(jiān)視器（Performance Monitor）監(jiān)視磁盤，如發(fā)現(xiàn)平均數(shù)據(jù)讀取隊(duì)列（Avg .Disk Read Queue Length）存在積壓，則磁盤每秒讀取的數(shù)據(jù)量（Disk Read Bytes/sec）較大。

針對這些問題，利用數(shù)據(jù)庫優(yōu)化的原則和技術(shù)，我們采取以下措施：

（1） 清除日志，清除個別數(shù)據(jù)庫和表數(shù)據(jù)的數(shù)據(jù)。

（2） 定期執(zhí)行歸檔和轉(zhuǎn)歷史庫操作，將歷史數(shù)據(jù)歸檔，轉(zhuǎn)歷史庫，減少數(shù)據(jù)量。

（3） 重新建立索引，利用數(shù)據(jù)庫事件探查器SQL Profiler，找CPU或Duration列值大的語句，根據(jù)其查詢條件建立相應(yīng)索引。但是在建立索引時，要注意本文前面提到的建立索引的原則。

（4） 進(jìn)行索引的優(yōu)化，建立后臺作業(yè)程序，定期對整個數(shù)據(jù)庫的所有表重建索引，優(yōu)化數(shù)據(jù)庫。

（5） 查詢語句的優(yōu)化，對數(shù)據(jù)庫的個別存儲過程和查詢語句進(jìn)行優(yōu)化。

（6） 優(yōu)化數(shù)據(jù)庫，提高硬件性能，升級操作系統(tǒng)也是很有效的手段之一。

（7） 開啟SQL Server 2005 Enterprise Edition支持地址窗口化擴(kuò)展插件（AWE），該插件開啟后可以允許在32位版本的操作系統(tǒng)上使用4 GB以上的物理自存，最多可支持6 GB的物理內(nèi)存。這樣可以有效地利用內(nèi)存，提高數(shù)據(jù)庫性能。

4 結(jié)束語

本文只是簡單地從數(shù)據(jù)庫管理和系統(tǒng)管理方面闡述數(shù)據(jù)庫優(yōu)化的一些原則和方法。實(shí)際上，影響SQL Server性能的因素很多，遠(yuǎn)遠(yuǎn)不止本文中列出的這些。在Windows NT下，文件系統(tǒng)的選擇、網(wǎng)絡(luò)協(xié)議、開啟的服務(wù)、SQL Server的優(yōu)先級等選項(xiàng)也不同程度上影響SQL Server的性能。影響性能的因素如此之多，而應(yīng)用又不盡相同，因此找出一個通用的優(yōu)化方案幾乎是不可能的，在系統(tǒng)開發(fā)和維護(hù)的過程中必須針對運(yùn)行的情況，不斷加以調(diào)整。

主要參考文獻(xiàn)

[1] 李聰慧. 試論數(shù)據(jù)庫系統(tǒng)的優(yōu)化舉措[J]. 信息安全與技術(shù)，2012（6）.

[2] 魏琦，于林林，宋旭東. 關(guān)系數(shù)據(jù)庫查詢優(yōu)化策略研究[J]. 電腦知識與技術(shù)，2010（31）.

[3] 杜剛強(qiáng)，姜丹. 數(shù)據(jù)庫系統(tǒng)的優(yōu)化[J]. 硅谷，2011（22）.

[4] 張水平. 數(shù)據(jù)庫原理及SQL Server應(yīng)用[M]. 西安：西安交通大學(xué)出版社，2008.

[5] 閃四清. 數(shù)據(jù)庫系統(tǒng)原理與應(yīng)用教程[M]. 北京：清華大學(xué)出版社，2008.