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

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

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

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

4、一個原本糟糕的查詢計劃經(jīng)過系統(tǒng)的優(yōu) 化之后會變得高效，因此所寫語句的優(yōu)劣至關重要。下面重 點說明改善查詢計劃的解決方案。解決問題 下面以關系數(shù)據(jù)庫系統(tǒng) Informix 為例，介紹改善用戶查詢計 劃的方法。1合理使用索引 索引是數(shù)據(jù)庫中重要的數(shù)據(jù)結構，它的根本目的就是為了提 高查詢效率。 現(xiàn)在大多數(shù)的數(shù)據(jù)庫產品都采用 IBM 最先提出 的 ISAM 索引結構。索引的使用要恰到好處，其使用原則如下：在經(jīng)常進行連接，但是沒有指定為外鍵的列上建立索引， 而不經(jīng)常連接的字段則由優(yōu)化器自動生成索引。在頻繁進行排序或分組 (即進行 group by 或 order by 操作) 的列上建立索引。在條件表達

5、式中經(jīng)常用到的不同值較多的列上建立檢索， 在不同值少的列上不要建立索引。比如在雇員表的 性別 列上只有 男 與 女 兩個不同值，因此就無必要建立索引。如果建立索 引不但不會提高查詢效率，反而會嚴重降低更新速度。如果待排序的列有多個，可以在這些列上建立復合索引( compound index )。使用系統(tǒng)工具。如 Informix 數(shù)據(jù)庫有一個 tbcheck 工具， 可以在可疑的索引上進行檢查。在一些數(shù)據(jù)庫服務器上，索 引可能失效或者因為頻繁操作而使得讀取效率降低，如果一 個使用索引的查詢不明不白地慢下來，可以試著用 tbcheck 工具檢查索引的完整性，必要時進行修復。另外，當數(shù)據(jù)庫 表更新

6、大量數(shù)據(jù)后，刪除并重建索引可以提高查詢速度。 2避免或簡化排序 應當簡化或避免對大型表進行重復的排序。當能夠利用索引 自動以適當?shù)拇涡虍a生輸出時，優(yōu)化器就避免了排序的步驟。 以下是一些影響因素：索引中不包括一個或幾個待排序的列； group by 或 order by 子句中列的次序與索引的次序不一樣； 排序的列來自不同的表。 為了避免不必要的排序，就要正確地增建索引，合理地合并 數(shù)據(jù)庫表（盡管有時可能影響表的規(guī)范化，但相對于效率的 提高是值得的） 。如果排序不可避免， 那么應當試圖簡化它， 如縮小排序的列的范圍等。3消除對大型表行數(shù)據(jù)的順序存取 在嵌套查詢中，對表的順序存取對查詢效率可能產生

7、致命的 影響。比如采用順序存取策略，一個嵌套 3 層的查詢，如果 每層都查詢 1000 行，那么這個查詢就要查詢 10 億行數(shù)據(jù)。 避免這種情況的主要方法就是對連接的列進行索引。例如， 兩個表：學生表（學號、姓名、年齡 ）和選 課表（學號、課程號、成績） 。如果兩個表要做連接，就要 在 學號 這個連接字段上建立索引。 還可以使用并集來避免順序存取。盡管在所有的檢查列上都 有索引， 但某些形式的 where 子句強迫優(yōu)化器使用順序存取。 下面的查詢將強迫對 orders 表執(zhí)行順序操作： SELECT FROM orders WHERE （customer_num=104 AND order_n

8、um>1001） OR order_num=1008 雖然在 customer_num 和 order_num 上建有索引， 但是在上 面的語句中優(yōu)化器還是使用順序存取路徑掃描整個表。因為這個語句要檢索的是分離的行的集合，所以應該改為如下語 句：SELECT FROM orders WHERE customer_num=104AND order_num>1001UNIONSELECT FROM orders WHERE order_num=1008 這樣就能利用索引路徑處理查詢。4避免相關子查詢 一個列的標簽同時在主查詢和 where 子句中的查詢中出現(xiàn)， 那么很可能當主查詢中的列

