生產環境sql語句調優實戰第十篇

陸陸續續寫了九篇關於生產環境sql語句的調優案例，發現了不少問題，可能有些問題回頭來看是比較低階的錯誤，稍加改動就能夠執行在秒級，有些可能是在秒級到毫秒級的小步提升等等，不管調優的改進多大，從dba的角度來看，好多問題都是基於資源來調優的，比如新增索引，降低IO，降低CPU消耗，提高CPU利用率等等。如果有時候從業務角度來下下功夫，可能某種程度上效果要更好於基於資源/代價的調優。
最近客戶反饋有幾條sql語句IO消耗很高，希望我們能夠給提點建議。
sql語句很短，但是執行時間在9秒左右。執行頻率也是蠻高的。平均下來一個小時100次。
語句如下：
select product_name from service_details where subscriber_no in (select subscriber_no from subscriber where prim_resource_val = :1 and sub_status = 'A') and product_status = 'A' ;

Stat Name	Statement Total	Per Execution	% Snap Total
Elapsed Time (ms)	5,304,719	9,558.05	0.88
CPU Time (ms)	1,806,243	3,254.49	0.87
Executions	555
Buffer Gets	158,171,280	284,993.30	0.95
Disk Reads	158,091,403	284,849.37	4.60
Parse Calls	555	1.00	0.00
Rows	5,612	10.11
User I/O Wait Time (ms)	3,351,159
Cluster Wait Time (ms)	0
Application Wait Time (ms)	4,142
Concurrency Wait Time (ms)	0
Invalidations	0
Version Count	35
Sharable Mem(KB)	818

    
執行計劃如下：
Plan hash value: 2174296646
-----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                      |   126 |  4284 | 50533   (1)| 00:10:07 |
|   1 |  NESTED LOOPS                |                      |       |       |            |          |
|   2 |   NESTED LOOPS               |                      |   126 |  4284 | 50533   (1)| 00:10:07 |
|*  3 |    TABLE ACCESS FULL         | SUBSCRIBER           |    18 |   342 | 50517   (1)| 00:10:07 |
|*  4 |    INDEX RANGE SCAN          | SERVICE_DETAILS_PK   |     9 |       |     1   (0)| 00:00:01 |
|*  5 |   TABLE ACCESS BY INDEX ROWID| SERVICE_DETAILS      |     7 |   105 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("PRIM_RESOURCE_VAL"=:1 AND "SUB_STATUS"='A')
   4 - access("SUBSCRIBER_NO"="SUBSCRIBER_NO")
   5 - filter("PRODUCT_STATUS"='A')

如果從資源代價的角度來看，一種思路是新增對應的索引。因為這個表是產品線中統一規劃的。所以要加入索引還是很不容易的。
如果沒有其它的調優思路，可能並行就是一把雙刃劍了，相對來說速度會高一些，但是IO和CPU的消耗會比較高，對於執行如此頻繁的語句來說使用多個並行可能對於系統負載時很高的。
看著sql語句比較簡單，但是還沒有立竿見影的效果也有些讓人著急。資料庫的角度的一些調整可能奏效不大，自己就想看看從業務角度能做點什麼。
靜下心來看看sql語句。
select product_name from service_details where subscriber_no in (select subscriber_no from subscriber where prim_resource_val = :1 and sub_status = 'A') and product_status = 'A' ;
sql語句中prim_resource_val就跟我們使用的手機號有些類似，這樣一個號碼為什麼沒有加入索引，從業務的角度來琢磨，可能是有做號碼變更之類的操作的時候這個號碼就會變化比較頻繁。而保持不變的就是subscriber_no。就類似我們去銀行辦理業務的時候顯示的客戶號。這個欄位就是主鍵列。
可能有的人有多個資源號的時候，打個比方，比如有機頂盒號，手機號等，在這個時候手機號就是主要的資源號。
這個時候再來分析為什麼產品線中沒有規劃給resource_value作為索引列，也是考慮了後期的一些變更。這個列還是變化性比較大。這樣考慮也就有一定的道理了。
因為對這部分的業務還比較熟悉，發現所需要的資源號，完全可以從一個獨立的表中得到更完整的資訊。subscriber_resource。
這個表儘管也是億級的表，但是根據資源號來查詢subscriber可以走index range scan。得到資料也要快很多。
subscriber_resource中存放著一個使用者所使用的各類資源資訊。從這個表裡直接對映resource_value得到的使用者資訊就很有限了。因為根據條件是隻需要啟用狀態的使用者，那麼我們完全可以在得到一個很簡單的使用者列表後直接來過濾使用者狀態就能得到所需要的資訊了。最後做了如下的改動。把資源表關聯進來。
select product_name 
  from service_details ser 
where subscriber_no in (select subscriber_no 
                          from subscriber 
                         where sub_status = 'A' 
                           and (subscriber_no, prim_resource_tp) in (select subscriber_no, resource_type from subcriber_resource where resource_value=:1) 
                           ) 
   and soc_status = 'A' 

先來看看執行計劃，表面來看所走的索引還是比較高效的。
-----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name                   | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                         |     7 |   196 |     7  (15)| 00:00:01 |       |       |
|   1 |  NESTED LOOPS                            |                         |       |       |            |          |       |       |
|   2 |   NESTED LOOPS                           |                         |     7 |   196 |     7  (15)| 00:00:01 |       |       |
|   3 |    VIEW                                  | VW_NSO_1                |     1 |    13 |     5   (0)| 00:00:01 |       |       |
|   4 |     HASH UNIQUE                          |                         |     1 |    36 |            |          |       |       |
|   5 |      NESTED LOOPS                        |                         |       |       |            |          |       |       |
|   6 |       NESTED LOOPS                       |                         |     1 |    36 |     5   (0)| 00:00:01 |       |       |
|   7 |        PARTITION RANGE ALL               |                         |     1 |    25 |     4   (0)| 00:00:01 |     1 |    11 |
|   8 |         TABLE ACCESS BY LOCAL INDEX ROWID| SUBSCRIBER_RESOURCE     |     1 |    25 |     4   (0)| 00:00:01 |     1 |    11 |
|*  9 |          INDEX RANGE SCAN                | SUBSCRIBER_RESOURCE_1IX |     1 |       |     3   (0)| 00:00:01 |     1 |    11 |
|* 10 |        INDEX UNIQUE SCAN                 | SUBSCRIBER_PK           |     1 |       |     1   (0)| 00:00:01 |       |       |
|* 11 |       TABLE ACCESS BY INDEX ROWID        | SUBSCRIBER              |     1 |    11 |     1   (0)| 00:00:01 |       |       |
|* 12 |    INDEX RANGE SCAN                      | SERVICE_DETAILS_PK      |     9 |       |     1   (0)| 00:00:01 |       |       |
|* 13 |   TABLE ACCESS BY INDEX ROWID            | SERVICE_DEAILS          |     7 |   105 |     1   (0)| 00:00:01 |       |       |
-----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   9 - access("RESOURCE_VALUE"=:1)
  10 - access("SUBSCRIBER_NO"="SUBSCRIBER_NO")
  11 - filter("SUB_STATUS"='A' AND "PRIM_RESOURCE_TP"="RESOURCE_TYPE")
  12 - access("SUBSCRIBER_NO"="SUBSCRIBER_NO")
  13 - filter("PRODUCT_STATUS"='A')  

最後在備份庫做相關的測試，執行時間都在毫秒級。
所以有些時候業務的角度來調優可能會有意向不到的收穫。還有幾個類似的語句，執行時間在分鐘級，調整成類似的形式之後，都在毫秒級就完成了資料查詢。