????上周給南京某客戶一個重要業務系統的數據庫做優化，能實施的馬上做了實施，優化前后性能對比非常明顯，系統最為嚴重的IO負載過重問題基本得到解決：優化前一天的物理讀是48億次，優化后是15億次，效果那是剛剛的，業務處理效率明顯改善，磁盤的壽命也會有大幅提升。?這些還只是優化建議的一部分。

????如果開發人員能把改SQL和業務邏輯的優化建議都完成，一天物理讀少于5億次應該也是完全沒問題的。如果不知道優化能夠帶來如此巨大的提升，可能業務部門會考慮花個上百萬換個閃存存儲都有可能，實際上當前的普通存儲對業務要求來說已經綽綽有余。?

? ? 當前的性能提升主要是通過調整索引完成的，創建了11個索引。同時還做了調整數據庫參數，把optimizer_index_cost_adj參數由20改回默認的100；再有通過hint+sql profile，讓sql選擇高效執行計劃。這些事情DBA可以搞定。但是，如果SQL寫的不好，就只能改代碼，這個周期可能就比較長了。下面就列舉其中的幾種情況：

1、部分sql沒有使用綁定變量，每秒硬解析次數將近200。這是一個超標很多的數值(硬解析小于10次/秒的系統，基本還算OK)。合理使用綁定變量是OLTP系統的一個最最基本的要求，但是目前還是有很多系統沒有使用綁定變量，只能說目前我們國內的很多開發團隊的數據庫開發水平還很初級。有些公司會要求DBA要有OCP、OCM等資質才能上崗，但是對開發人員卻非常寬容，只要功能實現了就算萬事大吉，如果經過一些簡單的培訓，這種不使用綁定變量的低級問題應該不會出現。?這個客戶的另一套數據庫在某個時段的硬解析接近1000次/秒，CPU使用率接近100%，當DBA告知開發人員這是因為沒有使用綁定變量的原因，開發人員居然振振有詞的說以前沒有出現類似問題，這就是典型的無知者無畏。

? ? 不使用綁定變量的一個無奈的解決辦法是改數據庫參數，將cursor_sharing有默認的exact改成force。這個改動非常不建議，很有可能引起其他性能問題，有很多bug與之相關。

2、這是一個非常低級而且普遍存在的寫法問題，對一個日期字段做to_char：where to_char(starttime,'yyyy-mm-dd')='20200110'。這個starttime字段上，已經存在一個單字段索引，還有一個to_char(starttime,'yyyymmdd')函數索引。但是上面的sql寫法，如果不改寫，就只能再創建一個to_char(starttime,'yyyy-mm-dd')的函數索引。如果開發人員又寫出 to_char(starttime,'yyyy/mm/dd')的sql呢？是不是還得再創建一個函數索引？這種sql的存在，說明開發團隊還不知道什么是開發規范和SQL審核。

如果你是開發人員，上面兩個寫法都命中了你(可能有的開發人員仍不知綁定變量寫法是個什么概念，建議馬上百度一下)，那么只能說你的數據庫開發水平還有非常巨大的進步空間。

3、select xxseq.nextval from (select 1 from all_objects where rownum<=10) ; 這個sql是借助all_objects視圖，生成10個sequence序列值。?這個寫法在功能是沒問題，但是sql頻繁的執行，大量all_objects視圖的調用，把這些本來對資源消耗可以忽略不計的sql，變成了top cpu SQL。?建議改成select xxseq.nextval from dual connect by level<=10;

4、select? ...... from xxtab where?name?like '%這是一個字段內容的完整值%';? ?SQL(簡化版)涉及的表比較大，消耗了大量的IO資源。?隨便選擇幾個兩個百分號中間的內容到表里按等值查詢，都能查得到，說明兩個百分號是可以去掉的，同時like 可以改成=。如果業務上線初期表只有幾千或是幾萬條記錄，這個sql消耗的資源也很少，但是隨著時間的增長，表的數據量達到千萬或上億，你這樣一個SQL就能把大部分IO資源都消耗掉。?凡是使用兩個百分號模糊查詢為主要過濾條件的，多從業務角度多考慮一下，盡量避免在大表上使用。

5、不明所以，照貓畫出狗(不是虎)的分頁寫法(xxtab這個表很大，sql全表掃描，消耗了大量IO資源)：

SELECT * FROM?

( SELECT A.*, ROWNUM RN

? ? FROM ( SELECT * FROM xxtab) A

) WHERE RN >= 1

? and rownum<= 3000? and ((type = 'typename')?and (state = 0));

這個分頁寫法不知道是從哪里學來的，錯的離譜。type和state兩個字段上的索引根本用不上，只能使用全表掃描的執行計劃。正常的寫法應該是：

SELECT A.*, ROWNUM RN

FROM?

(SELECT * FROM xxtab

?where rownum<= 3000? and ((type = 'typename')? and (state = 0))

) A;???這種寫法大表就能用上索引了，IO消耗也會大幅減少。

6、select ... from xxtab where (:b1 is null or col1=:b1) and (:b2 is null or col2=:b2)?;??這種寫法，可能來源于某些搜索需求，兩個輸入框，不管有沒有輸入查詢條件，都可以使用上面的sql。寫法簡單了，效率卻差了很多：不管b1、b2是否有輸入，col1和col2字段上即使有索引，也都只能選擇全表掃描。?這個在業務上可以要求必須有一個輸入條件，那么sql就可以根據不同的輸入，生成不同的內容，比如b1有輸入，b2無輸入：select ?.... from xxtab where col1=b1;??兩個都有輸入，對應sql是select ?... from xxtab where col1=:b1 and col2=:b2;?這樣才能高效的使用索引。

7、用rownum分頁寫法取數：

SELECT *

FROM ( SELECT t.*, ROWNUM RN

? ? FROM ( select t1.*,ROWNUM

? ? ? ? from ( select *

? ? ? ? ? ? from? ? ? ?p

? ? ? ? ? ? left join? c? ?on c.objectid = p.objectid

? ? ? ? ? ? left join? o? ?on o.objectid = p.objectid

? ? ? ? ? ? left join? e? ?on e.objectid = o.objectid

? ? ? ? ? ) t1

? ? ? ) t? ?WHERE ROWNUM < 1500000

? ) WHERE RN >= 1200000;

????這個分頁sql的框架完全正確，滿分。但是使用場景卻是嚴重不合適的：p表7700萬記錄(o表也很大)，4表left join沒有謂詞條件，主查詢結果集應該也至少是7700萬，每次取30萬(1500000-1200000)條，要執行250多次，執行計劃都是全表掃描，需要兩個大表做250多次全表掃描。如果這些sql是串行執行，那么還有一個邏輯問題，就是這些表的數據都是動態變化的，用rownum分頁這種方法，一定會造成漏取或重復取數。其實這個sql的最佳做法就是不加分頁，一次完成。表掃描只需要一次，而且也不會出現漏取或重復取數的問題。

總結：

? ? SQL優化對大部分系統來說，都能帶來非常大的性能提升。很多問題靠更換高級硬件是無法解決的，比如一個大表全表掃描，現有硬件條件執行時間10分鐘，更換硬件后執行時間1分鐘。而通過優化SQL，建個合適的索引，在不更換硬件的情況下，可能用不到10毫秒就能出結果。

? ?類似的sql寫法問題可能還有很多，這次只是挑選了其中一部分top sql做分享。SQL寫法靈活多變，性能好的SQL都是符合規范的，性能差的SQL會暴露你的開發水平。

總結

以上是生活随笔為你收集整理的硬解析优化_72最近一次现场生产系统优化的成果与开发建议的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。