Oracle SQL優(yōu)化進(jìn)階學(xué)習(xí)

引言

對于下面的Oracle分頁如何優(yōu)化該段語句：

SELECT *

FROM (SELECT A.*, ROWNUM RN

FROM (SELECT * FROM task_log order by taskid desc) A

WHERE ROWNUM <= 40)

WHERE RN >= 21;

第一部分：

判斷并獲取問題SQL？

問題sql主要有以下兩個表象

系統(tǒng)級別表象：

CPU消耗嚴(yán)重
IO等待嚴(yán)重
頁面相應(yīng)時間過長

SQL語句表象：

冗長
執(zhí)行時間過長
從全表掃描獲取數(shù)據(jù)
PLAN的cost很大

第二部分：

SQL優(yōu)化-基礎(chǔ)篇

數(shù)據(jù)庫設(shè)計(jì)的技巧：

主鍵設(shè)計(jì)

所有的表都應(yīng)該有主鍵

主鍵盡量為單例

主鍵盡量采用INT類型

盡量避免使用字符、UUID作為主鍵
數(shù)據(jù)類型設(shè)計(jì)

盡量使用數(shù)字類型而非字符類型

盡量使用日期類型而非字符類型

定長字符可以考慮使用char類型

盡量選擇最小的空間來存儲數(shù)據(jù)
數(shù)據(jù)分割設(shè)計(jì)

采用垂直分割將熱數(shù)據(jù)和冷數(shù)據(jù)拆分

采用水平分割或者分區(qū)實(shí)現(xiàn)減少表大小
減少遞歸查詢的設(shè)計(jì)

避免connect by語法以join替代

采用存儲過程實(shí)現(xiàn)connect by特性
范式化設(shè)計(jì)和反范式化設(shè)計(jì)

范式化設(shè)計(jì)優(yōu)點(diǎn)：數(shù)據(jù)精簡、DML效率高

范式化設(shè)計(jì)缺點(diǎn)：join時查詢效率稍差

范式化設(shè)計(jì)適用環(huán)境：在線交易系統(tǒng)、需要嚴(yán)格事務(wù)支持的環(huán)境

反范式化設(shè)計(jì)優(yōu)點(diǎn)：查詢效率高，避免join

反范式化設(shè)計(jì)缺點(diǎn)：數(shù)據(jù)冗余、DML代價(jià)大

反范式化設(shè)計(jì)適用環(huán)境：數(shù)據(jù)倉庫環(huán)境、無需事務(wù)支持的環(huán)境
對象放置設(shè)計(jì)

適合大型數(shù)據(jù)庫設(shè)計(jì)

熱數(shù)據(jù)冷數(shù)據(jù)分開存放

不同業(yè)務(wù)需求數(shù)據(jù)分開存放-成本考慮

在線交易及倉庫數(shù)據(jù)分開存放
列位置設(shè)計(jì)

常用的列放在前面

列數(shù)量要盡可能少

SQL編寫的技巧

合理使用索引

概念學(xué)習(xí)：

選擇率的概念：重復(fù)值少的稱之為選擇率高；重復(fù)值多的稱之為選擇率低

索引層級的概念：索引越大，可能層級越多；索引層級越大，效率越低

Oracle索引掃描的類型：
```
-index unique scan

-index range scan

-index skip scan

-index fast full scan

-index full scan
```
使用建議：

選擇率高且被where頻繁引用需要建立B樹索引

大表的join列需要建立索引

復(fù)雜文檔類型查詢采用全文索引效率更好

索引的建立要在查詢和DML性能之間取得平衡

復(fù)合索引用法就是上一條的具體思考

第一步優(yōu)化：

對于前面的那個優(yōu)化的例子，在未建立索引之前，執(zhí)行計(jì)劃如下所示（主要關(guān)注紅色箭頭的位置）

1.png

嘗試在排序字段建立索引：

drop index idx1;

create index idx1 on task_log(taskid desc);

exec dbms_stats.gather_schema_stats(ownname =>'SCOTT',options => 'GATHER',estimate_percent => null,method_opt=>'for all indexed columns',cascade=>true,degree=> 4 );

執(zhí)行效果如下：

2.png

使用UNION ALL替代UNION
避免select *寫法
JOIN字段建議建立索引
避免復(fù)雜SQL語句
避免where 1=1寫法
避免order by rand()類似寫法

初步了解執(zhí)行計(jì)劃

Cost的概念：

成本表示優(yōu)化器對執(zhí)行語句所用時間的最優(yōu)估計(jì)；

優(yōu)化器主要分2種，CBO和RBO，對于新版本數(shù)據(jù)庫，RBO已經(jīng)基本淘汰。

CBO（基于代價(jià)的優(yōu)化器）是RBO（基于規(guī)則的優(yōu)化器）的替代品，從9i開始o(jì)racle就建議用戶使用RBO來進(jìn)行SQL的優(yōu)化；

CBO大概的優(yōu)化原理很簡單，他通過對象上的統(tǒng)計(jì)信息來計(jì)算各個執(zhí)行計(jì)劃的代價(jià)，然后選擇代價(jià)較小的執(zhí)行計(jì)劃來運(yùn)行

對于CBO來說對象（比如表，索引）上的統(tǒng)計(jì)信息就顯得十分的重要，不僅要有統(tǒng)計(jì)信息，還要保證統(tǒng)計(jì)信息是準(zhǔn)確的，不準(zhǔn)確的統(tǒng)計(jì)信息可能會帶來災(zāi)難性的結(jié)果。

SQL優(yōu)化-進(jìn)階篇

深入理解執(zhí)行計(jì)劃

了解執(zhí)行計(jì)劃的并行操作

了解執(zhí)行計(jì)劃的各種類型JOIN

了解執(zhí)行計(jì)劃的子查詢相關(guān)

了解數(shù)據(jù)庫參數(shù)對執(zhí)行計(jì)劃影響

10046事件跟蹤

索引進(jìn)階使用技巧

位圖索引

IOT

函數(shù)索引

聚簇因子

固化數(shù)據(jù)的技巧

物化視圖和查詢重寫

全局臨時表

with語句固化數(shù)據(jù)

使用分區(qū)修剪來優(yōu)化查詢

查詢指定分區(qū)

查詢不指定分區(qū)

固定執(zhí)行計(jì)劃

profile

outline

baseline

優(yōu)化join方式

HASH JOIN

NEST LOOP JOIN

Sort merge join

STAR JOIN

使用HINT優(yōu)化

first rows對分頁SQL進(jìn)行優(yōu)化

index HINT強(qiáng)制使用INDEX

use_hash、use_nj改變join方法

并行hint/+parallel(t 4)/

第二步優(yōu)化：嘗試使用HINT

SELECT /+ first_rows(20)/*