9、值改變之后，子查詢必須重新查 詢一次。查詢嵌套層次越多，效率越低，因此應當盡量避免 子查詢。如果子查詢不可避免，那么要在子查詢中過濾掉盡 可能多的行。5避免困難的正規(guī)表達式MATCHES 和 LIKE 關鍵字支持通配符匹配，技術上叫正規(guī) 表達式。但這種匹配特別耗費時間。例如： SELECT FROM customer WHERE zipcode LIKE 98_ _ _即使在 zipcode 字段上建立了索引，在這種情況下也還是采 用順序掃描的方式。如果把語句改為 SELECT FROM customer WHERE zipcode >98000，在執(zhí)行查詢時就會利用索引來查詢，顯然會大

10、大提高速度。另外，還要避免非開始的子串。例如語句： SELECT FROM customer WHERE zipcode2 ， 3 >80 ，在 where 子句中采用了非開始子 串，因而這個語句也不會使用索引。6使用臨時表加速查詢 把表的一個子集進行排序并創(chuàng)建臨時表，有時能加速查詢。 有助于避免多重排序操作，而且在其他方面還能簡化優(yōu)化器 的工作。例如：SELECT ， rcvbles.balance ， other columnsFROM cust ， rcvblesWHERE cust.customer_id = rcvlbes.customer_idAND rc

11、vblls.balance>0AND cust.postcode>98000ORDER BY 如果這個查詢要被執(zhí)行多次而不止一次，可以把所有未付款 的客戶找出來放在一個臨時文件中，并按客戶的名字進行排 序：SELECT ， rcvbles.balance ， other columnsFROM cust ， rcvblesWHERE cust.customer_id = rcvlbes.customer_idAND rcvblls.balance>0ORDER BY INTO TEMP cust_with_balanc

12、e 然后以下面的方式在臨時表中查詢： SELECT FROM cust_with_balance WHERE postcode>98000 臨時表中的行要比主表中的行少，而且物理順序就是所要求 的順序，減少了磁盤 I/O ，所以查詢工作量可以得到大幅減 少。 注意：臨時表創(chuàng)建后不會反映主表的修改。在主表中數(shù)據(jù)頻 繁修改的情況下，注意不要丟失數(shù)據(jù)。 7用排序來取代非順序存取 非順序磁盤存取是最慢的操作，表現(xiàn)在磁盤存取臂的來回移 動。SQL語句隱藏了這一情況， 使得在寫應用程序時很容易 寫出要求存取大量非順序頁的查詢。 有些時候，用數(shù)據(jù)庫的排序能力來替代非順序的存取能改進 查詢。實例分析 下

13、面我們舉一個制造公司的例子來說明如何進行查詢優(yōu)化。 制造公司數(shù)據(jù)庫中包括 3 個表，模式如下所示： 1 part 表零件號 ( part_num ) 102 ， 032 500 ， 049 2 vendor 表 廠商號 ( vendor _num ) 910 ， 257 523 ， 045 3 parven 表 零件號 ( part_num ) 102 ， 032 234 ， 423 零件描述 其他列part_desc )other column )Seageat 30G diskNovel 10M network card廠商名( vendor_name ) Seageat Corp IBM

14、 Corp廠商號vendor_num )910 ，257321 ，001其他列other column ) 零件數(shù)量part_amount )3,450,0004，000 ，000下面的查詢將在這些表上定期運行，并產生關于所有零件數(shù) 量的報表：SELECT part_desc ， vendor_name ， part_amountFROM part ， vendor ， parvenWHERE part.part_num=parven.part_numAND parven.vendor_num = vendor.vendor_numORDER BY part.part_num如果不建立索引，上

15、述查詢代碼的開銷將十分巨大。為此，我們在零件號和廠商號上建立索引。索引的建立避免了在嵌套中反復掃描。關于表與索引的統(tǒng)計信息如下：表行尺寸行數(shù)量每頁行數(shù)量數(shù)據(jù)頁數(shù)量( table )(row size)( Row count )( Rows/Pages )Data Pages )part15010 ， 00025400Vendor1501，0002540Parven1315 ， 00030050索引鍵尺寸每頁鍵數(shù)量頁面數(shù)量( Indexes )(Key Size)( Keys/Page)(Leaf Pages )part450020Vendor45002Parven825060看起來是個相對簡單

