SQL優化是數據優化的重要方面，本文將分析Oracle自身的CBO優化，即基于成本的優化方法。Oracle為了自動的優化sql語句需要各種統計數據作為優化基礎。外面會通過sql的追蹤來分析sql的執行過程，消耗的資源信息。對于數據庫的性能問題往往是在系統部署一段時間之后出現的，即大量用戶開始使用該系統，系統的數據處理量和各種計算復雜性增加的時候，這個時候往往會追溯到系統的初始設計階段，所以我們還是要在編碼階段就編寫高效的sql語句。我在網上看到了很多關于sql優化的文章，但是不盡人意，有的很籠統的描述有的根本還是錯誤的方法，所以我重新將我的學習過程分享出來。
一、SQL查詢處理過程詳解
查詢處理與查詢優化是兩個相關聯的概念，查詢處理時執行SQL語句獲取數據的過程，而查詢優化是通過分析SQL語句以及其他資源獲得最佳執行計劃的過程。在這里最佳的執行計劃。我指的是消耗資源最少的計劃，例如包含有數據庫服務器的CPU和系統I/O。一條SQL 的執行分為3個階段：語法分析階段、語句優化階段、查詢執行階段。
1.1 語法分析階段
語法分析是在SGA中完成的，（SGA是指系統全局區，包括數據庫緩沖區、重做日志緩沖區、共享池、java池、大池、流池），在這里將sql語句分解為關系代數查詢，也就是通過這些關系代數查詢來驗證這個sql的語法有沒有寫錯，關鍵字是否正確等。
1.2 語句優化階段
這是這3個步驟中最關鍵的一個地方了，oracle默認使用的是基于CBO來選擇最好的執行計劃，你可能會問，啥是CBO？，好吧！CBO其實就是基于成本的優化程序，也就是會將對成本消耗評估，將消耗的cpu執行周期、內存、I/O速率等資源轉換為時間成本。時間最少的當然就是最好的了。例如Oracle的解析也分為硬解析和軟解析， 對于不同的oracle版本，硬解析的次數也不同，在oracle12中，硬解析的次數為19次，在oracle11g中硬解析的次數為59次。
在做這個階段，Oracle會將語法分析樹轉換為一個邏輯查詢，然后將邏輯查詢轉換為物理查詢計劃。而且這個物理查詢計劃還不止一種，因為優化器往往會生成好幾個有效的查詢計劃，然后會根據這些計劃來做出成本消耗評估。注意，這里只是做義工評估，并沒有把每一種計劃都去執行一遍。那么oracle是依據什么來評估的呢？一般會按照如下因素進行評估：a、查詢中涉及的連接操作以及連接順序 b、操作執行的算法 c、數據讀取的方式，例如讀內存還是磁盤 d、查詢各操作之間的數據傳遞方式。
一條sql語句進來，到最終對sql語句生成執行計劃之前，需要經歷一個過程，如下圖所示（嗨呀，隨手畫的圖， 畫得比較丑呀！）
1.3 查詢執行
查詢執行時最簡單的一個步驟了，只需要將剛才步驟2的物理查詢計劃進行執行即可，然后將處理的數據返回給用戶。
二、基于成本的優化
2.1 優化方式
優化方式的含義是為滿足SQL優化的目標而選擇的優化方式，在默認情況下，是以SQL語句的吞吐量作為優化的目標。
下面提供三種優化方式來滿足不同的查詢需求：
1、All_Rows：默認方式，優化的目標是實現查詢的最大吞吐量
2、FIRST_ROWS_n：優化輸出查詢的前n行數據，目標是滿足快速的響應需求
3、FIRST_ROWS：使用CBO的成本優化盡快輸出查詢的前幾行數據，滿足最小響應時間的需求
oracle提供了三種級別上的優化：實例級、會話級、語句級。
查詢當前數據庫的CBO優化方式：
可以看出我當前的數據庫的優化方式是實現查詢的最大吞吐量。
2.2 優化器工作過程
CBO通過4個步驟步驟完成SQL的優化
1、根據統計數據轉換SQL語句 ： 也就是指CBO認為轉換后的語句查詢會更高效，所以將你的sql語句轉換為另外一種形式，例如你寫的OR轉換為 UNION ALL，將between轉換為>=和<=等。
2、根據資源情況選訪問路徑：指訪問某個路徑的數據所消耗的資源。
3、根據統計數據選擇連接方法： 如果涉及多個表，CBO會根據統計數據以及表的鍵的信息來選擇連接的方法，在多個連接方法中選擇計算成本最低的一個作為最佳連接方法。
4、確定連接次序：指涉及的數據行的數目來確定最好的連接次序。
2.3 統計數據
--查看gather_stats_job的當前運行狀態
--查詢用戶scott擁有表的統計分析情況：sample_size表示采樣行數
select last_analyzed，table_name，owner，num_rows，sample_size from dba_tables where owner='SCOTT'；
--為模式scott的所有表統計數據(手工收集)
execute dbms_stats.gather_schema_stats(ownname => 'scott')；
三、主動優化SQL語句
3.1 優化查詢
1、優化查詢：explain，對于使用索引查詢，使用like的時候只有%不在第一個位置才會有效，使用多列查詢的時候，只有查詢條件中使用了這些字段中的第一個字段時，索引才會被引用，or查詢條件時，前后兩個條件中的列都是索引時，查詢中才會使用索引。
2、優化數據庫結構，將字段很多的表分解為多個表，增加中間表，增加冗余字段，優化插入速度，禁用唯一性檢查，使用批量插入，禁止外鍵檢查，禁止自動提交，優化表optimize
3、優化數據庫的服務器，硬件：內存，io， 優化參數。
4、使用綁定變量：我們都知道，在Oracle中是分為了硬解析和軟解析的，在SGA中，共享池就是存放解析后的SQL語句，此時的共享池包含SQL語句的最終執行計劃。如果有相同的是SQL查詢語句，就不需要再次解析SQL語句了，而是直接從共享池中執行SQL語句的執行計劃。使用共享池就是為了避免硬解析的發生，因為每次去進行硬解析的時候都需要重新去分析語句的語法語義，然后通過CBO優化生成的最終執行計劃，這樣就很消耗CPU的資源。使用綁定變量，也就是我們在java開發中常見的給一個sql語句加一個？來執行，然后再傳入參數。
例如： select ename，job，sal from scott.emp where deptno=？
然后我們再把參數傳入，這樣不僅可以防止SQL注入，而且可以對SQL進行優化。
5、消除子查詢：對于一些嵌套的子查詢，將嵌套的sql語句，例如：
這樣的一條sql語句每次需要執行N*M次操作，具體數值你可以使用下文中是sql跟蹤進行性能分析。
優化后的語句為：
優化后的這條sql只需要進行N+M此操作即可，其伸縮性更強，計算結果也不會呈指數增長。雖然初步看起來優化后的sql語句似乎更長一點，如果你在質疑到底對不對，你可以使用我們接下來講到的SQL語句分析工具來進行對比，大家可以通過其執行計劃來驗證。
3.2 SQL語句優化工具
使用explain plan for 指令來獲得SQL語句的執行計劃，所以我們先來創建一個執行這個指令所需要的表，在oracle的安裝目錄中，我們需要找到utlxplan.sql這個文件，然后執行。我這里的這個文件的路徑位于E：\oracle\app\product\11.2.0\dbhome_1\RDBMS\ADMIN\utlxplan.sql，執行命令如下：
表已創建
查看這個表結構：
然后我們通過這個命令來分析SQL語句的執行：
SQL> explain plan for
2 select count(*) from scott.emp；
Explained
我們來查看一下plan_table表中的sql語句執行計劃信息：
我們可以看到，這是一個全表掃描的，表明是emp。
如果我們想要更深入的對這條sql進行分析怎么辦，例如想要知道這個的訪問對象、消耗的CPU等信息。那么我們可以啟用SQL追蹤。
1、使用autotrace指令
使用該指令可以跟蹤SQL語句并分析其執行步驟，統計信息如物理讀數據量、磁盤和內存排序數據量。
具體的操作命令如下：
來看一下這個生成好的文件（部分內容，因為生成的內容比較多，所以這里不完全貼上來，需要查看的朋友可以自己去執行一個sql追蹤然后查看）：
在這段輸出中，可以看出，SQL語句被執行了38次，總共耗時0.01秒，語句被執行了48次，話費時間是0.17秒，在解析和執行期間沒有磁盤I/O和緩沖區讀取操作，fetch操作執行了70次，耗時0.09秒，涉及了9次磁盤讀取以及171次緩沖區讀取操作，總共讀取了0個數據庫塊，涉及50行數據。
在庫緩存中丟失的命中次數是22次，說明有22次硬解析出現。最后說明是47個用戶SQL語句，42個內部SQL語句總共涉及89個SQL語句。
四、被動優化SQL
在程序打包后，或者系統運行后如何來優化SQL語句，一般就是建立或刪除索引、建立分區表等操作，下面指給出一些思路，具體的實現還是需要在實際工作中才能領會。
1、使用分區表
2、創建壓縮表：原理就是，將表中重復的數據去掉，采用算法來替換這些重復的值，在需要的時候，用算法去重建這些重復的數據，從而實現對表的壓縮。
語句為；
3、創建壓縮索引：原理同壓縮表，主要就是去掉索引中的重復值，尤其對于大表，可以減少存儲空間并增強查詢性能。
語句為：
4、保持CBO的穩定性，創建存儲大綱，分為三種； 數據庫級別的存儲大綱、會話級別的存儲大綱、SQL語句級別的存儲大綱
5、使用V$SQL視圖
例如可以查詢消耗磁盤I/O最多的語句，緩沖區讀取次數最多的SQL語句等。
--查詢自實例啟動以來磁盤IO最多的sql語句
五、索引類型及使用時機
說到數據庫的優化，不得不提的就是索引了，下面詳細來講解一下oracle的索引類型及其使用時機。
1、B-樹索引
B-樹索引是Oracle默認的索引類型。葉子節點包含索引的實際值和該索引條目的行ID。分為根節點、分支節點、葉子節點3個部分，其中根節點位于索引的最頂端。在葉子節點中存儲了實際的索引列的值和該列對應的記錄的行ID，它是唯一的Oracle指針，指向該行的物理位置，葉子節點其實就是一個雙向鏈表，每個葉子節點包含一個指向下一個和上一個葉子節點的指針，這樣在一定范圍內便利用索引以搜索需要的記錄。
2、位圖索引
位圖索引使用位圖標識索引的列值，它適用于沒有大量數據更新、刪除和插入操作的數據倉庫。因為使用位圖索引時，每個位圖索引項與表中大量的行有關聯，當表中有大量的增刪改操作的時候，位圖索引頁需要相應的改變，而且索引會占用一定的磁盤空間，并且索引在更新的時候受影響的索引行需要鎖定。
例如我們執行如下語句：
SELECT EMPNO，ENAME，job，SAL FROM scott.emp WHERE JOB='SALESMAN'；
目的就是在emp中查出職位為salesman的員工信息，這里我們為其建立位圖索引，結構如下圖所示（純手工繪圖）：
創建位圖索引的語句為：
create bitmap index emp_job_bitmap_idx on emp(job)；
3、反向鍵索引
是值在創建索引過程中對索引列創建的索引鍵值的字節反向，使用反向鍵索引的好處是將值連續插入到索引中時反向鍵能避免爭用。使用反向鍵索引使得每個鍵值被顛倒了順序，將索引的鍵值分散開。
例如：
46892 ----> 29864
Horoscope ---> eposcoroH
創建反向鍵索引需要使用reverse關鍵字。
create index emp_sal_reverse_idx on emp(sal) reverse；
4、基于函數的索引
用戶查詢時，如果查詢語句的where子句中有函數存在，oracle將使用函數索引加快查詢速度。
create index dept_dname_idx on dept9UPPER（dname)）；
如上所示，我們創建了一個基于表dept中列dname的函數索引，創建該索引時首先將列dname中的值轉換為大寫，然后對大寫的dname創建索引，放入索引表。資源當用戶需要進行如下查詢的時候就會極大的提高查詢速度。
select UPPER(dname) from scott.dept where UPPER(dname) ='SALES'；
六、SGA詳解
Oracle的SGA是指系統全局區，它包括數據庫緩沖區、重做日志緩沖區、共享池、java池、大池、流池。要優化SGA就是要調整這些數據庫組件的參數 ，這些組件就是實例優化的操作對象，從而提高系統的運行效率，如提高用戶查詢的響應事件等。
數據庫緩沖區：存放用戶從庫中讀取的數據，用戶查找數據會先在這里進行查找，如果沒有才會去讀數據庫文件，所以該區域的設置不能過小。
重做日志緩沖區：這里放置用戶改變的數據，所有變化了的數據和需要回滾的數據都暫時保存在這里。
共享池：包括數據字典高速緩存和庫高速緩存，庫高速緩存存放oracle解析的SQL語句、PL/SQL過程、包以及各種控制結構，如表、庫緩沖句柄等。
java池：執行java代碼的區域，是為運行JVM分配的一段固定大小的內存。
大池：該內存區提供大型的內存分配，在共享服務器連接模式下提供會話區，在使用RMAN備份是也使用該內存區作為磁盤IO的數據緩沖區。
流池：流內存，為oracle流專用的內存池，流是指oracle數據庫中的一個數據共享。
對于數據庫的優化是一個很深入的內容了，例如還有可以優化重做日志緩沖區、優化共享池優化PGA內存等方面的內容，
日志緩沖區中將緩沖寫入到日志文件中的方式有每隔3秒提交、數據大于1MB的時候、檢驗點發生時、當DBWR進程將數據庫高速緩沖區中的數據寫到數據文件前，日志緩沖區的優化就是調整log_buffer_pace或者將不同的文件放在不同的磁盤上以避免沖突。
PGA是一個程序全局區，可以作為大規模的數據排序，而不需要去使用虛擬內存而占用操作系統的交換區。
更為詳細的內容在本文就不再說明，感興趣的朋友可以自行查閱相關資料。學習一些SQL的底層，可以更好的修煉內功。
核心關注：拓步ERP系統平臺是覆蓋了眾多的業務領域、行業應用，蘊涵了豐富的ERP管理思想，集成了ERP軟件業務管理理念，功能涉及供應鏈、成本、制造、CRM、HR等眾多業務領域的管理，全面涵蓋了企業關注ERP管理系統的核心領域，是眾多中小企業信息化建設首選的ERP管理軟件信賴品牌。
轉載請注明出處：拓步ERP資訊網http://www.guhuozai8.cn/
相關文章
