1. 介紹
事情是這樣的，UAT 環境的測驗小伙伴向我扔來一個小 bug，說是一個放大鏡的查詢很慢，轉幾分鐘才出資料，我立馬上開發環境試了一下，很快啊我說??，放大鏡的資料立馬就出來了，然后我登錄 UAT 環境一看，誒是有些慢?? ，于是開始了我的排查之旅...
2. 程序
首先我立馬拿到了執行的 SQL 在開發環境的資料庫執行了下，很快，都在 1s 左右，感覺沒啥問題啊，然后我就在頁面上點點點，發現好像上面有一個相關聯的下拉框，如果選中的有資料，再點擊這個放大鏡就會慢一點，然后我登錄 UAT 環境一試，哦不是這個問題，于是只能開始排查 SQL 了，
百度了一圈 Oracle 性能調優，大多很空泛，沒有一個通用的、具體的、可執行的步驟，但是找到了排查前必備的查看執行計劃explain plan，
以下是正兒八經的優化程序??：
2.1 查看該條 SQL 的執行計劃
2.1.1 生成執行計劃
在要排查的SQL前面加上explain plan for，例如以下的例子：
explain plan for
SELECT
*
FROM
SOURCE_LISTEX_202101
WHERE
pass_id = '012101200123001025061320201201002852';
2.1.2 查看執行計劃
推薦使用該 SQL 去查詢執行計劃?? （為什么？因為簡短好記??）
select * from table(dbms_xplan.display)
或者
SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE'));
查出來的應該是這個樣子：
Plan hash value: 1335523602
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 44479 | 38M| 17411 (1)| 00:03:29 |
| 1 | TABLE ACCESS BY INDEX ROWID | SOURCE_LISTEX_202101 | 44479 | 38M| 17411 (1)| 00:03:29 |
|* 2 | INDEX RANGE SCAN | LISTEX_202101_PASS_ID | 17792 | | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("PASS_ID"='012101200123001025061320201201002852')
而且如果你的 SQL 長了之后會發現，Operation 列是會有縮進的，縮進代表層級關系，就很亂??，這里我推薦 Datagrip??的右鍵可視化Explain plan，
使用方法為：選中SQL，右鍵Explain Plan，就可以查看啦，大概長這個樣子：
其中點擊 Explain Plan (Raw)，也是可以的，就是查看原生的執行計劃樣子，大概長這樣：
2.1.3 分析執行計劃
在 1.2 節可視化那個圖中，我們主要看表格中的 Total Cost， 它代表著該條操作的總消耗，我們根據層級關系逐個排查，找到最為耗時的操作，排查發現此處兩個Full Scan 全表掃描的性能消耗占據了全部總消耗的98% ((384+384)/779)??
之后根據執行計劃的層級關系我們去 SQL 中找到這兩個全表掃描對應的部分：
查一下 sett_ebankinstruct 這個表的數量 有近十萬條，看了一下表的定義，一個索引都沒加，，，
在此次 SQL 中，使用sett_ebankinstruct的欄位只有 instructionid 和 instructtype，
instructionid ： 很有可能會作為關聯條件去連接多個表，并且該欄位不會頻繁的update，故在該欄位上加索引，
如何加索引呢？
create index 索引名稱
on 表名 (欄位);
在此處加索引的 SQL 為：
create index IDX_SETT_EBANKINSTRUCT_INSTRID
on SETT_EBANKINSTRUCT (INSTRUCTIONID);
instructtype ： 考慮到 instructtype 只是型別，并且使用的情況可能就是 = 或者in (具體的幾個值),于是就不用加，
我們添加索引之后發現，第一個Full Scan 全表掃描已經消失??，因為在 sp.id = se.instructionid 進行表連接的時候走了索引，但是第二個Full Scan全表掃描仍然存在，說明此處并未走索引?? ：
原因是此處使用 not in ，括號里的instructionId并未走內部子查詢的索引，那么怎么改成走索引呢？將其改寫成 not exists 即可，
再次查看下執行計劃，發現兩個全表掃描都消失了，都變成了索引掃描（Index Scan）??????：
對比優化前后Total Cost：
優化前：779
優化后：62
優化提升：92%
3. 結論
如果排查確實是 SQL 問題，就直接看 執行計劃 ，重點關注占用Total Cost的部分，然后查看對應的 SQL ，
- 如果是表頻繁連接的欄位，就要考慮加索引了，
not in改not exists，（業界流傳not exists比not in快）
其實非也，如果主查詢和子查詢表大小相當，那么用in和exists差別不大，
如果子查詢表大，用exists快，如果子查詢表小，用in快，Where：資料量多的情況下，排除越多記錄的條件應該是先執行，
Oracle下能排除掉多的條件放后面，因為Oracle的where是從右往左執行的，格式化 SQL 后也就是從下往上執行，這樣寫那么會先排除大量的資料，因而加快后續操作的速度，
MySQL正好和Oracle相反，MySQL下的Where是從左往右執行的，格式化 SQL 之后也就是從上往下執行，
因而MySQL下Where的條件應該是排除多的條件放前面，
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/552964.html
標籤：Oracle
上一篇：boot-admin 專案資料庫預設欄位設計之最佳實踐
下一篇：返回列表