SQL優化二(SQL性能調優)

• RBO(rule-base optimizer):優化器遵循Oracle內部預定的規則,句法驅動和數據字典驅動。
• CBO(cost-based optimizer):依據語句執行的代價，主要指對CPU和記憶體的占用，優化器在判斷是否使用CBO時，要參照表和索引的統計信息統計表驅動，統計信息要在對錶做analyze後才會有。

• Choose：預設模式。根據表或索引的統計信息，如果有統計信息，則使用CBO方式；如果沒有統計信息，相應列有索引，則使用RBO方式。
• Rule：基於規則優化，忽略任何統計信息
• First rows：與Choose類似。不同的是如果表有統計信息，它將以最快的方式返回查詢結果，以獲得最佳響應時間。
• First_rows_n：與Choose類似。不同的是如果表有統計信息，它將以最快的方式返回查詢的前幾行，以獲得最佳響應時間。
• All rows：完全基於CBO的模式。當一個表有統計信息時，以最快方式返回表所有行，以獲得最大吞吐量。沒有統計信息則使用RBO方式。

• Instance級:修改啟動參數在init<SID>.ora文件中設定OPTIMIZER_MODE，需要資料庫重啟
• Session級:（JDBC或者Hibernate或者一次連接），通過alter session set optimizer_mode = value修改，忽略instance級
• Statement級:通過在SQL語句中加如Hint(隱語)實現,表明對語句塊選擇基於開銷的優化方法,並獲得最佳響應時間,忽略instance級和session級

               eg:SELECT /*+ALL_ROWS*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT';  ……來設定

ROWID記錄了記錄行所在的數據文件、數據塊以及行在該塊中的位置，所以通過ROWID來存取數據可以快速定位到目標數據上，可以說是整個資料庫都在用的索引，是Oracle存取單行數據的最快方法。這種存取方法不會用到多塊讀操作，一次I/O只能讀取一個數據塊。

select empno from emp where rowid='AAAR3sAAEAAAACXAAA';

索引唯一掃描(index unique scan)

通過唯一索引查找一個數值經常返回單個ROWID,如果存在UNIQUE 或PRIMARY KEY 約束（約束只有一行記錄匹配），Oracle實現索引唯一性掃描。

select empno from emp where empno=7369;

索引範圍掃描(index range scan)

使用一個索引存取多行數據，在唯一索引上使用索引範圍掃描的典型情況下是在謂詞(where限制條件)中使用了範圍操作符，有以下三種情況會導致引起索引範圍掃描:

• 在唯一索引列上使用了range操作符(> < <> >= <= between)
• 在組合索引上，只使用部分列進行查詢，導致查詢出多行
• 對非唯一索引列上進行的任何查詢。

select empno from emp where empno>7369;

索引全掃描(index full scan)

什麼時候會引起索引全掃描呢?當不使用謂詞邏輯where;所有查詢結果數據都必須從索引中可以直接得到;需要排序操作，比如order by。

 select empno from emp order by empno;

index full scan使用單塊讀方式有序讀取索引塊，產生db file sequential reads事件，當採用該方式讀取大量索引全掃描，效率低下

索引快速掃描(index fast full scan)

與索引全掃描很相似，只是不涉及排序動作。

select /*+ index_ffs(emp pk_emp) */empno from emp; //對指定的表執行快速全索引掃描,而不是全表掃描的辦法.  

index fast full scan使用多塊讀的方式讀取索引塊，產生db file scattered reads 事件，讀取時高效，但為無序讀取

優化技巧7:對於只從表中查詢出總行數的2%到4%行的表時，可以考慮創建索引。
優化技巧8:不要將那些頻繁修改的列作為索引列，頻繁修改會導致不必要的索引開銷。
優化技巧9:不要使用包含函數或操作符放入WHERE從句中的關鍵字作為索引，會導致索引失效，可以考慮使用函數索引。
優化技巧10:在組合索引中，沒有按照建立時的索引關鍵字順序描述，比如xyz變成了yxz，也會導致索引失效。
優化技巧11:如果在表中要建立索引的一列或多列上使用了函數或表達式，則創建的是基於函數的索引。基於函數的索引預先計算函數或表達式的值，並將結果存儲在索引中。B樹索引和bitmap索引也是函數索引
優化技巧12:排序動作能不做就不做，增加系統開銷的同時還會使快速索引失效。

排序合併連接(Sort Merge Join (SMJ))

