ＳＱＬ Ｓｅｒｖｅｒ環(huán)境下的ＳＱＬ優(yōu)化方法探討

摘要：在SQL SERVER環(huán)境下對(duì)查詢符號(hào)、聯(lián)合查詢、多條件，聯(lián)接運(yùn)算等幾個(gè)常見的SQL優(yōu)化問題進(jìn)行了分析研究，指出了由于優(yōu)化器的參與，純理論的優(yōu)化建議對(duì)SQL用戶的實(shí)踐可能產(chǎn)生的誤導(dǎo)。

關(guān)鍵詞：SQL；優(yōu)化；SQL SERVER；數(shù)據(jù)庫(kù)

中圖分類號(hào)：TP311文獻(xiàn)標(biāo)識(shí)碼：A 文章編號(hào)：1009-3044(2008)15-21002-02

The Method of Optimization in SQL Server

WU Xian-feng

(Sichuan Institute of Administration，Chengdu 610004，China)

Abstract:This paper research some questions of SQL performance optimization with SQL SERVER such as operator， UNION operation， Multi-search condition， JION operation. Considers that， because of Models of optimizing in database， some suggestion of optimization base on pure theory would misadvise user of SQL.

Key words: SQL; optimization; SQL SERVER; database

1 引言

SQL查詢的優(yōu)化是數(shù)據(jù)庫(kù)應(yīng)用領(lǐng)域的一個(gè)重要問題，關(guān)于這個(gè)問題的討論很多。但遺憾的是很多討論更多的從SQL語(yǔ)句的語(yǔ)法和構(gòu)造的層面來(lái)進(jìn)行說(shuō)明，脫離具體的數(shù)據(jù)庫(kù)環(huán)境。事實(shí)上，優(yōu)化問題是一個(gè)實(shí)踐性很強(qiáng)的問題，數(shù)據(jù)庫(kù)在執(zhí)行語(yǔ)句的時(shí)候，并非完全按SQL語(yǔ)句中規(guī)定的邏輯和層次來(lái)執(zhí)行，數(shù)據(jù)庫(kù)會(huì)對(duì)SQL語(yǔ)句進(jìn)行解析后，按特定的方式來(lái)執(zhí)行。不同的數(shù)據(jù)庫(kù)處理同樣的SQL語(yǔ)句也會(huì)有區(qū)別。

這里我們結(jié)合SQL SERVER對(duì)比較常見的幾種優(yōu)化建議進(jìn)行探討。

2 查詢符號(hào)的優(yōu)化

在條件表達(dá)式中，查詢符號(hào)的使用可能影響到索引能否被使用，從而影響查詢的效率，一般遵循這樣一些原則：不要使用表達(dá)式，盡量避免負(fù)邏輯，避免語(yǔ)句出現(xiàn)LIKE“%”等，此外，研究者一般認(rèn)為，使用or連接條件時(shí)，會(huì)被強(qiáng)制使用全表掃描，也有人認(rèn)為exist的效率遠(yuǎn)遠(yuǎn)低于in 的效率。

我們選用一個(gè)有10萬(wàn)行的表在SQL SERVER環(huán)境下進(jìn)行測(cè)試，通過SQL SERVER提供的查詢計(jì)劃圖形顯示，我們很容易得到查詢是按何種方式展開的。結(jié)果證明在where子句中，如果在字段上使用了代數(shù)表達(dá)式或函數(shù)時(shí)，使用LIKE“%”，查詢方式是全表掃描，但是在負(fù)邏輯，or連接條件等情況下，卻并不一定采用全表掃描的方式。

仔細(xì)分析其過程，可以看出，SQL SERVER優(yōu)化器首先預(yù)測(cè)結(jié)果行的數(shù)量，如果結(jié)果行數(shù)量較多時(shí)，則無(wú)論是負(fù)邏輯還是正邏輯，均采用全表掃描。如果結(jié)果行較少，我們可稱其為具有強(qiáng)選擇性，則優(yōu)化器會(huì)自動(dòng)將負(fù)邏輯轉(zhuǎn)化為等價(jià)的正邏輯表達(dá)，然后在索引上掃描。

