數據庫查詢優(yōu)化方法的研究與探索

劉玉紅，王俊峰

（1.烏魯木齊市教育招生考試中心 新疆 烏魯木齊 830002；2.重慶電子工程職業(yè)學院 重慶 401331）

關系數據庫以其簡明的結構 （即維一的數據庫結構關系）和嚴密的理論（關系代數）成為整個數據庫領域最重要的組成部分，SQL Server數據庫又是關系數據庫中應用最廣的數據庫。他功能強大、操作簡便，日益為廣大數據庫用戶所喜愛，越來越多的開發(fā)工具提供了與SQL Server的接口。了解和掌握SQL Server的功能，對于一個數據庫開發(fā)管理人員來說非常必要。SQL Server是一種高性能的大型關系型數據庫管理系統，廣泛的應用在C/S和B/S體系結構的數據庫系統中。評價系統性能優(yōu)化的標準有：吞吐量、響應時間、并行能力等。文中主要探討如何優(yōu)化SQL，以便取得最快的系統響應速度[1]。

數據庫的優(yōu)化通常可以通過對網絡、硬件、操作系統、數據庫參數和應用程序的優(yōu)化來進行。在設計階段進行數據庫性能優(yōu)化的成本最低，收益最大。在成品階段進行數據庫性能優(yōu)化的成本最高，收益最小。最常見的優(yōu)化手段就是對硬件的升級。根據統計，對網絡、硬件、操作系統、數據庫參數進行優(yōu)化所獲得的性能提升，全部加起來只占數據庫系統性能提升的40%左右，其余的60%系統性能提升來自對應用程序的優(yōu)化。許多優(yōu)化專家認為，對應用程序的優(yōu)化可以得到80%的系統性能的提升[2]。

1 數據庫性能良好的標準

對于同一個系統的實施可以設計出多個數據庫模型，這些模型由于性能目標需求差異而不同。事實上，對于同一需求，不同數據庫設計人員也會設計出不同的模型，雖然只要它們能提供所需的性能，則就是正確的模型，但是作為數據庫設計人員應該盡可能地采用各種技術在最大程度上提高數據庫的整體性能。數據庫性能的高低一般用兩個方面的指標來衡量：響應時間和吞吐量。響應越快，吞吐量越大，數據庫性能越好。不過，響應時間和吞吐量并不是都能一起得到改善的。下面是一般大型系統數據庫的性能標準[5]：

1）單條記錄的更新應當在1 s之內；

2）多條記錄的更新不超過10 s；

3）對于于4個表的、數據有一定限度的查詢應在5 s內完成；

4）對于有一定限度的查詢多表查詢應在10 s內完成；

5）整個表的查詢時間應在30 s內完成。

以下針對以上目標就影響數據庫性能的一些技術予以分析，當然不管什么技術對于數據庫性能的優(yōu)化都必須以提高整體性能為原則，而不是僅僅提高個別查詢的速度。

2 數據庫性能優(yōu)化的分析

2.1 影響數據庫性能的因素

SQL Server數據庫查詢速度慢的原因有很多，常見的有以下幾種：

1）沒有索引或者沒有用到索引（這是查詢慢最常見的問題，是程序設計的缺陷）

2）I/O吞吐量小，形成了瓶頸效應

3）沒有創(chuàng)建計算列導致查詢不優(yōu)化

4）內存不足

5）網絡速度慢

6）查詢出的數據量過大（可以采用多次查詢，其他的方法降低數據量）

7）鎖或者死鎖（這也是查詢慢最常見的問題，是程序設計的缺陷）

8）返回了不必要的行和列

9）查詢語句不好，沒有優(yōu)化

2.2 數據庫優(yōu)化分類

1）應用層——大部分性能的獲得來自于對SQL應用中查詢的優(yōu)化，這必須是以好的數據庫設計為基礎的。

2）數據庫層——應用共享在數據庫層中的資源，這些資源包括硬盤，事務日志和數據cache。

3）服務器層——在服務器層有許多共享的資源，包括數據高速緩存，存儲過程高速緩存，鎖，CPU等。

4）設備層——指的是存儲數據的磁盤及其控制器，在這一層，你應尤其關注磁盤的I／O。

