福建電腦2004年第8期SQL語句優(yōu)化原則盤莉莉(桂林航天工業(yè)高等?？茖W校計算機系廣 西桂林541004)[摘要]在數(shù)據(jù)庫中,對于大量的數(shù)據(jù), SQL語句消耗了70%至90%的數(shù)據(jù)庫資源,劣質(zhì)SQL語句和優(yōu)質(zhì)SQL語句之間的速度差別可以達到上百倍,可見對于一個系統(tǒng)不是簡單地能實現(xiàn)其功能就行,而是要寫出高質(zhì)量的SQL語句,提高系統(tǒng)的可用性。SQL優(yōu)化的實質(zhì)就是在結(jié)果正確的前提下,優(yōu)化器根據(jù)定義的索引來提高查找效率,盡量避免全表掃描的發(fā)生。[關(guān)鍵詞] SQL;DBMS;優(yōu)化1問題的提出要求在學生表中查詢名字中包含jeumy的人?？梢圆捎萌缦碌脑趹孟到y(tǒng)開發(fā)初期,由于開發(fā)數(shù)據(jù)庫數(shù)據(jù)比較少,對于查詢 sQL語句:查詢SQL語句,復雜視圖的編寫等體會不出SQL語句各種寫select # from student where last-name like '% jeuny % '法的性能優(yōu)劣,但是如果將應用系統(tǒng)提交實際應用后,隨著數(shù)這里由于通配符( % )在搜尋詞首出現(xiàn),將不使用last-name據(jù)庫中數(shù)據(jù)的增加,系統(tǒng)的響應速度就成為目前系統(tǒng)需要解決的索引。 在很多情況下可能無法避免這種情況,但是一定要心的最主要的問題之-。DBMS優(yōu)化一個很重要的方面就是中有底 ,通配符如此使用會降低查詢速度。然而當通配符出現(xiàn)SQL語句的優(yōu)化。所謂優(yōu)化即where子句利用了索引,不可優(yōu)在字 符串其他位置時,優(yōu)化器就能利用索引。在下面的查詢中化即發(fā)生了表掃描或額外開銷。如果在SQL語句的where子索引得到了 使用:句中寫的代碼不合理,就會造成優(yōu)化器刪去索引而使用全表掃select # from student where last name like 'j% '描,一般把這種SQL語句稱為劣質(zhì)SQL語句。在編寫SQL語.2.4 Order by語句句時我們應清楚優(yōu)化器根據(jù)何種原則來刪除索引,這有助于寫ORDER BY語句決定了查詢結(jié)果順序。Order by語句對出高性能的sQL語句。要排序的列沒有什么特別的限制，也可以將函數(shù)加入列中。任2 saL語句優(yōu)化原則何在Orderby語句的非索引項或者有計算表達式都將降低查下面就某些SQL語句的where子句編寫中需要注意的問詢速度。 仔細檢查order by語句以找出非索引項或者表達式, .題作詳細介紹。在這些where子句中,即使某些列存在索引,但它們會降低性能。 解決這個問題的辦法就是重寫order by語句是由于編寫了劣質(zhì)的SQL,DBMS在運行該SQL語句時也不能以使用索引, 也可以為所使用的列建立另外-個索引,同時應使用該索引,而同樣使用全表掃描,這就造成了響應速度的極絕對避免在 order by子句中使用表達式。大降低。2.5 NOT2.1 ISNULL與IS NOT NUL我們在查詢時經(jīng)常在where子句使用一些邏輯表達式，如不能用null作索引,任何包含null值的列都將不會被包含大于(>)、小于(<= 等于(=)以及不等于(<>)等等，也可在索引中。即使索引有多列這樣的情況下,只要這些列中有一以使用 and(與)、or(或)以及nor(非)。NOT 可用來對任何邏輯列含有null,該列就會從索引中排除。也就是說如果某列存在運算符號取反。 下面是- -個NOT子句的例子:空值,即使對該列建索引也不會提高性能。where not (age = 18)任何在where子句中使用is null或is not null的語句優(yōu)化如果要使用NOT,則應在取反的短語前面加上括號,并在器是不允許使用索引的。短語前面加上NOT運算符。NOT運算符包含在另外-個邏輯2.2任何對列的操作都將導致表掃描運算符中,這就是不等于(<> )運算符。換句話說，即使不在假定有一個學生表(student) ,對于一個學生的姓和名分成查詢where子句中顯式地加人NOT詞,NOT仍在運算符中,見兩列存放(FIRST-.NAME和LAST-NAME) ,現(xiàn)在要查詢- -個叫下例:"Bill jeumy"的學生。select # from student whereage <> 18應當采用下面這種SQL語句的編寫,DBMS系統(tǒng)就可以采對這個查詢,可以改寫為不使用NOT:用基于last name創(chuàng)建的索引。select # from student where age<18 σr age>18Select # from student where first-name = 'ill' and last-name =雖然這兩種查詢的結(jié)果- -樣 ,但是第二種查詢方案會比第'jeuny '-種查詢方案更快些。第二種查詢允許對age列使用索引,而遇到下面這種情況又如何處理呢?如果-一個變量(name) 第-種查詢則不能使用索引。中存放著Bill jeuny這個學生的姓名,對于這種情況我們又如何2.6 IN和EXISTS避免全程遍歷,使用索引呢?可以使用一個函數(shù)INSTR,將變有時候會將- -列和一 系列值相比較。最簡單的辦法就是量name中的姓和名分開就可以了,但是有-點需要注意,這個在 where子句中使用子查詢。在where子句中可以使用兩種格函數(shù)是不能作用在索引列上。任何對列的操作都將導致表掃式的子查詢。描,它包括數(shù)據(jù)庫函數(shù)、計算表達式等等,查詢時要盡可能將操第一種格式是使用IN操作符:作移至等號右邊。下面是SQL查詢語句:select # from student where第中國煤化工first-name = SUBSTR (‘ &&name '，1， INSTR(‘&name',' ')-1)我桶YHCN M H G,因為它比較容易編SUBSTR (' &&name'，INSTR 寫,而實際上第二種格式要遠比第-一種格式的效率高。在SQL(‘&&name',' ')+1)中幾乎可以將所有的IN操作符子查詢改寫為使用EXISTS的2.3帶通配符( % )的like語句子查詢。同樣以上面的例子來看這種情況。目前的需求是這樣的,第二種格式中,子查詢以select # '開始。(下轉(zhuǎn)第 12頁12福建電腦2004年第8期.們將縱橫兩個方面提到為區(qū)域劃分和行政等級。區(qū)域代表橫門用一臺計算機連 上-根電話線，上網(wǎng)幫助農(nóng)民下載最新的農(nóng)向;中央-地方兩級部門代表縱向。業(yè)技術(shù)知識,或者是尋找到城市打工的就業(yè)信息，可能是最有首先,從橫向上來看,實效的,這遠要比購買- -大批機器設(shè)備建立-一個沒有 任何業(yè)務一東部地區(qū)。 人均GDP較高,資金有保障,內(nèi)在和外在的機關(guān)局域網(wǎng)好得多。動力充足,有條件大力推進電子政務,并且在利用電子政務手地級市(以及直轄市)應該是地方電子政務建設(shè)的重點層.段打破行政體制阻力,重組政府業(yè)務流程,整合政府資源方面面,同樣分析地級市 的EG - Readines:1、人均GDP較高,政府創(chuàng)造出好的經(jīng)驗;財政能力較好,能夠為電子政務建設(shè)提供資金保障;2、管轄地一中部地區(qū)。 在資金相對有保障、具備外部和內(nèi)部動力域廣 闊,人口數(shù)量巨大,政府行政事務特別是公共服務事務的的情況下,可以積極借鑒東部發(fā)達地區(qū)的經(jīng)驗,穩(wěn)妥扎實地推規(guī)模巨大,具有通過電子政務 手段來提高工作效率、改進工作進電子政務;質(zhì)量的內(nèi)在動力;3、直接面向社會公眾,面對強大的要求行政一西部地區(qū)。 在資金有限,外部和內(nèi)部動力不足的情況透明、政務公開的社會輿論監(jiān)督 和壓力。因此,從資金保降、內(nèi)下,應該切實根據(jù)本地區(qū)的實際需要,有針對性地利用信息技外部動力等各方 面來看,地級市政府具有建設(shè)電子政務的良好術(shù)解決實際困難和問題,不宜在電子政務建設(shè)上全面鋪開,搞條件和動機。 實賤也證明,地級市政府在外部、內(nèi)部壓力的共大推進。同推動下,根據(jù)建立服務型政府的要求,在建設(shè)社保、醫(yī)保系其次,從縱向.上來看，借助EG- Readiness的概念,也可以統(tǒng),推進網(wǎng) 上審批提供在線服務等方面,取得了很好的效果。對不同層次的電子政務進行分析。在進行具體分析之前,要注比較起來, 省級政府的職能主要集中在宏觀管理和調(diào)控意到一個事實:在不同層次的政府部門(中央政府、省級政府、上,公共服務事務較少 ,而且不直接面對社會公眾,缺乏外部動市級政府等)中,政府職能的側(cè)重點也略有不同，中央政府和省力 ,因此,EG- Readiness較差。因此,人均GDP較高的省份,在級政府的職能主要集中在宏觀管理和調(diào)控,即管理職能上;而能夠提供資 金保障的情況下,可以從本省的國民經(jīng)濟和社會發(fā)市級政府直接面對社會公眾,為民服務的壓力與日俱增。展的實際需求出發(fā),把電子政務建設(shè)的重點放在促進政府部門一中央政府。 我個人認為，中央政府的EG - Readines間的互聯(lián)互通 ,共享政府信息資源,形成協(xié)同運作的宏觀管理處于中等偏上水平介于中部和東部地區(qū)之間。從人均GDP來和調(diào)控體系上。 人均GDP不高,經(jīng)濟發(fā)展水平較差的省份,在看,中央政府的人均GDP即全國的人均GDP,是平均水平。因內(nèi)外部動力不足 ,需求不充分的情況下.如果花費大筆資金全此,建設(shè)資金相對有保障;由于我國地域遼闊,人口眾多,經(jīng)濟面推進電子政務 ,效益有限。和社會發(fā)展情況復雜,中央政府部門承擔著異常繁重的行政管再來看縣級市,人均GDP可能有高有低,社會公眾的參與理職能。因此,雖然大部分中央政府部門不直接面對社會公眾度也可 能有差別,但是政府行政事務規(guī)模卻已經(jīng)由城市規(guī)模確服務,但是管理職能的繁重,加上政府機構(gòu)改革的不斷推進,人定,因此,我個人認為,一般來看 ,縣級市普遍缺乏電子政務建員精簡所以行政事務規(guī)模顯得非常龐大,提高工作效率、改進設(shè)的內(nèi)在 動力,而且由于政府行政事務規(guī)模不大，采用信息技工作流程的內(nèi)在動力不斷加大;此外,社會公眾對中央政府的術(shù) 、全面推行電子政務也不太容易收到實際的效益。當然, 也行政事務參與度雖然不大,但關(guān)注的程度卻超出任何其他級別不排除有 的縣級市在具備-定條件情況下,通過比較務實的方的政府,因此，中央政府也有很強的輿論監(jiān)督的外在壓力。因式能夠較好地發(fā)展電子政務。此,中央政府具有較高水平的EG- Rediness,有推進電子政務縱橫交叉之后,可以得出一個結(jié)論，中央政府的重點領(lǐng)域的強烈意圖。但是制約的因素是,中央財政的資金即使是相對和地級市(特 別是東部發(fā)達地區(qū)的地級市),可能是中國目前推有保障的,但是由于目前財政狀況8益緊張,也不可能無限制進電子政務 的優(yōu)先領(lǐng)域或者說是可能率先取得突破,創(chuàng)造先進地全面投資。因此,中央政府要面向未來,參照政府行政體制經(jīng)驗的領(lǐng)域。 我們應該根據(jù)需求導向、務求實效的原則,有重改革,根據(jù)政府職能轉(zhuǎn)變的要求,針對本屆政府面臨的挑戰(zhàn),在點有步驟地扎實推進。宏觀管理、政府收支和社會穩(wěn)定等重點職能領(lǐng)域,加快電子政此外,一個值得注意的問題是,由于EG - Readines的存務建設(shè)的進度,在其他領(lǐng)域則應量力而行。在,決定了各地區(qū)、各層次政府部門電子政務建設(shè)起步水平的一地方政府。 情況千差萬別,必須認真分析EG- Readi-.差距, 而由于電子政務對政府行政能力提高的加速作用,可能ness的各項指標,確定推進電子政務的時機和重點。在EG-會造 成不同地區(qū)政府部門行政能力差距的進一步加大,我們可Readiness 并不充分的條件下,是花大力氣全面推行電子政務，以稱之為“EG- DIVIDE"--電子政務鴻溝。 這個問題需要引還是根據(jù)自己的實際情況在局部有針對性地利用信息技術(shù)解起我們的重視,并在實踐中進一步研究加以解決。決現(xiàn)實問題,這是需要認真考慮的。例如在貧困山區(qū),政府部(上接第42頁)運用EXISTS子句不管子查詢從表中抽取什么數(shù)據(jù)它只查看時表中以后再執(zhí)行主查詢。 這也就是使用EXISTS比使用INwhere子句。這樣優(yōu)化器就不必遍歷整個表而僅根據(jù)索引就可通 常查詢速度快的原因。完成工作(這里假定在where語句中使用的列存在索引)。相對同時應盡可能使用NOT EXISTS來代替NOT IN,盡管二于IN子句來說,EXISTS使用相連子查詢,構(gòu)造起來要比IN子者都使里了 NOT1不能使田虔引而降低速度) ,NOT EXISTS要查詢困難一些。比NO中國煤化工通過使用EXISTS,DBMS系統(tǒng)會首先檢查主查詢,然后運MH| CN M H G過程,涉及數(shù)據(jù)庫層的行子查詢直到它找到第一個匹配項,這就節(jié)省了時間。而資源配直 、網(wǎng)冶層的重化稱以區(qū)保TF承統(tǒng)層的總體設(shè)計。我DBMS系統(tǒng)在執(zhí)行IN子查詢時,首先執(zhí)行子查詢，并將獲得的只是在開發(fā) 應用軟件過程有一些體會和心得,在編寫高效的結(jié)果列表存放在一-個加了索引的臨時表中。在執(zhí)行子查詢之sQL 語句作-些探索。前,DBMS系統(tǒng)先將主查詢掛起,待子查詢執(zhí)行完畢,存放在臨