[SQL查詢優(yōu)化技術(shù)] 數(shù)據(jù)庫(kù)sql實(shí)驗(yàn) 查詢優(yōu)化
發(fā)布時(shí)間:2020-02-16 來(lái)源: 美文摘抄 點(diǎn)擊:
摘要:數(shù)據(jù)查詢是數(shù)據(jù)庫(kù)應(yīng)用程序的重要組成部分,查詢語(yǔ)句的執(zhí)行速度已成為數(shù)據(jù)庫(kù)整體性能的瓶頸。本文闡述了影響查詢效率的因素,并根據(jù)具體情況,給出了優(yōu)化的方法,以提高查詢語(yǔ)句的執(zhí)行效率。?
關(guān)鍵詞:SQL;查詢優(yōu)化;查詢語(yǔ)句?
中圖分類號(hào):TP311.13 文獻(xiàn)標(biāo)識(shí)碼:A??
1 引言?
目前,隨著計(jì)算機(jī)技術(shù)的不斷發(fā)展,數(shù)據(jù)庫(kù)技術(shù)應(yīng)用越來(lái)越廣泛,特別是大型數(shù)據(jù)庫(kù)的廣泛使用。而基于數(shù)據(jù)庫(kù)的應(yīng)用程序在執(zhí)行時(shí),應(yīng)用程序需要在數(shù)據(jù)庫(kù)中查詢相應(yīng)的信息。SQL(Structured Query Language)語(yǔ)言是關(guān)系數(shù)據(jù)庫(kù)的標(biāo)準(zhǔn)語(yǔ)言,集數(shù)據(jù)操縱、數(shù)據(jù)定義和數(shù)據(jù)控制等功能于一體,數(shù)據(jù)庫(kù)的查詢又是數(shù)據(jù)庫(kù)的核心操作,也是最常用的操作。但人們?cè)谑褂肧QL語(yǔ)言往往只注意結(jié)果的準(zhǔn)確,忽視了其執(zhí)行的效率,影響了系統(tǒng)的整體性能。據(jù)統(tǒng)計(jì),90%的性能問(wèn)題主要來(lái)源于程序員或用戶使用了不恰當(dāng)?shù)腟QL查詢語(yǔ)句。由此可見(jiàn),SQL查詢語(yǔ)句的質(zhì)量對(duì)于數(shù)據(jù)庫(kù)管理信息系統(tǒng)的質(zhì)量有較大的影響,如何保證在結(jié)果正確的條件下,提高查詢效率成為一個(gè)急需解決的問(wèn)題。?
2 查詢語(yǔ)句效率的影響因素分析?
查詢語(yǔ)句的效率既可以利用邏輯方法進(jìn)行查詢優(yōu)化,也可以采用物理的方法進(jìn)行查詢優(yōu)化,但在實(shí)際中,CPU的處理速度比從磁盤上讀寫信息的速度快得多,要進(jìn)行查詢優(yōu)化,關(guān)鍵是要減少對(duì)磁盤的訪問(wèn)。RDBMS 優(yōu)化器的輸入是一條查詢語(yǔ)句,輸出是一個(gè)執(zhí)行策略。執(zhí)行策略是執(zhí)行查詢所需要的一系列步驟。優(yōu)化器的優(yōu)化算法影響數(shù)據(jù)庫(kù)的反應(yīng)速度,優(yōu)化器的優(yōu)化策略還與所查詢表的內(nèi)容和其他一些與服務(wù)器有關(guān)的因素有關(guān),如cache大小、cache策略、I/O 大小等。通常硬盤訪問(wèn)是成本最高的操作,因此對(duì)查詢優(yōu)化來(lái)講,最為重要的選擇就是使用什么索引和采用何種表的連接手段。?
影響查詢效率的因素,主要有以下幾點(diǎn):①索引是否合適;②查詢條件中的數(shù)據(jù)類型是否兼容;③查詢條件中操作符使用是否得當(dāng);④多個(gè)選擇條件查詢時(shí),選擇條件的次序是否合理;⑤ 是否合理安排連接選擇運(yùn)算.⑥多表查詢是否合理等。?
3 SQL語(yǔ)句的優(yōu)化方法?
SQL 查詢語(yǔ)句執(zhí)行都涉及到存取用戶或系統(tǒng)對(duì)象的表頁(yè)或索引項(xiàng)。一般而言,系統(tǒng)存取頁(yè)面的速度越快,SQL 語(yǔ)句的性能就越高。因此對(duì)SQL查詢語(yǔ)句進(jìn)行優(yōu)化,應(yīng)盡量減少頁(yè)面存取,或使用內(nèi)存的頁(yè)面代替訪問(wèn)磁盤。本文以SQL SERVER 2000為例,給出了進(jìn)行查詢優(yōu)化的幾種方法。?
3.1 選取合適的索引字段?
對(duì)于查詢優(yōu)化來(lái)說(shuō),提高速度最快的方法是建立索引。在數(shù)據(jù)庫(kù)中建立合適的索引能避免表掃描并減少因查詢而產(chǎn)生的輸入/輸出開(kāi)銷,使查詢速度大為提高。?
SELECT查詢中的WHERE語(yǔ)句是查詢優(yōu)化器處理的重點(diǎn)。WHERE語(yǔ)句中的每一列都是索引的候選列,但對(duì)每一列都建立索引會(huì)形成很多索引,增加系統(tǒng)的開(kāi)銷,故應(yīng)盡量分析出每一個(gè)重要查詢的使用頻度,將常查詢的列建立索引。?
盡量覆蓋一個(gè)查詢。例如:如果一個(gè)查詢只選擇列甲和列乙,把列甲和列乙作為索引鍵建立復(fù)合索引,這個(gè)查詢就是覆蓋的,這樣被提取的數(shù)據(jù)存放在索引鍵自身中,根本不需要讀取實(shí)際數(shù)據(jù)。因而會(huì)減少輸入/輸出總量,大大提高查詢速度。?
在經(jīng)常要進(jìn)行插入、刪除、修改、更新操作的表上不要建立太多不必要的索引。因?yàn)楸頂?shù)據(jù)修改后,所有的索引都必須做相應(yīng)的調(diào)整,所有的分頁(yè)操作都被記錄在系統(tǒng)日志中。這也會(huì)增加輸入/輸出操作,從而影響數(shù)據(jù)庫(kù)性能。?
3.2 避免使用不兼容的數(shù)據(jù)類型?
在SQL中,float、int、varchar binary和varbinary是不兼容的。數(shù)據(jù)的不兼容使得無(wú)法使用預(yù)先定義好的索引,影響查詢速度,如語(yǔ)句SELECT NAME FROM CUSTOMERWHERE COST>1000在執(zhí)行時(shí)因COST是MONEY型數(shù)據(jù),1000是整型,形成數(shù)據(jù)類型不兼容,無(wú)法使用建立在COST上的索引,系統(tǒng)在執(zhí)行時(shí)要先將1000轉(zhuǎn)換成MONEY型,再與COST進(jìn)行比較。故應(yīng)當(dāng)在編成程序時(shí),將1000轉(zhuǎn)化成MONEY型,而不是等到運(yùn)行時(shí)轉(zhuǎn)化。可將其轉(zhuǎn)化成SELECT NAME FROM CUSTOMERWHERE COST>$1000。?
3.3 避免對(duì)搜索參數(shù)使用其它操作符(如數(shù)學(xué),字符串函數(shù)等)?
若在WHERE子句中,存在一個(gè)數(shù)學(xué)表達(dá)式,查詢優(yōu)化器就不能使用分布統(tǒng)計(jì)信息,影響查詢效率。如查詢語(yǔ)句SELECT NAME?ID FROMCUSTOMERWHERE COST *5>3000應(yīng)寫成SELECT NAME?ID FROMCUSTOMERWHERE COST >600。?
例如:SELECT AU?LNAMEFROM AUTHORS WHERE SUBSTRING(STATE,1,1)= ‘A’ ?
應(yīng)寫成SELECT AU?LNAMEFROM AUTHORSWHERESTATELIKE‘A%’。?
3.4 避免使用非聚合表達(dá)式?
非聚合表達(dá)式很難利用索引,故在執(zhí)行含有非聚合表達(dá)式的SQL語(yǔ)句時(shí),必須進(jìn)行大規(guī)模的掃描,降低了執(zhí)行的速度,例如不等關(guān)系符號(hào)“<>”是一個(gè)排斥性的的操作符,故含有“<>”符號(hào)的SQL語(yǔ)句在執(zhí)行時(shí),在掃描整個(gè)表之前無(wú)法確定子句的選擇范圍,必須掃描整個(gè)表。像“NOT”,“NOT IN”,“NOT LIKE”,“NOT EXIST”等都是非聚合表達(dá)式,應(yīng)避免使用。例如:SELECT SNAME FROM STUDENT WHERE AGE<>20可改寫成SELECT SNAME FROM STUDENT WHERE AGE20。?
3.5避免通配符(%)在搜尋詞首出現(xiàn)?
通配符(%)出現(xiàn)在搜尋詞首,系統(tǒng)不使用索引,會(huì)降低SQL語(yǔ)句的執(zhí)行速度。然而當(dāng)通配符出現(xiàn)在字符串其他位置時(shí),優(yōu)化器就能利用索引。在下面的查詢中索引得到了使用:?
SELECTAU?LNAMEFROMAUTHORSWHERESTATELIKE‘A%’;?
3.6 對(duì)于連接查詢,適當(dāng)冗余?
缺省情況下多于4個(gè)表的連接查詢,按每次4個(gè)處理。對(duì)每個(gè)表集找到并保存最佳的外表,其余的組合則用來(lái)估計(jì)下一個(gè)次外表。如果在查詢中應(yīng)當(dāng)提供一些冗余的搜索參數(shù),使優(yōu)化器有更多的選擇余地,可提高查詢速度。?
如下面兩個(gè)查詢是相同的?
SELECTGRADEFROMST,KCWHEREST.SNO=KC.SNOANDST.NO=‘20060101’?
SELECTGRADEFROMST,KCWHEREST.SNO=KC.SNOANDST.NO=‘20060101’ ANDKC.NO=‘20060101’?
但后一個(gè)查詢的速度更快。?
3.7 注意選擇條件的排列順序?
選擇條件的排列順序不僅能影響索引的選取,還關(guān)系到臨時(shí)表的大小,對(duì)系統(tǒng)的性能有較大的影響。?
例如:比較SELECT*FROMAUTHORSWHERE STATE=‘CA’ANDAU?LNAME=‘WHITE’和SELECT*FROMAUTHORSWHEREAU?LNAME=‘WHITE’ ANDSTATE=‘CA’ 的查詢效率。假設(shè)AUTHORS表中共有20萬(wàn)條記錄,其中滿足STATE=‘CA’的記錄為12萬(wàn)條,滿足AU?LNAME=‘WHITE’的記錄為2萬(wàn)條。在SQL SERVER 2000中查詢條件的選取是從左至右的。第一條SQL語(yǔ)句在執(zhí)行時(shí),首先返回一個(gè)12萬(wàn)條記錄的臨時(shí)表,然后在從中進(jìn)行選擇。第二條SQL語(yǔ)句在執(zhí)行時(shí),首先返回一個(gè)2萬(wàn)條記錄的臨時(shí)表,然后在從中進(jìn)行選擇。顯然,第二條SQL語(yǔ)句的查詢效率比第一條要高。一般來(lái)說(shuō),為提高查詢速度,將條件嚴(yán)格的寫在前面。?
3.8 將多表的連接運(yùn)算改為嵌套SQL語(yǔ)句來(lái)實(shí)現(xiàn)?
連接運(yùn)算會(huì)產(chǎn)生很大的臨時(shí)表,特別是未優(yōu)化的多表連接查詢。未經(jīng)過(guò)優(yōu)化的SQL查詢語(yǔ)句會(huì)產(chǎn)生巨大的計(jì)算工作量,可將其改為嵌套SQL語(yǔ)句來(lái)提高查詢效率。例如:?
SELECT STUDENT.SNAME?
FROMSTUDENT, SC
WHERE STUDENT.SNO = SC.SNO?
AND SC.CNO= ‘ 2 ’?
可改為?
SELECT SNAME FROM STUDENT?
WHERE SNO IN (SELECT SNOFROM SC WHERE CNO= ‘ 2 ’)?
假設(shè)STUDENT表中有1000條記錄,SC表中有5000條記錄,其中CNO為2的有500條,在第一個(gè)SQL語(yǔ)句中,首先形成1000×5000=5000000條記錄的臨時(shí)表,再進(jìn)行選擇運(yùn)算。若采用第二個(gè)SQL語(yǔ)句,先對(duì)SC表進(jìn)行選擇運(yùn)算,有500條記錄滿足條件,然后再與STUDENT表進(jìn)行連接運(yùn)算,很顯然,后者的計(jì)算量要比前者少很多,故對(duì)于多個(gè)數(shù)據(jù)表的連接選擇運(yùn)算過(guò)程,先做選擇運(yùn)算后做連接運(yùn)算,比先做連接運(yùn)算后做選擇運(yùn)算計(jì)算量小,查詢響應(yīng)時(shí)間短,查詢效率高。?
3.9 使用存儲(chǔ)過(guò)程?
存儲(chǔ)過(guò)程是編譯好、優(yōu)化過(guò)且存儲(chǔ)在數(shù)據(jù)庫(kù)中的SQL語(yǔ)句和控制流語(yǔ)言的集合,設(shè)計(jì)優(yōu)良的應(yīng)用程序都應(yīng)當(dāng)使用存儲(chǔ)過(guò)程。如果在程序中利用好存儲(chǔ)過(guò)程,可極大地增強(qiáng)SQL語(yǔ)言的效率、功能和靈活性。編譯進(jìn)存儲(chǔ)過(guò)程的SQL語(yǔ)句在執(zhí)行時(shí)可省去大量的處理時(shí)間。存儲(chǔ)過(guò)程在客戶端執(zhí)行請(qǐng)求使用網(wǎng)絡(luò)的效率往往比將等效的SQL語(yǔ)句發(fā)送到服務(wù)器高。?
4 結(jié)束語(yǔ)?
分析了查詢語(yǔ)句效率影響因素的基礎(chǔ)上,提出了具體的優(yōu)化方法。在系統(tǒng)開(kāi)發(fā)和維護(hù)過(guò)程中須針對(duì)具體的情況,綜合運(yùn)用多種優(yōu)化方法,不斷加以調(diào)整,可以提高SQL語(yǔ)句的執(zhí)行效率,取得滿意的運(yùn)行效果。?
參考文獻(xiàn)?
[1]薩師煊,珊著.庫(kù)系統(tǒng)概論[M].北京:高等教育出版社,2001.?
[2]章立民著.SQL SERVER 2000 TRANSACT-SQL 程序設(shè)計(jì)[M].北京:中國(guó)鐵道出版社,2002.?
[3]胡百敬著.MICROSOFT SQL SERVER性能調(diào)校[M].北京:電子工業(yè)出版社,2005.
相關(guān)熱詞搜索:優(yōu)化 查詢 技術(shù) SQL查詢優(yōu)化技術(shù) mysql查詢優(yōu)化技術(shù) mysql分頁(yè)查詢優(yōu)化
熱點(diǎn)文章閱讀