5）網絡層——指連接用戶和SQL Server的網絡。

6）硬件層——指可利用的CPU。

7）操作系統層——理想地，SQL Server是一臺機器的唯一主要應用，它必須和操作系統以及其他軟件如Backup Server或SQL Server Monitor共享處理器、內存以及其他資源。

在大多數情況下面，對應用層進行優(yōu)化，因為對應用性能的優(yōu)化是設計和編程人員樂于接受的功能，其結果能被觀測及檢驗。查詢的性能是SQL應用的整個性能的一個關鍵[6]。

3 數據庫優(yōu)化策略研究

3.1 應用層優(yōu)化基本策略

1）事務設計能夠減少并發(fā)，因為長的事務保持占用鎖，也就減少了其他用戶對相關數據的存取

2）關聯一致性保證的策略。對數據查詢修改時需要考慮join操作對性能的影響

3）索引可以改善查詢性能，但也會增加修改數據的時間

4）為了安全而設立的審計限制了性能

5）遠程處理或復制處理能夠把決策支持從OLTP機器中分離出來

6）利用存儲過程來減少編譯時間和網絡的利用

7）利用最少量的鎖去滿足你的應用需要

3.2 數據庫層優(yōu)化基本策略

1）建立優(yōu)化的備份和恢復方案

2）在設備上分布存儲數據

3）審計操作影響性能；僅審計你所需要的

4）日常的維護活動將導致性能的降低和導致用戶不能操作數據庫表在數據庫層上優(yōu)化選擇包括：

①利用事務日志的閾值來自動轉儲事務日志防止其超出使用空間

②在數據段中用閾值來監(jiān)視空間的使用③利用分區(qū)來加速數據的裝入

④對象的定位以避免硬盤的競爭

⑤把重要表和索引放入cache中，保證隨時取得

3.3 服務器層優(yōu)化策略

1）應用的類型——服務器是支持OLTP還是DSS，或者兩者都支持

2）所支持的用戶數影響優(yōu)化決策——隨著用戶數的增加，對資源的競爭會發(fā)生改變

3）當用戶數和事務數達到一定的數量時復制服務器或其他分布式處理是一個解決的方法

4）優(yōu)化內存——一個關鍵的配置參數和其他方面的參數

5）決策是客戶端處理還是服務器端處理

6）配置cache的大小和I／O的大小

7）增加多個CPU

8）為空閑時間排定批處理任務和生成報表

9）工作負荷發(fā)生改變，重新配置特定參數

10）決定是否可能把DSS移到另一個SQL服務器中設備層

3.4 設備層優(yōu)化策略

1）主設備、包含用戶數據庫的設備，用戶數據設備，或數據庫日志是否要鏡像

2）怎樣在設備之間分布系統數據庫、用戶數據庫和數據庫日志

3）為獲得對堆表插入操作的高性能，是否有必要進行分區(qū)設備層上優(yōu)化的選項包括

4）用多個中等大小的設備及多個控制器可能比用少量的大設備有更好的I／O性能

5）分布數據庫，表和索引以在不同的設備上進行I／O裝載

3.5 網絡層優(yōu)化策略

實際上，SQL Server的所有用戶都是通過網絡存取他們的數據。網絡層上的配置包的大小，以使其與應用的需要相匹配

1）配置子網

2）分隔出繁忙的網絡運用

3）創(chuàng)建一個高容量的網絡

4）配置多個網絡引擎

5）更好地設計應用，限制所需的網絡傳輸

3.6 在硬件層上優(yōu)化策略

1）增加CPU以適應工作負荷

2）配置調度程序以提高CPU利用率

3）遵循多處理器應用設計指導以減少競爭

4）配置多個數據cache操作系統層

3.7 操作系統層優(yōu)化策略

1）文件系統——是否被SQL Server獨占使用

2）內存管理——精確估算操作系統和其他程序的內存占用

3）CPU的利用——整個系統共有多少處理器可用?有多少分配給SQL Server

4）在文件和原始分區(qū)之間選擇

5）增加內存

6）把客戶操作和批處理移到其他機器上

7）SQL Server利用多個 CPU

4 與應用程序相關的數據庫查詢優(yōu)化