16、的 3 表連接，但是其查詢開銷是很大的。 通過查看系統(tǒng)表可以看到，在 part_num 上和 vendor_num上有簇索引， 因此索引是按照物理順序存放的。 parven 表沒 有特定的存放次序。這些表的大小說明從緩沖頁中非順序存 取的成功率很小。此語句的優(yōu)化查詢規(guī)劃是：首先從 part 中順序讀取 400 頁，然后再對 parven 表非順序存取 1 萬次， 每次 2 頁（一個索引頁、 一個數(shù)據(jù)頁），總計 2 萬個磁盤頁， 最后對 vendor 表非順序存取 1.5 萬次，合 3 萬個磁盤頁。 可以看出在這個索引好的連接上花費的磁盤存取為 5.04 萬 次。實際上，我們可以通過使用臨時表分

17、 3 個步驟來提高查詢效 率：1從 parven 表中按 vendor_num 的次序讀數(shù)據(jù)：SELECT part_num ， vendor_num ， priceFROM parvenORDER BY vendor_numINTO temp pv_by_vn這個語句順序讀 parven （ 50 頁），寫一個臨時表（ 50 頁）， 并排序。假定排序的開銷為 200 頁，總共是 300 頁。 2把臨時表和 vendor 表連接，把結果輸出到一個臨時表， 并按 part_num 排序：SELECT pv_by_vn ， vendor.vendor_numFROM pv_by_vn ， vend

18、orWHERE pv_by_vn.vendor_num=vendor.vendor_numORDER BY pv_by_vn.part_numINTO TMP pvvn_by_pnDROP TABLE pv_by_vn這個查詢讀取 pv_by_vn（50 頁 ），它通過索引存取 vendor 表 1.5 萬次，但由于按 vendor_num 次序排列，實際上只是通 過索引順序地讀 vendor 表（ 40 2=42 頁），輸出的表每頁 約 95 行，共 160 頁。寫并存取這些頁引發(fā)5 160=800 次的讀寫，索引共讀寫 892 頁。3把輸出和 part 連接得到最后的結果：SELECT p

19、vvn_by_pn. ， part.part_descFROM pvvn_by_pn ， partWHERE pvvn_by_pn.part_num=part.part_numDROP TABLE pvvn_by_pn 這樣，查詢順序地讀 pvvn_by_pn（160 頁 ），通過索引讀 part 表 1.5 萬次，由于建有索引，所以實際上進行 1772 次磁盤 讀寫，優(yōu)化比例為 30 : 1。筆者在Informix Dynamic Sever 上做同樣的實驗，發(fā)現(xiàn)在時間耗費上的優(yōu)化比例為5 : 1（如果增加數(shù)據(jù)量，比例可能會更大 ）。小結20 的代碼用去了 80的時間，這是程序設計中的一個著

20、 名定律，在數(shù)據(jù)庫應用程序中也同樣如此。我們的優(yōu)化要抓 住關鍵問題，對于數(shù)據(jù)庫應用程序來說，重點在于 SQL 的 執(zhí)行效率。查詢優(yōu)化的重點環(huán)節(jié)是使得數(shù)據(jù)庫服務器少從磁 盤中讀數(shù)據(jù)以及順序讀頁而不是非順序讀頁。 百萬數(shù)據(jù)查詢優(yōu)化技巧三十則1. 對查詢進行優(yōu)化，應盡量避免全表掃描，首先應考慮在 where 及 order by 涉及的列上建立索引。2. 應盡量避免在 where 子句中對字段進行 null 值判斷， 否 則將導致引擎放棄使用索引而進行全表掃描，如： select id from t where num is null可以在 num 上設置默認值 0，確保表中 num 列沒有 nul

21、l 值， 然后這樣查詢：select id from t where num=03. 應盡量避免在 where子句中使用!=或<>操作符，否 則將引擎放棄使用索引而進行全表掃描。4. 應盡量避免在 where 子句中使用 or 來連接條件， 否則將 導致引擎放棄使用索引而進行全表掃描，如：select id from t where num=10 or num=20可以這樣查詢：select id from t where num=10union all select id from t where num=20 5.in 和 not in 也要慎用，否則會導致全表掃描，如： se