同樣，在分析or連接時(shí)，也可以看到，執(zhí)行計(jì)劃同樣有可能在索引上掃描，是全表掃描還是掃描索引，不是取決于使用and或是or連接，而是取決于對(duì)結(jié)果行數(shù)量的預(yù)測(cè)。即使是在or的兩端是不同的字段時(shí)，如：discount=1 or ordered=10000 ，只要存在相關(guān)的索引，查詢時(shí)均可使用這些索引。

與此相關(guān)的另一個(gè)問題是，許多人認(rèn)為or符號(hào)連接條件將被強(qiáng)制全表掃描，所以建議用UNION來(lái)替代OR符號(hào)，以提高效率，事實(shí)上這也是得不償失的。因?yàn)椋话闱闆r下，在UNION中可以用到的索引在or符號(hào)表達(dá)時(shí)同樣可以用到。在少數(shù)情況下，確實(shí)存在使用or符號(hào)時(shí)不能利用索引，而在用UNION表達(dá)時(shí)可以利用索引的情況。但由于UNION操作中多了合并插入、去除重復(fù)行等工作，所以總體效率還是會(huì)低于在單一的SELECT語(yǔ)句中用OR符號(hào)。

3 聯(lián)合查詢時(shí)的優(yōu)化

在聯(lián)合（union）運(yùn)算時(shí)，一般認(rèn)為需要考慮的問題是，在SQL語(yǔ)句中各個(gè)SELECT語(yǔ)句的順序問題。主張將結(jié)果行相對(duì)較少的行放在靠后，這樣可以減少插入操作。

但在SQL SERVER中，我們針對(duì)這一問題設(shè)計(jì)含兩個(gè)SELECT子句的UNION語(yǔ)句，使其兩個(gè)SELECT語(yǔ)句中返回的行數(shù)又較大的差別，可以看到這樣的結(jié)果：

(1)如果兩個(gè)SELECT語(yǔ)句中，如果小結(jié)果語(yǔ)句具有強(qiáng)選擇性，因而利用了索引掃描。這種情況下，大結(jié)果放在前面效率較高。

(2)如果兩個(gè)SELECT語(yǔ)句中均不具有強(qiáng)選擇性，但該表中有聚集索引時(shí)，SQL SERVER對(duì)兩部分結(jié)果的合并采用Merge Union運(yùn)算。

我們以一個(gè)具有1000個(gè)返回行的結(jié)果集為小結(jié)果，并將與其組合的大結(jié)果集的行數(shù)做多種變化，得到以下一組數(shù)據(jù)（見表1）：

數(shù)據(jù)表明，在這種情況下，大結(jié)果語(yǔ)句放在靠考前時(shí)，效率反而較低。

(3)如果兩個(gè)SELECT均不具有強(qiáng)選擇性，并且而該表上沒有聚集索引時(shí)，SQL SERVER對(duì)兩部分結(jié)果的合并采用Hash Union運(yùn)算。測(cè)試表明，這種情況下，效率并不會(huì)受到SELECT語(yǔ)句次序的影響。

4 多條件的優(yōu)化

這里討論的多條件優(yōu)化指的是在where 子句中有多個(gè)條件并用and連接時(shí)，條件的次序?qū)QL語(yǔ)句執(zhí)行的效率影響，一般認(rèn)為，將嚴(yán)格的條件放在前面，弱條件放在后面具有較高的效率。原因是，強(qiáng)條件可以得到較小的臨時(shí)表，在此基礎(chǔ)之上再進(jìn)行弱條件篩選總體效益較高。

試驗(yàn)表明，SQL SERVER在處理多條件時(shí)，條件的執(zhí)行順序并不完全按其在where子句中的順序來(lái)執(zhí)行，而是與該條件覆蓋的索引有著密切的關(guān)系（見表2）：

這里的索引是非聚集索引，如果其中的條件覆蓋聚集索引時(shí)情況稍有不同。由此我們可以看出，多條件時(shí)，條件的執(zhí)行順序會(huì)被優(yōu)化器作恰當(dāng)?shù)陌才牛捎诖嬖谒饕囊蛩兀到y(tǒng)的優(yōu)化也不是簡(jiǎn)單的將強(qiáng)條件先行執(zhí)行。而開發(fā)人員在構(gòu)造SQL語(yǔ)句的時(shí)候，則沒有必要去考慮where子句中條件的順序問題。