查詢優(yōu)化技術在DBMS性能提高方面有著非常重要的地位。實際系統對查詢優(yōu)化具體實現不盡相同，但一般來說可以歸納為4個步驟：

1）將查詢轉換成某種內部表示，通常是語法樹；

2）根據一定的等價變換規(guī)則把語法樹轉換成標準 （優(yōu)化）技術；

3）選擇低層的操作算法；

4）生成查詢計劃。

絕大多數性能的獲得來自于優(yōu)秀的數據庫設計、精確的查詢分析和適當的索引。為了取得更好的數據庫性能，我們就需要對數據庫進行優(yōu)化，減少系統資源的競爭，如對數據cache，過程cache，系統資源和CPU的競爭。SQL優(yōu)化的大部分性能的獲得來自于對SQL應用中查詢的優(yōu)化[3]。

目前的DBMS大都采用基于代價的優(yōu)化算法，下面給出的優(yōu)化策略一般能提高數據庫查詢效率：選擇運算應盡可能先做；應盡可能經常地編譯存儲過程，使存儲過程的查詢計劃和數據庫的數據存放結構保持一致；有效使用索引。查詢條件和索引的配合使用，對SQL語句的性能至關重要。下面是兩種常見的情況：

其一，如果查詢條件中包括索引的第一個列，而且結果列都在索引列中，系統使用匹配索引定位，會定位到索引的頁級，這時可從索引頁中直接提取結果，不需要使用數據頁。其二，如果查詢條件中不包括索引的第一個列，而且結果列都在索引列中，系統使用非匹配索引掃描，不掃描數據頁，從索引頁中直接提取結果。這種情況也不使用數據頁。

創(chuàng)建高效率查詢，可以充分利用索引的where條件書寫格式為“column operator expression”，這里的 operator一般是：=，>，<，>=，<=，is null。 而如果 operator是！=、！，便不能充分利用索引。如果要充分利用索引，在column中就不要包括函數和其他操作。expression必須是常量或可以轉化成常量。查詢優(yōu)化器認為，between 相當于“>=”和“<=”，“l(fā)ike‘Ger%”’相當于“>=‘Ger’and<‘Ges”’。 但是“‘like’%ebr”’因為沒有給出首字母，就不能轉化成這種結果。

在書寫SQL語句時，對于表連接的情況，注意盡量少寫冗余條件。一般要在SARG（搜索參數）的列上放置一個索引。如果被查詢列都包括在索引列中，這種查詢叫索引覆蓋查詢。這種查詢效率比較高，應盡量使用這種查詢。在做表連接查詢時，在外表的連接列上建立索引，可以大大加快速度。而且，查詢速度也和表的排列順序有關，如果行數大的表放在后面，可以提高速度。

5 結束語

數據庫的優(yōu)化是一個系統工程，在數據庫實施過程當中影響性能優(yōu)良的因素很多，而不同項目的應用要求又各不相同，所以要找出所有完全通用的優(yōu)化技術是不現實的，在數據庫開發(fā)和維護的過程中，必須針對數據庫運行的具體情況加以分析和調整。

[1]陳佳.基于SQL server數據庫優(yōu)化查詢的分析[J].企業(yè)導報，2010（8）:179-180.CHEN Jia.The analysis of database optimization querying based on SQL Server[J].Enterprise Guide，2010（8）:179-180.

[2]曾實.ORACLE數據庫優(yōu)化技術研究[J].科技信息，2011（27）:84，56.ZENG Shi.The research in ORACLE database optimization technology[J].Technology Information，2011（27）:56-84.

[3]徐鑫濤.淺談數據庫優(yōu)化 [J].中國科技信息，2008（4）:111-115.XU Xin-tao.About database optimization[J].Chinese Technology Information，2008（4）:111-115.

[4]劉姝.DB2數據庫設計及優(yōu)化技術研究[J].信息安全與技術，2011（11）:38-40 LIU Shu.DB2 database design and optimization technology tesearch[J].Information Safe and Technology，2011 （11）:38-40.

[5]三味工作室編著.SQL Server 7.5管理指南[M].北京：中國水利水電出版社，1998.

[6]Oracle數據庫管理與開發(fā)[M].大連：東軟電子出版社，2009.