數(shù)據(jù)庫優(yōu)化查詢計(jì)劃的方法

1、數(shù)據(jù)庫優(yōu)化查詢計(jì)劃的方法數(shù)據(jù)庫系統(tǒng)是管理信息系統(tǒng)的核心，基于數(shù)據(jù)庫的聯(lián)機(jī)事務(wù)處理（OLTP）以及聯(lián)機(jī)分析處理(OLAP)是銀行、企業(yè)、政 府等部門最為重要的計(jì)算機(jī)應(yīng)用之一。從大多數(shù)系統(tǒng)的應(yīng)用實(shí)例來看，查詢操作在各種數(shù)據(jù)庫操作中所占據(jù)的比重最大，而查詢操作所基于的SELECT語句在 SQL語句中又是代價(jià)最大的語句。舉例來說，如果數(shù)據(jù)的量積累到一定的程度，比如一個銀行的賬戶數(shù)據(jù)庫表信息積累到上百萬甚至上千萬條記錄，全表掃描一次 往往需要數(shù)十分鐘，甚至數(shù)小時(shí)。如果采用比全表掃描更好的查詢策略，往往可以使查詢時(shí)間降為幾分鐘，由此可見查詢優(yōu)化技術(shù)的重要性。 在應(yīng)用項(xiàng) 目的實(shí)施中發(fā)現(xiàn)，許多程序員在利用一些

2、前端數(shù)據(jù)庫開發(fā)工具（如PowerBuilder、Delphi等）開發(fā)數(shù)據(jù)庫應(yīng)用程序時(shí)，只注重用戶界面的華麗， 并不重視查詢語句的效率問題，導(dǎo)致所開發(fā)出來的應(yīng)用系統(tǒng)效率低下，資源浪費(fèi)嚴(yán)重。因此，如何設(shè)計(jì)高效合理的查詢語句就顯得非常重要。本文以應(yīng)用實(shí)例為基 礎(chǔ)，結(jié)合數(shù)據(jù)庫理論，介紹查詢優(yōu)化技術(shù)在現(xiàn)實(shí)系統(tǒng)中的運(yùn)用。 分析問題 許多程序員認(rèn)為查詢優(yōu)化是DBMS（數(shù)據(jù)庫管理系統(tǒng)）的任務(wù)， 與程序員所編寫的SQL語句關(guān)系不大，這是錯誤的。一個好的查詢計(jì)劃往往可以使程序性能提高數(shù)十倍。查詢計(jì)劃是用戶所提交的SQL語句的集合，查詢規(guī)劃是 經(jīng)過優(yōu)化處理之后所產(chǎn)生的語句集合。DBMS處理查詢計(jì)劃的過程是這樣的：在

3、做完查詢語句的詞法、語法檢查之后，將語句提交給DBMS的查詢優(yōu)化器，優(yōu)化 器做完代數(shù)優(yōu)化和存取路徑的優(yōu)化之后，由預(yù)編譯模塊對語句進(jìn)行處理并生成查詢規(guī)劃，然后在合適的時(shí)間提交給系統(tǒng)處理執(zhí)行，最后將執(zhí)行結(jié)果返回給用戶。在實(shí) 際的數(shù)據(jù)庫產(chǎn)品(如Oracle、Sybase等)的高版本中都是采用基于代價(jià)的優(yōu)化方法，這種優(yōu)化能根據(jù)從系統(tǒng)字典表所得到的信息來估計(jì)不同的查詢規(guī)劃 的代價(jià)，然后選擇一個較優(yōu)的規(guī)劃。雖然現(xiàn)在的數(shù)據(jù)庫產(chǎn)品在查詢優(yōu)化方面已經(jīng)做得越來越好，但由用戶提交的SQL語句是系統(tǒng)優(yōu)化的基礎(chǔ)，很難設(shè)想一個原本糟 糕的查詢計(jì)劃經(jīng)過系統(tǒng)的優(yōu)化之后會變得高效，因此所寫語句的優(yōu)劣至關(guān)重要。下面重點(diǎn)說明改善查

