最近,因為發(fā)現(xiàn)以前使用的數(shù)據(jù)查詢語句的效率越來越差,導致每次進行大量數(shù)據(jù)的操作都需要耗一個小時以上.這速度實在是太龜慢了,簡直是無法忍受了.本人決定親自拿把殺豬刀砍向它,好給它顏色瞧瞧.經(jīng)過一系列的測試后發(fā)現(xiàn),原來罪灰禍手是查詢數(shù)據(jù)時因為語句的組合方式不對導致了數(shù)據(jù)庫查詢慢,當時那個淚崩啊,感覺這實在是太坑爹了.
? ? ?因此,我就借此機會把我所知道的能提高查詢效率的方法整理了一下,并分享出來和大家共同學習.
一. 書寫順序方面
我們常常會在寫oracle語句后發(fā)現(xiàn)該語句的執(zhí)行時間過長,效率低下的問題.然而我們有沒有想過怎么會發(fā)生這樣的情況呢?
我大致歸納為兩個方面:
第一: 不了解oracle的解析器解析sql語句的順序
a. 數(shù)據(jù)表的處理順序
oracle 在解析一個查詢語句FROM后面的一系列數(shù)據(jù)表是按照從右往左的順序進行的.也就是說最后的數(shù)據(jù)表將是最先被oracle處理的,所以我們在寫多個表關聯(lián)的查詢語句時,把數(shù)據(jù)量最小的表或者是經(jīng)過條件篩選后得到數(shù)據(jù)量最小的表放到最后,數(shù)據(jù)量大的表就放在最前面.
select col1, col2 from t1, t2 where t1.id = t2.id ?and t2.sex = 'male';
上面的語句中數(shù)據(jù)表t2是第一個被oracle處理的數(shù)據(jù)表,然后就是t1.
b.where 條件的處理順序
oracle 在解析查詢語句制定執(zhí)行計劃時,針對where后面的查詢條件的解析是按照從下往上的順序進行的.也就說查詢的子條件越處于where的后面,它被oracle處理優(yōu)先級就越高.所以我們可以把能過濾大量數(shù)據(jù)的條件放到最后,讓oracle優(yōu)先處理它從而返回一個最小數(shù)據(jù)集,好讓oracle在后續(xù)的過濾工作基于該最小數(shù)據(jù)集進行,來縮小整個查詢時間.
select col1, col2 from t1, t2 where t1.id = t2.id and t2.sex = 'male' and t2.etime>=trunc(sysdate-1)?and t2.etime
上面語句中的查詢條件t2.channel = '0' 將會被oracle最先解析.
第二:查詢方式的錯誤選擇導致效率低下
平時工作下來積累的一些優(yōu)化建議如下:
1. where 后面的條件盡量避免涉及null值得判斷,為什么呢?那是因為一旦涉及到了null的判斷,oracle將不會走該字段所涉及的索引而觸發(fā)全表掃描.
select col1 from t where t.servid is not null; --效率低
如果t表的servid中有索引的話,那么本次查詢將拋棄索引,走全表掃描.建議如果真的需要判斷的話,可以定義該字段的默認值為0或者其它值.
2. where 后面盡量不要使用多個or把條件組合起來, 可以使用union/union all來實現(xiàn)
select col3, col5 from t where (t.servid = 5 or t.servid = 90) and t.channel = 1; --效率低
select col3, col5 from t where t.servid = 5 and t.channel = 1
union
select col3, col5 from t where t.servid = 90 and t.channel = 1; ? --效率高
3. 條件中盡量避免給有索引的字段進行不等操作(!=或<>),因為一旦進行不等操作,oracle將不會走現(xiàn)有的索引轉而走全表掃描.
4. 返回結果使用具體的列名來代替星號(*),因為oracle 遇到星號 需要另外去查對應的字典從而把所有的列名給轉換出來,這導致了需要消耗時間.
5. 訪問數(shù)據(jù)表的次數(shù)盡量少
6. 使用exists 來代替in , not exists 代替not in 來提交數(shù)據(jù)的訪問速度
7. 不要對有索引的字段進行函數(shù)操作或者運算操作,不然會導致這些字段不會走索引
select col1 from t where (t.servid * 2) = 10; --效率低下
select col1 from t where t.servid = 10/ 2; --效率高
8. 當需要刪除大量數(shù)據(jù)的時候,如果條件允許的話,可以使用truncate 來代替delete. 因為delete 時數(shù)據(jù)庫時需要維護一系列的信息以便進行回滾等操作,而truncate 則不是要額外維護這些信息,直接高效清空數(shù)據(jù)庫表.
9. 如果表有分區(qū)的話,盡量使用分區(qū)進行查詢.因為同類的數(shù)據(jù)都集中在一個分區(qū)(數(shù)據(jù)塊)里面,查詢是不需要跨塊查詢,從而效率較高
10. ?比較大小時盡量使用等于號來提高效率
select col1 from t where t.servid > 60; --效率比較低下,因為dml會直接跳到60這個記錄上,然后在往前掃大于60的記錄,額外的動作,額外的消耗.
select col1 from t where t.servid >= 61; --效率高
11. 判斷是否存在某條記錄時,使用exists 來提高效率,它可以避免進行全表掃描
select count(*) from t where t.name = 'Gavin'; ?--效率低下,進行全表掃描
select count(1) from dual where exists(select null from t where t.name='Gavin'); --效率高
12. ?union all 效率高于union ,因為union 相對于union all 來說多了一個去重的操作
13. 可以使用視圖來加速結果的搜索
14. 如果對有索引的字段進行模糊搜索時,盡量使用單右邊模糊匹配查詢,這樣就可以使它能有效的使用對應的索引.
select col5 from t where t.name like '%Ga%'; --效率低,不走索引
select col5 from t where t.name like 'Ga%'; ?--效率高,走索引
