SQL系列,終極系列
關於SQL語句最佳化方法
有些是通用的(如避免Select *);
有些不同的資料庫管理系統有所區別(如Where子句順序);
然後必鬚根據實際環境進行調優,因為即使是相同的資料庫和表,在資料量或其他環境變化之後,SQL效率可能是不同的。所以,最佳化不是一蹴而就的。
一些總結
下面是我在工作中,主要是Oracle環境下一些常用的SQL語句最佳化方法,僅供參考。當然,後續可以再深入研究下SQL執行計畫、索引等。
避免Select *
Selcet中每少提取一個欄位,資料的提取速度就會有相應的提升。提升的速度還要看您捨棄的欄位的大小來判斷。應避免使用Select *。
表關聯順序
Oracle的解析器按照從右至左的順序處理from子句中的表名,from子句中寫在最後的表(基礎資料表 driving table)將被最先處理,在from子句中包含多個表的情況下,你必須選擇記錄條數最少的表作為基礎資料表。如果有3個以上的表串連查詢, 那就需要選擇交叉表(intersection table)作為基礎資料表, 交叉表是指那個被其他表所引用的表。
WHERE子句中的順序
Oracle採用自下而上的順序解析Where子句,根據這個原理,表之間的串連必須寫在其他Where條件之前,那些可以過濾掉最大數量記錄的條件必須寫在Where子句的末尾。
避免全表掃描
Where中少用NOT、!=、<>、!<、!>、NOT EXISTS、NOT IN、NOT LIKE,它們會引起全表掃描。
用Where子句替代having子句
避免使用having子句,having只會在檢索出所有記錄之後才對結果集進行過濾。
exists代替in
Oracle中In子查詢返回的結果不能超過1000條,使用exists為替代方案。
效能測試
目的
我在用戶端中執行如下語句,通過改變表關聯順序、where條件順序,查看所用時間的變化。可能換個環境,得出的結果會大不相同,請以實際環境為準哦。
資料庫環境
TableA 大表(DB2資料量:77763 ORACLE資料量:77775)
TableB 小表(DB2資料量:297 ORACLE資料量:18294)
表關聯順序自串連
小表在前,DB2用時:0.015s ORACLE用時:0.329s
select count(*) from TableB b,TableA aWHERE b.ID=a.ID
大表在前,DB2用時:0.016s ORACLE用時:0.678s
select count(*) from TableA a,TableB b WHERE a.ID=b.ID
可以看到,DB2下時間變化不大,Oracle相差2倍。
左串連
小表在前,DB2用時:0.453s ORACLE用時:0.047s
select count(*)from TableB bLEFT JOIN TableA a ON b.ID=a.ID
大表在前,DB2用時:0.031s ORACLE用時:0.031s
select count(*)from TableA aLEFT JOIN TableB b ON a.ID=b.ID
可以看到,DB2下用時相差10倍以上,Oracle下變化不大。
內串連
小表在前,DB2用時:0.078s ORACLE用時:0.015s
select count(*)from TableB bINNER JOIN TableA a ON b.ID=a.ID
大表在前,DB2用時:0.016s ORACLE用時:0.016s
select count(*)from TableA aINNER JOIN TableB b ON a.ID=b.ID
可以看到,DB2下用時相差4倍,Oracle下變化不大。
WHERE條件順序
過濾條件在右,DB2用時:0.109s ORACLE用時:0.015s
select count(*)from TableB b,TableA aWHERE b.ID=a.ID AND b.TYPE = '0001'
過濾條件在左,DB2用時:0.156s ORACLE用時:0.016s
select count(*)from TableB b,TableA aWHERE b.TYPE = '0001' AND b.ID=a.ID
DB2用時變化1/3,當條件比較多,資料量比較大時,會更加明顯。