22、lect id from t where num in(1,2,3) 對于連續(xù)的數(shù)值，能用 between 就不要用 in 了： select id from t where num between 1 and 36. 下面的查詢也將導致全表掃描： select id from t where name like %abc% 若要提高效率，可以考慮全文檢索。7. 如果在 where 子句中使用參數(shù)，也會導致全表掃描。因 為 SQL 只有在運行時才會解析局部變量，但優(yōu)化程序不能 將訪問計劃的選擇推遲到運行時；它必須在編譯時進行選擇。 然而，如果在編譯時建立訪問計劃，變量的值還是未知的， 因而無法

23、作為索引選擇的輸入項。如下面語句將進行全表掃 描：select id from t where num=num 可以改為強制查詢使用索引： select id from t with(index( 索引名 ) where num=num8. 應盡量避免在 where 子句中對字段進行表達式操作，這 將導致引擎放棄使用索引而進行全表掃描。如： select id from t where num/2=100 應改為 :select id from t where num=100*29. 應盡量避免在 where 子句中對字段進行函數(shù)操作，這將導 致引擎放棄使用索引而進行全表掃描。如：select

24、 id from t where substring(name,1,3)=abc-nameabc 開頭的 idselect id from t wheredatediff(day,createdate,2005-11-30)=0-2005-11-30 生成的 id應改為 :select id from t where name like abc%select id from t where createdate>=2005-11-30 and createdate<2005-12-110. 不要在 where 子句中的 = 左邊進行函 數(shù)、算術運算或其他表達式運算，否則系統(tǒng)將可能無

25、法正確 使用索引。11. 在使用索引字段作為條件時， 如果該索引是復合索引， 么必須使用到該索引中的第一個字段作為條件時才能保證 系統(tǒng)使用該索引，否則該索引將不會被使用，并且應盡可能 的讓字段順序與索引順序相一致。12. 不要寫一些沒有意義的查詢，如需要生成一個空表結構： select col1,col2 into #t from t where 1=0 這類代碼不會返回任何結果集，但是會消耗系統(tǒng)資源的，應 改成這樣：create table #t(.)13. 很多時候用 exists 代替 in 是一個好的選擇： select num from a where num in(select n

26、um from b) 用下面的語句替換： select num from a where exists(select 1 from b where num=a.num)14. 并不是所有索引對查詢都有效， SQL 是根據(jù)表中數(shù)據(jù)來 進行查詢優(yōu)化的， 當索引列有大量數(shù)據(jù)重復時， SQL 查詢可 能不會去利用索引，如一表中有字段sex ，male 、female 幾乎各一半，那么即使在 sex 上建了索引也對查詢效率起不了 作用。15. 索引并不是越多越好，索引固然可以提高相應的select的效率，但同時也降低了 insert 及 update 的效率，因為 insert 或 update 時有可

27、能會重建索引，所以怎樣建索引需 要慎重考慮，視具體情況而定。一個表的索引數(shù)最好不要超 過6 個，若太多則應考慮一些不常使用到的列上建的索引是 否有必要。16. 應盡可能的避免更新 clustered 索引數(shù)據(jù)列，因為 clustered 索引數(shù)據(jù)列的順序就是表記錄的物理存儲順序， 一旦該列值改變將導致整個表記錄的順序的調整，會耗費相 當大的資源。若應用系統(tǒng)需要頻繁更新clustered 索引數(shù)據(jù)列，那么需要考慮是否應將該索引建為clustered 索引。17. 盡量使用數(shù)字型字段， 若只含數(shù)值信息的字段盡量不要設 計為字符型，這會降低查詢和連接的性能，并會增加存儲開 銷。這是因為引擎在處理查詢和連接時會逐個比較字符串中 每一個字符，而對于數(shù)字型而言只需要比較一次就夠了。18. 盡可能的使用 varchar/nvarchar 代替 char/nchar ，因 為首先變長字段存儲空間小，可以節(jié)省存儲空間，其次對于 查詢來說，在一個相對較小的字段內搜索效率顯然要高些。19. 任何地方都不要使用 select * from t ，用具體