4、詢計(jì)劃的解決方案。  解決問題 下面以關(guān)系數(shù)據(jù)庫系統(tǒng)Informix為例，介紹改善用戶查詢計(jì)劃的方法。 1合理使用索引 索引是數(shù)據(jù)庫中重要的數(shù)據(jù)結(jié)構(gòu)，它的根本目的就是為了提高查詢效率?，F(xiàn)在大多數(shù)的數(shù)據(jù)庫產(chǎn)品都采用IBM最先提出的ISAM索引結(jié)構(gòu)。索引的使用要恰到好處，其使用原則如下： 在經(jīng)常進(jìn)行連接，但是沒有指定為外鍵的列上建立索引，而不經(jīng)常連接的字段則由優(yōu)化器自動生成索引。 在頻繁進(jìn)行排序或分組（即進(jìn)行g(shù)roup by或order by操作）的列上建立索引。 在條件表達(dá)式中經(jīng)常用到的不同值較多的列上建立檢索，在不同值少的列上不要建立索引。比如在雇員表的“性別”列上只有“男”與“女”

5、兩個不同值，因此就無必要建立索引。如果建立索引不但不會提高查詢效率，反而會嚴(yán)重降低更新速度。 如果待排序的列有多個，可以在這些列上建立復(fù)合索引（compound index）。 使用系統(tǒng)工具。如Informix數(shù)據(jù)庫有一個tbcheck工具，可以在可疑的索引上進(jìn)行檢查。在一些數(shù)據(jù)庫服務(wù)器上，索引可能失效或者因?yàn)轭l繁操作而 使得讀取效率降低，如果一個使用索引的查詢不明不白地慢下來，可以試著用tbcheck工具檢查索引的完整性，必要時(shí)進(jìn)行修復(fù)。另外，當(dāng)數(shù)據(jù)庫表更新大量 數(shù)據(jù)后，刪除并重建索引可以提高查詢速度。 2避免或簡化排序 應(yīng)當(dāng)簡化或避免對大型表進(jìn)行重復(fù)的排序。當(dāng)能夠利用索引自動以適當(dāng)?shù)拇涡虍a(chǎn)

6、生輸出時(shí)，優(yōu)化器就避免了排序的步驟。以下是一些影響因素： 索引中不包括一個或幾個待排序的列； group by或order by子句中列的次序與索引的次序不一樣； 排序的列來自不同的表。 為了避免不必要的排序，就要正確地增建索引，合理地合并數(shù)據(jù)庫表（盡管有時(shí)可能影響表的規(guī)范化，但相對于效率的提高是值得的）。如果排序不可避免，那么應(yīng)當(dāng)試圖簡化它，如縮小排序的列的范圍等。 3消除對大型表行數(shù)據(jù)的順序存取 在 嵌套查詢中，對表的順序存取對查詢效率可能產(chǎn)生致命的影響。比如采用順序存取策略，一個嵌套3層的查詢，如果每層都查詢1000行，那么這個查詢就要查詢 10億行數(shù)據(jù)。避免這種情況的主要方法就是對連接

7、的列進(jìn)行索引。例如，兩個表：學(xué)生表（學(xué)號、姓名、年齡）和選課表（學(xué)號、課程號、成績）。如果兩個 表要做連接，就要在“學(xué)號”這個連接字段上建立索引。 還可以使用并集來避免順序存取。盡管在所有的檢查列上都有索引，但某些形式的where子句強(qiáng)迫優(yōu)化器使用順序存取。下面的查詢將強(qiáng)迫對orders表執(zhí)行順序操作： SELECT FROM orders WHERE (customer_num=104 AND order_num>1001) OR order_num=1008 雖然在customer_num和order_num上建有索引，但是在上面的語句中優(yōu)化器還是使用順序存取路徑掃描整個表。因?yàn)檫@個

8、語句要檢索的是分離的行的集合，所以應(yīng)該改為如下語句： SELECT FROM orders WHERE customer_num=104 AND order_num>1001 UNION SELECT FROM orders WHERE order_num=1008 這樣就能利用索引路徑處理查詢。 4避免相關(guān)子查詢 一個列的標(biāo)簽同時(shí)在主查詢和where子句中的查詢中出現(xiàn)，那么很可能當(dāng)主查詢中的列值改變之后，子查詢必須重新查詢一次。查詢嵌套層次越多，效率越低，因此應(yīng)當(dāng)盡量避免子查詢。如果子查詢不可避免，那么要在子查詢中過濾掉盡可能多的行。 5避免困難的正規(guī)表達(dá)式 MATCHES和LIKE關(guān)

