查詢語句（SELECT）的優化
2024-07-21 02:39:27
供稿：網友
這篇文章是基于Informix數據庫寫的。希望對大家有用。
程序設計中的一個聞名定律是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）、使用臨時表加速查詢
把表的一個子集進行排序并創建臨時表，有時能加速查詢。它有助于避免多重排序*作，而且在其他方面還能簡化優化器的工作。
但要注重：臨時表創建后不會反映主表的修改。在主表中數據頻繁修改的情況下，注重不要丟失數據。
