淺談如何優化SQL Server數據庫

趙灼

[摘 要] 本文從數據庫系統優化的目標、原則入手，通過實例研討SQL Server數據庫優化的解決方法，以提高數據庫系統的運行效能。

[關鍵詞] 數據庫； 優化； SQL Server

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

[中圖分類號] TP392 [文獻標識碼] A [文章編號] 1673 - 0194（2014）03- 0125- 03

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

1 數據庫優化的目標和基本原則

數據庫優化的目標是避免磁盤I/O瓶頸、減少CPU利用率，減少資源競爭。一般來說應該遵循以下原則：

（1） 合理設計數據庫，關鍵字段建立索引。

（2） 合理的數據庫對象放置策略。

（3） SQL語句語法的優化。

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

（5） 清理刪除日志，備份數據庫和清除垃圾數據。

2 數據庫優化的實現方法

2.1 數據庫設計的優化

設計階段是決定系統性能的關鍵階段，而且關系到以后幾乎所有性能調優的過程數據庫設計。

2.1.1 索引設計

索引需要根據預計的數據量和查詢來設計，可能與將來實際使用會有所區別。關于索引，應該注意以下幾個方面：

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

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

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

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

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

2.1.2 字段的設計

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

（1） 數據類型盡量用數字型。

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

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

（4） 少用TEXT和IMAGE。

（5） 自增字段要慎用，不利于數據遷移。

（6） 數據庫物理存儲和環境的設計。在設計階段，可以對數據庫的物理存儲、操作系統環境、網絡環境進行必要的設計，使系統在將來能適應比較多的用戶并發和比較大的數據量。這里需要注意文件組的作用，使用文件組可以有效地將I/O操作分散到不同的物理硬盤，提高并發能力。

2.2 合理的數據庫對象放置策略

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

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

（2） 分離系統數據庫I/O和應用數據庫I/O。把系統審計表和臨時庫表放在不忙的磁盤上。

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

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

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

2.3 編碼階段的優化

下面羅列一些編程階段需要注意的事項。

2.3.1 只返回需要的數據

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

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

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

（3） 注意SELECT INTO后的WHERE子句，因為SELECT INTO把數據插入到臨時表，這個過程會鎖定一些系統表，如果這個WHERE子句返回的數據過多或者速度太慢，會造成系統表長期鎖定，堵塞其他進程。

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

2.3.2 盡量少做重復的工作

這一點和上一點的目的是一樣的，就是盡量減少無效工作，但是這一點的側重點在客戶端程序，需要注意的事項如下：

（1） 控制同一語句的多次執行，特別是一些基礎數據的多次執行是很多程序員很少注意的。

（2） 減少多次的數據轉換，也許需要數據轉換是設計的問題，但是減少次數是程序員可以做到的。

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

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

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

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

這兩個語句應該合并成以下一個語句：

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 注意事務和鎖

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

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

（2） 事務操作過程不應該有交互，因為交互等待的時候，事務并未結束，可能鎖定了很多資源。

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

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

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

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

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

在復雜系統中，臨時表和表變量很難避免，關于臨時表和表變量的用法，需要注意：

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

（2） 如果需要多次用到一個大表的同一部分數據，考慮用臨時表和表變量暫存這部分數據。

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

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

（5） 關于臨時表和表變量的選擇，很多說法是表變量在內存，速度快，應該首選表變量，但是在實際使用中發現，這個選擇主要考慮需要放在臨時表的數據量，在數據量較多的情況下，臨時表的速度反而更快。

2.4 利用SQL Server新增功能和自帶工具優化數據庫

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

3 數據庫優化技術在政務辦公系統中具體應用實例

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

（1） 數據庫日志和操作日志記錄表數據量大，數據增長速度過快。主要的業務數據表數據量巨大，個別表數據量達到千萬級別。

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

（3） 通過SQL事件探查器（SQL Server Profiler）監測，如發現部分查詢效率低，查詢語句語法可以進行進一步的修改。

（4） 通過性能監視器（Performance Monitor）監視相應硬件的負載Memory：PageFaults/sec計數器，如發現該值偶爾走高，表明當時有線程競爭內存；如在高峰期該值持續很高，表明數據庫服務器內存可能是瓶頸。

（5） 通過性能監視器（Performance Monitor）監視磁盤，如發現平均數據讀取隊列（Avg .Disk Read Queue Length）存在積壓，則磁盤每秒讀取的數據量（Disk Read Bytes/sec）較大。

針對這些問題，利用數據庫優化的原則和技術，我們采取以下措施：

（1） 清除日志，清除個別數據庫和表數據的數據。

（2） 定期執行歸檔和轉歷史庫操作，將歷史數據歸檔，轉歷史庫，減少數據量。

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

（4） 進行索引的優化，建立后臺作業程序，定期對整個數據庫的所有表重建索引，優化數據庫。

（5） 查詢語句的優化，對數據庫的個別存儲過程和查詢語句進行優化。

（6） 優化數據庫，提高硬件性能，升級操作系統也是很有效的手段之一。

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

4 結束語

本文只是簡單地從數據庫管理和系統管理方面闡述數據庫優化的一些原則和方法。實際上，影響SQL Server性能的因素很多，遠遠不止本文中列出的這些。在Windows NT下，文件系統的選擇、網絡協議、開啟的服務、SQL Server的優先級等選項也不同程度上影響SQL Server的性能。影響性能的因素如此之多，而應用又不盡相同，因此找出一個通用的優化方案幾乎是不可能的，在系統開發和維護的過程中必須針對運行的情況，不斷加以調整。

主要參考文獻

[1] 李聰慧. 試論數據庫系統的優化舉措[J]. 信息安全與技術，2012（6）.

[2] 魏琦，于林林，宋旭東. 關系數據庫查詢優化策略研究[J]. 電腦知識與技術，2010（31）.

[3] 杜剛強，姜丹. 數據庫系統的優化[J]. 硅谷，2011（22）.

[4] 張水平. 數據庫原理及SQL Server應用[M]. 西安：西安交通大學出版社，2008.

[5] 閃四清. 數據庫系統原理與應用教程[M]. 北京：清華大學出版社，2008.