9、鍵字支持通配符匹配，技術(shù)上叫正規(guī)表達(dá)式。但這種匹配特別耗費(fèi)時(shí)間。例如：SELECT FROM customer WHERE zipcode LIKE “98_ _ _” 即使在zipcode字段上建立了索引，在這種情況下也還是采用順序掃描的方式。如果把語句改為SELECT FROM customer WHERE zipcode >“98000”，在執(zhí)行查詢時(shí)就會利用索引來查詢，顯然會大大提高速度。 另外，還要避免非開始的子串。例如語句：SELECT FROM customer WHERE zipcode2，3 >“80”，在where子句中采用了非開始子串，因而這個語句也不會使用索

10、引。 6使用臨時(shí)表加速查詢 把表的一個子集進(jìn)行排序并創(chuàng)建臨時(shí)表，有時(shí)能加速查詢。有助于避免多重排序操作，而且在其他方面還能簡化優(yōu)化器的工作。例如： SELECT ，rcvbles.balance，other columns FROM cust，rcvbles WHERE cust.customer_id = rcvlbes.customer_id AND rcvblls.balance>0 AND cust.postcode>“98000” ORDER BY 如果這個查詢要被執(zhí)行多次而不止一次，可以把所有未付款的客戶找出來放在一個臨時(shí)文件中，并

11、按客戶的名字進(jìn)行排序： SELECT ，rcvbles.balance，other columns FROM cust，rcvbles WHERE cust.customer_id = rcvlbes.customer_id AND rcvblls.balance>0 ORDER BY INTO TEMP cust_with_balance 然后以下面的方式在臨時(shí)表中查詢： SELECT FROM cust_with_balance WHERE postcode>“98000” 臨時(shí)表中的行要比主表中的行少，而且物理順序就是所要求的順序，減少了

12、磁盤I/O，所以查詢工作量可以得到大幅減少。 注意：臨時(shí)表創(chuàng)建后不會反映主表的修改。在主表中數(shù)據(jù)頻繁修改的情況下，注意不要丟失數(shù)據(jù)。 7用排序來取代非順序存取 非順序磁盤存取是最慢的操作，表現(xiàn)在磁盤存取臂的來回移動。SQL語句隱藏了這一情況，使得在寫應(yīng)用程序時(shí)很容易寫出要求存取大量非順序頁的查詢。 有些時(shí)候，用數(shù)據(jù)庫的排序能力來替代非順序的存取能改進(jìn)查詢。  實(shí)例分析   下面我們舉一個制造公司的例子來說明如何進(jìn)行查詢優(yōu)化。制造公司數(shù)據(jù)庫中包括3個表，模式如下所示： 1part表 零件號零件描述其他列 （part_num）（part_desc）（other col

13、umn） 102，032Seageat 30G disk 500，049Novel 10M network card 2vendor表 廠商號廠商名其他列 （vendor _num）（vendor_name） （other column） 910，257Seageat Corp 523，045IBM Corp 3parven表 零件號廠商號零件數(shù)量 （part_num）（vendor_num）（part_amount） 102，032910，2573,450,000 234，423321，0014，000，000 下面的查詢將在這些表上定期運(yùn)行，并產(chǎn)生關(guān)于所有零件數(shù)量的報(bào)表： SELECT p

14、art_desc，vendor_name，part_amount FROM part，vendor，parven WHERE part.part_num=parven.part_num AND parven.vendor_num = vendor.vendor_num ORDER BY part.part_num 如果不建立索引，上述查詢代碼的開銷將十分巨大。為此，我們在零件號和廠商號上建立索引。索引的建立避免了在嵌套中反復(fù)掃描。關(guān)于表與索引的統(tǒng)計(jì)信息如下： 表行尺寸行數(shù)量每頁行數(shù)量數(shù)據(jù)頁數(shù)量 （table）(row size)（Row count）（Rows/Pages）（Data Page

