sql語句調優及技巧

一、sql語言介紹

1.sql語言簡介

結構化查詢語言 (SQL)(StructuredQuery Language）最早是IBM的聖約瑟研究實驗室為其關聯式資料庫管理系統SYSTEM R開發的一種查詢語言，它的前身是SQUARE語言。SQL語言結構簡潔，功能強大，簡單易學，所以自從IBM公司1981年推出以來，SQL語言得到了廣泛的應用。如今無論是像Oracle、Sybase、DB2、Informix、SQL Server這些大型的資料庫管理系統，還是像Visual Foxpro、PowerBuilder這些PC上常用的資料庫開發系統，都支援SQL語言作為查詢語言。

2.sql語言三個組成部分

資料定義語言 (Data Definition Language)(DDL）：CREATE、DROP、ALTER等語句。

資料操作語言(DML）：資料查詢語言（DQL），如SELECT語句；資料更新語言，如，INSERT（插入）、UPDATE（修改）、DELETE（刪除）語句。 資料控制語言（DCL）：GRANT、REVOKE、COMMIT、ROLLBACK等語句。二、sql技巧 1.ORACLE的解析器按照從右至左的順序處理FROM子句中的表名, FROM子句中包含多個表的情況下,你必須選擇記錄條數最少的表作為基礎資料表。

例如: 表 TAB1 16,384條記錄   表 TAB2 
1條記錄

 選擇TAB2作為基礎資料表 (最好的方法)

     select count(*) from tab1,tab2   執行時間0.96秒

選擇TAB2作為基礎資料表 (不佳的方法)

     select count(*) from tab2,tab1   執行時間26.09秒

2.如果有3個以上的表串連查詢, 那就需要選擇交叉表(intersection table)作為基礎資料表, 交叉表是指那個被其他表所引用的表。

例如:  EMP表描述了LOCATION表和CATEGORY表的交集

SELECT *    FROM LOCATION L , CATEGORY C, EMP E    WHERE E.EMP_NO BETWEEN 1000 AND 2000   AND E.CAT_NO = C.CAT_NO   AND E.LOCN = L.LOCN

3.ORACLE採用自下而上的順序解析WHERE子句,根據這個原理,表之間的串連必須寫在其他WHERE條件之前, 那些可以過濾掉最大數量記錄的條件必須寫在WHERE子句的末尾。

例如: (低效,執行時間156.3秒)

SELECT … FROM EMP E WHERE SAL> 50000 AND    JOB = ‘MANAGER’AND   25 < (SELECT COUNT(*) FROM EMP             WHERE MGR=E.EMPNO);

 (高效,執行時間10.6秒)

SELECT … FROM EMP E WHERE 25 < (SELECT COUNT(*) FROMEMP             WHERE MGR=E.EMPNO)AND    SAL > 50000AND    JOB = ‘MANAGER’;

4.SELECT子句中避免使用 ‘ * ‘ 。SELECT子句中列出所有的COLUMN時,使用動態SQL列引用‘*’ 是一個方便的方法.不幸的是,這是一個非常低效的方法. 實際上,ORACLE在解析的過程中, 會將’*’ 依次轉換成所有的列名, 這個工作是通過查詢資料字典完成的, 這意味著將耗費更多的時間。5.使用oracle內建函數提高效率。如使用DECODE函數來減少處理時間，可以避免重複掃描相同記錄或重複串連相同的表。6.用TRUNCATE替代DELETE。

當刪除表中的記錄時,在通常情況下, 復原段(rollbacksegments ) 用來存放可以被恢複的資訊. 如果你沒有COMMIT事務,ORACLE會將資料恢複到刪除之前的狀態(準確地說是恢複到執行刪除命令之前的狀況)而當運用TRUNCATE時, 復原段不再存放任何可被恢複的資訊.當命令運行後,資料不能被恢複.因此很少的資源被調用,執行時間也會很短。7.盡量多使用COMMIT。只要有可能,在程式中盡量多使用COMMIT, 這樣程式的效能得到提高,需求也會因為COMMIT所釋放的資源而減少。注意：在使用COMMIT時必須要注意到事務的完整性（不能再迴圈中使用commit）。8.用Where子句替換HAVING子句。

避免使用HAVING子句, HAVING 只會在檢索出所有記錄之後才對結果集進行過濾. 這個處理需要排序,總計等操作. 如果能通過WHERE子句限制記錄的數目,那就能減少這方面的開銷。HAVING 中的條件一般用於對一些集合函數的比較,如COUNT()等等. 除此而外,一般的條件應該寫在WHERE子句中。9.使用表的別名(Alias)。當在SQL語句中串連多個表時, 請使用表的別名並把別名首碼於每個Column上.這樣一來,就可以減少解析的時間並減少那些由Column歧義引起的語法錯誤.Column歧義指的是由於SQL中不同的表具有相同的Column名,當SQL語句中出現這個Column時,SQL解析器無法判斷這個Column的歸屬。10.用表串連替換EXISTS。11.用EXISTS替換DISTINCT。EXISTS使查詢更為迅速,因為RDBMS核心模組將在子查詢的條件一旦滿足後,立刻返回。12.用索引提高效率。

索引是表的一個概念部分,用來提高檢索資料的效率. 實際上,ORACLE使用了一個複雜的自平衡B-tree結構. 通常,通過索引查詢資料比全表掃描要快. 當ORACLE找出執行查詢和Update語句的最佳路徑時, ORACLE最佳化器將使用索引. 同樣在連接多個表時使用索引也可以提高效率. 另一個使用索引的好處是,它提供了主鍵(primarykey)的唯一性驗證。

雖然使用索引能得到查詢效率的提高,但是我們也必須注意到它的代價. 索引需要空間來儲存,也需要定期維護, 每當有記錄在表中增減或索引列被修改時, 索引本身也會被修改. 這意味著每條記錄的INSERT , DELETE , UPDATE將為此多付出4 , 5 次的磁碟I/O . 因為索引需要額外的儲存空間和處理,那些不必要的索引反而會使查詢反應時間變慢。

13.用>=替代>。兩者的區別在於, 前者DBMS將直接跳到第一個DEPT等於4的記錄而後者將首先定位到DEPTNO=3的記錄並且向前掃描到第一個DEPT大於3的記錄（>=4和>3的比較）。14.用UNION替換OR (適用於索引列)。通常情況下, 用UNION替換WHERE子句中的OR將會起到較好的效果. 對索引列使用OR將造成全表掃描. 注意, 以上規則只針對多個索引列有效. 如果有column沒有被索引, 查詢效率可能會因為你沒有選擇OR而降低 。15.用IN來替換OR。這是一條簡單易記的規則，但是實際的執行效果還須檢驗，在ORACLE8i下，兩者的執行路徑似乎是相同的。16.避免在索引列上使用IS NULL和IS NOT NULL。

避免在索引中使用任何可以為空白的列，ORACLE將無法使用該索引 ．對於單列索引，如果列包含空值，索引中將不存在此記錄. 對於複合索引，如果每個列都為空白，索引中同樣不存在此記錄.　如果至少有一個列不為空白，則記錄存在於索引中。

空值不存在於索引列中,所以WHERE子句中對索引列進行空值比較將使ORACLE停用該索引。

17.和一般的觀點相反, count(*) 比count(1)稍快，當然如果可以通過索引檢索，對索引列的計數仍舊是最快的. 例如 COUNT(EMPNO)。18.對於複合索引，查詢列必須使用前置列。

create unique index PK_ENAME on DEPT(DNAME,LOC)

select * from dept t where t.loc = 'xxx'; （沒有使用索引）。

select * from dept t where t.dname = ''xxx; （使用索引）。

19.比較值與索引列的資料類型一致。（若不一致，存在隱士轉換）

20.查詢列與索引列次序一致。

create unique index PK_ENAME on DEPT(DNAME,LOC)

select * from dept where loc = 'BOSTON' and dname ='SALES' 隱式轉換 --> select * from dept where dname ='SALES' and loc = 'BOSTON' 

21.避免對索引列進行計算。

create index IND_EMPNO on EMP (EMPNO)

select * from emp t where to_char(t.empno) = '12345'; 未使用索引。

22.比較值避免使用NULL。全表掃描。

23.用多表串連代替EXISTS子句。

24.用NOT EXISTS代替NOT IN。

25.盡量少用子查詢。

26.把過濾記錄數最多的條件放在最後面；

三、索引原理。