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