查詢語句（SELECT）的優化

　　這篇文章是基於Informix數據庫寫的希望對大家有用
　　
　　
　　程序設計中的一個著名定律是％的代碼用去了％的時間在數據庫應用程序中也同樣如此數據庫應用程序的優化通常可分為兩個方面源代碼的優化和SQL語句的優化源代碼的優化在時間成本和風險上代價很高另一方面源代碼的優化對數據庫系統性能的提升收效有限
　　許多程序員認為查詢優化是DBMS（數據庫管理系統）的任務與程序員所編寫的SQL語句關系不大這是錯誤的一個好的查詢計劃往往可以使程序性能提高數十倍查詢計劃是用戶所提交的SQL語句的集合查詢規劃是經過優化處理之後所產生的語句集合DBMS處理查詢計劃的過程是這樣的在做完查詢語句的詞法語法檢查之後將語句提交給DBMS的查詢優化器優化器做完代數優化和存取路徑的優化之後由預編譯模塊對語句進行處理並生成查詢規劃然後在合適的時間提交給系統處理執行最後將執行結果返回給用戶雖然現在的數據庫產品在查詢優化方面已經做得越來越好但由用戶提交的SQL語句是系統優化的基礎很難設想一個原本糟糕的查詢計劃經過系統的優化之後會變得高效因此用戶所寫語句的優劣至關重要
　　對查詢語句進行優化的理由
　　下列幾方面的原因是我們進行SQL語句優化的理由
　　◆ SQL語句是對數據庫(數據)進行*作的惟一途徑
　　◆ SQL語句消耗了%~%的數據庫資源
　　◆ SQL語句獨立於程序設計邏輯相對於對程序源代碼的優化對SQL語句的優化在時間成本和風險上的代價都很低
　　◆ SQL語句可以有不同的寫法
　　◆ SQL語句易學難精通
　　從大多數數據庫應用系統的實例來看查詢*作在各種數據庫*作中所占據的比重最大而查詢*作所基於的SELECT語句在SQL語句中又是代價最大的語句
　　查詢語句（SELECT）的優化建議
　　（）合理使用索引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”這樣的不按索引字鍵順序寫法。TW.WINGwIT.cOm
　　（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 em=em 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>) OR order_num=
　　 雖然在cust_num和order_num上建有索引但是在上面的語句中優化器還是使用順序存取路徑掃描整個表因為這個語句要檢索的是分離的行的集合所以應該改為如下語句
　　 SELECT ＊ FROM orders WHERE cust_num= AND order_num>
　　 UNION
　　 SELECT ＊ FROM orders WHERE order_num=
　　 這樣就能利用索引路徑處理查詢
　　（）對於大數據量的求和應避免使用單一的sum命令處理可采用group by方式與其結合有時其效率可提高幾倍甚至百倍
　　（）避免會引起磁盤讀寫的rowid*作在where子句中或select語句中用rowid要產生磁盤讀寫是一個物理過程會影響性能
　　（）使用臨時表加速查詢
　　 把表的一個子集進行排序並創建臨時表有時能加速查詢它有助於避免多重排序*作而且在其他方面還能簡化優化器的工作
　　 但要注意臨時表創建後不會反映主表的修改在主表中數據頻繁修改的情況下注意不要丟失數據
　　

From:http://tw.wingwit.com/Article/program/Oracle/201311/18674.html