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

531968912發表於2016-05-20
  
大家好!
 
今天和大家分享下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';

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/25462274/viewspace-2103645/,如需轉載,請註明出處,否則將追究法律責任。

相關文章