5 聯(lián)接運(yùn)算的優(yōu)化

聯(lián)接運(yùn)算優(yōu)化中的一個(gè)問題是：當(dāng)一個(gè)SQL中既有聯(lián)接運(yùn)算，同時(shí)也存在的選擇運(yùn)算時(shí)，應(yīng)當(dāng)采用先選擇運(yùn)算縮小聯(lián)接運(yùn)算的范圍，避免聯(lián)接運(yùn)算產(chǎn)生較大的臨時(shí)表。所以有人舉出以下典型的例子：

方法一：SELECT * FROM products ，address WHERE products.productid = address. Productid and date = {^2005 - 1 - 1}

方法二：SELECT * FROM address WHERE productid in (SELECT productid FROM products WHERE date= {^2005 - 1 - 1})

事實(shí)上這兩種方法的結(jié)果集并非完全等價(jià)，因?yàn)樵趇n操作符在處理嵌套語(yǔ)句的結(jié)果集時(shí)，會(huì)自動(dòng)剔出重復(fù)的值。因此，它的結(jié)果等同于address和products之間為一對(duì)多的關(guān)系時(shí)的聯(lián)接結(jié)果。換而言之，如果address和products之間是多對(duì)多的關(guān)系時(shí)，兩種方法的結(jié)果是不一致的，第一種方法的結(jié)果集將大于第二種方法。所以，嚴(yán)格而言，無(wú)論第二種方法的性能如何，都不能算是一種優(yōu)化方法。

如果address和products為一對(duì)多的關(guān)系時(shí)兩種方法結(jié)構(gòu)相同，那是否存在效率上的差異？由SQL SERVER的查詢計(jì)劃可以看到：優(yōu)化器在處理這兩種方式時(shí)，采用的是同一種策略，總是先作選擇運(yùn)算，然后在作聯(lián)接運(yùn)算。無(wú)論兩表的聯(lián)接是以join的方式表達(dá)，還是在where子句中表達(dá)，抑或是以嵌套查詢的方式表達(dá)，其實(shí)現(xiàn)方式和代價(jià)是完全一樣的。

6 結(jié)語(yǔ)

以上對(duì)SQL語(yǔ)句優(yōu)化的一部分問題作了實(shí)踐性的探討，可見，由于數(shù)據(jù)庫(kù)系統(tǒng)在執(zhí)行SQL指令時(shí)均有一個(gè)優(yōu)化過程，因此，在討論SQL語(yǔ)句的優(yōu)化問題時(shí)，應(yīng)當(dāng)結(jié)合具體環(huán)境，結(jié)合優(yōu)化器的處理法則。僅僅從語(yǔ)法上作邏輯上的解釋和想象所提出的優(yōu)化對(duì)SQL用戶的實(shí)踐可能是一種誤導(dǎo)。

參考文獻(xiàn)：

[1] 王書海，劉明生，馬銀華.基于多表連接的分組查詢語(yǔ)句的性能分析與優(yōu)化[J].計(jì)算機(jī)工程，2000，26(7):186-187.

[2] 谷震離. 查詢語(yǔ)句對(duì)SQL Server 數(shù)據(jù)庫(kù)查詢性能優(yōu)化分析 [J].福建電腦，2007，3:21-22.

[3] 王振輝，吳廣茂.SQL查詢語(yǔ)句優(yōu)化研究[J].計(jì)算機(jī)應(yīng)用，2005，25(12):207-208.

[4] 楊庚.關(guān)系數(shù)據(jù)庫(kù)SQL語(yǔ)言查詢過程的分析和優(yōu)化設(shè)計(jì)[J]，計(jì)算機(jī)工程與應(yīng)用，1999，11:87-88.

[5] [美]微軟公司著.Querying Microsoft SQL Server 2000 With Transact-SQL[M].北京:清華大學(xué)出版社，2001.

[6] [美]微軟公司著.Programming a Microsoft SQL Server 2000 Database[M].北京:清華大學(xué)出版社，2001.

[7] 苗雪蘭，劉瑞新，王懷峰. 數(shù)據(jù)庫(kù)系統(tǒng)原理及應(yīng)用教程(第2版)[M]. 北京: 機(jī)械工業(yè)出版社，2004.