oracle sql最佳化筆記

標籤：

oracle最佳化一般分為：
1、sql最佳化（現在oracle都會根據sql語句先進行必要的最佳化處理,這種應該使用者不大了，但是像關聯和巢狀查詢肯定是和影響效能的）
A、oracle的sql語句的條件是從右往左執行的，如下語句：select * from t_user where nation=‘回族‘ and age > 20.oracle首先是查詢年齡大於20歲的，再查詢民族為回族的，最後匯總兩次得到的結果。
B、根據A中說的sql語句執行特點，上面的語句是可以進行最佳化的。A中的sql語句查詢條件不同可能會有不同的情況，見下面分析：
I、查詢條件為：nation=‘回族‘ and age > 20，先執行age>20,年齡大於20的資料很可能會很多；
II、查詢條件為：age > 20 and nation=‘回族‘ ， 先執行nation=‘回族‘ ，民族為回族的資料可能會比較少（相對年齡大於20的情況，畢竟回族是少數民族嘛）；
III、假如I和II中的第一次查詢用時差不多，那麼第二次查詢時，是在資料很多的時候查民族為回族的速度快了，還是在資料很少的時候查年齡大於20的快呢？這個可以根據實際情況嘗試下。
C、如果age欄位建立了索引（下面會說索引），那就將索引欄位的查詢條件放最右端，我曾嘗試過，大量資料情況下，速度會提高很多，甚至可能都不敢相信的那種。
其實，每個表的每條記錄都有一個rowid（還有rownum，這2個的區別後面會說），rowid可以類似的理解為對象的記憶體位址，有這個地址就能找到對應的資訊。
2、預存程序（這可能也不叫最佳化吧，是自己實踐中，臨時想到的一個方法，算是一種思路吧）
如果一條sql語句非常複雜（多表關聯，資料量龐大等情況），這種sql一般效能都非常的差，可以考慮將複雜的sql拆分成簡單的sql語句。但是開發的過程中執行只能執行一條sql語句，怎麼辦？那就可以用預存程序來編寫，預存程序返回需要的結果集即可。同時預存程序是在資料庫裡面，pl/sql developer工具可以很方便的進行測試調試擷取的結果集是否正確，這樣比起將sql放在後台來說，測試、維護方便得多。我有次寫過一條多表關聯的複雜sql語句，經過自己嘗試，發現這個多表關聯的語句中，有個表的單位欄位加上索引就會讓這條語句執行只花幾秒，但是客戶現場該欄位不能建索引（原因這裡不提了），導致這條sql執行起來要1分鐘左右。後來我把這條sql語句寫成預存程序，最後得到返回結果集所花時間為不到2秒。具體做法如下：
A、就拿下面語句作為樣本：select a.username,a.age,b.name as sexname,c.name as addrname from t_user a join t_sex b on a.sexcode=b.code join t_addr c on a.addrcode=c.code where a.age>20 and a.sexcode=‘m‘ and a.addrcode=‘xiamen‘; 實際情況的複雜語句比這個複雜多了，這裡暫時先拿這個簡單的語句來說明。
B、建立幾張事務暫存資料表，並把事務暫存資料表（這些表也可以做其他模組計算資料等用途）上面的部分欄位建立索引。針對A中的語句，建立2張暫存資料表，一張表存性別資訊（其中code欄位建立索引），一張表存住址資訊（其中code欄位建立索引），最後張表是存返回的結果集。
C、首先將複雜sql語句中需要用到的各個表的資料插入對應暫存資料表中，再查詢得到最終結果。針對A中的語句，首先將t_sex表中性別為m的資料插入性別暫存資料表中（這個語句很簡單吧？），再將地址表中廈門的地址的記錄插入地址暫存資料表中，最後通過查詢性別暫存資料表、地址暫存資料表和t_user表得到最終結果。這樣得到結果集的速度可能快很多，因為t_sex和t_addr表沒有索引，資料量大的情況，查詢起來特別慢
上面的舉例因為表比較簡單，可能也不夠恰當。但是我想說的是，複雜的sql，特別是關聯查詢時非常耗時的操作。所以首先可以想到將複雜的資料化成多條簡單的語句，再使用oracle其他最佳化方法（比如上面樣本中使用到了索引和事務暫存資料表）進行最佳化，這樣很可能會大幅提高sql效能。
3、索引
索引類似書的目錄，我們一看目錄，就知道想看的內容在哪頁了。可以大概這麼理解：索引存的是表每行記錄所在的磁碟地址，通過索引查詢，就很快知道了查詢資料所在磁碟哪些地方，如果沒有索引，那就只能在磁碟中一條條尋找了，有索引減少了io次數，自然提高了查詢資料。但是每次向表添加（刪除、更新操作類似情況）資料後，就要將新增記錄所在的地址添加到索引中，這樣就會導致新增資料的時候速度變慢。大概這麼解釋吧，為了能看懂，不一定完全正確。知道了索引，我們就可以在表上面添加適當的索引來提高查詢語句的效能。
可以將索引看成一張獨特的表，sql查詢時，如果欄位是索引欄位就會使用索引進行查詢，此時就會先查索引這張獨特的表，再去查詢屋裡表，所以不是將表的每個欄位都變成索引欄位就會提高查詢效率，同時索引會影響插入資料的效能，所以建議索引需要看場合。
4、分區表
如果一個表的資料量太大，查詢起來，肯定速度會慢，這時候可以考慮將普通表做成分區表，當然分庫也是不錯的辦法，但是有時候分庫對資料的操作很不方便，什麼是分區表，網上資料很多，就不說了。

oracle sql最佳化筆記