15、s） part15010，00025400 Vendor1501，000 2540 Parven13 15，000300 50 索引鍵尺寸每頁鍵數(shù)量頁面數(shù)量 （Indexes）(Key Size)（Keys/Page)(Leaf Pages） part450020 Vendor45002 Parven825060 看 起來是個相對簡單的3表連接，但是其查詢開銷是很大的。通過查看系統(tǒng)表可以看到，在part_num上和vendor_num上有簇索引，因此索引是按照 物理順序存放的。parven表沒有特定的存放次序。這些表的大小說明從緩沖頁中非順序存取的成功率很小。此語句的優(yōu)化查詢規(guī)劃是：首先從pa

16、rt中順序 讀取400頁，然后再對parven表非順序存取1萬次，每次2頁（一個索引頁、一個數(shù)據(jù)頁），總計(jì)2萬個磁盤頁，最后對vendor表非順序存取1.5 萬次，合3萬個磁盤頁?？梢钥闯鲈谶@個索引好的連接上花費(fèi)的磁盤存取為5.04萬次。 實(shí)際上，我們可以通過使用臨時(shí)表分3個步驟來提高查詢效率： 1從parven表中按vendor_num的次序讀數(shù)據(jù)： SELECT part_num，vendor_num，price FROM parven ORDER BY vendor_num INTO temp pv_by_vn 這個語句順序讀parven（50頁），寫一個臨時(shí)表（50頁），并排

17、序。假定排序的開銷為200頁，總共是300頁。 2把臨時(shí)表和vendor表連接，把結(jié)果輸出到一個臨時(shí)表，并按part_num排序： SELECT pv_by_vn， vendor.vendor_num FROM pv_by_vn，vendor WHERE pv_by_vn.vendor_num=vendor.vendor_num ORDER BY pv_by_vn.part_num INTO TMP pvvn_by_pn DROP TABLE pv_by_vn 這 個查詢讀取pv_by_vn(50頁)，它通過索引存取vendor表1.5萬次，但由于按vendor_num次序排列，實(shí)際上只是通過

18、索引順序地讀 vendor表（402=42頁），輸出的表每頁約95行，共160頁。寫并存取這些頁引發(fā)5160=800次的讀寫，索引共讀寫892頁。 3把輸出和part連接得到最后的結(jié)果： SELECT pvvn_by_pn.，part.part_desc FROM pvvn_by_pn，part WHERE pvvn_by_pn.part_num=part.part_num DROP TABLE pvvn_by_pn 這 樣，查詢順序地讀pvvn_by_pn(160頁)，通過索引讀part表1.5萬次，由于建有索引，所以實(shí)際上進(jìn)行1772次磁盤讀寫，優(yōu)化比例為 301。筆者在Informix

19、Dynamic Sever上做同樣的實(shí)驗(yàn)，發(fā)現(xiàn)在時(shí)間耗費(fèi)上的優(yōu)化比例為51(如果增加數(shù)據(jù)量，比例可能會更大)。  小結(jié) 20的代碼用去了80的時(shí)間，這是程序設(shè)計(jì)中的一個著名定律，在數(shù)據(jù)庫應(yīng)用程序中也同樣如此。我們的優(yōu)化要抓住關(guān)鍵問題，對于數(shù)據(jù)庫應(yīng)用程序來說，重點(diǎn)在于SQL的執(zhí)行效率。查詢優(yōu)化的重點(diǎn)環(huán)節(jié)是使得數(shù)據(jù)庫服務(wù)器少從磁盤中讀數(shù)據(jù)以及順序讀頁而不是非順序讀頁。 百萬數(shù)據(jù)查詢優(yōu)化技巧三十則1.對查詢進(jìn)行優(yōu)化，應(yīng)盡量避免全表掃描，首先應(yīng)考慮在 where 及 order by 涉及的列上建立索引。2.應(yīng)盡量避免在 where 子句中對字段進(jìn)行 null 值判斷，否則將導(dǎo)致引

20、擎放棄使用索引而進(jìn)行全表掃描，如： select id from t where num is null 可以在num上設(shè)置默認(rèn)值0，確保表中num列沒有null值，然后這樣查詢： select id from t where num=03.應(yīng)盡量避免在 where 子句中使用!=或<>操作符，否則將引擎放棄使用索引而進(jìn)行全表掃描。4.應(yīng)盡量避免在 where 子句中使用 or 來連接條件，否則將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描，如： select id from t where num=10 or num=20 可以這樣查詢： select id from t where nu

21、m=10 union all select id from t where num=205.in 和 not in 也要慎用，否則會導(dǎo)致全表掃描，如： select id from t where num in(1,2,3) 對于連續(xù)的數(shù)值，能用 between 就不要用 in 了： select id from t where num between 1 and 36.下面的查詢也將導(dǎo)致全表掃描： select id from t where name like '%abc%' 若要提高效率，可以考慮全文檢索。7.如果在 where 子句中使用參數(shù)，也會導(dǎo)致全表掃描。因?yàn)镾Q