select aa.CREATEPERSONNAME, bb.CREATEPERSON
from tbl_comm_commonticket aa, tbl_ybgz_ticket bb
where aa.CREATEPERSONNAME = bb.CREATEPERSON
order by aa.CREATEPERSONNAME, bb.CREATEPERSON

排序屬於代價很高的操作，特別對於大表。因此經常避免使用排序合併連接方法，但是如果2個row source都已經預先排序（比如primary Key索引），則這種連接方法可以選用。

嵌套迴圈(Nested Loops (NL))

分為驅動表(OUTER TABLE)和內層表(INNER TABLE)。因為嵌套迴圈，所以外層迴圈的次數越少越好，因此一般將數據量較小表或滿足條件的row source較小的表作為驅動表(用於外層迴圈)的理論依據。

select /*+USE_NL(emp,dept)*/ * from emp,dept where emp.deptno=dept.deptno;

嵌套迴圈返回已經連接的行，而不必等待所有的連接操作處理完才返回數據 ，所以提高了響應速度。如果OUTER TABLE比較小，並且在INNER TABLE上有唯一索引，或有高選擇性非唯一索引時，使用這種方法可以得到較好的效率。另外，這種連接方式，是在RBO優化器中。

哈希連接(Hash Join)

散列基本原理是：使用一個下標範圍比較大的數組來存儲元素。可以設計一個函數（哈希函數，也叫做散列函數），使得每個元素的關鍵字都與一個函數值（即數組下標，hash值）相對應，於是用這個數組單元來存儲這個元素；但是，不能夠保證每個元素的關鍵字與函數值是一一對應的，因此極有可能出現對於不同的元素，卻計算出了相同的函數值，這樣就產生了“衝突”，換句話說，就是把不同的元素分在了相同的“類”之中。 總的來說，“直接定址”與“解決衝突”是哈希表的兩大特點。

散列連接是CBO 做大數據集連接時的常用方式，優化器使用兩個表中較小的表（或數據源）利用連接鍵在記憶體中建立散列表，然後掃描較大的表並探測散列表，找出與散列表匹配的行。

hash join只有在CBO方式下可以使用；Oracle初始化參數HASH_JOIN_ENABLED決定是否啟用hash join；pga_aggregate_target指定散列連接可用的記憶體大小；儘量使內層表生成的散列表最小，最好能夠全部載入記憶體；主要用於等值連接。

select /*+USE_HASH(emp,dept)*/ * from emp,dept where emp.deptno=dept.deptno;

優化技巧13:避免使用不確定操作符，因為會引起全表掃描; <> ,!=可以等價轉化為 < or > 代替。
優化技巧14:Where子句中出現IS NULL或者IS NOT NULL時，Oracle會停止使用索引而執行全表掃描。可以在設計表時，對索引列設置為NOT NULL。這樣就可以用其他操作來取代判斷NULL的操作。
優化技巧15:當通配符“%”或者“_”作為查詢字元串的第一個字元時，索引不會被使用 。
優化技巧16:對數據類型不同的列進行比較時，會使索引失效。
優化技巧17:UNION操作符會對結果進行篩選，消除重覆，數據量大的情況下可能會引起磁碟排序。如果不需要刪除重覆記錄，應該使用UNION ALL。
優化技巧18:Order By語句中的非索引列會降低性能，可以通過添加索引的方式處理。嚴格控制在Order By語句中使用表達式。
優化技巧19:相同的Sql語句，要保證查詢字元完全相同，大小寫，空格位置，利用shared_pool，防止相同的Sql語句被多次分析，使用變數綁定。
優化技巧20:調整SQL語句的目的是為了在執行中使資源的使用減少到最小。除了選擇使用不同的SQL語法來優化執行代價，還可以通過調整執行順序優化SQL。
優化技巧21:Oracle在執行IN子查詢時，首先執行子查詢，將查詢結果放入臨時表再執行主查詢。而EXIST則是首先檢查主查詢，然後運行子查詢直到找到第一個匹配項。因此NOT EXISTS比NOT IN效率稍高，相應更快。但是(NOT) EXISTS 不等於(NOT) IN。
優化技巧22:可以多使用視圖進行軟解析，視圖只是把你要用的sql進行保存而已，你需要擔心的是視圖中的sql會不會效率太低，而不用擔心視圖的耗時。
優化技巧23:適當的時候強制使用rule會獲得更高效率；調試SQL時關註執行計劃和執行代價。
優化技巧24:避免視圖嵌套使用，尤其是針對視圖排序，篩選等操作。
優化技巧25:不同版本資料庫的執行計劃差別可能很大。
優化技巧26:不是只有select..是查詢，所有的DML操作都含有查詢過程。