　　這篇文章是基於Informix資料庫寫的。希望對大家有用。[@more@]　　程式設計中的一個著名定律是20％的程式碼用去了80％的時間，在資料庫應用程式中也同樣如此。資料庫應用程式的最佳化通常可分為兩個方面：原始碼的最佳化和SQL語句的最佳化。原始碼的最佳化在時間成本和風險上代價很高；另一方面，原始碼的最佳化對資料庫系統效能的提升收效有限。
　　許多程式設計師認為查詢最佳化是DBMS（資料庫管理系統）的任務，與程式設計師所編寫的SQL語句關係不大，這是錯誤的。一個好的查詢計劃往往可以使程式效能提高數十倍。查詢計劃是使用者所提交的SQL語句的集合，查詢規劃是經過最佳化處理之後所產生的語句集合。DBMS處理查詢計劃的過程是這樣的：在做完查詢語句的詞法、語法檢查之後，將語句提交給DBMS的查詢最佳化器，最佳化器做完代數最佳化和存取路徑的最佳化之後，由預編譯模組對語句進行處理並生成查詢規劃，然後在合適的時間提交給系統處理執行，最後將執行結果返回給使用者。雖然現在的資料庫產品在查詢最佳化方面已經做得越來越好，但由使用者提交的SQL語句是系統最佳化的基礎，很難設想一個原本糟糕的查詢計劃經過系統的最佳化之後會變得高效，因此使用者所寫語句的優劣至關重要。
　　1、對查詢語句進行最佳化的理由
　　下列幾方面的原因是我們進行SQL語句最佳化的理由：
　　◆ SQL語句是對資料庫(資料)進行*作的惟一途徑；
　　◆ SQL語句消耗了70%~90%的資料庫資源；
　　◆ SQL語句獨立於程式設計邏輯，相對於對程式原始碼的最佳化，對SQL語句的最佳化在時間成本和風險上的代價都很低；
　　◆ SQL語句可以有不同的寫法；
　　◆ SQL語句易學，難精通。
　　從大多數資料庫應用系統的例項來看，查詢*作在各種資料庫*作中所佔據的比重最大，而查詢*作所基於的SELECT語句在SQL語句中又是代價最大的語句。
　　2、查詢語句（SELECT）的最佳化建議
　　（1）、合理使用索引：where子句中變數順序應與索引字鍵順序相同。
　　如：create index test_idx on test(hm, rq, xx)
　　　　索引字鍵順序：首先是號碼hm，其次是日期rq，最後是標誌xx，所以where子句變數順序應是where hm<=“P1234”and rq=“06/06/1999”and xx=“DDD”,不應是where xx=“DDD” and rq=“06/06/1999” and hm <=“P1234”這樣的不按索引字鍵順序寫法。
　　（2）、將最具有限制性的條件放在前面，大值在前，小值在後。
　　　　如：where colA<=10000 AND colA>=1　效率高
　　　　 where colA>=1 AND colA<=10000 效率低
　　（3）、避免採用MATCHES和LIKE萬用字元匹配查詢
　　萬用字元匹配查詢特別耗費時間。即使在條件欄位上建立了索引，在這種情況下也還是採用順序掃描的方式。
　　例如語句：SELECT ＊ FROM customer WHERE zipcode MATCHES “524*”
　　可以考慮將它改為SELECT ＊ FROM customer WHERE ZipCode<=“524999” AND ZipCode >=“524000”，則在執行查詢時就會利用索引來查詢，顯然會大大提高速度。
　　（4）、避免非開始的子串
　　例如語句：SELECT ＊ FROM customer WHERE zipcode[2，3] >“24”，在where子句中採用了非開始子串，因而這個語句也不會使用索引。
　　（5）、避免相關子查詢
　　一個欄位的標籤同時在主查詢和where子句中的查詢中出現，那麼很可能當主查詢中的欄位值改變之後，子查詢必須重新查詢一次。查詢巢狀層次越多，效率越低，因此應當儘量避免子查詢。如果子查詢不可避免，那麼要在子查詢中過濾掉儘可能多的行。
　　例如：將下面的語句
　　select hm,rq from TabA
　　where item IN (select item form TabB where TabB.num=50)
　　改為：select hm,bf from TabA, TabB
　　 where TabA.item=TabB.item AND TabB.num=50
　　（6）、避免或簡化排序
　　應當簡化或避免對大型表進行重複的排序。當能夠利用索引自動以適當的次序產生輸出時，最佳化器就避免了排序的步驟。以下是一些影響因素：
　　◆ 索引中不包括一個或幾個待排序的欄位；
　　◆ group by或order by子句中欄位的次序與索引的次序不一樣；
　　◆ 排序的欄位來自不同的表。
　　為了避免不必要的排序，就要正確地增建索引，合理地合併資料庫表（儘管有時可能影響表的規範化，但相對於效率的提高是值得的）。如果排序不可避免，那麼應當試圖簡化它，如縮小排序的欄位的範圍等。
　　（7）、消除對大型錶行資料的順序存取
　　在巢狀查詢中，對錶的順序存取對查詢效率可能產生致命的影響。比如採用順序存取策略，一個巢狀3層的查詢，如果每層都查詢1000行，那麼這個查詢就要查詢10億行資料。避免這種情況的主要方法就是對連線的欄位進行索引。例如，兩個表：學生表（學號、姓名、年齡……）和選課表（學號、課程號、成績）。如果兩個表要做連線，就要在“學號”這個連線欄位上建立索引。
　　還可以使用並集來避免順序存取。儘管在所有的檢查列上都有索引，但某些形式的where子句強迫最佳化器使用順序存取。下面的查詢將強迫對orders表執行順序*作：
　　 SELECT ＊ FROM orders WHERE (cust_num=126 AND order_num>1001) OR order_num=1008
　　雖然在cust_num和order_num上建有索引，但是在上面的語句中最佳化器還是使用順序存取路徑掃描整個表。因為這個語句要檢索的是分離的行的集合，所以應該改為如下語句：
　　 SELECT ＊ FROM orders WHERE cust_num=126 AND order_num>1001
　　 UNION
　　 SELECT ＊ FROM orders WHERE order_num=1008
　　這樣就能利用索引路徑處理查詢。
　　（8）、對於大資料量的求和應避免使用單一的sum命令處理，可採用group by方式與其結合，有時其效率可提高几倍甚至百倍。
　　（9）、避免會引起磁碟讀寫的rowid*作。在where子句中或select語句中，用rowid要產生磁碟讀寫，是一個物理過程，會影響效能。
　　（10）、使用臨時表加速查詢
　　把表的一個子集進行排序並建立臨時表，有時能加速查詢。它有助於避免多重排序*作，而且在其他方面還能簡化最佳化器的工作。
　　但要注意：臨時表建立後不會反映主表的修改。在主表中資料頻繁修改的情況下，注意不要丟失資料。
　　
　　

查詢語句（SELECT）的最佳化

相關文章