• <xmp id="44wea"><menu id="44wea"></menu>
    <nav id="44wea"><strong id="44wea"></strong></nav><nav id="44wea"><code id="44wea"></code></nav>
  • <menu id="44wea"><u id="44wea"></u></menu>

    SQL語句優化提高數據庫性能

    發布于: 2016-06-06    瀏覽: 8725    作者:王佳林

    為了獲得穩定的執行性能,SQL語句越簡單越好。對復雜的SQL語句,要設法對之進行簡化,本文給大家介紹優化SQL語句提高數據庫性能。


    現在數據越來越復雜和龐大,很多時候影響程序運行性能不理想的原因中除了一部分是因為應用程序的負載確實超過了服務器的實際處理能力外,更多的是因為系統存在大量的SQL語句需要優化。

    一、問題的提出

    在項目實際使用中,數據是一個長期累計的過程,隨著數據庫中數據的增加,系統的響應速度就成為目前系統需要解決的最主要的問題之一。系統優化中一個很重要的方面就是SQL語句的優化。對于海量數據,劣質SQL語句和優質SQL語句之間的速度差別可以達到成千上百倍,因此高質量的SQL語句,更能提高系統的可用性。

    二、SQL語句編寫注意問題

    下面就某些SQL語句的where子句編寫中需要注意的問題作詳細介紹。在這些where子句中,即使某些列存在索引,但是由于編寫了劣質的SQL,系統在運行該SQL語句時也不能使用該索引,而同樣使用全表掃描,這就造成了響應速度的極大降低。

    1. 操作符優化

    (a) IN 操作符

     在使用中盡量用EXISTS替代IN、用NOT EXISTS替代NOT IN  。

    在許多基于基礎表的查詢中,為了滿足一個條件,往往需要對另一個表進行聯接。在這種情況下, 使用EXISTS(NOT EXISTS)通常將提高查詢的效率。。在子查詢中,NOT IN子句將執行一個內部的排序和合并。 無論在哪種情況下,NOT IN都是最低效的 (因為它對子查詢中的表執行了一個全表遍歷)。。為了避免使用NOT IN ,我們可以把它改寫成外連接(Outer Joins)NOT EXISTS。

    例子: 
    (推薦)select* from dt_article where exists(select id from dt_article_category wheredt_article_category。id=dt_article。category_id andtitle='公司新聞')
    (不推薦)select* from dt_article where category_id in (select id from dt_article_categorywhere title='公司新聞')

     

    (b) IS NULL IS NOT NULL操作(判斷字段是否為空)

    判斷字段是否為空一般是不會應用索引的,因為索引是不索引空值的。不能用null作索引,任何包含null值的列都將不會被包含在索引中。即使索引有多列這樣的情況下,只要這些列中有一列含有null,該列就會從索引中排除。也就是說如果某列存在空值,即使對該列建索引也不會提高性能。任何在where子句中使用is null或is not null的語句優化器是不允許使用索引的。 

        例子:

    (推薦)select* from dt_article where title>'';
     (不推薦)select* from dt_article where title is null;

    (c) > < 操作符(大于或小于操作符)

    (推薦)select * from dt_article where id>=101;

    (不推薦)select * from dt_article where id>100;

    兩者的區別在于, 前者將直接跳到第一個id等于101的記錄而后者將首先定位到id=100的記錄并且向前掃描到第一個id大于100的記錄。

    (d)LIKE操作符

    LIKE操作符可以應用通配符查詢,里面的通配符組合可能達到幾乎是任意的查詢,但是如果用得不好則會產生性能上的問題,如like '%福瑞希%'這種查詢不會引用索引,而like'福瑞希%'則會引用范圍索引。

    一個實際例子:用dt_article表中內容可來查詢, content like'%福瑞希%'這個條件會產生全表掃描,如果改成contentlike '福瑞希%'則會利用content的索引進行范圍的查詢,性能肯定大大提高。

    在很多情況下可能無法避免這種情況,但是一定要心中有底,通配符如此使用會降低查詢速度。然而當通配符出現在字符串其他位置時,優化器就能利用索引。

    (e) UNION操作符

    當SQL語句需要UNION兩個查詢結果集合時,這兩個結果集合會以UNION-ALL的方式被合并, 然后在輸出最終結果前進行去重和排序。 假如用UNION ALL替代UNION, 這樣排序就不是必要了。 效率就會因此得到提高。 需要注重的是,UNION ALL 將重復輸出兩個結果集合中相同記錄。 因此各位還是要從業務需求分析使用UNIONALL的可行性。 UNION 將對結果集合去重排序,這個操作會使用到SORT_AREA_SIZE這塊內存。 對于這塊內存的優化也是相當重要的。

    (f) NOT

    我們要避免在索引列上使用NOT, NOT會產生在和在索引列上使用函數相同的影響。 當查詢列碰到”NOT,他就會停止使用索引轉而執行全表掃描。

    (g) OR

        通常情況下, 用UNION替換WHERE子句中的OR將會起到較好的效果。 對索引列使用OR將造成全表掃描。 注重, 以上規則只針對多個索引列有效。 假如有column沒有被索引, 查詢效率可能會因為你沒有選擇OR而降低。 在下面的例子中, title和category_id上都建有索引。

    (推薦)select * from dt_article where title='清洗空氣' union all select * from dt_article where category_id=92

    (不推薦)select * from dt_article where title='清洗空氣' or category_id=92 假如你堅持要用OR, 那就需要返回記錄最少的索引列寫在最前面。 
           另外在一些情況下,也可以使用IN來替代OR,     這是一條簡單易記的規則,但是實際的執行效果還須檢驗。

    (推薦)select * from dt_article where category_id in (89,92)

    (不推薦)select * from dt_article where category_id=92 or category_id=89

    (h) DISTINCT

         當提交一個包含一對多表信息的查詢時,避免在SELECT子句中使用DISTINCT。 一般可以考慮用EXIST替換, EXISTS 使查詢更為迅速,因為RDBMS核心模塊將在子查詢的條件一旦滿足后,馬上返回結果。 

    2. SQL書寫的影響

     (a) WHERE后面的條件順序影響

    WHERE子句后面的條件順序對大數據量表的查詢會產生直接的影響。如:

    select * from dt_article where category_id=92 and is_hot=1
    select * from dt_article where is_hot=1 and category_id=92 

    以上兩個SQL中category_id(電壓等級)及is_hot(銷戶標志)兩個字段都沒進行索引,所以執行的時候都是全表掃描,第一條SQL的is_hot=1在記錄集內比率為99%,而category_id=92的比率只為1%,在進行第一條SQL的時候99%條記錄都進行category_id及is_hot的比較,而在進行第二條SQL的時候1%條記錄都進行category_id及is_hot的比較,以此可以得出第二條SQL的CPU占用率明顯比第一條低。

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

    3. 更多方面SQL優化資料分享

    (1) 選擇最有效率的表名順序(只在基于規則的優化器中有效):

    ORACLE 的解析器按照從右到左的順序處理FROM子句中的表名,FROM子句中寫在最后的表(基礎表 driving table)將被最先處理,在FROM子句中包含多個表的情況下,你必須選擇記錄條數最少的表作為基礎表。如果有3個以上的表連接查詢, 那就需要選擇交叉表(intersectiontable)作為基礎表, 交叉表是指那個被其他表所引用的表.

    (2) SELECT子句中避免使用 ‘ * ‘:

    ORACLE在解析的過程中, 會將'*' 依次轉換成所有的列名, 這個工作是通過查詢數據字典完成的, 這意味著將耗費更多的時間。

    (3) 減少訪問數據庫的次數:

    ORACLE在內部執行了許多工作: 解析SQL語句, 估算索引的利用率, 綁定變量 , 讀數據塊等。

    (4) 整合簡單,無關聯的數據庫訪問:

    如果你有幾個簡單的數據庫查詢語句,你可以把它們整合到一個查詢中(即使它們之間沒有關系) 。

    (5) 用TRUNCATE替代DELETE:

    當刪除表中的記錄時,在通常情況下, 回滾段(rollbacksegments ) 用來存放可以被恢復的信息. 如果你沒有COMMIT事務,ORACLE會將數據恢復到刪除之前的狀態(準確地說是恢復到執行刪除命令之前的狀況) 而當運用TRUNCATE時, 回滾段不再存放任何可被恢復的信息.當命令運行后,數據不能被恢復.因此很少的資源被調用,執行時間也會很短. (譯者按: TRUNCATE只在刪除全表適用,TRUNCATE是DDL不是DML) 。

    (6) 盡量多使用COMMIT:

    只要有可能,在程序中盡量多使用COMMIT, 這樣程序的性能得到提高,需求也會因為COMMIT所釋放的資源而減少,COMMIT所釋放的資源:

    a. 回滾段上用于恢復數據的信息.
    b. 被程序語句獲得的鎖
    c. redo log buffer 中的空間

    (7) 通過內部函數提高SQL效率:

    復雜的SQL往往犧牲了執行效率. 能夠掌握上面的運用函數解決問題的方法在實際工作中是非常有意義的。

    (8) 使用表的別名(Alias):

    當在SQL語句中連接多個表時, 請使用表的別名并把別名前綴于每個Column上.這樣一來,就可以減少解析的時間并減少那些由Column歧義引起的語法錯誤。

    (9) 總是使用索引的第一個列:

    如果索引是建立在多個列上, 只有在它的第一個列(leading column)被where子句引用時,優化器才會選擇使用該索引. 這也是一條簡單而重要的規則,當僅引用索引的第二個列時,優化器使用了全表掃描而忽略了索引。

    (10) 避免使用耗費資源的操作:

    帶有DISTINCT,UNION,MINUS,INTERSECT,ORDER BY的SQL語句會啟動SQL引擎執行耗費資源的排序(SORT)功能. DISTINCT需要一次排序操作, 而其他的至少需要執行兩次排序. 通常, 帶有UNION, MINUS , INTERSECT的SQL語句都可以用其他方式重寫. 如果你的數據庫的SORT_AREA_SIZE調配得好, 使用UNION , MINUS, INTERSECT也是可以考慮的, 畢竟它們的可讀性很強。

    在線客服

    售前咨詢

    售后服務

    投訴/建議

    服務熱線
    0731-82091505
    18874148081
    欧美中日韩免费观看网站,波多野结衣办公室双飞,无码专区人妻丝袜,免费理论片高清在线观看 陇西县| 合山市| 正宁县| 松江区| 饶阳县| 扬州市| 云南省| 安化县| 乌拉特后旗| 乌兰察布市| 清河县| 甘洛县| 龙州县| 隆德县| 拜泉县| 吐鲁番市| 大冶市| 合山市| 灵武市| 酉阳| 达尔| 乌拉特中旗| 望奎县| 金乡县| 闽清县| 顺昌县| 怀来县| 陆良县| 佳木斯市| 察雅县| 井陉县| 靖宇县| 周口市| 石阡县| 龙井市| 娄底市| 乐业县| 名山县| 马关县| 开江县| 靖安县| http://444 http://444 http://444 http://444 http://444 http://444