一份考驗你DBA水平的sql最佳化文件

大家好！

 

今天和大家分享下sql最佳化的資料，這份資料是2年前公司的培訓資料，今天拿出來和大家分享，考評！

 

今天重溫這份培訓資料，發現漏洞百出，這說明在這2年中我的水平是進步的（：）小誇下自己），在此考驗下大家，如果能發現5處錯誤的地方，說明你是一名"合格"的DBA，如果你發現10處以上錯誤的，說明你是"優秀"的DBA。

 

具體內容如下：

Oracle最佳化器的選擇

一、
Oracle的最佳化器共有3種模式：
RULE (基於規則RBO)、COST(基於成本CBO)、CHOOSE(基於選擇)
 
RBO方式：最佳化器在分析SQL語句時,所遵循的是Oracle內部預定的一些規則。比如我們常見的，當一個where子句中的一列有索引時去走索引。
 
CBO方式：它是看語句的代價(Cost),這裡的代價主要指Cpu和記憶體。最佳化器在判斷是否用這種方式時,主要參照的是表及索引的統計資訊。統計資訊給出表的大小、有少行、每行的長度等資訊。這些統計資訊起初在庫內是沒有的，是做analyze命令後才出現的，很多的時侯過期統計資訊會令最佳化器做出一個錯誤的執行計劃,因此應及時更新這些資訊。
 
Choose：這個是Oracle的預設值。當一個表或索引有統計資訊，則走CBO的方式，如果表或索引沒有統計資訊，表又不是特別的小，而且相應的列有索引時，那麼就走索引，走RBO的方式.

二、
程式中儘量使用繫結變數：避免或減少硬解析
例如：
select * from t1 where t1.a=1;
select * from t1 where t1.a=2;
select * from t1 where t1.a=3;

select * from t1 where t1.a=1000000;
上面這個語句每執行一次就需要在SHARE POOL 硬解析一次，如果一百萬戶萬併發查詢，就要硬解析一百萬次，消耗CPU和記憶體，如果業務量大，很可能導致共享池滿，資料庫當機。
如果繫結變數，則只需要硬解析一次，重複呼叫即可。

三、
Select 中儘量避免使用 "*"
這樣的全欄位訪問，ORACLE在解析的過程中， 會將*依次轉換成所有的列名,這個工作是透過查詢資料字典完成的,這意味著將耗費更多的時間。只提取你所要使用的列；使用別名能夠加快解析速度.

四、
避免使用耗費資源的操作
帶有DISTINCT,UNION,MINUS,INTERSECT,ORDER BY的
SQL語句會啟動SQL引擎執行耗費資源的排序(SORT)功能.
DISTINCT需要一次排序操作, 而其他的至少需要執行兩次
排序.
例如:一個UNION查詢,其中每個查詢都帶有GROUP BY子句
, GROUP BY會觸發嵌入排序(NESTED SORT) ; 這樣, 每個
查詢需要執行一次排序, 然後在執行UNION時, 又一個唯一
排序(SORT UNIQUE)操作被執行,而且它只能在前面的嵌入
排序結束後才能開始執行. 嵌入的排序的深度會大大影響查
詢的效率.
通常, 帶有UNION, MINUS , INTERSECT的SQL語句都可以
用其他方式重寫.

五、
用EXISTS替換DISTINCT
例如:
低效:
SELECT DISTINCT DEPT_NO,DEPT_NAME
FROM DEPT D,EMP E
WHERE D.DEPT_NO = E.DEPT_NO
高效:
SELECT DEPT_NO,DEPT_NAME
FROM DEPT D
WHERE EXISTS ( SELECT ‘X’
FROM EMP E
WHERE E.DEPT_NO = D.DEPT_NO);

六、
用UNION-ALL 替換UNION (允許的話)
UNION-ALL 不去重，效率更高；UNION先以UNION-ALL的方式被合併， 然後進行排序。
例如：
低效：
SELECT ACCT_NUM, BALANCE_AMT
FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE = ’31-DEC-95’
UNION
SELECT ACCT_NUM, BALANCE_AMT
FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE = ’31-DEC-95;
高效:
SELECT ACCT_NUM, BALANCE_AMT
FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE = ’31-DEC-95’
UNION ALL
SELECT ACCT_NUM, BALANCE_AMT
FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE = ’31-DEC-95;

七、
組合索引必須使用先導列
如果索引是建立在多個列上, 只有在它的第一個列(先導列) 被where子句引用時,最佳化器才會選擇使用該索引.
 
例如：
create table multiindexusage ( inda number , indb number , descr varchar2(10));
create index multindex on multiindexusage(inda,indb);
select * from multiindexusage where inda = 1; --走索引
select * from multiindexusage where indb = 1; --不走索引

