每秒執行6000的簡單SQL優化（二）

jeanron100發表於2016-10-27
原文網址 : http://blog.itpub.net/23718752/viewspace-2127254/
SQL優化

繼續前幾天的一次效能調優，這次調優難度不小，而且空間很小，看起來簡直就是絕處逢生的感覺。下面的兩條SQL語句執行頻率極高，每秒達到6000次，希望能夠優化。
select companyname from license

select supdepid from hrmdepartment where id =''
前幾天分析了一下，也嘗試了很多種方法，但是始終無法啟用索引，最後採用IOT的形式才看到效果，這是其一。
還有一點很多明眼人看出來了，為什麼建立了唯一性索引，表license中存在1行記錄，但是卻無法啟用，一個根本原因就是唯一性索引的列沒有非空約束。
為此我又進行了一次測試，新增了主鍵，那麼就會是非空約束和唯一性主鍵。
create table license tablespace users as select * from  USERV6.LICENSE ;
alter table license modify(companyname primary key);
exec dbms_stats.gather_table_stats(ownname=>null,tabname=>'LICENSE');
測試結果如下：

可以看到達到了預期的改進效果。但是改進幅度到底有多大呢。
我們抓取了一個awr的報告來看看。
改進前，是全表掃描，每次執行的buffer gets是7

而改進之後的情況如下:

第一條語句的優化暫且到這裡，如果是在11g中，可能result cache還有有一些改進之處。
再來看看第二條語句。
select supdepid from hrmdepartment where id =''
表hrmdepartment這種id是主鍵列，但是查詢使用了id=''
檢視執行計劃可以很明顯看到Filter的處理，裡面的條件很微妙NULL IS NOT NULL

這樣一個語句怎麼優化呢，一個很明顯的處理方式就是在SQL中做檢查，儘可能調整邏輯。
但是讓人尷尬的是這個應用是閉源的，無法直接修改裡面的邏輯。怎麼改進，或者說有什麼是DBA能想辦法緩解的。
首先使用唯一性索引是最高效的索引掃描方式。

能夠沾沾索引的光，其實還真有點門路，那就是建立一個複合索引，基於列supdepid,id
create unique index ind_hrm_id on HRMDEPARTMENT(id,supdepid);      
exec dbms_stats.gather_table_stats(ownname=>null,tabname=>'HRMDEPARTMENT',cascade=>true);
這個語句的效果如下：


如果執行已有的主鍵id條件的查詢，就會發現原來的唯一性索引變為了下面的區間掃描。

其實可以在繼續改進，就是建立複合索引，基於列（supdepid，id）
這樣的好處在於不會影響已有的唯一性主鍵索引。
查詢id=''的效果如下：

而根據id的值來查詢，這個是還是走原來的唯一性索引掃描。

這樣做可能看起來效果不大，畢竟掃描返回的行數都是全表的行數。
我們把資料量增大到500萬
create table test_obj as select level object_id ,'obj'||level object_name,'TABLE' object_type from dual connect by level<=5000000;
alter table test_obj modify(object_id primary key);
這個時候Ojbect_type為char型別，修改為varchar2
SQL> ALTER TABLE TEST_OBJ MODIFY(OBJECT_TYPE VARCHAR2(10));
然後把資料簡單的處理一下，讓資料的分佈基本打散。
update test_obj set object_type='VIEW' where object_id>200000 and object_id<1430000;
update test_obj set object_type='SYNONYM' where object_id>2000000 and object_id<3430000;
update test_obj set object_type='SEQUENCE' where object_id>4000000 and object_id<4743000;
資料的分佈情況如下：
SQL> select object_type,count(*)from test_obj group by object_type;
OBJECT_TYP   COUNT(*)
---------- ----------
VIEW          1229999
SYNONYM       1429999
SEQUENCE       742999
TABLE         1597003
如果我們建立複合索引基於列（object_type,object_id）
create index ind_test_obj_mx on test_obj(object_type,object_id);
exec dbms_stats.gather_table_stats(ownname=>null,tabname=>'TEST_OBJ');
全表掃描的cost為4342，則索引掃描的cost為3765，相比還是有不小的提高。
而對於這個問題的持續跟進結果就是，應用那邊也做了一些努力，我可以看到的是語句的執行頻率從原來的每小時2000萬降到了200萬。
這個改進的效果有多大。可以參見下面的圖。
橫軸是時間，縱軸是資料庫的負載
綠色的部分是每小時2000萬的時候資料庫的負載曲線
紅色的部分是每小時200萬的時候資料庫的負載曲線
藍色的部分是優化之後，每小時200萬的時候資料庫的負載曲線。

可以看到藍色的曲線還是略微要低於紅色的部分，但是改進的空間在200萬的執行頻率下效果被縮小了。
改進最大的是應用的調整，幅度可不是簡單的幾倍幾十倍，而是根本性的改變。

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

相關文章