22、L只有在運(yùn)行時(shí)才會解析局部變量，但優(yōu)化程序不能將訪問計(jì)劃的選擇推遲到運(yùn)行時(shí)；它必須在編譯時(shí)進(jìn)行選擇。然 而，如果在編譯時(shí)建立訪問計(jì)劃，變量的值還是未知的，因而無法作為索引選擇的輸入項(xiàng)。如下面語句將進(jìn)行全表掃描： select id from t where num=num 可以改為強(qiáng)制查詢使用索引： select id from t with(index(索引名) where num=num8.應(yīng)盡量避免在 where 子句中對字段進(jìn)行表達(dá)式操作，這將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描。如： select id from t where num/2=100 應(yīng)改為: select id fro

23、m t where num=100*29.應(yīng)盡量避免在where子句中對字段進(jìn)行函數(shù)操作，這將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描。如： select id from t where substring(name,1,3)='abc'-name以abc開頭的id select id from t where datediff(day,createdate,'2005-11-30')=0-2005-11-30生成的id 應(yīng)改為: select id from t where name like 'abc%' select id from t wher

24、e createdate>='2005-11-30' and createdate<'2005-12-1'10.不要在 where 子句中的“=”左邊進(jìn)行函數(shù)、算術(shù)運(yùn)算或其他表達(dá)式運(yùn)算，否則系統(tǒng)將可能無法正確使用索引。11.在使用索引字段作為條件時(shí)，如果該索引是復(fù)合索引，那么必須使用到該索引中的第一個字段作為條件時(shí)才能保證系統(tǒng)使用該索引，否則該索引將不會被使用，并且應(yīng)盡可能的讓字段順序與索引順序相一致。12.不要寫一些沒有意義的查詢，如需要生成一個空表結(jié)構(gòu)： select col1,col2 into #t from t where 1=0 這類代碼

25、不會返回任何結(jié)果集，但是會消耗系統(tǒng)資源的，應(yīng)改成這樣： create table #t(.)13.很多時(shí)候用 exists 代替 in 是一個好的選擇： select num from a where num in(select num from b) 用下面的語句替換： select num from a where exists(select 1 from b where num=a.num)14.并不是所有索引對查詢都有效，SQL是根據(jù)表中數(shù)據(jù)來進(jìn)行查詢優(yōu)化的，當(dāng)索引列有大量數(shù)據(jù)重復(fù)時(shí)，SQL查詢可能不會去利用索引，如一表中有字段sex，male、female幾乎各一半，那么即使在sex

26、上建了索引也對查詢效率起不了作用。15.索引并不是越多越好，索引固然可以提高相應(yīng)的 select 的效率，但同時(shí)也降低了 insert 及 update 的效率，因?yàn)?insert 或 update 時(shí)有可能會重建索引，所以怎樣建索引需要慎重考慮，視具體情況而定。一個表的索引數(shù)最好不要超過6個，若太多則應(yīng)考慮一些不常使用到的列上建的索引是否有 必要。16.應(yīng)盡可能的避免更新 clustered 索引數(shù)據(jù)列，因?yàn)?clustered 索引數(shù)據(jù)列的順序就是表記錄的物理存儲順序，一旦該列值改變將導(dǎo)致整個表記錄的順序的調(diào)整，會耗費(fèi)相當(dāng)大的資源。若應(yīng)用系統(tǒng)需要頻繁更新 clustered 索引數(shù)據(jù)列，那么需要考慮是否應(yīng)將該索引建為 clustered 索引。17.盡量使用數(shù)字型字段，若只含數(shù)值信息的字段盡量不要設(shè)計(jì)為字符型，這會降低查詢和連接的性能，并會增加存儲開銷。這是因?yàn)橐嬖谔幚聿樵兒瓦B接時(shí)會逐個比較字符串中每一個字符，而對于數(shù)字型而言只需要比較一次就夠了。18.盡可能的使用 varchar/nvarchar 代替 char/ncha