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

劉玉紅，王俊峰

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

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

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

1 數(shù)據(jù)庫性能良好的標(biāo)準(zhǔn)

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

1）單條記錄的更新應(yīng)當(dāng)在1 s之內(nèi)；

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

3）對于于4個(gè)表的、數(shù)據(jù)有一定限度的查詢應(yīng)在5 s內(nèi)完成；

4）對于有一定限度的查詢多表查詢應(yīng)在10 s內(nèi)完成；

5）整個(gè)表的查詢時(shí)間應(yīng)在30 s內(nèi)完成。

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

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

2.1 影響數(shù)據(jù)庫性能的因素

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

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

2）I/O吞吐量小，形成了瓶頸效應(yīng)

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

4）內(nèi)存不足

5）網(wǎng)絡(luò)速度慢

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

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

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

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

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

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

2）數(shù)據(jù)庫層——應(yīng)用共享在數(shù)據(jù)庫層中的資源，這些資源包括硬盤，事務(wù)日志和數(shù)據(jù)cache。

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

4）設(shè)備層——指的是存儲(chǔ)數(shù)據(jù)的磁盤及其控制器，在這一層，你應(yīng)尤其關(guān)注磁盤的I／O。

5）網(wǎng)絡(luò)層——指連接用戶和SQL Server的網(wǎng)絡(luò)。

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

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

在大多數(shù)情況下面，對應(yīng)用層進(jìn)行優(yōu)化，因?yàn)閷?yīng)用性能的優(yōu)化是設(shè)計(jì)和編程人員樂于接受的功能，其結(jié)果能被觀測及檢驗(yàn)。查詢的性能是SQL應(yīng)用的整個(gè)性能的一個(gè)關(guān)鍵[6]。

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

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

1）事務(wù)設(shè)計(jì)能夠減少并發(fā)，因?yàn)殚L的事務(wù)保持占用鎖，也就減少了其他用戶對相關(guān)數(shù)據(jù)的存取

2）關(guān)聯(lián)一致性保證的策略。對數(shù)據(jù)查詢修改時(shí)需要考慮join操作對性能的影響

3）索引可以改善查詢性能，但也會(huì)增加修改數(shù)據(jù)的時(shí)間

4）為了安全而設(shè)立的審計(jì)限制了性能

5）遠(yuǎn)程處理或復(fù)制處理能夠把決策支持從OLTP機(jī)器中分離出來

6）利用存儲(chǔ)過程來減少編譯時(shí)間和網(wǎng)絡(luò)的利用

7）利用最少量的鎖去滿足你的應(yīng)用需要

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

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

2）在設(shè)備上分布存儲(chǔ)數(shù)據(jù)

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

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

①利用事務(wù)日志的閾值來自動(dòng)轉(zhuǎn)儲(chǔ)事務(wù)日志防止其超出使用空間

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

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

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

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

1）應(yīng)用的類型——服務(wù)器是支持OLTP還是DSS，或者兩者都支持

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

3）當(dāng)用戶數(shù)和事務(wù)數(shù)達(dá)到一定的數(shù)量時(shí)復(fù)制服務(wù)器或其他分布式處理是一個(gè)解決的方法

4）優(yōu)化內(nèi)存——一個(gè)關(guān)鍵的配置參數(shù)和其他方面的參數(shù)

5）決策是客戶端處理還是服務(wù)器端處理

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

7）增加多個(gè)CPU

8）為空閑時(shí)間排定批處理任務(wù)和生成報(bào)表

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

10）決定是否可能把DSS移到另一個(gè)SQL服務(wù)器中設(shè)備層

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

1）主設(shè)備、包含用戶數(shù)據(jù)庫的設(shè)備，用戶數(shù)據(jù)設(shè)備，或數(shù)據(jù)庫日志是否要鏡像

2）怎樣在設(shè)備之間分布系統(tǒng)數(shù)據(jù)庫、用戶數(shù)據(jù)庫和數(shù)據(jù)庫日志

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

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

5）分布數(shù)據(jù)庫，表和索引以在不同的設(shè)備上進(jìn)行I／O裝載

3.5 網(wǎng)絡(luò)層優(yōu)化策略

實(shí)際上，SQL Server的所有用戶都是通過網(wǎng)絡(luò)存取他們的數(shù)據(jù)。網(wǎng)絡(luò)層上的配置包的大小，以使其與應(yīng)用的需要相匹配

1）配置子網(wǎng)

2）分隔出繁忙的網(wǎng)絡(luò)運(yùn)用

3）創(chuàng)建一個(gè)高容量的網(wǎng)絡(luò)

4）配置多個(gè)網(wǎng)絡(luò)引擎

5）更好地設(shè)計(jì)應(yīng)用，限制所需的網(wǎng)絡(luò)傳輸

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

1）增加CPU以適應(yīng)工作負(fù)荷

2）配置調(diào)度程序以提高CPU利用率

3）遵循多處理器應(yīng)用設(shè)計(jì)指導(dǎo)以減少競爭

4）配置多個(gè)數(shù)據(jù)cache操作系統(tǒng)層

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

1）文件系統(tǒng)——是否被SQL Server獨(dú)占使用

2）內(nèi)存管理——精確估算操作系統(tǒng)和其他程序的內(nèi)存占用

3）CPU的利用——整個(gè)系統(tǒng)共有多少處理器可用?有多少分配給SQL Server

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

5）增加內(nèi)存

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

7）SQL Server利用多個(gè) CPU

4 與應(yīng)用程序相關(guān)的數(shù)據(jù)庫查詢優(yōu)化

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

1）將查詢轉(zhuǎn)換成某種內(nèi)部表示，通常是語法樹；

2）根據(jù)一定的等價(jià)變換規(guī)則把語法樹轉(zhuǎn)換成標(biāo)準(zhǔn) （優(yōu)化）技術(shù)；

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

4）生成查詢計(jì)劃。

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

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

其一，如果查詢條件中包括索引的第一個(gè)列，而且結(jié)果列都在索引列中，系統(tǒng)使用匹配索引定位，會(huì)定位到索引的頁級，這時(shí)可從索引頁中直接提取結(jié)果，不需要使用數(shù)據(jù)頁。其二，如果查詢條件中不包括索引的第一個(gè)列，而且結(jié)果列都在索引列中，系統(tǒng)使用非匹配索引掃描，不掃描數(shù)據(jù)頁，從索引頁中直接提取結(jié)果。這種情況也不使用數(shù)據(jù)頁。

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

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

5 結(jié)束語

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

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

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

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

[4]劉姝.DB2數(shù)據(jù)庫設(shè)計(jì)及優(yōu)化技術(shù)研究[J].信息安全與技術(shù)，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數(shù)據(jù)庫管理與開發(fā)[M].大連：東軟電子出版社，2009.