- 背景:生産上對報表查詢突然變慢,經過分析發現同僚關聯了一張750W的日志表
- 分析:
①:執行順序:
根據Operation縮進來判斷,縮進最多的最先執行;(縮進相同時,最上面的最先執行)
②:對圖中動作的一些說明:
1. 上圖中 TABLE ACCESS BY … 即描述的是該動作執行時表通路(或者說Oracle通路資料)的方式;
表通路的幾種方式:(非全部)
- TABLE ACCESS FULL(全表掃描)
- TABLE ACCESS BY ROWID(通過ROWID的表存取)
- TABLE ACCESS BY INDEX SCAN(索引掃描)
(1) TABLE ACCESS FULL(全表掃描):
Oracle會讀取表中所有的行,并檢查每一行是否滿足SQL語句中的 Where 限制條件;
全表掃描時可以使用多塊讀(即一次I/O讀取多塊資料塊)操作,提升吞吐量;
使用建議:資料量太大的表不建議使用全表掃描,除非本身需要取出的資料較多,占到表資料總量的 5% ~ 10% 或以上
(2) TABLE ACCESS BY ROWID(通過ROWID的表存取) :
先說一下什麼是ROWID?
ROWID是由Oracle自動加在表中每行最後的一列僞列,既然是僞列,就說明表中并不會實體存儲ROWID的值;
你可以像使用其它列一樣使用它,隻是不能對該列的值進行增、删、改操作;
一旦一行資料插入後,則其對應的ROWID在該行的生命周期内是唯一的,即使發生行遷移,該行的ROWID值也不變。
讓我們再回到 TABLE ACCESS BY ROWID 來:
行的ROWID指出了該行所在的資料檔案、資料塊以及行在該塊中的位置,是以通過ROWID可以快速定位到目标資料上,這也是Oracle中存取單行資料最快的方法;
(3) TABLE ACCESS BY INDEX SCAN(索引掃描):
在索引塊中,既存儲每個索引的鍵值,也存儲具有該鍵值的行的ROWID。
一個數字列上建索引後該索引可能的概念結構如下圖:
是以索引掃描其實分為兩步:
Ⅰ:掃描索引得到對應的ROWID
Ⅱ:通過ROWID定位到具體的行讀取資料
----------------索引掃描延伸-------------------
索引掃描又分五種:
- INDEX UNIQUE SCAN(索引唯一掃描)
- INDEX RANGE SCAN(索引範圍掃描)
- INDEX FULL SCAN(索引全掃描)
- INDEX FAST FULL SCAN(索引快速掃描)
- INDEX SKIP SCAN(索引跳躍掃描)
a) INDEX UNIQUE SCAN(索引唯一掃描):
針對唯一性索引(UNIQUE INDEX)的掃描,每次至多隻傳回一條記錄;
表中某字段存在 UNIQUE、PRIMARY KEY 限制時,Oracle常實作唯一性掃描;
b) INDEX RANGE SCAN(索引範圍掃描):
使用一個索引存取多行資料;
發生索引範圍掃描的三種情況:
- 在唯一索引列上使用了範圍操作符(如:> < <> >= <= between)
- 在組合索引上,隻使用部分列進行查詢(查詢時必須包含前導列,否則會走全表掃描)
- 對非唯一索引列上進行的任何查詢
c) INDEX FULL SCAN(索引全掃描):
進行全索引掃描時,查詢出的資料都必須從索引中可以直接得到(注意全索引掃描隻有在CBO模式下才有效)
----------------------- 延伸閱讀:Oracle優化器簡述 -----------------------
Oracle中的優化器是SQL分析和執行的優化工具,它負責生成、制定SQL的執行計劃。
Oracle的優化器有兩種:
- RBO(Rule-Based Optimization) 基于規則的優化器
- CBO(Cost-Based Optimization) 基于代價的優化器
RBO:
RBO有嚴格的使用規則,隻要按照這套規則去寫SQL語句,無論資料表中的内容怎樣,也不會影響到你的執行計劃;
換句話說,RBO對資料“不敏感”,它要求SQL編寫人員必須要了解各項細則;
RBO一直沿用至ORACLE 9i,從ORACLE 10g開始,RBO已經徹底被抛棄。
CBO:
CBO是一種比RBO更加合理、可靠的優化器,在ORACLE 10g中完全取代RBO;
CBO通過計算各種可能的執行計劃的“代價”,即COST,從中選用COST最低的執行方案作為實際運作方案;
它依賴資料庫對象的統計資訊,統計資訊的準确與否會影響CBO做出最優的選擇,也就是對資料“敏感”。
---------------------------------------------------------------------
d) INDEX FAST FULL SCAN(索引快速掃描):
掃描索引中的所有的資料塊,與 INDEX FULL SCAN 類似,但是一個顯著的差別是它不對查詢出的資料進行排序(即資料不是以排序順序被傳回)
e) INDEX SKIP SCAN(索引跳躍掃描):
Oracle 9i後提供,有時候複合索引的前導列(索引包含的第一列)沒有在查詢語句中出現,oralce也會使用該複合索引,這時候就使用的INDEX SKIP SCAN;
什麼時候會觸發 INDEX SKIP SCAN 呢?
前提條件:表有一個複合索引,且在查詢時有除了前導列(索引中第一列)外的其他列作為條件,并且優化器模式為CBO時
當Oracle發現前導列的唯一值個數很少時,會将每個唯一值都作為正常掃描的入口,在此基礎上做一次查找,最後合并這些查詢;
例如:
假設表emp有ename(雇員名稱)、job(職位名)、sex(性别)三個字段,并且建立了如 create index idx_emp on emp (sex, ename, job) 的複合索引;
因為性别隻有 '男' 和 '女' 兩個值,是以為了提高索引的使用率,Oracle可将這個複合索引拆成 ('男', ename, job),('女', ename, job) 這兩個複合索引;
當查詢 select * from emp where job = 'Programmer' 時,該查詢發出後:
Oracle先進入sex為'男'的入口,這時候使用到了 ('男', ename, job) 這條複合索引,查找 job = 'Programmer' 的條目;
再進入sex為'女'的入口,這時候使用到了 ('女', ename, job) 這條複合索引,查找 job = 'Programmer' 的條目;
最後合并查詢到的來自兩個入口的結果集。
摘自:https://blog.csdn.net/xuheng8600/article/details/85834796
轉載于:https://my.oschina.net/u/3775800/blog/3096221