八、
WHERE條件中正確使用函式或者表示式
sql語句中，where條件中要正確使用函式或者表示式，不正確使用，會進行
欄位型別轉換，欄位上索引失效
例如：
下面語句會使索引失效，並且把資料庫庫中所有記錄的log_time時間值轉換
為字元後，和條件中的字串進行比較，走全表掃描，sql 執行效率大大降
低。
select log_time from account_login where to_char(log_time,'yyyy-mm
-dd hh24:mi:ss')='2009-04-01 00:00:00';
正確寫法，使用to_date函式
select log_time from account_login where log_time=to_date('2009-0
4-01 00:00:00','YYYY-MM-DD HH24:MI:SS');

九、
合理使用函式索引,提升查詢速度
 
在專案的開發中，經常需要對外部傳送如資料庫中的資料，進行處理後，再將它們存放在資料庫中
 
例如：
select account from account_base where account=lower('AbcDe');
在account欄位上建立索引，使用lower函式後，索引失效
在account欄位上建立函式索引lower(account),語句改寫如下：
select account from account_base where lower(account)=lower('AbcDe');
這樣查收走函式索引lower(account)，會大大提升查詢效率

十、
帶萬用字元（%）的like語句
last_name欄位建立索引，下面語句Oracle不使用last_name的索引。
select * from employee where last_name like '%cliton%';
在很多情況下可能無法避免這種情況，但是一定要心中有底
，萬用字元如此使用會降低查詢速度。然而當萬用字元出現在字
符串其他位置時，最佳化器就能利用索引。在下面的查詢中索
引得到了使用：
select * from employee where last_name like 'c%';

十一、
避免在索引列上使用如下運算子
 
避免在索引列上使用NOT、OR、IS NULL、IS NOT NULL、！=、||、+等這樣的關係運算子或連線符；這樣會產生和在索引列上不正確使用函式相同的影響,導致索引失效。
 
如果一定要對使用函式的列啟用索引， 請使用ORACLE的基於函式的索引。

十二、
避免出現索引列自動轉換
當比較不同資料型別的資料時, ORACLE自動對列進行簡單的型別轉換.
例如：EMP_TYPE是一個字元型別的索引列.
SELECT USER_NO,USER_NAME,ADDRESS
FROM USER_FILES
WHERE USER_NO = 109204421
這個語句被ORACLE轉換為:
SELECT USER_NO,USER_NAME,ADDRESS
FROM USER_FILES
WHERE TO_NUMBER(USER_NO) = 109204421
因為內部發生的型別轉換, 這個索引將失效

十三、
減少訪問資料庫的次數
 
當執行每條SQL語句時, ORACLE在內部執行了許多工作: 解析SQL語句, 估算索引的利用率, 繫結變數 , 讀資料塊等等. 由此可見, 減少訪問資料庫的次數 , 就能實際上減少ORACLE的工作量.
 
SQL最佳化的一個重要目標，減少資料庫的邏輯讀次數。

十四、
使用DECODE函式來減少處理時間
 
DECODE函式的語法：DECODE(value,if1,then1,if2,then2,if3,then3,...,else)，表示如果value等於if1時，DECODE函式的結果返回then1,.value等於if2時，返回then2..,如果value不等於任何一個if值，則返回else值。
 
使用DECOD函式可以避免重複掃描相同記錄或重複連線相同表。
 
例如：
低效：
SELECT COUNT(*)，SUM(SAL)
FROM EMP
WHERE DEPT_NO = 0020
AND ENAME LIKE ‘SMITH%’;
SELECT COUNT()SUM(SAL)

十五、
ORACLE解析器的兩個預設解析順序
 
ORACLE的解析器按照從右到左的順序處理FROM子句中的表名，因此FROM子句中寫在最後的表（基礎表）將被最先處理。 在FROM子句中包含多個表的情況下，選擇記錄條數最少的表作為基礎表。（只在基於規則的最佳化器中有效）。
 ORACLE採用自下而上的順序解析WHERE子句，那些可以過濾掉最大數量記錄的條件必須寫在WHERE子句的末尾。
 
 十六、
Order by使用注意事項
 
Order by語句的非索引項或者有計算表示式都將降低查詢速度。
 
Order by語句包含的欄位最好建立索引，避免在order by子句中使用表示式(會使索引失效)

十七、
最佳化GROUP BY
 
提高GROUP BY 語句的效率， 可以透過將不需要的記錄在GROUP BY 之前過濾掉。下面兩個查詢返回相同結果但第二個明顯就快了許多。
低效：
SELECT JOB ， AVG（SAL）
FROM EMP
GROUP by JOB
HAVING JOB = 'PRESIDENT'
OR JOB = 'MANAGER'
高效：
SELECT JOB ， AVG（SAL）
FROM EMP
WHERE JOB = 'PRESIDENT'
OR JOB = 'MANAGER' GROUP by JOB

十八、
用Where子句替換HAVING子句
避免使用HAVING子句, HAVING 只會在檢索出所有記錄之後才對結果集進行過濾. 這個處理需要排序
總計等操作. 如果能透過WHERE子句限制記錄的數目,那就能減少這方面的開銷.
例如:
低效:
SELECT REGION，AVG(LOG_SIZE)
FROM LOCATION
GROUP BY REGION
HAVING REGION REGION != ‘SYDNEY’
AND REGION != ‘PERTH’
高效：
SELECT REGION，AVG(LOG_SIZE)
FROM LOCATION
WHERE REGION REGION != ‘SYDNEY’
AND REGION != ‘PERTH’

十九、
幾個使用替代的最佳化寫法
 
用EXISTS替代IN
 
使用NOT EXISTS替代NOT IN
 
用IN來替換OR
 
用UNION替換OR （適用於索引列）
 
用>=替代>
 
用< or >替代<>
 
NOT IN都是最低效的 （因為它對子查詢中的表執行了一個全表遍歷）

二十、
能使用外連線，就不要使用子查詢
inner join 內連線和where相同
left join 左向外連線，返回左邊表所有符合條件的
例如：select * from a, b where a.id=b.id(+);
right join 右向外連線，返回右邊表所有符合條件的
例如： select * from a, b where a.id(+)=b.id;
full join完整外部連線，左向外連線和右向外連線的合集
交叉連線，也稱笛卡兒積，返回左表中的每一行與右表中所有行的組合cross join

二十一、
儘量多使用COMMIT
 
COMMIT所釋放的資源:
回滾段上用於恢復資料的資訊
被程式語句獲得的鎖
redo log buffer 中的空間
ORACLE為管理上述3種資源中的內部花費
 
實際應用中需要注意：
迴圈批次插入大量資料，合理的做法是：設定一個COMMIT頻度，比如插入
1000條記錄後COMMIT一次，這樣不會因為頻繁COMMIT浪費資料庫資源;也不會最後一次COMMIT，如果插入失敗，全部回滾。

二十二、
用TRUNCATE替代DELETE
 
如果是全表刪除，用TRUNCATE替代DELETE，執行時間大大提高，並且降低高水位線，釋放不用的資料塊空間，以便資料庫重複利用資料塊空間。

二十三、
使用rowid來寫去重sql是效率最高的
表資料去重sql寫法有很多，使用rowid來寫sql是效率最高的,rowid是一個偽
列，透過ROWID可以直接定位到存放相應記錄的資料塊，然後將其讀到記憶體
，以最快的速度找到要去重的記錄。
例如：
SQL>DELETE FROM CONFIG_ACTIVE_CODE A
WHERE ROWID > (
SELECT min(rowid) FROM CONFIG_ACTIVE_CODE B
WHERE A.ACTIVE_CODE= B.ACTIVE_CODE);

二十四、
合理使用表的別名(Alias)
 
當在SQL語句中連線多個表時, 請使用表的別名並把別名字首於每個Column上。這樣一來,就能夠減少解析的時間並減少那些由Column歧義引起的語法錯誤。

二十五、
合理使用索引
 
檢索資料記錄條數在表資料總量30%以上，使用索引將不會有顯著的效率提高。檢索資料記錄條數在表資料總量10%以內，透過索引會大大提高資料查詢速度。
 索引通常會使SELECT查詢變快，但會使INSERT,UPDATE這樣的DML變慢（因為要寫索引資料，有I/O）；要合理使用索引。
 
 二十六、
養成檢視執行計劃的好習慣
為了執行語句，Oracle必須實現許多步驟，這些步驟中的每一步可能是從數
據庫中物理檢索資料行，或者用某種方法準備資料行，供發出語句的使用者使
用。Oracle用來執行語句的這些步驟的組合被稱之為執行計劃。只有知道了
ORACLE在內部到底是如何執行該SQL語句後，才能知道最佳化器選擇的執行
計劃是否為最優的。
檢視執行計劃命令： set autot trace exp;
開發人員可以使用TOAD， PL/SQL developer工具檢視SQL自己計劃，調
優自己寫的SQL語句。

二十七、
合理使用Hint提示，穩定執行計劃
 
SQL語句中可以透過提示的方式，指定想要資料庫走哪種執行計劃，稱為hint

例如：
表明對錶選擇全域性掃描的方法.
SELECT /*+FULL(A)*/ EMP_NO,EMP_NAM FROM BSEMPMS A WHERE EMP_NO='CCBZZP';
表明對錶選擇索引升序的掃描方法.
SELECT /*+INDEX_ASC(BSEMPMS PK_BSEMPMS) */ FROM BSEMPMS WHERE DPT_NO='CCBZZP';